Last post Dec 31, 2009 04:44 PM by rmdw
Dec 30, 2009 12:22 AM|rmdw|LINK
Suppose you have a DataTable that mirrors a SQL Server table. Let's say it has this data in it:
Note: In SQL Server "Idx" is an auto-incrementing integer field.
Now, upon starting a module which allows editing of this table, a SELECT query is performed and the DataTable is populated with the aforementioned data. You start editing this table and decide to insert a new row. Your code assigns Idx the value of 4 and
Description the value of "Mno".
But while you're in the midst of doing this, another user has also been doing work on this table and added 2 rows of his own, which, when saved to SQL Server, were assigned values 4 & 5 for Idx. So when you eventually hit "Save", which updates SQL Server
with your changes, there's a discrepancy between the Idx value of 4 in your DataTable and the one assigned by SQL Server which is 6. Your Stored Procedure, using the SCOPE_IDENTITY() function returns this new ID value.
The question I have is how to store both the 4 and the 6 in the DataTable? I ask this because all of the aforementioned data handling work is done within the Data Objects layer. When the DataTable is passed up to the Business Objects layer and then again
to the Website layer the associated TreeView or GridView has to be updated because that newly inserted record can no longer refer to that record with an Idx value of 4 but instead must have a value of 6.
I hope I've succinctly described the problem. I'm curious how you would handle it. And please know that this structure MUST be maintained:
Website - Business Objects - Data Objects - SQL Server
Dec 31, 2009 02:39 PM|thuhue|LINK
You said:"Now, upon starting a module which allows editing of this table, a SELECT query is performed and the DataTable is populated with the aforementioned data. You start editing this table and decide to insert a new row.
Your code assigns Idx the value of 4 and Description the value of "Mno"."
Your code is in the frontend or backend? If in the frontend, why artificially assign idx there while SqlServer can create the auto-incrementing integer. The problem you described would only happen when two rows are inserted exactly at the
same time, which would be extremely rare. Can you place the insert query in a try catch statement and run it again in case the said error occurs? Please correct me if I misunderstand anything.
Dec 31, 2009 04:44 PM|rmdw|LINK
Well, this is an issue I've been struggling with so perhaps I'm not doing everything in a Best Practices kind of way. Let me describe to you in more detail what is occurring and you can give me your thoughts:
When the Website layer needs the DataTable in question, it makes a call to the Business Objects (BO) layer, which in turn just passes it onto the Data Objects (DO) layer. Code in DO makes the call to SQL Server, puts the retrieved data in a DataTable, and
passes it back to to BO and then to the Website.
Now, in the example I provided, imagine that this data is used to populate a treeview. As the 4 nodes are added, the "Description" value populates the Text property and the "Idx" value populates the Value property.
Note: My apologies if my terminology is "off" but I'm using Telerik's [excellent] RadTreeView and that's the terminology they use.
So we now have a populated treeview that looks like this:
Now, suppose we want to give the user the ability to edit these descriptions. One obvious way would be to place a TextBox to the right of the treeview. Then when a user clicked on one of the treeview nodes, the description would appear in the TextBox,
allowing it to be changed. So how does that work? Well, when the user clicks on a node, an event is fired and the code looks up the Value property of the clicked node. It then uses this value to query the DataTable to retrieve the appropriate record.
Note: In my example there are only 2 fields in the entire table but in a more realistic case there would be many more. Each of the fields would be editable, with their values copied into a control on the web page.
So all of that works fine. A problem arises though when the user wants to add another record. Continuing with our simple example, say they add a 5th node onto the tree and assign it a Description of "Mno". Remember that all of this is being done on the
Website layer only, dealing strictly with the DataTable in memory - ie. SQL Server is not involved at all at this point.
The big question is, what value should be assigned to the Idx field in the DataTable and to the Value property in the treeview? I thought - rightly or wrongly - that the best approach was to just get the Max Value of Idx, add 1 to it, and use this as a
working value; though always under the explicit understanding that when time came to save all the changes back to SQL Server, that it might assign a completely different value to Idx, as per the auto-increment algorithm within SQL Server.
To get a little more detailed, when the user presses "Save" on the Website layer, the DataTable is passed down to BO and then to DO. In the DO code, a connection is opened, a transaction is started, and then a foreach loop is used to iterate through all
of the rows of the DataTable. Where changes/deletions/insertions are detected, then SQL Server is called. All of this works fine except for one thing: When an Insert stored procedure is called, if Scope_Identity() therein reveals that the working Idx value
is not the same as the one permanently stored by SQL Server, how best do I pass that changed Idx value back up to the Website layer?
Remember: There very well many be many changed Idx values in the DataTable.
In times past I was actually only passing one DataRow at a time from the Website layer down to DO. But I didn't like that approach as way too much business-type code was being handled on the presentation layer. So now that I'm trying to change things over
to pass the entire DataTable down to DO, I'm looking for Best Practices ideas about how to handle the situation of changed Idx values. I have several possible ideas about how to proceed but would like to hear from those who have successfully tackled this
I hope this clarifies the situation and that you & others might be able to offer some good suggestions!