In this article I will explain with an example, how to bulk import CSV file data into SQL Server database using SqlBulkCopy in ASP.Net using C# and VB.Net.
The CSV file will be uploaded and its data will be read into a DataTable and the DataTable data will be inserted into database using SqlBulkCopy in ASP.Net.
Database
I have made use of the following table Customers with the schema as follows.
![Bulk Import CSV file data into database using SqlBulkCopy in ASP.Net]()
Note: You can download the database table SQL by clicking the download link below.
Download SQL file
HTML Markup
The HTML Markup consists of ASP.Net FileUpload control and a Button to trigger the file upload process.
<asp:FileUpload ID="FileUpload1" runat="server" />
<asp:Button Text="Upload" OnClick="Upload" runat="server" />
Namespaces
You will need to import the following namespaces.
C#
using System.IO;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
VB.Net
Imports System.IO
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Bulk Import CSV file data into database using SqlBulkCopy
When the Upload Button is clicked, the CSV file is uploaded and saved in a Folder named Files.
The CSV file data is read into a String variable using the File class ReadAllText method.
A DataTable is created with columns same as that of the destination database table and then the CSV file data is split using New Line (\n) and Comma (,) characters and using a loop the data is saved into the DataTable.
Once the connection is established with the Database and the SqlBulkCopy object is initialized, the name of the Table is specified using the DestinationTableName property.
Finally the DataTable is used by SqlBulkCopy to insert data into the SQL Server database table.
C#
protected void Upload(object sender, EventArgs e)
{
//Upload and save the file.
string csvPath = Server.MapPath("~/Files/") + Path.GetFileName(FileUpload1.PostedFile.FileName);
FileUpload1.SaveAs(csvPath);
DataTable dt = new DataTable();
dt.Columns.AddRange(new DataColumn[3] { new DataColumn("Id", typeof(int)),
new DataColumn("Name", typeof(string)),
new DataColumn("Country",typeof(string)) });
string csvData = File.ReadAllText(csvPath);
foreach (string row in csvData.Split('\n'))
{
if (!string.IsNullOrEmpty(row))
{
dt.Rows.Add();
int i = 0;
foreach (string cell in row.Split(','))
{
dt.Rows[dt.Rows.Count - 1][i] = cell;
i++;
}
}
}
string consString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(consString))
{
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
{
//Set the database table name.
sqlBulkCopy.DestinationTableName = "dbo.Customers";
con.Open();
sqlBulkCopy.WriteToServer(dt);
con.Close();
}
}
}
VB.Net
Protected Sub Upload(sender As Object, e As EventArgs)
'Upload and save the file.
Dim csvPath As String = Server.MapPath("~/Files/") + Path.GetFileName(FileUpload1.PostedFile.FileName)
FileUpload1.SaveAs(csvPath)
Dim dt As New DataTable()
dt.Columns.AddRange(New DataColumn(2) {New DataColumn("Id", GetType(Integer)), New DataColumn("Name", GetType(String)), New DataColumn("Country", GetType(String))})
Dim csvData As String = File.ReadAllText(csvPath)
For Each row As String In csvData.Split(ControlChars.Lf)
If Not String.IsNullOrEmpty(row) Then
dt.Rows.Add()
Dim i As Integer = 0
For Each cell As String In row.Split(","c)
dt.Rows(dt.Rows.Count - 1)(i) = cell
i += 1
Next
End If
Next
Dim consString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As New SqlConnection(consString)
Using sqlBulkCopy As New SqlBulkCopy(con)
'Set the database table name.
sqlBulkCopy.DestinationTableName = "dbo.Customers"
con.Open()
sqlBulkCopy.WriteToServer(dt)
con.Close()
End Using
End Using
End Sub
Screenshots
CSV File
![Bulk Import CSV file data into database using SqlBulkCopy in ASP.Net]()
Table containing the data from the CSV file
![Bulk Import CSV file data into database using SqlBulkCopy in ASP.Net]()
Downloads
