.net - ADO.net SqlDataAdapter.Update bulk insert how to detect primary key violations? -

i looking @ problem have older data access code uses sqldataadapter bulk insert operations. destination table has primary key constraint, , need identify rows try insert violate primary key (i.e. detect duplicates) , take action on these rows.

most of time, inserts not have duplicates, need handle cases when there is. via experimentation, found doing bulk insert , looking violations faster individually checking each row it's existence before adding.

the code follows.

foreach (datatable datatable in dataset.tables)   {     try     {       list<string> columns = new list<string>();       foreach (datacolumn column in datatable.columns)         columns.add(column.columnname);        string commandtext = "select " + string.join(",", columns.toarray()) + " " + datatable.tablename;        sqlcommand sqlcommand = new sqlcommand(commandtext, conn);       sqldataadapter dataadapter = new sqldataadapter(sqlcommand);       dataadapter.continueupdateonerror = true;       sqlcommandbuilder sqlbuilder = new sqlcommandbuilder(dataadapter);       dataadapter.update(datatable);      }     catch (exception)     {       // no exception should thrown continueupdateonerror = true     }   }    return dataset; 

note dataadapter.continueupdateonerror = true; means errors not throw exceptions (the exception block never entered)

so, once above has been run, can errors, including primary key violations, using code below...

foreach (datatable datatable in dataset) {     foreach (datarow datarow in datatable.rows)     {          if (datarow.haserrors)         {            // here want detect if error primary key violation, or else         }     } } 

so, can detect rows have some sort of error via .haserrors above, can't see way detect error primary key violation. there is rowerror property, text like... violation of primary key constraint 'pk_mykey'. cannot insert duplicate key in object 'dbo.my_table'. duplicate key value (6215). statement has been terminated.

the way can see distinguish other error (e.g. disk error etc) searching of above text, not seem robust or practice (the error text may change, or if running different language / culture text different)

the datarow has errorstate property (which seems added, , there error object has same text, , no real information.

is there way get better information on actual error, e.g. if primary key violation, or otherwise in above scheme?

thanks in advance!

one option bulk insert data empty staging table , run stored procedure merges data staging table real table. if not issue, can adjust query exclude duplicates.

i recommend use sqlbulkcopy bulk operations. experience, sqlbulkcopy fastest way bulk load data in .net sql server. there lot of useful properties can set.

https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy(v=vs.110).aspx