My Technical Notes

Sunday, 21 March 2010

Extracting info From SqlException when attempted Deletion causes a Reference Violation

When you try to delete a row using LINQ to SQL using code similar to the following:


context.GetTable<Category>().DeleteOnSubmit(category);
a SqlException can be thrown if this object has any child rows associated with it.

I have written code below, which, given a SqlException, first tests whether it was thrown due to a reference constraint violation, and finds out the contraint name, the name of the table that the child row exists in, and the name of the column that acts as the foreign key:


try
{
   // do something here
}
catch (SqlException ex)
{
    var msg = ex.Message;
    if (msg.Contains("DELETE") && msg.Contains("REFERENCE"))
    {
        var constraintName = Regex.Match(msg, "constraint \"([^\\\"]*)\"").Groups[1].Value;
        var tableName = Regex.Match(msg, "table \"dbo.([^\"]*)").Groups[1].Value;
        var column = Regex.Match(msg, "column '([^']*)").Groups[1].Value;
        // do something with this information now.
    }
}

No comments: