循环访问表并查询每个有TRelationcode的文件

Question

我在循环遍历包含TRelationCode的表的代码时遇到了问题。当它找到它时，必须从它获取RelationCode，然后将其转换为新的RelationCode并将其更新为新的。

要创建新的RelationCode，我创建了一个叫做MakeRelationCode（OldRelation）的函数。我有这个代码循环表：

但现在我需要更新旧代码到新的。

Answer 1

我更喜欢简单的SQL命令和一点vb逻辑，因此我跳过了SqlDataAdapter部分。这只会降低性能，并且只有在网格中显示某些内容并希望双向绑定时才需要。

以下代码未经测试并输入盲，请检查输入错误等。 我将所有内容都放在一个方法中。

Dim tableNames As New List(Of String) 
'Key: Old code, Value: New code' 
Dim trelationcodes As New Dictionary(Of String, String) 

Using conn As New SqlClient.SqlConnection("YourConnectionString") 'Change connection string to your needs' 
    Dim qTableNames = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.columns WHERE COLUMN_NAME = 'TRelationcode'" 
    conn.Open() 

    'Get table names with TRelationcode column' 
    Using commTableNames As New SqlClient.SqlCommand(qTableNames, conn) 
     Dim dataReader = commTableNames.ExecuteReader() 
     While dataReader.Read() 
      tableNames.Add(dataReader.GetString(0)) 
     End While 
    End Using 

    'Select all distinct old TRelationcode which will be updated' 
    Dim qTrelationcodesOld = "SELECT DISTINCT TRelationcode FROM {0}" 
    For Each tableName In tableNames 
     'Get all old TRelationcodes from table found previuosly' 
     Using commTrelationcodesOld As New SqlClient.SqlCommand() 
      commTrelationcodesOld.Connection = conn 
      commTrelationcodesOld.CommandText = String.Format(qTrelationcodesOld, tableName) 
      Dim dataReader = commTrelationcodesOld.ExecuteReader() 
      While dataReader.Read() 
       Dim code = dataReader.GetString(0) 
       If Not trelationcodes.ContainsKey(code) Then 
        trelationcodes.Add(code, "") 'Value will be set later' 
       End If 
      End While 
     End Using 

     'Get new TRelationcodes' 
     For Each tRelCodeOld In trelationcodes.Keys 
      trelationcodes(tRelCodeOld) = MakeRelationCode(tRelCodeOld) 
     Next 

     'Set new TRelationcodes' 
     Dim uTRelationcode = "UPDATE {0} SET TRelationcode = @newCode WHERE TRelationcode = @oldCode" 
     For Each tRelCodes In trelationcodes 
      Using commTrelationcodesNew As New SqlClient.SqlCommand() 
       commTrelationcodesNew.Connection = conn 
       commTrelationcodesNew.CommandText = String.Format(uTRelationcode, tableName) 
       commTrelationcodesNew.Parameters.Add("@oldCode", SqlDbType.VarChar).Value = tRelCodes.Key 'Varchar correct?' 
       commTrelationcodesNew.Parameters.Add("@newCode", SqlDbType.VarChar).Value = tRelCodes.Value 'Varchar correct?' 
       commTrelationcodesNew.ExecuteNonQuery() 
      End Using 
     Next 
    Next 
End Using 

该代码远离最佳状态，例如，我跳过了异常处理。
最关心的部分是你的MakeRelationCode函数。如果里面的逻辑可以用存储过程中的T-SQL编写，那么整体编码也将被简化。