Abusing the String.Format() function, or an efficent way to check if a record can be deleted?

In my main project at work, I'm building an application to configure a database. An issue I had to overcome was how to determine if a record could be deleted, or if its deletion would result in orphan records.

I have come up with a model of creating an AssertCanDelete() function that checks all of the tables that could be referencing a given record, and throws an exception saying the name of the table that is referencing the record. The default format for this method is as follows:

Create a Select statement that has a Select Count(*) sub query for each table that could reference the object.

Go through the result set and if any value is greater than 0, then I know that it is being referenced, and can't be deleted.

I used the the String.Format in my Command Text and ended up something like this:

            cm.CommandText = String.Format("SELECT * FROM " & _
                    "(SELECT COUNT(*) FROM {0}{3} WHERE {3}.{1} = :{1} AND {3}.{2} = :{2} ), " & _
                    "(SELECT COUNT(*) FROM {0}{4} WHERE {4}.{1} = :{1} AND {4}.{2} = :{2} ), " & _
                    "(SELECT COUNT(*) FROM {0}{5} WHERE {5}.{1} = :{1} AND {5}.{2} = :{2} ) ", _
                            DatabaseOwner, "PRTNR_ID", "SECT_NAME", "PRTNR_SECT_DE", "PRTNR_SECT_SRVC_FIL", "PRTNR_SECT_WF_STEP_ROLE")
            cm.Parameters.Add("PRTNR_ID", OracleType.Number).Value = criteria.PrtnrId
            cm.Parameters.Add("SECT_NAME", OracleType.VarChar).Value = criteria.SectName

Which I kind of like. The first "{0}" is the database table schema or database owner. The second "{}" for each line is the Table Name, which then gets repeated with a . and the first part of the primary key "{1}" and AND'ed to the second part of the primary key "{2}". It saved me alot of time having to write every SQL statement and every table name multiple times. I could also just copy a line, and insert the table name once, and I was good to go. Another useful way to not repeat yourself.

