When dealing with digital documents, you may notice that PDF data and Excel spreadsheets often go hand in hand. Although not as easily as we’d like. The PDF format is perfect format for preservation of your content layout - whether it be an invoice, a statement or a database. Thus, even though the format can display data perfectly, it can also make it difficult to reuse, edit or analyze in Microsoft Excel. Users have no choice but to turn to PDF converter solutions.

Since Twitter only allows you to pull the last 3200 Tweets, people are using many online services to archive their Tweets. However, if you are interested to do it yourself in .Net, this tutorial would be a good kickstart.

Few months ago I posted a Javascript class on how to Implement a paging listbox using jQuery. which has drawn a lot of traffic and few questions lately so I thought it would be more convenient to rewrite the code as jQuery plugin and make few enhancements plus providing a complete sample code in VB.Net and C#.

Enhancements:

1. Easier usage, You only need to insert a div with the class "paging-listbox" and settings inside "options" attribute to automatically have the paging listbox loaded inside that div. still you can load the listbox with regular Javascript call.
2. Added support for right-to-left layout.
3. You can pass additional parameters to source page via AJAX. for example a category ID that user selects from another form field and should be used to query records by on the source page.
4. Few visual enhancements.

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 Products
Imports 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 Details
Public 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 Products
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 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 ProductsDetails
Public 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 product
Dim 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 paging
Dim 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 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

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

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

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]