Import excel data from Microsoft excel to SQL Server usin C sharp .net

Question

User524418 posted

Hi

Could anyone help me with the sample code in C sharp to import data from excel to SQL server 2005 using C sharp. I have a table called Accounting Calendar ACCT_DATE with 10 columns I have the excel file called example.xls with the same column name as in SQL Server.Please help me with the sample code in C sharp

Thanks

 

 

Answer 1

User-305336156 posted

 Here's a simple way to do it without the need to have excel installed

http://davidhayden.com/blog/dave/archive/2006/05/26/2973.aspx

 

Hope this helps

Jaime

Answer 2

User524418 posted

I have 4 projects in my solution one defining Business objects,Logic,data layer and web

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

namespace TargetCorp.AP3.OutboundDatabase.BusinessObjects

{

public class AccountingDate

{

public AccountingDate() { }public DateTime AccountingD

{

get { return accountingD; }

set { accountingD = value; }

}

public int AccountingYearNumber

{

get { return accountingYearNumber; }set { accountingYearNumber = value; }

}

public int AccountingMonthNumber

{

get { return accountingMonthNumber; }

set { accountingMonthNumber = value; }

}

public string AccountingMonthName

{

get { return accountingMonthName; }set { accountingMonthName = value; }

}

public DateTime WeekBeginDate

{

get { return weekBeginDate; }

set { weekBeginDate = value; }

}

public DateTime WeekendDate

{

get { return weekendDate; }set { weekendDate = value; }

}

public int AccountingWeekNumber

{

get { return accountingWeekNumber; }

set { accountingWeekNumber = value; }

}

 

public int AccountingMonthWeekNumber

{

get{ return accountingMonthWeekNumber;}set { accountingMonthWeekNumber = value; }

}

public int AccountingQuarterNumber

{

get { return accountingQuarterNumber; }

set { accountingQuarterNumber = value; }

}

 

public int AccountingQuarterWeekNumber

{

get { return accountingQuarterWeekNumber; }set { accountingQuarterWeekNumber = value; }

}

private DateTime accountingD;

private int accountingYearNumber;

private int accountingMonthNumber;

private string accountingMonthName;

private DateTime weekBeginDate;

private DateTime weekendDate;

private int accountingWeekNumber;

private int accountingMonthWeekNumber;

private int accountingQuarterNumber;

private int accountingQuarterWeekNumber;

}

}

 

 

 

 

DA.cs

 

using System;

using System.Collections.Generic;

using System.Text;

using System.Data.Common;

using Microsoft.Practices.EnterpriseLibrary.Data;

using System.Diagnostics;

using System.Xml;

using System.Collections;

using System.Configuration;

using System.Data;

using System.Data.OleDb;//This namespace is mainly used for dealing with Excel sheet data

using System.Data.SqlClient;

using System.Linq;

using System.Web;

using System.Xml.Linq;

using TargetCorp.AP3.OutboundDatabase.BusinessObjects;

namespace TargetCorp.AP3.OutboundDatabase.DataAccess

