Free!  FoxPro to WPF screen converter
   Almost free!  One day of FREE VFP to WPF evaluation of your FoxPro app ONSITE
   Free!  Read the foreword to Les' new book (available here)...
Skip Navigation Links

Add/Edit/Delete forms in .NET

Microsoft's version of the simplest things we do in FoxPro


Les Pinter

Login or register to download source code

Introduction

The simplest screen in FoxPro is called a CRUD app (for CReate, Update, and Delete.) I don't like the name, but if you can't do them, you can't be a database programmer.

The FoxPro baseline case:

In FoxPro, such a screen might look like Fig. 1:



Fig. 1 - A simple Add, Edit and Delete form in FoxPro

The code is incredibly simple. The only slightly complicated code is to support a Filtering container, which is hard-coded to the columns in the Northwind Customers table. Not counting this code, the form has 46 lines of code. Filtering approximately doubles that, and most of the code is there because the displayed column names are slightly different from the internal column and tag names.

In the form's DataEnvironment, I've added the Customers table. I've set the table's BufferMode to 3 (Optimistic record locking), and selected CustomerName as the initial index. The column names have been truncated to 10 characters; that's a limitation of FoxPro in the case of free tables that are not part of a Database Container. The table's columns are bound to TextBoxes on the form by means of the ControlSource property of the TextBox.

The code

The Init (the constructor) of the form sets some standard FoxPro settings, then calls EnableAll(.F.), which sets the Enabled property of the Textboxes on the form to .F. EnableAll() calls Buttons(), which enables all of the CommandButtons except Save and Cancel, which are only enabled during adding and editing: Note the clever reuse of the negation of the boolean parameter that's passed to EnableAll(). The form has a single property called BeforeAdd which holds the RECNO() of the current record immediately prior to an Add or Edit.

Listing 1 - Code for simple Add/Edit/Delete form in FoxPro

PROCEDURE Init
  SET STRICTDATE TO 0
  SET CONFIRM ON
  SET DELETED ON
  SET EXACT OFF
  SET NEAR OFF
  SET TALK OFF
  SET SAFETY OFF
  THISFORM.EnableAll( .F. )
ENDPROC

PROCEDURE EnableAll
  LPARAMETERS OnOff
  THISFORM.SetAll  ( "Enabled", OnOff, "Textbox" )
  THISFORM.Buttons ( Not OnOff )
ENDPROC

PROCEDURE Buttons
  LPARAMETERS OnOff
  THISFORM.SetAll( "Enabled",      OnOff, "Commandbutton" )
  THISFORM.cmdSave.Enabled   = Not OnOff
  THISFORM.cmdCancel.Enabled = Not OnOff
ENDPROC

The Tab Order of the form is this: Add, Edit, Delete buttons, then the textboxes, then the rest of the commandbuttons. Initially, all commandbuttons are enabled except Save and Cancel. Notice how simple the code is for adding, editing, deleting, saving and canceling, and for record movement:

PROCEDURE cmdAdd.Click
  THISFORM.BeforeAdd = RECNO()
  APPEND BLANK
  THISFORM.EnableAll(.T.)
  THISFORM.Refresh
ENDPROC

PROCEDURE cmdEdit.Click
  THISFORM.BeforeAdd = RECNO()
  THISFORM.EnableAll( .T. )
ENDPROC

PROCEDURE cmdDelete.Click
  DELETE NEXT 1
  GO TOP
  THISFORM.Refresh
ENDPROC

PROCEDURE cmdPrevious.Click
  IF NOT BOF()
     SKIP -1
     IF BOF()
      GO TOP
     ENDIF
     THISFORM.Refresh
  ENDIF
ENDPROC

PROCEDURE cmdNext.Click
  IF NOT EOF()
     SKIP
     IF EOF()
      GO BOTTOM
     ENDIF
     THISFORM.Refresh
  ENDIF
ENDPROC


PROCEDURE cmdClose.Click
  THISFORM.Release
ENDPROC

PROCEDURE cmdSave.Click
  TABLEUPDATE(.T.)
  THISFORM.EnableAll(.F.)
ENDPROC

PROCEDURE cmdCancel.Click
  TABLEREVERT(.T.)
  * If adding, return to the record they were on before the APPEND BLANK
  IF RECNO() <> THISFORM.BeforeAdd AND THISFORM.BeforeAdd <> 0
     GO ( THISFORM.BeforeAdd )
  ENDIF
  THISFORM.EnableAll(.F.)
  THISFORM.Refresh
