getting sql time stamp back into my objects

Topics: Data Access Application Block
Jun 28, 2007 at 3:27 PM
Edited Jun 28, 2007 at 3:28 PM
HI, i generated my data access layer using the automation extensions web service factory. I set up the database so that it would generate the optimistic concurrency behavior, however, i noticed that the update and insert methods did not automatically provide an update to the timestamp property of my business objects from the database. What would the best way be to accomplish this?

or am i mistaking and the db.AddInParameter() call is passing my time stamp property in with the udpate stored procedure. Is it getting updated via that reference?
(sample code below)
public DbCommand ConstructUpdateCommand(Database db, Note note)
{
DbCommand command = db.GetStoredProcCommand("dbo.UpdateNote");

db.AddInParameter(command, "assetGuid", DbType.Guid, note.AssetGuid);
db.AddInParameter(command, "id", DbType.Guid, note.Id);
if(note.Notes != null)
{
db.AddInParameter(command, "notes", DbType.String, note.Notes);
}
db.AddInParameter(command, "noteTime", DbType.DateTime, note.NoteTime);
db.AddInParameter(command, "updated", DbType.Binary, note.Updated);
db.AddInParameter(command, "updatedBy", DbType.Int64, note.UpdatedBy);
return command;
}
Jul 3, 2007 at 2:23 PM
Hi,

I'm not sure what kind of solution you need. From what I can understand the behavior of the code generated by the service factory with the input you provided is not what you expect; have you tried asking on the discussion forum for the factory http://www.codeplex.com/servicefactory/Thread/List.aspx?

To diagnose the problem, you need to follow the chain from the data access code generated for you down to the database and make sure this timestamp information flows appropriately. Are you using your own CRUD stored procedures or does the service factory create them for you?

Fernando
Jul 3, 2007 at 6:31 PM

jpeckham wrote:
or am i mistaking and the db.AddInParameter() call is passing my time stamp property in with the udpate stored procedure. Is it getting updated via that reference?

It doesn't look like it. Timestamps should be OUTPUT parameters, not INPUT (since SQL Server automatically changes the value for you there's no need to send it as an input).

If this is generated code, you'll need to take it up with the people that wrote the generator.
Jul 3, 2007 at 7:11 PM
Thank you both, good ideas.

fsimonazzi : i'll start asking in the correct forum, my apologies. THe sql procedures and the code was generated this way, i didn't write it this way but before i go assuming the generated code is wrong and changing it i want to make sure i'm using it properly... maybe it's not their intention to get back updates to the object from the update procedure, maybe i need to make a subsequent call to the GetByPrimaryKey() method.

mgnoonan: i think you are right when it comes to an INSERT but not an UPDATE, because the timestamp must be sent as input to verify that concurrency has not been violated.

Currently i modified my business logic to runt he get method to get an updated version of the object after an update or insert occurs and send that back to the service client. Is this wrong to be making the second procedure call on the database to update the object?

additionally, how would i properly handle objects that have sub objects/sub object collections.. should i save them (update, insert, delete them) when i request saving their parent? or each time they're added or removed from the parent... if the latter, then what do i do about a parent that is new and hasn't been inserted yet, do i require the user save the parent object before attaching any child objects? seems like a lot of extra work for the user and not very intuitive.
Jul 3, 2007 at 9:32 PM

jpeckham wrote:
mgnoonan: i think you are right when it comes to an INSERT but not an UPDATE, because the timestamp must be sent as input to verify that concurrency has not been violated.

Yes, good point. The timestamp param should be included in the WHERE clause on an UPDATE, and if the stamp doesn't match, a concurrency exception is thrown.


jpeckham wrote:
Currently i modified my business logic to runt he get method to get an updated version of the object after an update or insert occurs and send that back to the service client. Is this wrong to be making the second procedure call on the database to update the object?

It's not wrong, but you might upset your users. By doing a Get, you are retrieving the current row which may or may not have been changed by another user. You are then taking your current set of values (which are now out of date) and overwriting them into the row. So with this "last one in wins" approach, you might be overwriting new data with old values, and your users will be unaware because that process produces no errors.


jpeckham wrote:
additionally, how would i properly handle objects that have sub objects/sub object collections.. should i save them (update, insert, delete them) when i request saving their parent? or each time they're added or removed from the parent... if the latter, then what do i do about a parent that is new and hasn't been inserted yet, do i require the user save the parent object before attaching any child objects? seems like a lot of extra work for the user and not very intuitive.

Generally speaking, you want to save the parent first, then the children, and wrap the whole thing up in a transaction. If no errors occurred, commit the transaction. Otherwise, roll the transaction back to undo any changes. You should also provide a way for your users to cancel the whole thing without persisting any changes to the database.

__________________________________
Matthew Noonan
EasyObjects.NET -- The O/RM for the Enterprise Library
http://www.easyobjects.net
Jul 3, 2007 at 11:41 PM
i don't do the "get" before i update, i update then i "get" to return the most current value (post update) to the UI since the update procedure isn't updating the reference to my object with the generated code from the webservice factory.

