using sql to lock, select, then update

[Steve Richter]

what is the standard way of using sql in C# to increment a counter
column in a shared control table? ( example: the next order number )

I would like to sql select a row in a table and lock it. Then run code
that assigns the new value to a column in the row. Then sql update the
column in the row, releasing the lock.

thanks,

SqlDataReader rdr = null;
SqlCommand cmd = null;

// get the current RowId
cmd = new SqlCommand();
cmd.CommandText =
"SELECT a.NextOrderNumber " +
"from SystemControlTable a " ;
rdr = cmd.ExecuteReader();
bool rc = rdr.Read();
int orderNumber = (Int32) rdr.GetSqlInt32(0);

int nextOrderNumber = CalcNextOrderNumber( orderNumber ) ;

// update the control record with the incremented rowId.
cmd = new SqlCommand();
cmd.CommandText =
"update @TableName " +
"set NextOrderNumber = @NextOrderNumber " ;
cmd.ExecuteNonQuery();

[Andy]

On Mar 14, 11:29 am, "Steve Richter" <[e-mail]:

what is the standard way of using sql in C# to increment a counter
column in a shared control table? ( example: the next order number )

I would like to sql select a row in a table and lock it. Then run code
that assigns the new value to a column in the row. Then sql update the
column in the row, releasing the lock.

thanks,

SqlDataReader rdr = null;
SqlCommand cmd = null;

// get the current RowId
cmd = new SqlCommand();
cmd.CommandText =
"SELECT a.NextOrderNumber " +
"from SystemControlTable a " ;
rdr = cmd.ExecuteReader();
bool rc = rdr.Read();
int orderNumber = (Int32) rdr.GetSqlInt32(0);

int nextOrderNumber = CalcNextOrderNumber( orderNumber ) ;

// update the control record with the incremented rowId.
cmd = new SqlCommand();
cmd.CommandText =
"update @TableName " +
"set NextOrderNumber = @NextOrderNumber " ;
cmd.ExecuteNonQuery();

ALTER PROCEDURE [dbo].[apGetNextSequenceNumber]
@SequenceKey int,
@SequenceNumber int output
AS
BEGIN
SET NOCOUNT ON

UPDATE SequenceNumber
SET @SequenceNumber = NextSequenceNumber = NextSequenceNumber + 1
WHERE SequenceKey = @SequenceKey

END

(In this case, I have several possible sequences which are independant
of each other). Execute this statement to get the next number and
then use that number in your order. Make sure its done within a
transaction.

HTH
Andy

[Rad [Visual C# MVP]]

On 14 Mar 2007 08:29:40 -0700, Steve Richter wrote:

what is the standard way of using sql in C# to increment a counter
column in a shared control table? ( example: the next order number )

I would like to sql select a row in a table and lock it. Then run code
that assigns the new value to a column in the row. Then sql update the
column in the row, releasing the lock.

thanks,

SqlDataReader rdr = null;
SqlCommand cmd = null;

// get the current RowId
cmd = new SqlCommand();
cmd.CommandText =
"SELECT a.NextOrderNumber " +
"from SystemControlTable a " ;
rdr = cmd.ExecuteReader();
bool rc = rdr.Read();
int orderNumber = (Int32) rdr.GetSqlInt32(0);

int nextOrderNumber = CalcNextOrderNumber( orderNumber ) ;

// update the control record with the incremented rowId.
cmd = new SqlCommand();
cmd.CommandText =
"update @TableName " +
"set NextOrderNumber = @NextOrderNumber " ;
cmd.ExecuteNonQuery();

What I would suggest is that you do the incrementing and assignment within
*the same* stored procedure. You can then wrap the procedure in a
transaction and set the appropriate isolation level (I forget exactly
which!)
--
Bits.Bytes
http://bytes.thinkersroom.com