From:
http://www.eggheadcafe.com/community/sql-server/13/10351461/how-to-import-excel-data-to-a-sql-table.aspx[
^]
2. Now design a tStudent table in SQL server
Create Table
(
StudentName varchar(64),
RollNo varchar(16),
Course varchar(32),
)
your ms excel sheet and SQL table is ready, now its time to write c# code to import the excel sheet intotStudent table
3.
Add these two name space in your class file
using System.Data.OleDb;
using System.Data.SqlClient;
Use following code
public void importDataFromExcel(string excelFilePath)
{
string sSQLTable = "tDataMigrationTable";
string myExcelDataQuery = "Select StudentName,RollNo,Course from [Sheet1$]";
try
{
string sExcelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelFilePath + ";Extended Properties=" + "\"Excel 8.0;HDR=YES;\"";
string sSqlConnectionString = "SERVER=MyDatabaseServerName;USER ID=DBUserId;PASSWORD=DBUserPassword;DATABASE=DatabaseName;CONNECTION RESET=FALSE";
string sClearSQL = "DELETE FROM " + sSQLTable;
SqlConnection SqlConn = new SqlConnection(sSqlConnectionString);
SqlCommand SqlCmd = new SqlCommand(sClearSQL, SqlConn);
SqlConn.Open();
SqlCmd.ExecuteNonQuery();
SqlConn.Close();
OleDbConnection OleDbConn = new OleDbConnection(sExcelConnectionString);
OleDbCommand OleDbCmd = new OleDbCommand(myExcelDataQuery, OleDbConn);
OleDbConn.Open();
OleDbDataReader dr = OleDbCmd.ExecuteReader();
SqlBulkCopy bulkCopy = new SqlBulkCopy(sSqlConnectionString);
bulkCopy.DestinationTableName = sSQLTable;
while (dr.Read())
{
bulkCopy.WriteToServer(dr);
}
OleDbConn.Close();
}
catch (Exception ex)
{
}
}