(untagged)

SQL Database Editor

Charl Pohlmann

0.00/5 (No votes)

25 Aug 2004

An article on a SQL database editor.

Sample Image - SQLEditor.gif

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