TableAdapterManager in ADO.Net Orcas

19 במאי 2007

one comment

TableAdapterManager in ADO.Net Orcas


There is a number of problems that every project I've ever been involved in has to face. One of them is the order in which records has to be updated in the database when dealing with Master-Detail tables with relations.


Lets say we have a very simple DataSet with Orders and Order Details tables (taken from Northwind).


TableAdapterManager in ADO.Net Orcas


If I want to add a new Order with Details, I will probably have a code very similar to the following: 


OrdersDataSet dsOrders = new OrdersDataSet();


// Get Data
OrdersTableAdapter ordersAdapter = new OrdersTableAdapter();
ordersAdapter.Fill(dsOrders.Orders);


Order_DetailsTableAdapter detailsAdapter = new Order_DetailsTableAdapter();
detailsAdapter.Fill(dsOrders.Order_Details);


// Perform changes


// Insert a new Order (Parent Table)
OrdersDataSet.OrdersRow newOrder = dsOrders.Orders.AddOrdersRow("ALFKI", 5, DateTime.Now, DateTime.Now, DateTime.Now, 3, 32.3800m, "Vins et alcools Chevalier", "59 rue de l'Abbaye", "Reims", "RJ", "05454-876", "France");


// Insert a new Order Details (Child Table)
dsOrders.Order_Details.AddOrder_DetailsRow(newOrder, 11, 14.0000m, 12, 0);


// Update the parent table first, than the child table
int ordersUpdated = ordersAdapter.Update(dsOrders.Orders);
int detailssUpdated = detailsAdapter.Update(dsOrders.Order_Details);


Note: I updated the Orders table first (Parent table first) and then updated the Order Details table.


If I want to delete an order, I'll use the following:


// Delete an order
foreach (OrdersDataSet.Order_DetailsRow detailsRow in
    dsOrders.Orders[0].GetOrder_DetailsRows())
{
    detailsRow.Delete();
}
dsOrders.Orders[0].Delete();

int
detailssUpdated = detailsAdapter.Update(dsOrders.Order_Details);
int ordersUpdated = ordersAdapter.Update(dsOrders.Orders);


Note: This time, I deleted rows from the Order Details table first and then deleted the master row from the Orders Table.


Now, in what order should I update the database in order to perform all of the above changes together? Over my yeas of experience I've seen many different solutions for this problem which all ended with tricky code that can be sometimes hard to maintain.


In Orcas, Typed Datasets will support the Master – Details Updates out of the box. A new class called TableAdapterManager has been added to the Typed DataSet generated code which has a method called UpdateAll(). This UpdateAll() method will figure out changes made to the DataSet and send updates, inserts and deleted in the right order and in the same transaction.


TableAdapterManager in ADO.Net Orcas


Using the TableAdapterManager, the code for updating Master – Details changes will be:


// Insert a new Order (Parent Table)
OrdersDataSet.OrdersRow newOrder = dsOrders.Orders.AddOrdersRow();


// Insert a new Order Details (Child Table)
dsOrders.Order_Details.AddOrder_DetailsRow(newOrder, …);


// Delete an order
foreach (OrdersDataSet.Order_DetailsRow detailsRow in
    dsOrders.Orders[0].GetOrder_DetailsRows())
{
   
detailsRow.Delete();
}
dsOrders.Orders[0].Delete();


OrdersDataSetTableAdapters.TableAdapterManager taManager = new TableAdapterManager();
int totalRowsUpdated = taManager.UpdateAll(dsOrders);


You can download a sample project that shows the usage of TableAdapterManager. This project is written in [OrcasBeta1] time, so there might be changes in later versions.


Enjoy! 

Add comment
facebook linkedin twitter email

one comment

Comments are closed.