ENDPROC

The only routine with any code to speak of deals with letting users filter records on the contents of various columns, and most of it is there because the displayed name of the column may not be the same as the column's actual name - e.g., region instead of state. I also decided to do a little frippery and display the current filter and how many records matched; it displays briefly inside the Filter container, then permanently on a pair of labels under the container, which are revealed when the container's Visible property is set to false:

PROCEDURE cmdFilter.Click
  COUNT TO HowMany
  THISFORM.Container1.Visible = .T.
  THISFORM.Container1.lblReccount.Caption = TRANSFORM(HowMany) + " matches"
  THISFORM.Container1.SetFocus
ENDPROC

* This is used to enable the Textbox in the Filter container,
* which was disabled by the EnableAll routine. It's a little opaque, but it works.

PROCEDURE Container1.GotFocus
  This.Text1.Enabled= .T.
  This.Combo1.Selected(1) = .T.
  This.Combo1.SetFocus()
ENDPROC

The two buttons are Apply and Cancel; Apply builds and executes the SET FILTER string; Clear clears the filter and exposes all of the records in the table:

PROCEDURE cmdApplyFilter.Click
  ColName = ""
  DO CASE
     CASE This.Parent.Combo1.Value = [Company Name]
      ColName = [UPPER(CompanyNam)]
     CASE This.Parent.Combo1.Value = [Contact Name]
      ColName = [UPPER(ContactNam)]
     CASE This.Parent.Combo1.Value = [Contact Title]
      ColName = [UPPER(ContactTit)]
     CASE This.Parent.Combo1.Value = [Address]
      ColName = [UPPER(Address)]
     CASE This.Parent.Combo1.Value = [City]
      ColName = [UPPER(City)]
     CASE This.Parent.Combo1.Value = [State]
      ColName = [UPPER(Region)]
     CASE This.Parent.Combo1.Value = [ZIP]
      ColName = [UPPER(PostalCode)]
     CASE This.Parent.Combo1.Value = [Phone]
      ColName = [UPPER(Phone)]
  ENDCASE
  Expr = "["+ UPPER(ALLTRIM(This.Parent.Text1.Value)) + "] $ " + ColName
  Cmd = "SET FILTER TO " + Expr
  &Cmd
  Thisform.lblfilter.Caption = IIF (LEN(ALLTRIM(this.parent.text1.value)) = 0, "None", Expr)
  COUNT TO HowMany
  GO TOP
  This.Parent.lblReccount.Caption = TRANSFORM(HowMany) + " matches"
  THISFORM.Refresh
  WAIT WINDOW "" TIMEOUT 1
  This.Parent.Visible = .F.
ENDPROC

PROCEDURE cmdClear.Click
  SET FILTER TO
  SET DELETED ON
  This.Parent.Text1.Value = ""
  COUNT TO HowMany
  This.Parent.lblReccount.Caption = TRANSFORM(RECCOUNT()) + " matches"
  ThisForm.lblFilter.Caption = "None"
  GO TOP
  WAIT WINDOW "" TIMEOUT 1
  This.Parent.Visible = .F.
ENDPROC

Finally, the setting of an index in FoxPro doesn't require retrieving the data again; just SET ORDER TO one of the index tags. Again, the display name may not match the TAG name, so I used a modal form called SetIndex to display friendly tag names, then return the actual tag name: <#Index>

PROCEDURE cmdIndex.Click
  DO FORM SetIndex TO IndexName
  IF NOT EMPTY ( IndexName )
     SET ORDER TO &IndexName.
     GO TOP
  ENDIF
ENDPROC

Nearly half of this code is needed due to the fact that column and index tag names are limited to 10 characters, or to the need to display State and ZIP instead of PostalCode and Region, the ecumenical names used by MicroSoft in the NorthWind Customers table used in this example.

The SetIndex form (Fig. 2) is a modal form, which means that its WindowType property is set to 2 - Modal. It returns the selected value, which is derived from (but not spelled the same as) the display name for the index tag (see the code following the screen shot).



Fig. 2 - Selecting an index tag

Code for the SetIndex modal form:

