How to manage NAV DBCS data using C#
It is known that NAV CC (Classic Client) does not support natively DBCS (Double-Byte Character Set) and so does the new NAV 2009 RTC (Role Tailored Client).
Due to differences in how the CC and RTC check the code page isn’t still possible to enable DBCS in the RTC but this is possible for the CC by implementing new fin.stx file.
This last sentence is duly written and explained in KB 915374.
How to enable and to display double-byte character sets in Microsoft Dynamics NAV
https://mbs.microsoft.com/knowledgebase/KBDisplay.aspx?scid=kb;EN-US;915374
Following the KB link is possible to enable DBCS for several NAV versions included NAV 2009.
… But what if we want to develop a simple WinForm project in VC# that access the database in order to retrieve and write information in the same way NAV does?
The following example will guide you through the creation of a simple Form using VS (Visual Studio ) and transforming Japanese characters in their “NAV equivalent” in order to deal correctly with the DBCS data management inside the NAV database.
The example proposed has been developed using the following scenario:
1. Windows Server 2008
2. SQL Server 2008
3. Visual Studio 2008 SP1
4. NAV 2009 RTM W1
ENABLE DBCS IN NAV 2009
a. Download and install a copy of NAV 2009 W1 RTM.
Install the sample database and the Classic Client.
The SQL sample database should use Windows Collation for Afrikaans, Basque, Catalan, Dutch, English, Faeroese, German, Indonesian, Italian, Portuguese.
This can be easily checked in NAV by checking the collation tab in
File > Database > Alter
b. In Windows Server 2008 select Japanese as language for non-Unicode programs.
Control Panel > Regional and Language Options > Administrative tab
Change the current language for non-Unicode programs to: Japanese (Japan)
(you will be prompted to reboot the machine)
c. Download the zip file contained in KB 915374
d. Extract the fin.stx file from the folder NAV 2009
e. Substitute the fin.stx file that is located in the Classic client folder with the brand new one extracted.
E.g. Path for Classic Client folder:
C:\Program Files (x86)\Microsoft Dynamics NAV\60\Classic
f. Substitute the fin.stx file that is located in the ENU folder of the Classic Client with the brand new one extracted.
E.g. Path for Classic Client folder:
C:\Program Files (x86)\Microsoft Dynamics NAV\60\Classic\ENU
Now you should be able to manage Japanese characters like those ones…
WHAT TYPE OF ENCODING?
Roughly speaking regarding what we have done is merely enabling the Japanese characters to be stored into SQL Server but… not directly!
The Japanese code page is 932 (Shift-JIS) while we are using the Latin1 code page 1252 to store the data. To know more about Windows Collation Designators you can check:
https://msdn.microsoft.com/en-us/library/aa176553.aspx
This means that while we are typing Japanese chars in the NAV classic Client (using code page 932), this have been reverted (translation encoded) in their respective characters using code page 1252 and stored in this way in the SQL Server field.
This means that while we are seeing this in NAV :
ラドクリフ、マラソン五輪代表に1万m出場にも含み
… those are the characters effectively stored in SQL Server …
ƒ‰ƒhƒNƒŠƒt?Aƒ}ƒ‰ƒ\ƒ“ŒÜ—Ö‘ã•\‚É1–œm?o?ê‚É‚àŠÜ‚Ý
Then the solution can come out clearly: use C# encoding function to manage the ‘translation’ between one set of character to another.
LET’S BUILD WinFormNavDBCS
1. Open Visual Studio 2008
2. Create a brand new Visual C# project
Go to File > New > Project
Select project types: Visual C# > Windows > Windows Forms Application
Give the project a Name: WinFormDBCS
Click OK
3. Design your form like this (2 TextBox, 1 Button)
4. Add this code to your form:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
namespace WinFormDBCS
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
//Create a new variable of type Encoding and
// assign it the Latin
// code page 1252
//To know more about it
// https://msdn.microsoft.com/en-us/library/system.text.encoding_members.aspx
Encoding nav = Encoding.GetEncoding(1252);
//Create a new variable of type Encoding and assign it the Japanese
// code page 932
Encoding unicode = Encoding.GetEncoding(932);
//Populate a byte variables withe the value of the first TextBox written
// in Japanese characters using the Japanese code page 932
byte[] unicodeBytes = unicode.GetBytes(textBox1.Text);
//Display in the second TextBox the result of the encoding conversion of
// the byte stored in Japanese directly in the Latin code page equivalent
//Tip: you can substite the mere text display with some simple code that
// writes directly this value into a NAV Text field
textBox2.Text = nav.GetString(unicodeBytes);
}
}
}
5. Press F5 to start debugging your project.
6. Insert this Japanese string (or whatever Japanese sentence you want to) in the first TextBox:
ラドクリフ、マラソン五輪代表に1万m出場にも含み
7. Press the Transform Data button and… see the results:
8. To check if NAV ‘digest’ those newly created string you have to select the whole string converted in the second TextBox and copy it:
9. Open SQL Server Management Studio and paste it into the “Address 2” field of customer 01121212.
Expand “Demo Database NAV (6-0)”
Expand “Tables”
Search for “CRONUS International Ltd_$Customer” table
Right click on “CRONUS International Ltd_$Customer” and click on “Edit top 200 rows”
Search for customer 01121212
Place the cursor in the “Address 2” field and paste the content in this field
10. Browse to the next record and close SQL Server Management Studio.
CHECK THE FINAL RESULT
Open NAV and… go to customer 01121212.
These postings are provided "AS IS" with no warranties and confer no rights. You assume all risk for your use.
Best Regards,
Duilio Tacconi
Microsoft Dynamics Italy
Microsoft Customer Service and Support (CSS) EMEA
Special thanks to Kenny Vaes of Helios-IT