Csharp CSV File Data Inserted into SQL Server Database Table in ASP.Net Using C# and VB.Net

Inserting data using form is a time consuming task like bill pay queue (if not automated). If it’s taking much time then usually we seek alternative solution. One of the most common solution is to import data from CSV to sql server using C# in Asp.net. Moreover while you are going to deploy a fresh website, sometimes it could have obvious to import or upload existing data into the live system. That’s why here I am trying to discuss about Csharp CSV File Data Inserted into SQL Server Database Table using C# and VB.Net. Based on our back end infrastructure setup to read CSV file in Asp.net C#/VB.Net we could decide one of the following way:

csharp csv file data inserted into sql server table using asp.net

  • Import CSV file into SQL server using bulk insert
  • Import CSV file into SQL server using bulk copy

To complete the code example we have to implement some pre-requisites first. Those are stated below:

Pre Requisite for Csharp CSV File Data Inserted into SQL Server:

Target Table SQL:

CREATE TABLE [dbo].[Customer](
	[ID] [bigint] NOT NULL,
	[Name] [varchar](max) NULL,
	[Address] [varchar](max) NULL,
	[Email] [varchar](max) NULL,
	[Phone] [varchar](max) NULL,
	[Note] [varchar](max) NULL
) ON [PRIMARY]

Sample File to Upload & Insert:
You can download from here.

Required HTML Markup:

        <asp:FileUpload ID="FileUpload1" runat="server" />
        <asp:CheckBox ID="CheckBox1" runat="server" Text="Skip Header Row"/>  
        <asp:Button Text="Upload" OnClick = "UploadCSVData" runat="server" />

UI to Upload CSV File into Server

Demo Output:

csharp csv file data inserted into sql server

Bulk Insert or Bulk Copy?

You may confuse when to use Bulk Insert to upload & read CSV File & when to use Bulk Copy to upload & read CSV File. The answer is: Bulk Insert is the perfect import option if your SQL database & Asp.Net application hosted in the same server. In case of different server Bulk Copy is the only solution to import data from CSV to sql server. One another thing you have to know that the way of Loading CSV File in Windows Application is a bit different in Asp.Net Application. And the difference is in web application you must upload the file into the server first before loading to a Database Table.

Upload CSV File:

C# Code to Upload CSV File into Server:

 private void UploadCSVFile()
    {
        string FilePath = Server.MapPath("~/Uploads/") + Path.GetFileName(FileUpload1.PostedFile.FileName);
        FileUpload1.SaveAs(FilePath);
    }

VB.Net Code to Upload CSV File into Server:

Private Sub UploadCSVFile()
	Dim FilePath As String = Server.MapPath("~/Uploads/") + Path.GetFileName(FileUpload1.PostedFile.FileName)
	FileUpload1.SaveAs(FilePath)
End Sub

Import CSV file into SQL Server using Bulk Insert:

As discussed in the above section we can use Bulk Insert when the Database resides within the application server. And it’s an extremely powerful tool you can programmatically use in your application(s). It’s more faster & more reliable as no network latency is required here. Let’s start the example csharp csv file data inserted into sql server using Bulk Insert:

C# Import CSV File using Bulk Insert:

    private void BulkInsertLoadData()
    {
        using (SqlConnection Conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=TESTDB;Trusted_Connection=yes;"))
        {
            string sql = "BULK INSERT dbo.Customer FROM '" + Server.MapPath("~/Uploads/") + Path.GetFileName(FileUpload1.PostedFile.FileName) + "' WITH (FIELDTERMINATOR = ',',FIRSTROW=2)";
            SqlCommand SqlCmd = new SqlCommand();
            SqlCmd.CommandTimeout = 0;
            Conn.Open();
            SqlCmd.Connection = Conn;
            SqlCmd.CommandText = sql;
            int nInserted = SqlCmd.ExecuteNonQuery();
        }
    }

VB.Net Import CSV File Using Bulk Insert:

Private Sub BulkInsertLoadData()
	Using Conn As New SqlConnection("Data Source=.\SQLEXPRESS;Initial Catalog=TESTDB;Trusted_Connection=yes;")
		Dim sql As String = "BULK INSERT dbo.Customer FROM '" + Server.MapPath("~/Uploads/") + Path.GetFileName(FileUpload1.PostedFile.FileName) + "' WITH (FIELDTERMINATOR = ',',FIRSTROW=2)"
		Dim SqlCmd As New SqlCommand()
		SqlCmd.CommandTimeout = 0
		Conn.Open()
		SqlCmd.Connection = Conn
		SqlCmd.CommandText = sql
		Dim nInserted As Integer = SqlCmd.ExecuteNonQuery()
	End Using
End Sub

Note: Here I have removed the header row using FIRSTROW property of BULK Insert command. Also I am using comma as a column separator here. You should replace it as per the separator of your CSV file.

Common Error & Solution:
Additional characters are coming during bulk insert

Import CSV file into SQL Server using Bulk Copy:

This is the only solution while your DB Server is different. Keep it in mind not only uploading the CSV file into the server but also you have to import csv file to datatable first. Sometimes it create the bottleneck in application level like memory time out while importing large volume of data. In those cases you can import csv file to datatable in a chunk and after completion work with another chunk to reduce at a time memory pressure. So to accomplish the example csharp csv file data inserted into sql server we have to import csv data into a DataTable first. While implementing this keep in mind that here I am removing the header row.

C# Import CSV File to Datatable First:

    private DataTable LoadDatafromCSVFile()
    {
        DataTable CSVDT = new DataTable();
        string DataLine = "";
        try
        {
            DataSet ds = new DataSet();
            DataRow xDR = null;

            using (StreamReader xSR = new StreamReader(Server.MapPath("~/Uploads/") + Path.GetFileName(FileUpload1.PostedFile.FileName)))
            {
                bool bHeader=true;
                while ((DataLine = xSR.ReadLine()) != null)
                {
                    string[] xLineArray = DataLine.Split(',');
                    if (xLineArray.Length > 0)
                    {
                        if (CheckBox1.Checked && bHeader)
                        {
                            foreach (string xCol in xLineArray)
                                CSVDT.Columns.Add(new DataColumn());
                            bHeader = false;
                        }
                        else
                        {
                            for (int i = 0; i < xLineArray.Length; i++)
                                xLineArray[i] = xLineArray[i].Trim();

                            xDR = CSVDT.NewRow();
                            xDR.ItemArray = xLineArray;
                            CSVDT.Rows.Add(xDR);
                        }
                    }
                }
            }
        }
        catch (Exception Ex)
        {
            //Better to raise the error
            return null;
        }
        //nRawCount = xDT.Rows.Count;
        return CSVDT;
    }

C# Import Datatable to SQL Database Table:

    private void Load2DB(DataTable CSVDT)
    {
        using (SqlConnection Conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=TESTDB;Trusted_Connection=yes;"))
        {
            SqlBulkCopy xBcp = new SqlBulkCopy(Conn, SqlBulkCopyOptions.Default, null);
            xBcp.DestinationTableName = "Customer";
            xBcp.BatchSize = CSVDT.Rows.Count;
            xBcp.BulkCopyTimeout = 0;
            if (Conn.State == ConnectionState.Closed) Conn.Open();
            xBcp.WriteToServer(CSVDT);
            xBcp.Close();
        }
    }

Definitely from now & on wards we can implement csharp csv file data inserted into sql server by using the above code sample. Find corresponding VB.Net code also from below:

VB.Net Import CSV File to Datatable First:

Private Function LoadDatafromCSVFile() As DataTable
	Dim CSVDT As New DataTable()
	Dim DataLine As String = ""
	Try
		Dim ds As New DataSet()
		Dim xDR As DataRow = Nothing

		Using xSR As New StreamReader(Server.MapPath("~/Uploads/") + Path.GetFileName(FileUpload1.PostedFile.FileName))
			Dim bHeader As Boolean = True
			While (InlineAssignHelper(DataLine, xSR.ReadLine())) IsNot Nothing
				Dim xLineArray As String() = DataLine.Split(","C)
				If xLineArray.Length > 0 Then
					If CheckBox1.Checked AndAlso bHeader Then
						For Each xCol As String In xLineArray
							CSVDT.Columns.Add(New DataColumn())
						Next
						bHeader = False
					Else
						For i As Integer = 0 To xLineArray.Length - 1
							xLineArray(i) = xLineArray(i).Trim()
						Next

						xDR = CSVDT.NewRow()
						xDR.ItemArray = xLineArray
						CSVDT.Rows.Add(xDR)
					End If
				End If
			End While
		End Using
	Catch Ex As Exception
		'Better to raise the error
		Return Nothing
	End Try
	'nRawCount = xDT.Rows.Count;
	Return CSVDT
End Function

VB.Net Import Datatable to SQL Database Table:

Private Sub Load2DB(CSVDT As DataTable)
	Using Conn As New SqlConnection("Data Source=.\SQLEXPRESS;Initial Catalog=TESTDB;Trusted_Connection=yes;")
		Dim xBcp As New SqlBulkCopy(Conn, SqlBulkCopyOptions.[Default], Nothing)
		xBcp.DestinationTableName = "Customer"
		xBcp.BatchSize = CSVDT.Rows.Count
		xBcp.BulkCopyTimeout = 0
		If Conn.State = ConnectionState.Closed Then
			Conn.Open()
		End If
		xBcp.WriteToServer(CSVDT)
		xBcp.Close()
	End Using
End Sub

Download Complete Source Code CSV File Data Inserted into SQL Server Database Table:

Download Code Example C#        Download Code Example VB.Net

Here I have tried my best to explain Csharp CSV File Data Inserted into SQL Server Database Table in ASP.Net including VB.Net. Also I have discussed import csv file into sql server using Bulk Insert & Bulkcopy with common errors. I think it will help you a lot. If you are still struggling with code please download your desired language full source code from above section links. Please don’t forget to comment because it will help us to improve our readers expectation. Discussion or different opinion is always welcome.

Posted in Asp.net, C#, VB.Net

Leave a Reply

Your email address will not be published. Required fields are marked *

AlphaOmega Captcha Classica  –  Enter Security Code
     
 

*