Lab 6-1: Connecting with a Database

Lab 6-1: Connecting with a Database

In this lab, you will learn to incorporate different kinds of data access into your application. You will learn to read data from a database with a DataReader. You will then learn to use a DataAdapter to fill a DataSet and use that DataSet to update your database. Finally, you will learn to use the XML Designer to create a typed DataSet, and use the Data Form Wizard to create a master-detail data form. The solution to this lab is available on the Supplemental Course Materials CD-ROM in the \Labs\Ch06\Lab 6-1\Solution folder.

Before You Begin

Before you begin this lab, you must have the Microsoft Access 2000 data engine or a more recent engine installed on your computer.

Estimated lesson time: 60 minutes

Exercise 6-1.1: Adding Data Access and Using the DataReader

In this exercise, you will learn to use a DataCommand to return a DataReader, which will allow you to read data directly into your application. You will first create a connection to your database in the Server Explorer.

To add a new connection to an application

1. Open Visual Studio .NET. From the File menu, choose New, Project. Choose a Windows Application project in the language of your choice, and click OK.

2. In the Server Explorer, right-click Data Connections and choose Add Connection. The Data Link Properties dialog box appears.

3. On the Provider tab, choose Microsoft Jet 4 OLE DB Provider. This is the data provider for Microsoft Access 2000. Choose Next to proceed to the Connection tab.

4. Under Select or Enter a Database Name, click the ellipses ( ) to open the Select Access Database dialog box.

5. Browse to The Virtual Doughnut Shop.mdb, and select it. This file is located in the \Labs\Lab 6-1\Ch06 folder. Click Open to select the database.

6. Click Test Connection to verify that your connection is valid. A message box should appear indicating that the connection succeeded.

   NOTE
   If you are not notified that your connection succeeded, go back to Step 5 to verify that you selected the correct database.

7. Click OK. A new connection is added to the Server Explorer.

To add and configure a DataCommand

1. In the Server Explorer, drag the icon representing the database to the form. A new OleDbConnection object representing a connection to your database is added to your application.

2. From the Toolbox Data tab, drag an instance of OleDbCommand to your form. A new OleDbCommand named OleDbCommand1 (oleDbCommand1) is added to your application.

3. Set the properties of OleDbCommand1 (oleDbCommand1) as follows in Table 6.7.

   Table 6-7. Properties of OleDbCommand1 (oleDbCommand1)

   Property	Value
   Connection	oleDbConnection1
   CommandText	SELECT * FROM Customers

   NOTE
   If you are asked whether you want to regenerate parameters, choose Yes.

4. From the Windows Forms tab of the Toolbox, drag a button and a ListBox over to your form. Change the Button1.Text (button1.Text) property to Click to Execute DataReader . You might have to make the button larger for all the text to be visible.

