SQL Bulk copy from DataTable in ASP.NET, C#:
Create Table "EmployeeInfo":
Name NVarChar(255) not null
Adderss NVarChar(255) not null
Phone NVarChar(12) not null
Create Store Procedure "InsertData_Employee_SP":
CREATE PROCEDURE InsertData_Employee_SP
(
@xmlString VARCHAR(MAX)
)
AS
BEGIN
DECLARE @xmlHandle INT
DECLARE @stagingTable TABLE
(
[Name] VARCHAR(50),
[Address] VARCHAR(50),
[Phone] VARCHAR(50)
)
EXEC sp_xml_preparedocument @xmlHandle output, @xmlString
INSERT INTO @stagingTable
SELECT [Name],
[Address],
[Phone]
FROM OPENXML (@xmlHandle, '/DataTable',1)
WITH (
[Name] varchar(50) '@Name',
[Address] varchar(50) '@Address',
[Phone] varchar(50) '@Phone'
)
INSERT INTO EmployeeInfo([Name], [Address], [Phone])
(SELECT [Name] , [Address],[Phone]FROM @stagingTable)
EXEC sp_xml_removedocument @xmlHandle
END
Code in C#:
Sample Data Table
DataTable dt = new DataTable();
dt.Columns.Add("Name");
dt.Columns.Add("Address");
dt.Columns.Add("Phone");
dt.Rows.Add("Sujoy Santra", "Kolkata", "123456890");
dt.Rows.Add("Sanjoy Roy", "Kolkata", "99999900");
dt.Rows.Add("Dilip Pal", "Mumbai", "9876543210");
Convert data table to XML:
private static string ConvertToXML(DataTable dt)
{
DataSet dsBuildSQL = new DataSet();
StringBuilder sbSQL;
StringWriter swSQL;
string XMLformat;
try
{
sbSQL = new StringBuilder();
swSQL = new StringWriter(sbSQL);
dsBuildSQL.Merge(dt, true, MissingSchemaAction.AddWithKey);
dsBuildSQL.Tables[0].TableName = "DataTable";
foreach (DataColumn col in dsBuildSQL.Tables[0].Columns)
{
col.ColumnMapping = MappingType.Attribute;
}
dsBuildSQL.WriteXml(swSQL, XmlWriteMode.WriteSchema);
XMLformat = sbSQL.ToString();
return XMLformat;
}
catch (Exception sysException)
{
throw sysException;
}
}
Now Insert the data table to database:
SqlConnection conn = newSqlConnection(WebConfigurationManager.ConnectionStrings["connection"].ToString());
SqlCommand command = new SqlCommand("InsertData_Employee_SP '" + xmlData + "'", conn);
conn.Open();
command.ExecuteNonQuery();
conn.Close();
Create Table "EmployeeInfo":
Name NVarChar(255) not null
Adderss NVarChar(255) not null
Phone NVarChar(12) not null
Create Store Procedure "InsertData_Employee_SP":
CREATE PROCEDURE InsertData_Employee_SP
(
@xmlString VARCHAR(MAX)
)
AS
BEGIN
DECLARE @xmlHandle INT
DECLARE @stagingTable TABLE
(
[Name] VARCHAR(50),
[Address] VARCHAR(50),
[Phone] VARCHAR(50)
)
EXEC sp_xml_preparedocument @xmlHandle output, @xmlString
INSERT INTO @stagingTable
SELECT [Name],
[Address],
[Phone]
FROM OPENXML (@xmlHandle, '/DataTable',1)
WITH (
[Name] varchar(50) '@Name',
[Address] varchar(50) '@Address',
[Phone] varchar(50) '@Phone'
)
INSERT INTO EmployeeInfo([Name], [Address], [Phone])
(SELECT [Name] , [Address],[Phone]FROM @stagingTable)
EXEC sp_xml_removedocument @xmlHandle
END
Code in C#:
Sample Data Table
DataTable dt = new DataTable();
dt.Columns.Add("Name");
dt.Columns.Add("Address");
dt.Columns.Add("Phone");
dt.Rows.Add("Sujoy Santra", "Kolkata", "123456890");
dt.Rows.Add("Sanjoy Roy", "Kolkata", "99999900");
dt.Rows.Add("Dilip Pal", "Mumbai", "9876543210");
Convert data table to XML:
private static string ConvertToXML(DataTable dt)
{
DataSet dsBuildSQL = new DataSet();
StringBuilder sbSQL;
StringWriter swSQL;
string XMLformat;
try
{
sbSQL = new StringBuilder();
swSQL = new StringWriter(sbSQL);
dsBuildSQL.Merge(dt, true, MissingSchemaAction.AddWithKey);
dsBuildSQL.Tables[0].TableName = "DataTable";
foreach (DataColumn col in dsBuildSQL.Tables[0].Columns)
{
col.ColumnMapping = MappingType.Attribute;
}
dsBuildSQL.WriteXml(swSQL, XmlWriteMode.WriteSchema);
XMLformat = sbSQL.ToString();
return XMLformat;
}
catch (Exception sysException)
{
throw sysException;
}
}
Now Insert the data table to database:
SqlConnection conn = newSqlConnection(WebConfigurationManager.ConnectionStrings["connection"].ToString());
SqlCommand command = new SqlCommand("InsertData_Employee_SP '" + xmlData + "'", conn);
conn.Open();
command.ExecuteNonQuery();
conn.Close();