Passing DataTable / XML / Array to SQL Server Stored Procedure

Introduction

Are you trying to pass DataTable object or an Array or a Xml to SQL server stored procedure then this article is just for you and all who need something like that..

What you need to know

To use this code you should be aware of DataTable, StringWriter, SqlClient and Use of XML in SQL Server.

SQL Server table and stored procedure

Table Script:

CREATE TABLE [dbo].[tempMytable](
    [sid] [varchar](50)
    [sname] [varchar](50)
) ON [PRIMARY]

Stored Procedure:

CREATE procedure InsertIntoMyTable
(
@mytable xml
)
as
begin

SELECT 
    cast(colx.query('data(sid) ') as varchar) as sid,
    cast(colx.query('data(sname) ') as varchar)     as sname
INTo #TMP FROM @mytable.nodes('DocumentElement/mytable') AS Tabx(Colx)

INSERT INTO tempMytable
SELECT * FROM #TMP WHERE sid not in (select sid from tempMytable)

UPDATE tempMytable
set sname = t.sname
from #TMP t where t.sid = tempMytable.sid

delete from tempMytable
where sid not in (select sid from #TMP)

end
go

.Net Code

This can be used in any of the .Net application.

C# Code:

        DataTable dt = new DataTable();
        dt.TableName = "mytable";
        dt.Columns.Add("sid");
        dt.Columns.Add("sname");
        DataRow dr = dt.NewRow();
        dr[0] = "1";
        dr[1] = "Kim";
        dt.Rows.Add(dr);
        dr = dt.NewRow();
        dr[0] = "2";
        dr[1] = "Jessica";
        dt.Rows.Add(dr);
        dr = dt.NewRow();
        dr[0] = "3";
        dr[1] = "Jennifer";
        dt.Rows.Add(dr);
        string result;
        using (StringWriter sw = new StringWriter())
        {
            dt.WriteXml(sw);
            result = sw.ToString();
        }
        string conn =ConfigurationManager.ConnectionStrings["DB"];
        using (SqlConnection conn = new SqlConnection(conn))
        {
            conn.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "InsertIntoMyTable";
            SqlParameter param = new SqlParameter("@mytable", SqlDbType.Xml);
            param.Value = result;
            cmd.Parameters.Add(param);
            cmd.ExecuteNonQuery();            
        }


Note: If you are passing GUID into the xml. Please take care of the following two steps

1. In the DataColumn in ADO.NET, dont use single quote around the GUID value. GUID is made by hexadecimal and contains 128 bits. Hence, numbers 0-9 and alphabets a-f are valid only. If you see – in GUID, GUID is divided into multiple small parts and – represent the separation into them.
e.g.

dr["PRODUCT_ID"] = "f7b681c0-faeb-11d6-9aed-0008c7e6fa6a"; //guid without any single quote

2. Casting to unique identifier is little tricky. Above example would give you "Target string size is too small to represent the XML instance" compile time error on Stored procedure. So cast in the following way.
Cast(convert(varchar(36), colx.query('data(PRODUCT_ID) ') ) as uniqueidentifier ) as PRODUCT_ID

PRODUCT_ID is of uniqueidentifier in database table and GUID in ADO.NET DataTable.


for bigint or numeric values please cast as follows
Cast(convert(varchar(20), colx.query('data(ID) ') ) as bigint)   as ID

ID can be any numeric type, so cast to specific type. in the above example I have cast to bigint.