Creating a Disconnected Application

For the rest of the chapter, we’ll turn our attention to disconnected applications. A disconnected application is one that does not have a permanently available connection to the data source. For example, a salesperson might require an application that can access data in the central database, even when he or she is out of the office.

ADO.NET provides the DataSet class to enable you to achieve data access in disconnected applications. The following figure shows the DataSet object model.

A DataSet is an in-memory collection of DataTable objects and relationships between these DataTables. You can create many DataTable objects in a DataSet to hold the results of numerous SQL queries.

Each DataTable has a collection of DataColumn objects. These DataColumn objects contain metadata about the columns, such as the column name, data type, default value, and so on.

Each DataTable also has a collection of DataRow objects. These DataRow objects contain the data for the DataSet.

To fill a DataSet with data, you must use a data adapter object. If you are using the SQL Server .NET data provider, you will use SqlDataAdapter. If you are using the OLE DB .NET data provider, you will use OleDbDataAdapter.

The following figure shows how data adapters work with DataSets.

Each data adapter works with a single DataTable in a DataSet. You call the Fill method on a data adapter to fill the DataSet with data from the database. You call the Update method on a data adapter to save any changes in the DataSet back to the database.

Internally, the data adapter has four command objects to achieve data access on behalf of the DataSet. The data adapter uses these command objects to retrieve data from the database and to update the database with any modifications. If the data adapter is using the SQL Server data provider, these commands will be SqlCommand objects. If the data adapter is using the OLE DB data provider, these commands will be OleDbCommand objects.

The following table describes these command objects.

Command Object in a Data Adapter	Description
SelectCommand	Contains a SQL SELECT statement to retrieve data from the database into the DataSet table
InsertCommand	Contains a SQL INSERT statement to insert new rows from the DataSet table into the database
UpdateCommand	Contains a SQL UPDATE statement to modify existing rows in the database
DeleteCommand	Contains a SQL DELETE statement to delete rows from the database

To illustrate these capabilities, you will create a new application that performs the following tasks:

• Create an OleDbDataAdapter to access the Products table in the Northwind database.

• Use the OleDbDataAdapter to fill a DataSet.

• Bind the DataSet to a DataGrid on a form to display the information retrieved from the database.

Creating the Form

In this exercise, you will create a new application to perform all the operations described in the previous section. The first step is to create the form.

1. Create a Visual C++ Windows Forms Application (.NET) project named DisconnectedApplication.

2. Open Solution Explorer, and look at the Source Files folder. Right- click Form1.h, and select View Code from the context menu to view it in the code editor.

3. Add the following using directive to the end of the list:

   // OleDb data provider definitions using namespace System::Data::OleDb;

4. Add a button to the lower-right corner of the form, giving it the name btnFill and the Text Fill. Also add a DataGrid to the form, giving it the name dgProducts. Set the Text for the form to Products. Size the DataGrid to fill most of the form.

5. Build your program, and fix any compiler errors.

6. Run the program. If all is well, a form should appear as shown in the following figure.

   

Creating and Configuring the Data Adapter

In this exercise, you will add an OleDbDataAdapter and an OleDbConnection to your form class. Using the wizard supplied by Visual Studio .NET, you will configure these objects so that the OleDbDataAdapter queries all the data in the Products table in the database.

1. Continue using the project from the previous exercise.

2. Open the Toolbox, and select the Data tab.

3. Drag an OleDbDataAdapter onto the form. As soon as you drop the control on the form, you will find that the Data Adapter Configuration Wizard is displayed. Click the Next button to take you to the Choose Your Data Connection page. If you have SQL Server running on your machine, you can use the drop-down list to choose a data connection. If you do not have SQL Server, this list will be blank and you’ll have to generate details for the connection. Start by pressing the New Connection button.

4. The Data Link Properties property sheet will be displayed. Click on the Provider tab, and select Microsoft Jet 4.0 OLE DB Provider from the list.

5. Now click the Next button. Enter the path to your database (for example, c:\temp\northwind.mdb) into the top edit control, or use the ellipses (...) button to browse for the file. Now press the Test Connection button to check that the adapter can connect to the database. If it works, you can press the OK button to get back to the Data Adapter Configuration Wizard, where you should see that you now have an entry in the drop-down list.

   

6. Press the Next button to get to the Choose A Query Type page. For Access databases, there’s only one option: Use SQL Statements.

7. Press the Next button again to get to the Generate The SQL Statements page. You can either type the SQL you wish to use, or you can use a graphical query builder. Click the Query Builder button to start the builder.

8. The first step is to specify the tables that we want to use. In this case, there’s only one, so select Products from the list in the Tables page of the Add Table property sheet, and press Add. Now press Close to dismiss the Add Table property sheet.

9. We want to display the product name, the unit price, and the number of units in stock, so select these fields from the list. Make sure that you also select the ProductID field as well. We don’t want to display it, but it’s the primary key for the table and the adapter won’t be able to update the table unless you include it. You will see the SQL being generated as you select fields.

   

10. When you’re done, press OK to dismiss the builder and return to the Generate The SQL Statements page of the Data Adapter Configuration Wizard. Press Next to generate the SQL. You should see a final confirmation page from the wizard.

    

    Note

    You will probably get a warning message box asking whether to include the password string in the code. There’s no password for this database, so the warning does not apply and you can choose either option. If you are accessing a database that does have a password, you might choose not to include it in the code because it will be visible to anyone who examines the compiled assembly.

11. When you exit the wizard, you will see that an OldDbDataConnection has been added to the project, which represents the connection to the database.

12. Build your program, and fix any compiler errors.

Creating and Filling the DataSet

In this exercise, you will add a DataSet to the project. The DataSet will be filled with data when the user clicks the Fill button on the form.

1. Continue using the project from the previous exercise.

2. Open the Toolbox, select the Data tab, and drag a DataSet onto the form. Note that because DataSets are provider independent, there’s only one type; there isn’t an OleDbDataSet and a SqlDataSet.

3. The Add Dataset dialog box will be displayed. Select the Untyped Dataset option, and press OK. A new DataSet component named dataSet1 will be added to the form.

4. Use the property editor to add an event handler for the Click event of btnFill.

5. Implement the button-click event handler method as follows:

   private: System::Void btnFill_Click(System::Object * sender, System::EventArgs * e) { oleDbDataAdapter1->Fill(dataSet1); dgProducts->DataSource = dataSet1->Tables->Item[0]->DefaultView; }

   The first statement tells the data adapter to fill the DataSet. The data adapter will implicitly invoke the SQL statement in its SelectCommand property and load the data into the DataSet.

   The second statement binds the DataSet to the DataGrid, which will cause the DataSet data to be displayed in the DataGrid.

6. Build your program, and fix any compiler errors.

7. Run the program, and click Fill. The DataGrid should display all the data from the Products table in the database.