CODEWITHSUNDEEP
c#csvimportsqlbulkcopy
Mr A
Mr A

Reputation: 6778

duplicate products added when importing csv files

I am using the code below to import the csv files onto sqlserver 2008, the issue is , its adding duplicate records onto the table , how can i check to avoid duplicate records import , below is the code which i am using , i have copied from somewhere , and have forgotten the link so cant reference it , the code works fine as it imports the data , but it doesnt checks before importing , the primary key i am using for my table is productid, below is the code, any assistance or suggestion will be appreciated:

SqlConnection con = new SqlConnection(@"Data Source=xxxxxxxx;Initial Catalog=xxxx;Persist Security Info=True;User ID=xx;Password=xxx");
    string filepath = Server.MapPath(FileUpload1.FileName);//"C:\\params.csv";
        StreamReader sr = new StreamReader(filepath);
        string line = sr.ReadLine();
        string[] value = line.Split(',');
        DataTable dt = new DataTable();
        DataRow row;
        foreach (string dc in value)
        {
            dt.Columns.Add(new DataColumn(dc));
        }

        while ( !sr.EndOfStream )
        {
            value = sr.ReadLine().Split(',');
            if(value.Length == dt.Columns.Count)
            {
                row = dt.NewRow();
                row.ItemArray = value;
                dt.Rows.Add(row);
            }
        }
        SqlBulkCopy bc = new SqlBulkCopy(con.ConnectionString, SqlBulkCopyOptions.TableLock);
        bc.DestinationTableName = "products";
        bc.BatchSize = dt.Rows.Count;
        con.Open();
        bc.WriteToServer(dt);
        bc.Close();
        con.Close(); 

}

Upvotes: 0

Views: 749

Answers (1)

Laur Ivan
Laur Ivan

Reputation: 4177

Normally, you should be able to set the PK field to be the productid. Insert would fail automatically if it's duplicated...

You could also sort the DataTable, pass through and process the duplicated rows with some logic.

Another solution would be to add the productid in some sort of searchable array and check against it before adding a new row.

HTH,

Upvotes: 1

Related Questions