Populating multiple tables in typed dataset

Question

I'll try to simplify my problem as much, as possible.

Feel free to comment and correct my English. Hope you can understand me.

My main question is: Is there any simple and "automated" way, to fill a table in dataset only with rows related to data in other table?

Let's say, we have database with following schema:

Now, i'm trying to make the same thing with table "Orders", and create custom method "FillByDate". It works, but there is small problem:

        DataSet1 myDataSetInstance = new DataSet1();
        DataSet1TableAdapters.OrdersTableAdapter OrdersTA = new DataSet1TableAdapters.OrdersTableAdapter();

        OrdersTA.FillByDate(myDataSetInstance.Orders, new DateTime(2013, 1,1), DateTime.Now);

        foreach (var row in myDataSetInstance.Orders)
        {
            MessageBox.Show(row.Comments); // OK
            MessageBox.Show(row.CustomersRow.Name); //NULL
        }

Getting related row from Customers table is impossible - first i have to manually fill that table. I can see two ways to do this

• Getting whole content of this table - but it will be A LOT of unneeded data
• Create custom query in it's TableAdapter - something like FillByOrdersByDate(@Date1, @Date2) - it is easy, when I have only 2 tables and 1 relation, but with more tables this method will require dozens of custom queries for each TableAdapter.

I really believe, that there have to be "better" way to do this.

Answer 1

Couple of ways to approach this - if you are only going to read the data, you can use a join query to populate the dataset. Alternatively, you can use a join query to populate the child table. Looking at your example, suppose you wanted to list customers and orders for all customers in a particular city. You have already written a 'FillbyCity' query for your Customers TA - you would write a similar FillbyCity Query for your Orders ta. Yes you could use a join to the customers table to do this: SELECT Orders.* FROM Orders INNER JOIN customers ON customers.customerid = orders.customerid WHERE customers.city = @city You would then use the datarelation to link individual customers to their orders, depending on the requirements of your application.

(If perchance you have David Sceppa's 'Programming ADO.Net 2.0' this is dealt with in chapter 7)

"but with more tables this method will require dozens of custom queries for each TableAdapter." Why dozens? I'm not sure where you're going with that.

(PS your English is fine, apart from mixing up it's and its - but lots of native speakers do that too. . )

Answer 2

There is an obscure... don't even know what to call it - ADO.NET SQL extension, or something - a command called SHAPE, that describes the relations you're looking for and ADO.NET uses that "special" SQL to give you a dataset that nicely contains multiple related tables.

   SHAPE  {select * from customers}
   APPEND ({select * from orders} AS rsOrders
           RELATE customerid TO customerid)

It works beautifully, but I think it's old and scarcely (un)supported

MS suggest that SHAPE provider is to be depreciated and to use XML instead (sorry - lost the link, but that was back in .NET 1.1) and they were pointing towards XML. I think the FOR XML T-SQL clause is doing the trick. I haven't done it myself (yet), where I use FOR XML to populate a DataSet, but if you follow the link in an answer I left to another similar question, I think it's going to work.