database.cpp

基于OLE的SQL CE数据库的C++操作类

#include "stdafx.h"
#include <commctrl.h>
#include <ssceoledb30.h>
#include "cemapi.h"
#include "Database.h"
//------------------------------------------------------------------------------------------
//  PURPOSE:  
//		- Connect the given SQL Server CE Database
//  PARAMETERS:
//		- lpszDBName	:: SQL Server CE Database filename in fullpath
//  OPERATION:
//		- ...
//  RETURN VALUE:
//      - HRESULT
//------------------------------------------------------------------------------------------
Database::Database()
{
	ZeroMemory(g_TTSInfo,1024*sizeof(TCHAR)); // 存放TTS信息
	ZeroMemory(szBuffer,1024*sizeof(TCHAR));//存放执行sql的字符串.
	pIDBInitialize		 = NULL;
	pIUnknownSession	 = NULL;

	pIDBCreateSession	 = NULL;
	pIDBProperties		 = NULL;

	// SQL query command text handle object
	pIDBCrtCmd			 = NULL;
	pICmdText			 = NULL;
	pITxnLocal			= NULL;
	pIRowset			= NULL;
}
//-------------------------------------------------------------------------------------
//  PURPOSE:  
//		- Disconnect from the given SQL Server CE Database
//  PARAMETERS:
//		- lpszDBName	:: SQL Server CE Database filename in fullpath
//  OPERATION:
//		- ...
//  RETURN VALUE:
//      - HRESULT
//-------------------------------------------------------------------------------------
HRESULT Database::DisconnectDB ()
{

	if(NULL != pIRowset)
	{
		pIRowset->Release();
		pIRowset = NULL;
	}
	if(NULL != pITxnLocal)
	{
		pITxnLocal->Release();
		pITxnLocal = NULL;
	}
	if(NULL != pICmdText)
	{
		pICmdText->Release();
		pICmdText = NULL;
	}
	if(NULL != pIDBCrtCmd)
	{
		pIDBCrtCmd->Release();
		pIDBCrtCmd = NULL;
	}

	if(NULL != pIDBCreateSession)
	{
		pIDBCreateSession->Release();
		pIDBCreateSession = NULL;
	}

	if(NULL != pIUnknownSession)
	{
		pIUnknownSession->Release();
		pIUnknownSession = NULL;
	}

	if(NULL != pIDBProperties)
	{
		pIDBProperties->Release();
		pIDBProperties = NULL;
	}

	// Release interfaces
	if(NULL != pIDBInitialize)
	{
		pIDBInitialize->Release();
		pIDBInitialize = NULL;
	}

	return S_OK;
}
//链接数据库
HRESULT Database::ConnectDB (LPTSTR lpszDBName)
{

	HRESULT				hr = NOERROR;
	DBPROPSET			dbpropset[1];	// Property Set used to initialize provider
	DBPROP				dbprop[1];		// property array used in property set to initialize provider

	// Create the SQL Server CE provider
	hr = CreateSqlSvrCeProvider();
	/*hr = CoCreateInstance(	CLSID_SQLSERVERCE_3_0, 
	0, 
	CLSCTX_INPROC_SERVER, 
	IID_IDBInitialize, 
	(void**)&pIDBInitialize);*/
	// Validation
	if(FAILED(hr))
		goto CleanExit;
	// Validate the return value


	// Initialize...
	VariantInit(&dbprop[0].vValue);
	// Initialize a property with name of database
	dbprop[0].dwPropertyID	 = DBPROP_INIT_DATASOURCE;
	dbprop[0].dwOptions		 = DBPROPOPTIONS_REQUIRED;
	dbprop[0].vValue.vt		 = VT_BSTR;
	dbprop[0].vValue.bstrVal = SysAllocString(lpszDBName);

	// Validation
	if(NULL == dbprop[0].vValue.bstrVal)
	{
		// Set return value
		hr = E_OUTOFMEMORY;
		goto CleanExit;
	}

	// Initialize the property set
	dbpropset[0].guidPropertySet = DBPROPSET_DBINIT;
	dbpropset[0].rgProperties	 = dbprop;
	dbpropset[0].cProperties	 = sizeof(dbprop)/sizeof(dbprop[0]);

	// Query the IDBProperties interface
	hr = pIDBInitialize->QueryInterface(IID_IDBProperties,
		(void **)&pIDBProperties);
	// Validation
	if(FAILED(hr))
		goto CleanExit;
	else
		pIDBInitialize->AddRef(); 

	// Create the given database...
	hr = pIDBProperties->SetProperties(1,
		dbpropset); 
	// Validation
	if(FAILED(hr))
		goto CleanExit;

	//// Free the used memory
	//SysFreeString(dbprop[0].vValue.bstrVal);

	// Initialize the SQL Server CE provider.
	pIDBInitialize->Initialize(); 

	// Create new database session...
	hr = CreateDBSession();


CleanExit:
	// Release the used memory
	VariantClear(&dbprop[0].vValue);
	if(pIDBProperties)
	{
		pIDBProperties->Release();
	}

	if (pIDBInitialize)
	{
		pIDBInitialize->Release();
	}

	// Only execute the following command when either one of the
	// above command fail.
	if (FAILED(hr))
		// Disconnect the database/reset the OLE DB variable
		DisconnectDB();
	return hr;
}
//////////////////////////////////////////////////////////////////////////
//FUNCTION: GetLocation
// Accorded to the phone number, got area code about the caller
VOID Database::GetLocation(TCHAR *phoneNumber)
{

	TCHAR t_buffer[4] = {NULL};
	int j = 0;
	if (7 == lstrlen(phoneNumber))
	{
		//ZeroMemory(g_TTSInfo, sizeof(g_TTSInfo));
		lstrcpy(g_TTSInfo, TEXT("Local Number"));
		return;
	}
	if(11 == lstrlen(phoneNumber))
		j = 1;
	for(int i=0; i<3; i++,j++)
	{
		t_buffer[i] = phoneNumber[j];
	}


	ConnectDB(TEXT("\\Windows\\npa.sdf"));
	wsprintf(szBuffer,TEXT("SELECT Location FROM AreaCode WHERE Npa=%s"),t_buffer);
	GetRowset(szBuffer);
	DisconnectDB ();
}

