skip to main | skip to sidebar

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 Class
Imports 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 Class
Public 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 paging
Dim 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 ..
Remember that the database server returns the entire query results even though only one page of records is added to the DataSet.
That is true, At first testing the time results for this method was bad even for closer pages..

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

Optimizing web pages for Google and other search engines is not enough. A successful concept for online marketing your blog or site has to be regarded in a more comprehensive way and must include the community [The Wisdom of Crowds!].

Web 2.0 is part of the community and vice versa. Social bookmarking is part of it, too. so you should make it as easy as possible for your visitors to bookmark your site. and gathering information on the best social bookmarking sites, their icons, submission URLs, and ranking is quite a pain.

This is a simple JavaScript that you can paste to any web page or blog to add the top 20 ranked social bookmarking sites as advised in [List of social bookmarking sites] and It should look like Icons on the right side -->>

And as you might know: loading many small images can slow down your page loading, as browser opens a new connection for each image to download, a well-known better approach is to put all this related images in one image, and use CSS to set background-position..

Here is the JavaScript to paste
<script type='text/javascript'>
function SocialBookmarks(){
   var holder = document.getElementById("SocialButtonsPlace");
   if (!holder) return;
  
   var title = encodeURIComponent(document.title);
   var url = encodeURIComponent(location.href);
   /* these are the social bookmaking sites' icons ,ordered by rank, you can remove a line
   but don't change the 'pos' so icon would show up correctly */
   var socials = [
   {'name':'Yahoo','pos':-16,'url':'http://myweb2.search.yahoo.com/myresults/bookmarklet?t='+title+'&amp;u='+url},
   {'name':'Google','pos':-32,'url':'http://www.google.com/bookmarks/mark?op=edit&amp;bkmk='+url+'&amp;title='+title},
   {'name':'Live','pos':-48,'url':'https://favorites.live.com/quickadd.aspx?url='+url+'&amp;title='+title},
   {'name':'Facebook','pos':-64,'url':'http://www.facebook.com/sharer.php?u='+url+'&amp;t='+title},
   {'name':'Digg','pos':-80,'url':'http://digg.com/submit?phase=2&amp;url='+url+'&amp;title='+title},
   {'name':'Ask','pos':-96,'url':'http://myjeeves.ask.com/mysearch/BookmarkIt?v=1.2&amp;t=webpages&amp;url='+url+'&amp;title='+title},
   {'name':'Technorati','pos':-112,'url':'http://technorati.com/faves?sub=addfavbtn&amp;add='+url+'&amp;title='+title},
   {'name':'Delicious','pos':-128,'url':'http://del.icio.us/post?url='+url+'&amp;title='+title},
   {'name':'StumbleUpon','pos':-144,'url':'http://www.stumbleupon.com/submit?url='+url+'&amp;title='+title},
   {'name':'Squidoo','pos':-160,'url':'http://www.squidoo.com/lensmaster/bookmark?'+url},
   {'name':'Propeller','pos':-176,'url':'http://www.propeller.com/submit/?U='+url+'&amp;T='+title+'&amp;C='+title},
   {'name':'Slashdot','pos':-192,'url':'http://slashdot.org/bookmark.pl?url='+url+'&amp;title='+title},
   {'name':'Reddit','pos':-208,'url':'http://reddit.com/submit?url='+url+'&amp;title='+title},
   {'name':'Fark','pos':-224,'url':'http://cgi.fark.com/cgi/fark/submit.pl?new_url='+url+'&amp;new_comment='+title+'&amp;linktype='},
   {'name':'Newsvine','pos':-240,'url':'http://www.newsvine.com/_wine/save?u='+url+'&amp;h='+title},
   {'name':'Furl','pos':-256,'url':'http://www.furl.net/storeIt.jsp?t='+title+'&amp;u='+url},
   {'name':'Blinklist','pos':-272,'url':'http://www.blinklist.com/index.php?Action=Blink/addblink.php&amp;Url='+url+'&amp;Title='+title},
   {'name':'dzone','pos':-288,'url':'http://www.dzone.com/links/add.html?url='+url+'&amp;title='+title},
   {'name':'Magnolia','pos':-304,'url':'http://ma.gnolia.com/bookmarklet/add?url='+url+'&amp;title='+title},
   {'name':'SWiK','pos':-320,'url':'http://stories.swik.net/?submitUrl&amp;url='+url}
   ];
  
   for(var i=0; i<socials.length; i++)
      holder.innerHTML = holder.innerHTML + '<li><A class=\'SocialButton\' style=\'background-position:0 '+socials[i].pos+'px\' href=\''+socials[i].url+'\' title=\''+socials[i].name+'\' target=\'_blank\'>&nbsp;</A></li>';
}

/* This call can be placed in body onload event */
SocialBookmarks();
</script>


And place this CSS code in the HTML Head or in an included CSS file
#SocialButtonsPlace {
list-style:none;
/* must do - Browsers WORLD WAR!*/
margin:0; padding: 0;
}
#SocialButtonsPlace Li{
float:left;
}
.SocialButton {
display:block;
height:16px;
width:16px;
line-height:16px;
margin:2px;
text-decoration:none;
background-image:url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEikWuhPxjyWXhmT9ax0HQ-a-A1tkGL_hUK5WnSPdXu2uN9C1YsuEJMb5YyVD3rXtuMX4W3bRQDMMT5pWzXxBe5ezHun2B8uVxJzud3WwYjhWrScnKfoB8e3hT4kt7vqlxJzyzhFqLxcQf5_/s400/bar.gif);
}


And create a place holder UL where the icons will show up
<UL id="SocialButtonsPlace"></UL>

Don't forget to save the icons [Bar Image] and upload it some where on your site and change its location in CSS.

You may also use an Image-Map to display the bar image horizontally or vertically and define the clickable regions..

that's it.. enjoy!

If you are interested in SEO or web search or marketing your blog, you should check that great article
[Can Social Bookmarking Improve Web Search] by Paul Heymann , One look on the author photo and you will want to read it..

Or watch the video

Can Social Bookmarks Improve Web Search?
Presentation (February 12th, 2008)
Conference on Web Search and Data Mining (WSDM2008)

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 Class
Class 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 updated
Dim 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 Cache
Dim 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.

it's all about Regular expressions; Regular expressions are a very powerful tool for performing pattern matches in strings. You can perform complex tasks that once required lengthy procedures with just a few lines of code using regular expressions...

in this example,i made a simple Code Beautifier and formatter for VB/VBScript/VB.net using JavaScript and CSS.

in this syntax highlighter There are patterns for matching Comments lines,Quoted strings,Escape HTML tags,and Language Keywords..

To extend this to format C# for example; all you need to change is the keywords of that programming language at the line
s = keywords_Beautifier("New|Class|..",s);
where you should place language keywords as one string separated by | as the first parameter

Go ahead try it your self




Output will look like this



Copy & paste this


You will need to include this css
<style>
b.KW {
color:blue;
}
b.Cm , b.Cm b.KW , b.Cm b.QS {
color:green;
font-weight:lighter;
}
b.QS, b.QS b.KW {
color:maroon;
font-weight:lighter;
}
</style>


Code Beautifier And Formatter Source Code

