(jQuery Autocomplete using Web Service in ASP.Net) – Với chức năng nhập thông tin sản phẩm cho đơn hàng để giúp người nhập liệu thao tác nhanh thông thường phần mềm sẽ tự động lấy các thông tin liên quan như: mã, giá, vat… Với những thông tin được điền sẵn trên các Textbox người sử dụng có thể chỉnh sửa lại nếu muốn, còn không sẽ sử dụng nguyên thông tin cũ. Bài viết dưới đây sẽ hướng dẫn các bạn các xây dựng chức năng nhập sản phẩm sử dụng jQuery AutoComplete.
- B1: Tạo CSDL SQL Customers
- B2: Tạo Bảng Products có cấu trúc phía dưới trong CSDL SQL Server
USE [Customers]
GO
CREATE PROCEDURE [dbo].[Pro_Products_List]
@Keyword nvarchar(250),
@SortField nvarchar(50),
@SortType nvarchar(10)
AS
declare@strSQL nvarchar(1000)
declare @strWhere nvarchar(500)
declare @strOrder nvarchar (50)
set @strSQL= 'Select * from Products'
set @strWhere =' Where 1=1 '
if @Keyword<>''
set @strWhere= @strWhere +' And (ProductCode like N''%' +@Keyword+'%''
Or ProductName like N''%' +@Keyword+'%'')'
if @SortField=''
Begin
set@strOrder =' Order by ProductName'
End
Else
Begin
set@strOrder =' Order by '+ @SortField + ' '+ @SortType
End
set @strSQL=@strSQL+@strWhere+@strOrder
print @strSQL
exec sp_executesql@strSQLBạn có thể tải về bảng cơ sở dữ liệu SQL bằng cách nhấn vào liên kết tải về dưới đây
Trong Visual Studio tạo thư mục Common và 1 Class có tên: Utility trong thư mục vừa tạo. Nhập đoạn Code phía dưới cho Class này.
C# Code
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
namespace AutocompleteUsingWebService
{
public class SqlDataProvider
{
#region"Membres Prives"
private string_connectionString;
#endregion
#region"Constructeurs"
public SqlDataProvider()
{
try
{
_connectionString = ConfigurationManager.ConnectionStrings["SiteSqlServer"].ConnectionString;
}
catch
{
}
}
#endregion
#region"Proprietes"
public stringConnectionString
{
get { return_connectionString; }
}
#endregion
#region"Functions"
public DataTableFillTable(string ProcName, params ObjectPara[] Para)
{
try
{
DataTable tb = new DataTable();
SqlDataAdapter adap = new SqlDataAdapter(ProcName, _connectionString);
adap.SelectCommand.CommandType = CommandType.StoredProcedure;
if ((Para != null))
{
foreach (ObjectParap in Para)
{
adap.SelectCommand.Parameters.Add(new SqlParameter(p.Name, p.Value));
}
}
adap.Fill(tb);
return tb;
}
catch
{
return null;
}
}
#endregion
}
public class ObjectPara
{
string _name;
object _Value;
public ObjectPara(stringPname, object PValue)
{
_name = Pname;
_Value = PValue;
}
public string Name
{
get { return _name; }
set { _name = value; }
}
public object Value
{
get { return _Value; }
set { _Value = value; }
}
}
}VB.NET Code
Imports System.Data.SqlClient
Imports System.Data
Namespace AutocompleteUsingWebService
Public Class SqlDataProvider
#Region "Membres Prives"
Shared _IsError As Boolean = False
Private _connectionString AsString
#End Region
#Region "Constructeurs"
Public Sub New()
Try
_connectionString = ConfigurationManager.ConnectionStrings("SiteSqlServer").ConnectionString
_IsError = False
Catch ex As Exception
_IsError = True
End Try
End Sub
#End Region
#Region "Proprietes"
Public ReadOnly Property ConnectionString() AsString
Get
Return _connectionString
End Get
End Property
#End Region
#Region "Functions"
Public FunctionFillTable(ByVal ProcName As String, ByVal ParamArrayPara() As ObjectPara) As DataTable
Try
Dim tb AsNew DataTable
Dim adap AsNew SqlDataAdapter(ProcName, _connectionString)
adap.SelectCommand.CommandType = CommandType.StoredProcedure
IfNot Para Is Nothing Then
For Eachp As ObjectParaIn Para
adap.SelectCommand.Parameters.Add(New SqlParameter(p.Name, p.Value))
Next
End If
adap.Fill(tb)
Return tb
Catch ex As Exception
Return Nothing
End Try
End Function
#End Region
End Class
Public Class ObjectPara
Dim _name As String
Dim _Value As Object
Sub New(ByVal Pname As String, ByVal PValue As Object)
_name = Pname
_Value = PValue
End Sub
Public PropertyName() As String
Get
Return _name
End Get
Set(ByVal value As String)
_name = value
End Set
End Property
Public PropertyValue() As Object
Get
Return _Value
End Get
Set(ByVal value As Object)
_Value = value
End Set
End Property
End Class
End Namespace
Chú ý: Thuộc tính SiteSqlServer chính là chuỗi Connect với SQL Server trong file Web.Config
- B5: Tạo file Service.asmx trong Project
- B6: Mở file Service.asmx và chỉnh sửa mã
<%@ WebService Language="VB" CodeBehind="~/Common/Service.vb" Class="Service" %>
- B7: Trong thư mục Common tạo file Service.vb
- B8: Nhập Code cho file Service.vb
C# Code
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Web.Services;
using AutocompleteUsingWebService;
using System.Data;
[WebService(Namespace = "http://tempuri.org/")]
[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 WebService : System.Web.Services.WebService {
[WebMethod]
public string[] ListProducts(string keyword)
{
List<string> Products = new List<string>();
SqlDataProvider objSQL = newSqlDataProvider();
DataTable objBind = objSQL.FillTable("Pro_Products_List", new ObjectPara("@Keyword", keyword.Trim()), new ObjectPara("@SortField", "CreatedDate"), new ObjectPara("@SortType", "DESC"));
if ((objBind != null))
{
foreach (DataRowrow in objBind.Rows)
{
if ((row != null))
{
//Code-Name-Price-Tax
Products.Add(string.Format("{0}//{1}//{2}//{3}//{4}", row["ProductName"], row["ProductID"], row["ProductCode"], row["UnitPrice"], row["VAT"]));
}
}
}
return Products.ToArray();
}
}
VB.NET Code
Imports System.Web
Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.Web.Script.Services
Imports System.Data.SqlClient
Imports System.Collections.Generic
Imports AutocompleteUsingWebService
<WebService(Namespace:="http://tempuri.org/")> _
<WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _
<ScriptService()> _
Public Class Service
Inherits System.Web.Services.WebService
<WebMethod()> _
<ScriptMethod(ResponseFormat:=ResponseFormat.Json)> _
Public FunctionListProducts(ByVal keyword As String) As String()
Dim Products As New List(Of String)()
Dim objSQL As New SqlDataProvider
Dim objBind As DataTable = objSQL.FillTable("Pro_Products_List", New ObjectPara("@Keyword", keyword.Trim), _
New ObjectPara("@SortField", "CreatedDate"), _
New ObjectPara("@SortType", "DESC"))
If Not objBind Is Nothing Then
For Each row As DataRow In objBind.Rows
If Notrow Is Nothing Then
If NotIsDBNull(row("ProductID")) And Not IsDBNull(row("ProductName")) Then
'Code-Name-Price-Tax
Products.Add(String.Format("{0}//{1}//{2}//{3}//{4}", row("ProductName"), row("ProductID"), row("ProductCode"), row("UnitPrice"), row("VAT")))
End If
End If
Next
End If
Return Products.ToArray()
End Function
End Class- B9: Mở file Site.Master dạng HTML và bổ xung đoạn mã phía dưới trong thẻ Head
<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.4/jquery.min.js" type = "text/javascript"></script>
<script src="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8/jquery-ui.min.js" type = "text/javascript"></script>
<link href="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8/themes/base/jquery-ui.css" rel = "Stylesheet" type="text/css" />
- B10: Mở file Default.aspx dưới dạng HTML và nhập mã HTML
<%@ PageTitle="Multi Value jQuery Autocomplete Using WebService in Asp.net" Language="vb"MasterPageFile="~/Site.Master"AutoEventWireup="false"EnableEventValidation="false" CodeBehind="Default.aspx.vb" Inherits="AutocompleteUsingWebService._Default" %>
<asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent">
<script type="text/javascript">
function pageLoad() {
$("#<%=txtProductName.ClientID %>").autocomplete({
source: function (request, response) {
$.ajax({
url: '<%=ResolveUrl("~/Service.asmx/ListProducts") %>',
data: "{ 'keyword': '" + request.term + "'}",
dataType: "json",
type: "POST",
contentType: "application/json; charset=utf-8",
success: function (data) {
response($.map(data.d, function (item) {
return {
label: item.split('//')[0],
val1: item.split('//')[1],
val2: item.split('//')[2],
val3: item.split('//')[3],
val4: item.split('//')[4]
}
}))
},
error: function (response) {
alert(response.responseText);
},
failure: function (response) {
alert(response.responseText);
}
});
},
select: function (e, i) {
$("#<%=hfProductID.ClientID %>").val(i.item.val1);
$("#<%=txtProductCode.ClientID %>").val(i.item.val2);
$("#<%=txtUnitPrice.ClientID %>").val(i.item.val3);
$("#<%=txtTax.ClientID %>").val(i.item.val4);
$("#<%=txtQuantity.ClientID %>").val(1);
},
minLength: 1
});
};
</script>
<asp:ScriptManager ID="ScriptManager1"EnablePageMethods ="true" runat="server">
</asp:ScriptManager>
<h3>
jQuery Autocomplete Using WebService in Asp.net
</h3>
<asp:UpdatePanel ID="updatePanel"runat="server"UpdateMode="Conditional">
<ContentTemplate>
<table cellpadding="1"cellspacing="2"width="85%"class="table table-striped table-hover">
<thead>
<tr>
<th valign="middle" align="center">
<asp:Label ID="plProductCode"runat="server">ProductCode</asp:Label>
</th>
<th valign="middle" align="center">
<asp:Label ID="plProductName"runat="server">ProductName</asp:Label>
</th>
<th valign="middle" align="center">
<asp:Label ID="plUnitPrice"runat="server">UnitPrice</asp:Label>
</th>
<th valign="middle" align="center">
<asp:Label ID="plQuantity"runat="server">Quantity</asp:Label>
</th>
<th valign="middle" align="center">
<asp:Label ID="plTax" runat="server">Tax (%)</asp:Label>
</th>
</tr>
</thead>
<tbody>
<tr>
<td valign="middle">
<asp:TextBox ID="txtProductCode"onkeydown="javascript:return false;" CssClass="form-control" runat="server" Width="120px"></asp:TextBox>
</td>
<td valign="middle">
<asp:TextBox ID="txtProductName"CssClass="form-control"runat="server"Width="350px"></asp:TextBox>
<asp:RequiredFieldValidatorID="valProductName"runat="server"ControlToValidate="txtProductName"Display="dynamic"resourcekey="valProductName"></asp:RequiredFieldValidator>
<asp:HiddenField ID="hfProductID" runat="server" />
</td>
<td valign="middle">
<asp:TextBox ID="txtUnitPrice"CssClass="form-control"runat="server"Width="140px"></asp:TextBox>
</td>
<td valign="middle">
<asp:TextBox ID="txtQuantity"CssClass="form-control"runat="server"Width="80px"></asp:TextBox>
</td>
<td valign="middle">
<asp:TextBox ID="txtTax" CssClass="form-control"runat="server"Width="80px"></asp:TextBox>
</td>
</tr>
</tbody>
</table>
</ContentTemplate>
</asp:UpdatePanel>
</asp:Content>
- B11: Viết Code cho file Default.aspx
C# Code
//Visit http://www.laptrinhdotnet.com for more ASP.NET Tutorials
using Microsoft.VisualBasic;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Web.UI.WebControls;
namespace AutocompleteUsingWebService
{
public partial class _Default : System.Web.UI.Page
{
#region"Event Handles"
protected voidPage_Load(object sender, System.EventArgs e)
{
try
{
if (!IsPostBack)
{
txtUnitPrice.Style["text-align"] = "right";
txtQuantity.Style["text-align"] = "right";
txtTax.Style["text-align"] = "right";
}
}
catch
{
}
}
#endregion
}
}
VB.NET Code
'Visit http://www.laptrinhdotnet.com for more ASP.NET Tutorials
Namespace AutocompleteUsingWebService
Public Class _Default
Inherits System.Web.UI.Page
#Region "Event Handles"
Protected SubPage_Load(ByVal sender AsObject, ByVal e As System.EventArgs) Handles Me.Load
Try
If NotIsPostBack Then
txtUnitPrice.Style("text-align") = "right"
txtQuantity.Style("text-align") = "right"
txtTax.Style("text-align") = "right"
End If
Catch ex As Exception
End Try
End Sub
#End Region
End Class
End Namespace
Bây giờ chạy Project bạn sẽ có kết quả như ảnh phía dưới. Mỗi khi chọn 1 sản phẩm thông tin liên quan đến sản phẩm như: Mã, Giá, Thuế sẽ tự động được lấy và điền vào Textbox.
Chúc các bạn thành công!
Quang Bình
0 comments Blogger 0 Facebook
Post a Comment