Save Insert Export Import Excel Data Into Sql Server SqlBulkCopy ASP.NET

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.



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



Other Posts:
1. Detecting Session Timeout and Redirect to Login Page in ASP.NET
2. JavaScript window.close() not working / does not work in firefox
3. Merging GridView Headers to have multiple Headers in GridView
4. Detect Browser refresh to avoid events getting fired again in ASP .NET
5. Search records in GridView and highlight result with AJAX

Other SQL Server articles:
The backup set holds a backup of a database other than the existing database-Sql Server Error 3154
Install configure and troubleshooting sql server reporting services 2005
Ms sql server bulk insert method to import bulk csv data into database

Tweet

If you like this post than join us or share