Using the Data Form Wizard in ADO.NET

This article has been excerpted from book "A Programmer's Guide to ADO.NET in C#".

You can use the Data Form Wizard to develop your database application with viewing, updating, and deleting capabilities. This is probably the fastest way to develop database applications in .NET (unless you're an extremely fast typist).

In this article, you'll use a Data Form Wizard to write a fully functioning database application including features such as inserting, updating, and deleting data without writing including features such as inserting, updating, and deleting data without writing a single line of code. In this simple example, I've used the familiar Northwind database. I'll use both the Customers and Orders tables to show you data relationship between table data.

Like Many parts of this article, this topic is in the form of tutorial. Just follow the simple steps, and in a few minutes you'll be able to run a wonderful application. In this article, you're going to create a windows application. After that you'll add a Data Form Wizard to it and call the Data Form Wizard from the main application.

Step 1: Selecting a Project Template

Create a new windows project by selecting New project > visual C# projects > windows application and typing your application name (see figure 4-56).

Figure 4-56. Creating a windows application project

Step 2: Adding a Data Form Wizard Item

Now add a Data Form Wizard by selecting project > Add New Item > Data Form Wizard from the available templates. You can type the name of your Data From class in the Name field of the dialog box (see figure 4-57).

Figure 4-57.Using the Data Form Wizard

Now click Open, which calls the Data Form Wizard.

Step 3: Walking through the Data Form Wizard

The first page of the wizard is a welcome page telling you what the wizard is about to do (see figure 4-58).



Figure 4-58. Welcome page of the Data Form Wizard

Step 4: Choosing the DataSet You Want

On the second page of the wizard, you can choose a dataset name that will later be used to access the data. You can either create a new dataset name or select an existing one. In this example, I'll choose MyDS as the dataset name (see in figure 4-5using-the-data-form-wizard-in-ado-net).



Figure 4-59:using-the-data-form-wizard-in-ado-net. Choosing a Dataset in the Data Form Wizard

Step 5: Choosing a Data Connection

The next page of the wizard asks you to provide a connection. The combo box displays your available connection. If you didn't create a connection, use the New Connection button, which launches the server Explorer discussed earlier in this article. I'll select the usual database, Northwind (see figure 4-60).



Figure 4-60. Choosing a data connection in the Data Form Wizard

Step 6: Choosing Tables or Views

The next page of the wizard lets you pick the table and views you want to connect to the dataset. As you can see in figure 4-61 select the Customers and Order tables in the Available Items list on this page and use the > button to add these tables to the selected Items list.



Figure 4-61. Choosing a DataTable or DataView in the Data Form Wizard

Now you're ready to create a relationship between these two tables.

Step 7: Creating a Relationship between Tables

The next page lets you define a relationship between the Customers and Orders tables. It's useful to provide a relationship between tables when you have a master detail relationship database. In other words, a customer may have many orders associated with it, so there is a relationship through the Customer ID in the Orders table joined to information about the customer in the Customers table. Now, say you want to see all the orders of a customer based on the CustomersID. If you do this manually, you need to write code to select data from the Orders table to correspond to a CustomerID and then fill data to the form. If you use Data Form Wizard instead, it does every thing for you. Neat huh?

This is the same step you're going to see on the Create a Relationship between Tables page of the wizard. You're going to create a relationship between the Customers and Orders table based on the Customer ID. I named the relationship between Customers and Orders table CustOrderRelation. You also need to pick the associated primary key and foreign key that links the parent to the child table. Once you've chosen the joining key (CustomerID), you have to click the >button to tell the wizard that you want to add it.

When you run the final program, you'll see how you can filter all orders for a customer based on the CustomerID. As you can see from figure 4-62, you need to pick one table as parent and another table as a child based on the relationship between them. In this example, the Customers table is the parent table, and the Order table is the child table.



Figure 4-62. Selecting Customers as the parent and Orders as the child table to create the custOrderRelation relationship

After adding the relationship to the Relations list, the wizard looks to like (figure 4-63).



Figure 4-63. CustOrder Relation listed in the Relations list

Step 8: Choosing Tables and Columns to Display on the Form

The next page of the wizard lets you select which tables and columns you want to show on the form. For this example, select all the columns from both of the tables (this is the default selecting). As you see in figure 4-64, the Customers table is the master, and the Orders table is the detail table.



Figure 4-64. Choosing columns to display on the Data From Wizard

Step using-the-data-form-wizard-in-ado-net: Choosing the Display Style


This page is an important part of creating your form. Actually The Data Form Wizard adds a Windows form with some controls on it and writes code to fill, update, delete, and navigate data. There are two ways to view the data, and you choose your option on this page. These two options are:

• All Records in a Grid
• Single Record in Individual Controls

Figure 4-65. Display these options.



Figure 4-65. Choosing between a grid and individual controls on the Data From wizard

The output of All Records in a Grid looks like Figure 4-66. After that you can resize controls on the form.



Figure 4-66. GridData From output

The second option, Single Record in Individual Controls, shows data text boxes and provides you with navigation controls. As you can see from figure 4-67, the single Record in Individual Controls option activates Add, Delete, Cancel, and Navigation controls check boxes. You can uncheck the check boxes if you don't want to add that feature in your project.



Figure 4-67. The single Record in Individual Controls option

The form generated by this generated by this option looks like figure 4-68. As you can see from figure 4-68, each column of the table has a field on the form.



Figure 4-68. Data Form Wizard- generated form for the single Record in Individual Control option

After your selection of data display style, you click Finish button. The Data From Wizard adds the windows form DataFrom1 and the class DataFrom1.cs corresponding to it.

Step 10: Calling the Data Form Wizard Form from the Application

Now you need to change one more thing. You need to call DataFrom1 when you start your application. By default your application calls the Form1 form on start up.

        static void Main()
        {
            Application.Run(new Form1());
        }

So, you need to replace Form1 with your Data Form Wizard's form name. In this example, listing 4-6 replaces Form1 with DataFrom1 in the main method.

Listing 4.6 Calling Data From1 from the application

        static void Main()
        {
            Application.Run(new Form1());
        }

NOTE: If you've modified the name of your Data From Wizard-generated form, you need to call that form instead of Data From1.

Step 11: Viewing the Output

Now you should see the output shown in figure 4-6using-the-data-form-wizard-in-ado-net when you run your application (if you selected the grid view option).

The load and Update buttons load and update the data, respectively, and Cancel all cancels all the operations. The neat thing is if you more into the top grid corresponding information changes in the button grid. Neat, huh?



Figure 4-60:using-the-data-form-wizard-in-ado-net. Data Form Wizard with all records in a grid option

Figure 4-70 shows the output when you select the single Record in Individual Control option. By using this view option, you can add, edit, delete, and navigate records easily.



Figure 4-70.Textbox Output with navigational controls

Finally, compile and run your application. Without writing a single line of code, you just created a fully functional database application.

The Load button on the individual control form loads the data, and the Add, Update, and Delete buttons on the form inserts, updates, and deletes records, respectively.

Data From Wizard: Looking under the Hood

You just saw how you can develop fully functional database applications in no time with the help of the Data Form Wizard. Now let's see what the wizard does for you in the actual code. (The inherent beauty of VS.NET is that it magically hides all the messy code for you.) The wizard adds two items to your project: MyDs.xsd and DataForm1.cs.

Understanding MyDS.xsd

MyDs.xsd is an XML schema for the dataset you've added to the project. It's similar to the one discussed in the "Understanding Typed DataSets in visual studio .NET" article of this article.

Understanding Data From1.cs

The second item added by the wizard is the DataForm1 class, a class derived from System.Windows.Forms.Form. The DataForm1 class defines its entire functionally. The InitializeComponent method creates the data connection, the data command, the data adapter, the dataset, and other data components.

The LoadDataSet method loads the data from the data source into the controls by calling FillDataSet (see listing 4-7).

Listing 4-7. Load DataSet method generated by the Data Form Wizard

        public void LoadDataSet()
        {
            // Create a new dataset to hold the records returned from the call to FillDataSet.
            // A temporary dataset is used because filling the existing dataset would
            // require the databindings to be rebound.
            MyDataFormWizardSamp.MyDS objDataSetTemp;
            objDataSetTemp = new MyDataFormWizardSamp.MyDS();

            try
            {
                // Attempt to fill the temporary dataset.
                this.FillDataSet(objDataSetTemp);
            }

            catch (System.Exception eFillDataSet)
            {
                // Add your error handling code here.
                throw eFillDataSet;
            }

            try
            {
                // Empty the old records from the dataset.
                objMyDS.Clear();
                // Merge the records into the main dataset.
                objMyDS.Merge(objDataSetTemp);
            }

            catch (System.Exception eLoadMerge)
            {
                // Add your error handling code here.
                throw eLoadMerge;
            }
        }

FillDataSet fills the dataset from the data adapter by calling the Fill method on each data adapter. Note that with the Data Form Wizard, a DataAdapter is created for each table, One DataAdapter for The Customers table and one DataAdapter for the Orders table. Both DataAdapters fill the same DataSet. Listing 4-8 shows the FillDataSet method.

Listing 4-8. The FillDataSet method generated by the Data Form Wizard

       public void FillDataSet(MyDataFormWizardSamp.MyDS dataSet)
        {
            // Turn off constraint checking before the dataset is filled.
            // This allows the adapters to fill the dataset without concern
            // for dependencies between the tables.
            dataSet.EnforceConstraints = false;

            try
            {
                // Open the connection.
                this.oleDbConnection1.Open();
                // Attempt to fill the dataset through the OleDbDataAdapter1.
                this.oleDbDataAdapter1.Fill(dataSet);
                this.oleDbDataAdapter2.Fill(dataSet);
            }

            catch (System.Exception fillException)
            {
                // Add your error handling code here.
                throw fillException;
            }

            finally
            {
                // Turn constraint checking back on.
                dataSet.EnforceConstraints = true;
                // Close the connection whether or not the exception was thrown.
                this.oleDbConnection1.Close();
            }
        }

The UpdateDataSource method Updates the data source from the DataSet. The UpdateDataSet method calls UpdateDataSource, which utilizes the Update method of the data adapters. Listing 4-using-the-data-form-wizard-in-ado-net shows the UpdateDataSource method.

Listing 4-using-the-data-form-wizard-in-ado-net. The UpdateDataSource and UpdataDataSet methods generated by the Data Form Wizard

       public void UpdateDataSource(MyDataFormWizardSamp.MyDS ChangedRows)
        {
            try
            {
                // The data source only needs to be updated if there are changes pending.
                if ((ChangedRows != null))
                {
                    // Open the connection.
                    this.oleDbConnection1.Open();
                    // Attempt to update the data source.
                    oleDbDataAdapter1.Update(ChangedRows);
                    oleDbDataAdapter2.Update(ChangedRows);
                }
            }

            catch (System.Exception updateException)
            {
                // Add your error handling code here.
                throw updateException;
            }

            finally
            {
                // Close the connection whether or not the exception was thrown.
                this.oleDbConnection1.Close();
            }
        }

Conclusion

Hope this article would have helped you in understanding the Data Form Wizard in ADO.NET. See my other articles on the website on ADO.NET.

This essential guide to Microsoft's ADO.NET overviews C#, then leads you toward deeper understanding of ADO.NET.