AjaxHelper.cs

出自 ProgWiki
於 2008年11月25日 (二) 16:15 由 Player (對話 | 貢獻) 所做的修訂 (AjaxHelper移動到AjaxHelper.cs)

(差異) ←上個修訂 | 最新修訂 (差異) | 下個修訂→ (差異)
前往: 導覽搜尋

用途

  • GetCompletionList
用於簡化AJAX Control Toolkit的AutoComplete的Server端取清單值的程式碼撰寫。

AjaxHelper.cs

#define _Use_ClassSQL
 
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Text;
using System.Data.SqlClient;
using System.Collections.Generic;
 
/// <summary>
/// AjaxHelper 的摘要描述
/// </summary>
public class AjaxHelper
{
	public AjaxHelper()
	{
		//
		// TODO: 在此加入建構函式的程式碼
		//
	}
 
#if (_Use_ClassSQL)
	public static string[] GetCompletionList(string strSqlSelect, string strColumn, int count)
	{
		MyDB.ClassSQL objSQL = new MyDB.ClassSQL();
		using (SqlConnection objConn = objSQL.OpenConnection())
		{
			using (DataSet objDS = objSQL.ExecuteDataSet(objConn, strSqlSelect))
			{
				if ((objDS.Tables.Count == 0) ||
					(objDS.Tables[0].Rows.Count == 0))
				{
					//無資料
				}
				else
				{
					int nCount = (objDS.Tables[0].Rows.Count > count) ? count : objDS.Tables[0].Rows.Count;
					List<string> items = new List<string>(objDS.Tables[0].Rows.Count);
					int index = 0;
					foreach (DataRow row in objDS.Tables[0].Rows)
					{
						//有bug
						//參照 http://www.codeplex.com/AjaxControlToolkit/WorkItem/View.aspx?WorkItemId=13342
						//items.Add(row[strColumn].ToString().Trim());
 
						//bug修正後版本
						items.Add(string.Format(
							"'{0}'",
							row[strColumn].ToString().Trim()));
 
						index++;
						if (index >= nCount)
							break;
					}
 
					return items.ToArray();
				}
			}
		}
 
		return default(string[]);
	}
#else
	public static string[] GetCompletionList(string strSqlSelect, string strColumn, int count)
	{
		string strConnection = ConfigurationManager.ConnectionStrings["LocalSqlServer"].ConnectionString;
 
		using (SqlConnection objConn = new SqlConnection(strConnection))
		{
			objConn.Open();
 
			using (SqlCommand objCommand = new SqlCommand(strSqlSelect, objConn))
			{
				using (DataSet objDS = new DataSet())
				{
					using (SqlDataAdapter objDA = new SqlDataAdapter(objCommand))
					{
						objDA.Fill(objDS);
 
						if ((objDS.Tables.Count == 0) ||
							(objDS.Tables[0].Rows.Count == 0))
						{
							//無資料
						}
						else
						{
							int nCount = (objDS.Tables[0].Rows.Count > count) ? count : objDS.Tables[0].Rows.Count;
							List<string> items = new List<string>(objDS.Tables[0].Rows.Count);
							int index = 0;
							foreach (DataRow row in objDS.Tables[0].Rows)
							{
								//有bug
								//參照 http://www.codeplex.com/AjaxControlToolkit/WorkItem/View.aspx?WorkItemId=13342
								//items.Add(row[strColumn].ToString().Trim());
 
								//bug修正後版本
								items.Add(string.Format(
									"'{0}'",
									row[strColumn].ToString().Trim()));
 
								index++;
								if (index >= nCount)
									break;
							}
 
							return items.ToArray();
						}
					}
				}
			}
		}
 
		return default(string[]);
	}
#endif
}

使用範例

[System.Web.Services.WebMethodAttribute(), System.Web.Script.Services.ScriptMethodAttribute()]
	public static string[] GetCompletionList(string prefixText, int count, string contextKey)
	{
		string strSqlSelect = string.Format(
@"select distinct top {0} ColumnName
 from MyTable 
 where ColumnName like {1}
 ORDER BY ColumnName ASC",
 count,
 SafeSQL.QuoteLikeRight(prefixText));
 
		return AjaxHelper.GetCompletionList(strSqlSelect, "ColumnName", count);
	}

相關