ADO.NET: Building Your First Data-Aware Form

hile the Data Form Wizard creates a fully functional data-entry form, you are left with a result that embeds the data logic directly in the user interface. This article shows you how to separate the data logic from the user interface logic in a form created by the Data Form Wizard.

The Data Form Wizard accomplishes two goals: first, it provides an illustration of how the various ADO.NET objects work; and, secondly, it shows you how data-binding works in a Windows form. Note the choice of words: “accomplishes” not “accomplishes well!” In fact, the means by which the Data Form Wizard accomplishes what it does leaves the user with a moderately useful academic sample but nothing that could be used in a serious production application. Furthermore, if you are attempting to get a sense of how the ADO.NET objects work, it can be a rather daunting task when viewed through the lens of a data-aware form created by the Data Form Wizard. For example: Where is the line of demarcation between the user interface (UI) and the data? What belongs in the UI? What belongs in the middle/data tier? How does ADO.NET really work? This article attempts to answer these questions by investigating what the Data Form Wizard produces. Specifically, this article focuses on how the various ADO.NET objects work on their own as well as how they work together in their joint function of working with data. Where the Data Form Wizard combines and confuses, this article separates and clarifies.

A Review of What the Data Form Wizard Produces
The end result of the Data Form Wizard is a consolidated entity consisting of UI and Data Tier elements. The wizard embeds all of the data logic directly on the form. Specifically, all of the ADO.NET objects as well as the associated setup code are directly embedded. You can quickly conclude that what the Data Form Wizard produces does not fall within component-based/n-tier development methodologies. A moderate attempt at componentization is achieved by the wizard through a set of form-level methods. Still, the initialization steps for both the form and the various ADO objects are merged into a single block of code that would result in many printed pages of paper. It is important to note that the Authors Form, illustrated in Figure 1, is a relatively simple form with only a few UI elements.

Figure 1: The initial version of the Authors Form produced by the Data Form Wizard.

Taking just a few more moments to discuss the results from the Data Form Wizard, you’ll find code that establishes a connection, a data adapter, a dataset and various command objects. These items, with perhaps the exception of the dataset object, are exclusively data related. Specifically, the UI does not need to directly deal with these items. In fact, the Data Form Wizard creates distinct form-level methods that act as an interface to the various operations that delete, update and insert data. If the Wizard created separate form methods, why not go the extra step of creating a separate class? The good news for you is that this article does just that!

Another observation is that the various command objects appear to require a lot of code to set up the various parameters. On one hand, you don’t have to crank out the code manually since the wizard does the work. On the other hand, what the wizard leaves you with is something that cannot be reused. Are you trapped with having to crank out this code manually if you want reuse? Is there a generic way you can determine the structure of tables and command parameters? The answer is “yes” and this issue is addressed in this article, too!

Next, the Data Form Wizard has a Load button that grabs all of the data and marshals that data to a dataset for data-binding purposes in the form. In conjunction with this, the wizard provides a set of navigation buttons that enables the user to go to the first, last, next and previous data records. No scaleable client/server application of note works this way. Typically, when data is requested, the user provides information to lookup the data via a query. When the dataset is small, a pick list with two columns helps to navigate through data. At all times, the goal is to minimize the amount of data brought back to the client. This article also highlights techniques you can use to minimize the amount of data you bring back to the client.

Finally, by default the wizard produces client-side rendered Select, Update, Insert and Delete SQL statements for the various ADO.NET command objects. For reasons of performance and security, you want to make use of stored procedures whenever possible. As you might guess, this article also illustrates how to use stored procedures with ADO.NET.

Issues to Address in the Modified Design

The previous section paints a dim picture of the output produced by the Data Form Wizard. The news is not all that bad. In reviewing the code produced, you can conclude that the actual functions of ADO are somewhat obscured through a lot of code. However, you can get a sense of how the various ADO objects work. Still, it remains to be seen whether the way ADO is implemented in the wizard is optimal or not. If you worked with earlier versions of ADO, you had to marvel at its simplicity and, further, you had to scratch your head about how ADO.NET works. With this in mind, the broad issues to address are:

  • Identify and segregate UI and data-related code
  • Simplify the ADO.NET implementation
  • Create a reusable middle-tier component to encapsulate data-related functions
  • Create a UI to consume the services of the new data component

With the big issues identified, let’s go about the task of improving upon what the Data Form Wizard left us!Identifying and Segregating UI and Data-Related Code
When building multi-tier applications you want the user interface to do nothing more than display data to the user. Apart from instantiating the data object and interacting with the data object’s interface, the UI should not know the details about how data is inserted, updated, deleted or selected. The only other task we want the form to do is data binding. This makes sense because the user accesses data through UI elements such as combo boxes, textboxes, checkboxes, etc.

As you might guess, the bulk of the work is offloaded from the form produced by the Data Form Wizard to a new data class that serves as a middle/data tier component. Near the end of this article, the manner in which the data form consumes the services of this new data object will be discussed.

Move over DataAdapter, Say Hello to the New Data Tier Component

In analyzing how ADO.NET is implemented, attention immediately focuses on the DataAdapter object. As you may recall, the DataAdapter ties together the various insert, update, delete and select commands objects. In addition, the DataAdapter fills a dataset with data. The DataAdapter also takes care of invoking the various commands to update, insert, delete and select data. The downside is that you are surrendering control to what is essentially a black box. If you modify the code produced by the wizard, the DataAdapter may cease to operate correctly. As it turns out, the wizard-created DataAdapter does not do anything that you cannot do yourself with a little work. Since a lot can be accomplished with little work, you gain complete control of your development process. If given the choice, a professional developer would gladly pay a small price for maximum control. The new data tier component ties together the ADO connection, Command, Dataset and DataReader objects.

How the Data Tier Component Works

The following sections outline the code for the data tier component. When broken down in small digestible units, you can see that the operation of the new class is simple and, yes, ADO.NET does not have to be that complicated after all!

Before getting into the nitty-gritty details, let’s take a moment to look at the specific ADO.NET objects the data tier component uses. Looking back at what the Data Form Wizard produced, you may recall objects like OleDbConnection, OleDbCommand, etc. These are the generic objects that can work with any OLE-DB provider. You may have noticed another namespace called System.Data.Sqlclient. This namespace contains a set of classes specifically designed and optimized to work with SQL Server. Since this class is being designed to use SQL Server, why not use objects that are specially tailored for the job? The objects that our new class use are called SqlConnection, SqlCommand and SqlDataReader. In addition, the new class uses the DataSet object. Since the DataSet is simply a vessel for holding data, a specialized SQL Server version of this object does not exist. (See Sidebar 4: Closing the DataReader)

Initializing the Data Tier Component

As shown in Listing 1, the code establishes the class definition by defining two public properties and seven private properties. As you can see, the class takes the place of the DataAdapter by coordinating the actions of the various commands. Because this is a custom class, a few additional commands are required to provide a list of items and to provide a blank record for new author entries. When the class initializes, the process culminates with setting up the connection and the various command objects. These private methods are discussed in the next section.

SetupConnection, SetupCommands, and CommandFactory Methods

The SetupConnection method, as detailed in Listing 2, is fairly straightforward. The method instantiates a new connection object, which is a member property of the class. The ConnectionString property is populated with specific information. Finally, the process culminates with opening the connection.

The SetupCommands method, as shown in Listing 3, makes individual calls to the CommandFactory method, shown in Listing 4. The argument passed to the CommandFactory method specifies how the CommandText property is constructed. By using a simple naming convention, you can generalize the process of constructing command objects. The tool used to create this feature is the SqlCommandBuilder class and, specifically, the DeriveParameters method. In earlier versions of ADO, you could populate the parameters collection by calling the Parameters Collection Refresh method. The DeriveParameters method of the SqlCommandBuilder class facilitates the same functionality in ADO.NET. The one caveat is that you must use the SqlClient objects in System.Data. Once the CommandObject has been made aware of the Connection object it is to use, along with the values specified in the TableName and KeyField properties, the data tier object is all set to go to work.

The Various Action Methods of the Data Tier Component

Once the data object has been instantiated, it waits for the user to make a request. The user in this case is the modified data form, which will be discussed later. You will begin to see a consistent pattern emerge throughout the following methods and, soon, it will gel very quickly.

GetBlank Method

The GetBlank method, as shown in Listing 5, does exactly what the name implies; it obtains an empty record for the table specified in the TableName property. Notice that the BlankCommand is invoked for this operation. The first step involves instantiating a new DataSet object. A new table is added to the DataSet. In this case, the table is the name specified in TableName. Once the table has been added, the ExecuteReader method of the BlankCommand object is invoked. This method produces a SqlDataReader object. The SqlDataReader object provides two important pieces of information: the information for the fields to be defined in the new table in the DataSet, and the data contained in the SqlDataReader itself.

The next block of code iterates through the SqlDataReader’s field collection. The information in each field object defines a new field in the new DataSet table. Once the fields, more commonly known as columns, have been defined, the next step involves iterating through the reader via the SqlDataReader’s Read method. The data in each row in the SqlDataReader is transferred to a new row in the DataSet table. This is essentially how the Fill method of the DataAdapter works. The good news is that, in this case, you don’t need to worry about a complex set of table mappings that have to be in place for the DataAdapter to work properly.

