FunctionX Practical Learning Logo

Master-Detail Navigation
 

Description

In this example, we will user two tables involved in a master/detail relationship. We will use navigation buttons to move through records of the first table. When we move from one record to another, we will change the records that display in a DataGrid control that holds records of the child table. To do this, we will use a DataView control to filter records that fit the current record of the parent table.

Practical LearningPractical Learning: Creating the Database
 
  1. Create a new Windows Forms Application named AssetsInventory1
  2. As done in our introduction, from Visual Studio .NET and using Server Explorer, open the server that has your SQL Server installation
  3. Create a new database named RecreationCenter
  4. Expand the RecreationCenter database and its Tables node. Right-click the Tables node and click New Table
  5. Create the following columns:
     
    Column Name Data Type Other Properties
    AssetCategoryID int Primary Key: True
    Identity: Yes
    AssetCategory varchar Length: 32
    Allow Nulls: cleared
  6. Save the new table as AssetCategories and close it
  7. From the Tables node, double-click AssetCategories and complete it with the following fields:
     
    AssetCategoryID AssetCategory
    1 Server
    2 Desktop Computer
    3 Laptop
    4 Printer
    5 Digital Camera
  8. Create another table with the following columns:
     
    Column Name Data Type Other Properties
    AssetID int Primary Key: True
    Identity: Yes
    AssetCategoryID int  
    Make varchar Length: 20
    Model varchar Length: 40
    YearAcquired varchar Length: 10
    Price varchar Length: 20
    Description varchar Length: 50
  9. Save the table as CompanyAssets
  10. On the Table toolbar, click the Relationships button
  11. In the Properties Pages, click New
  12. Under the Primary Key Table combo box, make sure that AssetCategories is selected. In the first combo box under it, select AsseCategoryID
  13. In the combo box under Foreign Key Table, make sure CompanyAssets is selected. In the combo box under CompanyAssets, select AssetCategoryID
  14. Click the Cascade Update Related Fields and the Cascade Delete Related Records check boxes
     
  15. Click Close
  16. Close the table. When asked to save, click Yes
  17. From the Tables node, double-click CompanyAssets and complete it with the following fields:
     
    ID AssCatID Make Model Year Price Description
    1 2 IBM NetVista M42 2002 1035.00  
    2 4 HP Laser Jet 4200dtn 2002 1950.96 B/W Laser
    3 2 HP d325 2000 650.85 Microtower AMD Athlon
    4 1 Dell PowerEdge 4600 2003 3450.95  
    5 2 IBM ThinkCentre 250 2002 1055.55  
    6 2 Compaq Presario d220 2000 850.55 Business Workstation
    7 1 Dell PowerEdge 2600 2000 3195.55 On Lease
    8 3 Gateway 200XL 2002 2095.00 Business notebook
    9 3 Toshiba Portege 2004 2300.00 Tablet PC Dockable
    10 4 Xerox Phaser 8200 2002 1250.55 Color Laser
    11 5 Olympus C-50 2002 450.75  
    12 2 Sony RZ50C 2004 1250.75  
  18. Close the table
  19. Design the form as follows:
     
    Control Name Text Other Properties
    Label   Asset Category ID:  
    TextBox txtAssetCategoryID   ReadOnly: True
    TextAlign: Right
    Anchor: Top, Left, Right
    CaptionText: Assets of this Category
    Label   Asset Category:  
    DataGrid grdAssets   AutoForm: Colorful 2
    Button btnFirst | <  
    Button btnPrevious <<  
    Button btnNext >>  
    Button btnLast > |  
    Button btnClose Close  
  20. From the Server Explorer section, drag the Tables node from the RecreationCenter database and drop it on the form. Two SqlDataAdapter (sqlDataAdapter1 and sqlDataAdapter2) and one SqlConnection (sqlConnection1) icons should have been added to your project
  21. On the main menu, click Data -> Generate Dataset...
  22. Change the name of the New dataset to dsAssets
     
  23. Click OK
  24. On the form, click the txtAssetCategoryID text box to select it. In the Properties window, use the DataBindings property to bind its Text to 
    dsAssets1 - AssetCategories. AssetCategoryID
  25. On the form, click the txtAssetCategory text box to select it. In the Properties window, use the DataBindings property to bind its Text to 
    dsAssets1 - AssetCategories. AssetCategory
  26. Double-click an empty area of the form to access the form's Load event
  27. Return to the form and double-click all buttons from left to right
  28. Implement their events as follows: 
    private: System::Void Form1_Load(System::Object *  sender, System::EventArgs *  e)
		 {
		this->sqlDataAdapter1->Fill(this->dsAssets1);
			 this->sqlDataAdapter2->Fill(this->dsAssets1);
		 }

