Thursday, April 07, 2005
ADO.NET Strongly Typed Relationships
It's been over a month since my last blog - but that's OK - I'm sure no-one will mind!
I've decided to add a little technical advice about accessing data with Strongly Typed DataSets in ADO.NET. Reason being, I spent over an hour looking for this information before I finally tracked it down on someone's blog. After my post, it will double the amount of blogs with this information ;)
In my ASP.NET WebService, I like to use strongly typed DataSets to access my data with ADO.NET, it's a whole lot cleaner that way. For my example, let's say we have a Customers table, and an Orders table. Customers are identified by their CustomerID, and orders by their OrderID (yes, I'm using Northwind). The Orders table also has the CustomerID that made the order (a one to many relationship). Defining the strongly typed DataSet for this in Visual Studio.NET is quite easy:
1. Right-click your Project in the Solution Explorer and select Add > Add New Item...
2. Select "Data Set" from the Templates list, and type the name for your item (CustomersDataSet.cs).
3. In the Server Explorer, navigate to the Northwind database tables. Drag across the Customers and Orders tables.
4. Select the Customers table and select Schema > Add > New Relation... from the File Menu.
5. This is where your relationship is defined. Change the name to CustomersOrders, and change the Child element to Orders. Clicking OK will then create the relationship for you.
Now that you have the strongly typed DataSet, you can directly refer to column names. For example, instead of:
you can use:
That was all good - but I wanted to look at the Orders that are assigned to a Customer (as per the relationship I added above). Most examples were telling me to do something like this:
This gets the job done - but we're back to using things like orderRow["OrderID"] instead of orderRow.OrderID. The disadvantage to using GetChildRows is that it returns an array of DataRow's - not Orders. A way to achieve the above code using your strongly typed relationship is as follows:
When the relationship was created above, it added a GetOrdersRows() method on the CustomersRow object. It may not seem like much - but having the strongly typed OrderRow can help (well, it helped me).
After doing a quick search on GetOrdersRows() (to see if I had missed any examples of this) - I found a good ADO.NET tutorial. I wish I had have found that a few hours ago!
I've decided to add a little technical advice about accessing data with Strongly Typed DataSets in ADO.NET. Reason being, I spent over an hour looking for this information before I finally tracked it down on someone's blog. After my post, it will double the amount of blogs with this information ;)
In my ASP.NET WebService, I like to use strongly typed DataSets to access my data with ADO.NET, it's a whole lot cleaner that way. For my example, let's say we have a Customers table, and an Orders table. Customers are identified by their CustomerID, and orders by their OrderID (yes, I'm using Northwind). The Orders table also has the CustomerID that made the order (a one to many relationship). Defining the strongly typed DataSet for this in Visual Studio.NET is quite easy:
1. Right-click your Project in the Solution Explorer and select Add > Add New Item...
2. Select "Data Set" from the Templates list, and type the name for your item (CustomersDataSet.cs).
3. In the Server Explorer, navigate to the Northwind database tables. Drag across the Customers and Orders tables.
4. Select the Customers table and select Schema > Add > New Relation... from the File Menu.
5. This is where your relationship is defined. Change the name to CustomersOrders, and change the Child element to Orders. Clicking OK will then create the relationship for you.
Now that you have the strongly typed DataSet, you can directly refer to column names. For example, instead of:
DataSet1.Tables["Customers"].Rows[0]
you can use:
DataSet1.Customers[0]
That was all good - but I wanted to look at the Orders that are assigned to a Customer (as per the relationship I added above). Most examples were telling me to do something like this:
CustomersDataSet.CustomerDataRow customer = myDataSet.Customers.FindCustomerByID(1);
if (customer != null)
{
// Print out order numbers
foreach (DataRow orderRow in customer.GetChildRows("CustomerOrders"))
{
Console.WriteLine(orderRow["OrderID"]);
}
}
This gets the job done - but we're back to using things like orderRow["OrderID"] instead of orderRow.OrderID. The disadvantage to using GetChildRows is that it returns an array of DataRow's - not Orders. A way to achieve the above code using your strongly typed relationship is as follows:
CustomersDataSet.CustomerDataRow customer = myDataSet.Customers.FindCustomerByID(1);
if (customer != null)
{
// Print out order numbers
foreach (CustomersDataSet.OrderRow orderRow in customer.GetOrdersRows())
{
Console.WriteLine(orderRow.OrderID);
}
}
When the relationship was created above, it added a GetOrdersRows() method on the CustomersRow object. It may not seem like much - but having the strongly typed OrderRow can help (well, it helped me).
After doing a quick search on GetOrdersRows() (to see if I had missed any examples of this) - I found a good ADO.NET tutorial. I wish I had have found that a few hours ago!