GetData Method

When you wish to obtain data for a specific row in the Authors Table, the GetData method is invoked, as shown in Listing 6. This method accepts one parameter, which is the primary key value for a specific author. The GetData method is nearly identical to the GetBlank method. Just like GetBlank, GetData establishes a new DataSet, uses a Command object (the SelectCommand in this case) to obtain a SqlDataReader object. Instead of a blank record, the SqlDataReader contains data for a specific author. Like GetBlank, the information in the SqlDataReader is used to specify the columns and the data to add to the DataSet table.

UpdateData Method

The UpdateData method, as shown in Listing 7, accepts a DataSet as its sole parameter. Remember that the UI binds to and updates the data in the DataSet. The primary key information can easily be gleaned from the DataSet. The UpdateData method invokes the services of the UpdateCommand. How can we determine the mapping between fields and stored procedure parameters? It is quite simple, since the name of the field and the stored procedure are the same with the exception of the “@” character that prefaces SQL Server variables. By iterating through the fields in the DataSet table, each corresponding parameter in the UpdateData Command can be populated. Finally, when all of the parameters have been populated, the ExecuteNonQuery method is invoked. This method simply executes the command without returning a result set.

InsertData Method

The InsertData method, as detailed in Listing 8, works in nearly the same way as the UpdateData method. The primary difference is that the InsertCommand is used and a new entry into the Authors table is created.

DeleteData Method

The DeleteData method, as shown in Listing 9, is very simple. This method accepts the primary key of the author to delete and then invokes the DeleteCommand. It does not get much simpler than this!

GetList Method

The last method to discuss is the GetList method, as shown in Listing 10. This method invokes the ListCommand to obtain a SqlDataReader of data. Like the other methods, the SqlDataReader provides the column-related data as well as the core data. In other words, the SqlDataReader provides the foundation for the DataSet table.

The Data Tier Component, and a Few Final Thoughts

So do you now have a complete and optimized design? Certainly not. A good start? Absolutely. Because ADO.NET is so new, there is not a plethora of resources to consult as would be the case with a mature technology. In the end, the Data Tier Component presented here greatly improves on what the Data Form Wizard produces. Most importantly, it should help to simply and clarify how portions of ADO.NET work. The idea was to focus on those things an experienced ADO developer would be most interested in when it comes to ADO.NET. As for improving on the design, I’ll leave that to you because that’s the most fun for a software developer! With the Data Tier Component out of the way, let’s conclude with taking a brief look at the data form.The Improved Data Entry Form

Figure 2: The improved version of the Authors Data Entry Form.

Figure 2 illustrates the new and improved version of the Authors Data Entry Form. This version uses a drop-down combo box to navigate through the authors. In addition, this version toggles the enabled status of various controls based on whether the user is in add, edit or view mode.

The inner workings of the data form are actually quite simple. The following sections take you through the different portions of the code to give you a lay of the land.

Initializing the Form

A form property called AuthorData is created to hold a reference to the Data Tier Component discussed earlier. The form also has a property to hold a reference to a DataSet that holds the current author information. The DataSet is produced from the Data Tier Component. Finally, there is a form-level property to keep track of the current edit mode status. This data type is an enumerated type that can have the value of add, edit or view.

In the New procedure, as shown in Listing 11, the author drop-down combo box is populated, the mode is set to view and the ChangeMode Method is invoked. The ChangeMode is a form-level method that simply toggles the enabled status of the various form objects. That method is discussed in a few moments.

PopulateAuthorCombo Method

The PopulateAuthorCombo method, as shown in Listing 12, invokes the Data Component’s GetList method, which returns a DataSet. With a few lines of code, the combo box can bind to the dataset. It is a matter of setting the DataSource, DisplayMember and ValueMember properties for the combo box. The only additional work involves saving the combo box’s SelectedIndex property and restoring it, if necessary.

ChangeMode Method

The ChangeMode method, as shown in Listing 13, toggles the enabled status of a control based on the current mode (add, edit or view). Every time the user initiates an operation, the mode property is updated and a call is made to the ChangeMode method. In further illustrations, you’ll see numerous calls to the ChangeMode method.

DisplayData Method

A call is made to the DisplayData method whenever the selected value of the combo box changes. The DisplayData method, as shown in Listing 14, gets the primary key information from the selected item in the combo box, and then passes that information to the Data Tier Component’s GetData method. If you recall, the GetData method returns a DataSet that contains data of the selected author. The last step is a call to the CreateBindings method, which is discussed in the next section.

CreateBindings Method

