A Visually Inheritable Unbound Dataform

• Download source - 23 Kb

Introduction

Have you ever had times in Visual Basic .NET when you'd like to show a simple data form to allow your user to Add/Update/Delete some information, but found that the data form produced by the data form wizard was lacking?

Wouldn't you like your data form to come standard, looking nice and have a simple listbox from which to choose the current record? Of course, this may not be the best solution for a table that has a large number of records, but at least it has plenty of uses for those simple tables for which you need to provide a way to change but really don't want to spend a lot of time on. A good example would be that of simple lookup tables. Simple lookup tables, for this article, are tables that hold all of the possible values that a certain field in another table can contain. But this form also has applications for allowing the user to manipulate the data of simple tables with a minimal number of records--say less than 1000. And you may choose to take the idea and make it suitable for a large number of records.

Say you have a table that tracks stolen property for a law enforcement agency and one field in that table is named category. You make a table called tblCategory to hold all of the valid property categories. When the user is entering in stolen property, the category combobox (cboPropCategory) would be filled with all of the values in tblCategory (the lookup table). Although you would probably spend some time making the data entry form for entering stolen property, you probably wouldn't want to spend a lot of time making a simple data form to manage the available categories, but your users will want the power to add to them or maybe even change them from time to time.

In this tutorial we'll explore how to make an unbound base data form that uses a datareader instead of a dataset. While the IDE provides many tools for using the dataset, the datareader has very little support. However, there are times, such as this, when you might want to change some values used by the rest of the program and have the database changes made instantly, thus making a datareader, and some command objects the best choice. We'll start by discussing form inheritance in Visual Basic .NET.

Background

I wrote this code to solve a real problem that I encountered and am presently using it in some applications. I was so frustrated at the amount of work involved just to have a nice data entry form, that was simple to use, looked nice and that used a datareader instead of the dataset. Since I have written this base form, I have used it again and again and have been very pleased at its flexibility and ease of use. I hope that you will enjoy it and that it helps you in at least some of your needs.

Even if it may not be something that you want to use, I hope that you will gain something in terms of maybe learning how to use Visual Inheritance, or how to use some OO features provided in VB.NET, such as methods that can and must be overriden, constructors and the like. You will also find, included in the source code, a nice class called clsParamBuilder that makes building a command object and adding parameters take about a third as much code as it normally would. So hopefully there is something for everyone in this article.

Please feel free to post any comments, bugs or suggestions for improvement.

Create the Base Form

A base form is actually just a form like any other form, but one from which another form's appearance and code are inherited. So, from the IDE, create your VB.NET project, then in Solution Explorer, right-click the project, choose "Add", and then "Add Windows Form". In the dialog that appears, name your base form. I chose to distinguish my base form from any other forms by naming it BASEfrmUnboundDataForm. The "frm" prefix is usually used in form names in Visual Basic. I chose to prefix that with the word "BASE" to help me remember that this is a base form to be inherited from and not to be used directly.

The idea is to create this base form, layout the controls on it as you want all data forms to appear, and then also include the basic code needed to do most operations. We should allow many methods to be overridden, should the programmer inheriting the form desire to deviate from the basic operations. Remember other forms will inherit this one, and yes some programming and adding of controls will still be required, but we will greatly reduce the amount of code and thinking required to make one or many working data forms. For this reason, we should keep our base form's layout simple and generic.

Controls to Add to the Base Form

• Add button (btnAdd)
• Update button (btnUpdate)
• Delete Button (btnDelete)
• Refresh Button (btnRefresh)
• Lookup Listbox (lstLookup)
• A label above the Lookup Listbox for a Caption (lblLookupList)
• Some panel controls to handle resizing and spacing issues (pnlTop, pnlRight, and pnlFields)
• A GroupBox control used to make a simple rectangle for looks (gbHorvRule)
• An ErrorProvider to allow for data entry error notification (errProvider)

When you get done your form should look like that of Figure 1 below.

Figure 1

Looking at the form above (figure 1), notice that there are 3 panel controls and that all of my controls are placed in those. pnlTop is the top left panel and includes the lookup listbox, lstLookup. Its dock property is set to "Top."

The panel to the right, having the Add(btnAdd), Update(btnUpdate), Delete(btnDelete) and Refresh(btnRefresh) buttons, is named pnlRight and has its dock property set to "Right." The third panel control, named pnlFields is to the bottom left. This panel has its dock property set to "Fill." This is where the form inheriting this form will place its data entry controls i.e. textboxes, checkboxes, etc. Its dock property is set to "Fill" in order to take up whatever space is left over from the other two panel controls.

