employees.cpp

EVC下OLEDB方法编写的数据库源代码

////////////////////////////////////////////////////////////////////////////////
// Microsoft SQL Server for CE Sample Code
//
// Microsoft Confidential
//
// Copyright 1999 - 2002 Microsoft Corporation.  All Rights Reserved.
//
// Component: Employees
//
// File: Employees.cpp
//
// Comment: Implementation of the Employees class.
// 
// Functions:
//			1. Create Northwind sample database
//			2. Create Employees table
//			3. Open a connection to Northwind database
//			4. Insert employee sample data using OLE DB API
//			5. Update employee info using OLE DB API
//			6. Queries through ICommandText
//			7. IRowsetIndex seek
//			8. Insert BLOB to database using ISequentialStream 
//			9. Load BLOB from database using ILockBytes
//			10. Wrap employee data insertions in a transaction
//
////////////////////////////////////////////////////////////////////////////////

#include "stdafx.h"
#include "Employees.h"
#include "dbcommon.h"

////////////////////////////////////////////////////////////////////////////////
// Declaration of function to handle messages for the employees dialog box
//
LRESULT CALLBACK EmployeesDlgProc(HWND, UINT, WPARAM, LPARAM);

////////////////////////////////////////////////////////////////////////////////
// Function: Employees::Employees()
//
// Description: Constructor
//
// Returns: none
//
// Notes:
//
////////////////////////////////////////////////////////////////////////////////
Employees::Employees(BOOL *pSuccess) : m_hWndEmployees(NULL), 
                                       m_hInstance(NULL),
                                       m_pIDBCreateSession(NULL),
                                       m_hBitmap(NULL)
{
	HRESULT hr = NOERROR;

	// Initialize environment
	//
	hr = CoInitializeEx(NULL, COINIT_MULTITHREADED);
	if(FAILED(hr))
	{
         if (pSuccess)
         {
            *pSuccess = FALSE;
         }

		 MessageBox(NULL, L"COM Initialization Failure.", L"Employees", MB_OK);
         return;
	}

    if (pSuccess)
    {
        *pSuccess = TRUE;
    }
}

////////////////////////////////////////////////////////////////////////////////
// Function: Employees::~Employees()
//
// Description: Destructor
//
// Returns: none
//
// Notes:
//
////////////////////////////////////////////////////////////////////////////////
Employees::~Employees()
{
	// Release interfaces
	//
	if(m_pIDBCreateSession)
	{
        HRESULT        hr = NOERROR;
        IDBInitialize *pIDBInitialize = NULL;

	    hr = m_pIDBCreateSession->QueryInterface(IID_IDBInitialize, (void **) &pIDBInitialize);
	    if(SUCCEEDED(hr))
	    {
            pIDBInitialize->Uninitialize();
            pIDBInitialize->Release();
        }
        
		m_pIDBCreateSession->Release();
	}

	if (m_hWndEmployees)
	{
       DestroyWindow(m_hWndEmployees);
  	}

	// Uninitialize the environment
	CoUninitialize();
}