Form elements like a textbox, combo box, checkbox, etc. can bind to elements in a DataSet. Specifically, a textbox can bind to a field which is contained in a table which in turn is contained in a DataSet. Each form element can support one active binding at a time. That is why the bindings are preemptively cleared at the beginning of the CreateBindings method, shown in Listing 15. Also, in order to refresh the data on the form, this method of clearing and re-establishing the bindings proved to be the easiest and most straightforward way of accomplishing the task. Once the bindings are cleared, you add a data binding to each object by specifying the object’s property to display the data. For a textbox, the Text property is used to display and update the data. For a checkbox, the Selected property is used for data binding. You could specify the Text property, but then you would see the label text change and you would have no way of writing data back! In each case, you need to specify which DataSet as well as the table and field the data is located. As you’ll see, it works like a charm!

WriteData and DeleteData Methods

When you click a command button, it invokes one of the form level methods. To review, whenever you pick an item from the Author combo box, the DisplayData method is invoked, which does the work of getting data from the Data Tier Component and binding the data to the various form controls. When you click the Save button, the WriteData method, as shown in Listing 16, is invoked. The Mode property determines whether the Data Tier Component’s InsertData or UpdateData method is invoked. Regardless of which method is invoked, the DataSet holding the current and perhaps modified or new author information is passed as a parameter to the respective method. Once the data has been updated, a call is made to the PopulateAuthorCombo and DisplayData methods. Finally, the mode is reset to view, toggling the enabled status of the various form controls.

The DeleteData method, as shown in Listing 17, is fairly straightforward. The user is prompted to confirm whether the data should be deleted and, if the user answers yes, the Data Tier Component’s DeleteData Method is invoked. Again, the PopulateAuthorCombo and DisplayData methods are invoked. This Delete button is only enabled in view mode, so there is no need to call the ChangeMode method.

What About Closing the Form with Pending Changes?

In earlier versions of Visual Basic, forms had a QueryUnload event that you could use to trap when the user pressed the Close box on the form toolbar. Like many things, there is a different way of doing the same thing in .NET. Windows forms support a Closing event that enables you to trap when user clicks the Close button. As seen in Listing 18, the form’s Mode property is checked and if it is not View, the user is prompted to save or cancel their changes first. The code looks quite a bit different than the equivalent Visual Basic 6 code, but it accomplishes the same job.The Stored Procedures
This solution implements six stored procedures in the Pubs SQL Server database. Those procedures are listed as follows:

  • sp_authors_blank: Returns an empty author record
  • sp_authors_delete: Deletes a specific author record
  • sp_authors_insert: Inserts a new author record
  • sp_authors_list: Obtains a list of authors
  • sp_authors_select: Selects data for a specific author
  • sp_authors_update: Updates data for a specific author

Please refer to the pubs.sql script file in the ZIP file that contains the source code for this article. Before working with the data tier component and data form, you must run the script. You can download this file from http://www.code-magazine.com/downloads/sepoct2002adodotnet.zip.

Summary
The first article of this series left off with the following three questions:

  • Which of the generated code is good?
  • Which of the generated code is bad?
  • How can the code be optimized?

When dealing with the first question you can conclude that not much was worth saving. That pretty much answers the second question, in that all the code was bad. Perhaps the underlying principle of the code was fine but the means used to carry out the intended result were not so good. In any case, this article has shown a method for how the code can be re-tooled and optimized. By implementing a component-based approach and by allocating the work to the proper tier, the mass of unintelligible and complex code was simplified. Hopefully for you, this has brought a sense of clarity to your understanding of ADO.NET and how data-aware forms can be implemented in .NET. By no means is this article meant to convey the notion that the solution discussed here is perfect or optimal, it is merely a first cut on how to employ a new technology. Hopefully, this solution gives you some ideas on how to employ component-based/database application development with Visual Basic, Visual Studio and ADO.NET.

Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

The Latest

homes in the real estate industry

Exploring the Latest Tech Trends Impacting the Real Estate Industry

The real estate industry is changing thanks to the newest technological advancements. These new developments — from blockchain and AI to virtual reality and 3D printing — are poised to change how we buy and sell homes. Real estate brokers, buyers, sellers, wholesale real estate professionals, fix and flippers, and beyond may

man on floor with data

DevX Quick Guide to Data Ingestion

One of the biggest trends of the 21st century is the massive surge in internet usage. With major innovations such as smart technology, social media, and online shopping sites, the internet has become an essential part of everyday life for a large portion of the population. Due to this internet

payment via phone

7 Ways Technology Has Changed Traditional Payments

In today’s digital world, technology has changed how we make payments. From contactless cards to mobile wallets, it’s now easier to pay for goods and services without carrying cash or using a checkbook. This article will look at seven of the most significant ways technology has transformed traditional payment methods.