Developing a Data Access Layer for an ASP.Net Application Supporting Multiple Languages

Following my previous post about a data access layer for ASP.Net, I’ll explain how to extend it for multi-language support.

To accommodate multiple languages, we’ll split the “Products” table into two: one for fundamental product data and another for language-specific fields. Below is the SQL code to create these tables:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
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  
    )  
)

Note that language-dependent fields now use nvarchar instead of varchar to store Unicode characters.

Our data access layer (DAL) requires modifications and two new classes. Let’s examine the updated code:

Product Entity Class:

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
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  
    ''' Detail Collection of this product  
    Public Details As ProductsDetails  
    ''' <remarks>
    '''  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
    ''' </remarks>  
    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  
  
    ''' Initialize Product Fields  
    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  
  
    ''' Select Product by ID  
    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  
  
    ''' Insert new Product and get new ID  
    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  
  
    ''' Update Product  
    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  
  
    ''' Delete This product  
    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

ProductDetail Entity Class:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
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  
  
    ''' Initialize Fields  
    Public Sub Initialize()  
        ID = 0  
        Lang = ""  
        Name = ""  
        Description = ""  
    End Sub  
  
    ''' Select Details by ID and Lang  
    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  
  
    ''' Update Detail or Insert Detail if not there  
    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  
  
    ''' Delete This Detail  
    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

Products Collection Class:

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
Public Class Products  
    Inherits CollectionBase  
  
    ''' PageSize=0 means no paging  
    Public PageSize As Integer  
    ''' PageIndex=0 means no paging  
    Public PageIndex As Integer  
    Private mPageCount As Integer  
    ''' To Get Top records if larger than 0  
    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  
  
    ''' Initialize collection  
    Public Sub Initialize()  
        mPageCount = 0  
        List.Clear()  
    End Sub  
  
    Public ReadOnly Property PageCount() As Integer  
        Get  
            Return mPageCount  
        End Get  
    End Property  
  
    ''' Gets or sets the element at the specified zero-based index  
    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  
  
    ''' Adds an object to the end of the Collection  
    Public Function Add(ByVal Obj As Product) As Integer  
        Return List.Add(Obj)  
    End Function  
  
    ''' Select Products by Status, More Search Params can be added..  
    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

ProductsDetails Collection Class:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
Public Class ProductsDetails  
    Inherits CollectionBase  
    ''' PageSize=0 means no paging  
    Public PageSize As Integer  
    ''' PageIndex=0 means no paging  
    Public PageIndex As Integer  
    Private mPageCount As Integer  
    ''' To Get Top records if larger than 0  
    Public TopRecords As Integer  
  
    Public Sub New()  
        Call Initialize()  
        PageSize = 0  
        PageIndex = 0  
        TopRecords = 0  
    End Sub  
  
    ''' Initialize collection  
    Public Sub Initialize()  
        mPageCount = 0  
        List.Clear()  
    End Sub  
  
    Public ReadOnly Property PageCount() As Integer  
        Get  
            Return mPageCount  
        End Get  
    End Property  
  
    ''' Gets or sets the element at the specified zero-based index  
    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  
  
    ''' Adds an object to the end of the Collection  
    Public Function Add(ByVal Obj As ProductDetail) As Integer  
        Return List.Add(Obj)  
    End Function  
  
    ''' Select Products Details by ID or Lang  
    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

Examples of Usage:

Selecting a product with details in one language:

1
2
3
4
5
6
7
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

Selecting a product with details in all languages:

1
2
3
4
5
6
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

Inserting a new product with details:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
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

Selecting a paginated list of products with details in one language:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
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

This implementation utilizes a generic command class (explained in my previous post) to streamline database interactions.

This design pattern offers a structured approach to managing multi-language data in your ASP.Net application. I welcome your feedback and suggestions for improvement.