I haven't gotten as far as an undo, but i'll check out rockford's book because i saw a mention of that functionality. As far as doing the parent then children in a transaction that's what i already have, just wanted to make sure it's normal to do it all at once like i am. (almost seems too chunky)


My real problem is when i send an object to the repository to update, how do i get the object i just saved back out to my client software and update the timestamp property of my object so that subsequent updates do not fail.

again i'll reiterate and elaborate: my current solution is to translate the object to a service object from a ui object, send it to the service, translate it from a service object to a business entity, update it with the repository, run a get command to get that object from the database by primary key, update the reference value to the new object, translate that object back to a srevice object and return it to the client in the response message, then the client translates that back into a UI object and replace the reference to the old object with it in the UI.

everything works fine, BUT again back to my initial question: is there a way to MODIFY this code:
public DbCommand ConstructUpdateCommand(Database db, Note note)
{
DbCommand command = db.GetStoredProcCommand("dbo.UpdateNote");

db.AddInParameter(command, "assetGuid", DbType.Guid, note.AssetGuid);
db.AddInParameter(command, "id", DbType.Guid, note.Id);
if(note.Notes != null)
{
db.AddInParameter(command, "notes", DbType.String, note.Notes);
}
db.AddInParameter(command, "noteTime", DbType.DateTime, note.NoteTime);
db.AddInParameter(command, "updated", DbType.Binary, note.Updated);
db.AddInParameter(command, "updatedBy", DbType.Int64, note.UpdatedBy);
return command;
}



so that it updates the 'note' object in the example to the most current values through the stored procedure so that i don't have to perform a get command right after the update command?

this seems like an entlib data access question more than a webservice factory question to me, since it's the Database object i'm referring to and the functionality contained within.
Jul 4, 2007 at 1:42 AM

jpeckham wrote:
i don't do the "get" before i update, i update then i "get" to return the most current value (post update) to the UI since the update procedure isn't updating the reference to my object with the generated code from the webservice factory.

Ah, sorry if I misread your question. But you are right in that it shouldn't take two queries to accomplish this task.


jpeckham wrote:
I haven't gotten as far as an undo, but i'll check out rockford's book because i saw a mention of that functionality. As far as doing the parent then children in a transaction that's what i already have, just wanted to make sure it's normal to do it all at once like i am. (almost seems too chunky)

No, not chunky, you want to keep your transactions as small and tight as possible. If you save everytime the user makes an entry, how would you ever roll it back? That's what is chunky!


jpeckham wrote:
My real problem is when i send an object to the repository to update, how do i get the object i just saved back out to my client software and update the timestamp property of my object so that subsequent updates do not fail.

again i'll reiterate and elaborate: my current solution is to translate the object to a service object from a ui object, send it to the service, translate it from a service object to a business entity, update it with the repository, run a get command to get that object from the database by primary key, update the reference value to the new object, translate that object back to a srevice object and return it to the client in the response message, then the client translates that back into a UI object and replace the reference to the old object with it in the UI.

Well, I use EasyObjects as my business logic layer (of course ;), so I can only say that that part is handled for me. There is still the issue of crossing the web service boundry, but that is not unique to either approach. EO is a wrapper around a DataTable, so any fields that are generated (such as an IDENTITY) or computed (such as a timestamp) are updated back in the table, then I can serialize the object for transport via http.

I don't use the webservice factory, so others may need to chime in here.

__________________________________
Matthew Noonan
EasyObjects.NET -- The O/RM for the Enterprise Library
http://www.easyobjects.net
Jul 4, 2007 at 1:56 PM
Hi,

My comments inline...


jpeckham wrote:

everything works fine, BUT again back to my initial question: is there a way to MODIFY this code:
public DbCommand ConstructUpdateCommand(Database db, Note note)
{
DbCommand command = db.GetStoredProcCommand("dbo.UpdateNote");

db.AddInParameter(command, "assetGuid", DbType.Guid, note.AssetGuid);
db.AddInParameter(command, "id", DbType.Guid, note.Id);
if(note.Notes != null)
{
db.AddInParameter(command, "notes", DbType.String, note.Notes);
}
db.AddInParameter(command, "noteTime", DbType.DateTime, note.NoteTime);
db.AddInParameter(command, "updated", DbType.Binary, note.Updated);
db.AddInParameter(command, "updatedBy", DbType.Int64, note.UpdatedBy);
return command;
}



so that it updates the 'note' object in the example to the most current values through the stored procedure so that i don't have to perform a get command right after the update command?



You're calling a stored procedure which defines the way you access the database. I don't think there's anything you can do to get back a piece of information that is not part of that signature... If that piece of information is not returned by the procedure (which seems to be the case) then how would you retrieve it when invoking the procedure?




this seems like an entlib data access question more than a webservice factory question to me, since it's the Database object i'm referring to and the functionality contained within.


I disagree :) What you need is an "update" stored procedure that would return the information you need (i.e. the timestamp) when invoked; without it there's little you can do with the DAAB's Database, or more accurately with the ADO.NET 2.0 API the DAAB helps you use, to get the information. Since the stored procedure was created for you by the service factory, I think the question belongs into the service factory's forum.

Regards,
Fernando