* cmdSelect.Click:
WITH THISFORM
DO CASE
   CASE .Combo1.Value  = [Company Name]
    .SelectedValue = [CompNam]
   CASE .Combo1.Value  = [Contact Name]
    .SelectedValue = [ContName]
   CASE .Combo1.Value  = [Contact Title]
    .SelectedValue = [ContTitle]
   CASE .Combo1.Value  = [City]
    .SelectedValue = [City]
   CASE .Combo1.Value  = [State]
    .SelectedValue = [Region]
   CASE .Combo1.Value  = [ZIP]
    .SelectedValue = [PostalCode]
ENDCASE
.Release
ENDWITH

* cmdCancel.Click:
THISFORM.SelectedValue = []
THISFORM.Release

* setIndexForm.Unload:
RETURN THISFORM.SelectedValue

(See the cmdIndex Click event code above to recall how this is used.)

That's quite a bit of functionality for a small amount of code. And that's why I love FoxPro. Now, let's see what it takes to do the same application in .NET - first VB, then C#.

The .NET Equivalent

To start this process, open Visual Studio, Use File, New to create a new project, then select Visual Basic, Windows Forms Application. Change the name of Form1.vb to frmCustomers.vb (Fig. 3).



Fig. 3 - Starting a new Windows Forms project

If you open the Properties Sheet for the project, you'll see that the startup form name has been changed to frmCustomers. Also, if you open the code-behind for the form (double-click on the form, or press F7, you'll see that the class name for the form was changed when you renamed it. That's an upgrade from VS2005, when it didn't. Do these guys ever use the software that they write?

Adding a Typed Dataset

The next step is to open the Data Sources window (if not already open), then click on Add New Data Source. This opens up the dialog in Fig. 4:



Fig. 4 - Adding a New Data Source

Our data source is going to be, of course, the Customers table in the Northwind database. Click on Next to open the Data Link Properties dialog. Actually, it's a different version of the Data Link Properties dialog, but it's the same steps, if you remember our previous article on data access. Select Northwind, Trusted Connection, and the NorthWind database, and click next to save the connection string with the name ConnStr(Fig. 5):



Fig. 5 - Saving the connection string

Expand the treeview and select the Customers table, then click Finish to save the Typed Dataset; the generated default name NORTHWINDDataSet will do.



Fig. 6 - Saving the generated Typed Dataset

What you will now have is a Data Sources pane with a treeview of the NORTHWINDDataSet, which has also been added to the project (Fig. 7).



Fig. 7 - Data Source Added

The little arrow to the right of the Customers table reference in the Data Sources window is what's important now. Click on the down-arrow, and select Details. This sets up the Code Generation Wizard to build your Add-Edit-Delete form.



Fig. 8 - Select Details from the drop-down beside the Customers table name

Now, drag-and-drop the word Customers to the form's design surface. The result is shown in Fig. 9:



Fig. 9 - The generated Add/Edit/Delete form

Press F5 to run the form and see how much of its functionality has been generated without writing a single line of code!

So what's that stuff at the bottom of the form?

At the bottom of Fig. 5 you saw five named items. These are components, which you could add from the toolbox. If a toolbox item isn't a visible component, it goes in the tray at the bottom (similar to the way FoxPro places OCX components on the form but doesn't display then when the form runs). The Wizard added them. They are:

  • NORTHWINDDataset
  • CustomersBindingSource
  • CustomersBindingNavigator
  • CustomersTableAdapter
  • TableAdapterManager

So what are they? The CUSTOMERSDataset is used to hold records returned by the CustomersTableAdapter; the TableAdapterManager calls the Load event of the TableAdapter; the BindingSource handles databinding and record pointer movement; and the CustomersBindingNavigator is the component at the top of the form that manages table operations. The interaction of these components and their properties and methods provide the equivalent of FoxPro's native data commands - SKIP, APPEND, DELETE and others. Without them, none of these commands have any counterpart in a form.

This is why FoxPro developers find it so difficult to move to .NET; there is no one-to-one equvalent for any of the data-related operators. And if you try to look for them, you absolutely, positively won't find them, because you won't know where to look, and you'll never guess where they are. That's why we wrote these articles.

Polishing the generated form

There are a few shortcomings in this generated form. Unlike FoxPro, The VS IDE makes no attempt to size the textboxes based on their size in the source table. We'll fix that later.

