Using a DataDictionary

[TheWizardofInt]

I have used this forum so heavily and it has made me so much better of a programmer, that I wanted to give back something:

 

'this writes to the format:
       '   TableName char 30
       '   FieldName char 30
       '   FieldType char 20
       '   FieldSize Int 4
       '   FieldNumType Int 4
       Dim i As Integer
       Dim sItem As String
       Dim sSQL As String
       Dim dr As DataRow
       Dim dt As DataTable
       Dim dtR As DataTable
       Dim dRow As DataRow
       Dim oConn As SqlClient.SqlConnection
       Dim cmd As SqlClient.SqlCommand
       Dim da As SqlClient.SqlDataAdapter

      dt = New DataTable
       dt.Columns.Add("Table")
       dt.Columns.Add("Name")
       dt.Columns.Add("Type")
       dt.Columns.Add("Size")
       'go through the tables and get the field data

       sSQL = "SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS"
       dtR = New DataTable
       Try
'cmbSQLServer.SelectedItem  and  cmbDatabase.SelectedItem  come from a 
'drop-down selector for the available Databases and Tables on the SQL server

           oConn = New SqlClient.SqlConnection("Server=" & cmbSQLServer.SelectedItem & ";Database=" & _
           cmbDatabase.SelectedItem & ";uid=" & txtSQL.Text & ";pwd=" & txtSQLPass.Text & ";")
           oConn.Open()
       Catch ex As Exception
           MsgBox(ex.Message, MsgBoxStyle.Critical, "An error has occurred")
           Exit Sub
       End Try
       Try
           cmd = New SqlClient.SqlCommand
           cmd.CommandText = sSQL
           cmd.Connection = oConn
           da = New SqlClient.SqlDataAdapter(cmd)
           da.Fill(dtR)
       Catch ex As Exception
           MsgBox(ex.Message, MsgBoxStyle.Critical, "An error has occurred!")
           oConn.Close()
           Exit Sub
       End Try

       For Each dr In dtR.Rows
           i += 1
           dRow = dt.NewRow
           dRow.Item("Table") = dr.Item(0)
           dRow.Item("Name") = dr.Item(1)
           dRow.Item("Type") = dr.Item(2)

           If Not dr.IsNull(3) Then
               dRow.Item("Size") = dr.Item(3)
           Else
               'these will show up as '-1' if you don't change them
               Select Case dr.Item(2).toupper
                   Case "DATE"
                       dRow.Item("Size") = 8
                   Case "DATETIME"
                       dRow.Item("Size") = 8
                   Case "DECIMAL"
                       dRow.Item("Size") = 8
                   Case "NUMERIC"
                       dRow.Item("Size") = 9
                   Case "INT"
                       dRow.Item("Size") = 4
                   Case "SMALLINT"
                       dRow.Item("Size") = 2
                   Case "TINYINT"
                       dRow.Item("Size") = 1
                   Case Else
                       Beep()
               End Select

           End If
           dt.Rows.Add(dRow)
           Application.DoEvents()
       Next
       'now build a datadictionary
       sSQL = "INSERT INTO DataDictionary (TableName, FieldName, FieldType, FieldSize, FieldNumType)" & _
               " Values(@TableName, @FieldName, @FieldType, @FieldSize, @FieldNumType)"
       pbBuild.Maximum = dt.Rows.Count
       i = 0
       For Each dRow In dt.Rows
           i += 1
           pbBuild.Value = i
           Application.DoEvents()
           Try
               cmd = New SqlClient.SqlCommand
               cmd.Parameters.Add(New SqlClient.SqlParameter("@TableName", System.Data.SqlDbType.Char, 30))
               cmd.Parameters("@TableName").Value = dRow.Item("Table")
               cmd.Parameters.Add(New SqlClient.SqlParameter("@FieldName", System.Data.SqlDbType.Char, 30))
               cmd.Parameters("@FieldName").Value = dRow.Item("Name")
               cmd.Parameters.Add(New SqlClient.SqlParameter("@FieldType", System.Data.SqlDbType.Char, 20))
               cmd.Parameters("@FieldType").Value = dRow.Item("Type")
               cmd.Parameters.Add(New SqlClient.SqlParameter("@FieldSize", System.Data.SqlDbType.Int, 4))
               cmd.Parameters("@FieldSize").Value = dRow.Item("Size")
               'FieldNumType
               cmd.Parameters.Add(New SqlClient.SqlParameter("@FieldNumType", System.Data.SqlDbType.Int, 4))
               cmd.Parameters("@FieldNumType").Value = ConvertToType(dRow.Item("Type"))
               cmd.Connection = oConn
               cmd.CommandText = sSQL
               cmd.ExecuteNonQuery()
           Catch ex As Exception
               MsgBox(ex.Message, MsgBoxStyle.Critical, "An error has occurred!")
               oConn.Close()
               Exit Sub
           End Try
       Next
       oConn.Close()

