CODEWITHSUNDEEP
c#sql-serverexceptionconstraints
Dominique
Dominique

Reputation: 17491

Can I retrieve the impacted constraint(s) from a InvalidConstraintException?

Two days ago, I have asked this question on how to modify deletion rules of foreign key constraints at runtime. That question has been answered, but now I would like to generalise my program:

Currently I have:

try
{ 
dt_MainTable1.Rows[0].Delete();
}
catch (Exception ex)
{
    if (MessageBox.Show("In order to avoid data corruption, not only " + 
                          " the required tupple will be deleted, " + 
                          "but also the ones in other tables, " + 
                          "referring to that tupple. " + 
                          "Is that what you want?",
                        "Warning",
                        MessageBoxButton.YesNo) == MessageBoxResult.Yes)
    {
        (ForeignKeyConstraint)(dt_SubTable1.Constraints[0]).DeleteRule 
          = Rule.Cascade;
        dt_MainTable1.Rows[0].Delete();
        (ForeignKeyConstraint)(dt_SubTable1.Constraints[0]).DeleteRule
          = Rule.None;
    }
}

... and this is what I would like to have: I try to do something (like a modification or a deletion), but I fall into a System.Data.InvalidConstraintException, based on a Constraint. Then I ask a question and based on the answer I turn the impacted Constraint's Deletion Rule or Modification Rule into None or Cascade. The big problem: can I retrieve the impacted Constraint from the generated System.Data.InvalidConstraintException? (I've been debugging and checking the properties of the System.Data.InvalidConstraintException, but I didn't find anything), just to show what I want (see the ex.FindRelevantConstraints() method):

try
{ 
LaunchDeleteAction(...);
}
catch (Exception ex)
{
    if (MessageBox.Show("In order to avoid data corruption, not only " + 
                          " the required tupple will be deleted, " + 
                          "but also the ones in other tables, " + 
                          "referring to that tupple. " + 
                          "Is that what you want?",
                        "Warning",
                        MessageBoxButton.YesNo) == MessageBoxResult.Yes)
    {
        for each (ForeignKeyConstraint C in ex.FindRelevantConstraints()   <===
          C.DeleteRule = Rule.Cascade;
        LaunchDeleteAction(...);
        for each (ForeignKeyConstraint C in ex.FindRelevantConstraints()   <===
          C.DeleteRule = Rule.None;
    }
}

Edit after some more investigation

Meanwhile I've found out that the Exception, ex, contains the name of the corresponding Constraint, as you can see in following watch window excerpt:

ex.Message : Cannot delete this row because constraints are enforced on relation Constraint1, and deleting this row will strand child rows.

I mean, it can't possibly be the intention to parse the Exception's message in order to get a reference to the Constraint object I'm looking for?

Desperate edit
In the meantime, I've decided to give special names to the Constraint objects I'm creating, like you can see here:

dt_SubTable1.Constraints.Add(
  new ForeignKeyConstraint("|FKC|MainTable.SubId|SubTable.Id|", 
                           dt_SubTable.Columns["Id"],
                           dt_MainTable.Columns["SubId"])
    { DeleteRule = Rule.None, 
      UpdateRule = Rule.None });

As you can see, I can use the pipe character for separating the table names and the dots for finding the column names, but that's really a desperate idea :-)

Upvotes: 3

Views: 721

Answers (2)

Heinzi
Heinzi

Reputation: 172280

To directly answer the question in your title:

Can I retrieve the impacted constraint(s) from a InvalidConstraintException?

No, unfortunately, you can't.

According to the reference source, InvalidConstraintException does not store any useful information apart from the message text and (if applicable) an inner exception.

Some additional reading reveals that the System.Data methods pass all relevant information (e.g. the constraint name) to the ExceptionBuilder methods, but, alas, this information is only used to create the Exception message and then thrown away.

Upvotes: 3

ste-fu
ste-fu

Reputation: 7454

As a general rule it is better to test for the presence or absence of something that will cause an exception rather than to catch it.

Likewise handling user interaction in a catch block is not ideal and changing the data integrity constraints to perform the operation seems the wrong way to deal with it.

A better approach might be to:

  • Test for the existence of any related data in other tables
  • If found: present a message to the user
  • If user clicks proceed: delete the related data first, before finally deleting the parent record

If the relationship is a simple as the main and sub tables in the example you could try something like

if (dt_SubTable1.AsEnumerable.Any(r => s => r.Field("ForeignIdField") == MainId))

If you have a few DataTable instances you could iterate over them checking the Constraints properties in code.

foreach (var constraint in dt_Sub1.Contstraints)
{
    if (constraint is ForeignKeyConstraint && constraint.RelatedTable.TableName = dt_Main.TableName)
    // check for matching data
}

If you are trying a more generalized approach This question has several methods showing how you can find the foreign keys for a given table. You could create a method FindRelevantConstraints(string tableName) wrapping the system stored procedure sp_fkeys which would return both the table name and the name of the field.

This will allow you to create a query for any and all sub tables that depend on your primary table using the id value

Upvotes: 3

Related Questions