When I was following this patern for another table, things quickly got ugly when I realized that there where multiple columns that would be referencing my record. Below is the code, what do you think? Is it an abuse of the String.Format() function and the entire process needs to be trashed, or is it an efficient way to check if a record can be deleted?

    Private Shared Sub AssertCanDelete(ByVal tr As OracleTransaction, ByVal criteria As Criteria)
        Using cm As OracleCommand = tr.Connection.CreateCommand()
            cm.Transaction = tr
            cm.CommandType = CommandType.Text
            cm.CommandText = String.Format("SELECT * FROM " & _
                    "(SELECT COUNT(*) FROM {0}{2} WHERE :{1} in ({2}.{1}, {2}.INIT_{1}, {2}.COMPARE_{1}) ), " & _
                    "(SELECT COUNT(*) FROM {0}{3} WHERE {3}.{1} = :{1} ), " & _
                    "(SELECT COUNT(*) FROM {0}{4} WHERE {4}.{1} = :{1} ), " & _
                    "(SELECT COUNT(*) FROM {0}{5} WHERE {5}.{1} = :{1} ), " & _
                    "(SELECT COUNT(*) FROM {0}{6} WHERE {6}.{1} = :{1} ), " & _
                    "(SELECT COUNT(*) FROM {0}{7} WHERE {7}.{1} = :{1} ), " & _
                    "(SELECT COUNT(*) FROM {0}{8} WHERE :{1} in ({8}.{1}, {8}.INIT_{1}, {8}.COMPARE_{1}) ), " & _
                    "(SELECT COUNT(*) FROM {0}{9} WHERE :{1} in ({9}.{1}, {9}.INIT_{1}) ), " & _
                    "(SELECT COUNT(*) FROM {0}{10} WHERE :{1} in ({10}.{1}, {10}.INIT_{1}, {10}.COMPARE_{1}) ), " & _
                    "(SELECT COUNT(*) FROM {0}{11} WHERE :{1} in ({11}.{1}, {11}.INIT_{1}, {11}.COMPARE_{1}), {11}.NEXT_{1}) ), " & _
                    "(SELECT COUNT(*) FROM {0}{12} WHERE :{1} in ({12}.{1}, {12}.INIT_{1}) ), " & _
                    "(SELECT COUNT(*) FROM {0}{13} WHERE :{1} in ({13}.{1}, {13}.INIT_{1}, {13}.COMPARE_{1}) ), " & _
                    "(SELECT COUNT(*) FROM {0}{14} WHERE :{1} in ({14}.{1}, {14}.INIT_{1}, {14}.COMPARE_{1}), {14}.NEXT_{1}) ) ", _
                            DatabaseOwner, "SECT_NAME", "sect_valid_ref", "sect_de_ref", "pkg_sect_de_ref", "prtnr_sect_de", "prtnr_sect", "pkg_sect_ref", "pkg_sect_valid_ref", "pkg_sect_de_fltr_ref", "pkg_sect_de_valid", "pkg_sect_de_nav_ref", "sect_de_fltr_ref", "sect_de_valid_ref", "sect_sect_de_nav")
            cm.Parameters.Add("SECT_NAME", OracleType.VarChar).Value = criteria.SectName


            Using dr As SafeDataReader = New SafeDataReader(cm.ExecuteReader())
                dr.Read()
                If dr.GetInt32(0) > 0 Then
                    Throw New ConstraintException("Section (" & criteria.SectName & ") can not be deleted because it is currently referenced in the Section Validation Reference table")
                End If
                If dr.GetInt32(1) > 0 Then
                    Throw New ConstraintException("Section (" & criteria.SectName & ") can not be deleted because it is currently referenced in the Section Data Element Reference table")
                End If
                If dr.GetInt32(2) > 0 Then
                    Throw New ConstraintException("Section (" & criteria.SectName & ") can not be deleted because it is currently referenced in the Package Section Data Element Reference table")
                End If
                If dr.GetInt32(3) > 0 Then
                    Throw New ConstraintException("Section (" & criteria.SectName & ") can not be deleted because it is currently referenced in the Partner Section Data Element table")
                End If
                If dr.GetInt32(4) > 0 Then
                    Throw New ConstraintException("Section (" & criteria.SectName & ") can not be deleted because it is currently referenced in the Partner Section table")
                End If
                If dr.GetInt32(5) > 0 Then
                    Throw New ConstraintException("Section (" & criteria.SectName & ") can not be deleted because it is currently referenced in the Package Section Reference table")
                End If
                If dr.GetInt32(6) > 0 Then
                    Throw New ConstraintException("Section (" & criteria.SectName & ") can not be deleted because it is currently referenced in the Package Section Validation Reference table")
                End If
                If dr.GetInt32(7) > 0 Then
                    Throw New ConstraintException("Section (" & criteria.SectName & ") can not be deleted because it is currently referenced in the Package Section Data Element Filter Reference table")
                End If
                If dr.GetInt32(8) > 0 Then
                    Throw New ConstraintException("Section (" & criteria.SectName & ") can not be deleted because it is currently referenced in the Package Section Data Element Validation Reference table")
                End If
                If dr.GetInt32(9) > 0 Then
                    Throw New ConstraintException("Section (" & criteria.SectName & ") can not be deleted because it is currently referenced in the Package Section Data Element Navigation Reference table")
                End If
                If dr.GetInt32(10) > 0 Then
                    Throw New ConstraintException("Section (" & criteria.SectName & ") can not be deleted because it is currently referenced in the Section Data Element Filter Reference table")
                End If
                If dr.GetInt32(11) > 0 Then
                    Throw New ConstraintException("Section (" & criteria.SectName & ") can not be deleted because it is currently referenced in the Section Data Element Validation Reference table")
                End If
                If dr.GetInt32(12) > 0 Then
                    Throw New ConstraintException("Section (" & criteria.SectName & ") can not be deleted because it is currently referenced in the Section Data Element Navigation Reference table")
                End If
            End Using
        End Using
    End Sub

Using LINQ To Ensure No Objects Have Duplicate Keys

I had an interseting business requirement the other day. I'm working with a CSLA Collection of objects, and they wanted the end user to be able to be able to change a property that is one of the three columns that makes up the primary key. I sat down and started using some LINQ to objects and was amazed at how much power it has in just a 6 lines of code. It was also my first time using the Group By Function

Normally with the group by, you perform some agregate functions on the group. So the "Into Group" would be "Into Sum(naviagation.SectDeNavOrdrNbr)". But by Grouping it into group, the result is a collection that contains the objects in the Group By Clause (SectDeNavOrdNbr, SectName, DeName) and then a collection of all the objects that are in that group.

So after I have my group by clause, I perform a contains to see if any collections of the group have more than one item in them. The must amazing part, is techinally, it only took 1 line of code! In the words of a dear friend of mine, "God Bless .Net".

 
  Dim hasDuplicatePrimaryKeys As Boolean = ( _
      From navigation As SectionDataElementNavigationReference In Me _
      Group By navigation.SectDeNavOrdNbr, navigation.SectName, navigation.DeName _                                          
      Into Group).Contains(Function(c) c.Group.Count() > 1)

  If (hasDuplicatePrimaryKeys) Then
      Throw New InvalidConstraintException("More than one Section Data Element Navigation Reference was found with the same Section Name, Data Element Name, and Section Data Element Navigation Order Number")
  End If