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 with SQL

When you have lots of records, copying them all to a dataset when you open a form just won't do. Use SQL instead.


Les Pinter

Login or register to download source code

Introduction

In the previous article in this series, we developed a simple Add/Edit/Delete form with data filtering in both FoxPro and .NET. In the .NET version, we used datasets pretty much the same way we use cursors in FoxPro. For large numbers of records, that's not practical.

If you use the FoxPro upsizing wizard, your USE statements will be converted into the equivalent of "SELECT * FROM <TableName>, which will slow your application to a crawl.

However, USE in FoxPro appears to return the entire table, but it really doesn't; it returns only the first record in the table. If an index tag is used, it returns the first record if indexed on the selected tag expression. Finally, a BROWSE returns only the number of records that appear in the grid.

Can we do the same thing in .NET? Of course we can. It just takes a little more work. Okay, a lot more work. For the FoxPro version, refer to the previous article.

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. 1).



Fig. 1 - 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

In this application, we'll create a typed dataset and use it to establish the initial databinding. However, whenever we retrieve data, we'll use our own dataset. (**Figure out why this is needed**)

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. 2:



Fig. 2 - 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. 3):



Fig. 3 - 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. 4 - 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. 5).



Fig. 5 - 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. 6 - Select Details from the drop-down beside the Customers table name

Now, drag-and-drop each of the columns from the Customers table to the form's design surface. The result, substantially cleaned up, is shown in Fig. 7. (Delete the TableAdapter; you won't need it.)



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

I've set the following form properties:

  • StartPosition: CenterScreen
  • FormBorderStyle: Fixed3D
  • MaximizeBox: False
  • MinimizeBox: False
  • Text: C U S T O M E R S

Finally, move and resize the textboxes on the form to improve its appearance.

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

At the bottom of Fig. 7 you see two 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

So what are they? The CUSTOMERSDataset is used to hold records returned by the CustomersTableAdapter, and the BindingSource handles databinding and record pointer movement. The BindingSource, as its name implies, permits automatic movement of data between the control and the underlying column in the current row. FoxPro's ControlSource is similar to the Text property DataBinding in a .NET form.

Adding a Data Access Component

Rather than write the same code to retrieve the connection string, open a SQLConnection, create a SQLDataAdapter or SQLCommand, and call its Fill method, I prefer to write a module containing methods to do all of the things I do with data. Mine is called DataAccessComponent, and it took about 15 minutes to write. You can put one of these in a separate project, compile it as a class library and distribute the resulting .DLL to all members of your development team. Copy it to the project folder, add a reference to it in your project, and you can Include it in your forms and use it as if you had the source code in a module in your project, which is the way we're using it here.

The ConnectionString is stored in a section of the app.config file called ConnectionStrings. You could use a ConfigurationManager method to return it; however, the My object in Visual Studio 2008 provides a better way. Type My., and as soon as you press the period, Intellisense displays the available objects. Choose Settings, and another collection is exposed. Your named Connection string ("ConnStr" in our case) will be one of them. You can read directly from it into the cs field, as we did at the start of the module in the next code listing,

The first seven methods return a dataset using different combinations of parameters. (Note that Next and Previous use SELECT TOP 1 *; when going to the previous record, you have to return the records in DESCending order). In each case, we create a SELECT string, then pass the string and the connection string to the SQLDataAdapter's constructor. In each of these, I have a Delim parameter, which I use to pass in a single quote if the table's primary key is a character field, and a blank otherwise.

Data AccessComponent

Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.ComponentModel  ' Needed to expose the <Description("")> attribute

Module DataAccessComponent

  Dim cs As String = My.Settings.Connstr

  Dim da As SqlDataAdapter
  Dim ds As New DataSet
  Dim ss As String
  Dim cmd As SqlCommand
  Dim cn As SqlConnection

