在进行客户数据修复的过程中，经常需要在多个表中删除数据。这些表之间存在着复杂的引用关系，即一个表可能引用另一个表，而那个表又可能引用更多的表。为了删除一个表中的数据，必须首先删除所有引用它的表中的数据，这通常意味着需要按照严格的顺序删除多达六个层级和220个表。为了避免繁琐且容易出错的工作，创建了一个工具来生成级联删除脚本。这个工具最初是为了支持Oracle数据库而开发的，但也可以扩展以支持SQL Server。源代码是公开的，以便其他人可以对其进行扩展和使用。
这个工具的代码结构分为三个部分。最底层部分用于获取数据库字典信息，如主键和引用表。中间部分是核心逻辑，用于生成脚本。最上层是用户界面，一个Windows应用程序界面，允许用户生成脚本。这个工具是用VB.NET和Visual Studio 2010实现的。
在底层部分，引入了一个接口，以便可以扩展以支持不同的数据库。
Public Interface IDictionaryInfo
' 获取表的主键
Function GetPKColName(ByVal strTableName As String) As String
' 获取外键引用表及其引用列
' 使用列表而不是字典作为返回类型，因为一个引用表可能有多个引用列
Function GetFKReferences(ByVal strTableName As String) As List(Of KeyValuePair(Of String, String))
End Interface
以下是支持Oracle数据库的接口实现。
' Oracle数据库的IDictionaryInfo实现
Public Class OracleDictionaryInfo
Implements IDictionaryInfo
' 获取表的主键
Public Function GetPKName(ByVal strTableName As String) As String Implements IDictionaryInfo.GetPKColName
strTableName = strTableName.ToUpper
' Oracle表在Oracle字典中以大写形式存储
' SQL查询以获取Oracle字典中的主键
Dim strSql As String = String.Format("SELECT cols.column_name FROM user_constraints cons, user_cons_columns cols WHERE cols.table_name = '{0}' AND cons.constraint_type = 'P' AND cons.constraint_name = cols.constraint_name", strTableName)
Dim dt = GetDataTable(strSql)
If dt.Rows.Count > 0 Then
Return dt.Rows(0)(0)
Else
Return String.Empty
End If
End Function
' 获取外键表及其引用列
Public Function GetFKReferences(ByVal strTableName As String) As System.Collections.Generic.List(Of System.Collections.Generic.KeyValuePair(Of String, String)) Implements IDictionaryInfo.GetFKReferences
strTableName = strTableName.ToUpper
Dim strSql As String = String.Format("select C.TABLE_NAME, C.COLUMN_NAME from user_cons_columns C where C.CONSTRAINT_NAME IN ( Select constraint_name from (user_constraints) where constraint_type='R' and r_constraint_name in (select constraint_name from user_constraints where constraint_type in ('P','U') and table_name='{0}')", strTableName)
Dim dt = GetDataTable(strSql)
Dim tnq = From dr In dt.Rows Select New KeyValuePair(Of String, String)(CType(dr(0), String), CType(dr(1), String))
Return tnq.ToList()
End Function
End Class
中间部分收集数据库字典信息的主键和引用。它调用一个内部递归函数以级联方式生成删除脚本。
' 生成脚本的核心类
Public Class GenScriptCore
Private iDic As IDictionaryInfo
Private Sub New()
' 不允许实例化
End Sub
' 生成脚本的主要函数。准备数据库类型、连接字符串和表名来调用函数
Public Shared Function GenScript(ByVal enumDB As DB, ByVal strConnection As String, ByVal strTableName As String) As String
Dim oc As New GenScriptCore
Select Case [enumDB]
Case DB.Oracle
oc.iDic = New OracleDictionaryInfo(strConnection)
Case DB.SQLServer
Throw New Exception("SQL server is not supported yet. But you can implement it by implementing the interface of IDictionaryInfo for SQL Server")
End Select
Dim strPKCol = oc.iDic.GetPKColName(strTableName)
If String.IsNullOrEmpty(strPKCol) Then
Throw New Exception(String.Format("table '{0}' does not exist or has no primary key / unique key", strTableName))
End If
Return oc.gs(strTableName, strPKCol)
' 调用主方法
End Function
' 通过递归调用生成脚本的核心函数
Private Function gs(ByVal strTableName As String, ByVal strFilterColumn As String, Optional ByVal strInFilter As String = Nothing) As String
Dim sb As New System.Text.StringBuilder
Dim strPKCol = iDic.GetPKColName(strTableName)
If String.IsNullOrEmpty(strPKCol) Then
Throw New Exception(String.Format("table '{0}' has no primary key / unique key", strTableName))
End If
' 定义引用表的where子句
Dim strNextLevelWhereClause As String
If Not String.IsNullOrEmpty(strInFilter) Then
strNextLevelWhereClause = String.Format("{0} in ({1})", strFilterColumn, strInFilter)
Else
strNextLevelWhereClause = String.Format("{0} = p_{0}", strFilterColumn)
End If
Dim strNextLevelFilter = String.Format("Select {0} from {1} where {2}", strPKCol, strTableName, strNextLevelWhereClause)
' 查找外键引用表并首先删除它们
Dim lstFKTableFKCol = iDic.GetFKReferences(strTableName)
For Each kv In lstFKTableFKCol
sb.AppendLine(gs(kv.Key, kv.Value, strNextLevelFilter))
Next
' 删除自身
Dim strDeleteWhereClause As String
If Not String.IsNullOrEmpty(strInFilter) Then
strDeleteWhereClause = String.Format("{0} in ({1})", strFilterColumn, strInFilter)
Else
strDeleteWhereClause = String.Format("{0} = p_{0}", strFilterColumn)
End If
sb.AppendFormat("delete {0} where {1};", strTableName, strDeleteWhereClause)
sb.AppendLine()
Return sb.ToString
End Function
End Class
用户界面收集用户输入的数据库类型、连接字符串和表名。它验证输入并调用中间层来生成脚本，该脚本显示在窗口中，同时复制到剪贴板。如果应用程序抛出异常，用户界面将以红色显示错误消息。
这个小工具对帮助很大。希望这个小工具也能帮到其他人。