Post date: Feb 11, 2011 9:55:14 AM
Column names must match in Excel and table
HDR=Yes because excel file has headers in its first line.
using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Data.OleDb;using System.Data;using System.Data.SqlClient;namespace LoadToDBFromExcel { class Program { static void Main(string[] args) { InsertToDbFromExcel("Customers"); InsertToDbFromExcel("Employees"); InsertToDbFromExcel("Orders"); } private static void InsertToDbFromExcel(string tableName) { DataSet ds = new DataSet(); using (OleDbConnection con = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Datafolder\\TestData.xls;Extended Properties=\"EXCEL 12.0;HDR=YES\"")) { con.Open(); using (OleDbCommand cmd = new OleDbCommand(string.Format("select * from [{0}$]", tableName), con)) { OleDbDataAdapter ad = new OleDbDataAdapter(cmd); ad.Fill(ds); con.Close(); } } using (SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=CustomerDB;Integrated Security=True")) { StringBuilder sb = new StringBuilder(); sb.AppendFormat(@" SET IDENTITY_INSERT {0} ON; INSERT {0}(", tableName); for (int i = 0; i < ds.Tables[0].Columns.Count; i++) { sb.Append(string.Format("{0}", ds.Tables[0].Columns[i].ColumnName)); if (i < ds.Tables[0].Columns.Count - 1) sb.AppendLine(", "); } sb.Append(")"); sb.Append(" VALUES("); for (int i = 0; i < ds.Tables[0].Columns.Count; i++) { sb.Append(string.Format("@{0}", ds.Tables[0].Columns[i].ColumnName)); if (i < ds.Tables[0].Columns.Count - 1) sb.AppendLine(", "); } sb.Append(")"); using (SqlCommand cmd = new SqlCommand(sb.ToString(), con)) { con.Open(); foreach (DataRow row in ds.Tables[0].Rows) { try { cmd.Parameters.Clear(); for (int i = 0; i < ds.Tables[0].Columns.Count; i++) { cmd.Parameters.AddWithValue(string.Format("@{0}", ds.Tables[0].Columns[i].ColumnName), row[i]); } cmd.ExecuteNonQuery(); } catch (Exception) { } } } } } }}