TableAdapter basics (VB.NET)

  • Article

Introduction

Most applications created using Visual Basic communicate with databases, which Visual Studio offers many different methods to work with databases.  For hobbyist and novice coders finding an easy method to communicate with databases. This is where TableAdapter method for communicating with databases enters the picture. The coder runs a data wizard, which generated code to provide a drag and drop to a form, build the project, run and interact with a database. Unfortunately without reading TableAdapter documentation and spending time to learn the mechanics frustration follows as the typical coder new to software developer usually tries different things out that tend not to work because the documentation has not be read.

This article provides basic insight into next steps after running the data wizard to move pass frustration working with a SQL-Server database. Other databases other than SQL-Server are supported databases like Microsoft Access and Oracle are less robust with underlying features.

Not everything needed to know about working with TableAdapter will be covered, only the very basics that will provided a better user experience and insight into working with this method of data communications. 

First steps

  • Using a tool such as Visio or pen and paper design tables needed.
    • Each table should have a primary auto incrementing primary key
    • Create the database using SSMS
    • Create relations between related tables using SSMS database diagram
    • Create a backup of the database using SSMS Task -> Backup database
    • Populate tables with mocked data
    • Create queries to validate the database model, most likely this will change over time. By performing these steps rather than simply creating tables without much thought will pay off later when dragging and dropping data bound controls onto a form followed by making changes to the model and having to edit data bindings on the form.
  • Create and configuring, see the following Microsoft documentation for a step by step guide.

Examine code generated

The following is code generated by creating a new TableAdapter. Note there is a great deal of hidden code generated which is hidden and never should be modified as when altering generated code by re-running the data wizard will delete your custom code.

Public Class  Form1
    Private Sub  ContactsBindingNavigatorSaveItem_Click(sender As Object, e As  EventArgs) _
        Handles ContactsBindingNavigatorSaveItem.Click
 
        Me.Validate()
        Me.ContactsBindingSource.EndEdit()
        Me.TableAdapterManager.UpdateAll(Me.NorthWindContactsDataSet)
 
    End Sub
 
    Private Sub  Form1_Load(sender As  Object, e As EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'NorthWindContactsDataSet.Contacts'
        'TODO table. You can move, or remove it, as needed.
        Me.ContactsTableAdapter.Fill(Me.NorthWindContactsDataSet.Contacts)
 
    End Sub
 
    Private Sub  CloseButton_Click(sender As Object, e As  EventArgs) Handles  CloseButton.Click
        Close()
    End Sub
End Class

Other than the close button and resizing the form the following is presented when running the project.

There is a BindingNavigator for traversing records and buttons to add and delete records along with a button to save changes. 

What is missing?

  • A prompt to ask if a record should be deleted, as is pressing delete marks the record for deletion and is deleted when the save button is clicked.
  • The Save button does not prompt same as the delete button.
  • There is no column validation when data is edited.
    • No previsions to show a edit form nor add form
  • If the server or catalog are wrong or not available the application may first become unresponsive or simply crash.  

Warning Since generated code when altered can mess up bindings in forms using the TableAdapter it's advisable to use source control e.g. GitHub repository to have backups when and if things go wrong and they usually do. 

Remedies

 
The above screenshot is the final product which provides.

  • Prompt to confirm removing the current record along with ensuring there is a current record. if confirming a delete the removal is immediate.
  • Editing and adding records using model windows along with conventional add and editing from the DataGridView. Validation is performed in the model forms but not in the DataGridView which can be implemented similar to how validation is done in the model forms.
  • Pressing the enter key on a record will open a model form to edit the current record. If the current record is the "new record" row in the DataGridView no action is taken to show the model form.

What is not shown

  • Loading of data is done asynchronously in the Shown event rather than the Load event of the form. Whenever possible use Form Shown rather than Form Load event as the Form Load event sometimes will hide runtime exceptions while the Shown event will not hide exceptions.
    • Buttons which interact with the data are disabled until data has loaded.
  • The event ListChanged event of the BindingSource is for information purposes to show how to check for data changing.
  • There is method to show how to learn of changes separated by added and modified. In this code sample deleted will always be zero since they are deleted immediately while in other projects this may be different.
  • An empty DataError event for the DataGridView would be where code goes to circumvent and handle errors. Without this a dialog is shown for each row having an issue which can be all records and nobody wants to press a dialog for a large data set.

Reading initial data

Always expect that reading data will fail when reading data at program start. This can be caused by SQL-Server service not running, the server name is wrong, the catalog name is wrong or missing. By placing code into a Task along with a try-catch statement to handle the error will allow the application to remain responsive rather than appearing to be frozen (which it’s not, the underlying non-user code is working hard).

Private Sub  Form1_Shown(sender As Object, e As  EventArgs) Handles  Me.Shown
 
    Task.Run(
        Sub()
            Try
                '
                ' Attempt to load data
                '
                ContactsTableAdapter.Fill(NorthWindAzureForInsertsDataSet.Contacts)
 
                For Each  column In  ContactsDataGridView.Columns.Cast(Of DataGridViewColumn)
                    column.HeaderText = column.HeaderText.SplitCamelCase()
                    Invoke(New Action(Sub() column.HeaderText =
                                         column.HeaderText.SplitCamelCase()))
                Next
 
                Invoke(New Action(Sub() ContactsBindingSource.DataSource =
                                     NorthWindAzureForInsertsDataSet.Contacts))
                Invoke(New Action(Sub() ButtonList().
                                     ForEach(Sub(button) button.Enabled = True)))
 
                Invoke(New Action(
                    Sub()
                        For Each  tsb As  ToolStripButton In
                                     ContactsBindingNavigator.
                                     Items.OfType(Of ToolStripButton)
                            If Not  tsb.Enabled Then
                                tsb.Enabled = True
                            End If
                        Next
 
                    End Sub))
 
            Catch ex As Exception
                MessageBox.Show(ex.Message)
                Exit Sub
            End Try
        End Sub)
 
 
    If Environment.UserName = "PayneK" Then
        ContactIdDataGridViewTextBoxColumn.Visible = True
    End If
 
End Sub

Notes:

  • Since running code in a Task this means running in a different thread, to access controls on the form Invoke allows this and without this a cross thread violation exception would be thrown.
  • The last statement is used for the developer to see the primary key column while users do not which is helpful when developing or debugging. Replace the user name with the developer user name.

The following code ensures no buttons can be clicked if the loading of data above is taking a long time. The following language extension methods are used to disable and enable buttons

Private Sub  Form1_Load(sender As  Object, e As EventArgs) Handles MyBase.Load
 
    ButtonList().ForEach(Sub(button) button.Enabled = False)
 
    For Each  tsb As  ToolStripButton In  ContactsBindingNavigator.Items.OfType(Of ToolStripButton)
        If tsb.Enabled Then
            tsb.Enabled = False
        End If
    Next
 
End Sub

Accessing current record

This can be done by casting the Current property of the BindingSource to a DataRow.

Dim row = CType(ContactsBindingSource.Current, DataRowView).Row

Then access columns as follows.

row.Field(Of Integer)("ContactId")

Or through generated code, for each table there is a Row prperty e.g. for a Customer table CustomerRow, for Contacts ContactsRow. Using this method provides access to columns that are strong typed.

NorthWindAzureForInsertsDataSet.Contacts(ContactsDataGridView.CurrentRow.Index)

Accessing properties

NorthWindAzureForInsertsDataSet.Contacts(ContactsDataGridView.CurrentRow.Index).ContactId

Validating data in model forms

New developers come up with some interesting ways for validating which is usually on lost focus or during key strokes then there is checking in the click event of the save button (which is shown but never actually used, there for what not to do).

A simple way to validate is to place a ErrorProvier component on the form then subscribe to Validating and Validated events of controls that have data to be validated.

Example to ensure that a string value is not empty. If on leaving the TextBox in this case is there is no value a visual indicator indicates this needs a value. Without entering a value the form may not be closed using the save button but can be closed using the cancel button.  To allow the cancel button to bypass validation it's DialogResult needs to be set to cancel and the property CauseValidation must be set to False.

This validation is used in both the edit and add model forms.

Private Sub  FirstNameTextBox_Validating(sender As Object, e As  ComponentModel.CancelEventArgs) _
    Handles FirstNameTextBox.Validating
 
    If String.IsNullOrWhiteSpace(FirstNameTextBox.Text) Then
        ErrorProvider1.SetError(FirstNameTextBox, "Required")
        e.Cancel = True
    End If
End Sub
 
Private Sub  FirstNameTextBox_Validated(sender As Object, e As  EventArgs) _
    Handles FirstNameTextBox.Validated
 
    ErrorProvider1.SetError(FirstNameTextBox, "")
End Sub


Usually the indicator for the ErrorProvider is a red cross, since the image can be replaced it was in this case.

Extra touches

Take time to find meaningful images for controls as done in the above screenshots, some users are better off recognizing images than text. A good place to start is Visual Studio Image Library.

Refreshing data

One of the most common mistake for novice coders is to figure out how to refresh data in a DataGridView after making changes in code. When done right as done in the supplied code samples there is never a reason to think about refreshing or reloading data. The same holds true if working with a DataAdapter, managed data provider, Entity Framework or Entity Framework Core.

Summary

Information has been presented to better understand the basics for working with TableAdapter for communicating with data. Before moving onto working related data get to understand what has been presented to provide a decent foundation. When working with related data where a DataGridView joins several tables together this will present challenges on how perform inserts and updates and when needed to bind to DataGridViewComboBox and DataGridViewCheckBox columns with and without related data.

See also

Writing SQL for your application
VB.NET Entity Framework 6 basic data operations Window Forms
Entity Framework dynamic Order by (VB.NET)
Visual Basic best practices
.NET: Defensive data programming (part 1)

Source code

Clone or download from the following GitHub repository. Once opened in Visual Studio run script.sql to create and populate the database.