Performing Asynchronous Operations

SQL Server allows applications to perform asynchronous database operations. Asynchronous processing enables methods to return immediately without blocking on the calling thread. This allows much of the power and flexibility of multithreading, without requiring the developer to explicitly create threads or handle synchronization. Applications request asynchronous processing when initializing a database connection, or when initializing the result from the execution of a command.

Opening and Closing a Database Connection

When using the SQL Server Native Client OLE DB provider, applications designed to initialize a data source object asynchronously can set the DBPROPVAL_ASYNCH_INITIALIZE bit in the DBPROP_INIT_ASYNCH property prior to calling IDBInitialize::Initialize. When this property is set, the provider returns immediately from the call to Initialize with either S_OK, if the operation has completed immediately, or DB_S_ASYNCHRONOUS, if the initialization is continuing asynchronously. Applications can query for the IDBAsynchStatus or ISSAsynchStatusinterface on the data source object, and then call IDBAsynchStatus::GetStatus orISSAsynchStatus::WaitForAsynchCompletion to get the status of the initialization.

In addition, the SSPROP_ISSAsynchStatus property has been added to the DBPROPSET_SQLSERVERROWSET property set. Providers that support the ISSAsynchStatus interface must implement this property with a value of VARIANT_TRUE.

IDBAsynchStatus::Abort or ISSAsynchStatus::Abort can be called to cancel the asynchronous Initialize call. The consumer must explicitly request Asynchronous Data Source Initialization. Otherwise, IDBInitialize::Initialize does not return until the data source object is completely initialized.

Execution and Rowset Initialization

Applications designed to asynchronously open the result from the execution of a command can set the DBPROPVAL_ASYNCH_INITIALIZE bit in the DBPROP_ROWSET_ASYNCH property. When setting this bit prior to calling IDBInitialize::Initialize, ICommand::Execute, IOpenRowset::OpenRowset or IMultipleResults::GetResult, the riid argument must be set to IID_IDBAsynchStatus, IID_ISSAsynchStatus, or IID_IUnknown.

The method returns immediately with S_OK if the rowset initialization completes immediately, or with DB_S_ASYNCHRONOUS if the rowset continues initializing asynchronously, with ppRowset set to the requested interface on the rowset. For the SQL Server Native Client OLE DB provider, this interface can only be IDBAsynchStatus or ISSAsynchStatus. Until the rowset is fully initialized, this interface behaves as if it were in a suspended state, and calling QueryInterface for interfaces other than IID_IDBAsynchStatus or IID_ISSAsynchStatus may return E_NOINTERFACE. Unless the consumer explicitly requests asynchronous processing, the rowset is initialized synchronously. All requested interfaces are available when IDBAsynchStaus::GetStatus or ISSAsynchStatus::WaitForAsynchCompletion returns with the indication that asynchronous operation is complete. This does not necessarily mean that the rowset is fully populated, but it is complete and fully functional.

If the executed command does not return a rowset, it still returns immediately with an object that supports IDBAsynchStatus.

If you need to get multiple results from asynchronous command execution, you should:

• Set the DBPROPVAL_ASYNCH_INITIALIZE bit of the DBPROP_ROWSET_ASYNCH property, before executing the command.

• Call ICommand::Execute, and request IMultipleResults.

The IDBAsynchStatus and ISSAsynchStatus interfaces can then be obtained by querying the multiple results interface using QueryInterface.

When the command has finished executing, IMultipleResults can be used as normal, with one exception from the synchronous case: DB_S_ASYNCHRONOUS may be returned, in which case IDBAsynchStatus or ISSAsynchStatus can be used to determine when the operation is complete.

Examples

In the following example, the application calls a non-blocking method, does some other processing, and then returns to process the results. ISSAsynchStatus::WaitForAsynchCompletion waits on the internal event object until the asynchronously executing operation is done or the amount of time specified by dwMilisecTimeOut is passed.

// Set the DBPROPVAL_ASYNCH_INITIALIZE bit in the   
// DBPROP_ROWSET_ASYNCH property before calling Execute().  
  
DBPROPSET CmdPropset[1];  
DBPROP CmdProperties[1];  
  
CmdPropset[0].rgProperties = CmdProperties;  
CmdPropset[0].cProperties = 1;  
CmdPropset[0].guidPropertySet = DBPROPSET_ROWSET;  
  
