8.4 Make a Generic Search Form in a Visual Basic .NET Desktop Application
Another useful utility that takes advantage of being data driven is a standard search form that you can use for any number of tables, such as customers, employees, or products. This How-To shows you how to create such a Windows Form so that all you need to use it with different tables is to set four custom properties of the form in code.
You like to be able to provide a usable search form for my users, without having the hassle of creating a custom form for every topic that the users are maintaining. In this How-To, you will see how to create a form that provides a quick lookup for records and can be used with various topics, such as Customers and Employees, by setting up only a few custom properties on the search form.
Technique
The forms package of Visual Basic has a class module behind it where you can add your own properties and methods. The .NET version of it is no exception.
In this How-To, you will see a simple use for custom properties that are being added to a form. Properties can be specified on a form by adding the following syntax to your form:
Public Property PropertyName() As DataType
Get
PropertyName = ModuleLevelMemoryVariable
End Get
Set(ByVal Value As DataType)
ModuleLevelMemoryVariable = Value
End Set
End Property
With the ModuleLevelMemoryVariable being declared in the module declaration area of the form's class module, you can see the properties created for the search form, called frmHowTo8_b.vb, in Listing 8.16.
Listing 8.16 frmHowTo8_4b.vb: Creating Custom Properties for the Search Form
Private mstrDisplayName As String
Private mstrRecordSource As String
Private mstrSearchField As String
Private moResultValue As Object
Private mstrKeyField As String
Public Property DisplayName() As String
Get
DisplayName = mstrDisplayName
End Get
Set(ByVal Value As String)
mstrDisplayName = Value
End Set
End Property
Public Property SearchRecordSource() As String
Get
SearchRecordSource = mstrRecordSource
End Get
Set(ByVal Value As String)
mstrRecordSource = Value
End Set
End Property
Public Property SearchField() As String
Get
SearchField = mstrSearchField
End Get
Set(ByVal Value As String)
mstrSearchField = Value
End Set
End Property
Public Property KeyField() As String
Get
KeyField = mstrKeyField
End Get
Set(ByVal Value As String)
mstrKeyField = Value
End Set
End Property
Public Property ResultValue() As Object
Get
ResultValue = moResultValue
End Get
Set(ByVal Value As Object)
moResultValue = Value
End Set
End Property
By assigning values to these properties after initiating an instance of the form, you can utilize the properties and the data stored in those properties from within the forms properties and methods, as well as the procedures assigned to the events within the form.
For more information on creating custom classes, properties, and methods for use with your database application, see Chapter 9, "Using Classes with Databases to Make Life Easier."
Steps
Open and run the VB.NET �Chapter 8 solution. From the main Windows Form, click on the command button with the caption How-To 8.4a. This form is a simple one that contains text boxes for the Customer table in Northwind. Click on the Search button to open the search form. Click on the button labeled B. You will see the data grid displayed in the bottom of the form filled with the CompanyName column of the Customer table, beginning with the letter B (see Figure 8.7).
Place the cursor in one of the customers displayed in the grid, and then click Accept. The search form will be hidden, and the fields in the first form will be filled in with the data from the chosen record.
Create a Windows Form. Then place the controls on the form shown in Figure 8.7, with the properties set forth in Table 8.5.
Table 8.5. Label, TextBox, and Command Button Controls Property Settings for the Calling Form
|
Label
|
Caption
|
Customer ID
|
Label
|
Caption
|
Company Name
|
Label
|
Caption
|
Contact
|
Label
|
Caption
|
Contact Title
|
Label
|
Caption
|
Address
|
Label
|
Caption
|
City
|
Label
|
Caption
|
Region
|
Label
|
Caption
|
Country
|
Label
|
Caption
|
Phone
|
Label
|
Caption
|
Fax
|
TextBox
|
Name
|
txtCustomerID
|
TextBox
|
Name
|
txtCompanyName
|
TextBox
|
Name
|
txtContact
|
TextBox
|
Name
|
txtContactTitle
|
TextBox
|
Name
|
txtAddress
|
TextBox
|
Name
|
txtCity
|
TextBox
|
Name
|
txtRegion
|
TextBox
|
Name
|
txtPostalCode
|
TextBox
|
Name
|
txtCountry
|
TextBox
|
Name
|
txtPhone
|
TextBox
|
Name
|
txtFax
|
Button
|
Name
|
btnSearch
|
|
Caption
|
&Search
|
On btnSearch, add the code in Listing 8.17 to the Click event. This routine shows the power of creating custom properties. After instantiating an instance of the Search form�in this case, frmHowTo8_4b.vb�the four custom properties shown in Listing 8.17 are set before the form is displayed. This is powerful in letting you get the form set up exactly the way you want to before the user even sees it. After setting up the custom properties, the ShowDialog method is called off of frmSearch. By calling this method, code execution is halted until the form is closed or hidden. This same line of code compares the DialogResult property of the form to the value; if it matches, the code calls the LoadIndividual routine, passing the ResultValue custom property of frmSearch. Both the DialogResult and ResultValue properties are set in frmSearch and will be shown later in these steps.
Listing 8.17 frmHowTo8_4a.vb: Executing a SQL Server-Supplied Stored Procedure That Lists the Tables in the Database
Private Sub btnSearch_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles btnSearch.Click
'-- Instantiate the search forms.
Dim frmSearch As frmHowTo8_4b
frmSearch = New frmHowTo8_4b()
'-- Set the custom data properties on the search form.
' This is what makes it so data driven.
frmSearch.DisplayName = "Customers"
frmSearch.SearchRecordSource = "Customers"
frmSearch.SearchField = "CompanyName"
frmSearch.KeyField = "CustomerID"
'-- Open the search form as dialog.
' Based on the DialogResult property, use the
' custom property ResultValue property and load
' the requested record.
If frmSearch.ShowDialog() = DialogResult.OK Then
LoadIndividual(frmSearch.ResultValue)
End If
End Sub
Create the LoadIndividual routine by entering the code shown in Listing 8.18 into the form. Taking the strKeyValue passed from the results of the search, a data adapter is created and a DataSet is filled. Next, the individual data row is created. Last, each of the TextBox controls is loaded with the value from the column with the corresponding name. Notice the use of the Try…Catch…End Try to ignore controls that don't have a like column in the data row.
Listing 8.18 frmHowTo8_4a.vb: Loading an Individual Record into Text Boxes on the Form
Private Sub LoadIndividual(ByVal strKeyValue As String)
Dim strSQL As String
Dim strName As String
Dim oCtl As Object
Dim dsCustIndiv As New DataSet()
Dim odaCustIndiv As OleDb.OleDbDataAdapter
Dim drCustIndiv As DataRow
Try
'-- Load the individual record into the dataset
strSQL = "Select * from Customers Where CustomerID = '" &
strKeyValue & "'"
odaCustIndiv = New OleDb.OleDbDataAdapter(strSQL, _
BuildCnnStr("(local)", "Northwind"))
'-- Fill the dataset
odaCustIndiv.Fill(dsCustIndiv, "Customers")
'-- Grab the individual data row
drCustIndiv = dsCustIndiv.Tables("Customers").Rows(0)
Catch oexpData As OleDb.OleDbException
MessageBox.Show("Error loading individual data: " & _
oexpData.Message)
Exit Sub
End Try
'-- Run through the text boxes on the form.
'-- If they match up with a field from the record, load them.
For Each oCtl In Me.Controls
If TypeOf oCtl Is TextBox Then
strName = Mid(oCtl.Name, 4)
'-- By trapping the exception this way, errors are ignored.
Try
oCtl.text = drCustIndiv(strName).ToString
Catch oexp As Exception
End Try
End If
Next
End Sub
Create the next Windows Form and call it whatever name you referred to in the search form in step 2. Then place the controls shown in Figure 8.7 of the search form, with the properties set as in Table 8.6.
Table 8.6. Label, TextBox, DataGrid, and Command Button Controls Property Settings for the Calling Form
|
GroupBox
|
Name
|
GroupBox1
|
|
Text
|
Click on a Letter
|
Button
|
Name
|
btnA
|
|
Caption
|
A
|
Button
|
Name
|
btnB
|
|
Caption
|
B
|
Button
|
Name
|
btnC
|
|
Caption
|
C
|
...
|
|
|
Button
|
Name
|
btnZ
|
|
Caption
|
Z
|
Button
|
Name
|
btnAll
|
|
Caption
|
All
|
DataGrid
|
Name
|
dgSearch
|
Button
|
Name
|
btnAccept
|
|
Caption
|
&Accept
|
Button
|
Name
|
btnCancel
|
|
Caption
|
&Cancel
|
Create the custom properties discussed in the "Technique" section of this How-To and found in Listing 8.19. Each of the properties is self-explanatory.
Listing 8.19 frmHowTo8_4b.vb: Creating Custom Properties for the Search Form
Private mstrDisplayName As String
Private mstrRecordSource As String
Private mstrSearchField As String
Private moResultValue As Object
Private mstrKeyField As String
Public Property DisplayName() As String
Get
DisplayName = mstrDisplayName
End Get
Set(ByVal Value As String)
mstrDisplayName = Value
End Set
End Property
Public Property SearchRecordSource() As String
Get
SearchRecordSource = mstrRecordSource
End Get
Set(ByVal Value As String)
mstrRecordSource = Value
End Set
End Property
Public Property SearchField() As String
Get
SearchField = mstrSearchField
End Get
Set(ByVal Value As String)
mstrSearchField = Value
End Set
End Property
Public Property KeyField() As String
Get
KeyField = mstrKeyField
End Get
Set(ByVal Value As String)
mstrKeyField = Value
End Set
End Property
Public Property ResultValue() As Object
Get
ResultValue = moResultValue
End Get
Set(ByVal Value As Object)
moResultValue = Value
End Set
End Property
On the form, add the code in Listing 8.20 to the Load event. This routine ensures that the calling form set the DisplayName custom property; thus, this routine can assume that the others were set as well. If not, a message box is displayed. If so, the Text property of the form, which is displayed in the Title bar, is set to DisplayName.
Listing 8.20 frmHowTo8_4b.vb: Executing a SQL Server-Supplied Stored Procedure That Lists the Tables in the Database
Private Sub frmHowTo8_4b_Load(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles MyBase.Load
If Len(Me.DisplayName) = 0 Then
MessageBox.Show("Form specific properties not set.")
Me.Close()
Else
Me.Text = Me.Text & " " & Me.DisplayName
End If
End Sub
For each of the command buttons that has a single letter, add the first subroutine displayed in Listing 8.21 to each of the Click events. For the btnAll Button control, add the second subroutine to the Click event. Each Button control will pass the letter it represents to the subroutine called SetData, discussed in the next step. The btnAll code simply passes the empty string.
Listing 8.21 frmHowTo8_4b.vb: Click Events for Each of the Letter Button Controls
Private Sub btnA_Click(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles btnA.Click
SetData("A")
End Sub
Private Sub btnAll_Click(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles btnAll.Click
SetData("")
End Sub
Add the subroutine in Listing 8.22 to the class module of the form. This routine takes the letter value passed in strFilterLetter as a parameter. A SQL Select string is created that takes the literal values Select, From, and Where and uses the custom properties KeyField, SearchField, and SearchRecordSource. The SearchField property is used with the Like clause, also using the strFilterLetter and the % (wildcard). Note that if "" is passed to strFilterLetter, all the records will be listed. Finally, odtSearch is filled and set as the data source for dgSearch, which is the DataGrid control.
Listing 8.22 frmHowTo8_4a.vb: Filling the Results Set Based on the Letter Button That Was Pressed
Sub SetData(ByVal strFilterLetter As String)
Dim odaSearch As OleDb.OleDbDataAdapter
Dim dtSearch As DataTable = New DataTable()
odaSearch = New _
OleDb.OleDbDataAdapter("Select " & Me.KeyField & ", " &
Me.SearchField & " From " & Me.SearchRecordSource & " Where " &
Me.SearchField & " Like '" & strFilterLetter & "%'",
(BuildCnnStr("(local)", "Northwind")))
odaSearch.Fill(dtSearch)
dgSearch.DataSource = dtSearch
End Sub
Note
|
This routine more than any in this How-To shows the flexibility of this technique. You can use any table values for these properties. Just think of how many places you can use this form without changing a line of code in the form.
|
On the buttons called btnAccept and btnCancel, add the code in Listing 8.23 to the appropriate Click event of each. The btnAccept_Click routine creates a DataTable object from the data grid's DataSource property. Then it derives the data row that is currently selected from that data table. The KeyField property is used to store the individual column value of drCurr into the ResultValue custom property. The DialogResult property is set to OK, and the form is hidden with the Hide method. By hiding the form, you can still read the properties of the form without the user seeing it.
In the btnCancel_Click routine, the DialogResult is set to No, and the form is closed. This action tells the calling form that the search was canceled.
Listing 8.23 frmHowTo8_4b.vb: Storing the Resulting Key Value to the ResultValue Custom Property and Setting the DialogResult
Private Sub btnAccept_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles btnAccept.Click
Dim dtFromGrid As DataTable
Dim drCurr As DataRow
Try
'-- Using the DataRow and DataTable objects of the DataGrid control,
' get the selected result and assign it to the custom property
' ResultValue. Then set the DialogResult
' property to DialogResult.OK,
' and hide the form so that the calling form can still access it.
dtFromGrid = CType(dgSearch.DataSource, DataTable)
drCurr = dtFromGrid.Rows(Me.dgSearch.CurrentRowIndex())
Me.ResultValue = drCurr(Me.KeyField).ToString
Me.DialogResult = DialogResult.OK
Me.Hide()
Catch exp As Exception
Me.DialogResult = DialogResult.No
Me.Close()
End Try
End Sub
Private Sub btnCancel_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles btnCancel.Click
Me.DialogResult = DialogResult.Cancel
Me.Close()
End Sub
How It Works
When the user clicks on the search button, the calling form sets the custom properties of the search form, telling it what record source to use, as well as other information used for searching for the specific record and domain desired�in this case:
Customers for the property DisplayName
Customers for the property SearchRecordSource
CustomerID for the property KeyField
CompanyName for the property SearchField
After the search form is loaded, the user presses one of the letters to narrow down the records to look for, a data adapter is passed a SQL String made up of the properties just mentioned, a data table is filled, and the data grid's DataSource property sets the data table.
When the user clicks Accept, the data table is retrieved from the data grid, which then produces the data row that contains the key field. This is stored into the ResultValue property of the search form, the DialogResult property is set to DialogResult.OK, and the form is hidden.
Back on the calling form, the LoadIndividual routine is called and passed the ResultValue property from the search form. The text boxes are then loaded with the data row results.
Comments
This technique shows a number of ways that the various ADO.NET objects can be used. Take a close look at the use of the dialog style form, forcing code execution to halt until you hide or close the form. This is a technique that you will use throughout your applications after you get used to it.
Again, you can enhance this tool in a number of ways. One way is to allow the user to enter a string value to type in, narrowing down the choices even more, and another is to add a property that could be used to specify multiple columns to be displayed.
|
No comments:
Post a Comment