(Note: My base data form had a bigger height and width initially but I shrank them in order to keep the image dimensions down for this site. Make yours about the height and width you'd like your inheriting form to have initially.)

One thing to keep in mind is that these controls will not be able to be changed at all unless you change the modifiers property of each of the controls appropriately. For example setting btnAdd's modifiers property to "private" means the inheriting form can't change it at all in size, text or any other way. For this reason I set all of the control's modifiers properties to "protected friend" so that any inheriting form can change them as desired. This provides maximum flexibility.

I added a GroupBox control named gbHorvRule just to visually separate the control buttons from the left side of the form.

Code to Add to the Base Form

A lot of thought had to go into this part in order to provide the desired default functionality, but allow the inheriting form to deviate from it.

First we need an enum to keep track of whether the form is in Add or Update mode. This is necessary because a person could click btnAdd in order to add a new record thus resetting the form to accept new data, and after entering the data the user must click btnUpdate in order to accept the changes. However, the person could have been editing an existing record, just made changes and clicked btnUpdate to accept them. btnUpdate will call a method will decide whether to call InsertRecord or UpdateRecord based on the current operation.

VB

Protected Enum FormDataMode
   Add
   Update
End Enum

We also need to add some member variables in our base form.

VB

'internal to base form cannot be affected by outside means
Private _DataMode As FormDataMode = FormDataMode.Update

Below are member variables that must get set or things won't work.

VB

'must be set
Private _ConnectionString As String = String.Empty
Private _TableName As String
Private _PrimaryKeyControl As Control
 'the form control that has the primary key--can be
 'hidden but must exist
Private _PrimaryKeyField As String

I chose to name member variables starting with "_". _ConnectionString will obviously contain the string needed to connect to the database. _TableName is the name of the table that the form will represent. _PrimaryKeyControl is actually a reference to the control on the form that contains the primary key. _PrimarykeyField is the name of the field in this table that is the primary key. Having all of this information allows the base form to perform some actions correctly without much or any additional coding. Of course all of the member variables are set using properties or through the constructor and not directly.

Below are those that can be set, but have their defaults:

VB

'can be set
Private _PanelsUseFormsBGColor As Boolean = True
     'if true then panel is the same as the form's color
Private _PanelsUseFormsBGImage As Boolean = True
      'if true then panel uses same background image as the form
Private _ConfirmBeforeDeleting As Boolean = True
        'if true, then the default deletion mechanisms
        'prompt with a messagebox before deleting.
Private _PrimaryKeyValueNeedsQuotes As Boolean = False
   'set true if pk is a varchar or something other than number
Private _TypeOfDatabase As ifrmDataProviderType = _
       ifrmDataProviderType.SQLServer

ifrmDataProviderType.SQLServer is an enum that I provide in a file called BASEFORMEnums.vb. It is in a separate file so that I can use it it various places. Its contents are below:

VB

Public Enum ifrmDataProviderType
  SQLServer = 0
  OleDB
  Oracle 'not yet implemented
End Enum

The last member variable (below) is a field that will have a readonly property that can be read to see if data has changed.

VB

'readonly externally
Private _DataChanged As Boolean = False

Properties

Basic properties are added for each of the member variables, following the obvious naming conventions and doing nothing more than returning the value or setting it. See the ConnectionString property (below) for example.

VB

Property ConnectionString() As String
   Get
      Return _ConnectionString
   End Get
   Set(ByVal Value As String)
      _ConnectionString = Value
   End Set
End Property

An exception is the DataMode property (below) which not only sets the value of _DataMode, but also calls an empty overridable function to provide similar functionality of an event. The overriding form can just override this function and provide whatever functionality is desired to take place when the data mode is changed.

VB

Private Property DataMode() As FormDataMode
  Get
     Return _DataMode
  End Get
  Set(ByVal Value As FormDataMode)
     _DataMode = Value
     DataModeChanged(Value)
          'call an empty overridable function
  End Set
End Property

DataModeChanged method (below)

VB

Protected Overridable Sub DataModeChanged(ByVal _
         CurDatamode As FormDataMode)
   'override this to make changes when form datamode changes
End Sub

(The code for all other properties is included in the download.)

Constructor Code

For the constructor code, I chose to provide three constructors for my base form. The first "New()" was provided just so the Form Designer would allow my form to load. It should not be used for other purposes. One of the other two constructors should always be used.

Both of the other two constructors are identical except that one has an additional parameter to specify the type of database. If not specified, then SQL Server is assumed. You may add additional database support as you like.

Notice that through the constructor the form gets the connection string, the name of the table to work with and the name of the primary key for that table.

VB

Public Sub New()
   MyBase.New()
   'This call is required by the Windows Form Designer.
   InitializeComponent()

   'this called only here to allow it to load in designer;
      'should never be used
   Debug.WriteLine("Do not use the new() constructor but" & _
          " only those with arguments")
End Sub

Public Sub New(ByVal strConnectionString As String, _
   ByVal strTableName As String,
        ByVal strPrimaryKeyField As String)
   MyBase.New()
   'This call is required by the Windows Form Designer.
   InitializeComponent()

   _ConnectionString = strConnectionString
   _TableName = strTableName
   _PrimaryKeyField = strPrimaryKeyField
End Sub

Public Sub New(ByVal strConnectionString As String, _
       ByVal strTableName _
      As String, ByVal strPrimaryKeyField As String,_
       ByVal TypeOfDatabase _
      As ifrmDataProviderType)
   MyBase.New()

   'This call is required by the Windows Form Designer.
   InitializeComponent()
   _ConnectionString = strConnectionString
   _PrimaryKeyField = strPrimaryKeyField
   _TableName = strTableName
   _TypeOfDatabase = TypeOfDatabase
End Sub

Methods

Now let's take a look at the methods that we must implement in our base form.

Our Form_Load event needs to fill our lookup by calling FillLookup. But it must not fill it when in design mode.

VB

Private Sub BASEfrmUnboundDataForm_Load(ByVal sender As Object, _
     ByVal e As System.EventArgs) Handles MyBase.Load
   'be sure to fill the lookup on load
   'don't fill in designer
   If Me.DesignMode = False Then FillLookup()
End Sub

The methods related to FillLookup will demonstrate the general philosophy for providing basic functionality in the base form, calling methods at various steps along the way--some of which do something by default and are overridable should a change be desired and others that are empty and overridable and do nothing unless overridden.

The code for FillLookup is shown below:

VB

Protected Overridable Sub FillLookup()
   'avoid filling lookup in designer
   If Me.DesignMode = True Then Exit Sub

   'This procedure populates the list box
   'on the form with a list of
   ' records; should select from this list.
   Dim conn As System.Data.IDbConnection
   Dim cmdGetRecords As System.Data.IDbCommand

   Dim dr As System.Data.IDataReader
   Dim objListItem As clsListItem
   Dim strID As String

   'open the connection
   conn = OpenConnection()
       'call OpenConnection which returns nothing on failure
   If conn Is Nothing Then Exit Sub
       'exit if  couldn'tt

   Try

Notice, in the code segment above, how the conn and cmdGetRecords variables are defined. Instead of defining them as SQLConnection and SQLCommand objects, I chose to define them using the interface that the SQLConnection and OLEDBConnection implement (IDBConnection) and did the same for the Command and DataReader Objects (IDBCommand, IDataReader). In doing this the connection and command objects used won't be tied to just one database but can accept any database that implements these interfaces. So for example, I used IDbConnection, IDbCommand, and IDataReader instead of SQLConnection, SQLCommand, and SQL DataReader, respectively.

Also notice that when setting the command object, cmdGetRecords, which will actually get the records to fill up the lookup listbox, I call the

VB.NET

_SelectCommandToFillLookup 

method. I chose to name all methods that must be overriden with a preceding "_" so that they would show up at the top in the "Method Name" dropdown of the code window in the IDE. There are 6 such methods that the inheriting form must override. Thus the base form is simply calling this method, and the inheriting form is getting the appropriate SQL string into a command object and returning it. This command object is then executed and the results passed to a datareader.

VB

cmdGetRecords = _SelectCommandToFillLookup()
'set connection object for this command
cmdGetRecords.Connection = conn

dr = cmdGetRecords.ExecuteReader()

The lookup listbox, lstLookup, is then cleared and using the datareader, each record is added to the listbox.

As you know listboxes in Visual Basic .NET can contain objects instead of just strings. For that reason, one can simply make a class that has whatever inside, and override the ToString function to return a value that will be shown in the listbox or wherever.

The code in this method calls _CreateNewLookupListItem which must be overridden. The programmer can override as he likes, within limits, but must return a clsListItem which consists of what to display and the primary key. _CreateNewLookupItem is passed the datareader, which contains the current row (a read should not be performed on the datareader). Perhaps if someone else were going to use your base form you'd only want to pass them a cloned copy of the datareader or something. You can decide if this behavior should be modified.

VB

'clear lookup items
lstLookup.Items.Clear()

' Loop through the result set using the datareader class.
' The datareader is used here because all that is needed;
'is a forward only cursor which is more efficient.
Do While dr.Read()
   objListItem = _CreateNewLookupListItem(dr)
   lstLookup.Items.Add(objListItem)
Loop

An example of the inheriting form overriding _CreateNewLookupListItem is show below:

VB

Protected Overrides Function _CreateNewLookupListItem(ByVal _
     CurDataReader As System.Data.IDataReader) As clsListItem
   Dim li As New clsListItem(CurDataReader.GetValue(0), _
        CurDataReader.GetValue(0))
   Return li
End Function

Continuing with the FillLookup method (below), we see that if there is anything in the list, then the first item is selected. There is also some cleanup. Notice that if anything goes wrong i.e. an exception occurs, then FillLookupError is called and is passed the exception. FillLookupError just uses a msgbox to display the ex.message property of the exception. It is overridable if the programmer chooses to do something a little different with the error.

VB

    'if there is at least one item, then set lookup to first one
    If lstLookup.Items.Count > 0 Then lstLookup.SetSelected(0, True)

    ' Close and Clean up objects
    dr.Close()
    conn.Close()
    cmdGetRecords.Dispose()
    conn.Dispose()
      Catch ex As System.Exception
    FillLookupError(ex)
      End Try
End Sub

This is the general scheme in which each operation is carried out. Insert, Update, and Delete all use the same general concept. For example, when an record is to be updated, UpdateRecord is called, this gets the command to use to do the update from _UpdateCommand, which was overridden by the inheriting form, executes it, and then calls UpdateError if anything goes wrong, or UpdateSucceeded if all goes right. UpdateSucceeded does nothing by default but could be overridden by the inheriting form in order to do something if the update succeeds. Similar methods are used for inserting and deleting--and using the same naming conventions.

Rather than display the entire code for the Insert, Update and Delete methods, I will just highlight the interesting parts of the UpdateRecord method which is the main method for updating records. DeleteRecord and InsertRecord are the main methods for deleting and inserting records respectively. Each of these is overridable, but I wouldn't recommend overriding them, but rather overriding the "helper" methods that they call in their normal course of operation. This works in a way that is similar to the FillLookup method, that we examined above.

All source code can be downloaded, but lets take a look at one of the three methods--the UpdateRecord method:

VB

Protected Overridable Sub UpdateRecord()
   ' This sub is used to update an existing
   'record with values from the form.
   Dim conn As System.Data.IDbConnection
   Dim cmdUpdate As System.Data.IDbCommand
   Dim intRowsAffected As Integer

   ' Validate form values; only does something
   'if IsValidForm is overridden
   If Not IsValidForm() Then Exit Sub

Notice the IsValidForm method. This is a function that returns a boolean (true/false) value. By default it returns true. It can be overridden by the inheriting form in order to validate the data before an update is made. If it returns false, then Exit Sub is called and the update never happens.

VB

'open and get the connection
conn = OpenConnection()
If conn Is Nothing Then Exit Sub

Try
   'get update command object
   cmdUpdate = _UpdateCommand()
   'set command connection
   cmdUpdate.Connection = conn
   'execute command
   intRowsAffected = cmdUpdate.ExecuteNonQuery()

   If intRowsAffected <> 1 Then
      UpdateError()
   Else
      'so anyone can see if any data was changed
      _DataChanged = True

In the code segment above, notice that if anything goes wrong, UpdateError (an overridable method) is called. Also, if all goes well, then _DataChanged is set to true, some other things happen and UpdateSucceeded is called.

VB

   Dim pkValue As String = PrimaryKeyControl.Text
   'refill the lookup
   FillLookup()
   'reposition it by finding item via primary key
   FindLookupItemByPK(pkValue)
   UpdateSucceeded()
End If

In the above code, notice that the primary key control's value is read and stored, then the lookup is filled so that the new value will appear, and then FindLookupItemByPK (a private method) is called with the stored value to make the item updated be the current row. Finally, UpdateSucceeded (another overridable method) is called since the update was successful.

VB

     Catch ex As System.Exception
         UpdateError(ex)
     Finally
         ' Close and Clean up objects
         conn.Close()
         cmdUpdate.Dispose()
         conn.Dispose()
      End Try
End Sub

UpdateError is called if any exceptions occurred. Like the FillLookup method's error method, this one can be overridden, but if not just shows the ex.message value in a MsgBox. Before the sub exits, the connection is closed, and the command and connection objects are disposed.

The InsertRecord and DeleteRecord methods follow a similar pattern. However the DeleteRecord method uses a few new methods, mainly GetLookupItemIndexByPK and AllowRecordDeletion.

VB

'call the overridable function that checks to see
 'if deletion is allowed for this item
If Not AllowRecordDeletion(PrimaryKeyControl.Text) Then Exit Sub

AllowRecordDeletion (called above) is a function that is passed the primary key and returns true or false--true meaning "ok to delete." This function is overridable, but by default, checks the ConfirmBeforeDeleting property and if it is set to true, prompts the user with a MsgBox asking them if they are sure they want to delete. The default code, returns true or false depending on their answer or always returns true if ConfirmBeforeDeleting is set to false. This function could be overridden to perform some checks like, "does this record have child records that would need to be deleted, etc?" and maybe refuse deletion if true. This would be good in the case of our "Lookup Table" where we have a list of possible field values and we want the user to be able to Add, Update and Delete these, but if they are in use i.e. at least one record was created using a certain value, then they shouldn't be allowed to be deleted. If the value had never been used then allowing deletion would be desirable.

VB

'get list index of item being deleted
iItemIndex = GetLookupItemIndexByPK(PrimaryKeyControl.Text)

The other differing method is the GetLookupItemIndexByPKmethod (called above) which as its name suggests, is passed a primary key value and returns the index of that item in the listbox (lstLookup). This is needed so that after the record is deleted, the listbox can select the prior record if one is available or none if the user deleted the last record. DeleteRecord handles all of these chores by default, but as you can see can be overridden to provide more or differing functionality on a form by form basis as required.

Of course there are a few other methods that need to be considered. For example, when you click Add, you basically need to clear the form to its default "new record" values. This is facilitated by calling the ClearForm method.

The default implementation of ClearForm uses some pretty nifty intelligence in order to clear each control in the fields panel, pnlFields, which holds the data entry controls that the inheriting form will supply. This implementation examines each control to see what type of control it is and then assigned it the most logical and expected default values. A listbox, for example, selects -1 which means "no selection" and a textbox clears the text. A combobox attempts to select the first item in the list, but upon failure selects nothing, and a checkbox clears the checkmark. The code for this method is shown below.

VB

'ClearForm is called after new is clicked or upon
 'startup if there are no records;
Protected Overridable Sub ClearForm()
   'clears or sets all to defaults
   'assume basic default functionality by going through
  ' all controls in
   'pnlfields and setting text controls to clear string;
  'clearing checkboxes;
   'setting combos to first element---should override to
   ' provide different functionality if needed
   Dim ctl As Control
   For Each ctl In pnlFields.Controls
      If ctl.GetType.Equals(GetType(TextBox)) Then
         CType(ctl, TextBox).Text = String.Empty
      ElseIf ctl.GetType.Equals(GetType(CheckBox)) Then
         CType(ctl, CheckBox).Checked = False
      ElseIf ctl.GetType.Equals(GetType(ComboBox)) Then
         Try
            'if possible set to first element
            CType(ctl, ComboBox).SelectedIndex = 0
         Catch
            CType(ctl, ComboBox).SelectedIndex = -1
         End Try
      ElseIf ctl.GetType.Equals(GetType(ListBox)) Then
         CType(ctl, ListBox).SelectedIndex = -1
      End If
   Next
End Sub

Wiring These Methods to Our Controls and Handling Some UI Events

While we won't discuss them all, we will take a look at a few of the important ones--a casual browsing of the downloaded source will reveal a few additional methods whose purpose should be obvious.

btnAdd_Click()

The Add button's click method only needs to call ClearForm, set the DataMode property to FormDataMode.Add and disable both the Add and Delete buttons. Add is clicked when the user wants to add a new record. It is required that they click the Update button to commit this record.

btnUpdate_Click()

The Update button's click method just calls InsertRecordif the DataMode property is set to FormDataMode.Add, and UpdateRecord if it was set to FormDataMode.Update.

btnDelete_Click()

The Delete button's click method is simple. It just calls DeleteRecord. By using the primary key to perform a delete, this method should, offer the desired functionality by default, and will probably never need changing.

btnRefresh_Click()

The Refresh button's click method calls the RefreshClicked method, which by default calls FillLookup, but can also be overridden. You should override RefreshClicked if you have other listboxes, etc., besides the lstLookup listbox, that need refreshing.

lstLookup_SelectedIndexChanged

When the item selected in the lookup listbox,lstLookup, changes, we need to fill the controls in pnlFields with the record values associated with the item selected. For that reason, this event first calls PopulateForm and then enables the Add and Delete buttons. It also sets the DataMode to FormDataMode.Update as opposed to FormDataMode.Add. That way if the user changes something and hits the Update button, the UpdateRecord method is called to save the changes, and not the InsertRecord method which would add a new record based on the current field values.

Now We Must Provide Some Methods to Allow for Validation Using the ErrorProvider Control

We should provide some methods that will affect the controls in pnlFields, based on some validation code, probably placed in the IsValidForm method, which use the ErrorProvider control to display an error beside the control having the offending data.

I created five such methods, all public, to facilitate this. First lets take a look at them and later we'll see how they can be used in the "Writing Code In the Inherited Form" section. The first two, ClearErr and SetErr, can be used anywhere, but are mostly used as helper methods for remaining three methods which are easier to use.

ClearErr	Is passed the appropriate control and clears any errors for it by calling errProvider.SetError and passing it the name of the control and an empty string.
SetErr	Is passed the appropriate control and calls errProvider.SetError to set that controls error string to the one passed in to SetErr.
ErrRequired-ValueSupplied	Is passed the appropriate control and an error message. Functions like the RequiredFieldVaildator in ASP.NET. Requires that the user enters something. Returns false and calls SetErr if they didn't.
ErrRegExpMatched	Is passed the appropriate control, a regular expression, and an error message. Tests to see that the control in question fits the regular expression given and if not returns false and calls SetErr with the error message.
ErrText-LengthInRange	Is passed the appropriate control, a minimum and maximum length for the text entered, and an error message. This method is only used for text related controls such as the textbox or combobox (which has a textbox). Returns false if invalid and calls SetErr with the error message.

Feel free to add your own validation methods as you need them. Perhaps you might want to add one that limits the min and max values for numeric data i.e. "must be between 1 and 99." When IsValidForm is called, and if you have overridden it, you can call these methods to decide if the form is valid and return false if not.

Perhaps I have failed to mention some of the other methods but you will find any of them in the downloadable source.

Now, let's take a look at using this joker...

Create the Inherited Form

Creating an inherited form is simple, just right click on the project in Solution Explorer, choose Add and then instead of "Add Windows Form" choose "Add Inherited Form." Name your inherited form and then click OK. After this the "Inheritance Picker" dialog is displayed. Choose BASEfrmUnboundDataForm as the form to inherit from and click OK. You should now see your form in the Solution Explorer window.

Remember there are 6 methods that absolutely must be overridden before your form will work and there are some other little subtleties that should also be performed.

Viewing the code for your new form, you will notice something like the following for the first 2 lines of code.

Public Class frmInheritingForm
   Inherits MYPROJECTNAME.BASEfrmUnboundDataForm

Of course, your form's name would be the class name and the name of your project would actually be there instead of "MYPROJECTNAME." We can see that our form inherits from BASEfrmUnboundDataForm. This means it has all the methods and code that the base form has, and may access those items that inheriting forms are allowed to access. Since the visual aspects of a form are actually just generated code from the form designer to the .vb code file, our form inherits the methods and the visual appearance of the base form. This is really cool and can save you 100's of hours of recoding the same old thing again and again.

Imagine that we are writing an application for the police department. All of our users are officers. As a result it makes sense for our officer table to also be our users i.e. based upon the officers badge number (actually badge number plus last 4 digits of SSN--this done incase officer 55 leaves and a new officer is hired and gets badge 55. If officer one was 559306 and officer two is 552963, then they are easily distinguished as different officers and no historically linked data is lost.) Using this example let's imagine that our officer table contains Badge, FirstName, MidName, LastName, Password, UserType, and Active (still with us or not).

With this in mind we would add text box controls for each field except the last two, which would be a listbox and checkbox, respectively. We would name them obvious names such as txtBadge.

A Screen Shot of My Actual Officers Form, frmOfficer (below)

Of course the data is fake, and I added a few things that I won't speak about in the article, but the complete source code to this form is included as frmOfficer.vb and frmOfficer.resx for your viewing. I am actually using this very form in a law enforcement application at present. For that reason, please vary the appearance of your form, if you should actually create a real officer's form as part of a program to sell. Notice in my version I have a special button for changing the badge. I also have the Badge field as read-only except during addition of a new record. Also the officer icon, is my own creation and property and not for commercial use. Feel free to replace it with your own icon in the event that you do something commercial with a form like this one for law enforcement.

Coding the Constructor

The first thing we should do is create a constructor for the new form. In order for our form to function we must have a connection string.

VB

Public Sub New(ByVal strConnectionString As String)
   MyBase.New(strConnectionString, "tblOfficer", "Badge")
   InitializeComponent()

   'indicate primary key control
   PrimaryKeyControl = txtBadge
   PrimaryKeyValueNeedsQuotes = True
   PrimaryKeyField = "Badge"
End Sub

Notice in the constructor (above) that we accept a connection string so that when someone instantiates our form, they will pass the connection string as part of the New method--the constructor.

We can still access our base form by using the MyBase keyword. For this reason, our new constructor, first calls the base form's constructor passing it the connection string, the table name and the name of the primarykey, by calling MyBase.New. This line should always be added to your form's constructor.

Next we set some of the properties--these are required. We tell the base form what the name of the control that will hold the primary key values is--in this case txtBadge. Then we set PrimaryKeyValueNeedsQuotes because the primary key is a string and not a number although most will be stored in a textbox regardless. Finally we set the PrimaryKeyField property to Badge.

Now all that we need to do is override the 6 methods that must be overridden. Remember these start with the "_" character. To see them, with the code window for your inherited form, click "(overrides)" in the top left listbox and then the method to be overridden from the right listbox. One of these methods is the _CreateNewLookupListItem method. By selecting one of these methods, VB.NET adds the method code template to the code window i.e. the function or sub and its parameters and an "End Function" or "End Sub." We just have to fill in the blanks.

_CreateNewLookupListItem

The _CreateNewLookupListItem method gets called each time that an item is to be added to the listbox. The current datareader is passed to this method so that by using the GetString or GetValue methods and creating a clsListItem, we can tell the base form how to make the list items. This allows us some flexibility in what is to be displayed and the key of that item. A sample implementation is shown below.

VB

Dim li As New clsListItem(CurDataReader.GetString(0), _
   CurDataReader.GetValue(1))
Return li

_SelectCommandToFillLookup

The _SelectCommandToFillLookup method returns a command object with the appropriate SQL required to fetch all the records of our table showing two fields, the first being what to display in the listbox--could be concatenated such as the code below--but should only return 2 fields. The second field should be the primary key field of the table. This is used by FillLookup.

VB

Dim cmd As New System.Data.SqlClient.SqlCommand(_
   "SELECT LastName + ', ' + _
    FirstName + ' ' +
  MidName as Name,Badge FROM tblOfficer ORDER BY LastName")
Return cmd

The above is a whopping 2 lines of code to accomplish all that we would like to accomplish in displaying our rows in a lookup table that will be used to navigate to the record desired for deletion or modification. And the second line merely returns the command object. So actually this could be done in just one line by replacing the "Dim cmd as" with "return" and getting rid of the "Return cmd" line if you like.

_SetFields

The base form method, PopulateFields, calls _SetFields in order to move data from the datareader to the corresponding controls. Since the base form knows nothing about these controls it allows you to tell it how to do that by passing you the datareader, being set to the current row. You simply use GetString or GetValue to read the appropriate fields. An example is found in the code below:

VB

With CurDataReader
   txtBadge.Text = .GetString(0)
   txtFirstName.Text = .GetValue(1)
   txtMidName.Text = .GetValue(2)
   txtLastName.Text = .GetValue(3)
   txtPassword.Text = .GetValue(4)
   Try
      cboUserType.SelectedIndex = _
       cboUserType.FindString(.GetValue(5))
   Catch
   End Try
   ckActive.Checked = .GetBoolean(6)
 End With

As you can see from the simple code above, it is merely reading the right field and sticking it in our control. This is very straightforward and easy. For the combobox, will use the FindString method to find the right value and select it. This is a combobox that doesn't allow items that are not in its list.

_SelectCommandToGetRecordByPK

The base form method, PopulateFields calls _SelectCommandToGetRecordByPK, passing it the primary key of the item in lstLookup that was selected and gets a Command object to be used to fetch that exact record. PopulateFields then calls _SetFields in order to transfer that record data into the appropriate controls located in pnlFields.

_InsertCommand

The base form calls InsertRecord if the Add button was clicked, data was entered and then the Update button was clicked. The btnUpdate event decides that since we are in Add mode, it will call InsertRecord which uses _InsertCommand to get the correct command object to insert the new record.

Basically you create a new command object, that will take the data from your controls (those in pnlFields) and move it to DataParameters that are added to the command object and then return that command object. To simplify things and since I was working with MSDE (SQL Server Desktop Addition), I decided to make a stored procedure to handle my Insertions. And also a special class to make the whole process simpler.

VB

Dim pb As New clsParamBuilder(New SqlClient.SqlCommand(_
  "spAddNewOfficer"), CommandType.StoredProcedure)

'add params
pb.AddParam("@Badge", txtBadge.Text)
pb.AddParam("@FirstName", txtFirstName.Text)
pb.AddParam("@MidName", txtMidName.Text)
pb.AddParam("@LastName", txtLastName.Text)
pb.AddParam("@Password", txtPassword.Text)
pb.AddParam("@UserType", cboUserType.Text)
pb.AddParam("@Active", ckActive.Checked)

'return the command object
Return pb.CommandObj

You can do this by creating a new command object as normal, with spAddNewOfficer as the text and setting the commandtype to "stored procedure", declaring a variable as a SQLParameter, adding each parameter's info and then adding each parameter to the command object and returning it. However, this results in a lot of code. For that reason, as you will notice above, I created a class called clsParamBuilder to make this a lot simpler.

The code for this class, which is in a separate code file, is shown below:

Code for clsParamBuilder

VB

Public Class clsParamBuilder
Private _cmd As IDbCommand

Public ReadOnly Property CommandObj() As IDbCommand
Get
Return _cmd
End Get
End Property

Sub New(ByVal cmd As IDbCommand)
_cmd = cmd
End Sub

Sub New(ByVal cmd As IDbCommand, ByVal CommandType As _
    System.Data.CommandType)
_cmd = cmd
_cmd.CommandType = CommandType
End Sub

Public Sub AddParam(ByVal ParamName As String, _
         ByVal ParamValue As Object, _
  Optional ByVal ParamDirection As System.data.ParameterDirection = _
  ParameterDirection.Input)
Dim p As System.Data.IDbDataParameter

'simplifies adding parameters to a command object
If TypeOf _cmd Is System.Data.SqlClient.SqlCommand Then
p = New System.Data.SqlClient.SqlParameter(ParamName, ParamValue)
ElseIf TypeOf _cmd Is System.Data.OleDb.OleDbCommand Then
p = New System.Data.oledb.OleDbParameter(ParamName, ParamValue)
End If

p.Direction = ParamDirection

_cmd.Parameters.Add(p)
End Sub
End Class

I won't take the time to explain this class as its usage and function should be evident given the code and the above examples. I have found it to be a real time saver. It prevents repetitive verbose coding when dealing with command objects and their parameters.

_UpdateCommand

The base form method calls UpdateRecord if the Add button was not clicked, data was changed and then the Update button was clicked. The btnUpdate event decides that since we are in Update mode, it will call UpdateRecord which uses _UpdateCommand to get the correct command object in order to update the current record.

The _UpdateCommand method works the same as the _InsertCommand except that it has code to perform an update. In my case it just calls a different stored procedure and the rest of the code is the same as the one for _InsertCommand.

Now, since we used a combo box for User Type we must somehow fill it with values. Assuming that these are fixed values, we would simply override the Form_Load event and place code there to fill our combo with those fixed values. Be sure to call PopulateForm at the end of this code if you over load Form_Load.

At this point our form should run. And as you can see, by inheriting the base form, which has around 835 lines of code, excluding the code that the form designer generates and maintains, we have saved a lot of time and coding. A recent, fully functioning inherited form of mine had less than 200 lines of code--of course this is counting spaces, comments, function definitions and stubs that the IDE adds by just selecting the method from the method drop-down list. So, yes there is still some coding required and especially if you have controls such as combos to restrict to certain values, or want to write validation code, but it is pretty simple code. Actually what I do is just open an already inherited form, copy much of the code and paste it into my new inherited form. Be careful not to copy all of it. You won't want to copy the same generated code, nor over the name of the form class, etc. of course.

Adding the Finishing Professional Touches to Our Form

Although our form will run, we should add a few more things to it, such as validation in order to make it more professional.

ClearForm

In my form I override the ClearForm method which would clear all textboxes and clear my checkbox (ckActive). Since most people are active by default I want ClearForm to do its normal thing so I call MyBase.ClearForm, but then I want to check the checkbox by default so I say add the line ckActive.Checked=true after the call the MyBase.ClearForm. That's all there is to it.

DataModeChanged

You may wish to override the DataModeChanged method to

• Make some controls readonly based upon whether adding a new record or updating and existing one
• Perform some additional action(s) based upon the mode changing

AllowRecordDeletion

In my form I also override the AllowRecordDeletion method so that it takes the primary key that is passed in, sees if the officer's badge(PK) is already in another table as a foreign key and if so will not allow the deletion of the officer. You may have similar needs to check certain things before allowing a record to be deleted. AllowRecordDeletion is the place to do that.

IsValidForm

You will most likely want to override the IsValidForm method in order to provide data validation for your form. Let's take a look at how I did it in my officers form.

Dim bValid As Boolean = True
If Not ErrRegExpMatched(txtBadge, "[A-Z][A-Z]\d{6}", _
     "Badge format should be 2 Letters, 2-digit badge number, " & _
     "then last 4 SSNO of officer. example BD021234") _
       Then bValid = False
If Not ErrRequiredValueSupplied(txtFirstName, _
       "First name is required")_
       Then bValid = False
If Not ErrRequiredValueSupplied(txtLastName, "Last name is required")_
      Then bValid = False
If Not ErrTextLengthInRange(txtPassword, 4, 15, _
    "Password must be between 4 and 15 characters long.") _
          Then bValid = False

If Not bValid Then
   MessageBox.Show("There were invalid entries on this form." & _
   " It cannot be added/updated until you've fixed the problems." & _
   " There are exclamation points(!) beside each problem.", _
   "INCOMPLETE OR INVALID DATA", MessageBoxButtons.OK, _
     MessageBoxIcon.Exclamation, _
   MessageBoxDefaultButton.Button1, _
   MessageBoxOptions.DefaultDesktopOnly)
End If
Return bValid

From the above code listing (the contents of my IsValidForm overridden method), you can see how to use the validation methods that we added to our base form. See how simple form validation is when done in this way. We start with bValid set to true and if anything goes wrong we set it to false. If it is false we tell the user something is invalid and then we return bValid so that the base form knows what action to take. Basically if adding or updating a record and we return invalid, the base form will not add or update the record. The problem controls will have the little red exclamation icon beside them and will show a tool tip if we hover over the top of them.

You may very well desire many more features in your inherited form and you can easily add them. Just remember that overriding some methods in the base form will necessitate that you call the method in the base form (using the MyBase keyword) at some point in your overridden method in order to get the default behavior in addition to your new behavior.

Conclusion

Well, I hope that you've enjoyed this tutorial and that this base form will be of use to you. And even if you may not use this base form, hopefully you have gained some insight into using Visual Inheritance, and some other concepts.

Fill free to use this code and expand upon it as you like. I would ask that you give me credit for my work by keeping my copyright line at the top of the BASEfrmUnboundDataForm and other code file(s) and make a comment below it if you make any changes to it. Also this code is provided "AS IS WITH ABSOLUTELY NO WARRANTIES AND I TAKE NO RESPONSIBILITY FOR ANY UNINTENTIONAL DAMAGES THAT IT MAY OR MAY NOT CAUSE." Other than these requirements, feel free to use and modify this code as you desire. It may be included in a product to be sold, but may not be sold in and of itself. In other words, I am providing this code to be freely used by all and no one should have to pay for it, but if you make a program to sell that uses it, then that is perfectly acceptable as long as I am given credit as mentioned above--a copyright notice of the original design.

Again, any comments, or suggestions for improvements, or bugs are welcome.

History

• March 2004 Initial submission