none
Serbian letters in dataset object filled from MySQL database RRS feed

  • Question

  • I am writting an application in c# which migrates data from MySQL database to MSSQL database. The data in MySQL database has Serbian letters, but when I connect to the database and fill my dataset object, I loose the Serbian letters (instead of čć I get èæ).
    I have changed Regional Settings to Serbian, which resulted in changing Local property of the dataset object, but made no effect to the data in the dataset object. I've also tried setting CHARSET and NAMES to utf8, with no success.
    I use MySQL Connector Net 5.0.8.1. Here's some code.

    This is the connection string:
        ConnectionString = "server=[serverName];charset =utf8;user id=[ID];password=[]password;database=[databaseName];";

    public DataSet readData(string cmdText)
    {
                MySqlCommand cmd = new MySqlCommand();
                ConnectMySQL();
                cmd.CommandText = cmdText;
                cmd.Connection = con;
                MySqlDataAdapter da = new MySqlDataAdapter(cmdText, con);
                System.Data.DataSet ds = new System.Data.DataSet();
                da.Fill(ds);
                CloseMySQLConnection();
                return ds;
    }

    How do I get Srebian letters in my dataset object?
    Friday, January 18, 2008 10:20 AM

Answers

  • There are no issues using SQL Server to generate Serbian so just use SSIS/DTS to move the database to SQL Server and do the development from SQL Server.  When a developer is having language related issues with .NET it is related to the developer not doing everything needed to remove the character conversion.

     

    Wednesday, January 23, 2008 2:31 PM

All replies

  • In SQL Server you need to use the correct collation to create your database, tables and column and you will not get character conversion.  And you can save the ADO.NET code as Serbian in the VS2005/8 advanced save as option in the file menu.  The important thing to remember don't use BIN because it is a case sensitive collation which means all interaction with the database will be case sensitive.  Check the links below for your collation I am assuming you are using Nvarchar instead of Varchar as your data type.

     

    http://msdn2.microsoft.com/en-us/library/ms143508.aspx

     

    http://msdn2.microsoft.com/en-us/library/ms180175.aspx

     

    Saturday, January 19, 2008 2:23 PM
  • I am using nvarchar. My problem is not in SQL Server. The database in SQL Server was created correctly. I get this problem before I start writting data to MSSQL database. I have the correct data in MySQL database, but the problem starts when I read the data from there and put it in dataset object.

    MySQL database-->DataSet object-->MSSQL database
    my problem occurs at the first --> sign, not the second.
    The data gets corrupted somewhere between reading it and filling it to dataset object.

    public DataSet sqlRead(string cmdText)
            {
                MySqlCommand cmd = new MySqlCommand();
                ConnectMySQL();
                cmd.CommandText = cmdText;
                cmd.Connection = con;
                MySqlDataAdapter da = new MySqlDataAdapter(cmdText, con);
                System.Data.DataSet ds = new System.Data.DataSet();
                da.Fill(ds);                                    // THIS IS WHERE THE DATA IS NOT CORRECT
                CloseMySQLConnection();
                return ds;
            }

    Here's what I get when I send "show variables like 'c%'" query to MySQL server:

    character_set_client    utf8
    character_set_connection    utf8
    character_set_database    utf8
    character_set_filesystem    binary
    character_set_results   utf8
    character_set_server    latin1
    character_set_system    utf8
    character_sets_dir    c:\\wamp\\mysql\\share\\charsets\\
    collation_connection    utf8_general_ci
    collation_database    utf8_general_ci
    collation_server    latin1_swedish_ci
    completion_type    0
    concurrent_insert    1
    connect_timeout    5
    Tuesday, January 22, 2008 11:09 AM
  • Create a new solution save it as Serbian in VS2005/8 advanced save as before filling your dataset.

     

    Tuesday, January 22, 2008 3:23 PM
  • I did all you said, but it did not help.
    Wednesday, January 23, 2008 10:12 AM
  • There are no issues using SQL Server to generate Serbian so just use SSIS/DTS to move the database to SQL Server and do the development from SQL Server.  When a developer is having language related issues with .NET it is related to the developer not doing everything needed to remove the character conversion.

     

    Wednesday, January 23, 2008 2:31 PM