Through this article, I would like to explain how to insert data into the SQL Server database from an Excel using stored procedure with help of XML for bulk data insertion using the Windows desktop application and Visual Studio 2017. I am sure there are many different ways to do that; here, I am looking at the very basics to keep things simple.
Create a project for a Windows desktop application. Insert a button to export an excel file, and we are going to write the C# code for exporting the excel data on the button click event.
Now go to nuget package and install ExcelDataReader and install this to our project so we can use the features of this package.
Next the coding part for the button click.
private void Button1_Click(object sender, EventArgs e) {
OpenFileDialog ope = new OpenFileDialog {
Filter = "Excel Files|*.xls;*.xlsx;*.xlsm"
};
if (ope.ShowDialog() == DialogResult.Cancel) {
return;
}
FileStream stream = new FileStream(ope.FileName, FileMode.Open);
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
DataSet result = excelReader.AsDataSet();
DataTable firstTable = result.Tables[0];
firstTable.Rows[0].Delete();
System.IO.StringWriter writer = new System.IO.StringWriter();
firstTable.WriteXml(writer, XmlWriteMode.WriteSchema, false);
string res = writer.ToString();
DataTable checkData = new DataTable();
checkData = CheckXml(res);
if (checkData.Rows.Count > 0) {
lblDuplication.Text = "Found Duplication on data provided. Cannot be saved to database.";
} else {
try {
using(SqlConnection con = new SqlConnection(@ "Data Source=xxxxxxxxxxxx;Initial Catalog=Test;Integrated Security=True")) {
using(SqlCommand cmd = new SqlCommand("InsertXML")) {
cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@xml", res);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
lblDuplication.Text = "Saved Successfully";
} catch (Exception) {
lblDuplication.Text = "Found an error in program. Contact IT Admin";
}
}
}
private DataTable CheckXml(string xml) {
DataSet ds = new DataSet();
DataTable dt = new DataTable();
using(SqlConnection con = new SqlConnection(@ "Data Source=xxxxxxxxxxxxxxx;Initial Catalog=Test;Integrated Security=True")) {
using(SqlCommand cmd = new SqlCommand("CheckXML")) {
cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@xml", xml);
con.Open();
SqlDataAdapter sda = new SqlDataAdapter {
SelectCommand = cmd
};
sda.Fill(dt);
con.Close();
return dt;
}
}
}
Here I am adding the stored procedure to handle the XML data insertion to DB.
First check the duplicate data.
USE [Test]
go
/****** Object: StoredProcedure [dbo].[CheckXML] Script Date: 1/13/2019 11:41:17 PM ******/
SET ansi_nulls ON
go
SET quoted_identifier ON
go
ALTER PROCEDURE [dbo].[Checkxml] @xml XML
AS
BEGIN
SET nocount ON;
CREATE TABLE #temp11
(
test_id NCHAR(10),
test_name VARCHAR(50),
test_surname VARCHAR(50),
test_age INT
)
INSERT INTO #temp11
SELECT customer.value('(Column1/text())[1]', 'nchar(10)') AS test_id,
customer.value('(Column2/text())[1]', 'varchar(50)') AS test_name,
customer.value('(Column3/text())[1]', 'varchar(50)') AS
test_surname,
customer.value('(Column4/text())[1]', 'int') AS test_age
FROM @xml.nodes('/NewDataSet/Sheet1')AS TEMPTABLE(customer)
SELECT *
INTO #temp1
FROM (SELECT *
FROM #temp11
UNION ALL
SELECT *
FROM test1) AS temp
SELECT *
INTO #temp2
FROM (SELECT Row_number()
OVER(
partition BY test_id, test_name, test_surname, test_age
ORDER BY test_id) AS rownum,
*
FROM #temp1) AS temp
SELECT *
FROM #temp2
WHERE rownum > 1
END
--
-- select * from test1
Next run the procedure for inserting data to DB.
USE [Test]
go
/****** Object: StoredProcedure [dbo].[InsertXML] Script Date: 1/13/2019 11:41:37 PM ******/
SET ansi_nulls ON
go
SET quoted_identifier ON
go
ALTER PROCEDURE [dbo].[Insertxml] @xml XML
AS
BEGIN
SET nocount ON;
INSERT INTO test1
SELECT customer.value('(Column1/text())[1]', 'nchar(10)') AS test_id,
customer.value('(Column2/text())[1]', 'varchar(50)') AS test_name,
customer.value('(Column3/text())[1]', 'varchar(50)') AS
test_surname,
customer.value('(Column4/text())[1]', 'int') AS test_age
FROM @xml.nodes('/NewDataSet/Sheet1')AS TEMPTABLE(customer)
END