data concurrency

  • Thread starter Thread starter Mike P
  • Start date Start date
M

Mike P

Can anyone give me any advice on catching data concurrency errors? I
want to amend the code below so that if the record to be amended has
already changed since it was taken from the table, the error will be
trapped and the code will go into a loop where it continues trying to
update the value until it finds that the record has not been amended
since it was taken from the table, and so can successfully update.

SqlConnection objConnectionAttPurchLNX = new
SqlConnection(ConfigurationSettings.AppSettings["strConnectTest"]);
string strPurpleCards = "SELECT * FROM purple_cards WHERE Serial =
" + Session["SerialNumber"];

//create a new DataSet object
DataSet objDataSetPurpleCards = new DataSet();

//create a new DataAdapter using the connection object and select
statement
SqlDataAdapter objDataAdapterPurpleCards = new
SqlDataAdapter(strPurpleCards, objConnectionAttPurchLNX);

//fill the dataset with data from the DataAdapter object
objDataAdapterPurpleCards.Fill(objDataSetPurpleCards,
"PurpleCards");

//declare a variable to reference the table
DataTable objTablePurpleCards =
objDataSetPurpleCards.Tables["PurpleCards"];

//edit an exisiting row in the table
DataRow objRow = null;
objRow = objTablePurpleCards.Rows[0];
objRow["Cash"] = Convert.ToDouble(Session["Cash"]) + dblTotal;

try
{
SqlCommand objCommandPurpleCards = new SqlCommand();
objCommandPurpleCards.Connection = objConnectionAttPurchLNX;
objCommandPurpleCards.CommandText = "UPDATE Purple_Cards SET Cash
= " + objRow["Cash"] + " WHERE Serial = " + Session["SerialNumber"];

objDataAdapterPurpleCards.UpdateCommand = objCommandPurpleCards;

// now do the update
objDataAdapterPurpleCards.Update(objDataSetPurpleCards,
"PurpleCards");

//refresh data and update Session["Cash"]
SqlCommand objCommandPurpleCardsRefresh = new
SqlCommand(strPurpleCards, objConnectionAttPurchLNX);
SqlDataReader objDataReaderPurpleCardsRefresh = null;

objConnectionAttPurchLNX.Open();
objDataReaderPurpleCardsRefresh =
objCommandPurpleCardsRefresh.ExecuteReader();

if (objDataReaderPurpleCardsRefresh.Read() == true)
{
double dblCash;
dblCash =
Convert.ToDouble(objDataReaderPurpleCardsRefresh["CASH"]);
dblCash = Math.Round(dblCash, 2);
Session["Cash"] = dblCash;
lblBalance.Text = "Balance : £" + Session["Cash"];
}

}
catch (Exception objError)
{
lbl1.Text = Convert.ToString(objError);
}

objConnectionAttPurchLNX.Close();


Thanks,

Mike
 
Mike,

You will have to have some sort of a timestamp on your table. When
updating your values, you will want to begin a transaction, checking to see
if the timestamp that you have and the timestamp on the server have changed.
If they have, then you don't do anything, otherwise, you save the values
(because the record hasn't changed).

I don't think that your algorithm is a good one, as you will find that
the timestamp that you have is always different than the original one.
Also, how do you mitigate the changes that were made, with the changes you
want to be made? What if a field was changed that you changed and that
write should overwrite the change you would have made? You need to be more
aware, and possibly make your users more aware of the concurrency issue and
then proceed based on user input, or through some very well-defined rules
for when this happens.

Hope this helps.

--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

Mike P said:
Can anyone give me any advice on catching data concurrency errors? I
want to amend the code below so that if the record to be amended has
already changed since it was taken from the table, the error will be
trapped and the code will go into a loop where it continues trying to
update the value until it finds that the record has not been amended
since it was taken from the table, and so can successfully update.

SqlConnection objConnectionAttPurchLNX = new
SqlConnection(ConfigurationSettings.AppSettings["strConnectTest"]);
string strPurpleCards = "SELECT * FROM purple_cards WHERE Serial =
" + Session["SerialNumber"];

//create a new DataSet object
DataSet objDataSetPurpleCards = new DataSet();

//create a new DataAdapter using the connection object and select
statement
SqlDataAdapter objDataAdapterPurpleCards = new
SqlDataAdapter(strPurpleCards, objConnectionAttPurchLNX);

//fill the dataset with data from the DataAdapter object
objDataAdapterPurpleCards.Fill(objDataSetPurpleCards,
"PurpleCards");

//declare a variable to reference the table
DataTable objTablePurpleCards =
objDataSetPurpleCards.Tables["PurpleCards"];

//edit an exisiting row in the table
DataRow objRow = null;
objRow = objTablePurpleCards.Rows[0];
objRow["Cash"] = Convert.ToDouble(Session["Cash"]) + dblTotal;

try
{
SqlCommand objCommandPurpleCards = new SqlCommand();
objCommandPurpleCards.Connection = objConnectionAttPurchLNX;
objCommandPurpleCards.CommandText = "UPDATE Purple_Cards SET Cash
= " + objRow["Cash"] + " WHERE Serial = " + Session["SerialNumber"];

objDataAdapterPurpleCards.UpdateCommand = objCommandPurpleCards;

// now do the update
objDataAdapterPurpleCards.Update(objDataSetPurpleCards,
"PurpleCards");

//refresh data and update Session["Cash"]
SqlCommand objCommandPurpleCardsRefresh = new
SqlCommand(strPurpleCards, objConnectionAttPurchLNX);
SqlDataReader objDataReaderPurpleCardsRefresh = null;

objConnectionAttPurchLNX.Open();
objDataReaderPurpleCardsRefresh =
objCommandPurpleCardsRefresh.ExecuteReader();

if (objDataReaderPurpleCardsRefresh.Read() == true)
{
double dblCash;
dblCash =
Convert.ToDouble(objDataReaderPurpleCardsRefresh["CASH"]);
dblCash = Math.Round(dblCash, 2);
Session["Cash"] = dblCash;
lblBalance.Text = "Balance : £" + Session["Cash"];
}

}
catch (Exception objError)
{
lbl1.Text = Convert.ToString(objError);
}

objConnectionAttPurchLNX.Close();


Thanks,

Mike
Click to expand...
 
Back
Top