Looping through tables and querying each one which has TRelationcode in it

Question

I'm having trouble with code that loops through the tables that contain TRelationCode. When it finds one it has to get the RelationCode from it and then convert it into the new RelationCode and update it to the new one.

To create the new RelationCode I've made a function Called MakeRelationCode(OldRelation). I have this code to loop through the tables:

Dim query As String = "use fmsStage; SELECT * FROM INFORMATION_SCHEMA.columns WHERE COLUMN_NAME = 'TRelationcode'"
Dim myCmd As SqlDataAdapter = New SqlDataAdapter(query, con)
Dim myData As New DataSet()
myCmd.Fill(myData)

For Each table As DataTable In myData.Tables
    For Each row As DataRow In table.Rows
        For Each col As DataColumn In table.Columns

        Next
    Next
Next

But now I need to update the old codes to the new ones.

Answer 1

I prefer simple SQL commands and a little vb logic thus I skipped the SqlDataAdapter part. This will only cost performance and is only necessary if you display something in a grid and want two-way-binding.

The following code is untested and typed blind so please check for typos etc. I put everything in one method.

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

The code is far away from being optimal, e.g. I skipped exception handling.
The most concerning part is your MakeRelationCode function. If the logic inside could be written in T-SQL in a Stored Procedure, the overall coding would also be simplified.