The only generated code you'll see in the form's CodeBehind is for the Load and Save events:

  Private Sub CustomersBindingNavigatorSaveItem_Click( _
   ByVal sender As System.Object, ByVal e As System.EventArgs) _
   Handles CustomersBindingNavigatorSaveItem.Click

    Validate()
    CustomersBindingSource.EndEdit()
    TableAdapterManager.UpdateAll(NORTHWINDDataSet)

  End Sub

  Private Sub frmCustomers_Load( _
   ByVal sender As System.Object, ByVal e As System.EventArgs) _
   Handles MyBase.Load

    CustomersTableAdapter.Fill(NORTHWINDDataSet.Customers)

  End Sub

However, if you click on the Navigation Bar Add commandbutton (the plus), you can add a record. Be sure to click the Save button (the little diskette) to save it. And if you try to delete a record, be aware that the SQLDataAdapter knows about the relationship between the Customers and Orders table, and won't let you delete any customer who has orders - which is all of them. However, if you just added a new customer, you can delete that customer without any problems. Again, be sure to save before exiting, or all you did was delete the customer from the in-memory dataset, not from the underlying SQL Table.

That's a very, very important point. If you look at the Delete button code in frmCustomers.Designer.vb, you'll notice (after digging for what may be a long time) that all it does is call the BindingManager's Remove method. What that gives you is a record in the datatable that has a "before" record, but no "after" record. That's how the Update method knows to delete it from SQL.

When you return a datatable, it's stored in memory in something like an XML format. Every record has a "before" and and "after" state. When you call the dataAdapter's Update method, what happens next is determined by the state of each record's before and after status:

  • If it has a before and an after record that aren't identical, it's been edited;
  • If it has an after and no before record, it's been added; and
  • If it has a before and no after record, it's been deleted.
In fact, if you don't tell it otherwise, the Update method of the DataAdapter looks at every pair of before/after records to see what to do. You'll generally create a reduced set of records for updating, like this:
If ds.HasChanges() then
   Dim dc as dataset = ds.GetChanges()
   da.Update(dc)
End If

Adding Filtering

If you click to the right of the commandbuttons on the DataNavigator, a dropdown will ask you what kind of control you want to add. This is Microsoft's version of the toolbar, and it's like FoxPro's toolbar on steroids. Pick CommandButton, name it btnFilter, assign whatever 16 X 16-bit image you've found and copied to the project directory, and double-click it to open an event handler for it's click method. FoxPro has a separate code snippet window for each method; in VB and C#, they're given generated names related to the object you double-clicked. In VB, a Handles <ObjectName>.Click clause is added to tell VB when to call it. In C#, an AddHandler command in the generated code creates the association with the appropriate event.

Here's the beautified Customers form with the GroupBox that I added to contain the Filtering stuff:



Fig. 10 - The Customers form with the Filter groupbox

The code for the two buttons in this groupbox is quite similar to the FoxPro example; except for the application of the filter: In FoxPro, it's SET FILTER TO '<string>' $ <ColumnName>; in VB, it sets a rowfilter on a DataView, which is derived from the Customers datatable, like this:

dv.RowFilter = ComboBox1.Text.TrimEnd + " LIKE '%" + TextBox1.Text.TrimEnd & "%'"

Note that I used modeless editing, which is preferred by Microsoft and their running dog lackies. It's funny how this has become almost an article of faith. Database programmers divide into those who Enable and disable textboxes when toggling between adding/editing and cursor movement, and those who think that those who enable and disable textboxes are morons. I don't know the reason for this disrespect. It's actually quite similar to how Republicans and Democrats look at each other, and it's equally sad,

The BindingNavigator obviates the necessity for a label to display record counts while filtering, and the associated code. It also renders the Next/Previous navigation code unnecessary.

Here's the complete code for the application:

VB:

Public Class frmCustomers

  Dim dv As DataView

  Private Sub CustomersBindingNavigatorSaveItem_Click( _
   ByVal sender As System.Object, ByVal e As System.EventArgs) _
   Handles CustomersBindingNavigatorSaveItem.Click

    Validate()
    CustomersBindingSource.EndEdit()
    TableAdapterManager.UpdateAll(NORTHWINDDataSet)

  End Sub

  Private Sub frmCustomers_Load( _
   ByVal sender As System.Object, ByVal e As System.EventArgs) _
   Handles MyBase.Load

    CustomersTableAdapter.Fill(NORTHWINDDataSet.Customers)

  End Sub

  Private Sub ToolStripButton1_Click( _
   ByVal sender As System.Object, ByVal e As System.EventArgs) _
   Handles ToolStripButton1.Click

    FilterGroupBox.Visible = True

  End Sub

  Private Sub btnApply_Click( _
   ByVal sender As System.Object, ByVal e As System.EventArgs) _
   Handles btnApply.Click

    dv = NORTHWINDDataSet.Customers.DefaultView
    dv.RowFilter = ComboBox1.Text.TrimEnd + " LIKE '%" + TextBox1.Text.TrimEnd & "%'"
    CustomersBindingSource.DataSource = dv
    CustomersBindingSource.Position = 0
    FilterGroupBox.Visible = False

  End Sub

  Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.Click

    dv = NORTHWINDDataSet.Customers.DefaultView
    dv.RowFilter = ""
    CustomersBindingSource.DataSource = dv
    CustomersBindingSource.Position = 0
    FilterGroupBox.Visible = False

  End Sub

