.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
Steve Jackman
Imran Ghani
Santhakumar Munuswamy

Home >> Articles >> .Net Framework >> Post New Resource Bookmark and Share   

 Subscribe to Articles

How to make an existing vb.net/sql server project compatible with unicode languages such as arabic

Posted By:Kemal AL GAZZAH       Posted Date: February 24, 2011    Points: 25    Category: .Net Framework    URL: http://www.dotnetspark.com  

This articles shows how to make quickly an existing vb.net/sql server project compatible with arabic or any other unicode language
 

How to make an existsing vb.net/sql server project compatible with unicode languages (such as arabic)


In sql server, in order to insert,update or select data in arabic, we need to use nvarchar or nchar data types.
but we have also to put an N besides each parameter in the sql script if we are using directly sql script in the code rather than stored procedures.
Stored procedures manages the unicode characters and you don't need to change anything to make it compatible with unicode languages,
but just make sure you are using nvarchar or nchar data type !

If we have an existing application which was dealing with only non unicode languages such as English or French
and we want to make it compatible with unicode languages such as Arabic, then we have to add an N besides each
parameter in each sql script.

This is a very time consuming task in development and testing if the project is too big !

In order to bypass this constraint, I made a function which adds automatically the required N's in their places beside each parameter in a given sql script string.

The detection of a parameter is done by finding it between two apostophes ('parameter')
This is the fucntion

Public Function add_N_To_script(ByVal script As String) As String
        '__________________________________________________________________________________________
         'ADDING N in sql script to make it compatible with unicode languages such as Arabic

         'By Kamel AL GAZZAH
        '__________________________________________________________________________________________
        Dim i, oc, nN, last As Integer
        Dim position As Integer
        Dim nscript, ascript, LASTSTR As String
        Dim go As Boolean
        nscript = script
        oc = 0
        position = 0
        nN = 0
        ascript = script
        last = 0
        Do
            go = True
            last = i
            LASTSTR = ""

            i = InStr(nscript, "'")
            If i > 1 Then LASTSTR = Mid(nscript, i - 1, 1)
            If LASTSTR = "N" Then go = False

            If i = 0 Then go = False
            If i = 1 And go = True Then If Mid(nscript, 2, 1) = "'" Then go = False
            If i = 1 And go = True Then If last <> 0 Then go = False

            If i > 1 And go = True Then
                If Mid(nscript, i - 1, 1) = "'" Then go = False
                If Mid(nscript, i + 1, 1) = "'" Then go = False
            End If

            If go = True Then
                position = position + i
                oc = oc + 1
                If oc Mod 2 = 1 Then
                    ascript = Mid(ascript, 1, position - 1 + nN) & "N" & Mid(ascript, position + nN, Len(ascript) - position + 1)
                    nN = nN + 1
                End If
            Else
                position = position + i
            End If
            nscript = Mid(nscript, i + 1, Len(nscript) - i)
        Loop Until i = 0
        Return ascript
    End Function


Example

script="insert into tab1(col1) values('????')"
if we run this script the values added in col1 and col2 will be ?????

and

add_N_To_script(script) will give "insert into tab1(col1,col2) values(N'????')"

By making this function, we saved at least 2 weeks of working time !


 Subscribe to Articles

     

Further Readings:

Responses

No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend