View Single Post
  #1 (permalink)  
Old August 2nd, 2016, 06:37 AM
mas_Far mas_Far is offline
Registered User
Points: 5, Level: 1
Points: 5, Level: 1 Points: 5, Level: 1 Points: 5, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Aug 2016
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Copying csv file to database(sql server) using c#

i want to copy .csv files to database without the repetition of data.so far i have copied the .csv while without checking the validation of repetition . i need to aslo verify that same data columns are not present



This is my 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;

namespace dataReader
{
public partial class _default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{

}

protected void Upload(object sender, EventArgs e)
{
//Upload and save the file
string csvPath = Server.MapPath("~/Farheen 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";
con.Open();
sqlBulkCopy.WriteToServer(dt);
con.Close();
}
}
}
}
}