RSS

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);
                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
            }
        }
Advertisements
 
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:

WordPress.com Logo

You are commenting using your WordPress.com 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: