Skip to main content

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

Comments

Popular posts from this blog

How to hide url parameters in asp.net

There are different ways to Hide the URL in asp.net , you can choose any one from bellow options . Previously I was Explained about the  Difference between Convert.tostring and .tostring() method Example  ,   Reasons to use Twitter Bootstrap , How to Register AJAX toolkit in web.config file in asp.net a) Using Post Method b) Using Of Session . c) URL Encoding & decoding process . d) Using Server.Transfer() instead of Response.Redirect() method (1)Use a form and POST the information. This might require additional code in source pages, but should not require logic changes in the target pages (merely change Request.QueryString to Request.Form). While POST is not impossible to muck with, it's certainly less appealing than playing with QueryString parameters. (2)Use session variables to carry information from page to page. This is likely a more substantial effort compared to (1), because you will need to take session variable checking into account...

ASP.NET Routing

ASP.NET routing enables you to use URLs that do not have to map to specific files in a Web site. Because the URL does not have to map to a file, you can use URLs that are descriptive of the user's action and therefore are more easily understood by users. The ASP.NET MVC framework and ASP.NET Dynamic Data extend routing to provide features that are used only in MVC applications and in Dynamic Data applications. For more information about MVC, see ASP.NET MVC 3 . For more information about Dynamic Data, see ASP.NET Dynamic Data Content Map . In an ASP.NET application that does not use routing, an incoming request for a URL typically maps to a physical file that handles the request, such as an .aspx file. For example, a request for http://server/application/Products.aspx?id=4 maps to a file that is named Products.aspx that contains code and markup for rendering a response to the browser. The Web page uses the query string value of id=4 to determine what type of c...

How to send mail asynchronously in asp.net with MailMessage

With Microsoft.NET Framework 2.0 everything is asynchronous and we can send mail also asynchronously. This features is very useful when you send lots of bulk mails like offers , Discounts , Greetings . You don’t have to wait for response from mail server and you can do other task . By using     SmtpClient . SendAsync Method (MailMessage, Object)    you need to do  System.Net.Mail has also added asynchronous support for sending email. To send asynchronously, you need need to Wire up a SendCompleted event Create the SendCompleted event Call SmtpClient.SendAsync smtpClient.send() will initiate the sending on the main/ui  thread and would block.  smtpClient.SendAsync() will pick a thread from the .NET Thread Pool and execute the method on that thread. So your main UI will not hang or block . Let's create a simple example to send mail. For sending mail asynchronously you need to create a event handler that will notify that mail success...