JoelDixon.com

"Pornography ... I shake my fist at it"

Blog
General
Travel
Hockey
Technical
Gaming
Links
About


Blog Archive
Current Blogs
November 2008 (1)
October 2008 (3)
September 2008 (1)
August 2008 (4)
July 2008 (3)
June 2008 (1)
May 2008 (1)
April 2008 (3)
March 2008 (3)
February 2008 (3)
January 2008 (1)
2007 (67)
2006 (23)
2005 (32)
December (1)
November (2)
October (3)
September (3)
August (1)
July (5)
June (2)
April (1)
March (1)
February (3)
January (10)
2004 (10)

Blog Tags
General
DVDs (2)
Funny (6)
Gadgets (5)
Hawt (1)
Movies (10)
Music (5)
Pets (3)
Recipe (14)
TV Shows (1)
Web (20)
Travel
Calgary (2)
Edmonton (12)
New York (7)
Niagara Falls (2)
Pittsburgh (5)
Washington (4)
Hockey
Pittsburgh Penguins (17)
Technical
.NET (2)
Java (4)
Software (3)
Work (2)
Gaming
Commodore 64 (1)
Master System (1)
Xbox 360 (13)

GamerTag


The rarely updated blog of Joel Dixon

Viewing blogs posted in April, 2005

Thursday, April 07, 2005

ADO.NET Strongly Typed Relationships

# Posted by Joel Dixon at 07/04/2005 01:55:00
Updated by Joel Dixon at 21/09/2007 04:03:35 - added comment captcha


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:

Code:

DataSet1.Tables["Customers"].Rows[0]

you can use:

Code:

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:

Code:

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:

Code:

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!



Comments (5) | Add Comment

Blog Search

Advanced Search


Recent Blogs
Street Fighter: The Later Years
Stop looking at me, swan!
Trivial
Awesome Blossom!
Not Will Ferrell Trivia
Timmy didn't think about the Mini-Feed
Thongs and Superglue
The Deep Voiced Knight
Jonathan Roy charged with assault
Superman x 4

Feed
Subscribe to feed Blog Entries

Add to Google
Add to My Yahoo!
Add to Netvibes

Recent Comments
What a waste
posted 4 months ago by joeldixon
I'm not 100% sure - I may have still been using Windows 98 back then (very old computer). I'm ... link

What a waste
posted 4 months ago by Tomek
U haven't said what win version u use, but my pentium3 with XP SP2 installed worked just fine after about ... link

The Deep Voiced Knight
posted 4 months ago by joeldixon
You're right - Christian Bale is a good Bruce Wayne - and if he toned down the scary voice he'd ... link

The Deep Voiced Knight
posted 4 months ago by thefurey
haha - why did delta cut her hair? i actually dont mind bale as batman...well, actually, he's a very ... link

The Deep Voiced Knight
posted 4 months ago by joeldixon
Yeah - for most of the movie I forgot that it was him. Then again, I didn't even notice ... link


Comment Standings
1. the man with no name (45)
2. Hoff (39)
3. Gav (27)
4. Brad (20)
5. Hitman (16)
6. Eryc-Ads (13)
7. Deep Lurker (6)
8. Dieter (4)
9. thefurey (4)
10. Zelks (4)

About This Site | Contact Me