Bulk Insert in to Db-Xml to Stored Procedure


//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)

Advertisements

2 Responses to Bulk Insert in to Db-Xml to Stored Procedure

  1. Hello there, it is my fourth visit in the site. I seldom come here, but after reading this post, I’ll surely bookmark this site so that I can visit here continuously to read awesome articles like this. Will you kindly tell me in which subject you can write next and most probably when?
    And, you may visit this site at https://technosway.com if you are searching for any SSL discounts for your business..

  2. Very good post! We will be linking to this particularly great post on our website. Keep up the great writing.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: