Save Data into Sql Server from Excel sheet

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:
string 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))
{
Connection.Open();
SqlTransaction Transaction = Connection.BeginTransaction();
Command.Transaction = Transaction;
DataAdapter.InsertCommand = CommandBuilder.GetInsertCommand();
dataSet.AcceptChanges();
int check = DataAdapter.Update(dataSet, "Amt1");
Transaction.Commit();
}
}

0 comments: