Upload CSV file to database using C#

Introduction

Sometime we need upload data to database from the flat file. In this theme we have a CSV file with 100K records with comma delimiter.
Now we need upload this data to database using C#. To this kind of implementation .NET Framework have a great object call SqlBulkCopy. This is support to us to upload data fast and reliable.

Then design the form with textboxes, progressBar, label and button. Finally add FileOpenDialog dialog also picks up the file from the location. After did necessary design GUI look like following figure?

Now we have form, let’s start to write code to do implement the goal. To this we need do two important works
1. We need read the CSV file and create data table or dataset
2. We need map source columns with destination columns(Database)


Task: One

Here we need write code read file and build the data table. But we have some practical issues.
• Field delimiter
• Header row
• Empty values.
We need take care above issues and compose code in common way. When we are code for that it’s like followings.

{codecitation class="brush: csharp; gutter: true;" width="650px"}


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Data;

namespace CG.CS.CSVFileUploadUsingSBCDemo.CSV
{
public class CSVReader
{


public static DataTable parseCSV(string filepath)
{
char[] delimeter = { '|' };
return parseCSV(filepath, delimeter, true, 65001);
}
public static DataTable parseCSV(string filename, bool hasHeader)
{
char[] delimeter = { '|' };
return parseCSV(filename, delimeter, hasHeader, 65001);
}
public static DataTable parseCSV(string filename, char[] delimeter)
{
return parseCSV(filename, delimeter, true, 65001);
}

public static string[] GetHeaderOnly(string filename, char[] delimeter)
{
try
{
if (!File.Exists(filename))
{
throw new FileNotFoundException("The Source csv file not found in the:" + filename);
}
string[] rowHeader = null;
using (StreamReader readFile = new StreamReader(filename, Encoding.Default))
{
string line;
if ((line = readFile.ReadLine()) != null)
{
rowHeader = line.Split(delimeter);
}
}
return rowHeader;
}
catch (Exception ex)
{
throw ex;
}
}
public static DataTable parseCSV(string filename, char[] delimeter, bool requriedHeader, int encodePage)
{
DataTable csvDataTable = new DataTable();

try
{
if (!File.Exists(filename))
{
throw new FileNotFoundException("The Source csv file not found in the:" + filename);
}
using (StreamReader readFile = new StreamReader(filename, Encoding.GetEncoding(encodePage)))
{
string line;
string[] orginalRow;
bool onlyFirstTime = true;
DataRow csvdataRow = null;
while ((line = readFile.ReadLine()) != null)
{

orginalRow = line.Split(delimeter);
if (onlyFirstTime)
{
onlyFirstTime = false;
if (requriedHeader)
{
for (int i = 0; i < orginalRow.Length; i++)
{
csvDataTable.Columns.Add(new DataColumn((orginalRow[i].Replace(@"""", "")), typeof(string)));
}
}
else
{
for (int i = 0; i < orginalRow.Length; i++)
{
csvDataTable.Columns.Add(new DataColumn("Col" + i.ToString(), typeof(string)));
}
}
}
else
{
csvdataRow = csvDataTable.NewRow();
csvdataRow.ItemArray = orginalRow.ToArray();
csvDataTable.Rows.Add(csvdataRow);
}
}
}
}
catch (Exception ex)
{
throw ex;
}

return csvDataTable;
}

public static DataTable parseCSV(string filename, char[] delimeter, bool requriedHeader)
{
DataTable csvDataTable = new DataTable();

try
{
if (!File.Exists(filename))
{
throw new FileNotFoundException("The Source csv file not found in the:" + filename);
}
using (StreamReader readFile = new StreamReader(filename, true))
{
string line;
string[] orginalRow;
bool onlyFirstTime = true;
DataRow csvdataRow = null;
while ((line = readFile.ReadLine()) != null)
{

orginalRow = line.Split(delimeter);
if (onlyFirstTime)
{
onlyFirstTime = false;
if (requriedHeader)
{
for (int i = 0; i < orginalRow.Length; i++)
{
csvDataTable.Columns.Add(new DataColumn((orginalRow[i].Replace(@"""", "")), typeof(string)));
}
}
else
{
for (int i = 0; i < orginalRow.Length; i++)
{
csvDataTable.Columns.Add(new DataColumn("Col" + i.ToString(), typeof(string)));
}
}
}
else
{
csvdataRow = csvDataTable.NewRow();
csvdataRow.ItemArray = orginalRow.ToArray();
csvDataTable.Rows.Add(csvdataRow);
}
}
}
}
catch (Exception ex)
{
throw ex;
}

return csvDataTable;
}

public static void MakeOneFileFromSource(string sourcefilePath, string sourceEncoding, string destinatinEncoding, string destFilePath, bool IsHeaderWrite)
{
try
{
if (!File.Exists(sourcefilePath))
{

}
else
{
string[] collectionString = File.ReadAllLines(sourcefilePath, Encoding.GetEncoding(int.Parse(sourceEncoding.TrimEnd())));
int startIndex = 0;
if (IsHeaderWrite)
{
startIndex = 0;
}
else
{
startIndex = 1;
}
using (FileStream stream = new FileStream(destFilePath, FileMode.Append, FileAccess.Write, FileShare.None))
{
using (StreamWriter writer = new StreamWriter(stream, Encoding.GetEncoding(int.Parse(sourceEncoding.TrimEnd()))))
{
for (int i = startIndex; i < collectionString.Length; i++)
{
writer.WriteLine(collectionString[i]);
}
writer.Flush();
}
}
collectionString = null;
}
}
catch (Exception ex)
{


}
}
}
}

{/codecitation}

Now we have data in the Data table. Next we need write code to map the source data columns with destination columns. To this we need write code with SqlBulkCopy Column Mapping object.

{codecitation class="brush: csharp; gutter: true;" width="650px"}

using (SqlConnection connection = new SqlConnection(txtboxConnection.Text))
{
connection.Open();
using (SqlBulkCopy sqlbulkCopy = new SqlBulkCopy(connection))
{
sqlbulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping("Col0", "Type"));
sqlbulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping("Col1", "ID"));
sqlbulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping("Col2", "SeqNo"));
sqlbulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping("Col3", "Price"));
sqlbulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping("Col4", "Prefixno"));
sqlbulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping("Col5", "RetaiPrice"));
sqlbulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping("Col6", "Status"));
sqlbulkCopy.DestinationTableName = "SqlBCDemoTbl";
sqlbulkCopy.NotifyAfter = 1;
sqlbulkCopy.SqlRowsCopied += new SqlRowsCopiedEventHandler(sqlbulkCopy_SqlRowsCopied);
sqlbulkCopy.WriteToServer(data);
}
}

{/codecitation}

I have mentined in introduction we have 100K records in flat file.Since its take long time to upload to database.to make realiable and fast I would like to introduce the background thread.
To this .Net Framework have a great compoenent call as BackGroundworker. We can use this to our purpose.
Now need to do one more work to write final code snippet to get it work.Create table in database to upload to data to database. The table look like followings.

Note:Within the start button click event we need to write this code to start the background worker thread.to get more detail about BackGroundWorker visit here.

{codecitation class="brush: csharp; gutter: true;" width="650px"}


BackgroundWorker worker = null;
char[] delimeter = null;
bool isHasHeaderRow = false;
private void btnStart_Click(object sender, EventArgs e)
{

string fileName = txtFileName.Text;

if (cmbDelimeter.SelectedIndex > 0)
{
switch (cmbDelimeter.SelectedItem.ToString())
{
case "Tab":
delimeter = new char[] { '\t' };
break;
case "Comma":
delimeter = new char[] { ',' };
break;
case "Pipe":
delimeter = new char[] { '|' };
break;
case "Space":
delimeter = new char[] { ' ' };
break;
default:
delimeter = new char[] { '\t' };
break;
}
}
if (chkHeaderRow.Checked)
{
isHasHeaderRow = true;
}
if (worker == null)
{
worker = new BackgroundWorker();
worker.ProgressChanged += new ProgressChangedEventHandler(worker_ProgressChanged);
worker.DoWork += new DoWorkEventHandler(worker_DoWork);
worker.RunWorkerCompleted += new RunWorkerCompletedEventHandler(worker_RunWorkerCompleted);
worker.WorkerReportsProgress = true;
worker.ReportProgress(10);


}
worker.RunWorkerAsync(fileName);
}


{/codecitation}

Now need write code within the DoWork event to read the source file and fetch to records to datatable and then map the columns with SqlBulkCopy object and call WriteServer Method in sqlbulCopy object.
For this code should be like followings.

{codecitation class="brush: csharp; gutter: true;" width="650px"}


void worker_DoWork(object sender, DoWorkEventArgs e)
{
try
{
DataTable data = CSVReader.parseCSV(e.Argument.ToString(), delimeter, isHasHeaderRow, ASCIIEncoding.Default.CodePage);
if (data != null)
{
totalNoofRows = data.Rows.Count;
}
using (SqlConnection connection = new SqlConnection(txtboxConnection.Text))
{
connection.Open();
using (SqlBulkCopy sqlbulkCopy = new SqlBulkCopy(connection))
{
sqlbulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping("Col0", "Type"));
sqlbulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping("Col1", "ID"));
sqlbulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping("Col2", "SeqNo"));
sqlbulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping("Col3", "Price"));
sqlbulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping("Col4", "Prefixno"));
sqlbulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping("Col5", "RetaiPrice"));
sqlbulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping("Col6", "Status"));
sqlbulkCopy.DestinationTableName = "SqlBCDemoTbl";
sqlbulkCopy.NotifyAfter = 1;
sqlbulkCopy.SqlRowsCopied += new SqlRowsCopiedEventHandler(sqlbulkCopy_SqlRowsCopied);
sqlbulkCopy.WriteToServer(data);
}
}
e.Result = "Success";
}
catch (Exception ex)
{
Throw ex;
}
}


{/codecitation}

And finally we need write code for display progress bar change according the row uploading.

{codecitation class="brush: csharp; gutter: true;" width="650px"}


void sqlbulkCopy_SqlRowsCopied(object sender, SqlRowsCopiedEventArgs e)
{
int rowCopied = int.Parse(e.RowsCopied.ToString());
double result = (double.Parse(rowCopied.ToString()) / double.Parse(totalNoofRows.ToString()));
int prsent = int.Parse(Math.Round((result * 100), 0).ToString());
worker.ReportProgress(prsent);
}

void worker_ProgressChanged(object sender, ProgressChangedEventArgs e)
{
progrssBarData.Value = e.ProgressPercentage;
}



{/codecitation}

That’s all run application and select the data file from the location and then click the Start button to start the data import porgress.

Conclusion

This article has deatil about to import CSV file records to Database using ADO.NET SqlBulkCopy object.

Download Source code

Thank you

RRaveen