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

  • 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

     

     

    Monday, January 26, 2009 3:36 PM

Answers

  • 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());

    // }

    // }

    // }

    // }

    // }

    // }

    //}

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, January 28, 2009 3:00 PM

All replies

  • 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

    Monday, January 26, 2009 4:03 PM
  • 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());

    // }

    // }

    // }

    // }

    // }

    // }

    //}

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, January 28, 2009 3:00 PM
  • 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());

    // }

    // }

    // }

    // }

    // }

    // }

    //}

    Wednesday, January 28, 2009 3:00 PM
  • 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());

    // }

    // }

    // }

    // }

    // }

    // }

    //}

    Wednesday, January 28, 2009 3:01 PM