Created
February 19, 2013 10:23
-
-
Save yetanotherchris/4984662 to your computer and use it in GitHub Desktop.
Inserting XML into SQL Server without unicode issues
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
using System.IO; | |
using System.Text; | |
using System.Data; | |
using System.Data.SqlClient; | |
using System.Data.SqlTypes; | |
... | |
using (SqlConnection connection = new SqlConnection("conn string")) | |
{ | |
connection.Open(); | |
string sql = "INSERT INTO mytable (xmlColumn) VALUES (@xmlData)"; | |
using (SqlCommand command = new SqlCommand(sql, connection)) | |
{ | |
// Swap round if the source file is unicode | |
string xml = File.ReadAllText(@"C:\myxml.xml"); | |
//string xml = File.ReadAllText(@"C:\myxml.xml", Encoding.Unicode); | |
using (MemoryStream stream = new MemoryStream()) | |
{ | |
using (StreamWriter writer = new StreamWriter(stream, Encoding.Unicode)) | |
{ | |
writer.Write(xml); | |
writer.Flush(); | |
stream.Position = 0; | |
SqlParameter parameter = new SqlParameter("@xmlData", SqlDbType.Text); | |
parameter.Value = new SqlXml(stream); | |
command.Parameters.Add(parameter); | |
command.ExecuteNonQuery(); | |
} | |
} | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment