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]

Since Jmail 4.5, you can send UTF-8 Text in Email Subject
V4 would Send UTF-8 Body , but the Subject will be corrupted
Dim JMailObj As New jmail.SMTPMail
JMailObj.ServerAddress = ServerAddress
JMailObj.Silent = True
JMailObj.Logging = True
JMailObj.Charset = "utf-8"
JMailObj.ContentType = "text/html"
JMailObj.Sender = Sender
JMailObj.Subject = Subject
JMailObj.AddRecipient Email
JMailObj.Body = Message
JMailObj.Execute
Set JMailObj = Nothing
download w3JMail v 4.5

Old fashioned guys use SQL Server varchar/text fields to store strings of multiple languages that uses 1 byte encoding like [Windows Character Table] :
Windows-1252 : English, French , Spanish, German,Italian,Spanish (Western European characters)...
Windows-1251 : Russian,Bulgarian,Serbian,Ukrainian
Windows-1253 : Greek
Windows-1256 : Arabic
.....

Of course, 1 byte encoding field can contain English + only one other language characters - unlike UTF-8) , just as a file encoded in 1-byte encoding..

To know about Character sets, you should check :
The Absolute Minimum Every Software Developer Absolutely, Positively Must Know About Unicode and Character Sets (No Excuses!)

in that case : Asp pages codepage should remain as default = 1252
<% @ LANGUAGE=VBScript CODEPAGE=1252 %>

and setting the Response.Charset or HTML meta tag for charset correctly will show things right.. and HTML page size is smaller than the same one in UTF-8

of course running a site like that in IIS, will require that windows > Control Panel > Regional and Language options > Advanced > must be English or Strings read from SQL server will be corrupted...

A disadvantage is that you can't show more than one language (Other than English) in the same page without using escape codes... ,which suitable is for small text (a link to other language home page)

but, if you need to output UTF-8 file (text,Xml ,RSS,..) from non UTF-8 page, you must remember that Strings are Unicode in memory, so if you read a string from SQL Server using settings as mentioned before , and as an example :
- if we have that string "привет" which is "hi" in Russian
- and saved in varchar field in SQL Server it will look like "ïðèâåò"
- when you read that string in memory using ado it will look like "ïðèâåò" , cause VB string can't know it is Russian ( it is readed from varchar and default codepage is 1252 ,so it thinks it is Western European characters)
- So To Convert it to Russian will use ADO Stream :
AlterCharset("ïðèâåò","windows-1252", "windows-1251")

- After that it would be saved in memory as "привет"
- and when written to UTf-8 file , it will be "привет" , but if u don't do the Conversion step it will be "ïðèâåò"

enough talking , here is the code
For this code to work in VB6, you will need to add a reference to the Microsoft ActiveX Data Objects 2.5+ Library and change [Dim Stream : Set Stream=Server.CreateObject("ADODB.Stream") ] to [Dim Stream as new ADODB.Stream]

Const adTypeBinary = 1
Const adTypeText = 2

' accept a string and convert it to Bytes array in the selected Charset
Function StringToBytes(Str,Charset)
  Dim Stream : Set Stream = Server.CreateObject("ADODB.Stream")
  Stream.Type = adTypeText
  Stream.Charset = Charset
  Stream.Open
  Stream.WriteText Str
  Stream.Flush
  Stream.Position = 0
  ' rewind stream and read Bytes
  Stream.Type = adTypeBinary
  StringToBytes= Stream.Read
  Stream.Close
  Set Stream = Nothing
End Function

' accept Bytes array and convert it to a string using the selected charset
Function BytesToString(Bytes, Charset)
  Dim Stream : Set Stream = Server.CreateObject("ADODB.Stream")
  Stream.Charset = Charset
  Stream.Type = adTypeBinary
  Stream.Open
  Stream.Write Bytes
  Stream.Flush
  Stream.Position = 0
  ' rewind stream and read text
  Stream.Type = adTypeText
  BytesToString= Stream.ReadText
  Stream.Close
  Set Stream = Nothing
End Function

' This will alter charset of a string from 1-byte charset(as windows-1252)
' to another 1-byte charset(as windows-1251)
Function AlterCharset(Str, FromCharset, ToCharset)
  Dim Bytes
  Bytes = StringToBytes(Str, FromCharset)
  AlterCharset = BytesToString(Bytes, ToCharset)