private: System::Void btnFirst_Click(System::Object *  sender, System::EventArgs *  e)
		 {
 this->BindingContext->get_Item(this->dsAssets1, S"AssetCategories")->Position = 0;
		 }

private: System::Void btnPrevious_Click(System::Object *  sender, System::EventArgs *  e)
		 {
 this->BindingContext->get_Item(this->dsAssets1, S"AssetCategories")->Position =
 this->BindingContext->get_Item(this->dsAssets1, S"AssetCategories")->Position - 1;
		 }

private: System::Void btnNext_Click(System::Object *  sender, System::EventArgs *  e)
		 {
 this->BindingContext->get_Item(this->dsAssets1, S"AssetCategories")->Position = 
 this->BindingContext->get_Item(this->dsAssets1, S"AssetCategories")->Position + 1;
		 }

private: System::Void btnLast_Click(System::Object *  sender, System::EventArgs *  e)
		 {
 this->BindingContext->get_Item(this->dsAssets1, S"AssetCategories")->Position =
 this->BindingContext->get_Item(this->dsAssets1, S"AssetCategories")->Count - 1;
		 }

private: System::Void btnClose_Click(System::Object *  sender, System::EventArgs *  e)
		 {
			 Close();
		 }
  29. Press Ctrl + F5 to test the application and navigate to different records
  30. Close the form
  31. To display the list of assets for each category in the data grid, from the Data section of the Toolbox, click DataView and click the form
  32. Change the DataView's Name to dvwAssets and set its Table to dsAssets1.CompanyAssets
  33. Set the DataSource of the DataGrid control to dvwAssets
  34. Double-click an empty area on the form and change the file as follows: 
    private: System::Void Form1_Load(System::Object *  sender, System::EventArgs *  e)
			 {
				 this->sqlDataAdapter1->Fill(this->dsAssets1);
				 this->sqlDataAdapter2->Fill(this->dsAssets1);

 this->dvwAssets->RowFilter = String::Concat(S"AssetCategoryID = ", this->txtAssetCategoryID->Text);
			 }

private: System::Void btnFirst_Click(System::Object *  sender, System::EventArgs *  e)
		 {
			 this->BindingContext->get_Item(this->dsAssets1, S"AssetCategories")->Position = 0;

 this->dvwAssets->RowFilter = String::Concat(S"AssetCategoryID = ", this->txtAssetCategoryID->Text);
		 }

private: System::Void btnPrevious_Click(System::Object *  sender, System::EventArgs *  e)
		 {
			 this->BindingContext->get_Item(this->dsAssets1, S"AssetCategories")->Position =
				 this->BindingContext->get_Item(this->dsAssets1, S"AssetCategories")->Position - 1;

 this->dvwAssets->RowFilter = String::Concat(S"AssetCategoryID = ", this->txtAssetCategoryID->Text);
		 }

private: System::Void btnNext_Click(System::Object *  sender, System::EventArgs *  e)
		 {
			 this->BindingContext->get_Item(this->dsAssets1, S"AssetCategories")->Position = 
				 this->BindingContext->get_Item(this->dsAssets1, S"AssetCategories")->Position + 1;

this->dvwAssets->RowFilter = String::Concat(S"AssetCategoryID = ", this->txtAssetCategoryID->Text);
		 }

private: System::Void btnLast_Click(System::Object *  sender, System::EventArgs *  e)
		 {
			 this->BindingContext->get_Item(this->dsAssets1, S"AssetCategories")->Position =
				 this->BindingContext->get_Item(this->dsAssets1, S"AssetCategories")->Count - 1;
             
 this->dvwAssets->RowFilter = String::Concat(S"AssetCategoryID = ", this->txtAssetCategoryID->Text);
		 }
  35. Execute the application to test it
  36. Close the form
 
Home Copyright © 2004-2012, FunctionX