dbconnection.cpp

visual c++ 实例编程

#include "stdafx.h"
#include "DBConnection.h"
#include "StrDefines.h"


CDBConnection::CDBConnection()
{
	m_pResultSet = NULL;
	m_pDynamicRow = NULL;
	m_nNumFields = 0;
	m_nCurRow = 0;

}

CDBConnection::~CDBConnection()
{
	// Be sure of deleting all pointers
	for(int n = 0 ; n < m_pFields.GetSize(); n++)
		delete m_pFields[n];
	m_pFields.RemoveAll();
	
	// and close any open DDE connections

	if(m_pResultSet != NULL)
		if(m_pResultSet->IsOpen())
			m_pResultSet->Close();

	if(m_pDynamicRow != NULL)
		if(m_pDynamicRow->IsOpen())
			m_pDynamicRow->Close();		
	
	if(m_DataBase.IsOpen())
		m_DataBase.Close();
}

// Function : ConnectToDataBase
// Purpose  : Connects to database initializing userinfo
// Params   : Usual connection params
// Return   : TRUE if success, otherwise FALSE
BOOL CDBConnection::ConnectToDataBase(CString DSN,CString sUser,CString sPassword,BOOL UseLogonDlg)
{
	
	CString sConnectionParam;
	BOOL bRet = FALSE;

	if (m_DataBase.IsOpen())
		m_DataBase.Close();

	// Selection from ODBC DSN
	if(!DSN.GetLength())
		bRet = m_DataBase.Open(NULL);
	else
	{
		m_sConnectedDSN = DSN; 
		
		// This does not display the ODBC connection dialog
		sConnectionParam.Format("ODBC;UID=%s;PWD=%s",sUser,sPassword);	
		bRet = m_DataBase.Open(_T(DSN),FALSE,FALSE,_T(sConnectionParam),TRUE );		
		
		// a prefared way of opening a databse. This did not function for delete, edit and append
		//	sConnectionParam.Format("DSN=%s;UID=%s;PWD=%s",DSN,sUser,sPassword);	
		//	bRet = m_DataBase.OpenEx(sConnectionParam,CDatabase::noOdbcDialog);
	}
	
	if(bRet)
	{
		m_sConnectedDSN = DSN;
	}
	else
		m_sConnectedDSN ="";
	
	return bRet;	
}//BOOL CDBConnection::ConnectToDataBase(CString DSN,CString sUser,CString sPassword,BOOL UseLogonDlg)

// Function : GetNextRow
// Purpose  : Retrives next row from a resulting recordset
// Params   : none
// Return   : Tab seperated string that contains all fieldvalues found in a record
//			  If Done it returns ", otherwise at least '\t'
CString CDBConnection::GetNextRow()
{	
	if(!m_pResultSet->IsOpen())
		return _T("");
	ASSERT(m_pResultSet->IsOpen());

	int nRowsFetched = m_pResultSet->GetRowsFetched();
	
	// Make sure the CDynamicBulkSet is cleaned up properly
	if(nRowsFetched == 0)
	{
		m_pResultSet->Close();
		return "";
	}//if(nRowsFetched == 0)

	if(m_nCurRow == nRowsFetched)
	{
		m_pResultSet->MoveNext(); // gets next recordset
		m_nCurRow = 0;
	}

	// Need to use this to convert LPSTR to UNICODE
	CString strData;
	CString sRet;


	if (m_pResultSet->IsEOF())
	{				
		m_pResultSet->Close();
		return "";
	}
	else
	{
		int nFields = m_pResultSet->GetODBCFieldCount();
		int nRowsFetched = m_pResultSet->GetRowsFetched();
		
		m_nCurRow++;

		// Display 1 rowset of data by field
		for (int nField = 0; nField < nFields; nField++)
		{			
			strData = GetFieldValue(nField);
			sRet += strData;	
		}//for (int nField = 0; nField < nFields; nField++)
	}//else

	sRet.TrimRight();
	sRet.TrimLeft();
	return sRet;
}//CString CDBConnection::GetNextRow()

// Function : AddNewRow
// Purpose  : Executes sql-stmt. It changes first all * to %
// Params   : The SQL-Statement to execute.
// Return   : TRUE if execution is successefull, otherwise FALSE
BOOL CDBConnection::ExecuteStatement(CString SqlStmt)
{
	BOOL bRet = FALSE;

	// replace all * and use % as wildcard
	SqlStmt.Replace("*'","%'");

	if(m_pResultSet != NULL)
		if(m_pResultSet->IsOpen())
			m_pResultSet->Close();

	CDynamicBulkSet *rs = new CDynamicBulkSet (&m_DataBase);

	m_pResultSet = rs;

	// Open the recordset as readonly object
	try
	{
		bRet = m_pResultSet->Open(CRecordset::snapshot, SqlStmt,
		   CRecordset::none | CRecordset::useMultiRowFetch);
			//CRecordset::readOnly | CRecordset::useMultiRowFetch);
	}
	catch(CDBException e)
	{
		AfxMessageBox(e.m_strError,MB_OK | MB_ICONSTOP);
		return FALSE;
	}
	m_nNumFields = m_pResultSet->GetODBCFieldCount();
	m_nCurRow = 0;
	return bRet;
}//BOOL CDBConnection::ExecuteStatement(CString SqlStmt)
	

