how to store excel data into database by file upload in c# language

hi friends ,

               please help me in storing the excel data in to database by fileupload in c# language. ...

the excel containing columns   like    -----------
--------------------------------------------------------------------------- --------
regd no |    name |     branch |     address   |     fee-particulars .
-----------------------------------------------------------------------------------

and I will also maintain the same coloumns in database . so , once the excel file has been selected from file upload the corresponded column data in the excel sheet has to be stored in the corresponded columns in the database fields .

          thanks in advance......

protected void Button_Click(object sender, EventArgs e)

{

//Create connection string to Excel work book

string excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Basic1.xls;Extended Properties=""Excel 8.0;HDR=YES;""";

//Below is mydatabase connection

string strConnection = "Data Source=10.0.0.14;Initial Catalog=db_USPayBO;User Id=sa;Password=server@123";

//Create Connection to Excel work book

OleDbConnection excelConnection =new OleDbConnection(excelConnectionString);

//Create OleDbCommand to fetch data from Excel

OleDbCommand cmd = new OleDbCommand("Select * from [ZipCodes$]", excelConnection);

excelConnection.Open();

OleDbDataReader dReader;

dReader = cmd.ExecuteReader();

SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection);

sqlBulk.DestinationTableName = "tblZIPCodes";

//sqlBulk.ColumnMappings.Add("ID", "ID");

//sqlBulk.ColumnMappings.Add("Name", "Name");

sqlBulk.WriteToServer(dReader);

}

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Dorababu Meka16y ago
protected void Button_Click(object sender, EventArgs e)
    {
        //Create connection string to Excel work book

        string excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Basic1.xls;Extended Properties=""Excel 8.0;HDR=YES;""";
//Below is mydatabase connection
        string strConnection = "Data Source=10.0.0.14;Initial Catalog=db_USPayBO;User Id=sa;Password=server@123";
        //Create Connection to Excel work book
        OleDbConnection excelConnection =new OleDbConnection(excelConnectionString);

        //Create OleDbCommand to fetch data from Excel
        OleDbCommand cmd = new OleDbCommand("Select * from [ZipCodes$]", excelConnection);

        excelConnection.Open();
        OleDbDataReader dReader;
        dReader = cmd.ExecuteReader();

        SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection);
        sqlBulk.DestinationTableName = "tblZIPCodes";
        //sqlBulk.ColumnMappings.Add("ID", "ID");
        //sqlBulk.ColumnMappings.Add("Name", "Name");
        sqlBulk.WriteToServer(dReader);

    }
0