Save Data into Sql Server from Excel sheet

Posted by Lakhangarg under C# category on | Views : 6244
Data into SQL Server from Excel sheet (C#). First we will get the data from excel sheet using the select query and OLEDB as provider.
Connection string for OLEDB is:
tring file = "D:\\Amt.xls";
string constr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
file + ";Extended Properties=Excel 8.0;";

Query to fetch data from a excel sheet:
string query = "Select Name,Salary from [Sheet1$]";

This Code will help to fetch the data from excel sheet and save the same into dataset.
DataSet dataSet = new DataSet();
using (OleDbConnection Connection = new OleDbConnection(constr))
using (OleDbDataAdapter DataAdapter =
new OleDbDataAdapter(query, Connection))
DataAdapter.Fill(dataSet, "Amt1");
DataAdapter.AcceptChangesDuringFill = false;

Following lines of code will help us to save the data into SQL server DB that we get from excel sheet.
constr = "server=B0001;database=master;uid=sa;pwd=sa";
query = "Select Name,Salary from Amt";
using (SqlConnection Connection = new SqlConnection(constr))
using (SqlCommand Command = new SqlCommand(query, Connection))
using (SqlDataAdapter DataAdapter = new SqlDataAdapter(Command))
using (SqlCommandBuilder CommandBuilder =
new SqlCommandBuilder(DataAdapter))
SqlTransaction Transaction = Connection.BeginTransaction();
Command.Transaction = Transaction;
DataAdapter.InsertCommand = CommandBuilder.GetInsertCommand();
int check = DataAdapter.Update(dataSet, "Amt1");

Comments or Responses

Login to post response