Read CSV File And Save To SQL Server In ASP.NET C# VB.NET

Read CSV File or Data In ASP.NET using C# VB.NET And save to MS SQL Server.

In this example i'm explaining how to read CSV file and save data to sql server.

My sql table has 3 columns FirstName,LastName and Department. and i m using datatable to read Data from CSV file and temporarily storing in datatable.

First of all you need to add reference to Microsoft.VisualBasic dll by rightclicking in solution explorer,select add reference and select microsoft.VisualBasic from list.


Add these namespaces in code behind of page.

using System.Data;
using System.Data.SqlClient;
using Microsoft.VisualBasic.FileIO;

Now write below mentioned code in click event of button.

C# CODE
protected void Button1_Click(object sender, EventArgs e)
    {
        DataTable tblReadCSV = new DataTable();

        tblReadCSV.Columns.Add("FirstName");
        tblReadCSV.Columns.Add("LastName");
        tblReadCSV.Columns.Add("Department");

        TextFieldParser csvParser = new TextFieldParser("C:\\test.txt");

        csvParser.Delimiters = new string[] { "," };
        csvParser.TrimWhiteSpace = true;
        csvParser.ReadLine();

        while (!(csvParser.EndOfData == true))
        {
            tblReadCSV.Rows.Add(csvParser.ReadFields());
        }

        //Create SQL Connection, Sql Command and Sql DataAdapter to save CSV data into SQL Server 
        string strCon = ConfigurationManager.ConnectionStrings["testdbConnectionString"].ConnectionString;
        string strSql = "Insert into Employees(FirstName,LastName,Department) values(@Fname,@Lname,@Dept)";
        SqlConnection con = new SqlConnection(strCon);
        SqlCommand cmd = new SqlCommand();
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = strSql;
        cmd.Connection = con;
        cmd.Parameters.Add("@Fname", SqlDbType.VarChar, 50, "FirstName");
        cmd.Parameters.Add("@Lname", SqlDbType.VarChar, 50, "LastName");
        cmd.Parameters.Add("@Dept", SqlDbType.VarChar, 50, "Department");

        SqlDataAdapter dAdapter = new SqlDataAdapter();
        dAdapter.InsertCommand = cmd;
        int result = dAdapter.Update(tblReadCSV);
        
    }

VB.NET CODE
Protected Sub Button1_Click(sender As Object, e As EventArgs)
 Dim tblReadCSV As New DataTable()

 tblReadCSV.Columns.Add("FirstName")
 tblReadCSV.Columns.Add("LastName")
 tblReadCSV.Columns.Add("Department")

 Dim csvParser As New TextFieldParser("C:\test.txt")

 csvParser.Delimiters = New String() {","}
 csvParser.TrimWhiteSpace = True
 csvParser.ReadLine()

 While Not (csvParser.EndOfData = True)
  tblReadCSV.Rows.Add(csvParser.ReadFields())
 End While

 'Create SQL Connection, Sql Command and Sql DataAdapter to save CSV data into SQL Server 
 Dim strCon As String = ConfigurationManager.ConnectionStrings("testdbConnectionString").ConnectionString
 Dim strSql As String = "Insert into Employees(FirstName,LastName,Department) values(@Fname,@Lname,@Dept)"
 Dim con As New SqlConnection(strCon)
 Dim cmd As New SqlCommand()
 cmd.CommandType = CommandType.Text
 cmd.CommandText = strSql
 cmd.Connection = con
 cmd.Parameters.Add("@Fname", SqlDbType.VarChar, 50, "FirstName")
 cmd.Parameters.Add("@Lname", SqlDbType.VarChar, 50, "LastName")
 cmd.Parameters.Add("@Dept", SqlDbType.VarChar, 50, "Department")

 Dim dAdapter As New SqlDataAdapter()
 dAdapter.InsertCommand = cmd
 Dim result As Integer = dAdapter.Update(tblReadCSV)

End Sub

Build and Run the code.

Download Sample Code


If you like this post than join us or share

5 comments:

hemalisachdev said...

Nice Code. Thanx...


hemalisachdev said...

Hey pls help i had study the code which is describe above for uploading csv file, bt i need further help that if we upload one files multiple times than it overwrites the previous last data bt i dnt want 2 overwrite the last data just new data in last file is to be inserted.
Pls help me for this...


amiT jaiN said...

@hemalisachdev: I have checked the code and it doesn't overwrite any data in database, records get added any number of time you upload same file


Momin Ansari said...

Thanks Yaar for the Code it Solved my Problem....there is no need for any Driver


Mario said...

thanks for your code!

Greetings from Chile!


Find More Articles