SQL Database Editor

Charl Pohlmann

Rate me:

3.80/5 (9 votes)

25 Aug 20042 min read

113.8K

2.1K

An article on a SQL database editor.

Introduction

I wrote this little application as a favor to a friend, mostly to start the migration process from VB6 to .NET. It turned out to be quite a learning experience.

Background

What do you do when you need to edit the data in a SQL Server database, and you don't have Enterprise Manager handy? That's the problem I had with the last application I wrote. And then one day, a friend asked me if I'd be able to write something like that. So I did, and I thought other programmers could use it too, so I'm posting the binaries as well as the source.

I found using ADO.NET, as opposed to ADO, a breeze. It's so easy to do things in .NET that couldn't be done in VB6. That's until I tried to use the CommandBuilder class on tables with no Primary keys. It turns out that the CommandBuilder class works only on tables with Primary keys defined. So, I wrote three functions to build the Insert, Update and Delete commands for tables with no Primary keys defined. Now, I'm no SQL guru, but up until now, I've had no problems with it. I must point out though that these functions work only on tables. You have to pass the name of a legitimate SQL table to the function, which will then return the SQL command string to insert, update or delete items from/to the table.

Using the code

As a courtesy to the folks who don't want to read through all the code in the app, here are the three functions, with their helper functions:

Private Sub BuildInsertCommand(ByRef dataAdapter As _
System.Data.SqlClient.SqlDataAdapter, _
ByVal tableName As String)
   Dim t_dt As DataTable = _
     GetTableColumnInfo(dataAdapter.SelectCommand.Connection, _
     tableName)
   Dim dv As New DataView(t_dt)
   Dim cmd As SqlClient.SqlCommand
   Dim dt As New DataTable
   Dim dca() As DataColumn
   Dim dc As DataColumn

   Dim sFilterFieldnames() As String
   Dim sParamNames() As String
   Dim sSQL As String
   Dim sTmp As String

   Dim sSQLDataType As String
   Dim lPrecision As Byte
   Dim lSize As Integer
   Dim lScale As Byte

   'Get the schema for the table to be read.
   dataAdapter.FillSchema(dt, SchemaType.Source)
   ReDim sFilterFieldnames(dt.Columns.Count - 1)
   ReDim sParamNames(dt.Columns.Count - 1)

   For Each dc In dt.Columns
      'Read & save the column names
      sFilterFieldnames(dc.Ordinal) = dc.ColumnName
      'Read the column names and generate parameter names.
      sParamNames(dc.Ordinal) = "@" + _
        RemoveIllegalChars(dc.ColumnName, m_RemoveChars)
   Next
   'Clean up
   dc.Dispose()
   dca = dt.PrimaryKey()

   'Insert Command ***********************************
   sSQL = "INSERT INTO [" + tableName + "] ("
   '   Fields
   For i As Integer = sFilterFieldnames.GetLowerBound(0) _
                    To sFilterFieldnames.GetUpperBound(0)
      sSQL = sSQL & "[" & sFilterFieldnames(i) & "]"
      If i <> sFilterFieldnames.GetUpperBound(0) Then
         sSQL = sSQL + ", "
      Else
         sSQL = sSQL + ")"
      End If
   Next
   '   Parameters
   sSQL = sSQL + " VALUES ("
   For i As Integer = sParamNames.GetLowerBound(0) _
                         To sParamNames.GetUpperBound(0)
      sSQL = sSQL + sParamNames(i)
      If i <> sParamNames.GetUpperBound(0) Then
         sSQL = sSQL + ", "
      Else
         sSQL = sSQL + "); "
      End If
   Next
   '   Do a SELECT again
   sSQL = sSQL + "SELECT "
   For i As Integer = sFilterFieldnames.GetLowerBound(0) To _
                               sFilterFieldnames.GetUpperBound(0)
      sSQL = sSQL + "[" + sFilterFieldnames(i) + "]"
      If i <> sFilterFieldnames.GetUpperBound(0) Then
         sSQL = sSQL + ", "
      End If
   Next
   sSQL = sSQL + " FROM [" + tableName + "] WHERE ("
   '   WHERE Clause
   If dca.GetLength(0) > 0 Then
      'If the table contains a primary key, use that to find the record.
      For i As Integer = dca.GetLowerBound(0) To dca.GetUpperBound(0)
         dv.RowFilter = "COLUMN_NAME = '" & dca(i).ColumnName & "'"
         sSQLDataType = dv(0)("TYPE_NAME").ToString()

         If sSQLDataType.Equals("text") Or _
            sSQLDataType.Equals("ntext") Or _
            sSQLDataType.Equals("image") Then

            sTmp = "[" + dca(i).ColumnName + "] LIKE @" + _
                   RemoveIllegalChars(dca(i).ColumnName, _
                   m_RemoveChars)
         Else
            sTmp = "[" + dca(i).ColumnName + "] = @" + _
                   RemoveIllegalChars(dca(i).ColumnName, _
                   m_RemoveChars)
         End If

         If dca.GetLength(0) > 1 Then
            sSQL = sSQL + "(" + sTmp + ")"
         Else
            sSQL = sSQL + sTmp
         End If
         If i <> dca.GetUpperBound(0) Then sSQL = sSQL + " AND "
      Next
   Else
      'otherwise use a combination of the fields.
      For i As Integer = sFilterFieldnames.GetLowerBound(0) _
                         To sFilterFieldnames.GetUpperBound(0)
         dv.RowFilter = "COLUMN_NAME = '" & sFilterFieldnames(i) & "'"
         sSQLDataType = dv(0)("TYPE_NAME").ToString()

         If sSQLDataType.Equals("text") Or _
            sSQLDataType.Equals("ntext") Or _
            sSQLDataType.Equals("image") Then

            sTmp = "[" + sFilterFieldnames(i) + "] LIKE " + sParamNames(i)
         Else
            sTmp = "[" + sFilterFieldnames(i) + "] = " + sParamNames(i)
         End If

         If sFilterFieldnames.GetLength(0) > 1 Then
            sSQL = sSQL + "(" + sTmp + ")"
         Else
            sSQL = sSQL + sTmp
         End If
         If i <> sFilterFieldnames.GetUpperBound(0) Then
            sSQL = sSQL + " AND "
      Next
   End If
   sSQL = sSQL + ")"

   'Create a new command object
   cmd = New SqlClient.SqlCommand(sSQL)
   'Set it's connection from the Select cammand in the DataAdapter
   cmd.Connection = dataAdapter.SelectCommand.Connection()

   '   Add Parameters
   For i As Integer = sFilterFieldnames.GetLowerBound(0) _
                      To sFilterFieldnames.GetUpperBound(0)
      dv.RowFilter = "COLUMN_NAME = '" & sFilterFieldnames(i) & "'"
      sSQLDataType = dv(0)("TYPE_NAME").ToString()
      Try
         lPrecision = CType(dv(0)("PRECISION").ToString, Byte)
      Catch ex As Exception
         lPrecision = 38
      End Try
      If lPrecision > 38 Then lPrecision = 38
      lSize = CType(dv(0)("LENGTH").ToString, Integer)
      lScale = CType(IIf(dv(0)("SCALE").ToString.Equals(""), 0, _
                                   dv(0)("SCALE").ToString), Byte)

      cmd.Parameters.Add(New SqlClient.SqlParameter(sParamNames(i), _
                             GetSQLType(sSQLDataType), _
                             lSize, _
                             ParameterDirection.Input, _
                             dt.Columns(sFilterFieldnames(i)).AllowDBNull, _
                             lPrecision, _
                             lScale, _
                             sFilterFieldnames(i), _
                             DataRowVersion.Current, _
                             Nothing))
   Next
   'Clean up
   dv.Dispose()
   t_dt.Dispose()
   dt.Dispose()

   'Set the command object
   dataAdapter.InsertCommand = cmd
End Sub

Private Sub BuildUpdateCommand(ByRef dataAdapter As _
       System.Data.SqlClient.SqlDataAdapter, _
       ByVal tableName As String)
   Dim t_dt As DataTable = _
     GetTableColumnInfo(dataAdapter.SelectCommand.Connection, tableName)
   Dim dv As New DataView(t_dt)
   Dim cmd As SqlClient.SqlCommand
   Dim dt As New DataTable
   Dim dca() As DataColumn
   Dim dc As DataColumn

   Dim sFilterFieldnames() As String
   Dim sParamNames() As String
   Dim sOrigParamNames() As String
   Dim sSQL As String
   Dim sTmp As String

   Dim sSQLDataType As String
   Dim lPrecision As Byte
   Dim lSize As Integer
   Dim lScale As Byte

   'Get the schema for the table to be read.
   dataAdapter.FillSchema(dt, SchemaType.Source)
   ReDim sFilterFieldnames(dt.Columns.Count - 1)
   ReDim sParamNames(dt.Columns.Count - 1)
   ReDim sOrigParamNames(dt.Columns.Count - 1)

   For Each dc In dt.Columns
      'Read & save the column names
      sFilterFieldnames(dc.Ordinal) = dc.ColumnName
      'Read the column names and generate parameter names.
      sParamNames(dc.Ordinal) = "@" + _
          RemoveIllegalChars(dc.ColumnName, m_RemoveChars)
      'Read the column names and generate Original parameter names.
      sOrigParamNames(dc.Ordinal) = "@Original_" + _
          RemoveIllegalChars(dc.ColumnName, m_RemoveChars)
   Next
   'Clean up
   dc.Dispose()
   dca = dt.PrimaryKey()

   'Update Command ***********************************
   sSQL = "UPDATE [" & tableName & "] SET "
   '   Fields
   For i As Integer = sFilterFieldnames.GetLowerBound(0) _
                      To sFilterFieldnames.GetUpperBound(0)
      sSQL = sSQL + "[" + sFilterFieldnames(i) + "] = " + sParamNames(i)
      If i <> sFilterFieldnames.GetUpperBound(0) Then
         sSQL = sSQL + ", "
      Else
         sSQL = sSQL + " "
      End If
   Next
   '   Parameters
   sSQL = sSQL + " WHERE "
   If dca.GetLength(0) > 0 Then
      'If the table contains a primary key, use that to find the record.
      For i As Integer = dca.GetLowerBound(0) To dca.GetUpperBound(0)
         sTmp = "[" + dca(i).ColumnName + "]"

         dv.RowFilter = "COLUMN_NAME = '" & dca(i).ColumnName & "'"
         sSQLDataType = dv(0)("TYPE_NAME").ToString()

         If sSQLDataType.Equals("text") Or _
            sSQLDataType.Equals("ntext") Or _
            sSQLDataType.Equals("image") Then

            sSQL = sSQL + "(" + sTmp + " LIKE @Original_" + _
                   RemoveIllegalChars(dca(i).ColumnName, _
                   m_RemoveChars)
         Else
            sSQL = sSQL + "(" + sTmp + " = @Original_" + _
                   RemoveIllegalChars(dca(i).ColumnName, m_RemoveChars)
         End If

         If i <> dca.GetUpperBound(0) Then
            sSQL = sSQL + " AND "
         Else
            sSQL = sSQL + "; "
         End If
      Next
   Else
      'otherwise use a combination of the fields.
      For i As Integer = sFilterFieldnames.GetLowerBound(0) To _
                         sFilterFieldnames.GetUpperBound(0)
         sTmp = "[" + sFilterFieldnames(i) + "]"

         dv.RowFilter = "COLUMN_NAME = '" & sFilterFieldnames(i) & "'"
         sSQLDataType = dv(0)("TYPE_NAME").ToString()

         If sSQLDataType.Equals("text") Or _
            sSQLDataType.Equals("ntext") Or _
            sSQLDataType.Equals("image") Then

            If dt.Columns(sFilterFieldnames(i)).AllowDBNull Then
               sSQL = sSQL + "((" + sTmp + " LIKE " + _
                      sOrigParamNames(i) + ") OR _
                      (" + sTmp + " IS NULL AND " + _
                      sOrigParamNames(i) + " IS NULL))"
            Else
               sSQL = sSQL + "(" + sTmp + " LIKE " + _
                              sOrigParamNames(i) + ")"
            End If
         Else
            If dt.Columns(sFilterFieldnames(i)).AllowDBNull Then
               sSQL = sSQL + "((" + sTmp + " = " + _
                      sOrigParamNames(i) + ") OR _
                      (" + sTmp + " IS NULL AND " + _
                      sOrigParamNames(i) + " IS NULL))"
            Else
               sSQL = sSQL + "(" + sTmp + " = " + sOrigParamNames(i) + ")"
            End If
         End If

         If i <> sFilterFieldnames.GetUpperBound(0) Then
            sSQL = sSQL + " AND "
         Else
            sSQL = sSQL + "; "
         End If
      Next
   End If
   '   Do a SELECT again
   sSQL = sSQL + "SELECT "
   For i As Integer = sFilterFieldnames.GetLowerBound(0) To _
                           sFilterFieldnames.GetUpperBound(0)
      sSQL = sSQL + "[" + sFilterFieldnames(i) + "]"
      If i <> sFilterFieldnames.GetUpperBound(0) Then
         sSQL = sSQL + ", "
      End If
   Next
   sSQL = sSQL + " FROM [" + tableName + "] WHERE ("
   '   WHERE Clause
   If dca.GetLength(0) > 0 Then
      'If the table contains a primary key, use that to find the record.
      For i As Integer = dca.GetLowerBound(0) To dca.GetUpperBound(0)
         dv.RowFilter = "COLUMN_NAME = '" & dca(i).ColumnName & "'"
         sSQLDataType = dv(0)("TYPE_NAME").ToString()

         If sSQLDataType.Equals("text") Or _
            sSQLDataType.Equals("ntext") Or _
            sSQLDataType.Equals("image") Then

            sTmp = "[" + dca(i).ColumnName + "] LIKE @" + _
                   RemoveIllegalChars(dca(i).ColumnName, _
                   m_RemoveChars)
         Else
            sTmp = "[" + dca(i).ColumnName + "] = @" + _
                   RemoveIllegalChars(dca(i).ColumnName, m_RemoveChars)
         End If

         If dca.GetLength(0) > 1 Then
            sSQL = sSQL + "(" + sTmp + ")"
         Else
            sSQL = sSQL + sTmp
         End If
         If i <> dca.GetUpperBound(0) Then sSQL = sSQL + " AND "
      Next
   Else
      'otherwise use a combination of the fields.
      For i As Integer = sFilterFieldnames.GetLowerBound(0) To _
                         sFilterFieldnames.GetUpperBound(0)
         dv.RowFilter = "COLUMN_NAME = '" & sFilterFieldnames(i) & "'"
         sSQLDataType = dv(0)("TYPE_NAME").ToString()

         If sSQLDataType.Equals("text") Or _
            sSQLDataType.Equals("ntext") Or _
            sSQLDataType.Equals("image") Then

            sTmp = "[" + sFilterFieldnames(i) + "] LIKE " + sParamNames(i)
         Else
            sTmp = "[" + sFilterFieldnames(i) + "] = " + sParamNames(i)
         End If

         If sFilterFieldnames.GetLength(0) > 1 Then
            sSQL = sSQL + "(" + sTmp + ")"
         Else
            sSQL = sSQL + sTmp
         End If
         If i <> sFilterFieldnames.GetUpperBound(0) Then
            sSQL = sSQL + " AND "
      Next
   End If
   sSQL = sSQL + ")"

   'Create a new command object
   cmd = New SqlClient.SqlCommand(sSQL)
   'Set it's connection from the Select cammand in the DataAdapter
   cmd.Connection = dataAdapter.SelectCommand.Connection()

   '   Add Parameters
   For i As Integer = sFilterFieldnames.GetLowerBound(0) To _
                            sFilterFieldnames.GetUpperBound(0)
      dv.RowFilter = "COLUMN_NAME = '" & sFilterFieldnames(i) & "'"
      sSQLDataType = dv(0)("TYPE_NAME").ToString()
      Try
         lPrecision = CType(dv(0)("PRECISION").ToString, Byte)
      Catch ex As Exception
         lPrecision = 38
      End Try
      If lPrecision > 38 Then lPrecision = 38
      lSize = CType(dv(0)("LENGTH").ToString, Integer)
      lScale = CType(IIf(dv(0)("SCALE").ToString.Equals(""), _
                             0, dv(0)("SCALE").ToString), Byte)

      cmd.Parameters.Add(New SqlClient.SqlParameter(sParamNames(i), _
                             GetSQLType(sSQLDataType), _
                             lSize, _
                             ParameterDirection.Input, _
                             dt.Columns(sFilterFieldnames(i)).AllowDBNull, _
                             lPrecision, _
                             lScale, _
                             sFilterFieldnames(i), _
                             DataRowVersion.Current, _
                             Nothing))
      cmd.Parameters.Add(New SqlClient.SqlParameter(sOrigParamNames(i), _
                             GetSQLType(sSQLDataType), _
                             lSize, _
                             ParameterDirection.Input, _
                             dt.Columns(sFilterFieldnames(i)).AllowDBNull, _
                             lPrecision, _
                             lScale, _
                             sFilterFieldnames(i), _
                             DataRowVersion.Original, _
                             Nothing))
   Next
   'Clean up
   dv.Dispose()
   t_dt.Dispose()
   dt.Dispose()

   'Set the command object
   dataAdapter.UpdateCommand = cmd
End Sub

Private Sub BuildDeleteCommand(ByRef dataAdapter _
        As System.Data.SqlClient.SqlDataAdapter, _
        ByVal tableName As String)
   Dim t_dt As DataTable = _
     GetTableColumnInfo(dataAdapter.SelectCommand.Connection, tableName)
   Dim dv As New DataView(t_dt)
   Dim cmd As SqlClient.SqlCommand
   Dim dt As New DataTable
   Dim dca() As DataColumn
   Dim dc As DataColumn

   Dim sFilterFieldnames() As String
   Dim sOrigParamNames() As String
   Dim sSQL As String
   Dim sTmp As String

   Dim sSQLDataType As String
   Dim lPrecision As Byte
   Dim lSize As Integer
   Dim lScale As Byte

   'Get the schema for the table to be read.
   dataAdapter.FillSchema(dt, SchemaType.Source)
   ReDim sFilterFieldnames(dt.Columns.Count - 1)
   ReDim sOrigParamNames(dt.Columns.Count - 1)

   For Each dc In dt.Columns
      'Read & save the column names
      sFilterFieldnames(dc.Ordinal) = dc.ColumnName
      'Read the column names and generate Original parameter names.
      sOrigParamNames(dc.Ordinal) = "@Original_" + _
               RemoveIllegalChars(dc.ColumnName, m_RemoveChars)
   Next
   'Clean up
   dc.Dispose()
   dca = dt.PrimaryKey()

   'Delete Command ***********************************
   sSQL = "DELETE FROM [" & tableName & "] WHERE "
   '   Parameters
   If dca.GetLength(0) > 0 Then
      'If the table contains a primary key, use that to find the record.
      For i As Integer = dca.GetLowerBound(0) To dca.GetUpperBound(0)
         sTmp = "[" + dca(i).ColumnName + "]"

         dv.RowFilter = "COLUMN_NAME = '" & dca(i).ColumnName & "'"
         sSQLDataType = dv(0)("TYPE_NAME").ToString()

         If sSQLDataType.Equals("text") Or _
            sSQLDataType.Equals("ntext") Or _
            sSQLDataType.Equals("image") Then

            sSQL = sSQL + "(" + sTmp + " LIKE @Original_" + _
                   RemoveIllegalChars(dca(i).ColumnName, _
                   m_RemoveChars) + ")"
         Else
            sSQL = sSQL + "(" + sTmp + " = @Original_" + _
                   RemoveIllegalChars(dca(i).ColumnName, _
                   m_RemoveChars) + ")"
         End If

         If i <> dca.GetUpperBound(0) Then
            sSQL = sSQL + " AND "
         End If
      Next
   Else
      'otherwise use a combination of the fields.
      For i As Integer = sFilterFieldnames.GetLowerBound(0) _
                         To sFilterFieldnames.GetUpperBound(0)
         sTmp = "[" + sFilterFieldnames(i) + "]"

         dv.RowFilter = "COLUMN_NAME = '" & sFilterFieldnames(i) & "'"
         sSQLDataType = dv(0)("TYPE_NAME").ToString()

         If sSQLDataType.Equals("text") Or _
            sSQLDataType.Equals("ntext") Or _
            sSQLDataType.Equals("image") Then

            If dt.Columns(sFilterFieldnames(i)).AllowDBNull Then
               sSQL = sSQL + "((" + sTmp + " LIKE " + _
                      sOrigParamNames(i) + ") OR _
                      (" + sTmp + " IS NULL AND " + _
                      sOrigParamNames(i) + " IS NULL))"
            Else
               sSQL = sSQL + "(" + sTmp + " LIKE " + sOrigParamNames(i) + ")"
            End If
         Else
            If dt.Columns(sFilterFieldnames(i)).AllowDBNull Then
               sSQL = sSQL + "((" + sTmp + " = " + _
                      sOrigParamNames(i) + ") OR _
                      (" + sTmp + " IS NULL AND " + _
                      sOrigParamNames(i) + " IS NULL))"
            Else
               sSQL = sSQL + "(" + sTmp + " = " + sOrigParamNames(i) + ")"
            End If
         End If

         If i <> sFilterFieldnames.GetUpperBound(0) Then
            sSQL = sSQL + " AND "
         End If
      Next
   End If

   'Create a new command object
   cmd = New SqlClient.SqlCommand(sSQL)
   'Set it's connection from the Select cammand in the DataAdapter
   cmd.Connection = dataAdapter.SelectCommand.Connection()

   '   Add Parameters
   For i As Integer = sFilterFieldnames.GetLowerBound(0) _
                      To sFilterFieldnames.GetUpperBound(0)
      dv.RowFilter = "COLUMN_NAME = '" & sFilterFieldnames(i) & "'"
      sSQLDataType = dv(0)("TYPE_NAME").ToString()
      Try
         lPrecision = CType(dv(0)("PRECISION").ToString, Byte)
      Catch ex As Exception
         lPrecision = 38
      End Try
      If lPrecision > 38 Then lPrecision = 38
      lSize = CType(dv(0)("LENGTH").ToString, Integer)
      lScale = CType(IIf(dv(0)("SCALE").ToString.Equals(""), _
               0, dv(0)("SCALE").ToString), Byte)

      cmd.Parameters.Add(New SqlClient.SqlParameter(sOrigParamNames(i), _
                             GetSQLType(sSQLDataType), _
                             lSize, _
                             ParameterDirection.Input, _
                             dt.Columns(sFilterFieldnames(i)).AllowDBNull, _
                             lPrecision, _
                             lScale, _
                             sFilterFieldnames(i), _
                             DataRowVersion.Original, _
                             Nothing))
   Next
   'Clean up
   dv.Dispose()
   t_dt.Dispose()
   dt.Dispose()

   'Set the command object
   dataAdapter.DeleteCommand = cmd
End Sub

Private Function GetSQLType(ByVal sSQLTypeName _
           As String) As System.Data.SqlDbType
   Select Case sSQLTypeName.ToLower()
      Case "bigint"
         GetSQLType = SqlDbType.BigInt
      Case "binary"
         GetSQLType = SqlDbType.Binary
      Case "bit"
         GetSQLType = SqlDbType.Bit
      Case "char"
         GetSQLType = SqlDbType.Char
      Case "datetime"
         GetSQLType = SqlDbType.DateTime
      Case "decimal"
         GetSQLType = SqlDbType.Decimal
      Case "float"
         GetSQLType = SqlDbType.Float
      Case "image"
         GetSQLType = SqlDbType.Image
      Case "int"
         GetSQLType = SqlDbType.Int
      Case "money"
         GetSQLType = SqlDbType.Money
      Case "nchar"
         GetSQLType = SqlDbType.NChar
      Case "ntext"
         GetSQLType = SqlDbType.NText
      Case "nvarchar"
         GetSQLType = SqlDbType.NVarChar
      Case "real"
         GetSQLType = SqlDbType.Real
      Case "smalldatetime"
         GetSQLType = SqlDbType.SmallDateTime
      Case "smallint"
         GetSQLType = SqlDbType.SmallInt
      Case "smallmoney"
         GetSQLType = SqlDbType.SmallMoney
      Case "sql_variant"
         GetSQLType = SqlDbType.Variant
      Case "text"
         GetSQLType = SqlDbType.Text
      Case "timestamp"
         GetSQLType = SqlDbType.Timestamp
      Case "tinyint"
         GetSQLType = SqlDbType.TinyInt
      Case "uniqueidentifier"
         GetSQLType = SqlDbType.UniqueIdentifier
      Case "varbinary"
         GetSQLType = SqlDbType.VarBinary
      Case "varchar"
         GetSQLType = SqlDbType.VarChar
      Case Else
         GetSQLType = SqlDbType.Variant
   End Select
End Function

Private Function GetTableColumnInfo(ByRef connection _
        As System.Data.SqlClient.SqlConnection, _
        ByVal tableName As String) _
        As System.Data.DataTable

   Dim t_cmd As New SqlClient.SqlCommand("sp_columns")
   t_cmd.CommandType = CommandType.StoredProcedure
   t_cmd.Connection = connection
   t_cmd.Parameters.Add("@table_name", _
         SqlDbType.NVarChar, 384).Value = tableName

   Dim t_da As New SqlClient.SqlDataAdapter(t_cmd)
   Dim t_dt As New DataTable

   Try
      t_da.Fill(t_dt)
      GetTableColumnInfo = t_dt
   Catch ex As Exception
      GetTableColumnInfo = Nothing
   End Try

   t_cmd.Dispose()
   t_da.Dispose()
End Function

Private Function RemoveIllegalChars(ByVal _
       value As String, ByVal chars As String) As String
   Dim i As Integer

   For i = 0 To chars.Length - 1
      value = value.Replace(chars.Substring(i, 1), "")
   Next
   Return value
End Function

Points of Interest

First and foremost, I would like to offer my thanks to guys like Tim Dawson, Jeff Atwood, and Rockford Lhotka for the free use of their code and controls. Special mention is made in the About box of the application.

Please note: I have not included Tim Dawson's SandBar or SandDock controls with the zips, although the application needs these to function. Please download the controls from his site.

Nough said! I hope somebody finds this application useful. Even better would be if somebody could extend it, and repost it.

History

Version 1.3.1687.36192: released.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Written By

Charl Pohlmann

Engineer

South Africa

I'm a mechanical engineer working in the pipe manufacturing business, although programming has been my first love for the past twelve years.

My languages of choice are VB.NET, VB6 and C#, in that order. I took last year off from programming, as me and my family moved into a new house, and there were things to be done. When I started up again, I discovered that not only had my .NET abilities gone rusty, but also my VB6 abilities.

Anyway, I love programming. I think .NET is the best thing since sliced bread (coming from VB6).

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.