How to import data from excel file into dataset and then SQL db?

OleDbConnection con = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source= c:/file.xls; Extended Properties=Excel 8.0; ");
OleDbDataAdapter oadp1 = new OleDbDataAdapter("Select * from [sheet1$] where Id >0", con);
DataSet ods1 = new DataSet();
oadp1.Fill(ods1, "Data");
DataSet Dds1 = new DataSet();
Dds1.Tables.Add(new DataTable("NewData"));
Dds1.Tables[“NewData”].Columns.Add(new DataColumn("Id"));
Dds1.Tables[“NewData”].Columns.Add(new DataColumn("Name"));
for (int i = 0; i < ods1.Tables[“Data”].Rows.Count; i++)
{
    DataRow row = Dds1.Tables[“NewData”].NewRow();
    row[0] = ods1.Tables[“Data”].Rows[i][0];
    row[1] = ods1.Tables[“Data”].Rows[i][1];
    Dds1.Tables[“NewData”].Rows.Add(row);
}
SqlConnection con1 = new SqlConnection(“Password = 123; Persist Security Info = True; User ID = sa; Initial Catalog = DB; Data Source = .;”);
SqlDataAdapter adp = new SqlDataAdapter("select * from CompanyData", con1);
SqlCommandBuilder scb = new SqlCommandBuilder(adp);
adp.InsertCommand = scb.GetInsertCommand(true);
adp.Update(Dds1, "CompanyData");
Tagged . Bookmark the permalink.

Leave a Reply