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
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
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
position = position + i
nscript = Mid(nscript, i + 1, Len(nscript) - i)
Loop Until i = 0
script="insert into tab1(col1) values('????')"
if we run this script the values added in col1 and col2 will be ?????
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 !