Using a Timestamp field to implement optimistic concurrency

microsoft.public.dotnet.framework.adonet

18 Oct 2006 9:46 PM

BBM

Hi,

I'm using ADO.Net SqlCommand objects to do Database reads/updates in an
application. I'd like to use optimistic concurrency, so each of my tables
has a Column of type TimeStamp in it.

Right now I am NOT using stored procedures. I am updating the CommandText
property of the SqlCommand object in code, and using command.executeReader or
command.executeNonQuery to read or update data.

I understand that the TimeStamp column will be automatically updated by SQL
Server (2005) when I Insert or Update a row. What is the best method for
getting the new timestamp value back into my application after a database
write?

I think I have a couple of options...

1) Just run another SELECT statement after the row update (with
command.executeScalar) to get back the TimeStamp. I can do this, but it just
seems cumbersome, and it has to be slow running two separate commands.

2) I've seen lots of examples with Stored Procedures and output parameters
- but I'm not using sproc's. Can I define one value for CommandText with
both the Update and the Select in it, have them both execute on
ExecuteNonQuery and get the new Timestamp back in an output parameter? If
this is the case, what does the SQL syntax look like for this? I've tried
using some example SQL that I've seen used with sproc's ...

cm.CommandText =
"Update A Set Name = @Name, Data = @Data WHERE Id = @Id and TS = @TS
Select @NewTS = TS FROM A WHERE Id = @Id"

@Id, @Name, @Data, and @TS are all regular input parms,
@NewTS is set up as an output parm before running the command like this...

cm.parameters.Add("@NewTS", SqlDBType.Timestamp)
cm.Parameters("@NewTS").Direction = ParameterDirection.Output

I use executeNonQuery to run the command, but nothing comes back in my
output parm.

mTS = cm.Parameters("@NewTS").Value ' Value comes back as Nothing

What's the best way to do this, and if it's an output parm, please include
an example of the SQL Syntax.

Thanks.

BBM

18 Oct 2006 11:19 PM

Otis Mukinfus

On Wed, 18 Oct 2006 14:46:01 -0700, BBM <bbm@newsgroups.nospam> wrote:

Show quote

>Hi,
>
>I'm using ADO.Net SqlCommand objects to do Database reads/updates in an
>application. I'd like to use optimistic concurrency, so each of my tables
>has a Column of type TimeStamp in it.
>
>Right now I am NOT using stored procedures. I am updating the CommandText
>property of the SqlCommand object in code, and using command.executeReader or
>command.executeNonQuery to read or update data.
>
>I understand that the TimeStamp column will be automatically updated by SQL
>Server (2005) when I Insert or Update a row. What is the best method for
>getting the new timestamp value back into my application after a database
>write?
>
>I think I have a couple of options...
>
>1) Just run another SELECT statement after the row update (with
>command.executeScalar) to get back the TimeStamp. I can do this, but it just
>seems cumbersome, and it has to be slow running two separate commands.
>
>2) I've seen lots of examples with Stored Procedures and output parameters
>- but I'm not using sproc's. Can I define one value for CommandText with
>both the Update and the Select in it, have them both execute on
>ExecuteNonQuery and get the new Timestamp back in an output parameter? If
>this is the case, what does the SQL syntax look like for this? I've tried
>using some example SQL that I've seen used with sproc's ...
>
>cm.CommandText =
>"Update A Set Name = @Name, Data = @Data WHERE Id = @Id and TS = @TS
>Select @NewTS = TS FROM A WHERE Id = @Id"
>
>@Id, @Name, @Data, and @TS are all regular input parms,
>@NewTS is set up as an output parm before running the command like this...
>
>cm.parameters.Add("@NewTS", SqlDBType.Timestamp)
>cm.Parameters("@NewTS").Direction = ParameterDirection.Output
>
>I use executeNonQuery to run the command, but nothing comes back in my
>output parm.
>
>mTS = cm.Parameters("@NewTS").Value ' Value comes back as Nothing
>
>What's the best way to do this, and if it's an output parm, please include
>an example of the SQL Syntax.
>
>Thanks.
>
>BBM

There's no reason you can't use the same SQL in a command object that you would
use in a stored procedure. The sample below is what the wizard will generate if
you use stored procedures to access your tables. If you leave out the stored
procedure specific code it should work the same as the stored procedure.

What this does is execute the update, then selects all the row back to the
caller. When executing your command object you would use
SqlCommand.ExecuteReader() if you are going to read the data that comes back. If
you are using a strong typed dataset it will manage this for you.

AFAIK you cannot read a timestamp visually unless you convert it to an array of
bytes, so don't worry about humans being able to read it. The machine will take
care of that.

ALTER PROCEDURE [dbo].[ConditionUpdate]
(
    @EventID int,
    @ConditionTypeID int,
    @ConditionBegin datetime,
    @ConditionEnd datetime,
    @Original_ConditionID int,
    @Original_TsCol timestamp,
    @ConditionID int
)
AS
    SET NOCOUNT OFF;
UPDATE [Condition] SET [EventID] = @EventID, [ConditionTypeID] =
@ConditionTypeID, [ConditionBegin] = @ConditionBegin, [ConditionEnd] =
@ConditionEnd WHERE (([ConditionID] = @Original_ConditionID) AND ([TsCol] =
@Original_TsCol));

SELECT ConditionID, EventID, ConditionTypeID, ConditionBegin, ConditionEnd,
TsCol FROM Condition WHERE (ConditionID = @ConditionID)

Good luck with your project,

Otis Mukinfus
http://www.arltex.com
http://www.tomchilders.com

19 Oct 2006 10:43 AM

WenYuan Wang

Hi BBM,

First of all, I¡¯d like to confirm my understanding of this issue.
According to your description, you want to know how to get a Timestamp
field without using SQL Stored Procedures.
If I have misunderstood anything here, please don¡¯t hesitate to correct me.

I recommend that you can use SQLCommand object to get the value from SQL
DataBase without Stored Procedures.
I have done some tests as below:
---------------------------------
string connectstring="....";
string sqlcommand = "Update MytestTable Set C1 = @a WHERE ID=1;¡±;
sqlcommand +=¡±select @b=TS from MytestTable where ID=1";
System.Data.SqlClient.SqlConnection sc=new
System.Data.SqlClient.SqlConnection(connectstring);
System.Data.SqlClient.SqlCommand scd=new System.Data.SqlClient.SqlCommand();
scd.Connection=sc;
scd.CommandText=sqlcommand;
scd.Parameters.Add("@a",SqlDbType.Char,255);
scd.Parameters["@a"].Value = "111222111";
scd.Parameters.Add("@b", SqlDbType.Timestamp);
scd.Parameters["@b"].Direction = ParameterDirection.Output;
sc.Open();
scd.ExecuteNonQuery();
sc.Close();
object returnTS = scd.Parameters["@b"].Value.ToString());
-----------------------------------
I can get the scd.Parameters["@b"].Value(not Null) after ExecuteNonQuery().

You can try the above code.
If anything is unclear, please don¡¯t hesitate to contact me.

Wen Yuan
Microsoft Online Community Support
===============================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
===============================
(This posting is provided "AS IS", with no warranties, and confers no
rights.)