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