Insert excel data into DB by using C#.NET

In this example i am going to describe how to Import or insert data into Sql server from Excel spreadsheet using sqlbulkcopy method.

Create a Excel workbook as shown in image below and insert some data into it.

Create a table in SQL database as shown in below

ColumnName Data Type
ID int
Name varchar(50)
Location varchar(50)


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");

Below i am writing the code without sqlbulk by using dataset.

protected void Button2_Click(object sender, EventArgs e)
{
DataSet dUser_Info = new DataSet();

string excelConnectionString = String.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;""", "C:\\Details.xls");

string query = String.Format("Select [Id],[Name],[Location] from [Sheet1$]");
OleDbConnection oleCon = new OleDbConnection(excelConnectionString);
OleDbDataAdapter oleAdp = new OleDbDataAdapter(query, oleCon);
try
{
oleAdp.Fill(dUser_Info);
}
catch (OleDbException ex)
{
if (ex.Message == "No value given for one or more required parameters.")
{
//paramter name is invalid
return;
}
}

SqlConnection DBConn = new SqlConnection(ConfigurationSettings.AppSettings["ConnectString"]);

DBConn.Open();

foreach (DataRow dr in dUser_Info.Tables[0].Rows)
{
SqlCommand DBQuery = new System.Data.SqlClient.SqlCommand();


DBQuery.Connection = DBConn;

DBQuery.CommandText = "insert into tbuserinfo (Id,Name,Location) values('" + dr["Id"].ToString() + "','" + dr["Name"].ToString() + "','" + dr["Location"].ToString()+"'";

DBQuery.ExecuteNonQuery();

}

DBConn.Close();

}