GetOne returns one record based on the table name, the key column name and a value.

  <Description("Returns one record from the named table using the supplied key name and value")> _
      Public Function GetOne( _
      ByVal TableName As String, _
      ByVal KeyName As String, _
      ByVal KeyValue As String, _
      Optional ByVal Delim As String = "") _
     As DataSet

    ss = "SELECT * FROM " + TableName + " WHERE " & KeyName & " = " & Delim & KeyValue & Delim
    If CUSTOMERS.DataFilter <> "" Then ss &= " AND " & CUSTOMERS.DataFilter
    da = New SqlDataAdapter(ss, cs)
    da.Fill(ds, TableName)
    'If no rows were returned, they've applied a filter that doesn't
    ' include the current record. Use just the filter.
    If ds.Tables(0).Rows.Count = 0 Then
      ss = "SELECT TOP 1 * FROM " + TableName + " WHERE " & CUSTOMERS.DataFilter
      da = New SqlDataAdapter(ss, cs)
      da.Fill(ds, TableName)
    End If
    Return ds

  End Function

The Next and Previous methods do what you'd expect, but you might not have guessed how it's done. Look closely at the string "ss", recalling that the variable KeyValue contains the record key of the current record, and you'll see how it works.

  <Description("Finds the NEXT record in a SQL table")> _
  Public Function SQLNext( _
   ByVal TableName As String, _
   ByVal KeyName As String, _
   ByVal KeyValue As String, _
   Optional ByVal Delim As String = "") _
  As DataSet

    ss = "SELECT TOP 1 * FROM " + TableName _
    + " WHERE " & KeyName & " > " & Delim & KeyValue & Delim
    If CUSTOMERS.DataFilter <> "" Then ss &= " AND " & CUSTOMERS.DataFilter
    ss &= " ORDER BY " & KeyName
    Try
      da = New SqlDataAdapter(ss, cs)
      da.Fill(ds, TableName)
    Catch ex As Exception
      Errors(ex)
    End Try
    Return ds

  End Function

  <Description("Finds the PREVIOUS record in a SQL table")> _
    Public Function SQLPrevious( _
     ByVal TableName As String, _
     ByVal KeyName As String, _
     ByVal KeyValue As String, _
     Optional ByVal Delim As String = "") _
    As DataSet

    ss = "SELECT TOP 1 * FROM " + TableName _
    + " WHERE " & KeyName & " < " & Delim & KeyValue & Delim
    If CUSTOMERS.DataFilter <> "" Then ss &= " AND " & CUSTOMERS.DataFilter
    ss &= " ORDER BY " & KeyName + " DESC"
    Try
      da = New SqlDataAdapter(ss, cs)
      da.Fill(ds, TableName)
    Catch ex As Exception
      Errors(ex)
    End Try
    Return ds

  End Function

I also have methods for executing any SQL command (like UPDATE or DELETE), counting the number of records matching the current filter (CUSTOMERS.DataFilter is set in the frmCustomers form),

<Description("Executes a SQL string")> _
  Public Sub SQLString(ByVal str As String)

    cn = New SqlConnection(cs)
    cn.Open()
    cmd = New SqlCommand(str, cn)
    cmd.ExecuteNonQuery()
    cn.Close()

  End Sub

  <Description("Counts records matching the current filter")> _
   Public Function CountRecords(ByVal TableName As String) _
   As Integer

    ss = "SELECT COUNT(*) FROM " + TableName
    If CUSTOMERS.DataFilter <> "" Then ss &= " WHERE " & CUSTOMERS.DataFilter
    cn = New SqlConnection(cs)
    cn.Open()
    cmd = New SqlCommand(ss, cn)
    Dim HowMany As Integer = cmd.ExecuteScalar
    Return HowMany

  End Function

When I save a record, it's either an UPDATE or an INSERT; the Adding field in frmCustomers indicates which it is:

