Import/Upload data from Excel to SQL Server using SqlBulkCopy in Asp.net using C# and VB

There are different ways to import (upload) data from an Excel file to an SQL Server database table. We can use 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 how to 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.

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 relatively much faster than any other process to import large data into a SQL Server table.

Let us now understand the functions of "SqlBulkCopy" and the ease with which it imports data. Here's an example. Open an Excel file and 4 columns and few data in "Sheet 1". 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.

I have added a fileupload control and button on the web page. So I can select the Excel file.

<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>
            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>

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;
                }
            }
        }
    }
}

Run the application. If every thing is done correctly, you should be able to select an Excel file with the colouns I have shown above. On button click, the code procedure will read and extract data from the file and insert the data in SQL Server table.

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

← PreviousNext →