ASP.Net Bulk Insert Excel Data using SqlBulkCopy

This code sample is for a Excel Import programmatically into SQL Server database.

Important points to note.

• Use excel data in a single sheet.


• The excel file format should match the table schema.

Excel format:

Id

Name

Description

CreatedAt

CreatedBy

ModifiedAt

ModifiedBy

Enabled

Deleted

.aspx page

<asp:FileUpload ID="FileUpload1" runat="server" />

<asp:Button ID="Button1" runat="server" Text="Import" CssClass="btn btn-warning" OnClick="ImportExcel" />

Code Behind File

[sourcecode language="csharp"]
protected void ImportExcel(object sender, EventArgs e)
        {
            if (FileUpload1.HasFile)
            {
                // SQL Server Connection String
                string sqlConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["flexi_stocky"].ConnectionString;

                // Bulk Copy to SQL Server
                SqlBulkCopy bulkInsert = new SqlBulkCopy(sqlConnectionString);
                try
                {
                  
                    string path = string.Concat(Server.MapPath("~/uploads/" + FileUpload1.FileName));
                    FileUpload1.SaveAs(path);

                    // Connection String to Excel Workbook
                    string excelConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 8.0", path);
                    OleDbConnection connection = new OleDbConnection();
                    connection.ConnectionString = excelConnectionString;
                    OleDbCommand command = new OleDbCommand("select * from [Sheet1$]", connection);
                    connection.Open();
                    // Create DbDataReader to Data Worksheet
                    DbDataReader dr = command.ExecuteReader();

                    
                    bulkInsert.DestinationTableName = "your_sqlTableName";
                    bulkInsert.WriteToServer(dr);
                    bulkInsert.Close();
                    //Show success
                }
                catch (Exception ex)
                {
                    bulkInsert.Close();
                    //Show error
                }

            }
            else
            {
                //File not set
            }
        }
[/sourcecode]