Date: 02/21/09 (C Sharp) Keywords: cms, database, sql, web First off, I'll state that I'm working with C# 2008 express edition, SQL Server 2008 Professional edition, and .NET 3.5. SqlConnection cnErasmus = new SqlConnection();
//populate the datatable with the data already in the table.
tblAuthorType = PopulateDataSet(cnErasmus);
//Attach DataTable to datagrid.
dgvAuthorType.DataSource = tblAuthorType;(note: it used to be a dataset. I forgot to change the name of the routine.) In case you want/need to see the actual filling of the DataTable, I'll put it private DataTable PopulateDataSet(SqlConnection cnErasmus)
{
string strAuthorTypeSelectQuery = "SELECT AuthType FROM AuthorType";
using (cnErasmus)
{
using (SqlCommand cmSelectCommand = new SqlCommand(strAuthorTypeSelectQuery, cnErasmus))
{
CreateConnectionString(cnErasmus);
OpenConnection(cnErasmus);
using (SqlDataReader theReader = cmSelectCommand.ExecuteReader())
{
tblAuthorType.Load(theReader);
CloseConnection(cnErasmus);
}
}
}All of this is completely normal. At least, I think it is. Now, the tough part seems to be logging changes. The obvious way to do so, to me (I'm a database guy more than a programmer) is through a transaction object: /******************SqlTransaction trnchangeAuthorTypeData =
cnErasmus.BeginTransaction("Changes");
//Must assign both transaction object and connection
//to Command object for a pending local transaction.
SqlCommand cmTransactionCommand = cnErasmus.CreateCommand();
cmTransactionCommand.Connection = cnErasmus;
cmTransactionCommand.Transaction = trnchangeAuthorTypeData;
//Whether transaction is committed or rolled back depends
//on which button the user presses...********************/These are the lines just after setting the dgv's data source. I've seen this done at least a dozen times in various places on the web, but in every case, the example has some sort of hardcoded INSERT or DELETE statement immediately following, and then a try/catch block with the appropriate transaction.Commit() or transaction.Rollback() statements. The important bit for me is that in every example I've found, all the statements follow one another. It's all very procedural. I want the Commit() or Rollback() to be fired based on which of those buttons gets pressed; the Cancel button will cause a Rollback() and the Save button will cause a Commit(). To me, this seems perfectly logical. My problem is that, unfortunately, trnchangeAuthorTypeData goes out of scope the second we hit the end of that block of code, and so is nowhere to be found when I get to btnSave_Click or btnCancel_Click. Setting it the normal way (trnChancgeAuthorTypeData = new SqlTransaction()) raises an error based on the protection level of SqlTransaction, so I can't declare it the way I declare every other variable. (While no resource I've consulted specifically explains this odd behavior, they all confirm that this is by design.) So, after all that, my question is: how do I separate BeginTransaction(), Commit(), and Rollback() into three separate routines? If that's not possible, as I have spent the last twelve hours ascertaining, is there some way other than transactions to make sure that, when the Cancel button is pressed, the system will roll back all changes since either (a) the form was opened or (b) the Save button was last pressed, and that can be done in that manner (with the transaction-analogue starting in the same routine where the datagridview is bound, and finishing in one of the button-press routines)? Thanks.
|