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.

Previous Post  I was Explained about the  jQuery - Allow Alphanumeric (Alphabets & Numbers) Characters in Textbox using JavaScript , Fileupload show selected file in label when file selected , Check for file size with JavaScript before uploading , Asp.Net AjaxFileUpload Control With Drag Drop And Progress Bar.

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