Get Row Values From SqlException [Resolved]

Posted by Sharpcnet under C# on 11/20/2013 | Points: 10 | Views : 1394 | Status : [Member] | Replies : 5
I'm saving a DataTable(dt) to database table(tblCountry) using bulkcopy. There are constraints set for tblCountry , so exceptions throw up if there is a mistake in dt.
For eg: tblCountry has CountryId as the Primary Key. If dt has 2 records with same CountryId, that would throw an exception. I would like to get that CountryId which is a duplicate, when there is an exception. Is this possible with C# or maybe Stored Procedure SqlServer 2008

protected void btnSave_Click()
{
try
{
conn.Open();

SqlBulkCopy bulkcopy = new SqlBulkCopy(conn);
bulkcopy.DestinationTableName = "tblCountry";
bulkcopy.ColumnMappings.Add(0, 1);
bulkcopy.ColumnMappings.Add(1, 2);
bulkcopy.ColumnMappings.Add(2, 3);
bulkcopy.WriteToServer(dt);

conn.Close();
}
Catch(SqlException ex)
{
throw ex;
}
}





Responses

Posted by: vishalneeraj-24503 on: 11/20/2013 [Member] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
Or alternatively, you can check duplicates with datatable,

var UniqueRows = dt.AsEnumerable().Distinct(DataRowComparer.Default);
DataTable dt2 = UniqueRows.CopyToDataTable();
return dt2;

//or you can also do
var allDuplicates = dt.AsEnumerable()
.GroupBy(dr => dr.Field<string>("CountryId"))
.Where(g => g.Count() > 1)
.SelectMany(g => g)
.ToList();

//or you can also check duplicates as

var allDuplicates = dt.AsEnumerable()
.GroupBy(dr => colsToConsider.Select(dr.Field<object>)
.ToArray(),
new ArrayEqualityComparer<object>())
.Where(g => g.Count() > 1)
.SelectMany(g => g)
.CopyToDataTable();

Please let me know.

Sharpcnet, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: vishalneeraj-24503 on: 11/20/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Hi you can try using this:-
if (sqlEx.Message.StartsWith("Cannot insert duplicate key row in object"))
{
message = sqlEx.Message;
}

or you can also check like this:-
For duplicate error the number is 2601

catch (SqlException e)
{
switch (e.Number)
{
case 2601:
// Do something.
break;
default:
throw;
}
}

Sharpcnet, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: vishalneeraj-24503 on: 11/20/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Hi you can also write like below:-

catch (SqlException ex)
{
for (int i = 0; i < ex.Errors.Count; i++)
{
errorMessages.Append("Index #" + i + "\n" +
"Message: " + ex.Errors[i].Message + "\n" +
"Error Number: " + ex.Errors[i].Number + "\n" +
"LineNumber: " + ex.Errors[i].LineNumber + "\n" +
"Source: " + ex.Errors[i].Source + "\n" +
"Procedure: " + ex.Errors[i].Procedure + "\n");
}
Console.WriteLine(errorMessages.ToString());
}

Hope this helps you.

Sharpcnet, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sharpcnet on: 11/20/2013 [Member] Starter | Points: 25

Up
0
Down
Hi. Correct me if I am wrong but those solutions will not give me the 'CountryId' which is a duplicate.

Sharpcnet, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 11/20/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Get the distinct records before adding to sqlBulkCopy...

DataView view = new DataView(table);

DataTable distinctValues = view.ToTable(true, "Column1", "Column2" ...);


reference:
http://social.msdn.microsoft.com/Forums/en-US/0093bc96-c646-4e23-bf07-5ae1b0ac3119/sqlbulkcopy-and-unique-identity-columns?forum=adodotnetdataproviders
http://www.codeproject.com/Tips/153008/Select-DISTINCT-records-based-on-specified-fields

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Sharpcnet, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response