How to pass multiple records to stored procedure in Asp.net?

There are three main ways to pass multiple records to the stored procedure.

• Comma-delimited string:  It is suitable for the scenario where you need to pass single column value
• XML document: You need to support SQL Server 2005, and you’ve multiple column values.
• Table-valued parameter: You’ve SQL Server 2008 or higher, and you don’t want to get into parsing stuff at the backend and want the best performance.

To know what is a table-valued parameter and how to pass a data table to stored procedure and implement upsert (update + insert) logic using Merge Statement, check out an old post here.

We’ll see in this post how you can pass an XML document to a stored procedure, parse it in a stored procedure, and insert it into the table.

First, check out how you can generate XML Documents in Asp.net using XmlSerializer.

Let’s say we have a list of Person class, and we want to serialize it to XML, then do as below:

List people = new List {
new Person { FirstName = "Scott", LastName = "Guthrie", Age = 32 },
new Person { FirstName = "Bill", LastName = "Gates", Age = 50 },
new Person { FirstName = "Susanne", LastName = "Guthrie", Age = 32 }
};

string strXML = SerializeObject(people);

And the function that serializes the object to XML is as below :

public string SerializeObject(T Obj)
{
    string strxml = string.Empty;
    using (StringWriter sw = new StringWriter())
    {
        XmlSerializer xs = new XmlSerializer(typeof(T));
        xs.Serialize(sw, Obj);
        strxml = sw.ToString();
    }
    return strxml;
}

And the resultant XML would be as below :

<?xml version="1.0" encoding="utf-16"?>
<ArrayOfPerson xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<Person>
<FirstName>Scott</FirstName>
<LastName>Guthrie</LastName>
<Age>32</Age>
</Person>
<Person>
<FirstName>Bill</FirstName>
<LastName>Gates</LastName>
<Age>50</Age>
</Person>
<Person>
<FirstName>Susanne</FirstName>
<LastName>Guthrie</LastName>
<Age>32</Age>
</Person>
</ArrayOfPerson>

Generated XML has encoding UTF-16. Now pass the above XML to your stored procedure as shown below:

string connectionString = Convert.ToString(ConfigurationManager.ConnectionStrings["XMLDBConnString"]);

using (SqlConnection cn = new SqlConnection(connectionString))
{
  using (SqlCommand cm = new SqlCommand("SaveUser", cn))
  {
     cm.CommandType = CommandType.StoredProcedure;

     cm.Parameters.Add("@UsersXml", SqlDbType.NVarChar, -1).Value = strXML;

     cn.Open();

     cm.ExecuteNonQuery();
  }
}

If you see the parameter datatype, it is Nvarchar as generated XML has encoding UTF-16. If you need to support Unicode characters, then you don’t need to change anything, or if you need to encode to UTF-8, then you can use UTF8StringWriter class  shown below:

public class Utf8StringWriter : StringWriter
{
   public override Encoding Encoding
   {
      get { return Encoding.UTF8; }
   }
}

Note: If you pass XML having encoding UTF-16 with varchar datatype to the stored procedure, it will throw an error: unable to switch the encoding

To avoid error, use Utf8StringWriter class instead of StringWriter, use varchar datatype, or use nvarchar datatype and pass XML with encoding UTF-16.

Now to parse generated element-centric XML, we need to use Xquery. If you’re new to Xquery, I suggest you go through the links below, or you can skip them if you wish.

Xpath Syntax
Get Table From XML

Check out the sample stored procedure :

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SaveUser]
@UsersXml AS NVARCHAR(MAX)
AS
BEGIN
DECLARE @XML AS XML

SELECT @XML = @UsersXml

INSERT INTO Person (FirstName, LastName, Age)
select M.Item.value('FirstName[1]','VARCHAR(50)'),
M.Item.value('LastName[1]','Varchar(50)'),
M.Item.value('Age[1]','INT')
FROM @xml.nodes('/ArrayOfPerson/Person') AS M(Item)
END

Note: XQuery is case-sensitive; hence check your tsql statements for typos if you don’t get the expected output.

That’s all you need; I hope it helps! Your comments are most welcome. 🙂