主题：删除基本资料行前判断是否被引用

'基础资料删除前判断是否已经被引用。条件是引用的列名必须相同。请问还能优化吗？

Dim bm1 As String = "表A"      '删除行所在表
Dim lm As String = "第一列"      '以该列为依据,判断是否被引用
Dim lm2,hhz As String
Dim bm2 As String = Tables(bm1).DataTable.name
For Each dt As DataTable In DataTables
    If DataTables(dt.Name).DataCols.Contains(lm) Then
        If Tables(bm1).current IsNot Nothing And Tables(bm1).current.isnull(lm) = False Then
            For i As Integer = Tables(bm1).BottomPosition To Tables(bm1).TopPosition Step -1
                Dim dr As Row = Tables(bm1).Rows(i)
                Dim d As String = lm & " = '" & dr(lm) & "'" 
                Dim d2 As String = "count(" & lm & ")"
                Dim d1 As String
                Dim r As Integer = eval(dt.Compute(d2,d))
                 If bm2 = dt.name Then
                    If cdec(r) > 1 Then
                        d1 = dt.Find(d & " And " & lm & " Is not null")(lm)
                        hhz = """" & dr(lm) & """" & "已经被" & """" & dt.name & """引用,禁止删除!"
                        Return hhz
                    End If
                      Else
                    If cdec(r) > 0 Then
                        d1 = dt.Find(d & " And " & lm & " Is not null")(lm)
                        hhz = """" & dr(lm) & """" & "已经被" & """" & dt.name & """引用,禁止删除!"
                        Return hhz
                    End If
                End If
            Next
        End If
    End If
Next
Dim cmd As New SQLCommand
'cmd.C 指定数据源
For Each dt As DataTable In DataTables
    bm2 = dt.name
    If bm2 IsNot Nothing And DataTables(bm2).Type <> 4 And DataTables(bm2).Type <> 5 Then
        If DataTables(bm2).DataCols.Contains(lm) Then
            If Tables(bm1).TopPosition > -1 Then
                For i As Integer = Tables(bm1).BottomPosition To Tables(bm1).TopPosition Step -1
                    Dim lz As String = Tables(bm1).Rows(i)(lm)
                    Dim nm As String = "Select Count(*) From {" & bm2 & "} Where " & lm & " = " & "'" & lz & "'"
                    cmd.CommandText = nm
                    If bm2 <> CurrentTable.DataTable.name
                        If cmd.ExecuteScalar > 0 Then
                            hhz = """" & Tables(bm1).Rows(i)(lm) & """已被""" & bm2 & """引用,禁止删除!" & vbcrlf & "如果你确认该条记录已经更改或删除,请先保存以便及时更新后台数据,再进行相应操作!"
                            Return hhz
                            Exit For
                        End If
                    Else
                            If cmd.ExecuteScalar > 1 Then
                                hhz = """" & Tables(bm1).Rows(i)(lm) & """已被""" & bm2 & """引用,禁止删除!" & vbcrlf & "如果你确认该条记录已经更改或删除,请先保存以便及时更新后台数据,再进行相应操作!"
                                Return hhz
                                Exit For
                            End If
                    End If
                Next
            End If
        End If
    End If
Next

什么叫已被引用？

重新整理：

 

'引用判断.条件是判断的列名必须相同.
Dim bm1 As String = "表A"      '删除行所在表
Dim lm As String = "第一列"      '以该列为依据,判断是否被引用
Dim lm2,hhz As String
Dim bm2 As String = Tables(bm1).DataTable.name
For Each dt As DataTable In DataTables  '遍历各表
    If DataTables(dt.Name).DataCols.Contains(lm) Then '包含指定列
        If Tables(bm1).BottomPosition > -1 And Tables(bm1).Rows(Tables(bm1).BottomPosition).isnull(lm) = False Then '存在指定行
            For i As Integer = Tables(bm1).BottomPosition To Tables(bm1).TopPosition Step -1
                Dim dr As Row = Tables(bm1).Rows(i)
                Dim d As String = lm & " = '" & dr(lm) & "'"
                Dim d2 As String = "count(" & lm & ")"
                Dim d1 As String
                Dim r As Integer = eval(dt.Compute(d2,d))
                If bm2 = dt.name Then
                 If cdec(r) > 1 Then
                        d1 = dt.Find(d & " And " & lm & " Is not null")(lm)
                        hhz = """" & dr(lm) & """" & "已经被" & """" & dt.name & """引用,禁止删除!"
                        Return hhz
                      Exit For
                    End If
                Else
                    If cdec(r) > 0 Then
                        d1 = dt.Find(d & " And " & lm & " Is not null")(lm)
                        hhz = """" & dr(lm) & """" & "已经被" & """" & dt.name & """引用,禁止删除!"
                        Return hhz
                      Exit For
                    End If
                End If
            Next
        End If
    End If
If hhz IsNot Nothing Then
Return hhz
End If
Next

Dim cmd As New SQLCommand
'cmd.C 指定数据源
For Each dt As DataTable In DataTables
    If dt.name IsNot Nothing And DataTables(dt.name).Type <> 4 And DataTables(dt.name).Type <> 5 Then
        If DataTables(dt.name).DataCols.Contains(lm) Then
            If Tables(bm1).BottomPosition > -1 Then
                For i As Integer = Tables(bm1).BottomPosition To Tables(bm1).TopPosition Step -1
                    Dim lz As String = Tables(bm1).Rows(i)(lm)
                    Dim nm As String = "Select Count(*) From {" & dt.name & "} Where " & lm & " = " & "'" & lz & "'"
                    cmd.CommandText = nm
                    If bm2 = dt.name Then
                       If cmd.ExecuteScalar > 1 Then
                            hhz = """" & Tables(bm1).Rows(i)(lm) & """已被""" & dt.name & """引用,禁止删除!" & vbcrlf & "如果你确认该条记录已经更改或删除,请先保存以便及时更新后台数据,再进行相应操作!"
                            Return hhz
                            Exit For
                           End If
                      Else
                        If cmd.ExecuteScalar > 0 Then
                            hhz = """" & Tables(bm1).Rows(i)(lm) & """已被""" & dt.name & """引用,禁止删除!" & vbcrlf & "如果你确认该条记录已经更改或删除,请先保存以便及时更新后台数据,再进行相应操作!"
                            Return hhz
                            Exit For
                        End If
                            Exit For
                    End If
                Next
            End If
        End If
    End If
If hhz IsNot Nothing Then
Return hhz
Exit For
End If
Next

这个删除判断功能应该是常用的，顶上去，请高手出招。

林总看一下，有没有效率更高的代码？

 呃，代码太多，看不懂。楼主是否是想判断全部表是否存在某一列，而且是否已经有记录？

 说下思路算了，遍历所有表，遍历所有列，判断列是否相同，相同就看表的rows是否大于0，基本是这样了。

判断全部表是否存在某一列，而且是否已经有记录。

 

遍历所有表，遍历所有列（包括后台数据）,判断列是否相同,相同就看列值相同的行的记录数，

指定表记录数>1，其它表记录数 >0，满足条件时返回一条记录位置提示，退出循环。（删除行时一次可能选择多行）

 上面的代码分2段，第1段判断当前操作还未保存的数据，第2段判断后台数据。

 

代码太长，请老大优化。谢谢。

你不如直接设计好两个表，输入几行测试数据，然后说明逻辑和需求。

 估计没有办法优化了的，不过，对于这种，如果操作频繁的话，要从设计里着手。

 比如，用一张表记录各个表的情况，这样直接查询这张表就可以得到你要的信息了。