// Function : GetFieldValue
// Purpose  : Retrives a field value. after row is pointed to in the fetched recordset
// Params   : nField : is the zero based index of the required field
// Return   : Value for the desired field
CString CDBConnection::GetFieldValue(int nField)
{

	long* rgLength;
	LPSTR rgData;
	CString sRet,strData;

	// set up the correct data and length arrays
	rgData = (LPSTR)m_pResultSet->m_ppvData[nField];
	rgLength = (long*)m_pResultSet->m_ppvLengths[nField];
	
	int nStatus = m_pResultSet->GetRowStatus(m_nCurRow);

	// Get the string to display
	if (nStatus == SQL_ROW_DELETED)
		strData = _T("<DELETED>");
	else if (nStatus == SQL_ROW_NOROW)
		// Shouldn't get this since rows fetched is checked
		strData = _T("<NO_ROW>");
	else if (rgLength[m_nCurRow - 1] == SQL_NULL_DATA)
		strData = _T("");// Field is empty == _T("<NULL>")
	else
		strData = &rgData[(m_nCurRow -1) * MAX_TEXT_LEN];					
	
	sRet += strData;
	
	// Add a tab to avoid fields containing <NULL>
	sRet += '\t';	
	return sRet;
}//CString CDBConnection::GetFieldValue(int nField)

// Function : AddNewRow
// Purpose  : Adds a new row to a table
// Params   : sTable Table were values are to be inserted
// Return   : TRUE if success, otherwise FALSE
BOOL CDBConnection::AddNewRow(CString sTable)
{
	CDynamicRow *pDynRow = new CDynamicRow(&m_DataBase);
	try
	{
		pDynRow->m_sTableName = sTable;
			
		for(int i = 0; i < m_pFields.GetSize();i++)	
			pDynRow->AddExchangeField(m_pFields.GetAt(i)->m_sColumn);	
			
		if(pDynRow->Open())
		{			
			if(!pDynRow->CanAppend())
			{
				AfxMessageBox(IDS_DB_CANNOTMODIFY,MB_OK | MB_ICONSTOP);
				if(pDynRow != NULL)
					if(pDynRow->IsOpen())
						pDynRow->Close();
				delete pDynRow;
				return FALSE;
			}						
			
			pDynRow->AddNew();			
			for(int i = 0; i < m_pFields.GetSize();i++)				
				pDynRow->m_pFields.ElementAt(i)->m_sValue = m_pFields.GetAt(i)->m_sValue;
			
			if(!pDynRow->Update())
			{
				AfxMessageBox(IDS_DB_UPDATEFAILD,MB_OK | MB_ICONSTOP);
				if(pDynRow != NULL)
				if(pDynRow->IsOpen())
					pDynRow->Close();

				delete pDynRow;
				return FALSE;
			}			
		}
		else
		{
			if(pDynRow != NULL)
				if(pDynRow->IsOpen())
					pDynRow->Close();
			delete pDynRow;
			AfxMessageBox(IDS_DB_OPENFAILD,MB_OK | MB_ICONSTOP);
		}
	}
	catch(CDBException e)
	{
		AfxMessageBox(e.m_strError,MB_OK | MB_ICONSTOP);
		e.Delete();
		if(pDynRow != NULL)
			if(pDynRow->IsOpen())
				pDynRow->Close();

		delete pDynRow;
		return FALSE;
	}

	if(pDynRow != NULL)
		if(pDynRow->IsOpen())
			pDynRow->Close();

	delete pDynRow;
	return TRUE;

}//BOOL CDBConnection::AddNewRow(CString sTable)

