Browse DevX
Sign up for e-mail newsletters from DevX


ADO.NET: Building Your First Data-Aware Form : Page 2

The first article in this series detailed how to build data-aware forms in Visual Studio .NET using the Data Form Wizard. It demonstrated how easy it is to build forms using a point-and-click interface. You also learned that the generated form was not especially suited to production development. This article continues where that article left off and introduces you to techniques to improve data-aware forms created with the Data Form Wizard.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

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.

Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date