Create a VB.Net Class to Process a SQL Table using ADO
August 2008, December 2010, March 2011, December 2016

If you download this tip please consider making a modest donation.

I recently developed, under contract, a web application using VS2005, VB.Net, ASP.Net, and SQL Server 2005. I needed a tool to create the class modules used to access the SQL data. I had previously written a tool to create class modules to process an Access table using DAO then ADO. Click here to view the DAO tip. Click here to view the ADO tip. I used the ADO tool as the basis for a tool that creates a VB.Net class to process a SQL Table using ADO. That is the subject of this month's tip.

2008/10/18 - I added readonly property sSQLSelectHandledColumns. This property is primarily used to create a SQL Select statement that retrieves only handled record columns. varBinary columns are not handled by the generated code. Previously all data was returned for each retrieved record. There is no reason to ask for all data when it is not being handled by the generated code. Not doing so has a dramatic effect on processing speed especially when the varBinary column contains large chunks of data. See below for details.

2010/12/19 - I added methods Copy, Count and Compare and property sByOverload. See below for details.

2011/03/11 - I made some significant enhancements to the Creation program. They include:

  • Trusted Connections - Now supports trusted connections. The prior version used SQL Server Authentication only. Windows Authentication is now supported.
  • Keep Recent 'Store In' Folders -  Save and Restore the most recent 'store in' folders. Previously only the last 'store in' folder was retained between session. Now as many as 20 recent 'store in' folders are retained.
  • Open 'Store In' Folder - Added a button to open the 'store in' folder. Click this button to open the 'store in' folder. It can be used to open the folder then copy the created modules to the appropriate application folder.
  • Hide Connection - After connecting to the SQL Server Instance the connection information is no longer displayed.

2016/12/09 - I updated the source code today - see below. There have been minor changes to the application.

The Creation Program - CreateVB8ADOClassesForSQLDB

Program CreateVB8ADOClassesForSQLDB is used to connect to SQL Server, provide a list of SQL server databases and a list of tables in the selected database. The application will create a class module for all tables or for the selected one. Enter authentication information then click the 'Connect' button to open a SQL Server Instance.


Using SQL Server Authentication
enter the SQL Server Instance, uncheck Trusted,
enter a Login ID and Password then click Connect
or

Using Windows Authentication
enter the SQL Server Instance, check Trusted, then click Connect


The recently used Database and associated Table are retained.
Recently used 'store in' folders are also retained.


After clicking 'Do Create...' if the
'store in' folder does not exist then this dialog is displayed.


In this example, class modules were created for all 37 tables found in the database.


In this example, one class module was created for the single, selected table.


Clicking the 'Open the StoreIn Folder' button opens that folder displaying the created class modules.

Recommended Columns

Each SQL table should have a GUID as the record identifier. This generated code can be used with non-GUID identified records but some of the functionally will be compromised. For example: the Update function uses the record's GUID to find the correct record to be updated. You could of course, modify the code to use an incremented number or some other unique ID to uniquely identify each record. I have found the GUID to be a reliable unique record identifier.

If the table contains any of these columns then they are updated by the generated code as follows:

Recommended Action when Inserted or Updated
GUID Set to System.Guid.NewGuid when the class is initialized.
dDateCreated Set to Now when the row is inserted. Not changed otherwise.
dDateLastUpdated Set to Now when the row is inserted. Set to Now when the row is
updated unless property bUpdateDateLastUpdated is set to False.
sBy or sUserName Set to Environment.Username when the row is inserted or updated.

The Created Class Module

Shown below is the code created for the Northwind Products table. Properties are created for each table column. The following methods are also created.

LoadAll
LoadUsingGUID
Insert
Delete
Update
Copy
Count
Compare


The created Products table class module.
[Note: This diagram does not show property sSQLSelectHandledColumns
which came into use after this diagram was created.]

Special Data Columns

I always create the following columns:

Column Description
GUID The unique record identifier.
dDateCreated The date the record was created.
dDateLastUpdated The date the record was last updated. Property bUpdateDateLastUpdated when set to False will prevent the date from being updated. Default is True.
sBy or
sUserName
If either of these columns are present they are set to Environment.UserName.

VarBinary Columns

VarBinary columns are not handled by the code generator. These fields are often used to contain photos and other binary data. A 'blank' property is created if the column datatype is VarBinary. Code similar to this is created:

#Region "Property oPhoto"
    'varbinary not handled.
#End Region '"Property oPhoto"

Special Properties

Beside creating a property for each table column, the following special properties are created.

Special Properties Description
sName_[tablename] The name of the table. It could be used to store the name of the table in a SQL statement, for example:
Dim o As New c_Products_b
Dim sSQL = "Select * From " & o.sName_Products & " Where ..."
sNow_[tablename] The date and time the class was created. It is mostly used to update column dDateLastUpdated.
bUpdateDateLastUpdated If True then the current date/time updates the dDateLastUpdated column in function 'Update'. Default is True.
sSQLSelectHandledColumns This readonly property contains a Select statement that addresses each of the handled record's columns. See below.
sByOverload By default the sBy property is updated with the Environment.UserName. When running on a remote server this variable is always the server's name. You can set sByOverload to store that value in sBy. For example you could use the logged-in user's name to more appropriately indicate the person who last updated the record. Move the name to sByOverload before performing an Insert or Update.

Properties Used in the Code Generator

Properties set in the class module generator cCreateVB8Code.

Property Description Example
oTableSMO The SMO table to be processed. .oTableSMO =
  oDatabaseSMO.Tables("Products")