////////////////////////////////////////////////////////////////////////////////
// Function: Create
//
// Description: Create a dialog to display employee info
//
// Returns: The handle to the window
//
// Notes:
//
////////////////////////////////////////////////////////////////////////////////
HWND Employees::Create(HWND hWndParent, HINSTANCE hInstance)
{
	HRESULT hr = NOERROR;
	RECT	rect;
	DWORD	dwCurSel;
	DWORD	dwEmployeeID;

	m_hInstance = hInstance;

	// Create the dialog window
	//
	GetClientRect(hWndParent, &rect);
	m_hWndEmployees = CreateDialog(	hInstance, 
									MAKEINTRESOURCE(IDD_DIALOG_EMPLOYEES), 
									hWndParent, 
									(DLGPROC)EmployeesDlgProc); 

    if (NULL == m_hWndEmployees)
    {
		MessageBox(NULL, L"Error - Create dialog", L"Northwind Oledb sample", MB_OK);
		return NULL;
    }

	// Open a connection to database and create a session object.
	//
	hr = InitDatabase();
	if (FAILED(hr))
	{
		MessageBox(NULL, L"Error - Initialize database", L"Northwind Oledb sample", MB_OK);
		return NULL;
	}

	// Populate combobox with employee name list.
	//
	hr = PopulateEmployeeNameList();
	if (FAILED(hr))
	{	
		MessageBox(NULL, L"Error - Retrive employee name list", L"Northwind Oledb sample", MB_OK);
		return NULL;
	}

	// Display the dialog window and center it under the commandbar
	//
	if (m_hWndEmployees)
	{
		MoveWindow(m_hWndEmployees, rect.left, rect.top, rect.right-rect.left,rect.bottom-rect.top, TRUE);
		ShowWindow(m_hWndEmployees, SW_SHOW);
		UpdateWindow(m_hWndEmployees);
	}

	// Set current selection of employee name combobox to index 0,
	//
	dwCurSel = SendDlgItemMessage(m_hWndEmployees, IDC_COMBO_NAME, CB_SETCURSEL, 0, 0);
	if (CB_ERR != dwCurSel)
	{
		// Retrieve current selected employee id from employee name combobox,
		// and load other employee info.
		//
		dwEmployeeID = SendDlgItemMessage(m_hWndEmployees, IDC_COMBO_NAME, CB_GETITEMDATA, dwCurSel, 0);
		hr = LoadEmployeeInfo(dwEmployeeID);
		if (FAILED(hr))
		{
			MessageBox(NULL, L"Error - Update employee info", L"Northwind Oledb sample", MB_OK);
			return NULL;
		}
		ShowEmployeePhoto();
	}

	return m_hWndEmployees;
}

////////////////////////////////////////////////////////////////////////////////
// Function: InitDatabase()
//
// Description: Open a connection to database, 
//				then create a session object.
//
// Returns: NOERROR if succesfull
//
// Notes:
//
////////////////////////////////////////////////////////////////////////////////
HRESULT Employees::InitDatabase()
{
    HRESULT			   	hr				= NOERROR;	// Error code reporting
	HANDLE				hFind;							// File handle
	WIN32_FIND_DATA		FindFileData;					// The file structure description  

	// If database exists, open it,
	// Otherwise, create a new database, insert sample data.
	//
	hFind = FindFirstFile(DATABASE_NORTHWIND, &FindFileData);
	if (INVALID_HANDLE_VALUE != hFind)
	{
		FindClose(hFind);
		hr = OpenDatabase();
	}
	else
	{
		// Create Northwind database
		//
		hr = CreateDatabase();
		if(SUCCEEDED(hr))
		{
			// Insert sample data
			//
			hr = InsertEmployeeInfo();
		}
	}

	return hr;
}

