0 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 post i'm going to explain how to read CSV file and save the data to sql server In ASP.NET using C# or VB.NET.

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.

1using System.Data;

2using System.Data.SqlClient;

3using Microsoft.VisualBasic.FileIO;

Now write below mentioned code in click event of button.

C# CODE

01protected void Button1_Click(object sender, EventArgs e)

02    {

03        DataTable tblReadCSV = new DataTable();

04 

05        tblReadCSV.Columns.Add("FirstName");

06        tblReadCSV.Columns.Add("LastName");

07        tblReadCSV.Columns.Add("Department");

08 

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

10 

11        csvParser.Delimiters = new string[] { "," };

12        csvParser.TrimWhiteSpace = true;

13        csvParser.ReadLine();

14 

15        while (!(csvParser.EndOfData == true))

16        {

17            tblReadCSV.Rows.Add(csvParser.ReadFields());

18        }

19 

20        //Create SQL Connection, Sql Command and Sql DataAdapter to save CSV data into SQL Server

21        string strCon = ConfigurationManager.ConnectionStrings["testdbConnectionString"].ConnectionString;

22        string strSql = "Insert into Employees(FirstName,LastName,Department) values(@Fname,@Lname,@Dept)";

23        SqlConnection con = new SqlConnection(strCon);

24        SqlCommand cmd = new SqlCommand();

25        cmd.CommandType = CommandType.Text;

26        cmd.CommandText = strSql;

27        cmd.Connection = con;

28        cmd.Parameters.Add("@Fname", SqlDbType.VarChar, 50, "FirstName");

29        cmd.Parameters.Add("@Lname", SqlDbType.VarChar, 50, "LastName");

30        cmd.Parameters.Add("@Dept", SqlDbType.VarChar, 50, "Department");

31 

32        SqlDataAdapter dAdapter = new SqlDataAdapter();

33        dAdapter.InsertCommand = cmd;

34        int result = dAdapter.Update(tblReadCSV);

35 

36    }

VB.NET CODE

01Protected Sub Button1_Click(sender As Object, e As EventArgs)

02 Dim tblReadCSV As New DataTable()

03 

04 tblReadCSV.Columns.Add("FirstName")

05 tblReadCSV.Columns.Add("LastName")

06 tblReadCSV.Columns.Add("Department")

07 

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

09 

10 csvParser.Delimiters = New String() {","}

11 csvParser.TrimWhiteSpace = True

12 csvParser.ReadLine()

13 

14 While Not (csvParser.EndOfData = True)

15  tblReadCSV.Rows.Add(csvParser.ReadFields())

16 End While

17 

18 'Create SQL Connection, Sql Command and Sql DataAdapter to save CSV data into SQL Server

19 Dim strCon As String = ConfigurationManager.ConnectionStrings("testdbConnectionString").ConnectionString

20 Dim strSql As String = "Insert into Employees(FirstName,LastName,Department) values(@Fname,@Lname,@Dept)"

21 Dim con As New SqlConnection(strCon)

22 Dim cmd As New SqlCommand()

23 cmd.CommandType = CommandType.Text

24 cmd.CommandText = strSql

25 cmd.Connection = con

26 cmd.Parameters.Add("@Fname", SqlDbType.VarChar, 50, "FirstName")

27 cmd.Parameters.Add("@Lname", SqlDbType.VarChar, 50, "LastName")

28 cmd.Parameters.Add("@Dept", SqlDbType.VarChar, 50, "Department")

29 

30 Dim dAdapter As New SqlDataAdapter()

31 dAdapter.InsertCommand = cmd

32 Dim result As Integer = dAdapter.Update(tblReadCSV)

33 

34End Sub