Lab Creating Data Set Objects

In this lab you will create typed and untyped DataSet objects.

► Exercise 1: Creating a DataSet with the DataSet Designer

The DataSet Designer is a design-time tool that assists in the creation of typed DataSet objects by allowing you to drag and drop database tables from Server Explorer onto the design surface. As you drop tables on the surface, they are added to the DataSet as typed objects that make programming as simple as writing the table and column names you want to access.

1. Create a Windows application and name it DataSetDesignerExample.

2. From the Project menu, select Add New Item.

3. Select the DataSet template and name it NorthwindDataSet.xsd.

4. Navigate to the Customers table in Server Explorer and drag it onto the design surface.

5. Navigate to the Orders table in Server Explorer and drag it onto the design surface.

After dropping the Customers and Orders tables onto the DataSet Designer, the design surface should look similar to Figure 7-1.

DatasetDesignerEHampleVB - Microsoft Visual Studio

JffJxJ

File Edit View Project Build Debug Data Test Tools

Window Community Help

«1-na J, | *) • ► Debug

• " » W, - - .

NorthwindDataset.xsd* Forml.vb [Design] |__X

Customers (ft]

rn; Orders ^^^^^HH

T CustomerlD

H OrderlD

CompanyName

CustomerlD

ContactName

EmployeelD

ContactTitle

OrderDate

Address

RequiredDate

City

ShippedDate

Region

ShipVia

PostalCode

Freight

Country

ShipName

Phone

ShipAddress

Fax

ShipCity

CustomersTableAdapter (ft)

ShipRegion

|1äl Fill, Get Data ()

ShipPostalCode ShipCountry

Fill.GetData 0

Error Listj

Figure 7-1 Customers and Orders DataTable objects and TableAdapter objects as seen in the DataSet Designer

6. Build the project before moving on to the next step.

7. Drag a ListBox onto Form1 and name it CustomersListBox.

8. Drag a button onto Form1 and set the following properties:

□ Name = GetCustomersButton

9. Double-click the Get Customers button and add the following code to the GetCustomersButton_Click event handler:

' Instantiate a Northwind typed dataset. Dim NorthwindDataSet1 As New NorthwindDataSet

' Instantiate a CustomersTableAdapter.

Dim CustomersTableAdapter1 As New NorthwindDataSetTableAdapters.CustomersTableAdapter

' Call the default Fill method to load all customers into the Customers DataTable. CustomersTableAdapter1.Fill(NorthwindDataSet1.Customers)

' Loop through the rows in the Customers table and add the value from the CompanyName column

' to the ListBox.

For Each NWCustomer As NorthwindDataSet.CustomersRow In NorthwindDataSet1.Customers.Rows CustomersListBox.Items.Add(NWCustomer.CompanyName)

// Instantiate a Northwind typed dataset.

NorthwindDataSet NorthwindDataSet1 = new NorthwindDataSet ();

// Instantiate a CustomersTableAdapter.

NorthwindDataSetTableAdapters.CustomersTableAdapter CustomersTableAdapter1 = new NorthwindDataSetTableAdapters.CustomersTableAdapterO;

// Call the default Fill method to load all customers into the Customers DataTable. CustomersTableAdapter1.Fill(NorthwindDataSet1.Customers);

// Loop through the rows in the Customers table and add the value from the CompanyName column

