Bulk Insert in to Db-Xml to Stored Procedure

August 24, 2012

//Create Data table to collect data

DataTable dt = new DataTable();
dt.TableName = “Test”;
dt.Columns.Add(“DeviceCode”, typeof(Int32));
dt.Columns.Add(“Message”, typeof(string));
dt.Columns.Add(“EmpId”, typeof(string));
dt.Columns.Add(“EmpCode”, typeof(string));
dt.Columns.Add(“CardID”, typeof(string));
DataRow dr = dt.NewRow();
dr[“DeviceCode”] = “1”;
dr[“Message”] = “1”;
dr[“EmpId”] = “1”;
dr[“EmpCode”] = “1”;
dr[“CardID”] = “1”;

dt.Rows.Add(dr);
dr = dt.NewRow();
dr[“DeviceCode”] = “2”;
dr[“Message”] = “2”;
dr[“EmpId”] = “2”;
dr[“EmpCode”] = “2”;
dr[“CardID”] = “2”;

dt.Rows.Add(dr);

string result;
using (StringWriter sw = new StringWriter())
{
dt.WriteXml(sw);
result = sw.ToString();
}

string cn = Convert.ToString(ConfigurationManager.ConnectionStrings[“mycn”]);
SqlConnection conn = new SqlConnection();
conn.ConnectionString = cn;
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = “sp_Insert_DeviceCommand_Bulk”;
SqlParameter param = new SqlParameter(“@mytable”, SqlDbType.Xml);
param.Value = result;
cmd.Parameters.Add(param);
cmd.ExecuteNonQuery();

===================================================
Stored Procedure for bulk insert/Xml to Database
===================================================
ALTER PROCEDURE [dbo].[sp_Insert_DeviceCommand_Bulk]
@mytable as xml
AS
BEGIN
SET NOCOUNT ON;

–INSERT INTO dbo.tblqueuedata
–(DeviceCode,Message,EmpId,EmpCode,CardId)
–SELECT DeviceCode,Message,EmpId,EmpCode,CardId FROM @dtQueue
INSERT INTO tblqueuedata
(DeviceCode,Message,EmpId,EmpCode,CardId)
SELECT
cast(convert(varchar(20), colx.query(‘data(DeviceCode) ‘)) as int) as DeviceCode,
cast(colx.query(‘data(Message) ‘) as varchar) as Message,
cast(colx.query(‘data(EmpId) ‘) as varchar) as EmpId,
cast(colx.query(‘data(EmpCode) ‘) as varchar) as EmpCode,
cast(colx.query(‘data(CardId) ‘) as varchar) as CardId
FROM @mytable.nodes(‘DocumentElement/Test’) AS Tabx(Colx)