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

Tweet

If you like this post than join us or share