SQL Server bulk insert with real-world example.

Sometimes we need to insert huge amounts of data from either Excel or a CSV file into SQL server database. In these situations, a plain DataReader with ExecuteNonQuery is not going to be enough.

That is where SqlBulkCopy comes in handy as you can insert huge amounts of data very quickly into an SQL server.

This can be done from within your application whether it be a console app or web app.

In this blog, we will be using a web application built using MVC C# and SQL 2019 standard.

First, we will need to create a table in the database to allow us to bulk import the data.

The database table will be simple and the code is below:

USE [cmsDemoCode]
GO
/****** Object: Table [dbo].[Products]  Script Date: 07/04/2021 21:15:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Products](

	[idCol] [int] IDENTITY(1,1) NOT NULL,

	[productID] [nvarchar](50) NULL,

	[title] [nvarchar](50) NULL,

	[description] [nvarchar](50) NULL,

	[price] [nvarchar](50) NULL,

	[imageName] [nvarchar](50) NULL,

 CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED 
(
[idCol] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

HTML Code.

Now we need to populate an excel file with data so that we can upload it to the website. The excel file will also have images so both the file and images will be in a ZIP file.

We are going to use MimeMapping to ensure that the user can only upload a ZIP file which we will extract the content of and save to disk.

The HTML for uploading the file is below:

<div class="container">
    <div class="row">
        <div clas="col-12">
            <h2>BulkInsert @ViewBag.Message</h2>
            @using (Html.BeginForm("Home", "Bulk", FormMethod.Post, new {enctype = "multipart/form-data"}))
            {
                <div>  
                    @Html.TextBox("file", "", new {  type= "file"}) <br />  
   
                    <input type="submit" value="Upload" /> 
                </div>  
            }
        </div>
    </div>
</div>

Controller.

using System;
using System.IO;
using System.Web;
using System.Web.Mvc;
using Web.Controller.Helpers;

namespace Web.Controller.Controllers
{
    public class BulkController : System.Web.Mvc.Controller
    {

        [HttpGet]
        public ActionResult Home()
        {
            ViewBag.Message = "Bulk.";

            return View();
        }

        [HttpPost]
        public ActionResult Home(HttpPostedFileBase file)
        {

            try
            {
                string mimeType = MimeMapping.GetMimeMapping(file.FileName);

                if (mimeType == "application/x-zip-compressed")
                {
                    if (file.ContentLength > 0)
                    {
                        string fileName = Path.GetFileName(file.FileName);
                        string path = Path.Combine(Server.MapPath("~/UploadedFiles"), fileName);
                        file.SaveAs(path);

                        IExtractAndBulkInsert iExtractAndBulkInsert = new ExtractAndBulkInsert();
                        bool status = iExtractAndBulkInsert.ReadExcelFile(path);

                        if (status)
                        {
                            ViewBag.Message = "Insert Completed.";
                            return View();
                        }
                        
                    }
                }
                ViewBag.Message = "Insert failed.";
                return View();

            }
            catch (Exception e)
            {
                Console.WriteLine(e);
                throw;
            }
           
        }
    }
}

Class Code.

If the file is a ZIP file, we save it to disk and then pass the path to our ExtractAndBulkInsert class which will extract the contents of the ZIP file, we then check that the file with the data is an excel file and the images are either .png or .jpg. We then loop over the excel file and copy the data to a DataTable before bulk inserting it into the database.

using System;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.IO.Compression;
using System.Linq;
using System.Web;
using ExcelDataReader;

namespace Web.Controller.Helpers
{
    public interface IExtractAndBulkInsert
    {
        bool ReadExcelFile(string pathToFile);
    }

    public class ExtractAndBulkInsert : IExtractAndBulkInsert
    {
        public bool ReadExcelFile(string pathToFile)
        {
            string zipFilePath      = HttpContext.Current.Server.MapPath("~/UploadedFiles");
            string extractImagesTo  = HttpContext.Current.Server.MapPath("~/UploadedFiles");

            string pathToExcelFileOnServer = string.Empty;

            using (ZipArchive archive = ZipFile.OpenRead(pathToFile))
            {
                foreach (ZipArchiveEntry entry in archive.Entries)
                {
                    if (entry.FullName.EndsWith(".xlsx", StringComparison.OrdinalIgnoreCase) || entry.FullName.EndsWith("xls", StringComparison.OrdinalIgnoreCase))
                    {
                        if (File.Exists(Path.Combine(zipFilePath, entry.Name)))
                        {
                            File.Delete(Path.Combine(zipFilePath, entry.Name));
                        }

                        pathToExcelFileOnServer = Path.Combine(zipFilePath, entry.Name);
                        entry.ExtractToFile(Path.Combine(zipFilePath, entry.Name));
                    }
                    else
                    {
                        if (!entry.FullName.EndsWith(".png", StringComparison.OrdinalIgnoreCase) &&&& !entry.FullName.EndsWith(".jpg", StringComparison.OrdinalIgnoreCase)) continue;
                        if (File.Exists(Path.Combine(extractImagesTo, entry.Name)))
                        {
                            File.Delete(Path.Combine(extractImagesTo, entry.Name));
                        }
                        entry.ExtractToFile(Path.Combine(extractImagesTo, entry.Name));
                    }
                }
            }

            if (!string.IsNullOrEmpty(pathToExcelFileOnServer))
            {
                using (var stream = File.Open(pathToExcelFileOnServer, FileMode.Open, FileAccess.Read))
                {
                    using (var reader = ExcelReaderFactory.CreateReader(stream))
                    {
                        var result = reader.AsDataSet();

                        var dt = new DataTable();
                        dt.Columns.Add("productID");
                        dt.Columns.Add("title");
                        dt.Columns.Add("description");
                        dt.Columns.Add("price");
                        dt.Columns.Add("image");

                        foreach (DataTable table in result.Tables)
                        {
                            foreach (DataRow dr in table.Rows.Cast().Skip(1)) //Skipping header
                            {
                                dt.Rows.Add(dr[0].ToString(), dr[1].ToString(), dr[2].ToString(), dr[3].ToString(), dr[4].ToString());
                            }
                        }


                        using (var sqlBulkCopy = new SqlBulkCopy(DbConnectionAbstractClass.ConnectionString))
                        {
                            sqlBulkCopy.BatchSize = 5000;
                            sqlBulkCopy.DestinationTableName = "dbo.Products";

                            sqlBulkCopy.ColumnMappings.Add("productId", "productID");
                            sqlBulkCopy.ColumnMappings.Add("title", "title");
                            sqlBulkCopy.ColumnMappings.Add("description", "description");
                            sqlBulkCopy.ColumnMappings.Add("price", "price");
                            sqlBulkCopy.ColumnMappings.Add("image", "imageName");

                            sqlBulkCopy.WriteToServer(dt);
                        }
                    }
                }

                return true;
            }
            return false;
        }
    }
}
SQL server bulk insert with real world example

You can find the code for this example at GitHub.com