SqlBulkCopy Unicode/UTF8 error

Question

I am using c# SqlBulkCopy functionality to save records with UTF8 encoded multilingual values. My database field is 'NVARCHAR(max)' and my to be saved value is 我多言語で我. But it is saving as '???????' . Is there any way to save this exact Japanese/Multilingual value via SqlBulkCopy.

row["ParameterValue"] = RegData.ParamValue;

I debugged & checked the above line also. But data is coming to above line also totally fine. After this line i am executing

bulkCopy.WriteToServer(dataTable);

(I have tried this with simple db insert & its working fine. but i have millions records to be saved at once. so simple db insert is not good for me)

Answer 1

'???????' is the result I'd expect from:

select cast(N'我多言語で我' as varchar(max)) 

Inserting to a NVARCHAR column via a newly created DataTable with a string column seems to work for me...

If you run a SQL profiler trace you should see an insert bulk ... statement with the datatype being used for the bulk dataset (n.b. you won't see the values).

If this and the target field are the correct NVARCHAR type, maybe there's some server side processing occuring?

Edit: the following works for me

    // insert code to create SqlConnection
    var dbName = string.Format("Test{0}", Environment.TickCount);
    var command = connection.CreateCommand();
    command.CommandText = string.Format("create database {0}", dbName);
    command.ExecuteNonQuery();
    command.CommandText = string.Format("use {0}", dbName);
    command.ExecuteNonQuery();
    try
    {
        command.CommandText = "create table Test (Value nvarchar(max))";
        command.ExecuteNonQuery();

        var dataTable = new DataTable();
        dataTable.Columns.Add("Value", typeof(string));

        var row = dataTable.NewRow();
        row["Value"] = "我多言語で我";
        dataTable.Rows.Add(row);

        var sqlBulkCopy = new SqlBulkCopy(connection);
        sqlBulkCopy.DestinationTableName = "Test";
        sqlBulkCopy.WriteToServer(dataTable);

        Console.WriteLine("Please check the following query:");
        Console.WriteLine(string.Format("select * from {0}..Test", dbName));
        Console.ReadKey();                
    }
    finally
    {
        command.CommandText = string.Format("drop database {0}", dbName);
        command.ExecuteNonQuery();
    }