Home | Overview | How Do I | FAQ | Sample | Tutorial | ODBC Driver List
This article explains the MFC facilities available for managing transactions and refers you to additional information in DAO Help. For general transaction information in DAO, see the topic "BeginTrans, CommitTrans, Rollback Methods" in DAO Help.
Topics covered include:
A transaction is a series of changes made to a database's data and/or schema. Mark the beginning of a transaction by calling the BeginTrans member function of class CDaoWorkspace. Commit the transaction using the CommitTrans member function, or undo all your changes since BeginTrans using the Rollback member function.
The key idea of transactions is that the operation is “atomic” — a group of related smaller operations must all succeed for the whole operation to succeed. If one small operation fails, the whole operation fails.
There is an implicit transaction while action queries are running. If a query doesn't complete for any reason, it is automatically rolled back. Transactions are optional and can be nested up to five levels. (This is in contrast to ODBC, which does not permit nested transactions.) Transactions increase the speed of data changing operations and enable changes to be reversed easily.
The current transaction consists of all changes made to a recordset object after you last called the BeginTrans member function and before you call the Rollback or CommitTrans member functions.
The workspace object defines a transaction space. Transactions are global to the workspace in which they occur. They affect all open databases, recordsets, and querydefs in the same workspace. If you have several open recordsets and/or databases in a workspace, each call to BeginTrans, CommitTrans, and Rollback applies to all of the objects.
For example, suppose you have called BeginTrans for a workspace and you begin updates through two recordsets that belong to database objects in the same workspace. If you call CommitTrans or RollBack in the workspace, the call affects both recordsets, even if they are open on different databases.
If this transaction model is not what you need, you can open separate transaction spaces by opening separate workspaces. Create a new CDaoWorkspace object for each separate transaction space. For more information, see the article DAO Workspace: Opening a Separate Transaction Space.
The following example illustrates transactions by using two recordsets to delete a student's enrollment from a school registration database. First it removes the student from all classes in which the student is enrolled. Then it removes the student's master record, after which the student no longer exists in the database.
The Delete calls in both recordsets must succeed, so a transaction is required.
Important The example shown illustrates correct transaction procedure, but for the illustrated case this is not the most efficient way to do the job. For details, see the discussion in Efficiency Considerations for This Example.
The example assumes the existence of:
m_dbStudentReg, a document data member that contains a CDaoDatabase object already open on the database.m_rsStudentSet, a document data member that contains a recordset object based on class CStudentSet, derived from CDaoRecordset. This recordset returns all enrolled students.CEnrollmentSet, a second CDaoRecordset-derived class. This recordset, as written by ClassWizard, returns all students enrolled in all classes. The example code filters the recordset to return only the records representing classes in which the specified student is enrolled.The example modifies the default SQL string defined with ClassWizard before opening the recordset. The modification filters the records with a student ID passed in as a parameter.
BOOL CEnrollDoc::RemoveStudent(CString strStudentID)
{
// Construct a recordset for courses student is in
CEnrollmentSet rsEnrollmentSet( &m_dbStudentReg );
// Define the SQL string for the recordset to
// Filter records with the SQL keyword WHERE
CString strSQL = rsEnrollmentSet.GetDefautlSQL( ) +
"WHERE [Student ID] = " + strStudentID;
try
{
// Open the recordset using
// the modified SQL string
rsEnrollmentSet.Open( dbOpenDynaset, strSQL );
// Start the transaction
m_dbStudentReg.m_pWorkspace->BeginTrans( );
// Remove the student from all classes the
// student is enrolled in
while ( !rsEnrollmentSet.IsEOF( ) )
{
rsEnrollmentSet.Delete( );
rsEnrollmentSet.MoveNext( );
}
// Delete the student's master record
m_rsStudentSet.Delete( );
// Commit the transaction
m_dbStudentReg.m_pWorkspace->CommitTrans( );
}
catch(CDaoException* e)
{
m_dbStudentReg.m_pWorkspace->Rollback( );
AfxMessageBox( "Failed to remove student." );
e->Delete( );
return FALSE;
}
m_rsStudentSet.Close( );
}
For information about the try/catch exception handling shown here, see the article Exceptions: Database Exceptions and the CDaoException class.
The transaction example shown in Transaction Example shows you how to do transactions. But in some cases, as in deleting records, transactions may not be most efficient approach. In fact, there are two more efficient approaches to deleting the student record along with all related records for that student:
If the student registration database defines a relation between the STUDENT and ENROLLMENT tables with the cascade delete attribute set, you can delete a single student record in the STUDENT table and let cascade deletes remove all related records in the ENROLLMENT table.
For information about cascade deletes, see the topic "Relation Object" in DAO Help. Relations in MFC are discussed under class CDaoDatabase in the Class Library Reference.
A bulk query would delete all records in any tables you specify that contain the student ID for the student you want to delete.
The query’s SQL statement looks like this:
DELETE FROM STUDENT,ENROLLMENT WHERE STUDENT.StudentID = ENROLLMENT.StudentID AND StudentID = strStudentID
The expression “STUDENT.StudentID = ENROLLMENT.StudentID” “joins” the tables on the StudentID field. The expression “StudentID = strStudentID” finds those records in the join that have the particular student ID in strStudentID. The SQL deletes those records.
Transactions do have a role to play, of course. The point is that you should use the best approach for the particular task.
For more information about transactions, see the following topics in DAO Help:
In the Class Library Reference, see: CDaoWorkspace, especially the BeginTrans, CommitTrans, Rollback, and SetIsolateODBCTrans member functions.
Also see CDaoRecordset, especially the AddNew, Edit, Update, Delete, IsBOF, and IsEOF member functions.
See Also DAO: Where Is..., DAO Recordset