Simple Excel Data read and save with C#.NET

Today i will demonstrate the process of Excel data read with both DataReader & DataSet . Though the Code is not optimize but its very simple to read a excel file in OLEDB.

In the example i have hard-coded the Excel file location named "Book1.xls", and the selected sheet is "Sheet1". We also have a database and a table in it named "TableInfo" where we will insert data after reading from excel file.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Collections;

namespace ExcelExport
{
    class GenerateExcel
    {
        public ArrayList ColumnNames;

        public DataSet ReadExcell()
        {
            String conStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + @"Data Source=C:\Documents and Settings\tanowar\Desktop\Excel\WinApp\ExcelExport\ExcelExport\Book1.xls;" + "Extended Properties=Excel 8.0;";
            string sqlConString = "Data Source=DtaBase;Initial Catalog=GPXPO;User ID=User;Password=XXX ";

            SqlConnection sqlCon = new SqlConnection(sqlConString);          

            OleDbConnection oleCon = new OleDbConnection(conStr);
            DataSet ds = new DataSet();

            try
            {
                #region DataSet

                oleCon.Open();                
                OleDbCommand oleCmd = new OleDbCommand("select * from [Sheet1$]", oleCon);

                OleDbDataAdapter oleAd = new OleDbDataAdapter();
                oleAd.SelectCommand = oleCmd;
                oleAd.Fill(ds);
                oleCon.Close();

                #endregion


                #region DataReader

                OleDbDataReader reader = oleCmd.ExecuteReader();
                while (reader.Read())
                {
                    sqlCon.Open();                    
                    SqlCommand sqlCmd = new SqlCommand("insert into TableInfo (AllotNo,BankCode,LotteryNo,BONo,Name,Shares)values('"+reader[0].ToString() +"','"+reader[1].ToString() +"','"+reader[2].ToString() +"','"+reader[3].ToString() +"','"+reader[4].ToString() +"',"+reader[5].ToString() +")",sqlCon);
                    sqlCmd.ExecuteNonQuery();
                    sqlCon.Close();
                }
               #endregion

            }
            catch (Exception exc)
            {
                exc.ToString();                
            }
            return ds;
        }
    }
}