Import (Upload) CSV file data to SQL Server DataBase in ASP.Net using C#

Description:

In this example we explain that how to upload CSV file data to SQL Server database in asp.net application using C#.

Or how to Import CSV file data to SQL Server database in asp.net.generally we read the CSV file Data and inserted it into SQL Server Database. Sometimes we have requirement like we have thousands of record are available in CSV file so it is very time consuming if we inserted it manually to the Database by application so to overcome his we develop the code in which you have to just upload the CSV format file and it will automatically read the data (records) by separating delimiter and inserting it in SQL Server database.

Here we also use the SQLBulkCopy class to insert the data.

Code:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.IO;

using System.Data;

using System.Data.SqlClient;

using System.Configuration;

public partial class CSVUplaod : System.Web.UI.Page

{

    protected void Page_Load(object sender, EventArgs e)

    {

    }

    protected void CSVUpload_Click(object sender, EventArgs e)

    {

        //Upload and save the file

        string path = Server.MapPath("~/Upload/Files/") + Path.GetFileName(FileUpload1.PostedFile.FileName);

        FileUpload1.SaveAs(path);

        DataTable dtrecords = new DataTable();

        dtrecords.Columns.AddRange(new DataColumn[3] { new DataColumn("Id", typeof(int)),

            new DataColumn("Population", typeof(string)),

            new DataColumn("City",typeof(string)) });

        string CSVrecords = File.ReadAllText(path);

        foreach (string row in CSVrecords.Split('\n')) //one loop for one new line

        {

            if (!string.IsNullOrEmpty(row))

            {

                dtrecords.Rows.Add();

                int i = 0;

                foreach (string cell in row.Split(',')) //here you have to specify the delimeter here is , you can also specify ; or ther based on CSV file format

                {

                    dtrecords.Rows[dtrecords.Rows.Count - 1][i] = cell;

                    i++;

                }

            }

        }

        string consString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;

        using (SqlConnection con = new SqlConnection(consString))

        {

            using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))

            {

                //Set the database table name

                sqlBulkCopy.DestinationTableName = "dbo.City";

                con.Open();

                sqlBulkCopy.WriteToServer(dtrecords);

                con.Close();

            }

        }

    }

}