Get Row Values From SqlException [Resolved]

Posted by Sharpcnet under C# on 11/20/2013 | Points: 10 | Views : 2406 | Status : [Member] | Replies : 5

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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;
}
}

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

0	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

0	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

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

0	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

0	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

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

Latest Posts