Programming with Class

We want to allow programmers to use this inheritable form simply by filling in some properties. What are properties? In Visual FoxPro, they're something like public variables at the class level. In Visual Basic .NET you can enter a Public As String statement in the declarations at the top of a class, and the resulting element (called a field ) is accessible to classes subclassed from the class. For example, if a class contains Public MyField as String in its declarations, then in a subclass of the class, IntelliSense will expose Me.MyField ( Me is like THISFORM in FoxPro). But it's not visible in the class's property sheet, nor is it visible in the property sheet of a subclass of the class.

In order to view and set the property in a subclass of the class, you have to create a private variable and a property procedure to save and retrieve it. And what's exposed is not the private variable, but rather the property procedure containing the Getter and Setter routines. For example, to provide a settable MainTable property, you add the code shown in Listing 4.3 to the top of your form class's code, just below the declarations.

Listing 4.3. Declaring A Property Procedure

 Private _MainTable As String Public Property MainTable() As String     Get         Return _MainTable     End Get        Set(ByVal Value As String)         _MainTable = Value     End Set End Property 

You really only have to enter four of these lines of code: When you type Public Property As and press Enter, the IDE writes all of the code except Return _MainTable and _MainTable = Value . So you have to create the private variable (by convention the name of the property procedure preceded by an underscore ), the procedure name, and the Return and Value assignment statements. Then, when you subclass the class, MainTable (not _MainTable ) appears in the property sheet. It looks unnecessarily complicated, especially to FoxPro developers; but after you do it a few hundred times, you won't even notice it.

Automating Data Access

ADO.NET is the data engine used in .NET applications. It's a disconnected methodology; you request data and close the door. When you want to save it, you reconnect and send the changes. Inside the application, data is stored in a dataset ” sort of a miniature data environment, which can contain tables, relations, and some other elements. For our simple application, it will contain a single table, and the programmer will know the table's name. So we'll need a MainTable property. Open up the BaseForm.vb code module and enter the code shown in Listing 4.3. Rebuild the project and then rebuild the solution. Now open the inherited form in the designer, press F4, and look under Misc (see Figure 4.9): Voil  , there's your new MainTable property!

Figure 4.9. Exposing a property in a subclass.

How do we use this property? We use it just exactly as we use properties in FoxPro; they're variables that the programmer can set while designing the form. I need two more properties and a constant before I can do what needs to be done, so I'll just fast-forward and list the entire declarations and property procedures code in one fell swoop, as shown in Listing 4.4.

Listing 4.4. The BaseForm Inheritable Form Class

 Public Class BaseForm     Inherits System.Windows.Forms.Form #Region " My declarations "     Public Const TurnOn As Boolean = True     Public Const TurnOff As Boolean = False     Public ConnStr As String = _      "Provider=SQLOLEDB;server=(local);database=Northwind;uid=sa;pwd=;"     Public dc As OleDb.OleDbConnection     Public daFiltered As OleDb.OleDbDataAdapter     Public dsFiltered As DataSet     Public daOneRecord As OleDb.OleDbDataAdapter     Public dsOneRecord As DataSet     Public _MainTable As String     Public _keyfield As String     Public _searchfield As String     Public spacer As String #End Region #Region " My Property procedures "     Public Property MainTable() As String         Get             Return _MainTable         End Get         Set(ByVal Value As String)             _MainTable = Value         End Set     End Property     Public Property KeyField() As String         Get             Return _keyfield         End Get         Set(ByVal Value As String)             _keyfield = Value         End Set     End Property     Public Property SearchField() As String         Get             Return _searchfield         End Get         Set(ByVal Value As String)             _searchfield = Value         End Set     End Property #End Region 

The #Region directives allow you to hide chunks of code. For example, when I collapse all of my code regions , this is what I see in the code editor for the BaseForm (see Figure 4.10). Needless to say, this is a lot easier to navigate than 453 lines of code.

Figure 4.10. Collapsed code using #Region directives.