<Description("Save a record by constructing an INSERT or UPDATE string from a DataRow")> _
  Public Sub SaveRecord( _
   ByVal TableName As String, _
   ByVal dr As DataRow, _
   Optional ByVal Adding As Boolean = False, _
   Optional ByVal KeyName As String = "", _
   Optional ByVal KeyValue As String = "", _
   Optional ByVal Delim As String = "")

    Dim cmd As String

    Select Case Adding
      Case True
        cmd = "INSERT INTO " + TableName & " VALUES ( "
        For Each s As DataColumn In dr.Table.Columns
          If s.DataType.ToString = "System.String" _
          Or s.DataType.ToString = "System.Date" Then
            cmd &= "'" & Replace(dr(s.ColumnName), "'", "''") & "'" & ","
          Else
            cmd &= Replace(dr(s.ColumnName), "'", "''") & ","
          End If
        Next
        cmd = cmd.Substring(0, cmd.Length - 1) & " )"
      Case Else
        cmd = "UPDATE " + TableName & " SET "
        For Each s As DataColumn In dr.Table.Columns
          If s.DataType.ToString = "System.String" _
          Or s.DataType.ToString = "System.Date" Then
            cmd &= s.ColumnName & "=" & "'" & Replace(dr(s.ColumnName), "'", "''") & "'" & ","
          Else
            cmd &= s.ColumnName & "=" & Replace(dr(s.ColumnName), "'", "''") & ","
          End If
        Next
        cmd = cmd.Substring(0, cmd.Length - 1)
        cmd &= " WHERE " & KeyName & "=" & Delim & KeyValue & Delim
    End Select
    SQLString(cmd)

  End Sub

Deleting a record is a special case of the SQLCmd method:

<Description("Deletes a record from the named table using the supplied key name and value")> _
    Public Function Delete( _
    ByVal TableName As String, _
    ByVal KeyName As String, _
    ByVal KeyValue As String, _
    Optional ByVal Delim As String = "") As String

    cn = New SqlConnection(cs)
    cn.Open()
    ss = "DELETE " + TableName + " WHERE " & KeyName & " = " & Delim & KeyValue & Delim
    cmd = New SqlCommand(ss, cn)
    Dim msg As String = "Deleted"
    Try
      cmd.ExecuteNonQuery()
    Catch ex As Exception
      Errors(ex)
      msg = "Not deleted"
    End Try
    cn.Close()
    Return msg

  End Function

The Errors routine reports any errors encountered in the other methods:

  Sub Errors(ByVal ex As Exception)

    Dim s As String = ex.Message
    If ex.InnerException IsNot Nothing Then s &= vbCrLf & ex.InnerException.Message
    MessageBox.Show(s)

  End Sub

Finally, I added a couple of methods to enable and disable textboxes and buttons on the form, which is passed in ByRef as a parameter:

  Public Sub Enabler(ByRef f As Form, ByVal OnOff As Boolean)

    For Each c As Control In f.Controls
      If TypeOf c Is TextBox Then c.Enabled = OnOff
    Next
    Buttons(f, Not OnOff)

  End Sub

  Sub Buttons(ByRef f As Form, ByVal OnOff As Boolean)

    For Each c As Control In f.Controls
      If TypeOf c Is Button Then
        If c.Text = "&Save" Or c.Text = "&Cancel" Then
          c.Enabled = Not OnOff
        Else
          c.Enabled = OnOff
        End If
      End If
    Next

  End Sub

End Module

Polishing the generated form

I've made some changes to the Customers form; the final version appears in Fig. 8:



Fig. 8 - The final Customers form layout

I've added a Filter button which enables the GroupBox at the right side of the form; when the form is first displayed, the GroupBox is invisible, and the labels underneath it show how many records match the current filter (which initially is None). The Textboxes, as well as the Save and Cancel buttons, are disabled in the Load event code;



Fig. 9 - The intial Customers form

Here's the Declarations section of the form, and an event handler to close the form if the user presses ESC:

Public Class CUSTOMERS

  Dim dsC As New DataSet

  Public CustomerID As String = ""
  Public DataFilter As String = ""

  Dim RecCount As Integer = 0
  Dim Adding As Boolean = False

