Loading ...

Using SqlBulkCopy to import Excel SpreadSheet data into SQL Server in ASP.Net | Debugging Code

Using SqlBulkCopy to import Excel SpreadSheet data into SQL Server in ASP.Net

 /5
Avg: 0 / 5 (0votes)

In this blog we are going to learn how to import Excel SpreadSheet data into SQL Server in ASP.Net using SqlBulkCopy

Source code attached: Download

Excel Data (All empty rows/columns removed):

Project view:

ASPX code:

default.aspx:

 

<%@ Page Language="C#" AutoEventWireup="true" %>

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.IO" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
        Import Users:
        <div>
            Please upload your file:
            <asp:FileUpload ID="ImportUsersFileUploader" runat="server" />
            <br />
            <asp:Button ID="ImportUsersButton" runat="server" Text="Import Users" OnClick="ImportUsersButton_Click" />
            <br />
            <asp:Label ID="MessageLabel" runat="server"></asp:Label>
        </div>
    </form>
</body>
</html>
<script runat="server">


    protected void ImportUsersButton_Click(object sender, EventArgs e)
    {
        if (ImportUsersFileUploader.HasFile)
        {
            string baseDirectoryPhysicalPath = Server.MapPath("~/Assets/Excel_Files");
            string postedFileName = ImportUsersFileUploader.PostedFile.FileName;
            string fileName = Path.GetFileName(postedFileName);
            string fileExtension = Path.GetExtension(postedFileName);

            string targetFilePhysicalPath = Path.Combine(baseDirectoryPhysicalPath, fileName);

            // Save to target location
            ImportUsersFileUploader.SaveAs(targetFilePhysicalPath);

            string oledbConnectionString = "";

            if (fileExtension == ".xlsx")
                oledbConnectionString = ConfigurationManager.AppSettings["ExcelOleDbConnectionString"];
            else if (fileExtension == ".xls")
                oledbConnectionString = ConfigurationManager.AppSettings["ExcelLegacyOleDbConnectionString"];

            oledbConnectionString = oledbConnectionString.Replace("{ExcelFilePhysicalPath}", targetFilePhysicalPath);

            using (OleDbConnection oleDbConnection = new OleDbConnection(oledbConnectionString))
            {
                oleDbConnection.Open();

                DataTable oleDbSchemaTable = oleDbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, restrictions: null);

                if (oleDbSchemaTable != null)
                {
                    // Sheet1$
                    string firstSheet = oleDbSchemaTable.Rows[0]["TABLE_NAME"].ToString();

                    // SELECT * FROM [Sheet1$]
                    string command = string.Format("SELECT * FROM [{0}]", firstSheet);


                    DataTable dataTable = new DataTable();

                    dataTable.Columns.Add(new DataColumn("UserID", typeof(int)));
                    dataTable.Columns.Add(new DataColumn("UserName", typeof(string)));
                    dataTable.Columns.Add(new DataColumn("FirstName", typeof(string)));
                    dataTable.Columns.Add(new DataColumn("LastName", typeof(string)));
                    dataTable.Columns.Add(new DataColumn("Age", typeof(int)));

                    using (OleDbDataAdapter oda = new OleDbDataAdapter(command, oleDbConnection))
                    {
                        oda.Fill(dataTable);
                    }

                    SaveDataTableToDB(dataTable);
                }
            }

            MessageLabel.Text = "Users imported successfully.";
        }

    }

    private void SaveDataTableToDB(DataTable sourceDataTable)
    {
        string consString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

        using (SqlConnection sqlConnection = new SqlConnection(consString))
        {
            using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(sqlConnection))
            {
                sqlBulkCopy.ColumnMappings.Add("UserID", "UserID");
                sqlBulkCopy.ColumnMappings.Add("UserName", "UserName");
                sqlBulkCopy.ColumnMappings.Add("FirstName", "FirstName");
                sqlBulkCopy.ColumnMappings.Add("LastName", "LastName");
                sqlBulkCopy.ColumnMappings.Add("Age", "Age");

                sqlBulkCopy.DestinationTableName = "dbo.Users";

                sqlConnection.Open();

                sqlBulkCopy.WriteToServer(sourceDataTable);
            }
        }
    }

</script>

Web.config:

 

<?xml version="1.0" encoding="utf-8"?>

<!--
  For more information on how to configure your ASP.NET application, please visit
  https://go.microsoft.com/fwlink/?LinkId=169433
  -->
<configuration>
  <system.web>
    <compilation debug="true" targetFramework="4.6.1"/>
    <httpRuntime targetFramework="4.6.1"/>
  </system.web>
  <connectionStrings>

    <add name="ConnectionString" connectionString="Initial Catalog=testdb;Server=(local);User ID=testuser;Password=test"/>

  </connectionStrings>
  <appSettings>
    <!--"HDR=Yes;" indicates that the first row contains columnnames, not data. "HDR=No;" indicates the opposite.-->
    <add key="ExcelOleDbConnectionString" value="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={ExcelFilePhysicalPath};Extended Properties='Excel 8.0;HDR=YES'"/>
   
    <!--For 97 XLS extension-->
    <add key="ExcelLegacyOleDbConnectionString" value="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={ExcelFilePhysicalPath};Extended Properties='Excel 8.0;HDR=YES'"/>

  </appSettings>
</configuration>

 

Users create schema:

 

CREATE TABLE [dbo].[Users]
(
    [PK_ID] INT IDENTITY(1, 1) NOT NULL,
    [UserID] INT NOT NULL,
    [UserName] NVARCHAR(100) NOT NULL,
    [FirstName] NVARCHAR(100) NOT NULL,
    [LastName] NVARCHAR(100) NOT NULL,
    [Age] INT NOT NULL,
);

Final output when the users are imported:

Source code attached: Download

 

Attachments

Comments (no comments yet)

Top Posts