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