There are so many different hosting companies available right now that knowing which one is the best for your requirements isn’t easy. ASP.NET programmers want to be certain that they are working with a host that is able to handle the more complex requirements. As you would expect with anything that is developed by Microsoft, ASP.NET works best with a Windows-based hosting solution. However, your usual list of the so called ‘best web hosts’ are not necessarily going to be the right solution for ASP.NET.

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..

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.

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)

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?

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!