ASP.Net Bulk Insert Excel Data using SqlBulkCopy

12 Apr

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

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);
                    string path = string.Concat(Server.MapPath("~/uploads/" + FileUpload1.FileName));

                    // 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);
                    // Create DbDataReader to Data Worksheet
                    DbDataReader dr = command.ExecuteReader();

                    bulkInsert.DestinationTableName = "your_sqlTableName";
                    //Show success
                catch (Exception ex)
                    //Show error

                //File not set
Leave a comment

Posted by on April 12, 2015 in ASP.Net


Tags: ,

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: