Passing multiple rows to SQL Server using .NET and XML
The System.Data.SqlClient namespace does not include a straightforward way of sending an arbitrary amount of data rows to SQL Server in a single statement, e.g. for making a mass updates/bulk insert.
In most circumstances, generating SQL statements dynamically using string concatenation would be a bad choice, since it might offer vectors for SQL injection and other pitfalls resulting from inadequately escaped code.
When using SQL Server 2008, table valued parameters come in handy, however they require modifications on the server which may not be eligible in all scenarios.
There is another possibility using SQL Server’s builtin XML data type together with the System.Xml classes to achieve this goal. The following code demonstrates a simple approach for inserting data from a string array using a single SQL statement.
using ( SqlConnection con = new SqlConnection( "connection string" ) ) { con.Open(); using ( SqlCommand cmd = con.CreateCommand() ) { string[] arr = { "a", "b", "c", "d" }; XmlDocument xml = new XmlDocument(); xml.AppendChild( xml.CreateElement( "MyColumns" ) ); foreach ( string s in arr ) { XmlElement e = xml.CreateElement( "MyColumn" ); e.InnerText = s; xml.DocumentElement.AppendChild( e ); } cmd.AddParameter( "@XML", DbType.Xml, xml.OuterXml ); cmd.CommandText = "INSERT INTO MyTable ( SomeColumn ) SELECT Item.value( '.', 'NVARCHAR(MAX)' ) " + "FROM @XML.nodes( 'MyColumns/MyColumn' ) AS x( Item );"; cmd.ExecuteNonQuery(); } } |
Since preparing data with an XmlDocument can require a lot of memory with large data, the following alternative can be considered when passing a string array:
/// <summary>Serializes the given strings to an XML string. Will use short element names to reduce string size.</summary> public static string SerializeToXML( IEnumerable<string> strings ) { using ( StringWriter sw = new StringWriter() ) using ( XmlTextWriter xw = new XmlTextWriter( sw ) ) { foreach ( string s in strings ) { xw.WriteStartElement( "s" ); xw.WriteString( s ); xw.WriteEndElement(); } xw.Flush(); return sw.ToString(); } } |
When an entire object needs to be passed to the database, the following generic solution works well:
/// <summary>Serializes the given objects to an XML string. Will use XmlSerializer which requires more memory.</summary> public static string SerializeToXMLUsingXmlSerializer<T>( IEnumerable<T> t ) { using ( StringWriter sw = new StringWriter() ) using ( XmlWriter xw = XmlWriter.Create( sw, new XmlWriterSettings() { OmitXmlDeclaration = true, Indent = false } ) ) { T[] tmp = t.ToArray(); XmlSerializer xs = new XmlSerializer( tmp.GetType(), new XmlRootAttribute( typeof( T ).Name ) ); XmlSerializerNamespaces ns = new XmlSerializerNamespaces(); ns.Add( String.Empty, String.Empty ); xs.Serialize( xw, tmp, ns ); return sw.ToString(); } } |
When only sending a few rows to the server, this approach does not offer better performance that an iterative approach. However when dealing with larger amounts of data, the overhead of sending, executing, receiving and evaluating each query will add up to a signifficant amount of time – which can be avoided using this technique.