C# Transactions - Multiple Queries

[Jaco Pretorius]

Hi there

I would like to write code that stops any user to view or alter data on multiple tables until I am completed. I am only keeping the DB (SQL Server) busy for a short while. Below is the code that I have implemented. (Only part of the code) I am not sure if this is sufficient. Help will be appreciated! I am using the Same Transaction on different Commands on different tables.

Transaction = SystellenceData.Connection.BeginTransaction(IsolationLevel.Serializable);

SqlCommand Command = new SqlCommand("SELECT ref_no_counter FROM docs_os_combination WHERE os_comb_id = " + OSCombID.ToString(), SystellenceData.Connection, Transaction);

object ObjectResult = Command.ExecuteScalar();
if (ObjectResult != DBNull.Value)
{
Result = (int)(ObjectResult);
string NewDocumentRefNo = RefNo.Trim().Replace("$$$$", Result.ToString().PadLeft(4, '0'));

Command.CommandText = "SELECT COUNT(*) AS NoRecords FROM docs_document WHERE (document_ref_no = '" + NewDocumentRefNo + "')";

if (((int)Command.ExecuteScalar()) == 0)
{
int NewCountValue = Result + 1;
if (NewCountValue > 9999)
 NewCountValue = 0;

Command.CommandText = "UPDATE docs_os_combination SET ref_no_counter = " + NewCountValue.ToString() + " WHERE os_comb_id = " + OSCombID.ToString();

if (Command.ExecuteNonQuery() != 1)
{
Transaction.Rollback();
Result = -1;
RefNo = "";
}
 else
{
Transaction.Commit();
RefNo = NewDocumentRefNo;
}

[Christian Binder]

I think, it works that way. Another example implementation of Transactions can be found on msdn.
I'd recommend using SqlParameters instead of concating strings to avoid SQL-injection.