foreach (NorthwindDataSet.CustomersRow NWCustomer in NorthwindDataSet1.Customers.Rows) {

CustomersListBox.Items.Add(NWCustomer.CompanyName);

Run the application and click the Get Customers button. Verify that the CompanyName from each customer is displayed in the CustomersListBox similar to Figure 7-2.

5| Dataset Designer Example

Alfreds Futterkiste

Ana Trujillo Emparedados y helados

Antonio Moreno Taquería

Around the Horn

Berglunds snabbköp

Blauer See Delikatessen

Blondesddsl père et fils

Bólido Comidas preparadas

Bon app'

Bottom-Dollar Markets B's Beverages Cactus Comidas para llevar Centro comercial Moctezuma Chop-suey Chinese Comércio Mineiro Consolidated Holdings d

Figure 7-2 The CompanyName from all customers appearing in the CustomersListBox after clicking the Get Customers button

► Exercise 2: Creating a Typed DataSet with the Data Source Configuration Wizard

This exercise provides instructions for creating a strongly typed DataSet using the Data Source Configuration Wizard.

1. Create a Windows application and name it DataSourceWizardExample.

2. Start the Data Source Configuration Wizard by selecting Add New Data Source from the Data menu.

NOTE Data menu

If the Data menu is not available, open the form in Design view.

3. Leave the default selection of Database on the Choose A Data Source Type page, as shown in Figure 7-3. Then, click Next.

Figure 7-3 The Choose a Data Source Type page of the Data Source Configuration Wizard

Figure 7-3 The Choose a Data Source Type page of the Data Source Configuration Wizard

The list box displays the available data connections from Server Explorer. Select a connection to the Northwind sample database or create a new data connection on the Choose Your Data Connection page, as shown in Figure 7-4. Then, click Next. If prompted, click Yes to add the database to your project.

Figure 7-4 The Choose Your Data Connection page of the Data Source Configuration Wizard

5. After selecting the desired data connection, you are given the option of saving it to the application configuration file. Leave the default option selected, as shown in Figure 7-5. Then, click Next.

Figure 7-5 The Save the Connection String to the Application Configuration File page of the Data Source Configuration Wizard

6. Expand the Tables node and select the Customers table on the Choose Your Database Objects page, as shown in Figure 7-6.

Figure 7-6 The Choose Your Database Objects page of the Data Source Configuration Wizard

7. Click Finish to complete the wizard and add the typed DataSet to your project.

After completing the wizard, the data source is available in the Data Sources window, which enables the quick construction of data-bound forms.

8. With Form1 in Design view, select Show Data Sources from the Data menu.

9. Drag the Customers node from the Data Sources window onto Form1, as shown in Figure 7-7.

Figure 7-7 Form1 in the Visual Studio IDE after dropping the Customers node from the Data Sources window

Visual Studio adds code to fill the Customers DataTable to the form as a result of dropping the Customers table from the Data Sources window.

10. Run the application and verify that the Customers table data appears on the form, as shown in Figure 7-8.

3 Data Source Wizard Example VB

3 Data Source Wizard Example VB

J Customer! D

CompanyName

ContactName

ContactTitle

Address —

ALFKI

Alfreds Futterkiste

Maria Anders

Sales Represent...

Obere Str. !_

ANATR

Ana Trujillo Empa...

Ana Trujillo

Owner

Avda. de le

ANTON

Antonio Moreno...

Antonio Moreno

Owner

Mataderos

AROUT

Around the Horn

Thomas Hardy

Sales Represent...

120 Hanov

BERGS

Berglunds snabb...

Christina Berglund

Order Administrator

Berguvsvä;

BLAUS

Blauer See Delik...

H anna Moos

Sales Represent...

Forsterstr. E

BLONP

Blondesddsl père...

Frédérique Citeaux

Marketing Manager

24, place K

BGLID

Bólido Comidas p...

Martin Sommer

Owner

Zf Araquil,

BO NAP

Bon app'

Laurence Lebihan

Owner

12, rue des

BOTTM

Bottom-Dollar Ma...

Elizabeth Lincoln

Accounting Man...

23 Tsawas w

jl

i

►r

Figure 7-8 The Customers data displayed on Form1

► Exercise 3: Configuring Untyped DataSet Objects

Create untyped DataSet objects by dragging DataSet objects from the Toolbox onto a form.

1. Create a Windows application and name it UntypedDataSet.

2. From the Data section of the Toolbox, drag a DataSet object onto Form1.

3. In the Add DataSet dialog box, select Untyped DataSet and click OK.

4. Drag a DataGridView onto the form.

5. Select the DataSet1 instance in the component tray and navigate to the Tables property in the Properties window. Click the ellipsis in the Tables property to open the Tables Collection Editor.

6. Add a table and set its Name and TableName properties to Categories.

7. Select the Columns property and click the ellipsis to open the Columns Collection Editor.

8. Add a column and set the following properties:

□ AutoIncrement = True

□ ColumnName = CategoryID

□ DataType = System.Int32

9. Add a second column and set the following properties:

□ ColumnName = CategoryName

10. Close the Columns Collection Editor, select the Constraints property, and add a Unique constraint.

11. Select the CategoryID column and the Primary key check box and click OK.

12. Close the Constraints Collection Editor and the Tables Collection Editor.

13. Add a button to the form and set the following properties:

□ Name = FillDataSetButton

14. Create a Form1_Load event handler and add the following code: ' VB

DataGridView1.DataSource = DataSet1.Tab1es("Categories") DataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect

dataGridViewl.DataSource = dataSet1.Tab1es["Categories"]; dataGridViewl.SelectionMode = DataGridViewSelectionMode.FullRowSelect;

15. Create a FillDatasetButton_Click event handler and add the following code to it:

Dim newRow As DataRow = DataSet1.Tab1es("Categories").NewRow() newRow.Item("CategoryName") = "Beverages" DataSet1.Tab1es("Categories").Rows.Add(newRow)

Dim newRow2 As DataRow = DataSet1.Tab1es("Categories").NewRow() newRow2.Item("CategoryName") = "Condiments" DataSet1.Tab1es("Categories").Rows.Add(newRow2)

Dim newRow3 As DataRow = DataSet1.Tab1es("Categories").NewRow() newRow3.Item("CategoryName") = "Seafood" DataSet1.Tab1es("Categories").Rows.Add(newRow3)

DataRow newRow = dataSet1.Tab1es["Categories"].NewRow(); newRow["CategoryName"] = "Beverages"; dataSet1.Tab1es["Categories"].Rows.Add(newRow);

DataRow newRow2 = dataSet1.Tab1es["Categories"].NewRow(); newRow2["CategoryName"] = "Condiments"; dataSet1.Tab1es["Categories"].Rows.Add(newRow2);

DataRow newRow3 = dataSet1.Tab1es["Categories"].NewRow(); newRow3["CategoryName"] = "Seafood"; dataSet1.Tab1es["Categories"].Rows.Add(newRow3);

16. With Form1 in Design view, select DataSet1 in the component tray.

17. From the Tables property in the Property window, open the Tables Collection Editor and add another table to the DataSet.

18. Set its Name and TableName properties to Products.

19. Add a column and set the following properties:

□ AutoIncrement = True

□ ColumnName = ProductID

□ DataType = System.Int32

20. Add a column and set the following properties:

□ ColumnName = ProductName

□ DataType = System.String

21. Add a column and set the following properties:

□ ColumnName = CategoryID

□ DataType = System.Int32

22. Click Close. Select the Constraints property for the Products table and add a Unique constraint.

23. Select the ProductID column and the Primary key check box and click OK.

24. Add a Foreign Key Constraint and set the following:

□ Parent table = Categories

□ Key columns = CategoryID

□ Foreign Key columns = CategoryID

26. Close the Collection Editors, and then close the Table Collection Editor.

27. Select DataSet1 in the component tray and add a DataRelation by clicking the ellipsis in the Relations property of DataSet1.

28. Click Add in the Relations Collection Editor. Set the following in the Relations dialog box:

□ Name = CategoriesProducts

□ Parent table = Categories

□ Key columns = CategoryID

□ Foreign Key columns = CategoryID

29. Close the dialog boxes.

30. Add the following code to the bottom of the FilllDatasetButton_Click event handler:

Dim newRow4 As DataRow = DataSet1.Tab1es("Products").NewRowO newRow4.Item("CategoryID") = 1 newRow4.Item("ProductName") = "Chai" DataSet1.Tab1es("Products").Rows.Add(newRow4)

Dim newRow5 As DataRow = DataSet1.Tab1es("Products").NewRow() newRow5.Item("CategoryID") = 2 newRow5.Item("ProductName") = "Aniseed Syrup" DataSet1.Tab1es("Products").Rows.Add(newRow5)

Dim newRow6 As DataRow = DataSet1.Tab1es("Products").NewRow() newRow6.Item("CategoryID") = 3 newRow6.Item("ProductName") = "Ikura" DataSet1.Tab1es("Products").Rows.Add(newRow6)

Dim newRow7 As DataRow = DataSet1.Tab1es("Products").NewRow() newRow7.Item("CategoryID") = 1 newRow7.Item("ProductName") = "Chang" DataSet1.Tab1es("Products").Rows.Add(newRow7)

Dim newRow8 As DataRow = DataSet1.Tab1es("Products").NewRow() newRow8.Item("CategoryID") = 2

newRow8.Item("ProductName") = "Chef Anton's Gumbo Mix" DataSet1.Tab1es("Products").Rows.Add(newRow8)

Dim newRow9 As DataRow = DataSet1.Tab1es("Products").NewRow() newRow9.Item("CategoryID") = 3 newRow9.Item("ProductName") = "Boston Crab Meat" DataSet1.Tab1es("Products").Rows.Add(newRow9)

DataRow newRow4 = dataSet1.Tab1es["Products"].NewRow(); newRow4["CategoryID"] = 1; newRow4["ProductName"] = "Chai"; dataSet1.Tab1es["Products"].Rows.Add(newRow4);

DataRow newRow5 = dataSet1.Tab1es["Products"].NewRow(); newRow5["CategoryID"] = 2; newRow5["ProductName"] = "Aniseed Syrup"; dataSet1.Tab1es["Products"].Rows.Add(newRow5);

DataRow newRow6 = dataSet1.Tab1es["Products"].NewRow(); newRow6["CategoryID"] = 3; newRow6["ProductName"] = "Ikura"; dataSet1.Tab1es["Products"].Rows.Add(newRow6);

DataRow newRow7 = dataSet1.Tab1es["Products"].NewRow(); newRow7["CategoryID"] = 1;

newRow7["ProductName"] = "Chang";

dataSet1.Tables["Products"].Rows.Add(newRow7);

DataRow newRow8 = dataSet1.Tables["Products"].NewRow(); newRow8["CategoryID"] = 2;

newRow8["ProductName"] = "Chef Anton's Gumbo Mix"; dataSet1.Tables["Products"].Rows.Add(newRow8) ;

DataRow newRow9 = dataSet1.Tables["Products"].NewRow(); newRow9["CategoryID"] = 3; newRow9["ProductName"] = "Boston Crab Meat"; dataSet1.Tables["Products"].Rows.Add(newRow9);

31. Create an event handler for the DataGndView1_CettDoubleClkk event and add the following code:

' Get the CategoryID of the selected row

Dim Category As Integer = CInt(DataGridView1.Se1ectedRows(0).Ce11s("CategoryID").Va1ue) ' Get the underlying DataRow that is selected

Dim rows() As DataRow = DataSet1.Tab1es("Categories").Se1ect("CategoryID = " & Category)

' Use the GetChildRows method to navigate the relationship and return the related records Dim ProductList As String = ""

For Each r As DataRow In rows(0).GetChi1dRows("CategoriesProducts")

ProductList += r.Item("ProductName").ToString & Environment.NewLine

Next

' Display the products in a message box MessageBox.Show(ProductList)

// Get the CategoryID of the selected row int Category = (int)dataGridView1.Se1ectedRows[0].Ce11s["CategoryID"].Va1ue; // Get the underlying DataRow that is selected

DataRow[] rows = dataSet1.Tab1es["Categories"].Se1ect("CategoryID = " + Category);

// Use the GetChildRows method to navigate the relationship // and return the related records string ProductList = "";

foreach (DataRow r in rows[0].GetChi1dRows("CategoriesProducts")) {

ProductList += r["ProductName"].ToString() + Environment.NewLine; }

// Display the products in a message box MessageBox.Show(ProductList);

32. Run the application and click the Fill Dataset button.

33. Now double-click one of the categories in the grid to open a message box displaying the related products.

0 0

Post a comment