We want to let users close the form by pressing ESC. Note that this requires Form.KeyPreview = True, just like in FoxPro. (Note also that later, we change the form's CancelButton property to cmdCancel while adding and editing, so that ESC means Cancel during those operations.)

  Private Sub CUSTOMERS_KeyPress( _
   ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) _
   Handles Me.KeyPress

    If Strings.Asc(e.KeyChar) = Keys.Escape Then Close()

  End Sub

We use the Load event to load a single record and display how many records are in the table. If you want the screen to come up with empty textboxes, comment out the first three lines of code:

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

    dsC = DataAccessComponent.SQLNext("CUSTOMERS", "CUSTOMERID", "", "'")
    CustomersBindingSource.DataSource = dsC.Tables("CUSTOMERS")
    CustomerID = dsC.Tables("CUSTOMERS").Rows(0)(0).ToString
    DataAccessComponent.Enabler(Me, False)
    ShowRecordCount()

  End Sub

The ShowRecordCount method is called from several routines to show the number of records matching the current filter:

  Sub ShowRecordCount()

    RecCount = DataAccessComponent.CountRecords("CUSTOMERS")
    lblRecordCount.Text = String.Format("{0} records", RecCount) + " "
    If RecCount = 0 then
       ShowButtons(False)
      Else
       ShowButtons(True)
    End If

  End Sub

This is used to disable certain buttons when no records match the current filter

  Sub ShowButtons(ByVal OnOff As Boolean)

    cmdNext.Enabled = OnOff
    cmdPrevious.Enabled = OnOff
    cmdDelete.Enabled = OnOff
    cmdEdit.Enabled = OnOff

  End Sub

The Add, Edit, Delete, Save and Cancel Click event handlers are a little opaque, but if you read them line for line, they'll begin to make sense. Run the program with a breakpoint set at the beginning of each method's code, and take the time to see what they're doing. I set Form.CancelButton = cmdCancel while adding or editing, then turn it off during Save and Cancel.

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

    dsC.Clear()
    Dim dr As DataRow = dsC.Tables(0).NewRow

    ' The columns by default have the value NULL, so change it to an empty string:

    For I = 0 To dsC.Tables(0).Columns.Count - 1
      dr(I) = ""
    Next

    ' Create a 5-character random string as the key - better than letting them type it in.

    Dim gd As String = Guid.NewGuid.ToString
    dr(0) = gd.Substring(0, 5).ToUpper()

	' Add the generated row to the (empty) datatable

    dsC.Tables(0).Rows.Add(dr)

    CustomersBindingSource.DataSource = dsC.Tables("CUSTOMERS")
    txtCustomerid.Enabled = False
    DataAccessComponent.Enabler(Me, True)
    txtCustomerid.Enabled = False
    Adding = True
    CancelButton = cmdCancel

  End Sub

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

    DataAccessComponent.Enabler(Me, True)
    txtCustomerid.Enabled = False
    CancelButton = cmdCancel

  End Sub

The Delete button is pretty trivial in SQL; however, the code that runs after you delete the record takes a little planning. I chose to go to the "next" record, unless there isn't any, in which case I turn off the filter. That's one way to do it, and I'll bet you like your way better...<g>

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

    Dim msg As String
    msg = DataAccessComponent.Delete("CUSTOMERS", "CUSTOMERID", CustomerID, "'")
    If msg <> "Deleted" Then MessageBox.Show(msg)

    CustomersBindingSource.RemoveCurrent()
    CustomersBindingSource.EndEdit()
    dsC.Clear()
    dsC = DataAccessComponent.SQLNext("CUSTOMERS", "CUSTOMERID", CustomerID, "'")
    ' If no matching records due to filter, clear the filter
    If dsC.Tables(0).Rows.Count = 0 Then
      DataFilter = ""
      dsC = DataAccessComponent.GetOne("CUSTOMERS", "CUSTOMERID", CustomerID, "'")
    End If
    CustomersBindingSource.DataSource = dsC.Tables("CUSTOMERS")
    CustomerID = dsC.Tables("CUSTOMERS").Rows(0)(0).ToString
    ShowRecordCount()

  End Sub

Save creates either an INSERT or an UPDATE command. And if an Add or Edit is canceled, we want to see what was there before the Add or Edit started. Also, we remove the filter if we were adding, because the record we just added might not match the current filter.

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

    CustomersBindingSource.EndEdit()
    CustomerID = dsC.Tables(0).Rows(0)(0).ToString
    DataAccessComponent.SaveRecord( _
     "CUSTOMERS", dsC.Tables(0).Rows(0), Adding, "CustomerID", CustomerID, "'")
    DataAccessComponent.Enabler(Me, False)                    ' Disable all textboxes, enable buttons.
    If Adding Then
      DataFilter = ""                                         ' Empty the data filter
      lblFilter.Text = "None"
      ShowRecordCount()
    End If
    Adding = False                    	                      ' Turn the Adding switch off
    CancelButton = Nothing                                    ' Escape now handled by the KeyPress event handler

  End Sub

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

    CustomersBindingSource.CancelEdit()
    dsC.Clear()
    ' Load the last record displayed before the Add or Edit...
    dsC = DataAccessComponent.GetOne("CUSTOMERS", "CUSTOMERID", CustomerID, "'")
    CustomersBindingSource.DataSource = dsC.Tables("CUSTOMERS")
    CustomerID = dsC.Tables("CUSTOMERS").Rows(0)(0).ToString  ' Save the CustomerID
    DataAccessComponent.Enabler(Me, False)                    ' Disable all textboxes, enable buttons
    Adding = False                    	                      ' Turn the Adding switch off
    CancelButton = Nothing                                    ' Escape now handled by the KeyPress event handler

  End Sub

The NEXT and PREVIOUS get just one record using SELECT TOP 1. (Notice the ORDER BY..DESC trick for Previous)

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

    dsC.Clear()
    dsC = DataAccessComponent.SQLNext("CUSTOMERS", "CUSTOMERID", CustomerID, "'")
    If dsC.Tables(0).Rows.Count = 0 Then _
    dsC = DataAccessComponent.GetOne("CUSTOMERS", "CUSTOMERID", CustomerID, "'")
    CustomersBindingSource.DataSource = dsC.Tables("CUSTOMERS")
    CustomerID = dsC.Tables("CUSTOMERS").Rows(0)(0).ToString

  End Sub

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

    dsC.Clear()
    dsC = DataAccessComponent.SQLPrevious("CUSTOMERS", "CUSTOMERID", CustomerID, "'")
    If dsC.Tables(0).Rows.Count = 0 Then _
    dsC = DataAccessComponent.GetOne("CUSTOMERS", "CUSTOMERID", CustomerID, "'")
    CustomersBindingSource.DataSource = dsC.Tables("CUSTOMERS")
    CustomerID = dsC.Tables("CUSTOMERS").Rows(0)(0).ToString

  End Sub

These two event handlers make the Filter Groupbox visible or invisible:

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

    GroupBox1.Visible = True

  End Sub

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

    GroupBox1.Visible = False

  End Sub

The two two buttons at the bottom of the Filter GroupBox also have event handlers; the ApplyFilter button is in the Filter groupbox. When clicked, the DataFilter field is populated. Then the filter is applied using the GetOne method. If no records match, the BindingSource DataMember is set to Nothing to prevent an error.

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

    DataFilter = cmbColumnName.Text.TrimEnd
    If radStartsWith.Checked Then
      DataFilter &= " LIKE '" & txtFilter.Text.TrimEnd & "%'"
    Else
      DataFilter &= " LIKE '%" & txtFilter.Text.TrimEnd & "%'"
    End If
    lblFilter.Text = DataFilter
    GroupBox1.Visible = False
    ShowRecordCount()

    dsC.Clear()
    dsC = DataAccessComponent.GetOne("CUSTOMERS", "CUSTOMERID", CustomerID, "'")
    If dsC.Tables(0).Rows.Count = 0 Then
      lblRecordCount.Text = "No matches"
      CustomersBindingSource.DataMember = Nothing
      CustomerID = ""
      ShowButtons(False)
    Else
      ShowButtons(True)
    End If

  End Sub

The other button clears the DataFilter, gets the first record in the table, and closes the groupbox:

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

    DataFilter = ""
    GroupBox1.Visible = False
    lblFilter.Text = "None"
    dsC = DataAccessComponent.SQLNext("CUSTOMERS", "CUSTOMERID", "", "'")
    CustomersBindingSource.DataSource = dsC.Tables("CUSTOMERS")
    CustomerID = dsC.Tables("CUSTOMERS").Rows(0)(0).ToString
    DataAccessComponent.Enabler(Me, False)
    ShowRecordCount()

  End Sub

And finally, the cmdClose click handler just closes the form:

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

    Close()

  End Sub

End Class

Conclusion

You can build a form with any of your tables, make minor modifications to this code, and have the form up and running in a few minutes. I should build a code generator to do so, but there are only so many hours in the day, and I've got a lot of Fox-to-.NET explaining to do.

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