Geeks With Blogs

News
Jason Coyne Jason Coyne's blog

One of my recent applications had an issue which is probably very common. 

I was fetching data out of a database and putting it into a datatable, and then converting the datatable to a collection of business objects. Later, the collection of business objects needed to be commited back to the database, however the original dataset is no longer around. Therefore, when I create rows in my dataset to update back to the database, the data adapter actually attempted to insert new rows, when the rows in question already exist.

The most obvious answer is to select the data back out before the update, change the data in the dataset, and then update. But that causes an extra roundtrip to the database, when I dont care about the data in the database any longer.  Also, when updating multiple rows, the business objects would have to be matched up against the dataset, which could be problematic.

My next solution was to use an update query as the insertcommand of the dataset,  and call the update statement. This worked fine, but was clunky. you could not do a real insert and an update in the same operation.

I finally came across this solution, which is quite elegant and very easy to implement. using the row.AcceptChanges() and row.SetModified() functions you can make the data adapter correctly call the update statement.  row.AcceptChanges() marks the row as unchanged, then SetModified() sets it as updated, even though it started as added.  You cannot call SetModified without calling AcceptChanges, as the setmodified function only works on rows that are set as unmodified.

Hopefully the entire ORM problem is solved in the future by things like LINQ, but until then, this is a nice workaround.

semi-psudocode example

//fetch data and populate business objects
MyStrongDataSet ds = FetchDataFromDatabase();
List<bizobjects>  biz = ConvertDataSetToObjects(ds);

//remove dataset from memory to simulate real world disconnection from data
ds = null;

//modify biz objects
DoSomeWork(biz);

//populate empty dataset with business objects data
ds = new MyStrongDataSet();
foreach (bizobjects currentBiz in biz)
{
DataRow row = ds.NewRow();
row.Someval = currentBiz.Someval;

//Here is the magic
ds.rows.add(row);
row.AcceptChanges();
row.SetModified();


}
dataAdpater.update(ds);

 

update

If you are getting a "Concurrency violation" using my example, here is the solution

By default, the dataadapter creates an update statement that checks
all of the column's original values. Since the datarow used in this
solution does not know the true original values of the row, only your
updated values, that code fails.

The solution is to modify the update statement used by the dataadapter
to only use the primary key for the where clause.  Its pretty easy to
autogenerate such a query by looping through your datatable schema and
creating "set clauses" for each column, and then a single where
condition, or you could hand code a statement up and assign it to the
updatecommand.  In my code I use an automatically generated update
statement.




 

Posted on Wednesday, September 5, 2007 3:17 PM Programming , c# | Back to top


Comments on this post: Using a datatable to update rows and not insert when the row did not originate in the datatable with acceptchanges() and setmodified()

# re: Using a datatable to update rows and not insert when the row did not originate in the datatable with acceptchanges() and set
Requesting Gravatar...
I used your suggestion and it worked.... almost. I get a "Concurrency violation: the UpdateCommand affected 0 of the expected 1 records."

any suggestions?
Left by Jordon on Sep 27, 2007 1:06 PM

# re: Using a datatable to update rows
Requesting Gravatar...
That was very useful info about modifying the Update SQL command of the DataTableAdapter.
During testing I received first "Concurrency violation" message which may be caused by the wizard which sometimes creates a simple Select statement.
Later it generated the full Update SQL command with a bunch of parameters - and it failed too.
Then I edited the Update SQL as you describe to include the parameter for the primary key only.
That worked. Thanks!
Left by Gustav Brock on Feb 07, 2008 4:32 AM

Your comment:
 (will show your gravatar)


Copyright © Jason Coyne | Powered by: GeeksWithBlogs.net