how to store the data table content to sql data base

I have a excel file.I Store the data from excel to a data table .Now i want to store the data table content to sql table .how to store the data table to data base ?

Am storing the data table content from excel by using below procedure.

string filePath = Server.MapPath("~/Files/") + Path.GetFileName(FileUpload1.PostedFile.FileName);

FileUpload1.SaveAs(filePath);

//string filePath=@"C:\Users\350154\Desktop\Sample.xlsx";

try

{

using (XLWorkbook workBook = new XLWorkbook(filePath))

{

//Read the first Sheet from Excel file.

IXLWorksheet workSheet = workBook.Worksheet(1);

//Create a new DataTable.

DataTable dt = new DataTable();

bool firstRow = true;

foreach (IXLRow row in workSheet.Rows())

{

if (firstRow)

{

foreach (IXLCell cell in row.Cells())

{

dt.Columns.Add(cell.Value.ToString());

}

firstRow = false;

}

else

{

//Add rows to DataTable.

dt.Rows.Add();

int i = 0;

foreach (IXLCell cell in row.Cells())

{

dt.Rows[dt.Rows.Count - 1][i] = cell.Value.ToString();

i++;

}

protected void Bulk_Insert(object sender, EventArgs e)
{
DataTable dt = new DataTable();
dt.Columns.AddRange(new DataColumn[3] { new DataColumn("Id", typeof(int)),
new DataColumn("Name", typeof(string)),
new DataColumn("Country",typeof(string)) });
foreach (GridViewRow row in GridView1.Rows)
{
if ((row.FindControl("CheckBox1") as CheckBox).Checked)
{
int id = int.Parse(row.Cells[1].Text);
string name = row.Cells[2].Text;
string country = row.Cells[3].Text;
dt.Rows.Add(id, name, country);
}
}
if (dt.Rows.Count > 0)
{
string consString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(consString))
{
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
{
//Set the database table name
sqlBulkCopy.DestinationTableName = "dbo.Customers";
//[OPTIONAL]: Map the DataTable columns with that of the database table
sqlBulkCopy.ColumnMappings.Add("Id", "CustomerId");
sqlBulkCopy.ColumnMappings.Add("Name", "Name");
sqlBulkCopy.ColumnMappings.Add("Country", "Country");
con.Open();
sqlBulkCopy.WriteToServer(dt);
con.Close();
}
}
}
}

Hi.

You can do like this,

Create a User-Defined TableType in your database:

CREATE TYPE [dbo].[MyTableType] AS TABLE(

[Id] int NOT NULL,

[Name] [nvarchar](128) NULL

)

and define a parameter in your Stored Ptocedure:

CREATE PROCEDURE [dbo].[InsertTable]

@myTableType MyTableType readonly

BEGIN

insert into [dbo].Records select * from @myTableType

END

and send your DataTable directly to sql server:

using (var command = new SqlCommand("InsertTable") {CommandType = CommandType.StoredProcedure})

{

var dt = new DataTable(); //create your own data table

command.Parameters.Add(new SqlParameter("@myTableType", dt));

SqlHelper.Exec(command);

}

thanks.

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Ananth G9y ago
1. protected void Bulk_Insert(object sender, EventArgs e)
2. {
3. DataTable dt = new DataTable();
4. dt.Columns.AddRange(new DataColumn[3] { new DataColumn("Id", typeof(int)),
5. new DataColumn("Name", typeof(string)),
6. new DataColumn("Country",typeof(string)) });
7. foreach (GridViewRow row in GridView1.Rows)
8. {
9. if ((row.FindControl("CheckBox1") as CheckBox).Checked)
10. {
11. int id = int.Parse(row.Cells[1].Text);
12. string name = row.Cells[2].Text;
13. string country = row.Cells[3].Text;
14. dt.Rows.Add(id, name, country);
15. }
16. }
17. if (dt.Rows.Count > 0)
18. {
19. string consString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
20. using (SqlConnection con = new SqlConnection(consString))
21. {
22. using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
23. {
24. //Set the database table name
25. sqlBulkCopy.DestinationTableName = "dbo.Customers";
27. //[OPTIONAL]: Map the DataTable columns with that of the database table
28. sqlBulkCopy.ColumnMappings.Add("Id", "CustomerId");
29. sqlBulkCopy.ColumnMappings.Add("Name", "Name");
30. sqlBulkCopy.ColumnMappings.Add("Country", "Country");
31. con.Open();
32. sqlBulkCopy.WriteToServer(dt);
33. con.Close();
34. }
35. }
36. }
37. }
please refer below url
http://www.aspsnippets.com/Articles/SqlBulkCopy-Bulk-Copy-data-from-DataTable-DataSet-to-SQL-Server-Table-using-C-and-VBNet.aspx