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 ProductsImports 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 DetailsPublic 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 ProductsPublic 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 ProductsDetailsPublic 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 productDim 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 pagingDim 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..
Since Jmail 4.5, you can send UTF-8 Text in Email Subject
V4 would Send UTF-8 Body , but the Subject will be corruptedDim 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
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:
- Ships with ANSI only controls (Label, Textbox, etc.).
- Properties Window in IDE is ANSI only. Unicode strings are displayed as '????'
- PropertyBag automatically converts Unicode strings to ANSI.
- Clipboard functions are ANSI only.
- 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.
Web developer, jQuery plugin author, social media fan and Technology Blogger.