Import Excel Data to Database Using C#

This Blog describe how to import Excel Data to a database using C# language.

Database Structure

Query:

Create table tbl_bulkupload(Product_ID int primary key,Product_Name varchar(100),Quantity nvarchar(50),Price money)

Design:

table design

Now create a Excel sheet using valid columns in a database.

excel sheet

Add these connecting strings to webconfig for creating an excel connection.

webconfig file

Bulkupload.aspx:

  1. <asp:Label ID=”lblproduct” runat=”server” Text=”Upload Products”></asp:Label>  
  2. <asp:FileUpload ID=”FileUploadProduct” runat=”server” />  
  3. <asp:Button ID=”btnupload” runat=”server” Text=”Upload” OnClick=”btnupload_Click” />  
  4. <asp:Label ID=”MsgAlert” runat=”server”></asp:Label>  
Screen Design:

upload products

Create a folder inside the project for storing a uploaded Excel file.

folder inside the project

Bulkupload.aspx.cs

Add these namespaces in C# page:

  1. using System.Data.Common;  
  2. using System.Data.OleDb;  
  3. using System.Data.SqlClient;  
  4. protected void btnupload_Click(object sender, EventArgs e)  
  5. {  
  6.    if (FileUploadProduct.HasFile)  
  7.    {  
  8.       try  
  9.       {  
  10.          string path = string.Concat(Server.MapPath(“~/Excel/” + FileUploadProduct.FileName));  
  11.          FileUploadProduct.SaveAs(path);  
  12.          string excelConnectionString = string.Format(“Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 8.0″, path);  
  13.          OleDbConnection connection = new OleDbConnection();  
  14.          connection.ConnectionString = excelConnectionString;  
  15.          OleDbCommand command = new OleDbCommand(“select * from [Sheet1$]”, connection);  
  16.          connection.Open();  
  17.          DbDataReader dr = command.ExecuteReader();  
  18.          string sqlConnectionString = @”Data Source=.;Initial Catalog=Wordpress;Integrated Security=True”;  
  19.          SqlBulkCopy bulkInsert = new SqlBulkCopy(sqlConnectionString);  
  20.          bulkInsert.DestinationTableName = “tbl_bulkupload”;  
  21.          bulkInsert.WriteToServer(dr);  
  22.          MsgAlert.Text = “Product uploaded successfully”;  
  23.          connection.Close();  
  24.       }  
  25.       catch (Exception ex)  
  26.       {  
  27.          MsgAlert.Text = ex.Message;  
  28.    }
  29. }  
Output

Screen 1:


upload product

Screen 2:

table