////////////////////////////////////////////////////////////////////////////////
// Function: CreateDatabase
//
// Description:
//		Create Northwind Database through OLE DB
//		Create Employees table
//
// Returns: NOERROR if succesfull
//
////////////////////////////////////////////////////////////////////////////////
HRESULT Employees::CreateDatabase()
{
	HRESULT				hr					 = NOERROR;	// Error code reporting
	DBPROPSET			dbpropset[1];					// Property Set used to initialize provider
	DBPROP				dbprop[1];						// property array used in property set to initialize provider

	IDBInitialize	    *pIDBInitialize      = NULL;    // Provider Interface Pointer
	IDBDataSourceAdmin	*pIDBDataSourceAdmin = NULL;	// Provider Interface Pointer
	IUnknown			*pIUnknownSession	 = NULL;	// Provider Interface Pointer
	IDBCreateCommand	*pIDBCrtCmd			 = NULL;	// Provider Interface Pointer
	ICommandText		*pICmdText			 = NULL;	// Provider Interface Pointer

	VariantInit(&dbprop[0].vValue);

	// Delete the DB if it already exists
	//
	DeleteFile(DATABASE_NORTHWIND);

   	// Create an instance of the OLE DB Provider
	//
	hr = CoCreateInstance(	CLSID_SQLSERVERCE_2_0, 
							0, 
							CLSCTX_INPROC_SERVER, 
							IID_IDBInitialize, 
							(void**)&pIDBInitialize);
	if(FAILED(hr))
	{
		goto Exit;
	}

	// 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(DATABASE_NORTHWIND);
	if(NULL == dbprop[0].vValue.bstrVal)
	{
		hr = E_OUTOFMEMORY;
		goto Exit;
	}

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

	// Get IDBDataSourceAdmin interface
	//
	hr = pIDBInitialize->QueryInterface(IID_IDBDataSourceAdmin, (void **) &pIDBDataSourceAdmin);
	if(FAILED(hr))
	{
		goto Exit;
	}

	// Create and initialize data store
	//
	hr = pIDBDataSourceAdmin->CreateDataSource(1, dbpropset, NULL, IID_IUnknown, &pIUnknownSession);
	if(FAILED(hr))	
    {
		goto Exit;
    }

    // Get IDBCreateSession interface
    //
  	hr = pIDBInitialize->QueryInterface(IID_IDBCreateSession, (void**)&m_pIDBCreateSession);
	if(FAILED(hr))
	{
		goto Exit;
	}

	// Get IDBCreateCommand interface
	//
	hr = pIUnknownSession->QueryInterface(IID_IDBCreateCommand, (void**)&pIDBCrtCmd);
	if(FAILED(hr))
	{
		goto Exit;
	}

	// Create a command object
	//
	hr = pIDBCrtCmd->CreateCommand(NULL, IID_ICommandText, (IUnknown**)&pICmdText);
	if(FAILED(hr))
	{
		goto Exit;
	}

	// Drop "Employees" table if it exists ignoring errors
	//
	ExecuteSQL(pICmdText, (LPWSTR)SQL_DROP_EMPLOYEES);

	// Create Employees table
	//
	hr = ExecuteSQL(pICmdText, (LPWSTR)SQL_CREATE_EMPLOYEES_TABLE);
	if(FAILED(hr))
	{
		goto Exit;
	}

	// Create Index
	// Note: The sample table has small amount of demo data, the index is created here.
	// In your application, to improve performance, index shoule be created after 
	// inserting initial data. 
	//
	hr = ExecuteSQL(pICmdText, (LPWSTR)SQL_CREATE_EMPLOYEES_INDEX);
	if(FAILED(hr))
	{
		goto Exit;
	}


Exit:
    // Clear Variant
    //
	VariantClear(&dbprop[0].vValue);

	// Release interfaces
	//
	if(pICmdText)
	{
		pICmdText->Release();
	}

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

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

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

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

	return hr;
}

////////////////////////////////////////////////////////////////////////////////
// Function: OpenDatabase
//
// Description:	Open a connection to database
//
// Returns: NOERROR if succesfull
//
////////////////////////////////////////////////////////////////////////////////
HRESULT Employees::OpenDatabase()
{
    HRESULT			   	hr				= NOERROR;	// Error code reporting
	DBPROP				dbprop[1];					// property used in property set to initialize provider
	DBPROPSET			dbpropset[1];				// Property Set used to initialize provider

    IDBInitialize       *pIDBInitialize = NULL;		// Provider Interface Pointer
	IDBProperties       *pIDBProperties	= NULL;		// Provider Interface Pointer

	VariantInit(&dbprop[0].vValue);		

    // Create an instance of the OLE DB Provider
	//
	hr = CoCreateInstance(	CLSID_SQLSERVERCE_2_0, 
							0, 
							CLSCTX_INPROC_SERVER, 
							IID_IDBInitialize, 
							(void**)&pIDBInitialize);
	if(FAILED(hr))
	{
		goto Exit;
	}

	// 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(DATABASE_NORTHWIND);
	if(NULL == dbprop[0].vValue.bstrVal)
	{
		hr = E_OUTOFMEMORY;
		goto Exit;
	}

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

	//Set initialization properties.
	//
	hr = pIDBInitialize->QueryInterface(IID_IDBProperties, (void **)&pIDBProperties);
    if(FAILED(hr))
    {
		goto Exit;
    }

	// Sets properties in the Data Source and initialization property groups
	//
    hr = pIDBProperties->SetProperties(1, dbpropset); 
	if(FAILED(hr))
    {
		goto Exit;
    }

	// Initializes a data source object 
	//
	hr = pIDBInitialize->Initialize();
	if(FAILED(hr))
    {
		goto Exit;
    }

    // Get IDBCreateSession interface
    //
  	hr = pIDBInitialize->QueryInterface(IID_IDBCreateSession, (void**)&m_pIDBCreateSession);

Exit:
    // Clear Variant
    //
	VariantClear(&dbprop[0].vValue);