Follow Us

Skip Navigation LinksHome > Articles > ADO.NET > Passing DataTable / XML / Array to SQL Server Stored Procedure

Passing DataTable / XML / Array to SQL Server Stored Procedure

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..

By gaonkaramit   On   Friday, 23 January 2009

Page Views : 32321   |   Technologies : ADO.NET

Rating : Rated :
1
| More..

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.







Keywords :
Tags :
Rate This Article :

Comments :

# 1 Annonymous Wrote on 09/01/2009


How to get guid in xml and convert in uniqueidentifier. Example dr["PRODUCT_ID"] = "'f7b681c0-faeb-11d6-9aed-0008c7e6fa6a'"; when i am using cast(coly.query('data(PRODUCT_ID)') as uniqueidentifier) as PRODUCT_ID, it's creating error Explicit conversion from data type xml to uniqueidentifier is not allowed. Please help me



# 2 Annonymous Wrote on 11/04/2009


superb



# 3 Annonymous Wrote on 12/10/2009


Really Good. It Rocks




blog comments powered by Disqus
User Login
Username :
Password :
Register Login

Forgot Password


Related Articles