Database Design column, "Boost Performance With a DataRelation," by Paul Delcogliano
Visual Studio Magazine, February 2003

Follow the steps below to create the sample application on your pc...


- Extract the CreateStoredProcs.sql file from the CodeAndSQLScript.zip file and run the file using the Northwind SQL Server database, from Query Analyzer. This will create the stored procs needed by the sample application.

- Create a new folder on your PC, name it DataRelationCode

- Copy the following files from should be extracted from the CodeAndSQLScript.zip file to the DataRelationCode folder
	OrderHierarchy.*
	cNorthwindData.*
	DRSample.*
	AssemblyInfo.vb

- Open the project from the DataRelationCode folder in Visual Studio.NET by double clicking the DRSample.sln file.

- Open the OrderHierarchy.vb file and edit the connection string constant. Look for the following line of code:

Const DB_CONN As String = "Data Source=localhost; initial catalog=northwind;user id=sa;password=;"

You will have to replace the data source, user id and password with values that are specific to your instance of SQL Server.


- Click the "Run" button from the VS.Net IDE to execute the code.

- The application opens the Northwind database, and displays all orders in the treeview. When you click on an order id in the treeview, details about the order and it's related Order Detail child records are displayed. Notice that whenever you select a different Order ID from the treeview, the application does not requery the database. All data is clientside. Also notice that there isn't any special code to filter child records when a new Order ID is selected. All "filtering" is handled by the relationship and data binding.

- The app is a sample that allows you to play with the various options and settings related to using a DataRelation and Constraint objects. 

- When adding new orders, no data is committed to the database until you click the "Save" button. 

- When you delete an order, data is committed to the database everytime you click the "Delete Order" button.

- Here's some things for you to try...

- Add multiple order records by clicking on the "Add Order" button. Do this with "Auto Increment" checked. Then click save. Notice how multiple records are saved to the database.

- Try to add multiple order records following the same steps as above, however this time uncheck the "Auto Increment" check box. Errors will be thrown because you are violating the UniqueConstraint rule on the OrderID column -- adding multiple Orders with the same OrderID value (NULL).

- Try deleting orders by clicking on the "Delete Order" button. Try this a few times, each with different "Delete Rules" selected and turning on and off "EnforceConstraints" to get a feel for how each of these options affects your code.

