How to work with several tables, dataadapter and concurrency update

  • Thread starter Thread starter Werner
  • Start date Start date
W

Werner

Hi,

I don't know how to handle the following situation:
I have a main table A and some dependent tables b...
Now I will load the tabel A and the dependent rows from the tables
b... in a dataset.
1.) How can I do that with a dataadapter? I can load the data from the
A table with the Fill method of the da but how can I only load the
related rows in the other tables.

2.) How can I store the modified data in a concurrency scenario over
all tabels?

Thanks for hints
Werner
 
Thanks for your answer.
But my master table and client table contain over 10000000 rows. I load
about 1000 rows in my master tabel. How can I load the client tables with an
dataadapter? If I load the client tables without an relation to the master
tabel I have to load all data to the client tabel, I think. But I need only
1000 rows related to the master tabel.
How can I fill the client table with the nessecary data?

Thanks
 
This book:
http://www.daveandal.net/books/4923/
covers those topics - and you can run and download the samples to play with
to see how it's done in different scenarios, including reconciling errors
and updating multiple tables in the database. There's a feedback link on
that site if you want more details...
 
Werner,

Good question. Let's start with a query to retrieve customer
information:

SELECT * FROM Customers WHERE Country = 'Canada'

Now, how do you get just the orders for these customers? The simplest
solution is to use a query that looks like:

SELECT O.* FROM Orders O, Customers C
WHERE O.CustomerID = C.CustomerID
AND C.Country = 'Canada'

You could also use a JOIN query or a query with an IN clause and a
sub-select. You can use keep building on this approach for "grandchild"
tables, like the Order Details table:

SELECT OD.* FROM [Order Details] OD, Orders O, Customers C
WHERE OD.OrderID = O.OrderID
AND O.CustomerID = C.CustomerID
AND C.Country = 'Canada'

If you're working with a provider and database (like SQL Server) that
support batch queries, you can batch these queries together and fetch all
of the rows in a single call to DataAdapter.Fill. I've included some
sample C# & SqlClient code you could try.

DataSets are designed to hold both current and original values so you
can submit pending changes using optimistic concurrency to ensure you don't
accidentally overwrite another user's changes.

I hope this information proves helpful.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2004 Microsoft Corporation. All rights reserved.



string strConn, strSQL;
strConn = "Data Source=(local);Initial Catalog=Northwind;" +
"Trusted_Connection=Yes;";
strSQL = "SELECT C.* FROM Customers C WHERE C.Country = @Country;" +
"SELECT O.* FROM Orders O, Customers C " +
"WHERE O.CustomerID = C.CustomerID AND C.Country = @Country;" +
"SELECT OD.* FROM [Order Details] OD, Orders O, Customers C " +
"WHERE OD.OrderID = O.OrderID AND O.CustomerID = C.CustomerID " +
"AND C.Country = @Country";
SqlDataAdapter da = new SqlDataAdapter(strSQL, strConn);
da.SelectCommand.Parameters.Add("@Country", SqlDbType.NChar, 15);
da.SelectCommand.Parameters[0].Value = "Canada";

//Supply DataTable names for the results
da.TableMappings.Add("Table", "Customers");
da.TableMappings.Add("Table1", "Orders");
da.TableMappings.Add("Table2", "Order Details");

//Fill the DataSet
DataSet ds = new DataSet();
da.Fill(ds);

//Add relations
ds.Relations.Add("Customers_Orders",
ds.Tables["Customers"].Columns["CustomerID"],
ds.Tables["Orders"].Columns["CustomerID"]);
ds.Relations.Add("Orders_Details",
ds.Tables["Orders"].Columns["OrderID"],
ds.Tables["Order Details"].Columns["OrderID"]);

//Print out the results
foreach (DataRow rowCustomer in ds.Tables["Customers"].Rows)
{
Console.WriteLine("{0} {1}", rowCustomer["CustomerID"],
rowCustomer["CompanyName"]);
foreach (DataRow rowOrder in rowCustomer.GetChildRows("Customers_Orders"))
{
Console.WriteLine(" {0} {1} {2}", rowOrder["CustomerID"],
rowOrder["OrderID"], rowOrder["OrderDate"]);
foreach (DataRow rowDetail in rowOrder.GetChildRows("Orders_Details"))
Console.WriteLine(" {0} {1} {2}", rowDetail["OrderID"],
rowDetail["ProductID"], rowDetail["Quantity"]);
Console.WriteLine();
}
Console.WriteLine();
}
 
Back
Top