Step 5. Implementing Database Connection

On this step we create a class that encapsulates database connection. It uses ATL OLE DB templates to get access to OLE DB providers for MS Access, MS SQL and ODBC data sources.

1. Create a new class. Select Project -> Add Class... from the main menu, select Generic C++ Class and click Open. Name this class CDB and click Finish.

2. Double-click CDB class in the Class View window. Class declaration is opened. Add a private member m_Session with CSession type and add manually a public function that returns a reference to this variable:

class CDB
{
public:
	CDB(void);
	~CDB(void);

	CSession& Session()
	{
		return m_Session;
	}

private:
	CSession m_Session;
};
		

This variable stores actual connection to a data source.

2. Right-click CDB class in Class View and through a Add Variable wizard add a function with void return type, public access and CloseSession name without any parameters. Find its empty definition and replace it with the following code:

void CDB::CloseSession(void)
{
	if (m_Session.m_spOpenRowset!=NULL)
		m_Session.Close();
}

This method closes a database session if it's open. Find CDB class destructor in the same file and add a call to this method. No destructor should be

CDB::~CDB(void)
{
	CloseSession();
}
		

3. With a class wizard add a new method to CDB class with void return type, public access type, FormatErrors name and CString& errStr argument. Change its body to the following code:

void CDB::FormatErrors(CString& errStr)
{
	USES_CONVERSION;

	CDBErrorInfo errInfo;
	DWORD numRecords;
	HRESULT hRes=errInfo.GetErrorRecords(&numRecords);
	if (FAILED(hRes) || hRes == S_FALSE || numRecords == 0)
		return;

	LCID lcid = GetUserDefaultLCID();
	CComBSTR str;
	CComPtr ptr;

	for (DWORD i=0;i<numRecords;i++)
	{
		errInfo.GetErrorInfo(i,lcid,&ptr);
		ptr->GetDescription(&str);
		errStr+=W2T(str);
		errStr+=_T("; ");
	}
}
		

4. Add a new method with the following parameters:

• Access type - public
• Name - OpenMSAccess
• Return type - bool
• Parameter - CString dbPath

Replace its bode with the following code:

bool CDB::OpenMSAccess(CString dbPath)
{
	CDataSource db;
	CDBPropSet dbinit(DBPROPSET_DBINIT);
	
	CloseSession();

	dbinit.AddProperty(DBPROP_INIT_DATASOURCE, dbPath);
	HRESULT hr=db.Open(_T("Microsoft.Jet.OLEDB.4.0"), &dbinit);

	if (FAILED(hr))
		return false;
	
	return m_Session.Open(db)==S_OK;
}
	  

This code opens a data source represented by Microsoft.Jet.OLEDB.4.0 OLE DB provider and on success establishes a session with it. The method will be used to connect to MS Access databases.

5. Add a new method with the following parameters:

• Access type - public
• Name - OpenODBC
• Return type - bool
• Parameter 1- CString dsn
• Parameter 2 - CString dbUser
• Parameter 3 - CString dbPassword

Replace its bode with the following code:

bool CDB::OpenODBC(CString dsn, CString dbUser, CString dbPassword)
{
	USES_CONVERSION;
	CDataSource db;
	
	CloseSession();

	CString connStr;
	connStr.Format(_T("Provider=MSDASQL;Data Source=%s;UID=%s;PWD=%s"),
		dsn,dbUser,dbPassword);
	HRESULT hr=db.OpenFromInitializationString(CT2W(connStr));
	if (FAILED(hr))
		return false;
	
	return m_Session.Open(db)==S_OK;
}
	  

This method opens a connection with ODBC data source with specified DSN, user name and password.

6. Add a new method with the following parameters:

• Access type - public
• Name - OpenMSSQL
• Return type - bool
• Parameter 1 - CString dbServer
• Parameter 2 - CString dbCatalog
• Parameter 3 - CString dbUser
• Parameter 4 - CString dbPassword
• Parameter 5 - bool winAuthen

Replace its declaration with the following code:

bool CDB::OpenMSSQL(CString dbServer, CString dbCatalog, CString dbUser, CString dbPassword, bool winAuthen)
{
	USES_CONVERSION;
	
	CloseSession();

	CDataSource db;
	CDBPropSet dbinit(DBPROPSET_DBINIT);

	if (!winAuthen)
	{
		dbinit.AddProperty(DBPROP_AUTH_PASSWORD, dbPassword);
		dbinit.AddProperty(DBPROP_AUTH_USERID, dbUser);
	}
	else
		dbinit.AddProperty(DBPROP_AUTH_INTEGRATED, OLESTR("SSPI"));

	dbinit.AddProperty(DBPROP_INIT_CATALOG, dbCatalog);
	dbinit.AddProperty(DBPROP_INIT_DATASOURCE, dbServer);

	HRESULT hr = db.Open(L"SQLOLEDB.1", &dbinit);

	if (FAILED(hr))
		return false;
	return m_Session.Open(db)==S_OK;
}
	  

This method uses native MS SQL OLE provider to establish connection with a server.

7. By this point we've created all methods of this class. Let's add a variable to CExtension class. With a wizard add a variable to CExtension class with private access type, CDB type and m_DB name.

8. Finally, we should open a connection to a database during server extension initialization. It's necessary if user is authenticated through a database or accounting records are written to the database.

Double-click InitializeEx subnode of CExtension class in Class View. Change this method implementation so it should look like the following:

STDMETHOD(InitializeEx)(VARIANT_BOOL start, IServer * pServer)
{
	if (start==VARIANT_TRUE)
	{
		m_pServ2=pServer;
		m_pLivAcct=pServer;
		m_pCSVAcct=pServer;

		if (!ReadAuthenticationSettings())
			return E_INVALIDARG;
		if (!ReadAuthenMethodsSettings())
			return E_INVALIDARG;
		if (!ReadAccountingSettings())
			return E_INVALIDARG;

		if (m_AuthenType==db || m_AcctMethod==database)
		{
			bool res=true;
			switch(m_DBType)
			{
			case access:
				if (!m_DB.OpenMSAccess(m_DBPath))
					res=false;
				break;
			case mssql:
				if (!m_DB.OpenMSSQL(m_DBServer,m_DBCatalog,
					m_DBUser,m_DBPassword,m_bUseNTSecurity))
					res=false;
				break;
			case odbc:
				if (!m_DB.OpenODBC(m_DSN,m_DBUser,m_DBPassword))
					res=false;
				break;
			};

			if (!res)
			{
				CString s;
				m_DB.FormatErrors(s);
				m_pServ2->LogExtensionError(E_FAIL,(LPCTSTR)s);
				return E_FAIL;
			}
		}
		HRESULT hr=AdjustPrivileges();
		if (FAILED(hr))
			return hr;
	}
	return S_OK;
}
		

AdjustPrivileges function will be created in the next tutorial step.

Go to the next step.