<SCRIPT LANGUAGE=javascript>
function Code_Beautifier(){
var a = document.getElementById("codearea");
if (!a) return;
var s = a.value;
//escape HTML Tags <>
s= s.replace(/</g ,"&lt;");
s= s.replace(/>/g ,"&gt;");
//next will use regular expressions patterns, and surround matches with <b ClssS="[Css class]"></b>
// but note that 'ClssS' is misspelled so it don't get changed by keywords pattern
//Beautify a quoted string
s= s.replace(/(\"[^\"]*\")/gi ,'<b ClssS="qs">$1</b>');
//Beautify Comments
s = Comments_Beautifier(s);
//escape lines
s= s.replace(/\n/g ,"<br />");
//escape spaces
s= s.replace(/\s\s/gi ,"&nbsp;&nbsp;");
//Beautify keywords
s = keywords_Beautifier("New|Class|Shared|Protected|Friend|byval|byREF|Optional|RETURN|GET|Property|Erase|LBound|UBound|Let|Set|Rem|Const|Empty|Nothing|Null|True|False|Control|Do|Loop|For|Next|For|If|Then|Else|select|While|Wend|end|Abs|Asc|AscB|AscW|Chr|ChrB|ChrW|CBool|CByte|CDate|CDbl|Cint|CLng|CSng|CStr|DateSerial|DateValue|Hex|Oct|Fix|Int|Sgn|TimeSerial|TimeValue|date|Time|DateSerial|DateValue|Day|Month|Weekday|Year|Hour|Minute|Second|Now|TimeSerial|TimeValue|Dim|Private|Public|ReDim|Sub|On|Err|InputBox|MsgBox|Atn|Cos|Sin|Tan|Exp|Log|Sqr|Randomize|Rnd|Mod|Is|And|Or|Xor|Eqv|Imp|CreateObject|IsObject|option|Call|FUNCTION|Sub|Instr|InStrB|Len|LenB|Lcase|Ucase|Left|LeftB|Mid|MidB|Right|RightB|Space|StrComp|string|Ltrim|Rtrim|Trim|IsArray|IsDate|IsEmpty|IsNull|IsNumeric|IsObject|VarType|ERROR",s);
s = CorrectCssClass(s);
var o = document.getElementById("output") ;
if (o) o.innerHTML = s;
var oa = document.getElementById("outarea");
if (oa) oa.value=s;
}
function keywords_Beautifier(keys,s) {
var rx = new RegExp("(\\b)("+ keys +")(\\b)", "gi")
return s.replace(rx ,'$1<b ClssS="Kw">$2</b>$3');
}
function Comments_Beautifier(s) {
return s.replace(/\'([^\n]+)\n/gi ,'<b ClssS="Cm">\'$1</b>\n');
}
function CorrectCssClass(s) {
return s.replace(/ClssS/g ,'class');
}
</SCRIPT>


Some fine JavaScript Regular Expressions References
Using Regular Expressions with JavaScript and ActionScript
javascript regual expressions
JavaScript RegExp Object Reference

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:

  1. Download zip file and run executable
  2. 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
  3. click "Start"
  4. 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>

  5. If things running ok, you should see connection information (Latency- Bandwidth- Packet Loss)

Data Access layer(DAL) is is a layer of code which provides simplified access to data stored in persistent storage of some kind (DBMS, XML,..)

DAL would return a reference to an object [in terms of object-oriented programming(OOP)] with its attributes instead of a recordset of fields from a database table. This allows presentation layer to be created with a higher level of abstraction, and make project database independent.

So, instead of using SQL commands such as insert, delete, and update to access a specific table in a database, a method would be called from inside the class, which would return an object containing the requested values. instead of making a query into a database to fetch all users from several tables the application can call a single method from a DAL which encapsulate those database calls.

As an example of DAL, we'll use a simple Table of 'Users', here is the SQL Create script:
CREATE TABLE [dbo].[Users](
[ID] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
[Name] [varchar](100) NOT NULL DEFAULT (''),
[Email] [varchar](100) NOT NULL DEFAULT (''),
[Password] [varchar](20) NOT NULL DEFAULT (''),
[LastLogin] [smalldatetime] NOT NULL DEFAULT ('1/1/1900'),
[Status] [tinyint] NOT NULL DEFAULT (0)
)


We will create 2 classes for users table, Object Class and Object Collection Class with support of paging

Object Class
'few needed ADO Constants, instead of including adovbs.asp
Const adCmdText = &H0001
Const adCmdTableDirect = &H0200
Const adCmdFile = &H0100
Const adOpenForwardOnly = 0
Const adOpenKeyset = 1
Const adLockReadOnly = 1
Const adLockOptimistic = 3
Const adExecuteNoRecords = &H00000080
Const adUseClient = 3

''''''''''''''' Object class '''''''''''''''
Class User
  ' Object Attributes / Table Fields
  Public ID 'as Long
  Public Name 'as String
  Public Email 'as String
  Public Password 'as Strng
  Public LastLogin 'as Date
  Public Status 'as byte

  Private Sub Class_Initialize()
    Initialize
  End Sub

  'default values
  Public Sub Initialize()
    ID=0
    Name=""
    Email = ""
    Password = ""
    LastLogin = Now()
    Status=0
  End Sub

  'Select user by ID
  Public Sub SelectUser(UserID)
    Initialize

    if UserID=0 then exit Sub

    Dim rs : Set rs = server.CreateObject("ADODB.Recordset")
    Dim SQL : SQL= "SELECT * From users where ID="& UserID
    rs.Open SQL, ConnStr ,adOpenForwardOnly, adLockReadOnly,adCmdText
    If Not rs.EOF Then
      ID = CLng(Rs("ID"))
      Name = Rs("Name")
      Email = Rs("Email")
      Password = Rs("Password")
      LastLogin = Cdate(rs("LastLogin"))
      Status = cbyte(Rs("Status"))
    End If
    rs.Close
    Set rs = Nothing
  End Sub

  'Insert New User and get new ID
  Public Sub InsertUser()
    if ID<>0 then exit Sub

    Dim rs : Set rs = server.CreateObject("ADODB.Recordset")
    rs.Open "Users", ConnStr ,adOpenKeyset,adLockOptimistic,adCmdTableDirect
    rs.AddNew
    Rs("Name") = Name
    Rs("Email") = Email
    Rs("Password") = Password
    rs("LastLogin") = LastLogin
    Rs("Status") = Status

    rs.update
    ID = CLng(Rs("ID"))
    Rs.Close
    Set rs = Nothing
  End Sub

  'Update User
  Public Sub UpdateUser()
    if ID=0 then exit Sub

    Dim rs : Set rs = server.CreateObject("ADODB.Recordset")
    Dim SQL : SQL= "SELECT * From users where ID="& ID
    rs.Open SQL, ConnStr ,adOpenForwardOnly,adLockOptimistic,adCmdText
    If Not rs.EOF then
      Rs("Name") = Name
      Rs("Email") = Email
      Rs("Password") = Password
      rs("LastLogin") = LastLogin
      Rs("Status") = Status
      rs.update
    End If
    Rs.Close
    Set rs = Nothing
  End Sub

  'delete user
  Public Sub DeleteUser()
    if ID=0 then exit Sub

    Dim conn : Set conn = server.CreateObject("ADODB.Connection")
    conn.Execute "Delete From users where ID="& ID, ,adExecuteNoRecords
    Conn.Close
    Set Conn = Nothing

    Initialize
  End Sub
End Class


Object Collection Class
Class Users
  'private Array to hold users Objects
  Private arr
  ''''' Paging Variables
  Private mPageCount 'as Long
  Public PageIndex 'as Long
  Public PageSize 'as Long

  'for getting top records if larger 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
  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

  Public Property Get PageCount()
    PageCount = mPageCount
  End Property

  'Select users by Status (you can add more search parameters or create another select 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 TopRecords>0 Then rs.MaxRecords = TopRecords

    Dim Top
    If TopRecords>0 Then Top = " top "& TopRecords &" "
    Dim SQL : SQL= "SELECT "& Top &" * From users"
    If Status>0 Then SQL = SQL & " where Status="& Status

    rs.Open SQL, ConnStr, adOpenForwardOnly,adLockReadOnly,adCmdText
    ' if paging : move to PageIndex and 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
    'loop until EOF or Paging Size reached
    while Not rs.EOF And (PageSize=0 Or i<PageSize)
      '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
End Class


Sample Usage
Dim Usrs : Set Usrs = New Users
Usrs.PageIndex = 1
Usrs.PageSize = 10
Call Usrs.SelectUsers(0)

Dim i,Usr
For i=0 To Usrs.UpperBound
  Set Usr = Usrs.ElementAt(i)
  'Do something
  Set Usr = Nothing
Next
Set Usrs = Nothing


Note that the paging method used in the collection class is the usual ADO method,since it is not the optimal or the fastest,Please check my article on [Paging in ASP and ASP.Net] for better paging methods..

This Data access layer can be later extended to be also a Business Logic Layer(BLL) by enforcing business rules that may apply (permissions, validations, formatting..)

Also if you have a lot of DAL classes, DAL can be moved to ActiveX DLL (Com Object) to improve performance..[25+ ASP Tips to Improve Performance and Style]

When error occurs while you compile or run an ASP page, IIS generates a 500;100 error and executes a Server.Transfer() method to pass control to the currently defined custom error page. (By default this page is "WinDir/help/iishelp/common/500-100.asp")

When control is passed to the custom error page, the Server.GetLastError() method can be used to obtain detailed information regarding the error that occurred. for more info [Creating Custom ASP Error Pages]

While you can setup a custom ASP error page for some website as described in the MSDN article mentioned before; why not customize the default IIS error 500 page [WinDir/help/iishelp/common/500-100.asp] directly,To present users with a friendly error message and do server-wide error logging and/or send it to IT Email..

This is modification for the 500-100.asp to display an error message and send email to IT:
<%@ language="VBScript" %>
<% Option Explicit

If Response.Buffer Then
  Response.Clear
  Response.Status = "500 Internal Server Error"
  Response.ContentType = "text/html"
  Response.Expires = 0
End If

Call SendError()
%>
<html>
<head>
  <style>
  Body,TD  {FONT-SIZE: 11px; FONT-FAMILY: Tahoma }
  </style>
  <META NAME="ROBOTS" CONTENT="NOINDEX">
  <title>Unexpected Error</title>
  <META HTTP-EQUIV="Content-Type" Content="text-html; charset=Windows-1252">
</head>
<body>
  <h3 align="center">Unexpectd Error</h3>
  An unexpected error has occurred. We apologize for the inconvenience.
  <p>Please try the following:</p>
  <ul>
   <li>Click the <a href="javascript:location.reload()">Refresh</a> button, or try again later.</li>
   <li>Open the home page, and then look for links to the information you want. </li>
  </ul>
</body>
</html>
<%
Sub SendError()
  Dim er : Set er = Server.GetLastError      
  Dim msg : msg = "Error 500 on "& Now &"<br>"
  msg = msg & "category:"& er.Category & "<br>"
  If er.ASPCode>"" Then msg = msg & "aspcode:"& er.ASPCode & "<br>"
  msg = msg & "number:"& hex(er.Number) & "<br>"
  
  If er.ASPDescription > "" Then
    msg = msg & "description:"& er.ASPDescription & "<br>"
  ElseIf er.Description >"" Then
    msg = msg & "description:"& er.Description & "<br>"
  end If
  
  If er.Source > "" Then msg = msg & "source:"& er.Source & "<br>"
  If er.File <> "?" Then
    msg = msg & "file:"& er.File & "<br>"
    If er.Line > 0 Then msg = msg & "line:"& er.Line & "<br>"
    If er.Column > 0 Then msg = msg & "column:"& er.Column & "<br>"
  End If
  
  msg = msg & "USER_AGENT:"& Request.ServerVariables("HTTP_USER_AGENT") & "<br>"
  msg = msg & "REQUEST_METHOD:"& Request.ServerVariables("REQUEST_METHOD") & "<br>"
  msg = msg & "SCRIPT_NAME:"& Request.ServerVariables("SCRIPT_NAME") & "<br>"
  if Request.QueryString>""Then msg = msg & "QueryString:"& Request.QueryString & "<br>"
  if Request.Form>""Then msg = msg & "Post:"& Left(Request.Form,500) & "<br>"
  
  Call SendEmail(IT_Email,IT_Email,"Error 500",msg)
End Sub
%>


* don't forget to disable "Show friendly HTTP error messages" In IE under Tools/Internet Options/Advanced

VMG Files are Plain text format used for storing SMS text messages on Nokia cell phones; contains header information such as the sender, date, and time, as well as the actual message encoded as UTF-16LE..

at some Application i needed a way to Export SMS Messages to be able to Copy it to my Nokia phone, of Course to drafts folders.. since you can't copy it directly to outbox..

well, here is a function to save an sms to a .VMG file using ADODB.Stream
''''' Save SMS as VMG file
Sub SaveSMS(FilePath,Receipents,SMS,SMSTime)
  Const adTypeText = 2
  Const adSaveCreateOverWrite = 2

  Dim stream : Set stream = Server.CreateObject("ADODB.stream")
  stream.Type = adTypeText
  stream.Charset = "UTF-16LE"
  Stream.Open

  stream.WriteText "BEGIN:VMSG" & vbCrLf & _
           "VERSION:1.1" & vbCrLf & _
           "X-IRMC-STATUS:DRAFT" & vbCrLf & _
           "X-IRMC-BOX:INBOX" & vbCrLf & _
           "BEGIN:VCARD" & vbCrLf & _
           "VERSION:2.1" & vbCrLf & _
           "N:" & vbCrLf & _
           "TEL:" & vbCrLf & _
           "END:VCARD" & vbCrLf
            
  Dim i
  For i = 0 To UBound(Receipents)
    stream.WriteText "BEGIN:VENV" & vbCrLf & _
            "BEGIN:VCARD" & vbCrLf & _
            "VERSION:2.1" & vbCrLf & _
            "N:"& Receipents(i)(0) & vbCrLf & _
            "TEL:" & Receipents(i)(1) & vbCrLf & _
            "END:VCARD" & vbCrLf
  Next

  stream.WriteText "BEGIN:VENV" & vbCrLf & _
          "BEGIN:VBODY" & vbCrLf & _
          "Date:" & VMGTime(SMSTime) & vbCrLf & _
          SMS & vbCrLf & _
          "END:VBODY" & vbCrLf & _
          "END:VENV" & vbCrLf

  For i = 0 To UBound(Receipents)
    stream.WriteText "END:VENV" & VbCrLf
  Next

  stream.WriteText "END:VMSG" & vbCrLf
  stream.Flush

  stream.SaveToFile FilePath, adSaveCreateOverWrite
  stream.Close
  Set stream = Nothing
End Sub

Function VMGTime(d)
  VMGTime = pad2(Day(d)) & "." & pad2(Month(d)) & "." & Year(d) & " " & Hour(d) & ":" & Minute(d) & ":" & Second(d)
End Function

Private Function pad2(str)
  If Len(str)<2Then
    pad2 = "0" & str
  Else
    pad2 = str
  End If
End Function

'' A sample Call
Call SaveSMS(Server.MapPath("1.vmg"),array(Array("John","22222222"),Array("Paul","11111111")),"Hello SMS",now)

in a ASP.Net 2.0 web Application, to get a connection string from Web.config, you would write:
ConfigurationManager.ConnectionStrings("DB").ConnectionString

trying to do the same in a Windows application, would give you the error :
Name 'ConfigurationManager' is not declared
even you did import "System.Configuration"...

well, after searching i found that you need to add reference to "System.Configuration.dll" : under Project > Add refrence > .Net > select System.Configuration

Sometimes, you may need to change the owner of an object. This article contains a code sample that you can use to change ownership of objects. :
INF: SQL Server Procedure to Change Object Owner
also this seems to work: Change the owner of all db objects

The most common reason for ending up with orphan users is, moving databases from one server to another or from one domain to another. Restoring an old copy of master database can also result in this problem. Moving databases between servers is quite a common task. So, what's exactly resulting in orphan users?

Well, all SQL Server logins are stored in the system table sysxlogins (in master database). Whenever you create a new login (or grant access to a Windows account), a corresponding entry gets added to the sysxlogins table. So, you could say, each login has a corresponding row in sysxlogins table. In sysxlogins, each login has an associated SID (Security Identifier), which is of type varbinary(85).

How to troubleshoot orphan users in SQL Server databases?

URL rewriting is usually needed to make a URL for a dynamic web page more presentable for the reader or for search engines optimization.

For example, a regular ASP URL might look like this:
http://www.site.com/Articles/Article.asp?id=20

A more presentable way of rewriting the URL might be:
http://www.site.com/Articles/20.htm

of course, you can write HTML/ASP file for each article, but that would be much of I/O overhead on adding/updating articles and for reading too..

one solution is to use an ISAPI filter but many people want to avoid this for due to either the limitation on the skills, a limitation of the hosting service they use, or just to avoid complexity and potential risk. It also makes the solution less portable.

A much simple solution, is to use 404 custom error page in IIS, here are steps and code:
1- Create "URL-Rewrite.asp", under Articles folder.
2- in IIS , right-click on "Articles" folder > properties > Custom errors > Select 404 and click "Edit Properties" ..
set Message Type : "URL", and set URL : "/Articles/URL-Rewrite.asp"
3- Place this code in "URL-Rewrite.asp"
<% option Explicit

Dim Er,ID
Set Er = Server.GetLastError()
If Not Er Is Nothing Then
  '' For 404: URL will be passed as query string
  ID = GetURLID(Request.QueryString )
  'Http error 400 won't raise a code error
  If ID>0 And Er.Number=0 Then
    Response.Status = 200
    Call DisplayArticle(ID)

  ' error 500 or similar  
  ElseIf Er.Number<>0 Then
    Response.Write "Unexpected error was occured"  

  ' undesired URL  
  Else
    Response.Status = 404
    Response.Write "Page cannot be found"  
  End if  
End If  
Set Er = Nothing

Function GetURLID(URL)
  ' Extract ID using regular expressions
  Dim ID : ID = 0
  Dim reg : Set reg = New RegExp
  reg.Global = False
  reg.IgnoreCase = True
  reg.Pattern = "^404;http://www.site.com/Articles/(\d+).htm$"
  if reg.Test(URL) Then
    ID= reg.Replace(URL ,"$1")
  End If
  Set reg = Nothing
  GetURLID = ID
End Function

Sub DisplayArticle(ID)
  '''' here you will place the real code that read article form database and write it
  Response.Write   "<html>" &_
          "<head>" &_
          "<title>Article "& ID &"</title>" &_
          "</head>" &_
          "<body>" &_
          "Content " & ID &_
          "</body>" &_
          "</html>"
End Sub
%>


now,when requesting the url "http://www.site.com/Articles/20.htm", the IIS doesn't find the file so it transfer execution to the custom error page "http://www.site.com/Articles/URL-Rewrite.asp" and sending "404;http://www.site.com/Articles/20.htm" as query string.
The Transfer happens by calling "server.Transfer" which keeps the existing query string and any form variables available to the page it is transferring to, in our case "URL-Rewrite.asp".

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 query
Dim 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 method
Dim 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]

More Tech Tips! | Technology tips on web development

Mike

Mike MoreWeb developer, jQuery plugin author, social media fan and Technology Blogger.
My favorite topics are: jQuery , Javascript , ASP.Net , Twitter , Google..
<connect with="me"> </connect>

Subscribe by email

Enter your email address:

or via RSS