< Day Day Up > |
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 ProcedurePrivate _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 AccessADO.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 ClassPublic 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 CodePrivate 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 WorksThe 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 ClickedThe 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 CodePrivate 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 WorksClicking 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 RoutinesThere 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 CodePublic 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 WorksInputs 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. |
< Day Day Up > |