Import (Upload) Data from Excel to SQL Server using SqlBulkCopy in C# Asp.net

There are different ways to import (upload) data from an Excel file to an SQL Server database table. We can use either a DataSet to import data into a table or by using Asp.Net “SqlBulkCopy” class, which comes with the namespace System.Data.SqlClient.

In this article, I am going to show you with an example on how you can use SqlBulkCopy properties and methods to upload or import data from Excel to an SQL Server table. I have written the code behind procedures both in C# and in Vb.Net.

Asp.Net SqlBulkCopy Class

The SqlBulkCopy class provides some very efficient and easy to use properties to do a bulk import from an Excel sheet into a database table. Yes, bulk imports. It totally negates the use of a loop (traditional approach) to fetch data from a source and transfer the same data to another. Therefore, it reduces the code we would have written to execute the entire process.

SqlBulkCopy is comparatively much faster than any other process to import large data into a SQL Server table.

Also Read: How to Bind a GridView with Database using Datatable in C# Asp.Net

Let us now understand the functions of SqlBulkCopy and the ease with which it accomplishes the importing of data. All we need is an Excel sheet with 4 columns and few data in it. Please see the below image.

Import Data from Excel in Asp.Net

Once you have created the Excel file, next thing you have to do it create a table in SQL Server. The table name in this example is EmployeeDetails. I have already created the table for this purpose.

Table: dbo.EmployeeDetails

The Markup

Open Visual Studio and add new Web Site. In the <body> section of your web page, add the below mark up.

<html>
<head>
    <title>Import Excel Data</title>
</head>
<body>
    <form id="form1" runat="server">
    
        <!-- ADD A FILE UPLOAD CONTROL AND A BUTTON TO EXECUTE. -->
        <div style="font:14px Verdana">
        
            Select a file to upload: 
                <asp:FileUpload ID="FileUpload" Width="450px" runat="server" />
            <p><input type="button" id="btImport" value="Import Data" runat="server" /></p>
            <p><asp:Label id="lblConfirm" runat="server"></asp:Label></p>
                
        </div>
        
    </form>
</body>
</html>

Useful Excel Tips: 7 Excel Keyboard Shortcuts You Must Know

