Data Binding

Data binding is one of the best things in FoxPro. Curiously, it's one of the worst things in Visual Basic .NET. Let's look at them and see what can be done to improve the situation while Microsoft is figuring out how to do data binding more simply in a forthcoming version of Visual Studio .NET.

Data Binding in FoxPro

When you build a screen in FoxPro, you can simply drag the base controls onto a form, and then assign as the ControlSource property the name of a field in a table or cursor, for example, Clients.Name . Thereafter, THISFORM.Refresh will get the current record's value for the Name field, assign it to the Value property of the control, and display it. Nothing could be simpler.

If you subclass the FoxPro form controls, then open the Tools, Options, Field Mapping page and map the data types used in your tables to your subclassed controls, you're in for a real treat. Open a form, open its Data Environment, add a table, and then drag the word Fields to the upper-left corner of the form and let go, and the screen will be autopopulated with all of the fields in the table or cursor, including a label to the left of each using the name of the field. If your table or view is part of a FoxPro Database Container and you've specified additional information, such as the exact spelling of the label caption, any picture clauses, and a few other things, they'll all be used. It's spectacular!

Finally, if you use a FoxPro 8 CursorAdapter to create the cursor, the binding goes all the way from the table or cursor, or from SQL Server, or from an XML Web Service to your screen controls and back again. It's ridiculously easy.

Data Binding in Visual Basic .NET

That last sentence applies to Visual Basic .NET ”if you remove the word easy . How they could have missed the mark so badly is hard to imagine. They could have walked over to the FoxPro division and asked Randy or Calvin how it's done, but noooooo So let's see what's required to bind data in Visual Basic .NET, and try to make it better.

Manually Binding Data

To manually bind the base form controls, add a SQLDataAdapter from the Pubs database, and then generate a dataset named dsEmps for the Employee table to the form. Use SELECT * FROM Employee to return all records. (There are only 10 in the sample data.)

Next , open the Properties sheet of each control, expand the Databindings property by clicking on the plus sign, and then open the Text drop-down. You'll see the name of the dataset with a plus sign to the left of the name. Click on it to expand it, and you'll see Employee, the only table contained in the dataset. Expand it in turn , and you can select fname , the employee's first name. Select it, then write the following line of code in the form's Load event:

 SqlDataAdapter1.Fill(DsEmps) 

Press F5 to run the form and verify that it loads and displays a record. The first name of our good friend Paolo Accorti should appear.

Using Generic Code to Bind Controls to Data Columns

If you want to write inheritable forms that bind the data automatically, you have two choices: typed datasets and untyped datasets . A typed dataset is a Visual Basic (or C#) class that has one property procedure for each of the columns in your table. In that way, when you instantiate an object called Customers based on your CustomerRow class, Customers.CustomerID references a property in the class.

You can get a typed dataset by building a DataAdapter for the form, then right-clicking on it and selecting Generate Dataset, or by selecting Generate Dataset from the Data menu when a form is open and a DataAdapter has been added to the form.

However, you might want to use form properties to specify a connection string, a table name and a SELECT statement, and let the inheritable form's generic code do the work for you. The issue is in the DataBindings.Add method. If you use a typed dataset, it takes a dataset as the second parameter and TableName.FieldName as the third parameter; if you use an untyped dataset, it takes a DataTable as the second parameter and a string variable containing the Column name as the third parameter.

If you name the controls on your form with a three-character prefix denoting the control type, and use the name of a table column as the rest of the control name, you can simply iterate through the form controls, make sure they're data controls, and then call the appropriate databinding.Add() method.

Listing 8.8 shows how to do databinding with an untyped dataset.

Listing 8.8. Automatic Data Binding with an Untyped Dataset

 Private Sub Form1_Load( _    ByVal sender As System.Object, _    ByVal e As System.EventArgs) _   Handles MyBase.Load    If Not Me.DesignMode Then        If ConnStr = "" Then       MsgBox("Connection string is empty", MsgBoxStyle.Critical)        Else       CN.ConnectionString = ConnStr       CN.Open()        End If        If CN.State = ConnectionState.Open Then       DataAccess = DATypes.SQL       Dim sc As New SqlClient.SqlCommand       sc.CommandText = SelectCmd       sc.Connection = CN       DA.SelectCommand = sc       DA.Fill(DS, MainTable)       BindInputFields()        Else       MsgBox("Could not open data source" + vbCrLf + ConnStr, _         MsgBoxStyle.Critical)        End If    End If End Sub Public Sub BindInputFields()    Dim Ctrl As Control    Dim FName As String    Dim DT As DataTable    DT = DS.Tables(0)    For Each Ctrl In Controls        LastPeriod = Ctrl.GetType.ToString.ToUpper.LastIndexOf(".") + 1        CtrlType = Ctrl.GetType.ToString.ToUpper.Substring(LastPeriod)        FName = Ctrl.Name.Substring(3)        If UserControls.IndexOf(CtrlType) > 0 _           Then Ctrl.DataBindings.Add("Text", DT, FName)    Next End Sub 

If you're using a typed dataset, the BindInputFields() method code is slightly different:

 Public Sub BindInputFields()    Dim Ctrl As Control    Dim FName As String    For Each Ctrl In Controls        LastPeriod = Ctrl.GetType.ToString.ToUpper.LastIndexOf(".") + 1        CtrlType = Ctrl.GetType.ToString.ToUpper.Substring(LastPeriod)        FName = Ctrl.Name.Substring(3)        If UserControls.IndexOf(CtrlType) > 0 Then _           Ctrl.DataBindings.Add("Text", DS, TableNAme + "." + FName)    Next End Sub 

Introducing the BindingContext

This is a good time to introduce something really different. In FoxPro, to move to the next record, you use SKIP . SKIP issued on the last record in the current index order (or on the last record if no index is attached) moves to the phantom record, at which time SKIP will give an error.

The same is true for moving backwards with SKIP -1 . The FoxPro environment handles many, many things about records in the current work area, including moving, buffering, application of global settings, and much more.

There are no environmental settings or methods for data in Visual Basic .NET, and it's a shame. Everything about a record is handled by its dataset, or by its data adapter. Everything, that is, except movement within the dataset. That's handled by something called the BindingContext , which is available automatically within a form.

A BindingContext reference uses a single parameter describing the data container in question (for example, Me.BindingContext(dsEmps). whatever ). The record number within the dataset is both gettable and settable via the Position property, so to move to the first record in the collection would be accomplished by the following code:

 Me.BindingContext(dsEmps).Position = 0 

and SKIP would be

 Me.BindingContext(dsEmps).Position = Me.BindingContext(dsEmps).Position + 1 

The best way to demonstrate the differences in data binding and navigation is to write the same application in both languages.

A Simple FoxPro Screen to Demonstrate Binding and Navigation

The screen shown in Figure 8.3 was built in Visual FoxPro 7. it uses the same Employee table found in both the SQL Server and the MSDE Northwind databases. There should also be a copy in your Visual FoxPro Solutions directory.

Figure 8.3. A FoxPro screen with data binding and navigation buttons .

If you don't have the Northwind!Employee table, you can get a copy from MSDE or SQL. To copy the table to a FoxPro DBF, use the code in Listing 8.9.

Listing 8.9. Copying the Northwind!Employee Table from MSDE to a DBF

 h = SQLSTRINGCONNECT( _  "Driver={SQL Server};Server=(local);Database=Northwind;UID=sa;PWD=;") SQLEXEC( h, "SELECT * FROM EMPLOYEE") COPY TO Employee SQLEXEC( h, "SELECT * FROM Orders") COPY TO Orders SQLEXEC( h, "SELECT * FROM OrderDetails") COPY TO OrderDetails SQLDisconnect(h) USE Employees INDEX ON EmployeeID Tag EmployeeID USE Orders INDEX ON OrderID Tag OrderID INDEX ON EmployeeID Tag EmployeeID USE OrderDetails INDEX ON OrderID Tag OrderID 

There are a few properties that I've set in the Employees form (see Listing 8.10).

Listing 8.10. Form Properties

 ShowTips   = .T. AutoCenter = .T. Caption    = "Employees" MaxButton  = .F. MinButton  = .F. Width      = 270 Height     = 360 

In the Data Environment, I open the Employees, Orders, and OrderDetails tables; the Data Environment will automatically close them when the form closes .

In addition, I wrote one line of code each in the Load and Init events of the form, as shown in Listing 8.11.

Listing 8.11. Load and Init event code for the Form

  THISFORM::Load  CREATE CURSOR RelatedOrders ( OrderID Integer, OrderTotal Numeric(10,2) )  Init  event: THISFORM.CalculateOrderTotals 

I added the form method shown in Listing 8.12 to calculate the order subtotal for each order for the current employee.

Listing 8.12. The CalculateOrderTotals Method

  * CalculateOrderTotals  method: SELECT Employees RecNo = RECNO() EmpID = EmployeeID SELECT                                       ;    OrderDetails.OrderID,                     ;    SUM(Quantity*UnitPrice) AS OrderTotal     ;   FROM Orders, OrderDetails                  ;  WHERE Orders.EmployeeID = EmpID             ;    AND Orders.OrderID = OrderDetails.OrderID ;  GROUP BY OrderDetails.OrderID               ;  ORDER BY OrderDetails.OrderID               ;   INTO CURSOR C1 SELECT RelatedOrders ZAP APPEND FROM DBF("C1") USE IN C1 GO TOP SELECT Employees GO ( RecNo ) THISFORM.Refresh 

Most of the remaining code goes in the Click event for the four navigation buttons (see Listing 8.13).

Listing 8.13. Click Event Code for the Buttons

  cmdFirst::Click:  SELECT Employees GO TOP THISFORM.CalculateOrderTotals  cmdPrev  ::Click SELECT Employees SKIP -1 IF BOF()    GO TOP ENDIF THISFORM.CalculateOrderTotals  cmdNext  ::Click SELECT Employees SKIP IF EOF()    GO BOTTOM ENDIF THISFORM.CalculateOrderTotals  cmdLast  ::Click SELECT Employees GO BOTTOM THISFORM.CalculateOrderTotals 

Finally, I'm responsible for disposing of the cursor that I created in the Load event:

  Unload  event: USE IN RelatedOrders 

That is the sum total of the code required to run this form, which calculates the total of all orders for a given employee and displays them in a grid below the employee information.

The Equivalent Form in Visual Basic .NET

The form shown in Figure 8.4 is the equivalent in Visual Basic .NET to the screen that we created in FoxPro in the preceding section.

Figure 8.4. A Visual Basic .NET screen with data binding and navigation buttons.

Use the Windows Forms toolbox to build this form. After adding the labels and text boxes and the grid, add a SQLDataAdapter from the Data toolbox, selecting the Northwind connection previously created. Then, right-click on the DataAdapter and generate a dataset named dsEmployees . That gives it public scope and creates a typed dataset.

To see the typed dataset, use Ctrl+Alt+L to open the Solution Explorer and verify that dsEmployees has been added to the project. Click on the Show All Files icon at the top of the Solution Explorer, and you'll see a plus sign to the left of the dsEmployees.xsd entry; click it to expand it, and you'll see dsEmployees.vb . That's the typed dataset; it's a class that includes property procedures, one for each column in the table. That's what gives us the ability to do the next step, which is data binding.

Select the form and use F4 to open the Properties sheet. Select the first text box. Then expand the (DataBindings) entry, open the drop-down beside the Text entry, and drill down to the EmployeeID column of the Employees table inside the dsEmployees typed dataset. Do likewise for the FirstName , LastName , and HireDate text boxes.

Coding the Form

The code for the Visual Basic .NET version of this form is pretty informative. You need to add a dataset declaration for the employee orders. Put it at the top of the code, before the Windows Form Generated Code, so that it will have Public scope:

 Public dsEmployeeOrders As New DataSet 

Next, add the following code to load the Employees dataset and then load the grid with the first employee's order totals (see Listing 8.14).

Listing 8.14. The Form's Load Event Code

 Private Sub Form1_Load( _   ByVal sender As System.Object, ByVal e As System.EventArgs) _   Handles MyBase.Load   Me.SqlDataAdapter1.Fill(DsEmployees1, "Employees")   LoadGrid() End Sub 

The GetData procedure builds and executes the SQL SELECT , statement to subtotal order detail entries. Note that in SQL Server the Order Details table has a space in the name, and so has to be enclosed in square brackets (see Listing 8.15).

Listing 8.15. The GetData Method

 Public Sub GetData() Dim CmdStr As String = _   "  SELECT " _ + " [Order Details].OrderID," _ + " SUM(Quantity*UnitPrice) AS OrderTotal" _ + " FROM Orders, [Order Details]" _ + " WHERE Orders.EmployeeID = " + txtEmployeeID.Text.ToString _ + " AND Orders.OrderID = [Order Details].OrderID " _ + " GROUP BY [Order Details].OrderID" _ + " ORDER BY [Order Details].OrderID" Dim Da As New SqlDataAdapter Da = New SqlDataAdapter(CmdStr, Me.SqlConnection1.ConnectionString) dsEmployeeOrders.Clear() Try   Da.Fill(dsEmployeeOrders, "Orders")   Catch oEx As Exception   MsgBox("Error loading dataset" + vbCrLf + oEx.Message)   Close() End Try End Sub 

The click events of the four navigation buttons call each of four independent routines. I wrote them that way so that they could also be called elsewhere, as you'll see in Listing 8.16.

Listing 8.16. The Click Event Code for the Four Navigation Buttons

 Private Sub cmdTop_Click( _   ByVal sender As System.Object, ByVal e As System.EventArgs) _   Handles cmdTop.Click    FirstRecord() End Sub Private Sub cmdBottom_Click( _  ByVal sender As System.Object, ByVal e As System.EventArgs) _  Handles  cmdBottom.Click    LastRecord() End Sub Private Sub cmdNext_Click( _   ByVal sender As System.Object, ByVal e As System.EventArgs) _   Handles cmdNext.Click    NextRecord() End Sub Private Sub cmdPrev_Click( _   ByVal sender As System.Object, ByVal e As System.EventArgs) _   Handles cmdPrev.Click    PreviousRecord() End Sub 

The code for the FirstRecord , LastRecord , NextRecord , and PreviousRecord procedures shown in Listing 8.17 will look familiar, although alarmingly verbose. Think of Me.BindingContext(DatasetName,"tablename") as GOTO in FoxPro, or of its Position property as a user -settable RECNO() , and you'll be close. Note that Me.BindingContext(dsEmployees, "Employees").Count is like RECCOUNT() .

Listing 8.17. Code for the Record Positioning Procedures

 Public Sub FirstRecord() Me.BindingContext(DsEmployees1, "Employees").Position = 0 LoadGrid() End Sub Public Sub PreviousRecord() Me.BindingContext(DsEmployees1, "Employees").Position -= 1 LoadGrid() End Sub Public Sub NextRecord() Me.BindingContext(DsEmployees1, "Employees").Position += 1 LoadGrid() End Sub Public Sub LastRecord() Me.BindingContext( _  DsEmployees1, "Employees").Position = _  DsEmployees1.Tables(0).Rows.Count - 1 LoadGrid() End Sub 

Updating the Data Source Tables

The Update button sends changes in the dataset back to SQL. When we built the DataAdapter , unless you clicked on the Advanced Options button and turned off INSERT , UPDATE , and DELETE generation, the appropriate UPDATE command object has already been created and will be executed when the Update method of the SQLDataAdapter is called (see Listing 8.18).

Listing 8.18. The Update Button Click Event Code

 Private Sub cmdUpdate_Click( _   ByVal sender As System.Object, ByVal e As System.EventArgs) _   Handles cmdUpdate.Click Try    Me.BindingContext("Employees").EndCurrentEdit()    SqlDataAdapter1.Update(DsEmployees1)    MessageBox.Show("Database updated.", _       Me.Text, MessageBoxButtons.OK, _       MessageBoxIcon.Information) Catch exp As Exception    MessageBox.Show("Error updating database: " & exp.Message, _       Me.Text, MessageBoxButtons.OK, _       MessageBoxIcon.Error) End Try End Sub 

Adding Functionality with the KeyDown Event

The KeyDown event code shown in Listing 8.19 is a freebie . You can "hook" keystrokes to the Home, End, left-arrow, and right-arrow keys and navigate using them as well as clicking on the command buttons.

Listing 8.19. Hooking the Four Navigation Methods to the Home, End, Left-Arrow, and Right-Arrow Keys in Visual Basic .NET

 Private Sub frmMain_KeyDown( _   ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) _   Handles MyBase.KeyDown    If e.KeyCode = Keys.Right Then NextRecord()    If e.KeyCode = Keys.Left Then PreviousRecord()    If e.KeyCode = Keys.Home Then FirstRecord()    If e.KeyCode = Keys.End Then LastRecord() End Sub 

The FoxPro equivalent is shown in Listing 8.20. (Be sure to change the form's KeyPreview property to .T. .)

Listing 8.20. Hooking the Four Navigation Methods to the Home, End, Left-Arrow, and Right-Arrow Keys in FoxPro

 LPARAMETERS nKeyCode, nShiftAltCtrl DO CASE    CASE nKeyCode =  1  && Home         _Screen.ActiveForm.cmdFirst.Click    CASE nKeyCode =  6  && End         _Screen.ActiveForm.cmdLast.Click    CASE nKeyCode = 19  && LeftArrow         _Screen.ActiveForm.cmdPrev.Click    CASE nKeyCode =  4  && RightArrow         _Screen.ActiveForm.cmdNext.Click ENDCASE 

However, it doesn't quite work right. Unfortunately, in FoxPro when you click on a grid, the table that's bound to it gets the focus, and as a result the arrow key interaction is pretty messy. I tried to use ON KEY LABEL for the same purpose in FoxPro, but with even messier results. The problem is fixable in Visual Visual FoxPro 8 thanks to the new MouseEnter and MouseLeave events, which tell me when I'm over the grid; but the code in this book is generally supposed to work with earlier versions of Visual Visual FoxPro as well ” certainly Visual FoxPro 7 in most cases. So I didn't support the exact same functionality. Anyway, it's worth noting that sometimes , Visual Basic .NET is better than FoxPro.

Loading the Grid

LoadGrid (see Listing 8.21) is where the grid is populated with the calculated subtotals for orders for the selected employee. The GetData part is very similar to its FoxPro counterpart ; but can we talk ?

What is all of this code for configuring the grid? Can't you just lay it out using mouse clicks and the Properties sheet? Not in this version. Microsoft is burning the midnight oil even as I am at this instant, busily fixing this turkey .

Listing 8.21. The LoadGrid Procedure

 Sub LoadGrid() GetData() With grdOrders .CaptionText = "Orders" .DataSource = dsEmployeeOrders .DataMember = "Orders" End With grdOrders.TableStyles.Clear() Dim grdTableStyle1 As New DataGridTableStyle With grdTableStyle1 .MappingName = "EmployeeOrders" End With Dim grdColStyle1 As New DataGridTextBoxColumn With grdColStyle1 .MappingName = "OrderID" .HeaderText = "Order ID" .Width = 75 End With Dim grdColStyle2 As New DataGridTextBoxColumn With grdColStyle2 .MappingName = "SubTotal" .HeaderText = "Sub Total" .Format = "c" .Width = 75 End With grdTableStyle1.GridColumnStyles.AddRange _ (New DataGridColumnStyle() {grdColStyle1, grdColStyle2}) grdOrders.TableStyles.Add(grdTableStyle1) End Sub 

Formatting Input in Visual Basic .NET

You might have noticed that you can type "Howdy" into a date field in Visual Basic .NET. That means that you're going to have to write your own custom handlers for each and every date time field. By the time this book hits the street, that nasty oversight should have been repaired in some form or fashion. In the meantime, or in case they haven't fixed it, you can do it yourself.

Say you have a date field and you want dates fixed both coming from the data table and when entered by the user. The following four lines of code will add references to format handlers for the Parse and Format operations of the field, which occur when loading bound data from a table column and when typed in, respectively. This goes in the Load event of your form (see Listing 8.22).

Listing 8.22. Load Event Code to Manage Data Entry for a Date Field

 Dim dbnHireDate As New Binding("Text", dsEmployeeInfo, "HireDate") AddHandler dbnHireDate.Format, AddressOf DateToString AddHandler dbnHireDate.Parse, AddressOf StringToDate txtHireDate.DataBindings.Add(dbnHireDate) 

The two procedures in Listing 8.23 are referred to by the AddHandler statements shown earlier, so put them somewhere down near the bottom of the form code.

Listing 8.23. Date Formatting Handlers

 Protected Sub  DateToString  (ByVal sender As Object, ByVal e As ConvertEventArgs) e.Value = CType(e.Value, DateTime).ToShortDateString End Sub Protected Sub  StringToDate  (ByVal sender As Object, ByVal e As ConvertEventArgs) Try     e.Value = CDate(e.Value) Catch exp As Exception     MessageBox.Show("Bad date format: " & exp.Message, Me.Text, _                     MessageBoxButtons.OK, MessageBoxIcon.Error) End Try End Sub 

These work like Valid clauses in FoxPro, except that this particular Valid clause is not needed with date fields in FoxPro. It's kind of a pain in the neck to have to do something that was taken care of automatically back in 1982 in the first version of dBase II, but it's just this same code over and over; so copy it to a code snippet and keep it handy.