End Class

C#:

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Drawing;
using System.Diagnostics;
using System.Windows.Forms;
using System.Linq;
using System.Xml.Linq;

namespace WizardGeneratedApp
{
	public partial class frmCustomers
	{
		internal frmCustomers()
		{
			InitializeComponent();
		}
	  private DataView dv;

	  private void CustomersBindingNavigatorSaveItem_Click(object sender, System.EventArgs e)
	  {
		Validate();
		CustomersBindingSource.EndEdit();
		TableAdapterManager.UpdateAll(NORTHWINDDataSet);
	  }

	  private void frmCustomers_Load(object sender, System.EventArgs e)
	  {
		CustomersTableAdapter.Fill(NORTHWINDDataSet.Customers);
	  }

	  private void ToolStripButton1_Click(object sender, System.EventArgs e)
	  {
		FilterGroupBox.Visible = true;
	  }

	  private void btnApply_Click(object sender, System.EventArgs e)
	  {
		dv = NORTHWINDDataSet.Customers.DefaultView;
		dv.RowFilter = ComboBox1.Text.TrimEnd() + " LIKE '%" + TextBox1.Text.TrimEnd() + "%'";
		CustomersBindingSource.DataSource = dv;
		CustomersBindingSource.Position = 0;
		FilterGroupBox.Visible = false;
	  }

	  private void btnCancel_Click(object sender, System.EventArgs e)
	  {
		dv = NORTHWINDDataSet.Customers.DefaultView;
		dv.RowFilter = "";
		CustomersBindingSource.DataSource = dv;
		CustomersBindingSource.Position = 0;
		FilterGroupBox.Visible = false;
	  }
	}
}

Adding custom code

If you open the Typed Dataset that the Wizard generated for this application, you'll find 1,988 lines of code (2,314 in C#). That's just to display the contents of columns and support databinding! If you had to write the code yourself, your career would be over. But you rarely do; the code generation that happens behind the scenes in .NET is not for public consumption.

The problem occurs when you have to add something yourself. I like to let the user press ESC to close a form. We can use the KeyPress event to do this.

In VB, open the Code Window, select frmCustomer Events from the top left combobox, then pick KeyPress from the right side combobox list of available events, and add this line of code to the resulting generated event handler:

If Asc(e.KeyChar) = 27 Then Close()

In C#, it's done differently. With the Form design surface open, press F4 to open the Properties Sheet, then click on the little lightning bolt to display available events, and double-click on KeyPress. This adds an AddHandler call in the code-behind, and a stub for a frmCustomers_KeyPress method. Add the following code:

if (e.KeyChar == (char)Keys.Escape) { Close(); }

(Note that in both VB and C#, the form's KeyPress event must be set to True.)

Conclusion

The point of this article isn't that .NET takes less code. In fact, it's actually a lot more code; you just don't have to write it. Besides, the Gallery and FoxPro Foundation Classes have navigation and filtering components and more, and if you availed yourself of all of those mechanisms, you could achieve a similarly small number of lines of code in VFP. The point is rather that if you use what's there, .NET is a very productive programming environment, in which you can do everything that you can do in FoxPro. But you'd better plan to get up close and personal with some code generation tools. This series of articles will introduce you to a number of my favorites.

But we can't ever forget that retrieving the entire contents of a table isn't always practical. In the next article in this series, we'll do everything that this form does using SQL pass-through to minimize data traffic.

qqq


Copyright(C) Pinter Consulting, 2012Tel: +1 (650) 464-6924
Automated conversion between C# and VB by Visible C#/Visible VB from Tangible Software Solutions