TheWizardofInt
Well-known member
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:
It uses this sub procedure to convert to number types from named types
Now this insert statement will use the datadictionary field when it does an insert
Code:
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 dont 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()
Code:
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
Code:
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