how to import / upload CSV file data to SQL Server database in ASP.Net using C#?  

  RSS

Piyush Goyal
(@gpiyush152)
Active Member Registered
Joined: 1 year ago
Posts: 5
25/05/2018 2:53 pm  
how to save csv data into database.

piyush goyal


ReplyQuote
TechGroup Admin
(@admin)
Eminent Member Admin
Joined: 1 year ago
Posts: 11
25/05/2018 4:06 pm  
Hi Piyush,
This Solution Can solve your problem.
 
using System.IO;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
 
protected void Upload(object sender, EventArgs e)
{
    //Upload and save the file
    string csvPath = Server.MapPath("~/Files/") + Path.GetFileName(FileUpload1.PostedFile.FileName);
    FileUpload1.SaveAs(csvPath);
 
    DataTable dt = new DataTable();
    dt.Columns.AddRange(new DataColumn[3] { new DataColumn("Id", typeof(int)),
            new DataColumn("Name", typeof(string)),
            new DataColumn("Country",typeof(string)) });
 
 
    string csvData = File.ReadAllText(csvPath);
    foreach (string row in csvData.Split('\n'))
    {
        if (!string.IsNullOrEmpty(row))
        {
            dt.Rows.Add();
            int i = 0;
            foreach (string cell in row.Split(','))
            {
                dt.Rows[dt.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.Customers";    //dbo.Customers your table
            con.Open();
            sqlBulkCopy.WriteToServer(dt);
            con.Close();
        }
    }
}

TopicCreator liked
ReplyQuote
Share:
  
Working

Please Login or Register