There are so many different hosting companies available right now that knowing which one is the best for your requirements isn’t easy. ASP.NET programmers want to be certain that they are working with a host that is able to handle the more complex requirements. As you would expect with anything that is developed by Microsoft, ASP.NET works best with a Windows-based hosting solution. However, your usual list of the so called ‘best web hosts’ are not necessarily going to be the right solution for ASP.NET.
Showing posts with label ASP.Net. Show all posts
Showing posts with label ASP.Net. Show all posts
Kendo UI Grid is a very popular widget for visualizing data tables.
And in this example will demonstrate how to get CRUD(create/read/update/destroy) operations to work with ASP.Net(C#) Web Method.
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].
After I posted a previous article comparing between many methods of Paging in ASP and ASP.Net, I wanted to focus on how to tweak paging using DataSet as it is unpopular tip and it saves lots of time to return records in closer pages which usually gets more hits than further pages...
The method DbDataAdapter.Fill(dataSet As DataSet, startRecord As Integer, maxRecords As Integer, srcTable As String) specifies the start record and number of records to fill the DataSet with, and of course you need to run a count query first to calculate PageCount'Count Query
Cmd.CommandText = "Select count(ID) from Users where.."
Count = CInt(Cmd.ExecuteScalar())
PageCount = Math.Ceiling(Count / PageSize)
'Select Query
cmd.CommandText = "Select Top " & (PageIndex * PageSize) & " * from Users where.. Order By.."
Dim DS As New DataSet
Dim DA As DbDataAdapter = PFactory.CreateDataAdapter()
DA.SelectCommand = Cmd
DA.Fill(DS, (PageIndex - 1) * PageSize, PageSize, SrcTable)
Dim DT As New DataTable= DS.Tables(SrcTable)
DA = Nothing
DS = nothing
For Each Row As DataRow In DT.Rows
'Do Something
Next
DT = Nothing
MSDN @ [ADO.Net & Paging Through a Query Result ] Says:
This might not be the best choice for paging through large query results because, although the DataAdapter fills the target DataTable or DataSet with only the requested records, the resources to return the entire query are still used ..That is true, At first testing the time results for this method was bad even for closer pages..
Remember that the database server returns the entire query results even though only one page of records is added to the DataSet.
To tweak that: I added a top clause in the select query to select Top(PageSize * PageIndex), so if we want the 2nd page where page size is 10: the database returns only 20 records, and the DataSet is filled with the 2nd 10 records.. instead of letting the database to return the entire table. and this tweak makes DataSet timing chart similar to 'Top Clause' SQL paging method [ more on that @ Paging in ASP and ASP.Net]
Although I posted a long article comparing between many methods of Paging in ASP and ASP.Net, I wanted to highlight more on this method of paging using DataReader cause you won't see out there very often.. although it is simpler than SQL Paging methods and it is slightly faster than usual DataSet paging method..
It was inspired by the article [How do I page through a Recordset?] which Shows & compares between paging methods for classic ASP.
The function do paging by fetching the Top PageSize * PageIndex Records and then it loops to move the start record of current Page index..' Paging Params (1-based)
Dim PageSize As Integer = 10
Dim PageIndex As Integer = 3
' Create+Open Conn on SQL Server 2005 Express
Dim Conn As New SqlConnection("Data Source=127.0.0.1\sqlexpress,1433;Initial Catalog=DB;Persist Security Info=False;User ID=user;Password=pass;Network Library=dbmssocn")
Conn.Open()
'Create SQL Command
Dim Cmd As New SqlCommand()
Cmd.Connection = Conn
'Execute Count Query To calculate PageCount
Cmd.CommandText = "SELECT COUNT(*) FROM users where status=1"
Dim Count As Integer = Cmd.ExecuteScalar()
Dim PageCount As Integer = Math.Ceiling(Count / PageSize)
'Query Records
'If you forget to add 'Top PageIndex * PageSize', Performance degrades badly in large tables
Cmd.CommandText = "SELECT Top " & (PageIndex * PageSize) & " * FROM users where status=1"
Dim rdr As SqlDataReader = Cmd.ExecuteReader()
'Move Reader to start record
For I As Integer = 0 To ((PageIndex - 1) * PageSize) - 1
If Not rdr.Read Then Exit For
Next
'Loop the desired Records
'if you didn't Add Top clause you will notice that all records in table are fetched!!
While rdr.Read()
'Do Something
End While
rdr.Close()
rdr = Nothing
Conn.Close()
Conn = Nothing
Cmd = Nothing
*If you forget to add Top (PageIndex * PageSize), Performance degrades badly in large tables
I needed to do some testing on SQL Server on my PC, but response was of course very fast, so i needed to simulate WAN connection to get some real world results..
i found a good article [How To: Simulate WAN Connections in Your Own Test Lab, For Free!] by Michel Roth
Since WAN main characteristics are : limited in bandwidth and high in latency, we can use one of two software:
1- NetLimiter Pro (commercial-28-day evaluation period)
it can limit the bandwidth per process. You can use this ability in two ways:
a. You can install it on the Server (ex:SQL Server) and limit the incoming bandwidth.
b. You can install it on the Client machine(ex:IIS) and limit the outgoing bandwidth for the client(Preferred).
2. TMnetSim Network Simulator (free!)
a *free* tool to allow you to simulate network latency & packet loss!,You can install it on the client or on the server. I gonna use the tool on the Server.
Here are the steps to setup that tool for testing SQL Server 2005 Express on your local PC:
- Download zip file and run executable
- SQL Server default port is 1433 so:
Under 'Outbound Connection' >> enter IP: 127.0.0.1, Port : 1433
Under 'Inbound Connection' >> enter port: 1422 (any other port!)
Under 'inbound-->outbound policies' >> Delay Type : Gausian, Delay Base:250 , Daley Jitter : 25 - click "Start"
- Setup your Connection String on ASP
Provider=SQLOLEDB; Data Source=127.0.0.1\sqlexpress,1422;Initial Catalog=db;User Id=user;Password=pass;
or ASP.Net :<connectionStrings>
<add name="SQLConn" connectionString="Data Source=127.0.0.1\sqlexpress,1422;Initial Catalog=DB;Persist Security Info=False;User ID=user;Password=pass;Network Library=dbmssocn" providerName="System.Data.SqlClient"/>
</connectionStrings> - If things running ok, you should see connection information (Latency- Bandwidth- Packet Loss)
Most applications use data access code to access the underlying data store to perform basic data queries such as Select, Insert, Update and Delete.
This is a quick and lightweight data access class that you can extend later.. This class will encapsulate data access code for SQL Server and MS Access Databases and Supports paging using the methods discussed in [Paging in ASP and ASP.Net]
We will use the System.data.Common Namespace and The most important one is the DbProviderFactory Class. This abstract factory class accepts a provider name and in return provides us with objects of all necessary ADO.NET classes.
We start by creating a new Command Class. that will use DbCommand and implicitly use DbConnection. If you've used ADO before you are probably not happy with those many objects you have to deal with in ADO.Net just to query some records.. So this way you would write less & generic code.
First,your ConnectionString should be saved in web.config(for web Apps..), as follows:<connectionStrings>
<add name="StrConn" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\data.mdb;Persist Security Info=True" providerName="System.Data.OleDb"/>
<add name="StrConn2" connectionString="Data Source=127.0.0.1\sqlexpress,1433;Initial Catalog=DB;Persist Security Info=False;User ID=user;Password=pass;Network Library=dbmssocn" providerName="System.Data.SqlClient"/>
</connectionStrings>
Which contains 2 connection strings; one for Access that is saved in "App_Data" folder and second one for connecting to SQL Server 2005 Express(on local machine & default port) using the Network Library , and as you can see each one defines its ProviderName which is needed to tell DbProviderFactory what Data access object it should create..Imports System.Data
Imports System.Data.Common
Imports System.Configuration
Public Class GenericCommand
Private ConnStringID As String = ""
Private ConnString As String = ""
Private Provider As String = ""
Private PFactory As DbProviderFactory
Private Conn As DbConnection
Private Cmd As DbCommand
''' <summary>Count SQL Query used with ExecuteDataSet or ExecuteDateReader to calculate PageCount</summary>
Public CountCommandText As String = ""
Private mPageCount As Integer = 0
Public PageSize As Integer = 0
''' <summary>1-based Page Index</summary>
Public PageIndex As Integer = 0
Public Sub New(ByVal Config_ConnStringID As String, Optional ByVal SQL As String = "")
ConnStringID = Config_ConnStringID
Init()
CommandText = SQL
End Sub
Public Sub Init()
If ConnString = "" Then
ConnString = ConfigurationManager.ConnectionStrings(ConnStringID).ConnectionString
Provider = ConfigurationManager.ConnectionStrings(ConnStringID).ProviderName
End If
If Conn Is Nothing Then
PFactory = DbProviderFactories.GetFactory(Provider)
Conn = PFactory.CreateConnection
Conn.ConnectionString = ConnString
Cmd = PFactory.CreateCommand
Cmd.Connection = Conn
End If
End Sub
Public ReadOnly Property PageCount() As Integer
Get
Return mPageCount
End Get
End Property
Public Property CommandType() As Data.CommandType
Get
Return Cmd.CommandType
End Get
Set(ByVal value As Data.CommandType)
Cmd.CommandType = value
End Set
End Property
Public Property CommandText() As String
Get
Return Cmd.CommandText
End Get
Set(ByVal SQL As String)
Cmd.CommandText = SQL
End Set
End Property
Public Sub AddParam(ByVal Name As String, ByVal Value As Object)
Dim p As DbParameter = PFactory.CreateParameter
p.ParameterName = Name
p.Value = Value
Cmd.Parameters.Add(p)
p = Nothing
End Sub
Public Sub AddParam(ByVal Name As String, ByVal Dir As Data.ParameterDirection, ByVal DType As DbType, Optional ByVal Value As Object = Nothing)
Dim p As DbParameter = PFactory.CreateParameter
p.ParameterName = Name
p.Value = Value
If DType > -1 Then p.DbType = DType
p.Direction = Dir
Cmd.Parameters.Add(p)
p = Nothing
End Sub
Public Function ParamValue(ByVal Name As String) As Object
Dim p As DbParameter
p = Cmd.Parameters(Name)
If p Is Nothing Then
Return Nothing
Else
Return p.Value
End If
End Function
Public Sub ClearParams()
Cmd.Parameters.Clear()
End Sub
''' <summary>Executes Command and return DataSet
''' ,Does Paging If PageSize and PageCount is set
''' and Calculate PageCount if CountCommandText is set</summary>
''' <param name="SrcTable">Table name</param>
Public Function ExecuteDataSet(ByVal SrcTable As String) As DataSet
mPageCount = 0
Conn.Open()
ExecuteCount()
Dim DS As New DataSet
Dim DA As DbDataAdapter = PFactory.CreateDataAdapter()
DA.SelectCommand = Cmd
'Do Paging
If PageSize > 0 And PageIndex > 0 Then
DA.Fill(DS, (PageIndex - 1) * PageSize, PageSize, SrcTable)
Else
DA.Fill(DS, SrcTable)
End If
DA.Dispose()
DA = Nothing
Conn.Close()
Return DS
End Function
''' <summary>Uses ExecuteDataSet to execute the command and return only the DataTable</summary>
''' <param name="SrcTable">Table name</param>
Public Function ExecuteDataTable(ByVal SrcTable As String) As DataTable
Dim DS As DataSet = ExecuteDataSet(SrcTable)
Dim DT As DataTable
DT = DS.Tables(SrcTable)
DS = Nothing
Return DT
End Function
Public Enum ReaderPaging
''' <summary>Normal command Execution</summary>
None = 0
''' <summary>Moves reader to start record based on PageSize and PageIndex</summary>
MovePaging = 1
''' <summary>Fix for Top Clause Paging: If it is Last Page and Records Count is less than Pagesize</summary>
TopClausePaging = 2
End Enum
''' <summary>Executes Command and return DataReader
''' ,does paging by moving to start record if MovePaging is selected
''' and/or Calculate PageCount if CountCommandText,PageSize,PageIndex are set </summary>
''' <param name="Paging"></param>
Public Function ExecuteReader(Optional ByVal Paging As ReaderPaging = ReaderPaging.None) As DbDataReader
mPageCount = 0
Conn.Open()
Dim count As Integer = ExecuteCount()
Dim Reader As DbDataReader, i As Integer = 0
Reader = Cmd.ExecuteReader(CommandBehavior.CloseConnection)
'Do Paging
If PageSize > 0 And PageIndex > 0 Then
If Paging = ReaderPaging.MovePaging Then
For i = 0 To ((PageIndex - 1) * PageSize) - 1
If Not Reader.Read Then Exit For
Next
ElseIf Paging = ReaderPaging.TopClausePaging Then
If PageSize > 1 And PageCount > 1 And PageIndex = PageCount And (count Mod PageSize <> 0) Then
For i = 1 To PageSize - (count Mod PageSize)
If Not Reader.Read Then Exit For
Next
End If
End If
End If
Return Reader
End Function
''' <summary>execute count command if paging params are set and calculate PageCount</summary>
Private Function ExecuteCount() As Integer
If CountCommandText > "" And PageSize > 0 And PageIndex > 0 Then
Dim Tmp As String = Cmd.CommandText
Cmd.CommandText = CountCommandText
Dim Count As Integer = CInt(Cmd.ExecuteScalar())
mPageCount = Math.Ceiling(Count / PageSize)
Cmd.CommandText = Tmp
Return Count
End If
Return 0
End Function
''' <summary>Executes Non Query,that's it!</summary>
Public Function ExecuteNonQuery() As Integer
Dim rows As Integer
Conn.Open()
rows = Cmd.ExecuteNonQuery()
Conn.Close()
Return rows
End Function
''' <summary>Executes an Insert query and return Identity column by appending ";Select Scope_Identity()" for SQL Server or executing another command for MS Access</summary>
Public Function ExecuteIdentity() As Object
Dim ID As Object
Conn.Open()
If Provider.ToLower = "System.Data.SqlClient".ToLower Then
Cmd.CommandText = Cmd.CommandText & ";Select Scope_Identity()"
ID = Cmd.ExecuteScalar()
Else
Cmd.ExecuteNonQuery()
Cmd.CommandText = "Select @@Identity"
ID = Cmd.ExecuteScalar()
End If
Conn.Close()
Return ID
End Function
''' <summary>Executes scalar</summary>
''' <returns>Object</returns>
Public Function ExecuteScalar() As Object
Dim o As Object
Conn.Open()
o = Cmd.ExecuteScalar()
Conn.Close()
Return o
End Function
Protected Overrides Sub Finalize()
If Conn.State = ConnectionState.Open Then Conn.Close()
Conn = Nothing
Cmd = Nothing
MyBase.Finalize()
End Sub
End Class
Example of usage for Insert queryDim Cmd As New GenericCommand("StrConn2")
Cmd.CommandText = "insert into Users(Name,Email) values (@Name,@Email)"
Cmd.AddParam("@Name", "Some Name")
Cmd.AddParam("@Email", "Some Email")
Dim UserID as integer = Cmd.ExecuteIdentity()
Cmd = Nothing
And example of usage for Select Query with paging using DataSet methodDim Cmd As New GenericCommand("StrConn2")
Cmd.PageSize = 10
Cmd.PageIndex = 3
'Count Query
Cmd.CountCommandText = "SELECT COUNT(*) From Users where Status>=@Status"
'Select Query ; if you don't use Top Cmd.PageSize * Cmd.PageIndex : performance degrades badly in large tables
Cmd.CommandText = "SELECT Top " & (Cmd.PageSize * Cmd.PageIndex) & " * FROM Users where Status>=@Status"
Cmd.AddParam("@Status", 1)
Dim Tbl As DataTable = Cmd.ExecuteDataTable("Users")
Dim PageCount As Integer = Cmd.PageCount
For Each Row As DataRow In Tbl.Rows
'Do Something
Next
Tbl = Nothing
Cmd = Nothing
Read more on the subject @ MSDN [Writing Generic Data Access Code in ASP.NET 2.0 and ADO.NET 2.0]
Subscribe to:
Posts
(
Atom
)
Web developer, jQuery plugin author, social media fan and Technology Blogger.