原本程序是一个查询文本框,现用户要求改程序,要如google或baidu输入一样,有自动完成功能。下图是已经完成的效果:
实现使用了Web Service技术与Ajax的AutoCompleteExtender的控件。在数据库中,写好存储过程:
usp_Fqa_LotNumberCompletionList
CREATE
PROCEDURE
[
dbo
]
.
[
usp_Fqa_LotNumberCompletionList
]
(
@PrefixText NVARCHAR ( 4000 ),
@TopCount INT
)
AS
DECLARE @W NVARCHAR ( 4000 ) = @PrefixText + ' % '
EXECUTE ( ' SELECT TOP ' + @TopCount + ' [LotNumber] FROM [dbo].[Fqa] WHERE [IsSubmit] = 1 AND [LotNumber] LIKE ''' + @w + '''' )
(
@PrefixText NVARCHAR ( 4000 ),
@TopCount INT
)
AS
DECLARE @W NVARCHAR ( 4000 ) = @PrefixText + ' % '
EXECUTE ( ' SELECT TOP ' + @TopCount + ' [LotNumber] FROM [dbo].[Fqa] WHERE [IsSubmit] = 1 AND [LotNumber] LIKE ''' + @w + '''' )
创建一个Web Service,执行存储过程:
FqaLotNumberService
using
System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Services;
/// <summary>
/// Summary description for FqaLotNumberService
/// </summary>
namespace Insus.NET
{
[WebService(Description = " Fqa Lot Number Service " , Name = " FqaLotNumberService " , Namespace = http://xxx.xxx.com/WebServices/ )]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
[System.Web.Script.Services.ScriptService]
public class FqaLotNumberService : System.Web.Services.WebService
{
BusinessBase objBusinessBase = new BusinessBase();
public FqaLotNumberService()
{
// Uncomment the following line if using designed components
// InitializeComponent();
}
[WebMethod]
public string [] GetFqaLotNumber( string prefixText, int count)
{
Parameter[] parameter = {
new Parameter ( " @PrefixText " ,SqlDbType.NVarChar, 4000 ,prefixText),
new Parameter ( " @TopCount " ,SqlDbType.Int, 4 ,count)
};
DataTable LotNumberTable = objBusinessBase.GetDataToDataSet( " usp_Fqa_LotNumberCompletionList " , parameter).Tables[ 0 ];
ArrayList array = new ArrayList();
foreach (DataRow dataRow in LotNumberTable.Rows)
{
array.Add(dataRow[ " LotNumber " ].ToString());
}
return ( string [])array.ToArray( typeof ( string ));
}
}
}
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Services;
/// <summary>
/// Summary description for FqaLotNumberService
/// </summary>
namespace Insus.NET
{
[WebService(Description = " Fqa Lot Number Service " , Name = " FqaLotNumberService " , Namespace = http://xxx.xxx.com/WebServices/ )]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
[System.Web.Script.Services.ScriptService]
public class FqaLotNumberService : System.Web.Services.WebService
{
BusinessBase objBusinessBase = new BusinessBase();
public FqaLotNumberService()
{
// Uncomment the following line if using designed components
// InitializeComponent();
}
[WebMethod]
public string [] GetFqaLotNumber( string prefixText, int count)
{
Parameter[] parameter = {
new Parameter ( " @PrefixText " ,SqlDbType.NVarChar, 4000 ,prefixText),
new Parameter ( " @TopCount " ,SqlDbType.Int, 4 ,count)
};
DataTable LotNumberTable = objBusinessBase.GetDataToDataSet( " usp_Fqa_LotNumberCompletionList " , parameter).Tables[ 0 ];
ArrayList array = new ArrayList();
foreach (DataRow dataRow in LotNumberTable.Rows)
{
array.Add(dataRow[ " LotNumber " ].ToString());
}
return ( string [])array.ToArray( typeof ( string ));
}
}
}
上面的Web Service中,你会看到一个类别BusinessBase,可以从下面地址下载:http://www.cnblogs.com/insus/articles/1654653.html
.aspx:
View Code
批号:
<
asp:TextBox
ID
="txtLotNumber"
runat
="server"
></
asp:TextBox
>
< asp:Button ID ="ButtonSearch" runat ="server" OnClick ="ButtonSearch_Cliek" Text ="Search" />
< ajaxToolkit:AutoCompleteExtender ID ="AutoCompleteExtender1" runat ="server" MinimumPrefixLength ="1"
TargetControlID ="txtLotNumber" ServiceMethod ="GetFqaLotNumber" ServicePath ="~/WebServices/FqaLotNumberService.asmx"
CompletionSetCount ="10" >
</ ajaxToolkit:AutoCompleteExtender >
< asp:Button ID ="ButtonSearch" runat ="server" OnClick ="ButtonSearch_Cliek" Text ="Search" />
< ajaxToolkit:AutoCompleteExtender ID ="AutoCompleteExtender1" runat ="server" MinimumPrefixLength ="1"
TargetControlID ="txtLotNumber" ServiceMethod ="GetFqaLotNumber" ServicePath ="~/WebServices/FqaLotNumberService.asmx"
CompletionSetCount ="10" >
</ ajaxToolkit:AutoCompleteExtender >
实现过程中,Ajax AutoCompleteExtender控件,只能使用本域的Service,不能跨域,此问题Insus.NET花上不少时间测试。原本Service是摆放在一个单独的Web Service的站点上的,取不到数据,只能把Service移至本站点中来。