This post explains How To Save Insert Or Export Import Excel Data In to Sql Server Database Table Using SqlBulkCopy In ASP.NET
First of all create a Excel workbook as shown in image below and insert some data into it.
Export Import Insert Excel Data Into Sql Server Using SqlBulkCopy
Create a table in SQL database with following schema
Now write this code to insert data into SQL table
public partial class _Default : System.Web.UI.Page
{
string strConnection = ConfigurationManager.ConnectionStrings
["ConnectionString"].ConnectionString;
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button1_Click(object sender, EventArgs e)
{
//Create connection string to Excel work book
string excelConnectionString =
@"Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=C:\Details.xls;
Extended Properties=""Excel 8.0;HDR=YES;""";
//Create Connection to Excel work book
OleDbConnection excelConnection =
new OleDbConnection(excelConnectionString);
//Create OleDbCommand to fetch data from Excel
OleDbCommand cmd = new OleDbCommand
("Select [ID],[Name],[Location] from [Detail$]",
excelConnection);
excelConnection.Open();
OleDbDataReader dReader;
dReader = cmd.ExecuteReader();
SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection);
sqlBulk.DestinationTableName = "Details";
//sqlBulk.ColumnMappings.Add("ID", "ID");
//sqlBulk.ColumnMappings.Add("Name", "Name");
sqlBulk.WriteToServer(dReader);
}
}
If there are more columns in your database table or excel workbook and you want to insert data in some of them than you need to add ColumnMappings like this
sqlBulk.ColumnMappings.Add("ID", "ID");
sqlBulk.ColumnMappings.Add("Name", "Name");
End result will be like this
Hope this helps
Download the sample code attached
Comments
Post a Comment