using sql to lock, select, then update

  • Thread starter Thread starter Steve Richter
  • Start date Start date
S

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();
 
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();
Click to expand...

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
 
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();
Click to expand...

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!)
 
Back
Top