Code Behind (C#)

using System;

using System.Data.OleDb;
using System.Data.SqlClient;        // FOR "SqlBulkCopy" CLASS.

public partial class SiteMaster : System.Web.UI.MasterPage
{
    protected void ImportFromExcel(object sender, EventArgs e)
    {
        // CHECK IF A FILE HAS BEEN SELECTED.
        if ((FileUpload.HasFile)) {

            if (! Convert.IsDBNull(FileUpload.PostedFile) & 
                FileUpload.PostedFile.ContentLength > 0) {

                //FIRST, SAVE THE SELECTED FILE IN THE ROOT DIRECTORY.
                FileUpload.SaveAs(Server.MapPath(".") + "\\" + FileUpload.FileName);

                SqlBulkCopy oSqlBulk = null;

                // SET A CONNECTION WITH THE EXCEL FILE.
                OleDbConnection myExcelConn = new OleDbConnection 
                    ("Provider=Microsoft.ACE.OLEDB.12.0; " +
                        "Data Source=" + Server.MapPath(".") + "\\" + FileUpload.FileName +
                        ";Extended Properties=Excel 12.0;");
                try {
                    myExcelConn.Open();

                    // GET DATA FROM EXCEL SHEET.
                    OleDbCommand objOleDB = 
                        new OleDbCommand("SELECT *FROM [Sheet1$]", myExcelConn);

                    // READ THE DATA EXTRACTED FROM THE EXCEL FILE.
                    OleDbDataReader objBulkReader = null;
                    objBulkReader = objOleDB.ExecuteReader();

                    // SET THE CONNECTION STRING.
                    string sCon = "Data Source=DNA;Persist Security Info=False;" +
                        "Integrated Security=SSPI;" +
                        "Initial Catalog=DNA_Classified;User Id=sa;Password=;" +
                        "Connect Timeout=30;";

                    using (SqlConnection con = new SqlConnection(sCon))
                    {
                        con.Open();

                        // FINALLY, LOAD DATA INTO THE DATABASE TABLE.
                        oSqlBulk = new SqlBulkCopy(con);
                        oSqlBulk.DestinationTableName = "EmployeeDetails"; // TABLE NAME.
                        oSqlBulk.WriteToServer(objBulkReader);
                    }

                    lblConfirm.Text = "DATA IMPORTED SUCCESSFULLY.";
                    lblConfirm.Attributes.Add("style", "color:green");

                } catch (Exception ex) {

                    lblConfirm.Text = ex.Message; 
                    lblConfirm.Attributes.Add("style", "color:red");

                } finally {
                    // CLEAR.
                    oSqlBulk.Close();
                    oSqlBulk = null;
                    myExcelConn.Close();
                    myExcelConn = null;
                }
            }
        }
    }
}

Vb.Net

Option Explicit On

Imports System.Data.OleDb
Imports System.Data.SqlClient          ' FOR "SqlBulkCopy" CLASS.

Partial Class Site
    Inherits System.Web.UI.MasterPage

    Dim myConn As SqlConnection

    Protected Sub btImport_ServerClick(ByVal sender As Object, ByVal e As System.EventArgs) _
            Handles btImport.ServerClick

        If (FileUpload.HasFile) Then        ' CHECK IF A FILE HAS BEEN SELECTED.

            If Not IsDBNull(FileUpload.PostedFile) And _
                FileUpload.PostedFile.ContentLength > 0 Then

                ' SAVE THE SELECTED FILE IN THE ROOT DIRECTORY.
                FileUpload.SaveAs(Server.MapPath(".") + "\\" + FileUpload.FileName);

                Dim oSqlBulk As SqlBulkCopy

                ' SET A CONNECTION WITH THE EXCEL FILE.
                Dim myExcelConn As OleDbConnection = _
                    New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
                        Server.MapPath(".") & "\" & FileUpload.FileName() & _
                        ";Extended Properties=Excel 12.0;")
                Try
                    myExcelConn.Open()

                    ' GET DATA FROM EXCEL SHEET.
                    Dim objOleDB As New OleDbCommand("SELECT *FROM [Sheet1$]", myExcelConn)

                    ' READ THE DATA EXTRACTED FROM THE EXCEL FILE.
                    Dim objBulkReader As OleDbDataReader
                    objBulkReader = objOleDB.ExecuteReader

                    ' FINALLY, LOAD DATA INTO THE DATABASE TABLE.

                    Dim sCon As String = "Data Source=DNA;Persist Security Info=False;" & _
                        "Integrated Security=SSPI;" & _
                        "Initial Catalog=DNA_Classified;User Id=sa;Password=;" & _
                        "Connect Timeout=30;"

                    Using con As SqlConnection = New SqlConnection(sCon)
                        con.Open()
                        oSqlBulk = New SqlBulkCopy(con)
                        ' TABLE, DATA WILL BE UPLOADED TO.
                        oSqlBulk.DestinationTableName = "EmployeeDetails"       
                        oSqlBulk.WriteToServer(objBulkReader)
                    End Using

                    lblConfirm.Text = "DATA IMPORTED SUCCESSFULLY."
                    lblConfirm.Attributes.Add("style", "color:green")

                Catch ex As Exception
                    lblConfirm.Text = ex.Message 
                    lblConfirm.Attributes.Add("style", "color:red")
                Finally

                    ' CLEAR.
                    oSqlBulk.Close() : oSqlBulk = Nothing
                    myExcelConn.Close() : myExcelConn = Nothing
                End Try
            End If
        End If
    End Sub
End Class

That’s it. Thanks for reading. ☺

← PreviousNext →

Import (Upload) Data from Excel to SQL Server using SqlBulkCopy in C# Asp.net – Vb.Net

Tools you can use

Asp.Net SqlBulkCopy Class