Save UTF8 string by SqlBulkCopy c#

Question

I have this Sql query:

INSERT INTO myTable (myField) VALUES (N'Thermal Oxide: 0 Å to 40 μm')

and I want to exeute it by SqlBulkCopy like that:

DataTable myDataTable=myDb.getData("select top 1* from myTable").Clone();
DataRow dr = myDataTable.NewRow();  
dr["myField"] ="N'Thermal Oxide: 0 Å to 40μm'";
myDataTable.Rows.Add(dr);
this.openCon();
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(con))   {
    bulkCopy.DestinationTableName = dt.TableName;
    bulkCopy.BulkCopyTimeout = 100;
    bulkCopy.WriteToServer(myDataTable);
}   this.closeCon();

It is work perfect, but my question is: where can I initilize the 'N' prefix? and maybe there is no need?

Thanks, chani

Answer 1

Just:

dr["myField"] = "Thermal Oxide: 0 Å to 40μm";

The N'...' is only required for literals in TSQL; but since you are taking data (not TSQL) you do not need to worry about that. In the case of SqlBulkCopy, it will go down to the server in a raw format, not directly as TSQL.

In the more common case of an adapter or ORM, the library will either (most likely) use parameters, or it will worry about the escapting itself.

It perhaps should also be noted that adding 1 row via SqlBulkCopy is overkill. But it should work.