It uses this sub procedure to convert to number types from named types

Public Function ConvertToType(ByVal sType As String) As Integer
       Select Case UCase(sType)
           Case "BIGINT"
               Return System.Data.SqlDbType.BigInt
           Case "BINARY"
               Return System.Data.SqlDbType.Binary
           Case "BIT"
               Return System.Data.SqlDbType.Bit
           Case "CHAR"
               Return System.Data.SqlDbType.Char
           Case "DATETIME"
               Return System.Data.SqlDbType.DateTime
           Case "DECIMAL"
               Return System.Data.SqlDbType.Decimal
           Case "FLOAT"
               Return System.Data.SqlDbType.Float
           Case "IMAGE"
               Return System.Data.SqlDbType.Image
           Case "INT"
               Return System.Data.SqlDbType.Int
           Case "MONEY"
               Return System.Data.SqlDbType.Money
           Case "NCHAR"
               Return System.Data.SqlDbType.NChar
           Case "NTEXT"
               Return System.Data.SqlDbType.NText
           Case "NUMERIC"
               Return System.Data.SqlDbType.Int
           Case "NVARCHAR"
               Return System.Data.SqlDbType.NVarChar
           Case "REAL"
               Return System.Data.SqlDbType.Real
           Case "SMALLDATETIME"
               Return System.Data.SqlDbType.SmallDateTime
           Case "SMALLINT"
               Return System.Data.SqlDbType.SmallInt
           Case "SMALLMONEY"
               Return System.Data.SqlDbType.SmallMoney
           Case "TEXT"
               Return System.Data.SqlDbType.Text
           Case "TIMESTAMP"
               Return System.Data.SqlDbType.Timestamp
           Case "TINYINT"
               Return System.Data.SqlDbType.TinyInt
           Case "UNIQUEIDENTIFIER"
               Return System.Data.SqlDbType.UniqueIdentifier
           Case "VARBINARY"
               Return System.Data.SqlDbType.VarBinary
           Case "VARCHAR"
               Return System.Data.SqlDbType.VarChar
           Case "VARIANT"
               Return System.Data.SqlDbType.Variant
           Case Else
               Application.DoEvents()
       End Select
   End Function

Now this insert statement will use the datadictionary field when it does an insert

Public Function InsertRecord(ByVal sFields() As NVPair, ByVal sTable As String) As String
           'sFields uses an array with structure of Name and Value, both strings
           'You put all of your fields by name and value into the array, then
           'call the function and name the table you want to insert into
           Dim iNewRec As Integer
           Dim sSQL As String
           Dim sValues As String
           Dim iTot As Integer = sFields.GetUpperBound(0)
           Dim i As Integer
           Dim oConn As SqlClient.SqlConnection
           Dim cmd As New SqlClient.SqlCommand
           Dim dt As New DataTable

           'make a sql statement to add the new record
           sSQL = "Insert Into [" & sTable & "] ("
           sValues = " Values("

           'build the sql statement
           For i = sFields.GetLowerBound(0) To sFields.GetUpperBound(0)
               sSQL &= "[" & sFields(i).sName & "],"
               sValues &= "@" & sFields(i).sName & ","
               dt = New DataTable
               dt = GetData(sTable, sFields(i).sName)
               If Not dt Is Nothing Then
                   If dt.Rows.Count > 0 Then
                       'param = New SqlParameter("@CustomerID", System.Data.SqlDbType.NChar, 5)
                       cmd.Parameters.Add(New SqlClient.SqlParameter("@" & sFields(i).sName, dt.Rows(0).Item(4), dt.Rows(0).Item(3)))
                       cmd.Parameters("@" & sFields(i).sName).Value = sFields(i).sValue
                   Else
                       Return "Err: No such field"
                   End If
               Else
                   Return "Err: Data connectivity!"
               End If
           Next
           sSQL = Left(sSQL, sSQL.Length - 1) & ") "
           sValues = Left(sValues, sValues.Length - 1) & ") "
           sSQL &= sValues
           Try
               oConn = New SqlClient.SqlConnection(sDSN)
               oConn.Open()
               cmd.CommandText = sSQL
               cmd.Connection = oConn
               cmd.CommandTimeout = 0     
               cmd.ExecuteNonQuery()
           Catch ex As Exception
               Try
                   oConn.Close()
                   oConn.Dispose()
               Catch ex1 As Exception

               End Try
               Return ex.Message
               Exit Function
           End Try
           oConn.Close()
           oConn.Dispose()
           Return "1"
       End Function

[TheWizardofInt]

What this is, is a program that makes a Data Dictionary for all of the tables in any MS SQL database, and then shows how to use the data dictionary in an Insert table

 

Is uses both named and numeric types so that your Insert can use parameters and create them dynamically

 

When I use this, I never have to look up the design values for the tables I create (some have over 6000 fields in just under 200 tables), I just name the field and the table and I am set. One Insert statement works for every insert in the whole program and, of course, one Update statement as well.