// Function : DeleteRow
// Purpose  : Deletes a single row from a table in the database.
// Params   : tablename from where we are going to delete
// Return   : TRUE if success, otherwise FALSE
BOOL CDBConnection::DeleteRow(CString sTable)
{
	CDynamicRow *pDynRow = new CDynamicRow(&m_DataBase);
		
	try
	{
		pDynRow->m_sTableName = sTable;
		CString sFilter;

		for(int i = 0; i < m_pFields.GetSize();i++)	
		{
			pDynRow->AddExchangeField(m_pFields.GetAt(i)->m_sColumn);
			
			sFilter += m_pFields.GetAt(i)->m_sColumn;
			sFilter += "= '";
			sFilter += m_pFields.GetAt(i)->m_sValue;
			sFilter += "'";

			if(i < m_pFields.GetSize() - 1)
				sFilter += " AND ";
		}//for(int i = 0; i < m_pFields.GetSize();i++)	

		pDynRow->m_strFilter = sFilter;
		
		if(pDynRow->Open())
		{			
			if(pDynRow->GetRowsFetched()> 0)				
				pDynRow->Delete(); 			
			else
				AfxMessageBox(IDS_DB_ROWSETEMPTY,MB_OK | MB_ICONSTOP);
		}
		else
		{
			AfxMessageBox(IDS_DB_OPENFAILD,MB_OK | MB_ICONSTOP);
		}
	}
	catch(CDBException e)
	{

		AfxMessageBox(e.m_strError,MB_OK | MB_ICONSTOP);
		e.Delete();
		
		if(pDynRow != NULL)
			if(pDynRow->IsOpen())
				pDynRow->Close();
		delete pDynRow;
		return FALSE;
	}

	if(pDynRow != NULL)
		if(pDynRow->IsOpen())
			pDynRow->Close();

	delete pDynRow;
	return TRUE;
}//BOOL CDBConnection::DeleteRow(CString sTable);

// Function : UpdateRow
// Purpose  : Updates a single row from a table in the database.
// Params   : tablename where we are going to update
// Return   : TRUE if success, otherwise FALSE
BOOL CDBConnection::UpdateRow(CString sTable)
{

	CDynamicRow *pDynRow = new CDynamicRow(&m_DataBase);
	pDynRow->m_bUpdateRow = FALSE;	
	try
	{
		pDynRow->m_sTableName = sTable;
		
		CString sFilter,sFieldValue;
		for(int i = 0; i < m_pFields.GetSize();i++)	
		{
			sFieldValue = m_pFields.GetAt(i)->m_sValue;
			
			pDynRow->AddExchangeField(m_pFields.GetAt(i)->m_sColumn);

			if(sFieldValue.GetLength() > 0)
			{	
				sFilter += m_pFields.GetAt(i)->m_sColumn;
				sFilter += " LIKE '";
				sFilter += m_pFields.GetAt(i)->m_sValue;
				sFilter += "'";
				
				if(i < m_pFields.GetSize() - 1)
					sFilter += " AND ";
			}//if(sFieldValue.GetLength() > 0)
		}//for(int i = 0; i < m_pFields.GetSize();i++)	

		
		sFilter.TrimLeft();
		sFilter.TrimRight();

		int nLastAndPos = sFilter.Find("AND",sFilter.GetLength() - 5);
		if( nLastAndPos == ( sFilter.GetLength() - 3))
			sFilter = sFilter.Left(sFilter.GetLength() - 3);

		pDynRow->m_strFilter = sFilter;

		if(pDynRow->Open())
		{
			if(!pDynRow->CanUpdate())
			{
				AfxMessageBox(IDS_DB_CANNOTMODIFY,MB_OK | MB_ICONSTOP);
				
				if(pDynRow != NULL)
					if(pDynRow->IsOpen())
						pDynRow->Close();
				delete pDynRow;
				return FALSE;
			}
						
			pDynRow->Edit();		
					
			for(int i = 0; i < m_pFields.GetSize();i++)	
			{
				CString sMsg;
				sMsg = m_pFields.GetAt(i)->m_sNewValue;
				sMsg += "\n";
				pDynRow->m_pFields.ElementAt(i)->m_sValue = m_pFields.GetAt(i)->m_sNewValue;
			}
		
			if(!pDynRow->Update())
			{
				AfxMessageBox(IDS_DB_UPDATEFAILD,MB_OK | MB_ICONSTOP);
				if(pDynRow != NULL)
					if(pDynRow->IsOpen())
						pDynRow->Close();
				delete pDynRow;
				return FALSE;
			}			
		}
		else
		{
			if(pDynRow != NULL)
				if(pDynRow->IsOpen())
					pDynRow->Close();
	
			delete pDynRow;
			AfxMessageBox(IDS_DB_OPENFAILD,MB_OK | MB_ICONSTOP);
		}
	}
	catch(CDBException e)
	{
		AfxMessageBox(e.m_strError,MB_OK | MB_ICONSTOP);
		e.Delete();
		if(pDynRow != NULL)
			if(pDynRow->IsOpen())
				pDynRow->Close();
		delete pDynRow;
		return FALSE;
	}

	if(pDynRow != NULL)
		if(pDynRow->IsOpen())
			pDynRow->Close();
	delete pDynRow;
	return TRUE;
}//BOOL CDBConnection::UpdateRow(CString sTable)

// Function : PrepareFieldExchange
// Purpose  : Prepares the dataset for updating......
// Params   : none
// Return   : TRUE if success, otherwise FALSE
BOOL CDBConnection::PrepareFieldExchange()
{
	for(int n = 0 ; n < m_pFields.GetSize(); n++)
		delete m_pFields[n];
	m_pFields.RemoveAll();
	return TRUE;
}