Click here to Skip to main content
Click here to Skip to main content

SQL Database Editor

By , 25 Aug 2004
Rate this:
Please Sign up or sign in to vote.

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

About the Author

Charl Pohlmann
Engineer
South Africa 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).

Comments and Discussions

 
GeneralGetSQLType PinmemberTicTac2Kcal13-Aug-05 23:39 
GeneralSignIn Locks up Application PinmemberDon Baechtel15-Jan-05 4:50 
GeneralRe: SignIn Locks up Application PinmemberCharl Pohlmann19-Jan-05 0:14 
GeneralRe: Ported for ORACLE? PinmemberCharl Pohlmann18-Oct-04 3:51 
GeneralNice try, with interesting snippets PinmemberAllann31-Aug-04 12:45 
GeneralRe: Nice try, with interesting snippets PinmemberCharl Pohlmann8-Sep-04 19:47 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

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

| Advertise | Privacy | Mobile
Web03 | 2.8.140415.2 | Last Updated 26 Aug 2004
Article Copyright 2004 by Charl Pohlmann
Everything else Copyright © CodeProject, 1999-2014
Terms of Use
Layout: fixed | fluid