{

// private string fileName;

// private string folderName;

// private DataSet dsCSV = new DataSet();

// private DataSet ConnectFile()

// {

// DataSet ds = new DataSet();

// try

// {

// string ConnectionString, CommandText;

// OleDbConnection conn;

// OleDbCommand Command;

// ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + folderName + ";Extended Properties='text;HDR=Yes;FMT=Delimited'";

// CommandText = "select * from [" + fileName + "]";

// using (conn = new System.Data.OleDb.OleDbConnection(ConnectionString))

// {

// Command = new System.Data.OleDb.OleDbCommand(CommandText, conn);

// conn.Open();

// System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter(CommandText, conn);

// da.Fill(ds, "FASClient");

// }

// }

// catch (Exception ex)

// {

// MessageBox.Show(ex.Message.ToString());

// }

// return ds;

// }

// private void btnSelectFile_Click(object sender, EventArgs e)

// {

// OpenFileDialog fileLookUp = new OpenFileDialog();

// fileLookUp.Title = "Get File";

// fileLookUp.Filter = "Files (*.csv)|*.csv|All Files (*.*)|*.*";

// if (fileLookUp.ShowDialog() == DialogResult.OK)

// {

// txtFileName.Text = fileLookUp.FileName;

// fileName = Path.GetFileName(txtFileName.Text);

// folderName = Path.GetDirectoryName(txtFileName.Text);

// }

// }

// private void btnReadFile_Click(object sender, EventArgs e)

// {

// if (string.IsNullOrEmpty(txtFileName.Text))

// lblError.Text = "Please select the 'CSV file' and click 'Process'.";

// else

// {

// dsCSV = ConnectFile();

// if (dsCSV.Tables.Count > 0 && dsCSV.Tables[0] != null)

// {

// dgCSV.DataSource = dsCSV.Tables[0];

// }

// }

// }

// private void btnProcess_Click(object sender, EventArgs e)

// {

// string xmldata = dsCSV.GetXml().ToString();

// SqlConnection oSqlConn = null;

// SqlCommand oSqlCmd = null;

// try

// {

// string cConnStr = string.Empty;

// ConnectionStringSettings connSettings = ConfigurationManager.ConnectionStrings["SQLConn"];

// if (connSettings != null)

// cConnStr = connSettings.ConnectionString;

// using (oSqlConn = new SqlConnection(cConnStr))

// {

// oSqlConn.Open();

// // Create a command to select the iid for HRCCountry table

// oSqlCmd = new SqlCommand("usp_Import_Client", oSqlConn);

// oSqlCmd.CommandType = CommandType.StoredProcedure;

// oSqlCmd.CommandTimeout = 0;

// oSqlCmd.Parameters.AddWithValue("@cFasClient", xmldata);

// int recsAffected = oSqlCmd.ExecuteNonQuery();

// MessageBox.Show("Successfully processed the records");

// }

// }

// catch (SqlException sqlex)

// {

// MessageBox.Show(sqlex.Message.ToString());

// }

// catch (Exception ex)

// {

// MessageBox.Show(ex.Message.ToString());

// }

// }

//}

// public class AccountingDateDA

// {

// Database db = DatabaseFactory.CreateDatabase("OutboundDatabaseDataAccess");

// string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=example.xls;ExtendedProperties=""Excel 8.0;HDR=YES;""";

// DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");

// using (DbConnection connection = factory.CreateConnection())

// {

// connection.ConnectionString = connectionString;

// using (DbCommand command = connection.CreateCommand())

// {

// // Cities$ comes from the name of the worksheet

// command.CommandText = "SELECT ACCT_D,ACCT_YR_I FROM [Sheet1$]";

// connection.Open();

// using (DbDataReader dr = command.ExecuteReader())

// {

// while (dr.Read())

// {

// Debug.WriteLine(dr["ACCT_D"].ToString());

// }

// }

// }

// }

// }

// }

//}

Answer 3

User524418 posted

I have 4 projects in my solution one defining Business objects,Logic,data layer and web

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

namespace TargetCorp.AP3.OutboundDatabase.BusinessObjects

{

public class AccountingDate

{

public AccountingDate() { }public DateTime AccountingD

{

get { return accountingD; }

set { accountingD = value; }

}

public int AccountingYearNumber

{

get { return accountingYearNumber; }set { accountingYearNumber = value; }

}

public int AccountingMonthNumber

{

get { return accountingMonthNumber; }

set { accountingMonthNumber = value; }

}

public string AccountingMonthName

{

get { return accountingMonthName; }set { accountingMonthName = value; }

}

public DateTime WeekBeginDate

{

get { return weekBeginDate; }

set { weekBeginDate = value; }

}

public DateTime WeekendDate

{

get { return weekendDate; }set { weekendDate = value; }

}

public int AccountingWeekNumber

{

get { return accountingWeekNumber; }

set { accountingWeekNumber = value; }

}

 

public int AccountingMonthWeekNumber

{

get{ return accountingMonthWeekNumber;}set { accountingMonthWeekNumber = value; }

}

public int AccountingQuarterNumber

{

get { return accountingQuarterNumber; }

set { accountingQuarterNumber = value; }

}

 

public int AccountingQuarterWeekNumber

{

get { return accountingQuarterWeekNumber; }set { accountingQuarterWeekNumber = value; }

}

private DateTime accountingD;

private int accountingYearNumber;

private int accountingMonthNumber;

private string accountingMonthName;

private DateTime weekBeginDate;

private DateTime weekendDate;

private int accountingWeekNumber;

private int accountingMonthWeekNumber;

private int accountingQuarterNumber;

private int accountingQuarterWeekNumber;

}

}

 

 

 

 

DA.cs

 

using System;

using System.Collections.Generic;

using System.Text;

using System.Data.Common;

using Microsoft.Practices.EnterpriseLibrary.Data;

using System.Diagnostics;

using System.Xml;

using System.Collections;

using System.Configuration;

using System.Data;

using System.Data.OleDb;//This namespace is mainly used for dealing with Excel sheet data

using System.Data.SqlClient;

using System.Linq;

using System.Web;

using System.Xml.Linq;

using TargetCorp.AP3.OutboundDatabase.BusinessObjects;

namespace TargetCorp.AP3.OutboundDatabase.DataAccess

{

// private string fileName;

// private string folderName;

// private DataSet dsCSV = new DataSet();

// private DataSet ConnectFile()

// {

// DataSet ds = new DataSet();

// try

// {

// string ConnectionString, CommandText;

// OleDbConnection conn;

// OleDbCommand Command;

// ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + folderName + ";Extended Properties='text;HDR=Yes;FMT=Delimited'";

// CommandText = "select * from [" + fileName + "]";

// using (conn = new System.Data.OleDb.OleDbConnection(ConnectionString))

// {

// Command = new System.Data.OleDb.OleDbCommand(CommandText, conn);

// conn.Open();

// System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter(CommandText, conn);

// da.Fill(ds, "FASClient");

// }

// }

// catch (Exception ex)

// {

// MessageBox.Show(ex.Message.ToString());

// }

// return ds;

// }

// private void btnSelectFile_Click(object sender, EventArgs e)

// {

// OpenFileDialog fileLookUp = new OpenFileDialog();

// fileLookUp.Title = "Get File";

// fileLookUp.Filter = "Files (*.csv)|*.csv|All Files (*.*)|*.*";

// if (fileLookUp.ShowDialog() == DialogResult.OK)

// {

// txtFileName.Text = fileLookUp.FileName;

// fileName = Path.GetFileName(txtFileName.Text);

// folderName = Path.GetDirectoryName(txtFileName.Text);

// }

// }

// private void btnReadFile_Click(object sender, EventArgs e)

// {

// if (string.IsNullOrEmpty(txtFileName.Text))

// lblError.Text = "Please select the 'CSV file' and click 'Process'.";

// else

// {

// dsCSV = ConnectFile();

// if (dsCSV.Tables.Count > 0 && dsCSV.Tables[0] != null)

// {

// dgCSV.DataSource = dsCSV.Tables[0];

// }

// }

// }

// private void btnProcess_Click(object sender, EventArgs e)

// {

// string xmldata = dsCSV.GetXml().ToString();

// SqlConnection oSqlConn = null;

// SqlCommand oSqlCmd = null;

// try

// {

// string cConnStr = string.Empty;

// ConnectionStringSettings connSettings = ConfigurationManager.ConnectionStrings["SQLConn"];

// if (connSettings != null)

// cConnStr = connSettings.ConnectionString;

// using (oSqlConn = new SqlConnection(cConnStr))

// {

// oSqlConn.Open();

// // Create a command to select the iid for HRCCountry table

// oSqlCmd = new SqlCommand("usp_Import_Client", oSqlConn);

// oSqlCmd.CommandType = CommandType.StoredProcedure;

// oSqlCmd.CommandTimeout = 0;

// oSqlCmd.Parameters.AddWithValue("@cFasClient", xmldata);

// int recsAffected = oSqlCmd.ExecuteNonQuery();

// MessageBox.Show("Successfully processed the records");

// }

// }

// catch (SqlException sqlex)

// {

// MessageBox.Show(sqlex.Message.ToString());

// }

// catch (Exception ex)

// {

// MessageBox.Show(ex.Message.ToString());

// }

// }

//}

// public class AccountingDateDA

// {

// Database db = DatabaseFactory.CreateDatabase("OutboundDatabaseDataAccess");

// string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=example.xls;ExtendedProperties=""Excel 8.0;HDR=YES;""";

// DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");

// using (DbConnection connection = factory.CreateConnection())

// {

// connection.ConnectionString = connectionString;

// using (DbCommand command = connection.CreateCommand())

// {

// // Cities$ comes from the name of the worksheet

// command.CommandText = "SELECT ACCT_D,ACCT_YR_I FROM [Sheet1$]";

// connection.Open();

// using (DbDataReader dr = command.ExecuteReader())

// {

// while (dr.Read())

// {

// Debug.WriteLine(dr["ACCT_D"].ToString());

// }

// }

// }

// }

// }

// }

//}

Answer 4

User524418 posted

I have 4 projects in my solution one defining Business objects,Logic,data layer and web

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

namespace TargetCorp.AP3.OutboundDatabase.BusinessObjects

{

public class AccountingDate

{

public AccountingDate() { }public DateTime AccountingD

{

get { return accountingD; }

set { accountingD = value; }

}

public int AccountingYearNumber

{

get { return accountingYearNumber; }set { accountingYearNumber = value; }

}

public int AccountingMonthNumber

{

get { return accountingMonthNumber; }

set { accountingMonthNumber = value; }

}

public string AccountingMonthName

{

get { return accountingMonthName; }set { accountingMonthName = value; }

}

public DateTime WeekBeginDate

{

get { return weekBeginDate; }

set { weekBeginDate = value; }

}

public DateTime WeekendDate

{

get { return weekendDate; }set { weekendDate = value; }

}

public int AccountingWeekNumber

{

get { return accountingWeekNumber; }

set { accountingWeekNumber = value; }

}

 

public int AccountingMonthWeekNumber

{

get{ return accountingMonthWeekNumber;}set { accountingMonthWeekNumber = value; }

}

public int AccountingQuarterNumber

{

get { return accountingQuarterNumber; }

set { accountingQuarterNumber = value; }

}

 

public int AccountingQuarterWeekNumber

{

get { return accountingQuarterWeekNumber; }set { accountingQuarterWeekNumber = value; }

}

private DateTime accountingD;

private int accountingYearNumber;

private int accountingMonthNumber;

private string accountingMonthName;

private DateTime weekBeginDate;

private DateTime weekendDate;

private int accountingWeekNumber;

private int accountingMonthWeekNumber;

private int accountingQuarterNumber;

private int accountingQuarterWeekNumber;

}

}

 

 

 

 

DA.cs

 

using System;

using System.Collections.Generic;

using System.Text;

using System.Data.Common;

using Microsoft.Practices.EnterpriseLibrary.Data;

using System.Diagnostics;

using System.Xml;

using System.Collections;

using System.Configuration;

using System.Data;

using System.Data.OleDb;//This namespace is mainly used for dealing with Excel sheet data

using System.Data.SqlClient;

using System.Linq;

using System.Web;

using System.Xml.Linq;

using TargetCorp.AP3.OutboundDatabase.BusinessObjects;

namespace TargetCorp.AP3.OutboundDatabase.DataAccess

{

// private string fileName;

// private string folderName;

// private DataSet dsCSV = new DataSet();

// private DataSet ConnectFile()

// {

// DataSet ds = new DataSet();

// try

// {

// string ConnectionString, CommandText;

// OleDbConnection conn;

// OleDbCommand Command;

// ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + folderName + ";Extended Properties='text;HDR=Yes;FMT=Delimited'";

// CommandText = "select * from [" + fileName + "]";

// using (conn = new System.Data.OleDb.OleDbConnection(ConnectionString))

// {

// Command = new System.Data.OleDb.OleDbCommand(CommandText, conn);

// conn.Open();

// System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter(CommandText, conn);

// da.Fill(ds, "FASClient");

// }

// }

// catch (Exception ex)

// {

// MessageBox.Show(ex.Message.ToString());

// }

// return ds;

// }

// private void btnSelectFile_Click(object sender, EventArgs e)

// {

// OpenFileDialog fileLookUp = new OpenFileDialog();

// fileLookUp.Title = "Get File";

// fileLookUp.Filter = "Files (*.csv)|*.csv|All Files (*.*)|*.*";

// if (fileLookUp.ShowDialog() == DialogResult.OK)

// {

// txtFileName.Text = fileLookUp.FileName;

// fileName = Path.GetFileName(txtFileName.Text);

// folderName = Path.GetDirectoryName(txtFileName.Text);

// }

// }

// private void btnReadFile_Click(object sender, EventArgs e)

// {

// if (string.IsNullOrEmpty(txtFileName.Text))

// lblError.Text = "Please select the 'CSV file' and click 'Process'.";

// else

// {

// dsCSV = ConnectFile();

// if (dsCSV.Tables.Count > 0 && dsCSV.Tables[0] != null)

// {

// dgCSV.DataSource = dsCSV.Tables[0];

// }

// }

// }

// private void btnProcess_Click(object sender, EventArgs e)

// {

// string xmldata = dsCSV.GetXml().ToString();

// SqlConnection oSqlConn = null;

// SqlCommand oSqlCmd = null;

// try

// {

// string cConnStr = string.Empty;

// ConnectionStringSettings connSettings = ConfigurationManager.ConnectionStrings["SQLConn"];

// if (connSettings != null)

// cConnStr = connSettings.ConnectionString;

// using (oSqlConn = new SqlConnection(cConnStr))

// {

// oSqlConn.Open();

// // Create a command to select the iid for HRCCountry table

// oSqlCmd = new SqlCommand("usp_Import_Client", oSqlConn);

// oSqlCmd.CommandType = CommandType.StoredProcedure;

// oSqlCmd.CommandTimeout = 0;

// oSqlCmd.Parameters.AddWithValue("@cFasClient", xmldata);

// int recsAffected = oSqlCmd.ExecuteNonQuery();

// MessageBox.Show("Successfully processed the records");

// }

// }

// catch (SqlException sqlex)

// {

// MessageBox.Show(sqlex.Message.ToString());

// }

// catch (Exception ex)

// {

// MessageBox.Show(ex.Message.ToString());

// }

// }

//}

// public class AccountingDateDA

// {

// Database db = DatabaseFactory.CreateDatabase("OutboundDatabaseDataAccess");

// string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=example.xls;ExtendedProperties=""Excel 8.0;HDR=YES;""";

// DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");

// using (DbConnection connection = factory.CreateConnection())

// {

// connection.ConnectionString = connectionString;

// using (DbCommand command = connection.CreateCommand())

// {

// // Cities$ comes from the name of the worksheet

// command.CommandText = "SELECT ACCT_D,ACCT_YR_I FROM [Sheet1$]";

// connection.Open();

// using (DbDataReader dr = command.ExecuteReader())

// {

// while (dr.Read())

// {

// Debug.WriteLine(dr["ACCT_D"].ToString());

// }

// }

// }

// }

// }

// }

//}