Passing xml data from the app to the database
So I recently talked about how SQL Server can take xml data and shred it into something more relational.
I wanted to follow it up with this post and show an example of how to use ADO.Net to make use of SQL’s xml data type and pass it to an application.
First say that you have a stored procedure with this signature:
CREATE PROCEDURE ShredThis(@p1 XML)
ASThen you can use that procedure from within C# code with something like this:
private void button1_Click(object sender, EventArgs e) { SqlCommand cmd = null; try { cmd = new SqlCommand(); cmd.CommandText = "ShredThis"; cmd.CommandType = CommandType.StoredProcedure; XmlReader xr = XmlReader.Create(new StringReader(@" <book author=""J K Rowling"" Title=""Harry Potter""> <chapter number =""1"" name=""the boy who lived""><chapter> <chapter number =""2"" name=""diagon alley""><chapter> <chapter number =""3"" name=""quidditch""><chapter> <chapter number =""4"" name=""dementors""><chapter> <chapter number =""5"" name=""voldemort's back""><chapter> <book>")); cmd.Parameters.Add("@p1", SqlDbType.Xml).Value = new SqlXml(xr); cmd.Connection = new SqlConnection(@"Data Source=mswart\SQL2008;Initial Catalog=tempdb;Trusted_Connection=True"); cmd.Connection.Open(); cmd.ExecuteNonQuery(); } finally { if (cmd != null && cmd.Connection != null && (cmd.Connection.State == ConnectionState.Open)) { cmd.Connection.Close(); } } } |
You can cut and paste this into any app and adjust it to how you need it. Remember a couple things:
- Don’t forget the using clauses to make sure the code compiles
- Check out the SqlXml class.
- The SqlDbType enumeration has an Xml entry. This is what it’s used for
Cheers,
Michael J. Swart