sStoreIn The folder into which to store the created file. c:\MyProject
sFileName The file name of the class module being created. Products
sTableName The name of the SQL table to process.  
sClassName The class name. Products
sClassPrefix A class name prefix. Identify a prefix for example 'cls'. I use 'c_'. c_
sClassSuffix A class name suffix. Identify a suffix. I use '_b' to indicate that this is the base class. _b

Property sSQLSelectHandledColumns

Readonly property sSQLSelectHandledColumns contains a SQL Select statement that references all the table's columns handled by the created class module. For example:


You could use this property to construction an overriding SQL statement before passing it to the LoadAll method.
This property is used automatically where an unhandled column is part of the generated code.
Always when a varBinary field is encountered.

Using the Generated Code

Private Function LoadDataPersons() As Boolean
    ' Load all person records, sort by last name
    Dim o As New c_tblAdminPerson_b
    Dim oColl As New Collection
    Dim oConn As SqlClient.SqlConnection = OpenSQLConnection()
    Dim sSQL As String = "Select * From " & o.sName_tblAdminPerson
    sSQL &= " Order By sNameLast"
    With o
        If .LoadAll(oConn, oColl, sSQL) Then
            For Each o In oColl
                '... Process Each
            Next
            Return True
        End If
    End With
End Function
Private Function LoadDataOnePerson(ByVal GUIDPerson As Guid) As Boolean
    ' Load one person using a GUID
    Dim o As New c_tblAdminPerson_b
    Dim oColl As New Collection
    Dim oConn As SqlClient.SqlConnection = OpenSQLConnection()
    With o
        If .LoadUsingGUID(oConn, oColl, GUIDPerson) Then
            o = oColl(1)
            With o
                '... Process This One
            End With
            Return True
        End If
    End With
End Function
Private Function DeleteOnePerson(ByVal GUIDPerson As Guid) As Boolean
    ' Delete one person using a GUID
    Dim o As New c_tblAdminPerson_b
    Dim oConn As SqlClient.SqlConnection = OpenSQLConnection()
    With o
        If .Delete(oConn, GUIDPerson) Then
            Return True
        End If
    End With
End Function
Private Function InsertAPerson() As Boolean
    ' Create a new person record
    Dim o As New c_tblAdminPerson_b
    Dim oConn As SqlClient.SqlConnection = OpenSQLConnection()
    With o
        .sNameFirst = "Larry"
        .sNameLast = "Rebich"
        .sByOverload = GetLoggedOnUserName()
        If .Insert(oConn) Then
            Return True
        End If
    End With
End Function
Private Function UpdatePersonFirstName(ByVal GUIDPerson As Guid, _
                                       ByVal sNameFirst As String) As Boolean
    ' Update the person's first name.
    ' The person is identified using a GUID.
    Dim o As New c_tblAdminPerson_b
    Dim oColl As New Collection
    Dim oConn As SqlClient.SqlConnection = OpenSQLConnection()
    With o
        If .LoadUsingGUID(oConn, oColl, GUIDPerson) Then
            o = oColl(1)
            With o
                .sNameFirst = sNameFirst
                .sByOverload = GetLoggedOnUserName()
                If .Update(oConn) Then
                    Return True
                End If
            End With
        End If
    End With
End Function
Protected Sub Page_Load(...) Handles Me.Load
    Response.Write("Larry Count = " & CountPersonsWithFirstName("Larry"))
End Sub

Private Function CountPersonsWithFirstName(ByVal sNameFirst As String) As Integer
    Dim o As New c_tbl0800Person_b
    Dim sWhere As String = "sNameFirst Like '" & sNameFirst & "%'"
    Dim oConn As SqlClient.SqlConnection = OpenSQLConnection()
    With o
        CountPersonsWithFirstName = .Count(oConn, sWhere)
    End With
End Function
Private Function CompareRecords(ByVal o1 As c_tbl0800Person_b, _
                                ByVal o2 As c_tbl0800Person_b) As Boolean
    With o1
        Return .Compare(o1, o2)
    End With
End Function
Private Sub SomeRoutine(ByVal o1 As c_tbl0800Person_b)
    '...
    Dim o2 As c_tbl0800Person_b = o1.Copy(o1)
    '...
End Sub

The Program Uses These Modules

The code generator uses a number of support classes. They are:

Module - Click for More Info Description Used in the Demo To
cCreateVB8Code
  in modDoCreateSQLServerClassModules.vb
The subject of this tip. Create class modules.
BGAbout Create an About dialog. Show an About dialog.
BGFileNameParse Parse a file name. Make sure a folder has an ending backslash.
BGHelpMenu Build a Help menu. Build the Help Menu.
BGInfo Display Help Display this web page.
BGLocLocLoc Form housekeeping. Locate the form.
BGStatusBar Dynamic Status Bar. Issue status messages.
BGWebConnect Connect to the web using a URL and
the System.Diagnostics.Process class.
Display this web page.
cFilenameParse Parse a file name.  
cSortCollection Sort a collection. Sort the recently used StoreIn folders.
modCreateGUID Create or Validate a Guid.  
modIsAuthor Test environment for Author's computer.  
modRegRead Read and write to the registry. Used by BGLocLocLoc and to save
and restore settings in the registry.

Download VB.Net Code

Download the source code by clicking on the zipped file name. The compiled desktop application is included in the second file. Look for the Exe in the ...bin/Release folder.

Download - Source Zipped File Size
VS2008 VB Solution CreateClasses_20161209.zip 91K

with compiled Exe

CreateClasses_20161209_WithExe.zip 3,878K

Previous Tip   Tip Index   Next Tip

Home

Updated 2016/12/09