Joined tables, update database with many rows, What is Best Practice?  
Author Message
Ed Mundorf





PostPosted: .NET Framework Data Access and Storage, Joined tables, update database with many rows, What is Best Practice? Top

I have 3 tables(A, B, and C): A and B have identity fields and C is an associative table with each row referencing back to the identity of A and B. To better describe the tables, A will represents objects, B will represent predefined items the object can have, and C will contain any object/item relationship. I will then have a data grid displaying a handful of objects and the items that could be set. Items already configured for an object will display with a check box, and the rest will be unchecked. When a new association is set, a row will be inserted into table C. When an association is un-set, the row will be deleted from table C. Let's say the situation is like this: initially one object in A has 20 items set, and another object has no items set. After the grid is displayed, the user un-sets some of the associations for the first object, and sets some of the associations for the second object. Each object could be configured with hundreds of items.

Here's the real question: What is the best practice for receiving the data from the database and then updating table C Keep in mind database performance is a key factor as the updates are performed on a live production system. Network traffic is to be minimized by reducing the number of round trip calls from the application, (i.e. I don't want to make a single call for each insert or delete).

Thanks for listening, and special thanks for your suggestions!!

Ed



.NET Development26  
 
 
Ed Mundorf





PostPosted: .NET Framework Data Access and Storage, Joined tables, update database with many rows, What is Best Practice? Top

I'm not looking for code or the entire design. I'm looking what mechanism would be the best, like stored procedures, batched statements, datareaders, etc.

Ed


 
 
Ed Mundorf





PostPosted: .NET Framework Data Access and Storage, Joined tables, update database with many rows, What is Best Practice? Top

I have come up with an approach for updating the data. I will use an XML structure which I will pass into a stored procedure as a varchar(8000). I will use the following to extract the data into a handle to then SELECT FROM OPENXML

Here is the xml passed in:

<lane id=123>
<items>
<item id=199 />
<item id=200 />
<item id=201 />
</items>
</lane>

This xml will represent the items set for a lane. I will first delete all records for the lane, and then add the items back in from the xml. By limiting the names of the nodes to 1 character, I can handle a little over 500 associations.

Still, what I'm not sure of is how to deal with the query and the grid. Should I still use data sets with the joined tables Can I easily determine any differences from the original query to the updated information from the user interface

Ed


 
 
alazela





PostPosted: .NET Framework Data Access and Storage, Joined tables, update database with many rows, What is Best Practice? Top

Any reason not to just use the datasets/grid for both query and updates You can configure the adapter to update the association table via a stored procedure, and multiple changes will be batched if you're using a Sql2005 server. Saves you a fair amount of code and should be sufficiently performant.