The additional public variables declared in the preceding code include constants to provide more meaningful symbols than True and False ; a connection string and DataConnection to hook up to SQL Server; a couple of DataAdapter s and datasets to get a list of candidate records and the single record the user selected, respectively; and public properties for the names of the Main Table, the key field (for retrieving the selected record), and the name of the searchable field to display in the ListBox .

The reason that everything has to be declared up front is Option Strict . The code won't compile unless we use DIM , PUBLIC , or PRIVATE (or FRIEND or whatever) to declare every single variable that we use in the code. IntelliSense uses these declarations to know what to show us when we hit that first period, and the compiler uses them to set aside storage.

ADO.NET uses a connection to build a DataAdapter . The DataAdapter contains Select , Insert , Update , and Delete logic to get the data to and from the data source specified by the connection. The data is stored inside your form in a DataSet object, which is like a data environment built of XML. It contains tables, relations, and other stuff that this exercise won't need. For our purposes, it contains a table, and its name is contained in the MainTable property. The KeyField , the one we'll use to retrieve a single record and to post updates, is another named property, and SearchField , the field to search and to display in the list box, is named in the third property. Our code will refer to these three properties, trusting that the programmer has filled them in correctly.

Now we're ready to write some code. The Load event fires first in Visual Basic .NET, just as it does in FoxPro. We'll use the connection string to open the connection; then construct a SELECT statement and create a DataAdapter ; then use the DataAdapter 's Fill method to fill a DataSet with a table named using the contents of the MainTable property. Listing 4.5 shows the code for the Load event.

Listing 4.5. The BaseForm Load Event Code

 Private Sub BaseForm_Load( _   ByVal sender As System.Object, ByVal e As System.EventArgs) _  Handles MyBase.Load     Try         Label2.Text = "in the " + SearchField + " field"         dc = New OleDb.OleDbConnection         dc.ConnectionString = ConnStr         dc.Open()     Catch oEx As Exception         MsgBox("Connection failed: " + oEx.Message)         Close()     End Try End Sub 

How BaseForm Load Works

The program puts the name of the search field into the label at the top of the screen so that the display makes sense. Then it uses the connection string from the template form to open a connection to the data source, which could be SQL, ODBC, or anything else.

Loading the List Box and Displaying a Record When Clicked

The user gets a chance to filter the data in the table; if the table contains only a dozen or two records, it may not even be necessary. When the Show Matching Records button is clicked, the list is loaded and the first record in the list is displayed. Subsequently, clicking on any item in the list causes its record to be displayed. The code is shown in Listing 4.6.

Listing 4.6. The LoadList Button Click Event Code

 Private Sub LoadList_Click( _   ByVal sender As System.Object, ByVal e As System.EventArgs) _  Handles btnLoadList.Click     LoadTheList() End Sub Public Sub LoadTheList()     Dim I As Integer     Dim NumFound As Integer     Dim Str As String     Str = "SELECT * FROM " + MainTable _         + " WHERE UPPER(" + SearchField + ") LIKE '" _         + SearchValue.Text.ToUpper.Trim + "%'"     daFiltered = New OleDb.OleDbDataAdapter(Str, dc)     dsFiltered = New DataSet     daFiltered.Fill(dsFiltered, MainTable)     'Clear the listbox and load it     With ListBox1         .Items.Clear()         NumFound = dsFiltered.Tables(MainTable).Rows.Count - 1         Dim dr As DataRow         For I = 0 To NumFound             dr = dsFiltered.Tables(MainTable).Rows(I)             Str = dr(SearchField)             Str = Str.PadRight(40)             Str = Str + CStr(dr(KeyField))             .Items.Add(Str)         Next     End With     ListBox1.SelectedIndex = 0     LoadaRecord()     Buttons(TurnOn) End Sub Private Sub ListBox1_SelectedIndexChanged( _   ByVal sender As System.Object, ByVal e As System.EventArgs) _  Handles ListBox1.SelectedIndexChanged     LoadaRecord() End Sub Public Sub LoadaRecord()     Dim Kv As String     Kv = ListBox1.SelectedItem     Kv = Kv.Substring(40)     Kv = Kv.Trim     Dim str As String     str = "SELECT * FROM " + MainTable _         + " WHERE " + KeyField + " = '" + Kv + "'"     daOneRecord = New OleDb.OleDbDataAdapter(str, dc)     dsOneRecord = New DataSet     dsOneRecord.Clear()     daOneRecord.Fill(dsOneRecord, MainTable)     Dim dr As DataRow     dr = dsOneRecord.Tables(MainTable).Rows(0)     ' Load on-screen controls' text properties     Dim FldName As String     Dim Ctrl As Control     For Each Ctrl In Controls         Try             If TypeOf Ctrl Is TextBox Or TypeOf Ctrl Is ComboBox Then                 Ctrl.DataBindings.Clear()                 FldName = Ctrl.Name.Substring(3)             ' skip characters "0-2"                 Ctrl.Text = dr(FldName)             End If         Catch   ' ignore fields that don't have a column to bind to         End Try     Next End Sub 