End Function

This article Shows & compares many paging methods of ASP and ASP.Net and also SQL Paging methods that can be used in both of them..
You would notice here that all the fast paging methods runs a small query first to get number of records and calculate page count..

Classic ASP
in ASP , The article [How do I page through a recordset?] is a very good refrence. and I think that paging by Recordset.Move() was the best of paging methods that don't use stored procedures.

Even Recordset.GetRows() + Recordset.Move() performed best -as the article says- which combines the effective move() technique, with GetRows(). but for me since I use Custom Business Classes ,GetRows() is not needed.

GetRows() converts a heavy recordset object into a lighter-weight array for local processing

Recordset.Move()
The Recordset.Move() technique uses Move() method to skip the first n rows in the result set to start at the first row for the page we are interested in.

<!--#include file=inc.asp-->
<!--#include file=topRS.asp-->
<%
rstart = PerPage * Pagenum - (PerPage - 1)
dataSQL = "SELECT ArtistName, Title FROM SampleCDs WITH (NOLOCK)"
set rs = conn.execute(dataSQL)
if not rs.eof then
rs.move(rstart-1)
response.write "<table border=0 cellpadding=5>"
for x = 1 to PerPage
if rs.eof then exit for
artist = rs(0)
title = rs(1)

rs.movenext
next
response.write "</table>"
else
response.write "No rows found."
response.end
end if
%>
<!--#include file=foot.asp-->

ASP.Net
In ASP.Net you would hear about DataGrid but it is terrible cause each time you move another page the entire records are fetched..
Now lets examine good paging methods for ASP.Net:

1) Dataset
Using the method DbDataAdapter.Fill(DataSet, Int32, Int32, String) which specifies the start record and number of records to fill the DataSet with.

'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

However, 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 this: I added a top clause in the select query to select Top(PageSize * PageIndex), so if we are requesting the 2nd page of a page size of 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 (1 million records). this tweak which i used for DataSet,DataReader and Recordset make their performance+time is much better for closer pages.

2) DataReader
You won't see this out there very often! it is inspired By ADO Paging method[Recordset.Move()]. check it out:

'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 Reader As DbDataReader =Cmd.ExecuteReader(CommandBehavior.CloseConnection)

'> Move to desired Record
Dim startRecord as Integer = (PageIndex - 1) * PageSize
For i As Integer=0 To (startRecord - 1)
   If Not Reader.Read Then Exit For
Next

While Reader.Read()
   'Do Something
End While
Reader.Close()
Reader = Nothing

When it is only one table you query or it is read only & forward only mode : DataReader is better.
and According to tests @ [A Speed Freak's Guide to Retrieving Data in ADO.NET] DataReader will be noticeably faster in a larger Page Size

3) Recordset
What? yes, why not.. lets try ADO Recordset in ASP.Net, just add reference to Microsoft ActiveX Data Objects 2.5+ ,here is the code:

Dim Conn As New ADODB.Connection
Dim Cmd As New ADODB.Command
Conn.Open(ConnString)
Cmd.ActiveConnection = Conn
Dim RS As New ADODB.Recordset
Dim i As Integer = 0, Count As Integer = 0

'Count Query
cmd.CommandText = "Select count(ID) from Users where.."
Rs.Open(Cmd, , CursorTypeEnum.adOpenForwardOnly, LockTypeEnum.adLockReadOnly,CommandTypeEnum.adCmdText)
If Not Rs.EOF Then Count = CInt( Rs(0).Value)
Rs.Close()
PageCount = Math.Ceiling(Count / PageSize)

'Select Query
cmd.CommandText = "Select Top " & (PageIndex * PageSize) & " * from Users where.. Order By.."
Rs.MaxRecords = PageIndex * PageSize
RS.Open(Cmd, , CursorTypeEnum.adOpenForwardOnly, LockTypeEnum.adLockReadOnly,CommandTypeEnum.adCmdText)
If Not RS.EOF Then RS.Move((PageIndex - 1) * PageSize)

While not RS.EOF()
   'Do Something
   Rs.MoveNext()
Wend
RS.Close()
RS = Nothing
Conn.Close()

SQL Paging
The following 2 methods will use SQL Paging which can be used in Both ASP and ASP.Net...

4) Top Clause
A SQL Paging using Top Clause as described in MSDN [How To: Page Records in .NET Applications] , and Reading records using a DataReader.