5. In the designer, double-click Button1 (button1) to open the code editor to the default event handler for the Button1.Click (button1.Click) event. Add the following code to retrieve a DataReader and populate the ListBox:

   Visual Basic .NET

   Dim myReader As Data.OleDb.OleDbDataReader Dim CustomerString As String OleDbConnection1.Open() myReader = OleDbCommand1.ExecuteReader() While myReader.Read() = True ' Retrieves the first and last names from Customers and ' concatenates them into a single string CustomerString = myReader(1).ToString() & " " & _ myReader(2).ToString() ' adds the string to the ListBox ListBox1.Items.Add(CustomerString) End While myReader.Close() OleDbConnection1.Close()

   Visual C#

   System.Data.OleDb.OleDbDataReader myReader; string CustomerString; oleDbConnection1.Open(); myReader = oleDbCommand1.ExecuteReader(); while (myReader.Read()) { // Retrieves the first and last names from Customers and // concatenates them into a single string CustomerString = myReader[1].ToString() + " " + myReader[2].ToString(); // adds the string to the ListBox listBox1.Items.Add(CustomerString); } myReader.Close(); oleDbConnection1.Close();

6. Save and test your application. When you click the button, the ListBox should fill with the first and last names of the customers from the Customers table.

Exercise 6-1.2: Retrieving and Updating Data Using DataAdapter Objects and a DataSet

DataReader objects provide rapid database access, but they are read-only and do not allow you to update your data. In this exercise, you will implement two-way data access with a DataAdapter and a DataSet. You will fill the DataSet from the DataAdapter objects, bind the DataSet to a DataGrid control, and perform updates to the database.

To implement two-way data access

1. From the Windows Forms tab of the Toolbox, drag a button to the surface of the form. Change the button s Text property to Click here for Exercise 2. Again, you might need to enlarge the button to make all of the text visible.

2. In the designer, double-click Button2 (button2) to open the default event handler for the Button2.Click (Button2.Click) event. Add the following code to the method:

   Visual Basic .NET

   Dim Exercise2 As New Form2() Exercise2.Show()

   Visual C#

   Form2 Exercise2 = new Form2(); Exercise2.Show();

3. From the File Menu, choose Add New Item. Select Windows Form, and click Open to add a new Windows Form.

4. In the Server Explorer, click the node to the left of your new data connection. The node expands to display its child nodes. Click the node next to the Tables node to display the tables in your database.

5. From the Server Explorer, drag the icons representing the Customers, Items, and Orders tables to the form. An OleDbDataAdapter is created for each table, and an OleDbConnection is added as well, representing a connection to the database.

6. From the Data tab of the Toolbox, drag a DataSet onto the form. The Add Dataset dialog box opens. Choose Untyped Dataset, and click OK.

7. From the Windows Forms tab of the Toolbox, drag two buttons and a DataGrid to the form. Enlarge the datagrid in the designer so that it is readable and set the properties as shown in Table 6.8.

   Table 6-8. Form2 Control Properties

   Property	Value
   Button1.Text (button1.Text)	Get Data
   Button2.Text (button2.Text)	Update Data
   DataGrid1.DataSource (DataGrid1.DataSource)	DataSet1 (dataSet1)

8. In the designer view for Form2, double-click Button1 (button1) to open the code editor to the default event handler for the Button1.Click (button1.Click) event. Add the following code to this method:

   Visual Basic .NET

   ' Calls the Fill method of each DataAdapter in turn and adds the ' resultant data to the dataset OleDbDataAdapter1.Fill(DataSet1) OleDbDataAdapter2.Fill(DataSet1) OleDbDataAdapter3.Fill(DataSet1)

   Visual C#

   // Calls the Fill method of each DataAdapter in turn and adds the // resultant data to the dataset oleDbDataAdapter1.Fill(dataSet1); oleDbDataAdapter2.Fill(dataSet1); oleDbDataAdapter3.Fill(dataSet1);

9. In the designer view for Form2, double-click Button2 (button2) to open the code editor to the default event handler for the Button2.Click (button2.Click) event. Add the following code to this method:

   Visual Basic .NET

   ' Calls the Update method of each DataAdapter in turn and updates ' any changes OleDbDataAdapter1.Update(DataSet1) OleDbDataAdapter2.Update(DataSet1) OleDbDataAdapter3.Update(DataSet1)

   Visual C#

   // Calls the Update method of each DataAdapter in turn and updates // any changes oleDbDataAdapter1.Update(dataSet1); oleDbDataAdapter2.Update(dataSet1); oleDbDataAdapter3.Update(dataSet1);

10. Save and test your application. When the first form opens, click the button labeled Click here for Exercise 2 to open your work from this exercise. When you click the button labeled Get Data, the data will be loaded into the DataGrid.

    You can navigate the DataGrid by expanding the node in the upper-left corner of the DataGrid to display a list of tables. Click on each one to view the contents represented in these tables. You can then make changes in the DataGrid and update the database by clicking the button labeled UpdateDate. Note that changing some values might cause your program to function incorrectly. For example, changing an entry in a primary key field to a value that already exists in that column will cause a runtime exception.

    NOTE
    Although these three tables are related in the database, no relations exist between them in the DataSet, so care must be taken when updating with new values. You will add Data Relations to a typed DataSet in the next exercise.

Exercise 6-1.3: Creating a Typed DataSet Using the XML Designer

In the preceding exercises, you used DataReaders and DataAdapters to retrieve untyped data. In this exercise, you will use the XML Designer to create a typed DataSet that will serve as a template for data in your application.

To add a typed DataSet to your application with the XML Designer

1. From the Project menu, chose Add New Item. The Add New Item dialog opens.

2. In the Add New Item window, choose DataSet, and name your file dsDoughnut.xsd. Click Open, and the XML Designer opens.

3. Drag the Customers table from the Server Explorer onto the XML Designer. A new schema element representing the Customers table is added.

4. Drag the Items table and the Orders table to the designer. Note that a new schema element is created for each table.

You can then add DataRelation objects to your DataSet. You can do this at run time in your code or at design time using the XML Designer. In the next section, you will use the XML Designer to add DataRelation objects to a DataSet schema.

To add DataRelation objects to your schema using the XML Designer

1. From the Toolbox, drag a Relation object to the XML Designer and drop it on the Orders table schema. This opens the Edit Relation dialog box, as shown in Figure 6.7.

   

   Figure 6-7. The Edit Relation dialog box.

2. In the Edit Relation dialog box, set the properties to the values shown in Table 6.9.

   Table 6-9. Relation Properties

   Property	Value
   Parent element	Orders
   Child element	Items
   Key Fields	OrderID
   Foreign Key Fields	OrderID

   Leave the other boxes at their preset values. Click OK to proceed.

   A new Relation object is added to the DataSet schema.

3. Drag another Relation object onto the Orders table schema, and set the properties shown in Table 6.10 in the Edit Relation dialog box.

   Table 6-10. Data Relation Properties

   Property	Value
   Parent element	Customers
   Child element	Orders
   Key Fields	CustomerID
   Foreign Key Fields	CustomerID

   Leave the other boxes at their preset values. Click OK to proceed.

   Another new Relation object is added to the DataSet schema.

4. From the File menu, choose Save All to save your work.

Exercise 6-1.4: Using the Data Form Wizard

Visual Studio .NET provides a Data Form Wizard that simplifies the task of creating forms to view and update data. In this exercise, you will use the Data Form Wizard to create two master-detail forms that will be used to view and update data in the database.

To create the master-detail data forms

1. From the Project menu, choose Add New Item. The Add New Item dialog box appears.

2. Select the Data Form Wizard, and name the new form CustomersOrders. Click Open to continue. The Data Form Wizard begins.

3. Click Next to proceed.

4. Choose the DataSet you want to use as the basis for your form. You have the option of creating a new DataSet or using the DataSet you created in the previous exercise. Choose dsDoughnut, and click Next to continue.

5. The wizard automatically creates the methods necessary to fill and update the form unless you specify otherwise. Make sure the Include an Update Button check box is selected and leave the other two drop-down boxes at their default settings. Click Next to continue.

6. Choose the tables and fields you want to display on this form. From the Master or Single table drop-down menu, choose Customers. In the Detail table drop-down menu, choose Orders. Note that the fields represented in these tables appear in the list beneath the drop-down menu, allowing you to edit the fields included on the form. Verify that all fields are selected in each list, and click Next to continue.

7. The final page allows you to choose your display style. You can choose to display the master records in a DataGrid or in individual controls. If you choose to display your records in individual controls, you can add additional navigation controls through the Data Form Wizard as well. Choose Single record in individual controls for the display style, verify that all additional controls are selected, and click Finish to complete the CustomersOrders data form.

8. Repeat Steps 1 through 7, specifying Orders as the Master table and Items as the detail table. Name this form OrdersItems.

9. In the Solution Explorer, right-click Form2 and choose View Designer. The designer for Form2 opens.

10. From the Toolbox, drag two buttons onto the form. Name the buttons btnViewCustomers and btnViewOrders, and change their text properties to View Customers and View Orders, respectively. You might need to rescale Form2 or the other components to make everything fit and be easily readable.

11. Double-click btnViewCustomers to open the btnViewCustomers.Click event handler. Add the following lines of code to this method:

    Visual Basic .NET

    Dim myCustomersOrders As New CustomersOrders() myCustomersOrders.Show()

    Visual C#

    CustomersOrders myCustomersOrders = new CustomersOrders(); myCustomersOrders.Show();

12. Create a similar method to display the OrdersItems form when btnViewOrders is clicked.

13. Save and build your project.

Connecting the data form to the database

The Data Form Wizard creates a skeleton data access form for your application and adds logic to facilitate navigation of records, but you must still fill your DataSet with data to be able to browse your database. In this section, you will add data adapters to your forms that will manage filling the DataSet and updating the database.

1. In the Solution Explorer, right-click CustomersOrders and choose View Designer. The designer opens.

2. From the Server Explorer, drag the Customers table onto the designer. A new OleDbDataConnection and OleDbDataAdapter are added to your form.

3. From the Server Explorer, drag the Orders table and the Items table onto the designer. Note that a new OleDbDataAdapter is added for each table, but a new connection is not added.

4. Rename the controls as in Table 6.11.

   Table 6-11. Data Object Names

   Old Name	New Name
   OleDbDataConnection1 (oleDbDataConnection1)	dcDoughnut
   OleDbDataAdapter1 (oleDbDataAdapter1)	daCustomers
   OleDbDataAdapter2 (oleDbDataAdapter2)	daOrders
   OleDbDataAdapter3 (oleDbDataAdapter3)	daItems

5. In the designer, double-click the Load button to open the btnLoad.Click event handler. Insert the following code to fill the DataSet:

   Visual Basic .NET

   ' The data adapters fill the instance of the dsDoughnut dataset that ' was added by the data form wizard daCustomers.Fill(objdsDoughnut) daOrders.Fill(objdsDoughnut) daItems.Fill(objdsDoughnut)

   Visual C#

   // The data adapters fill the instance of the dsDoughnut dataset that // was added by the data form wizard daCustomers.Fill(objdsDoughnut); daOrders.Fill(objdsDoughnut); daItems.Fill(objdsDoughnut);

6. In the Solution Explorer, right-click CustomersOrders and choose View Designer. The designer opens. Double-click the Update button to open the btnUpdate.Click event handler. Insert the following code:

   Visual Basic .NET

   ' The data adapters update the database with the current records daCustomers.Update(objdsDoughnut) daOrders.Update(objdsDoughnut) daItems.Update(objdsDoughnut)

   Visual C#

   // The data adapters update the database with the current records daCustomers.Update(objdsDoughnut); daOrders.Update(objdsDoughnut); daItems.Update(objdsDoughnut);

7. Repeat Steps 1 through 6 for the OrdersItems pages.

8. Save and test your work.