How LoadList Click Works

Clicking on the Show Matching Records button is handled by the LoadList_Click routine, which simply calls LoadList() . The routine creates a SELECT statement ending in a LIKE condition that matches any string starting with the letters the user typed in (try it with a single letter to start). The field that's searched is the one named in the SearchField property, which is also the field that's loaded into the TextBox item list. The key value for each record, KeyField , is appended to the end of the 40-character SearchField string, so that it's not visible. When the user clicks on the list, the key is extracted from position 41 (40 in VBSpeak) of the selected item and used to return a single record into the dsOneRecord dataset.

The challenge here was to bind the data to the fields on the screen. I used a little trick here; I assume that each field starts with a three-character mnemonic for the control type ( txt for text box, cmb for combo box, and so on ”a mechanism we're all pretty much used to anyway), and that the remaining characters are precisely the name of a field in the dataset. Datasets don't have field names, but the rows in the tables that they contain do; so I reference a row in the Tables(MainTable) collection, and then use dr( FieldName ) ” where I just inferred FieldName from the control name ”to find the data and assign it to the control's Text property. The Try...Catch...End Try with no code after the Catch is a neat trick; if the control doesn't have a matching field in the data row, it's an error, which I throw away.

Utility Routines

There are a few routines that are used by several of the buttons' Click events, so I'll show them first. Listing 4.7 shows the code for the Inputs subroutine.

Listing 4.7. The Inputs Subroutine Code

 Public Sub Inputs(ByVal onoff As Boolean)     Dim Ctrl As Control     For Each Ctrl In Controls         If TypeOf Ctrl Is TextBox _         Or TypeOf Ctrl Is ComboBox Then             Ctrl.Enabled = onoff         End If     Next     SearchValue.Enabled = Not onoff     btnLoadList.Enabled = Not onoff     Buttons(Not onoff) End Sub Public Sub Buttons(ByVal onoff As Boolean)     btnAdd.Enabled = onoff     btnEdit.Enabled = onoff     btnDelete.Enabled = onoff     btnClose.Enabled = onoff     btnSave.Enabled = Not onoff     btnCancel.Enabled = Not onoff End Sub Public Sub ClearFields()     Dim Ctrl As Control     For Each Ctrl In Controls         If TypeOf Ctrl Is TextBox _         Or TypeOf Ctrl Is ComboBox Then             Ctrl.Text = ""         End If     Next End Sub 

How the Inputs Subroutine Works

Inputs turns the text boxes, combo boxes, and other controls on and off as needed. I can pass it the constant TurnOn (True) to enable them or TurnOff (False) to disable them. Buttons ensures that, when the input fields are enabled, all of the buttons except Save and Cancel are disabled and vice versa. ClearFields blanks the input fields before adding a record.