//---------------------------------------------------------------------------------------------
//  PURPOSE:  
//		- Execute the given SQL statement and return a RowSet object.
//  PARAMETERS:
//		- lpszQuery	:: SQL query command string.
//  OPERATION:
//		- ...
//  RETURN VALUE:
//      - HRESULT

// NOTE:
//		THE FOLLOWING CODE SHOW HOW TO CREATE A ROWSET OBJECT
//		WITH COMMAND OBJECT (SQL STATEMENT).
//----------------------------------------------------------------------------------------------
HRESULT Database::GetRowset(LPTSTR lpszQuery)
{
	HRESULT			hr = NOERROR;
	// Set the SQL query statement
	hr = pICmdText->SetCommandText(DBGUID_SQL,
		lpszQuery); 
	if(FAILED(hr))
		goto CleanExit;
	// Execute the SQL query statement
	hr = pICmdText->Execute(NULL,
		IID_IRowset,
		NULL,
		NULL,
		(IUnknown **)&pIRowset);
	if (!FAILED(hr))
	{
		if(NULL != pIRowset)
			// Proceed to walk through the retrieve Rowset object
			ProcessRowset(pIRowset);
	}

CleanExit:

	if (NULL != pIRowset)
	{
		pIRowset->Release();
		pIRowset = NULL;
	}

	if (NULL != pICmdText)
	{
		pICmdText->Release();
		pICmdText = NULL;
	}
	if (FAILED(hr))
		// Disconnect the database/reset the OLE DB variable
		DisconnectDB();

	return hr;
}
//创建Provider
HRESULT Database::CreateSqlSvrCeProvider (void)
{
	HRESULT		hr = NOERROR;

	// Create the SQL Server CE provider。

	hr = CoCreateInstance(	CLSID_SQLSERVERCE_3_0, 
		0, 
		CLSCTX_INPROC_SERVER, 
		IID_IDBInitialize, 
		(void**)&pIDBInitialize);	 
	//This function creates on the local system a single uninitialized object of the class associated with a specified class identifier. 
	return hr;
}
//-----------------------------------------------------------------------------------
//  PURPOSE:  
//		- Create a new SQL Server CE Database session of the connected database...
//  PARAMETERS:
//		- NULL
//  OPERATION:
//		- ...
//  RETURN VALUE:
//      - HRESULT
//-----------------------------------------------------------------------------------
HRESULT Database::CreateDBSession (void)//创建会话
{


	HRESULT				hr = NOERROR;

	// Query the IDBCreateSession interface
	hr = pIDBInitialize->QueryInterface(IID_IDBCreateSession,
		(void **)&pIDBCreateSession);
	if(FAILED(hr))
		goto CleanExit;

	// Create a new database session...
	hr = pIDBCreateSession->CreateSession( NULL,IID_IDBCreateCommand,(IUnknown**) &pIDBCrtCmd);
	if(FAILED(hr))
		goto CleanExit;	

	// Create a command object pointer
	hr = pIDBCrtCmd->CreateCommand(NULL,
		IID_ICommandText,
		(IUnknown**)&pICmdText);

CleanExit:

	return hr;

}
//----------------------------------------------------------------------------------------------
//  PURPOSE:  
//		- Retrieve and display data resulting from
//		  a query specified in GetRowset function
//  PARAMETERS:
//		- NIL
//  OPERATION:
//		- ...
//  RETURN VALUE:
//      - HRESULT
//----------------------------------------------------------------------------------------------
HRESULT Database::ProcessRowset (IRowset *pIRowset)
{
	HRESULT			hr = NOERROR;

	// FOR LISTVIEW USED ONLY
	LVITEM			pitem;

	ULONG			lColumn		= 0;
	ULONG			lNumCols	= 0;
	ULONG			lCount		= 0;
	ULONG           lNumRowsRetrieved = 0;//../
	ULONG           ConsumerBufColOffset = 0;

	IAccessor		*pIAccessor    = NULL;
	IColumnsInfo	*pIColumnsInfo = NULL;
	DBCOLUMNINFO	*pDBColumnInfo = NULL;
	DBBINDING		*pBindings	   = NULL;

	HACCESSOR       hAccessor	   = NULL;
	HROW            hRows[10];
	HROW			*pRows		   = &hRows[0];
	BYTE			*pBuffer	   = NULL;

	WCHAR			*pStringsBuffer = NULL;





	// Obtain access to the IColumnInfo interface, from the Rowset object.
	hr = pIRowset->QueryInterface(IID_IColumnsInfo,
		(void **)&pIColumnsInfo);
	// Validation
	if(FAILED(hr))
	{
		// Update status
		MessageBox(NULL,TEXT("Failed to query IColumnsInfo interface!"),TEXT("error"),MB_OK);
		// Terminate the current routine
		goto CleanExit;
	}
	else
		pIRowset->AddRef(); 

	// Retrieve the column information.
	pIColumnsInfo->GetColumnInfo(&lNumCols,
		&pDBColumnInfo,
		&pStringsBuffer);

	// Free the column information interface.
	pIColumnsInfo->Release();

	// Create a DBBINDING array.
	pBindings = new DBBINDING[lNumCols];

	// Using the ColumnInfo structure, fill out the pBindings array.
	for(lCount=0; lCount<lNumCols; lCount++)
	{
		pBindings[lCount].iOrdinal		= lCount+1;
		pBindings[lCount].obValue		= ConsumerBufColOffset;
		pBindings[lCount].pTypeInfo		= NULL;
		pBindings[lCount].pObject		= NULL;
		pBindings[lCount].pBindExt		= NULL;
		pBindings[lCount].dwPart		= DBPART_VALUE;
		pBindings[lCount].dwMemOwner	= DBMEMOWNER_CLIENTOWNED;
		pBindings[lCount].eParamIO		= DBPARAMIO_NOTPARAM;
		// NOTE:
		//		DUE TO THE OUTPUT DATA TYPE OF EACH FIELDS WAS
		//		CONVERTED INTO "DBTYPE_WSTR" WITHIN THE SQL SERVER CE
		//		(HARDCODED) INSTEAD OF USING THE ORIGINAL DATA TYPE AS:
		//			pBindings[lCount].wType
		//		AS A RESULT, IT WILL NO LONGER FOLOOW THE VALUE STORE
		//		IN pDBColumnInfo[lCount].ulColumnSize
		//
		//		HENCE, THE MAXIMUM COLUMN SIZE WAS SET TO 48BYTES
		//		IT CAN BE ANY VALUE, AS LONG AS IT IS LARGE ENOUGH
		//		TO HOLD THE CONVERTED DATA FOR ALL THE READ COLUMNS.
		pBindings[lCount].cbMaxLen	= 200;	
		pBindings[lCount].dwFlags		= 1;
		// NOTE:
		//		DUE TO DATA CONVERSION ERROR, SO WE HARDCODED THE
		//		DATA TYPE TO DBTYPE_WSTR INSTEAD OF USING THE DATA
		//		TYPE OBTAIN FROM THE DBCOLUMNINFO STRUCTURE AS:
		//			DBColumnInfo[lCount].wType
		//		THROUGH THE GetColumnInfo INTERFACE
		pBindings[lCount].wType			= DBTYPE_WSTR;
		pBindings[lCount].bPrecision	= pDBColumnInfo[lCount].bPrecision;
		pBindings[lCount].bScale		= pDBColumnInfo[lCount].bScale;
		// NOTE:
		//		DUE TO THE DATA TYPE WAS HARDCODED TO DBTYPE_WSTR. HENCE
		//		THE "ColumnSize" VALUE IN THE DBCOLUMNINFO STRUCTURE AS:
		//			pDBColumnInfo[lCount].ulColumnSize
		//		WILL NO LONGER APPLICABLE AND THE NEW HARDCODED SIZE OF
		//		48 BYTES WAS USED IN THIS CASE.
		//		THIS VALUS SHOULD BE CHANGE ARCCODING TO THE DEFINE DATA
		//		TYPE AND NOT NECESSARY MUST BE 48 BYTES.

		// Compute the next buffer offset.
		ConsumerBufColOffset += 200;	
	}

	// Get the IAccessor interface.
	hr = pIRowset->QueryInterface(IID_IAccessor,
		(void **)&pIAccessor);
	// Validation
	if(FAILED(hr))
	{
		// Update status
		MessageBox(NULL,TEXT("Failed to query IColumnsInfo interface!"),TEXT("error"),MB_OK);
		// Terminate the current routine
		goto CleanExit;
	}
	else
		pIRowset->AddRef(); 

	// Create an accessor from the set of bindings (pBindings).
	hr=pIAccessor->CreateAccessor(DBACCESSOR_ROWDATA, 
		lNumCols,
		pBindings,
		0,
		&hAccessor,
		NULL);

	// Get a set of 10 rows.
	hr=pIRowset->GetNextRows(NULL,
		0,
		1,
		&lNumRowsRetrieved,
		&pRows);


	// Allocate space for the row buffer.
	pBuffer = new BYTE[ConsumerBufColOffset];
	// Display the rows.

	while(lNumRowsRetrieved > 0)
	{
		//For each row, print the column data.
		for(lCount=0; lCount<lNumRowsRetrieved; lCount++)
		{
			// Initialize...
			memset(pBuffer, 0, ConsumerBufColOffset);
			// Get the row data values.
			pIRowset->GetData(hRows[lCount], hAccessor, pBuffer);

			// -- Item
			pitem.mask = LVIF_TEXT| LVIF_IMAGE | LVIF_PARAM;
			//LVM_GETITEMCOUNT   This message retrieves the number of items in a list-view control.
			pitem.pszText = (LPTSTR)&pBuffer[pBindings[0].obValue];

			if (pitem.pszText != NULL)
			{
				_tcscat(g_TTSInfo, _T(" "));
				_tcscat(g_TTSInfo, pitem.pszText);
			}
		}
		// Release the rows retrieved.
		hr=pIRowset->ReleaseRows(lNumRowsRetrieved, 
			hRows, 
			NULL, 
			NULL, 
			NULL);
		if(FAILED(hr))
		{
			// Update status
			MessageBox(NULL,TEXT("Failed to query IColumnsInfo interface!"),TEXT("error"),MB_OK);
			// Terminate the current routine
			goto CleanExit;
		}

		// Get the next set of 10 rows.
		hr=pIRowset->GetNextRows(NULL,
			0,
			1,
			&lNumRowsRetrieved,
			&pRows);
		if(FAILED(hr))
		{
			// Update status
			MessageBox(NULL,TEXT("Failed to query IColumnsInfo interface!"),TEXT("error"),MB_OK);
			// Terminate the current routine
			goto CleanExit;
		}
	}
CleanExit:

	if (NULL != pIColumnsInfo)
	{
		pIColumnsInfo->Release();
		pIColumnsInfo = NULL;
	}
	if (NULL != pBindings)
	{
		delete [] pBindings;
		pBindings = NULL;
	}
	if(NULL !=pBuffer)
	{
		delete [] pBuffer;
		pBuffer = NULL;
	}
	if (NULL != pIAccessor)
	{
		pIAccessor->Release();
		pIAccessor = NULL;
	}
	return hr;
}