'Count Query
Cmd.CommandText = "Select Count(ID) from Users where.."
Count = CInt(Cmd.ExecuteScalar())
PageCount = Math.Ceiling(Count / PageSize)

'Select Query
Cmd.CommandText= "Select * from (" & _
   "Select Top " & PageSize & " * from (" & _
   "Select Top " & (PageIndex * PageSize) & " * from Users as T1 where.." &_
   " Order by ID asc " & _
   ") as T2 Order by ID desc " & _
   ") as T3 Order by ID asc "

Dim Reader As DbDataReader = Cmd.ExecuteReader(CommandBehavior.CloseConnection)

'Last page fix!!
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

'Loop the desired records
while Reader.Read()
   'do Something
end While
Reader.Close()
Reader = Nothing

Note that: If you are at last page and number of records their is less than PageSize, This method will always return the last PageSize records.. so you would need to skip some records first to reach the desired records..

5) Row_Number Function
A SQL Paging method described in [Custom Paging in ASP.NET 2.0 with SQL Server 2005], Row_Number is a new method introduced in SQL Server 2005, which enables us to associate a sequentially-increasing row number for the results returned.

'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 " & PageSize & " * from (" & _
   "Select *,ROW_NUMBER() OVER (ORDER BY ID ASC) AS Row from Users where.."
   " ) as T1 where Row>" & ((PageIndex - 1) * PageSize) & " and Row<=" & (PageIndex * PageSize)

Dim Reader As DbDataReader= Cmd.ExecuteReader(CommandBehavior.CloseConnection)
while Reader.Read()
   'do Something
end While
Reader.Close()
Reader = Nothing

Interesting results on Timing these Paging methods
I Timed these paging methods on one table of more than 1 million records , getting & looping 100 records per page , and page index moves further to get records as shown in the table.
Tests made using [Microsoft Web Stress Tool] on a PC of P3.2GHZ, 1GB Ram, Windows XP, SQL Server 2005 Express.

Method	1 to 10000	10000 to 100000	100000 to 500000	500000 to 1000000
Recordset	601.00	1101.06	2708.94	4750.35
DataSet	518.79	734.94	2264.78	4463.53
DataReader	490.12	813.29	2165.71	4094.18
Top Clause	518.88	735.29	1881.18	4017.88
Row_Number	381.18	466.35	801.18	1309.76

DataReader,DataSet and Recorset methods time is close to 'Top Clause' method after using the tweak menthioned before, but DataReader gives slightly better time than DataSet.

The old ADO Recordset is not bad, but you should not need it in ASP.Net.

SQL Top Clause is slightly faster, although the SQL Server resources usage is not so efficient in further pages.

Row_Number is the fastest if you have SQL Server 2005.

* Generally, when the SQL query sort or search records by indexed columns: query cost is lower..

Update!: I wrote a VB.Net Class to encapsulate all that Data Access code to help me [Write Less & Generic Data Access Code in ADO.NET 2.0], check it out!

A great tutorial @ Using Unicode in Visual Basic 6.0

Although Visual Basic 6.0 stores strings internally as Unicode(UTF-16) it has several limitations:

1. Ships with ANSI only controls (Label, Textbox, etc.).
2. Properties Window in IDE is ANSI only. Unicode strings are displayed as '????'
3. PropertyBag automatically converts Unicode strings to ANSI.
4. Clipboard functions are ANSI only.
5. Menus are ANSI only.

The purpose of this tutorial is to resolve these issues and provide working VB code solutions. The level of difficulty of these solutions vary but in general require intimate knowledge of ActiveX Controls and Classes. Subclassing and API programming are a must to gain functionality that Vb does not directly support.

How do I page through a recordset in ADO

A very common task when designing web pages is to allow users to "page" through a resultset. This means 10 or 50 or some fixed number of rows are displayed on each page, and the user can click next / previous or choose a page number from a dropdown. Previously, this article had a total of three samples. One in straight ASP, and two with different approaches using stored procedures. Thanks to Chris Hohmann, Anith Sen, Steve Kass, David Portas, Uri Dumant — and, most recently, Brynn and Bob Barrows — for providing me the ammunition and motivation to re-write this article. Now, it contains a total of TEN different techniques. After explaining each one, I will also show you which ones performed best in my tests (see results), and even provide you with all the samples (and the testing code) as a download.