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

Muhammad Aqib Shehzad
Apr 28, 2016
20.6k
0
1
Article

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

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

Step 1:

Firstly, create Database if it does not already exist and then create table in which the dat is inserted after importing file data.

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

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

Here is the Script to create table.

CREATE TABLE [dbo].[StudentCourses](
[StudentID] [int] NOT NULL,
[Name] [varchar](500) NULL,
[Course] [varchar](100) NULL,
CONSTRAINT [PK_StudentCourses] PRIMARY KEY CLUSTERED
(
[StudentID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Before inserting Data Here is our empty table.

Step 2:

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

I am also using bootstrap to make UI look good.

Here is the Full code of UI.

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.10.2/jquery.min.js"></script>
<link rel="stylesheet" href="http://netdna.bootstrapcdn.com/bootstrap/3.0.2/css/bootstrap.min.css" />
<script src="http://netdna.bootstrapcdn.com/bootstrap/3.0.2/js/bootstrap.min.js"></script>
</head>
<body>
<form id="form1" runat="server">
<div class="form-horizontal" style="margin-top:50px">
<div class="form-group">
<label class="col-lg-3 control-label">Upload CSV File</label>
<div class="col-lg-5">
<asp:FileUpload ID="fupcsv" runat="server" />
</div>
</div>
<div class="form-group">
<div class="col-lg-9 col-lg-offset-3">
<asp:Button ID="btnimportcsv" runat="server" Text=">Import CSV Data to SQL Table" OnClick="btnimportcsv_Click" />
</div>
</div>
</div>
</form>
</body>
</html>

Step 3:

Before starting we will create the connection string in web.config. Today I will introduce you 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 it will open the context menu Now Choose New è Text Document, New text documents created now change the extension of text file from .txt to .udl and Hit Enter.

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

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

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

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

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.

And that’s all to generate connection string.

Step 4:

So we will start to firstly check the valid .csv extension file and Upload to the 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 valid .csv extension file";
lblmessage.ForeColor = System.Drawing.Color.Red;
}

Firstly, checked the file Extension by using the Path.GetExtension () Method and the we have save the file to Certain path by using Server.MapPath to assign 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 firstly upload to the 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 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 New line.
foreach (string row in csvData.Split('\n'))
{
// Check for is null or empty row record
if (!string.IsNullOrEmpty(row))
{
// added rows
dtCSV.Rows.Add();
int i = 1;
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 use 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 dump into the Destination table";
lblmessage.ForeColor = System.Drawing.Color.Green;
}
}
}
else
{
lblmessage.Text = "Please upload valid .csv extesnion file";
lblmessage.ForeColor = System.Drawing.Color.Red;
}
}