// Set asynch mode for command.  
CmdProperties[0].dwPropertyID = DBPROP_ROWSET_ASYNCH;  
CmdProperties[0].vValue.vt = VT_I4;  
CmdProperties[0].vValue.lVal = DBPROPVAL_ASYNCH_INITIALIZE;  
CmdProperties[0].dwOptions = DBPROPOPTIONS_REQUIRED;  
  
hr = pICommandProps->SetProperties(1, CmdPropset);  
  
hr = pICommand->Execute(  
   pUnkOuter,  
   IID_ISSAsynchStatus,  
   pParams,  
   pcRowsAffected,  
   (IUnknown**)&pISSAsynchStatus);  
  
if (hr == DB_S_ASYNCHRONOUS)  
{  
   // Do some work here...  
  
   hr = pISSAsynchStatus->WaitForAsynchCompletion(dwMilisecTimeOut);  
   if ( hr == S_OK)  
   {  
      hr = pISSAsynchStatus->QueryInterface(IID_IRowset, (void**)&pRowset);  
      pISSAsynchStatus->Release();  
   }  
}  

ISSAsynchStatus::WaitForAsynchCompletion waits on the internal event object until the asynchronously executing operation is done or the dwMilisecTimeOut value is passed.

The following example shows asynchronous processing with multiple result sets:

DBPROP CmdProperties[1];  
  
// Set asynch mode for command.  
CmdProperties[0].dwPropertyID = DBPROP_ROWSET_ASYNCH;  
CmdProperties[0].vValue.vt = VT_I4;  
CmdProperties[0].vValue.lVal = DBPROPVAL_ASYNCH_INITIALIZE;  
  
hr = pICommand->Execute(  
   pUnkOuter,  
   IID_IMultipleResults,  
   pParams,  
   pcRowsAffected,  
   (IUnknown**)&pIMultipleResults);  
  
// Use GetResults for ISSAsynchStatus.  
hr = pIMultipleResults->GetResult(IID_ISSAsynchStatus, (void **) &pISSAsynchStatus);  
  
if (hr == DB_S_ASYNCHRONOUS)  
{  
   // Do some work here...  
  
   hr = pISSAsynchStatus->WaitForAsynchCompletion(dwMilisecTimeOut);  
   if (hr == S_OK)  
   {  
      hr = pISSAsynchStatus->QueryInterface(IID_IRowset, (void**)&pRowset);  
      pISSAsynchStatus->Release();  
   }  
}  

To prevent blocking, the client can check the status of a running asynchronous operation, as in the following example:

// Set the DBPROPVAL_ASYNCH_INITIALIZE bit in the   
// DBPROP_ROWSET_ASYNCH property before calling Execute().  
hr = pICommand->Execute(  
   pUnkOuter,  
   IID_ISSAsynchStatus,  
   pParams,  
   pcRowsAffected,  
   (IUnknown**)&pISSAsynchStatus);   
  
if (hr == DB_S_ASYNCHRONOUS)  
{  
   do{  
      // Do some work...  
      hr = pISSAsynchStatus->GetStatus(DB_NULL_HCHAPTER, DBASYNCHOP_OPEN, NULL, NULL, &ulAsynchPhase, NULL);  
   }while (DBASYNCHPHASE_COMPLETE != ulAsynchPhase)  
   if SUCCEEDED(hr)  
   {  
      hr = pISSAsynchStatus->QueryInterface(IID_IRowset, (void**)&pRowset);  
   }  
   pIDBAsynchStatus->Release();  
}  

The following example demonstrates how you can cancel the currently running asynchronous operation:

// Set the DBPROPVAL_ASYNCH_INITIALIZE bit in the   
// DBPROP_ROWSET_ASYNCH property before calling Execute().  
hr = pICommand->Execute(  
   pUnkOuter,  
   IID_ISSAsynchStatus,  
   pParams,  
   pcRowsAffected,  
   (IUnknown**)&pISSAsynchStatus);  
  
if (hr == DB_S_ASYNCHRONOUS)  
{  
   // Do some work...  
   hr = pISSAsynchStatus->Abort(DB_NULL_HCHAPTER, DBASYNCHOP_OPEN);  
}  

See Also

SQL Server Native Client Features
Rowset Properties and Behaviors
ISSAsynchStatus (OLE DB)