使用 AJAX Control Toolkit 的 `AutoCompleteExtender` 搭配 Web Service 實現 TextBox 輸入時即時從資料庫查詢並顯示建議清單,支援顯示文字與隱藏值 (ID) 分離。
CREATE TABLE Ceshi (
id UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
cname NVARCHAR(30) NOT NULL
);
-- 建立取得拼音首字母的函數 (支援中文模糊搜尋)
CREATE FUNCTION dbo.f_GetPy(@str NVARCHAR(4000)) RETURNS NVARCHAR(4000)
AS BEGIN
DECLARE @strlen INT, @re NVARCHAR(4000);
DECLARE @t TABLE (chr NCHAR(1) COLLATE Chinese_PRC_CI_AS, letter NCHAR(1));
INSERT INTO @t VALUES
(N'吖','A'),(N'八','B'),(N'嚓','C'),(N'咑','D'),(N'妸','E'),(N'發','F'),
(N'旮','G'),(N'铪','H'),(N'丌','J'),(N'咔','K'),(N'垃','L'),(N'嘸','M'),
(N'拏','N'),(N'噢','O'),(N'妑','P'),(N'七','Q'),(N'呥','R'),(N'仨','S'),
(N'他','T'),(N'屲','W'),(N'夕','X'),(N'丫','Y'),(N'帀','Z');
SELECT @strlen = LEN(@str), @re = '';
WHILE @strlen > 0
BEGIN
SELECT TOP 1 @re = letter + @re, @strlen = @strlen - 1
FROM @t WHERE chr <= SUBSTRING(@str, @strlen, 1) ORDER BY chr DESC;
IF @@ROWCOUNT = 0
SELECT @re = SUBSTRING(@str, @strlen, 1) + @re, @strlen = @strlen - 1;
END
RETURN @re;
END
<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="ajax" %>
<%@ Register Assembly="AutoCompleteExtra" Namespace="AutoCompleteExtra" TagPrefix="cc1" %> <!-- 增強版 Extender -->
<asp:ScriptManager ID="ScriptManager1" runat="server" />
<asp:TextBox ID="txtCompanyName" runat="server" CssClass="searchTextBox" Width="280px" />
<cc1:AutoCompleteExtraExtender
ID="aceCompany"
runat="server"
TargetControlID="txtCompanyName"
ServiceMethod="GetCompanyNameList"
MinimumPrefixLength="1"
CompletionInterval="10"
UseContextKey="True"
AutoPostBack="false">
</cc1:AutoCompleteExtraExtender>
[System.Web.Services.WebMethod]
[System.Web.Script.Services.ScriptMethod]
public static string[][] GetCompanyNameList(string prefixText, int count, string contextKey)
{
var list = new List();
var nameList = new List();
var idList = new List();
// 呼叫資料存取層 (此處假設 CeshiManage 為 BLL)
var mgr = new CeshiManage();
mgr.GetTopUserName(count, prefixText.ToUpper(), out idList, out nameList);
for (int i = 0; i < nameList.Count; i++)
{
// 回傳格式: [顯示文字, 隱藏值]
list.Add(new string[] { nameList[i], idList[i] });
}
return list.ToArray(); // JSON 序列化為 [[text1,val1],[text2,val2],...]
}
public class CeshiManage
{
public void GetTopUserName(int count, string prefix, out List ids, out List names)
{
ids = new List();
names = new List();
string sql = @"SELECT TOP (@count) id, cname FROM Ceshi
WHERE cname LIKE @prefix + '%' OR dbo.f_GetPy(cname) LIKE @prefix + '%'
ORDER BY cname";
using (var conn = new SqlConnection(connStr))
using (var cmd = new SqlCommand(sql, conn))
{
cmd.Parameters.AddWithValue("@count", count);
cmd.Parameters.AddWithValue("@prefix", prefix);
conn.Open();
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
ids.Add(reader.GetGuid(0).ToString());
names.Add(reader.GetString(1));
}
}
}
}
}
| 屬性 | 說明 |
|---|---|
| ServiceMethod | 靜態 WebMethod 名稱,回傳 `string[][]` 或 `AutoCompleteItem[]` |
| MinimumPrefixLength | 觸發查詢的最小字元數 (建議 1-2) |
| CompletionInterval | 延遲毫秒數,避免每按鍵都查詢 |
| UseContextKey | 啟用 contextKey 參數傳遞額外過濾條件 |
| AutoCompleteExtraExtender | 增強版支援 `string[][]` 回傳格式 (標準版僅支援 `string[]`) |
.searchTextBox { border: 1px solid #ddd; padding: 5px; font-size: 14px; }
.autocomplete_list { border: 1px solid #ccc; background: white; max-height: 200px; overflow-y: auto; }
.autocomplete_item { padding: 5px 10px; cursor: pointer; }
.autocomplete_highlight { background: #0078d4; color: white; }
// 前端 JavaScript
function onItemSelected(sender, e) {
var selectedText = e.get_text(); // 顯示文字
var selectedValue = e.get_value(); // 隱藏值 (ID)
$("#hfSelectedId").val(selectedValue); // 存入 HiddenField 供後端使用
}
// Extender 設定
<cc1:AutoCompleteExtraExtender ... OnClientItemSelected="onItemSelected" />
此方案結合中文拼音首字母搜尋、ID/文字分離、延遲查詢與快取,可構建流暢好用的中文自動完成體驗。