Insert Bulk Data From CSV File To Database Table Using SQLBulkCopy Class

Article

In this article, we will see how we can Import CSV File data to a Database in ASP.NET C# using SqlBulkCopy.

Let’s start to build the application which will process our required command.

Step 1. First, create a Database if it does not already exist, and then create a table in which the data is inserted after importing file data.

I Have Created a table, namely StudentCourses, and created three columns inside it.

threecolumntable

Columns are StudentID, Name, and Course, which are old data after importing.

Here is the Script to create a table.

using System;
namespace CreateTableExample
{
    class Program
    {
        static void Main(string[] args)
        {
            string createTableQuery = @"
                CREATE TABLE [dbo].[StudentCourses](
                    [StudentID] [int] NOT NULL,
                    [Name] [varchar](500) NULL,
                    [Course] [varchar](100) NULL,
                    CONSTRAINT [PK_StudentCourses] PRIMARY KEY CLUSTERED (
                        [StudentID] ASC
                    )
                ) ON [PRIMARY]
            ";
            Console.WriteLine(createTableQuery);
        }
    }
}

Before inserting Data, Here is our empty table.

emptytable

Step 2. Let’s Build an Interface / Markup of File Upload Control and Button.

I am also using Bootstrap to make UI look good.

Here is the Full code of the UI.

using System;
using System.Web.UI;
public partial class _Default : Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
    }
    protected void btnimportcsv_Click(object sender, EventArgs e)
    {
        // Handle button click logic here
    }
}

Step 3. Before starting, we will create the connection string in the web. config. Today I will introduce you to the best connection string generator method, which can reduce the error and it also test the connection as well. Right Click on your PC Desktop, and it will open the context menu. Now, Choose New è Text Document, New text documents created now change the extension of a text file from .txt to .udl and Hit Enter.

It will show a prompt like this just press to continue, and the extension of the file has been changed.

Now double-click the file to open it will look like this.

doubleclick

Now fill in the desired information in respective fields and test the connection. It will give you a prompt of failure or success.

linkproperties

Now press ok at the prompt and then finally hit the OK button of the Data Link Properties Window. If you have selected the check box of Allow saving password, it will give you a confirmation message" Are you sure?"

datalink

After selecting yes, it will print the saved password to the file. Now we will check how it looks like our connection string. Right-click the data link properties file and open it with Notepad. It will show you this final oledb connection string.

connection string

Step 4. So we will start to first check the valid .csv extension file and Upload it to a certain path.

string fileExt = Path.GetExtension(fupcsv.PostedFile.FileName);
if (fileExt == ".csv")
{
    string csvPath = Server.MapPath("~/CSVFIles/") + Path.GetFileName(fupcsv.PostedFile.FileName);
    fupcsv.SaveAs(csvPath);
}
else
{
    lblmessage.Text = "Please upload a valid .csv extension file";
    lblmessage.ForeColor = System.Drawing.Color.Red;
}

Firstly, check the file Extension by using the Path.GetExtension () Method, and we have saved the file to a Certain path by using Server.MapPath to assign the path and then use SaveAs() Method to upload it to the Path.

Step 5. In this step, we will do our code behind functionality to first upload to a certain path and then read the file using File.ReadAllText() Method. After that, a connection has been established with the database, and we will use SqlBulkCopy Class to define our destination table and write to the database using the WriteToServer() Method of SqlBulkCopy Class to finish the task.

Here is the full code which can do our desired task. Code Comments are added for well understanding.

string fileext = Path.GetExtension(fupcsv.PostedFile.FileName);
if (fileext == ".csv")
{
    string csvPath = Server.MapPath("~/CSVFIles/") + Path.GetFileName(fupcsv.PostedFile.FileName);
    fupcsv.SaveAs(csvPath);
    // Add Columns to DataTable to bind data
    DataTable dtCSV = new DataTable();
    dtCSV.Columns.AddRange(new DataColumn[3]
    {
        new DataColumn("StudentID", typeof(int)),
        new DataColumn("Name", typeof(string)),
        new DataColumn("Course", typeof(string))
    });
    // Read all the lines of the text file and close it
    string csvData = File.ReadAllText(csvPath);
    // Iterate over each row and split it to newline
    foreach (string row in csvData.Split('\n'))
    {
        // Check for null or empty row record
        if (!string.IsNullOrEmpty(row))
        {
            // Added rows
            dtCSV.Rows.Add();
            int i = 0;
            foreach (string cell in row.Split(','))
            {
                dtCSV.Rows[dtCSV.Rows.Count - 1][i] = cell;
                i++;
            }
        }
    }
    // Database connection string
    string consString = ConfigurationManager.ConnectionStrings["GamesConnectionString"].ConnectionString;

    using (SqlConnection con = new SqlConnection(consString))
    {
        // Class used to bulk load data from another source
        using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
        {
            // Set the database table name in which data to be added
            sqlBulkCopy.DestinationTableName = "dbo.StudentCourses";
            con.Open();
            // Copy all the rows from dataTable to the destination Database table
            sqlBulkCopy.WriteToServer(dtCSV);
            con.Close();
            lblmessage.Text = "Bulk Data Successfully dumped into the Destination table";
            lblmessage.ForeColor = System.Drawing.Color.Green;
        }
    }
}
else
{
    lblmessage.Text = "Please upload a valid .csv extension file";
    lblmessage.ForeColor = System.Drawing.Color.Red;
}