SAMPLE: RLockMFC.exe Demonstrates Row-Level Locking Using MFC and SQL Server

The RLockMFC.exe sample program demonstrates how to use the Microsoft Foundation Class (MFC) CDatabase and CRecordset objects with SQL Server in a Visual C++ application to pessimistically lock individual rows within a recordset while they are being changed.

The following file is available for download from the Microsoft Download Center:
Release Date: April 9, 2001

For additional information about how to download Microsoft Support files, click the following article number to view the article in the Microsoft Knowledge Base: Microsoft scanned this file for viruses. Microsoft used the most current virus-detection software that was available on the date that the file was posted. The file is stored on security-enhanced servers that help to prevent any unauthorized changes to the file. The sample opens a CRecordset object with a SQL Server table. It allows a client to browse the records and update the name field if required. If a second client connects, they can browse the recordset and edit any of the records except the one that is being edited by the first client. If both clients try to edit the same record, a message box with the following messages is displayed Followed by this message: Clients can browse the recordset by using the navigation buttons. Click the Lock The Row button to start editing a record and then click either Cancel Save or Save The Change to discard or save your changes respectively.

NOTE: Clicking any navigation button during a transaction will roll back any changes.

To run the sample, follow these steps:

1. Create the following table in SQL Server:

   CREATE TABLE [dbo].[RowLock] (
   	[pk] [int] IDENTITY (1, 1) NOT NULL ,
   	[name] [char] (50) NULL 
   ) ON [PRIMARY]
   GO
   
   ALTER TABLE [dbo].[RowLock] WITH NOCHECK ADD 
   	CONSTRAINT [PK_RowLock] PRIMARY KEY  CLUSTERED 
   	(
   		[pk]
   	)  ON [PRIMARY] 
   GO
   					

2. Insert some data into the RowLock table.
3. Run two instances of the sample and browse the recordset using both clients. Browse to a record and click the Lock The Row button to start editing with the first client. Then try to edit the same record with the second client. A message box appears, which states that the row is currently locked.

The sample works by opening an optimistic server-side cursor on the recordset; this allows free browsing of the recordset. When the user begins editing, a pessimistic cursor opens on the recordset at the appropriate record, locking only that record. When the user selects either Cancel Save or Save The Change, it releases the pessimistic lock and opens an optimistic cursor again.

Notes

• Pessimistic locking is permitted with the MFC CDatabase and CRecordset classes only when you are not loading the ODBC cursor library.
• The sample overloads the CRecordset::SetLockingMode and CRecordset:Edit functions to support pessimistic locking with the SQL Server driver.
• The sample has not been tested with any other drivers.
• To obtain pessimistic locking, the sample sets the concurrency to SQL_CONCUR_LOCK and then starts a transaction.
• To obtain pessimistic locking using ActiveX Data Objects (ADO), see the following article in the Microsoft Knowledge Base:
  252317 SAMPLE: Rowlock.exe Demonstrates Row-Level Locking Using ADO and SQL Server
• You must modify the connection string to meet your database requirements. The connection strings are in the following code:

  File : PessimisticMFCDlg.CPP
  BOOL CPessimisticMFCDlg::OnInitDialog()
  {
  	CDialog::OnInitDialog();
          .
          .
          .	
  theDatabase.OpenEx("DSN=LocalServer;UID=sa;PWD=;",CDatabase::noOdbcDialog);
          .
          .
          .
  	return TRUE;
  }
  					

  File : RowLock.CPP
  CString Rowlock::GetDefaultConnect()
  {
  	return _T("ODBC;DSN=LocalServer");
  }