Creating Data Access Layer for A Multi-Language ASP.Net Application

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

Tags: ADO.Net , ASP.Net , DAL , OOP , SQL , SQL Server , Unicode , VB.Net