Many times in web forms we need to provide a listbox with lots of elements, A good solution is to filter and page these elements to speedup page loading and optimize DB/Network usage..
Update! I've rewritten the code as jQuery plugin, made few enhancements and provided a complete sample code in VB.Net and C#. check-out the new plugin of Paging Listbox.
This is a complete implementation of a paging listbox using jQuery on client side and ASP.Net on server side. and should look like this..
The form web page, which also contains the JavaScript class to build the select box, of course don't forget to download the jQuery library.<html>
<head>
<title>jQuery & Ajax Fun: Implementing a Paging Listbox</title>
<script type="text/javascript" src="JS/jquery-1.3.min.js"></script>
<script type="text/javascript">
function AjaxListBox() {
this.source = '';
this.divID= '';
this.keyID= '';
this.buttonID= '';
this.lastKey= '';
this.startup= true;
this.minWidth=0;
this.position = {'top':0,'left':0};
var self = this;
this.init= function() {
$(document).ready(function(){
//calc position and min-width for listbox
self.minWidth = $('#'+self.keyID).width()+ $('#'+self.buttonID).width()+4;
self.position = $('#'+self.keyID).position();
self.position.top= self.position.top + $('#'+self.keyID).height()+2;
// Position and hide div
$('#'+self.divID).css({'display':'none','border':'gray 1px solid','position':'absolute','z-index':5,'top':self.position.top,'left':self.position.left});
// bind onclick handler for 'toggle' button
$('#'+self.buttonID).bind('click',null,self.toggle);
// bind onkeydown handler for 'Key' textinput and call find function
$('#'+self.keyID).bind('keydown',null,self.keydown);
//load list
self.load();
});
}
this.load= function(key,pi) {
if(key==null ||key=='undefined') key='';
if(pi==null ||pi=='undefined') pi='';
//Save key to use when move through pages
this.lastKey= key;
$('#'+this.divID).html('please wait..');
$.get(this.source,{'key':key,'pi': pi},this.loaded,'html' );
}
this.loaded = function(data,txtStatus) {
//Set Inner html with response of Ajax request
$('#'+self.divID).html(data);
$('#'+self.divID+' > select').css({'border-width':'0'});
//Add handler for onchange to reload when another page is requested
$('#'+self.divID+' > select').bind('change',null,self.change);
//Add handler for onblur to hide box
$('#'+self.divID+' > select').bind('blur',null,self.hide);
if (self.startup) self.startup=false;
else self.show();
}
this.change = function() {
//Get Value of Select Box
var v = $('#'+self.divID+' > select').val();
//To do paging the value must be like 'pi=2' which means go to page 2
if (/^pi=\d+$/i.test(v)) {
var pi= v.replace(/pi=/i,'');
self.load(self.lastKey,pi);
}
}
this.toggle = function(e) {
if ($('#'+self.divID).css('display')=='none') self.show();
else self.hide();
}
this.show = function(e){
$('#'+self.divID).show();
//Insure width is more than min-width
var w = $('#'+self.divID+' > select').width();
if (w>0 && w<self.minWidth) $('#'+self.divID+' > select').width(self.minWidth);
}
this.hide = function(e){
$('#'+self.divID).hide();
}
this.find = function() {
//text to search for
self.load($('#'+self.keyID).val());
}
this.keydown = function(e) {
// this will catch pressing enter and call find function
var intKey = e.keyCode;
if(intKey == 13) {
self.find();
//and prevent submitting the form that contain this input box
return false;
}
}
}
</script>
<style type="text/css">
* {
font:12px arial
}
.AjaxListBoxKey {
border:gray 1px solid;
width:120px;
}
.AjaxListBoxKeyBTN{
border:silver 1px solid;
background-color:#333333;
color:white;
padding:.5px;
font:12px arial;
}
</style>
</head>
<body>
<form id="form1" action="" method="post">
Select Product
<input id="key" name="key" type="text" class="AjaxListBoxKey" /><input type="button" id="find" class="AjaxListBoxKeyBTN" value="▼" />
<div id="box"></div>
<script type="text/javascript">
var box = new AjaxListBox();
box.source = "listbox.aspx";
box.divID = "box";
box.keyID = "key";
box.buttonID= "find";
box.init();
</script>
</form>
</body>
</html>
The server side page(whatever the language is!) is requested to handle query string parameters (pi: PageIndex, key: Search keyword) and response with just a list box(no other tags!) that contains the matched elements at the requested page index, plus 2 extra elements to go to previous and next pages indexes with value like 'pi=3' which means go to page 3. Of course you can select the PageSize and listbox size that works for you.
Here is server side page "listbox.aspx"<%@ Page Language="VB" %>
<script runat="server" language="VB">
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim key As String = Request.QueryString("key") & ""
Dim PageIndex As Integer = 1
Try
PageIndex = Integer.Parse(Request.QueryString("PI"))
Catch ex As Exception
PageIndex = 1
End Try
Dim ps As New Products
ps.PageSize = 5
ps.PageIndex = PageIndex
ps.SelectItems(Product.ProductStatus.Active, key)
Response.Write(ps.PagingBox("ProductID", 0, , , 5))
ps = Nothing
End Sub
</script>
On Listbox.aspx I'm using my favorite[Traditional yet Powerful : Data Access Layer for ASP.Net] to access the products Table after adding the following function to the collection class to build the listbox.Public Function PagingBox(ByVal FieldID As String, ByVal SelectedID As Integer, Optional ByVal FieldClass As String = "", Optional ByVal Onchange As String = "", Optional ByVal size As Integer = 0) As String
Dim ret As New StringBuilder("<select name=""" & FieldID & """ id=""" & FieldID & """")
If FieldClass > "" Then ret.Append(" FieldClass=""" & FieldClass & """")
If Onchange > "" Then ret.Append(" Onchange=""" & Onchange & """")
If size > 0 Then ret.Append(" size=""" & size + 2 & """")
ret.Append(">")
If Me.Count = 0 Then
ret.Append("<option value="""">-- No Results! --</option>")
End If
If PageIndex > 1 Then
ret.Append("<option value=""pi=" & (PageIndex - 1) & """>" & _
"-- to Page " & (PageIndex - 1) & " of " & PageCount & " --</option>")
End If
For i As Integer = 0 To Me.Count - 1
Dim P As Product = Item(i)
ret.Append("<option value=""" & P.ID & """")
If P.ID = SelectedID Then ret.Append(" selected")
ret.Append(">" & P.Name & "</option>")
Next
If PageIndex < PageCount And PageIndex > 0 Then
ret.Append("<option value=""pi=" & (PageIndex + 1) & """>" & _
"-- to Page " & (PageIndex + 1) & " of " & PageCount & " --</option>")
End If
ret.Append("</select>")
Return ret.ToString
End Function
Many greetings to jQuery folks..
After i posted [Traditional yet Powerful : Data Access Layer for ASP.Net].. it is time to extend it to support a multi-language ASP.Net Application..
To support many languages.. Products table should be split into 2 tables. one for basic information and the other for language-dependent fields. here is the SQL Create script:CREATE TABLE [dbo].[Products](
[ID] [int] PRIMARY KEY IDENTITY(1,1) NOT NULL,
[Status] [tinyint] NOT NULL DEFAULT (0),
[Price] [decimal](18,0) NOT NULL DEFAULT (0),
)
CREATE TABLE [dbo].[ProductsDetails](
[ID] [int] NOT NULL DEFAULT (0),
[Lang] [varchar](2) NOT NULL DEFAULT (''),
[Name] [nvarchar](50) NOT NULL DEFAULT (''),
[Description] [nvarchar](500) NOT NULL DEFAULT (''),
CONSTRAINT [PK_ProductsDetails] PRIMARY KEY CLUSTERED
(
[ID] ASC,
[Lang] ASC
)
)
You should notice that multi-language text fields are now saved in nvarchar instead of varchar to support Unicode.
Now, DAL classes need few changes and adding another 2 classes
The Entity Class for ProductsImports System.Data
Imports System.Data.Common
Imports Microsoft.VisualBasic
Public Class Product
Public Enum ProductStatus
None = 0
Active = 1
Inactive = 2
End Enum
Public ID As Integer
Public Price As Decimal
Public Status As ProductStatus
''' <summary>Detail Collection of this product</summary>
Public Details As ProductsDetails
''' <summary> if GetLang=Nothing (default) no Detail Records is queried
''' if GetLang="EN" then Detail(EN) is queried and added to the details Collection and you can refer to it by .Detail property
''' if GetLang="" then All Details records are queried and add to details collection and you can refer to each of them by .Detail(Lang) property
''' </summary>
Public GetLang As String = Nothing
Public Sub New(Optional ByVal ProductID As Integer = 0)
SelectItem(ProductID)
End Sub
Public Sub New(ByVal ProductLang As String, Optional ByVal ProductID As Integer = 0)
GetLang = ProductLang
SelectItem(ProductID)
End Sub
''' <summary>Initialize Product Fields</summary>
Public Sub Initialize()
ID = 0
Price = 0
Status = ProductStatus.Active
If Details Is Nothing Then
Details = New ProductsDetails
Else
Details.Clear()
End If
End Sub
''' <summary>Return a Detail Object based on Lang param. If Lang was not specified return first Detail Object found</summary>
Public ReadOnly Property Detail(Optional ByVal Lang As String = "") As ProductDetail
Get
If Details.Count = 0 Then Return Nothing
If Lang = "" Then Return Details(0)
For i As Integer = 0 To Details.Count - 1
If Details(i).Lang.ToUpper = Lang.ToUpper Then
Return Details(i)
End If
Next
Return Nothing
End Get
End Property
Friend Sub Populate(ByRef dr As DbDataReader)
Populate(CType(dr, Object))
End Sub
Friend Sub Populate(ByRef dr As DataRow)
Populate(CType(dr, Object))
End Sub
Private Sub Populate(ByRef dr As Object)
ID = CInt(dr("ID"))
Price = CDec(dr("Price"))
Status = CByte(dr("Status"))
End Sub
''' <summary>Select Product by ID</summary>
Public Sub SelectItem(ByVal ProductID As Integer)
Call Initialize()
If ProductID = 0 Then Exit Sub
Dim cmd As New GenericCommand("SQLConn")
Dim rdr As DbDataReader
'Select Query
cmd.CommandText = "Select Top 1 Products.* "
If GetLang > "" Then cmd.CommandText += ",Lang,Name,Description"
'from
cmd.CommandText += " from Products"
If GetLang > "" Then cmd.CommandText += ",ProductsDetails"
'where
cmd.CommandText += " Where Products.ID=@ID"
If GetLang > "" Then cmd.CommandText += " and ProductsDetails.ID=Products.ID and Lang=@Lang"
cmd.AddParam("@ID", ProductID)
If GetLang > "" Then cmd.AddParam("@Lang", GetLang)
rdr = cmd.ExecuteReader()
If rdr.Read() Then
Populate(rdr)
If GetLang > "" Then
'' Add Detail
Dim det As New ProductDetail
det.Populate(rdr)
Details.Add(det)
det = Nothing
End If
End If
rdr.Close()
rdr = Nothing
cmd = Nothing
If GetLang = "" And GetLang IsNot Nothing And ID > 0 Then
Details.SelectItems(ID)
End If
End Sub
''' <summary>Insert new Product and get new ID</summary>
Public Sub InsertItem()
If ID <> 0 Then Exit Sub
Dim cmd As New GenericCommand("SQLConn")
cmd.CommandText = "Insert Into Products (Price,Status) Values (@Price,@Status)"
cmd.AddParam("@Price", Price)
cmd.AddParam("@Status", Status)
ID = CInt(cmd.ExecuteIdentity())
cmd = Nothing
'Insert Details if any
For i As Integer = 0 To Details.Count - 1
'first: set new ID on details objects
Details(i).ID = ID
Details(i).UpdateOrInsertItem()
Next
End Sub
''' <summary>Update Product</summary>
Public Sub UpdateItem()
If ID = 0 Then Exit Sub
Dim cmd As New GenericCommand("SQLConn")
cmd.CommandText = "Update Products set Price=@Price,Status=@Status where ID=@ID"
cmd.AddParam("@Price", Price)
cmd.AddParam("@Status", Status)
cmd.AddParam("@ID", ID)
cmd.ExecuteNonQuery()
cmd = Nothing
'Update Details if any
For i As Integer = 0 To Details.Count - 1
Details(i).UpdateOrInsertItem()
Next
End Sub
''' <summary>Delete This product</summary>
Sub DeleteItem()
If ID = 0 Then Exit Sub
Dim cmd As New GenericCommand("SQLConn")
cmd.CommandText = "DELETE FROM Products WHERE ID=@ID"
'will assume that you set relationship between the 2 tables with 'cascade delete' to delete Details records when product is deleted
' or delete them by adding another query "DELETE FROM ProductsDetails WHERE ID=@ID"
cmd.AddParam("@ID", ID)
cmd.ExecuteNonQuery()
cmd = Nothing
Call Initialize()
End Sub
Protected Overrides Sub Finalize()
Details = Nothing
MyBase.Finalize()
End Sub
End Class
The Entity Class for Products DetailsPublic Class ProductDetail
Public ID As Integer
Public Lang As String
Public Name As String
Public Description As String
Public Sub New()
Initialize()
End Sub
Public Sub New(ByVal ProductID As Integer, ByVal ProductLang As String)
SelectItem(ProductID, ProductLang)
End Sub
''' <summary>Initialize Fields</summary>
Public Sub Initialize()
ID = 0
Lang = ""
Name = ""
Description = ""
End Sub
''' <summary>Select Details by ID and Lang</summary>
Public Sub SelectItem(ByVal ProductID As Integer, ByVal ProductLang As String)
Call Initialize()
If ProductID = 0 Or ProductLang = "" Then Exit Sub
Dim cmd As New GenericCommand("SQLConn")
Dim rdr As DbDataReader
cmd.CommandText = "Select Top 1 * from ProductsDetails Where ID=@ID and Lang=@Lang"
cmd.AddParam("@ID", ProductID)
cmd.AddParam("@Lang", ProductLang)
rdr = cmd.ExecuteReader()
If rdr.Read() Then
Populate(rdr)
End If
rdr.Close()
rdr = Nothing
cmd = Nothing
End Sub
Friend Sub Populate(ByRef dr As DbDataReader)
Populate(CType(dr, Object))
End Sub
Friend Sub Populate(ByRef dr As DataRow)
Populate(CType(dr, Object))
End Sub
Private Sub Populate(ByRef dr As Object)
ID = CInt(dr("ID"))
Lang = dr("Lang")
Name = dr("Name")
Description = dr("Description")
End Sub
''' <summary>Update Detail or Insert Detail if not there</summary>
Public Sub UpdateOrInsertItem()
If ID = 0 Or Lang = "" Then Exit Sub
Dim cmd As New GenericCommand("SQLConn")
cmd.CommandText = "Update ProductsDetails set Name=@Name,Description=@Description where ID=@ID and Lang=@Lang"
cmd.AddParam("@Name", Name)
cmd.AddParam("@Description", Description)
cmd.AddParam("@ID", ID)
cmd.AddParam("@Lang", Lang)
'Try to update
If cmd.ExecuteNonQuery() = 0 Then
'if affected rows=0 cause Detail record is not there , then Insert:
cmd.CommandText = "Insert Into ProductsDetails (ID,Lang,Name,Description) Values (@ID,@Lang,@Name,@Description)"
cmd.ExecuteNonQuery()
End If
cmd = Nothing
End Sub
''' <summary>Delete This Detail</summary>
Sub DeleteItem()
If ID = 0 Or Lang = "" Then Exit Sub
Dim cmd As New GenericCommand("SQLConn")
cmd.CommandText = "DELETE FROM ProductsDetails WHERE ID=@ID and Lang=@Lang"
cmd.AddParam("@ID", ID)
cmd.AddParam("@Lang", Lang)
cmd.ExecuteNonQuery()
cmd = Nothing
Call Initialize()
End Sub
End Class
and The Entity Collection Class for ProductsPublic Class Products
Inherits CollectionBase
''' <summary>PageSize=0 means no paging</summary>
Public PageSize As Integer
''' <summary>PageIndex=0 means no paging</summary>
Public PageIndex As Integer
Private mPageCount As Integer
''' <summary>To Get Top records if larger than 0</summary>
Public TopRecords As Integer
Public GetLang As String = Nothing
Public Sub New(Optional ByVal Lang As String = Nothing)
Call Initialize()
PageSize = 0
PageIndex = 0
TopRecords = 0
GetLang = Lang
End Sub
''' <summary>Initialize collection</summary>
Public Sub Initialize()
mPageCount = 0
List.Clear()
End Sub
Public ReadOnly Property PageCount() As Integer
Get
Return mPageCount
End Get
End Property
''' <summary>Gets or sets the element at the specified zero-based index</summary>
Default Public Property Item(ByVal Index As Integer) As Product
Get
Return List.Item(Index)
End Get
Set(ByVal value As Product)
List.Item(Index) = value
End Set
End Property
''' <summary>Adds an object to the end of the Collection</summary>
Public Function Add(ByVal Obj As Product) As Integer
Return List.Add(Obj)
End Function
''' <summary>Select Products by Status, More Search Params can be added..</summary>
Public Sub SelectItems(Optional ByVal Status As Product.ProductStatus = Product.ProductStatus.None)
Call Initialize()
Dim Top As String = ""
If TopRecords > 0 Then Top = " TOP " & TopRecords & " "
If PageSize > 0 And PageIndex > 0 Then Top = " TOP " & (PageIndex * PageSize) & " "
Dim Cmd As New GenericCommand("SQLConn")
Cmd.PageSize = PageSize
Cmd.PageIndex = PageIndex
'Count Query
Cmd.CountCommandText = "SELECT COUNT(*) from Products"
'Select Query
Cmd.CommandText = "SELECT " & Top & " Products.* "
If GetLang > "" Then Cmd.CommandText += ",Lang,Name,Description"
Cmd.CommandText += " from Products"
'Detials Table ?
If GetLang > "" Then
Cmd.CountCommandText += ",ProductsDetails"
Cmd.CommandText += ",ProductsDetails"
End If
'Where
Cmd.CountCommandText += " Where 0=0"
Cmd.CommandText += " Where 0=0"
'Tables inner join ?
If GetLang > "" Then
Cmd.CountCommandText += " and ProductsDetails.ID=Products.ID and Lang=@Lang"
Cmd.CommandText += " and ProductsDetails.ID=Products.ID and Lang=@Lang"
Cmd.AddParam("@Lang", GetLang)
End If
'Status?
If Status > 0 Then
Cmd.CountCommandText += " and Status=@Status"
Cmd.CommandText += " and Status=@Status"
Cmd.AddParam("@Status", Status)
End If
Dim DT As DataTable = Cmd.ExecuteDataTable("Products")
mPageCount = Cmd.PageCount
Dim p As Product
For Each row As DataRow In DT.Rows
p = New Product()
p.Populate(row)
If GetLang > "" Then
p.Details = New ProductsDetails
Dim d As New ProductDetail
d.Populate(row)
p.Details.Add(d)
d = Nothing
End If
Add(p)
p = Nothing
Next
DT = Nothing
Cmd = Nothing
End Sub
Protected Overrides Sub Finalize()
MyBase.Finalize()
End Sub
End Class
and The Entity Collection Class for ProductsDetailsPublic Class ProductsDetails
Inherits CollectionBase
''' <summary>PageSize=0 means no paging</summary>
Public PageSize As Integer
''' <summary>PageIndex=0 means no paging</summary>
Public PageIndex As Integer
Private mPageCount As Integer
''' <summary>To Get Top records if larger than 0</summary>
Public TopRecords As Integer
Public Sub New()
Call Initialize()
PageSize = 0
PageIndex = 0
TopRecords = 0
End Sub
''' <summary>Initialize collection</summary>
Public Sub Initialize()
mPageCount = 0
List.Clear()
End Sub
Public ReadOnly Property PageCount() As Integer
Get
Return mPageCount
End Get
End Property
''' <summary>Gets or sets the element at the specified zero-based index</summary>
Default Public Property Item(ByVal Index As Integer) As ProductDetail
Get
Return List.Item(Index)
End Get
Set(ByVal value As ProductDetail)
List.Item(Index) = value
End Set
End Property
''' <summary>Adds an object to the end of the Collection</summary>
Public Function Add(ByVal Obj As ProductDetail) As Integer
Return List.Add(Obj)
End Function
''' <summary>Select Products Details by ID or Lang</summary>
Public Sub SelectItems(Optional ByVal ID As Integer = 0, Optional ByVal Lang As String = "")
Call Initialize()
Dim Tbl As DataTable
Dim Top As String = ""
If TopRecords > 0 Then Top = " TOP " & TopRecords & " "
If PageSize > 0 And PageIndex > 0 Then Top = " TOP " & (PageIndex * PageSize) & " "
Dim Cmd As New GenericCommand("SQLConn")
Cmd.PageSize = PageSize
Cmd.PageIndex = PageIndex
Cmd.CountCommandText = "SELECT COUNT(*) FROM ProductsDetails where 0=0"
Cmd.CommandText = "SELECT " & Top & " * FROM ProductsDetails where 0=0"
If ID > 0 Then
Cmd.CountCommandText += " and ID=@ID"
Cmd.CommandText += " and ID=@ID"
Cmd.AddParam("@ID", ID)
End If
If Lang > "" Then
Cmd.CountCommandText += " and Lang=@Lang"
Cmd.CommandText += " where Lang=@Lang"
Cmd.AddParam("@Lang", Lang)
End If
Tbl = Cmd.ExecuteDataTable("ProductsDetails")
mPageCount = Cmd.PageCount
Dim d As ProductDetail
For Each Row As DataRow In Tbl.Rows
d = New ProductDetail()
d.Populate(Row)
Add(d)
d = Nothing
Next
Tbl = Nothing
Cmd = Nothing
End Sub
Protected Overrides Sub Finalize()
MyBase.Finalize()
End Sub
End Class
A sample of usage to select a product and one Language detail:Dim P As New Product("EN", 100)
Response.Write("ID=" & P.ID)
Response.Write("Status=" & P.Status)
Response.Write("Lang=" & P.Detail.Lang)
Response.Write("Name=" & P.Detail.Name)
Response.Write("Description=" & P.Detail.Description)
P = Nothing
A sample to select a product and all Language details:Dim P As New Product("", 100)
Response.Write("ID=" & P.ID)
Response.Write("Status=" & P.Status)
Response.Write("Name=" & P.Detail("EN").Name)
Response.Write("Name=" & P.Detail("RU").Name)
P = Nothing
A sample to insert new productDim p As New Product
p.Price = 1000
Dim pd As New ProductDetail
pd.Lang = "EN"
pd.Name = "Product 1"
p.Details.Add(pd)
pd = New ProductDetail
pd.Lang = "RU"
pd.Name = "Продукт 1"
p.Details.Add(pd)
p.InsertItem()
Response.Write("id=" & p.ID)
p = Nothing
pd = Nothing
And a Sample of usage to select list of products and one language detail with pagingDim Ps As New Products("RU")
Ps.PageSize = 10
Ps.PageIndex = 2
Ps.SelectItems()
For i As Integer = 0 To Ps.Count - 1
Dim p As Product = Ps(i)
Response.Write("ID=" & p.ID)
Response.Write("Name=" & p.Detail.Name)
Response.Write("<hr>")
p = Nothing
Next
WriteLn("PageCount=" & Ps.PageCount)
Ps = Nothing
As Before, DAL uses a very helpful generic Command class to [Write Less & Generic Data Access Code in ADO.NET 2.0].
That is it! Hope that was helpful, Also if you have a better approach to this design pattern I would be glad to hear your ideas..
Since I posted [Data Access Layer for Classic ASP], I've been trying to find time to post the ASP.Net version too..
This is a complete example of creating Data Access Layer(DAL) using the traditional yet powerful "Object to Relational mapping model" or "Custom Entity Classes" that support Paging too..
Many people talk about using the Datasets method for creating a DAL as in [Tutorial 1: Creating a Data Access Layer] and how it is fast to generate.
but for me the Traditional 'Object to Relational mapping' model seems more simpler to work with and to extend, and about the slow creation time issue: as a developer you know that creating these classes for the 2nd time things gets more faster, most of its code is cut+paste-friendly and there are software that would generate it for you too..
They would also say that datasets method is data-source independent (no DBMS), not just database independent, well my proposed DAL is surely database independent and what are the chances that your application will move away from a DBMS? well,ZERO. and still those classes can encapsulate anything you want, even if data is saved in csv files.
The 'Object to Relational mapping' model consists of :
Entity Class - represents a single record or row from a database resultset.
Entity Collection Class - contains a collection of entity objects or a tabular list of data from a resultset.
Add to that, DAL uses a very helpful generic Command class i posted before to [Write Less & Generic Data Access Code in ADO.NET 2.0].
As an example of DAL, we'll use a simple Table of 'Products', here is the SQL Create script:CREATE TABLE [Products] (
[ID] [int] PRIMARY KEY IDENTITY(1, 1),
[Name] [varchar] (50) NOT NULL DEFAULT (''),
[Description] [varchar] (500) NOT NULL DEFAULT (''),
[Status] [tinyint] NOT NULL DEFAULT (0),
[Price] [decimal](18, 0) NOT NULL DEFAULT (0)
)
The Entity ClassImports System.Data
Imports System.Data.Common
Imports Microsoft.VisualBasic
Public Class Product
'' Table Fields
Public ID As Integer
Public Name As String
Public Description As String
Public Price As Decimal
Public Status As ProductStatus
Public Enum ProductStatus
None = 0
Active = 1
Inactive = 2
End Enum
Public Sub New(Optional ByVal ProductID As Integer = 0)
SelectItem(ProductID)
End Sub
''' <summary>Initialize Product Fields</summary>
Public Sub Initialize()
ID = 0
Name = ""
Description = ""
Price = 0
Status = ProductStatus.Active
End Sub
Friend Sub Populate(ByRef dr As DbDataReader)
Populate(CType(dr, Object))
End Sub
Friend Sub Populate(ByRef dr As DataRow)
Populate(CType(dr, Object))
End Sub
Private Sub Populate(ByRef dr As Object)
ID = CInt(dr("ID"))
Name = dr("Name")
Description = dr("Description")
Price = CDec(dr("Price"))
Status = CByte(dr("Status"))
End Sub
''' <summary>Select Product by ID</summary>
Public Sub SelectItem(ByVal ProductID As Integer)
Call Initialize()
If ProductID = 0 Then Exit Sub
Dim cmd As New GenericCommand("SQLConn")
Dim rdr As DbDataReader
cmd.CommandText = "Select Top 1 * from Products Where ID=@ID"
cmd.AddParam("@ID", ProductID)
rdr = cmd.ExecuteReader()
If rdr.Read() Then Populate(rdr)
rdr.Close()
rdr = Nothing
cmd = Nothing
End Sub
''' <summary>Insert new Product and get new Identity</summary>
Public Sub InsertItem()
If ID <> 0 Then Exit Sub
Dim cmd As New GenericCommand("SQLConn")
cmd.CommandText = "Insert Into Products (Name,Description,Price,Status) Values (@Name,@Description,@Price,@Status)"
cmd.AddParam("@Name", Name)
cmd.AddParam("@Description", Description)
cmd.AddParam("@Price", Price)
cmd.AddParam("@Status", Status)
ID = CInt(cmd.ExecuteIdentity())
cmd = Nothing
End Sub
''' <summary>Update Product</summary>
Public Sub UpdateItem()
If ID = 0 Then Exit Sub
Dim cmd As New GenericCommand("SQLConn")
cmd.CommandText = "Update Products set Name=@Name,Description=@Description,Price=@Price,Status=@Status where ID=@ID"
cmd.AddParam("@Name", Name)
cmd.AddParam("@Description", Description)
cmd.AddParam("@Price", Price)
cmd.AddParam("@Status", Status)
cmd.AddParam("@ID", ID)
cmd.ExecuteNonQuery()
cmd = Nothing
End Sub
''' <summary>Delete This product</summary>
Sub DeleteItem()
If ID = 0 Then Exit Sub
Dim cmd As New GenericCommand("SQLConn")
cmd.CommandText = "DELETE FROM Products WHERE ID=@ID"
cmd.AddParam("@ID", ID)
cmd.ExecuteNonQuery()
cmd = Nothing
Call Initialize()
End Sub
End Class
and The Entity Collection ClassPublic Class Products
Inherits CollectionBase
''' <summary>PageSize=0 means no paging</summary>
Public PageSize As Integer
''' <summary>PageIndex=0 means no paging</summary>
Public PageIndex As Integer
Private mPageCount As Integer
''' <summary>To Get Top records if larger than 0</summary>
Public TopRecords As Integer
Public Sub New()
Call Initialize()
PageSize = 0
PageIndex = 0
TopRecords = 0
End Sub
''' <summary>clear collection</summary>
Public Sub Initialize()
mPageCount = 0
List.Clear()
End Sub
''' <summary>Gets or sets the element at the specified zero-based index</summary>
Default Public Property Item(ByVal Index As Integer) As Product
Get
Return List.Item(Index)
End Get
Set(ByVal value As Product)
List.Item(Index) = value
End Set
End Property
''' <summary>Adds an object to the end of the Collection</summary>
Public Function Add(ByVal Obj As Product) As Integer
Return List.Add(Obj)
End Function
''' <summary>Select Products by Status, More Search Params can be added..</summary>
Public Sub SelectItems(Optional ByVal Status As Product.ProductStatus = Product.ProductStatus.None)
Call Initialize()
Dim Tbl As DataTable
Dim Top As String = ""
If TopRecords > 0 Then Top = "TOP " & TopRecords
If PageSize > 0 And PageIndex > 0 Then Top = "TOP " & (PageIndex * PageSize)
Dim Cmd As New GenericCommand("SQLConn")
Cmd.PageSize = PageSize
Cmd.PageIndex = PageIndex
Cmd.CountCommandText = "SELECT COUNT(*) FROM Products"
Cmd.CommandText = "SELECT " & Top & " * FROM Products"
If Status > 0 Then
Cmd.CountCommandText += " where Status=@Status"
Cmd.CommandText += " where Status=@Status"
Cmd.AddParam("@Status", Status)
End If
Tbl = Cmd.ExecuteDataTable("Products")
mPageCount = Cmd.PageCount
Dim p As Product
For Each Row As DataRow In Tbl.Rows
p = New Product
p.Populate(Row)
Add(p)
p = Nothing
Next
Tbl = Nothing
Cmd = Nothing
End Sub
Public ReadOnly Property PageCount() As Integer
Get
Return mPageCount
End Get
End Property
Protected Overrides Sub Finalize()
MyBase.Finalize()
End Sub
End Class
A Sample of usage to insert a product:Dim p As New Product
p.Name = "Product1"
p.Status = Product.ProductStatus.Active
p.InsertItem()
Response.Write(p.ID & ":" & p.Name)
p = Nothing
And a Sample of usage to select list of products with pagingDim ps As New Products
ps.PageSize = 10
ps.PageIndex = 2
ps.SelectItems(Product.ProductStatus.Active)
For i As Integer = 0 To ps.Count - 1
Dim p As Product = ps(i)
Response.Write(p.ID & ":" & p.Name & "<br>")
p = Nothing
Next
Response.Write("PageCount: " & ps.PageCount)
ps = Nothing
Also note that the paging method used in the collection class is DataSet paging method, to read more about paging methods comparison and tweaking: please check [Paging in ASP and ASP.Net].
A very important method of increasing data access efficiency is to cache frequently accessed data. like country list or top articles.. in situations where the data changes less frequently, such as once a day or every few hours, it is inefficient to hit the database for every page request.
As many would suggest to cache the HTML Output of a portion of a page(Fragment Caching) as a country select box or caching the entire page(Output Caching), there are cases where Data Caching will be more powerful. for example you may need to display that list of countries in more than one page/Portion with different look and/or use that list programmatically.
In a previous post [Data Access Layer for Classic ASP]: I proposed DAL Classes to encapsulate Data Access code for a certain Table/Object, and now I'll extend the DAL to handle Data caching too, please refer to my previous post before continuing..
I've modified the "Users" class to handle saving and reading from cache, which is saved to ADO XML format or Advanced Data Tablegram (ADTG) Binary format using "Save" method of ADO Recordset, Which has many advantages :
- You don't need to change much of your Data Access code to read from cache
- Opening the recordset from XML will act the same as Opening Database when you use Paging
- You can use The "Filter" method of ADO Recordset to filter records as you would do in a SQL Query
Here is the New ClassClass Users
'private Arr to hold users Objects
Private arr
'0 : Binary ADTG , 1: XML
Public CacheType 'as Byte
' Cache File Path
Public CachePath 'as String
''''' Paging Variables
Private mPageCount 'as Long
Public PageIndex 'as Long
Public PageSize 'as Long
'for getting top records if larget than 0
Public TopRecords 'as Long
'destroy arr
Private Sub Class_Terminate()
Erase arr
end Sub
Private Sub Class_Initialize()
Initialize
TopRecords = 0
PageIndex = 0
PageSize = 0
CacheType = 0
CachePath = ""
end Sub
'Initialize collection
Public Sub Initialize()
redim Arr(-1)
mPageCount = 0
End Sub
'return Upper Bound of objects array
Public Property get UpperBound()
UpperBound = UBound(arr)
End Property
'return object at index i
Public Property Get ElementAt(i)
set ElementAt = arr(i)
End Property
'return Upper Bound of objects array
Public Property Get PageCount()
PageCount = mPageCount
End Property
'Select users by Status ( u can add more search parameters or create another selectUsers method)
Public Sub SelectUsers(Status)
Initialize
Dim rs : Set rs = server.CreateObject("ADODB.Recordset")
'Do paging
If PageSize>0 And PageIndex>0 Then
RS.CursorLocation = adUseClient
RS.CacheSize = PageSize
RS.PageSize = PageSize
End If
'if open from XML
If CachePath>"" Then
If Status>0 Then rs.Filter = "Status="& Status
Rs.Open CachePath,,,,adCmdFile
'if Open from DB
Else
If TopRecords>0 Then
rs.MaxRecords = TopRecords
Top = " top "& TopRecords &" "
End If
Dim SQL : SQL= "SELECT "& Top &" * From users"
If Status>0 Then SQL = SQL & " where Status="& Status
rs.Open SQL , ConnStr, adOpenForwardOnly,adLockReadOnly,adCmdText
End If
' if paging : get page count
If Not rs.EOF And PageSize>0 And PageIndex>0 Then
RS.AbsolutePage = PageIndex
mPageCount = RS.PageCount
End If
Dim i : i=0
'(TopRecords=0 Or i<TopRecords) condition is needed to get correct Top N records when opening from Cache
' ,MaxRecords doesn't seem to work in that case
While Not rs.EOF And (TopRecords=0 Or i<TopRecords) And (PageSize=0 Or i<PageSize)
'loop until EOF or Paging Size reached
'create Object and set Values then add to collection
Dim u : Set u = New User
u.ID = CLng(Rs("ID"))
u.Name = Rs("Name")
u.Email = Rs("Email")
u.Password = Rs("Password")
u.LastLogin = cdate(rs("LastLogin"))
u.Status = cbyte(Rs("Status"))
ReDim Preserve arr(i)
Set arr(i) = u
set u = Nothing
rs.MoveNext
i=i+1
Wend
rs.Close
Set rs = Nothing
End Sub
' Open Recordset and Save it
Public Sub CacheUsers(Status)
If CachePath="" Then Exit Sub
Dim rs : Set rs = server.CreateObject("ADODB.Recordset")
Dim Top
If TopRecords>0 Then
rs.MaxRecords = TopRecords
Top = " top "& TopRecords &" "
End if
Dim SQL : SQL= "SELECT "& Top &" * From users"
If Status>0 Then SQL = SQL & " where Status="& Status
rs.Open SQL , ConnStr, adOpenForwardOnly,adLockReadOnly,adCmdText
Call SaveRS(rs)
rs.Close
Set rs = Nothing
End Sub
'Handle saving Recordset to Stream
Private Sub SaveRS(rs)
Const adTypeText = 2
Const adTypeBinary = 1
Const adSaveCreateOverWrite = 2
Const adPersistXML = 1
Const adPersistADTG = 0
Dim Stream : Set Stream = Server.CreateObject("ADODB.Stream")
If CacheType=1 Or CacheType=2 Then
Stream.Type = adTypeText
Stream.Open
rs.Save Stream, adPersistXML
Else
Stream.Type = adTypeBinary
Stream.Open
rs.Save Stream, adPersistADTG
end If
Application.Lock
Stream.SaveToFile CachePath,adSaveCreateOverWrite
Stream.Close
Application.UnLock
Set Stream = Nothing
End Sub
End Class
Sample Usage
1- Save Cache when Target Data is updatedDim aUsers : Set aUsers = New Users
aUsers.CacheType = 1 'XML cache
aUsers.CachePath = Server.MapPath("users.xml")
aUsers.TopRecords = Top
Call aUsers.CacheUsers(0)
Set aUsers = Nothing
2- Read CacheDim aUsers : Set aUsers = New Users
aUsers.CacheType = 1 'XML cache
aUsers.CachePath = Server.MapPath("users.xml")
aUsers.TopRecords = Top
Call aUsers.SelectUsers(0)
Dim i,aUser
For i=0 To aUsers.UpperBound
Set aUser = aUsers.ElementAt(i)
'do something
Set aUser = Nothing
Next
Set aUsers = Nothing
Time Comparison
I did a timing test to compare between reading from a SQL Server and from XML/Binary file, but Reading from a local SQL Server (on the same machine as IIS) will be always faster, so to get real world results i simulated a LAN/WAN Connection on SQL Server [How to? check: Simulating WAN Connection for SQL Server Performance Testing].
And to simulate concurrent connections on IIS and calculate timing i used the very neat [Microsoft Web Stress Tool] on a PC of P3.2GHZ, 1GB Ram, Windows XP, SQL Server 2005 Express.
| method / Records | 100 | 500 | 1000 | 3000 |
|---|---|---|---|---|
| Open WAN SQL Server | 559.25 | 614.80 | 980.85 | 2489.85 |
| Open LAN SQL Server | 276.70 | 436.00 | 777.60 | 2368.90 |
| Open XML Cache | 72.25 | 369.95 | 821.10 | 2469.31 |
| Open Binary Cache | 60.45 | 311.25 | 666.45 | 2062.20 |
As you can see reading less than 1000 records from XML Cache is slightly faster than reading from LAN SQL Server and time saving gets bigger when compared to WAN SQL server(reading >1000 records gets slower in XML due to loading&parsing overhead).
You can also notice that reading from Binary(ADTG) is faster than reading from XML and LAN/WAN SQL Server in all cases, and files created in binary are Approx. 50% smaller than the equivalent XML files.
Web developer, jQuery plugin author, social media fan and Technology Blogger.