myrecordset.cpp

老外写的一个很不错的数据库操作类,用起来很方便,写法也很规范,可以做为学习的范例或实际运用

// MyRecordset.cpp: implementation of the CMyRecordset class.
//
//////////////////////////////////////////////////////////////////////

#include "stdafx.h"
#include "MyRecordset.h"

#ifdef _DEBUG
#undef THIS_FILE
static char THIS_FILE[]=__FILE__;
#define new DEBUG_NEW
#endif

//////////////////////////////////////////////////////////////////////
// Construction/Destruction
//////////////////////////////////////////////////////////////////////

CMyRecordset::CMyRecordset(CDatabase* pDatabase):CRecordset(pDatabase)
{
	m_pDatabase = pDatabase;
	m_pFields = NULL;
	m_pParams = NULL;
	m_nParams = 0;
	m_nInputParams = 0;
	m_nOutputParams = 0;
	m_nInputOutputParams = 0;
	m_nReturnParam = 0;
}

CMyRecordset::~CMyRecordset()
{
	if( m_pFields != NULL )
		delete[]	m_pFields;
	m_pFields = NULL;

	if(m_pParams)
		delete[] m_pParams;
	m_pParams = NULL;
}

bool CMyRecordset::SafeOpen(CString Sql)
{
	int length=Sql.GetLength();
	if(length==0)
	{
		AfxMessageBox("SQL语句为空!");
		return false;
	}
	if(IsOpen())
		Close();
	try
	{
		Open(Sql,AFX_DB_USE_DEFAULT_TYPE,CRecordset::snapshot);
	}
	catch(CDBException *E)
	{
#ifdef _DEBUG
		AfxMessageBox(Sql);
#endif
		AfxMessageBox(E->m_strError);
		return false;
	}
	return true;
}

BOOL	CMyRecordset::Open( CString strSQL,	UINT nOpenType,	DWORD dwOptions )
{
	m_nFields = 255;
	m_bNotLoadedFieldsMap = true;
	if(IsOpen())
		Close();
	return CRecordset::Open( nOpenType, (LPCTSTR)strSQL,dwOptions );
}

void	CMyRecordset::MakeFieldMap()
{
	m_mapField.RemoveAll();
	int	nFields = m_nFields = GetODBCFieldCount();
	
	if(m_pFields)
		delete[] m_pFields;
	m_pFields = new CDBField[ m_nFields ];

	CODBCFieldInfo	fi;
	CString			strName;
	for( int i = 0; i < nFields; i++ ) 
	{
		GetODBCFieldInfo( i, fi );
		SetDBFieldType( m_pFields[i], fi );
		
		//	Set the field name
		fi.m_strName.MakeUpper();
		strName = fi.m_strName;

		//	Make different field names for the fields with
		//	equal names.
		int	fldCount = 1;
		while( GetFieldID( strName ) != -1 ) 
		{
			fldCount++;
			strName.Format( "%s%d", fi.m_strName, fldCount );
		}
		m_pFields[i].m_strFieldName = strName;
		m_mapField.SetAt(i,strName);
	}
}

short CMyRecordset::SQLType2CType( short nSQLType )
{
	short nFieldType = 0;

	switch( nSQLType )
	{
	case SQL_BIT:
		nFieldType = SQL_C_BIT;
		break;

	case SQL_TINYINT:
		nFieldType = SQL_C_UTINYINT;
		break;

	case SQL_SMALLINT:
		nFieldType = SQL_C_SSHORT;
		break;

	case SQL_INTEGER:
		nFieldType = SQL_C_SLONG;
		break;

	case SQL_REAL:
		nFieldType = SQL_C_FLOAT;
		break;

	case SQL_FLOAT:
	case SQL_DOUBLE:
	case SQL_NUMERIC:
	case SQL_DECIMAL:
		nFieldType = SQL_C_DOUBLE;
		break;

	case SQL_DATE:
	case SQL_TIME:
	case SQL_TIMESTAMP:
		nFieldType = SQL_C_TIMESTAMP;
		break;

	case SQL_BIGINT:
	case SQL_CHAR:
	case SQL_VARCHAR:
	case SQL_WCHAR:
	case SQL_WVARCHAR:
		nFieldType = SQL_C_CHAR;
		break;
	case SQL_WLONGVARCHAR:
	case SQL_LONGVARCHAR:
	case SQL_BINARY:
	case SQL_VARBINARY:
	case SQL_LONGVARBINARY:
		nFieldType = SQL_C_BINARY;
		break;

	default:
		ASSERT( FALSE );
	}
	return	nFieldType;
}

void	CMyRecordset::SetDBFieldType( CDBVariant& varValue, CODBCFieldInfo& fi )
{
	int	nFieldType = SQLType2CType( fi.m_nSQLType );
	switch( nFieldType )
	{
	case SQL_C_BIT:
		varValue.m_dwType = DBVT_BOOL;
		break;

	case SQL_C_UTINYINT:
		varValue.m_dwType = DBVT_UCHAR;
		break;

	case SQL_C_SSHORT:
	case SQL_C_SLONG:
		varValue.m_dwType = DBVT_LONG;
		break;

	case SQL_C_FLOAT:
		varValue.m_dwType = DBVT_SINGLE;
		break;

	case SQL_C_DOUBLE:
		varValue.m_dwType = DBVT_DOUBLE;
		break;

	case SQL_C_TIMESTAMP:
		varValue.m_pdate = new TIMESTAMP_STRUCT;
		varValue.m_dwType = DBVT_DATE;
		break;

	case SQL_C_CHAR:
		varValue.m_pstring = new CString;
		varValue.m_dwType = DBVT_STRING;
		break;

	case SQL_C_BINARY:
		varValue.m_pbinary = new CLongBinary;
		varValue.m_dwType = DBVT_BINARY;
		break;

	default:
		//	Unknown data type
		ASSERT( FALSE );
	}
}

void	CMyRecordset::Move( long nRows, WORD wFetchType /*= SQL_FETCH_RELATIVE*/ )
{
	if( m_bNotLoadedFieldsMap )
	{
		MakeFieldMap();
		m_bNotLoadedFieldsMap = false;
	}
	CRecordset::Move( nRows, wFetchType );
}
////////////////////////////////////////////////////////////////

void	CMyRecordset::Close()
{
	CRecordset::Close();
	m_nInputParams=0;
	m_nOutputParams=0;
	m_nInputOutputParams=0;
	m_nReturnParam=0;
	m_nParams = 0;
	m_mapParam.RemoveAll();
	m_mapField.RemoveAll();
	delete[] m_pParams;
	m_pParams = NULL;
	delete[] m_pFields;
	m_pFields = NULL;
}

int	CMyRecordset::GetFieldID( CString strName )
{
	strName.MakeUpper();
	CString s1;
	for (int i=0;m_mapField.Lookup( i, s1 ) ;i++)
	{
		if(s1==strName)
			return i;
	}
	return -1;
}

CString	CMyRecordset::GetFieldName( int nField )
{
	ASSERT( nField >= 0 );
	ASSERT( nField < GetODBCFieldCount() );
	CString strFieldName;
	if(m_mapField.Lookup(nField,strFieldName))
		return strFieldName;
	else
	{
		strFieldName.Empty();
		return strFieldName;
	}
}

CString	CMyRecordset::GetParamName( int nParamId )
{
	ASSERT( nParamId >= 0 );

	CString		cName;
	CParamInfo paramInfo;
	if(m_mapParam.Lookup(nParamId,paramInfo))
		return paramInfo.m_strParamName;
	else
	{
		cName.Empty();
		return	cName;
	}
}

void CMyRecordset::DoFieldExchange( CFieldExchange* pFX )
{

	pFX->SetFieldType( CFieldExchange::outputColumn );
	CString		strFieldName;
	for( UINT i = 0; i < m_nFields && pFX->m_nOperation!=CFieldExchange::BindParam && pFX->m_nOperation!=CFieldExchange::RebindParam; i++ )
	{
		strFieldName.Format( "[%s]", GetFieldName(i) );
		switch( m_pFields[i].m_dwType )
		{
		case	DBVT_NULL:
					break;
		case	DBVT_BOOL:
					RFX_Bool( pFX, strFieldName, m_pFields[i].m_boolVal );
					break;
		case	DBVT_UCHAR:
					RFX_Byte( pFX, strFieldName, m_pFields[i].m_chVal );
					break;
		case	DBVT_SHORT:
					m_pFields[i].m_dwType = DBVT_LONG;
					RFX_Long( pFX, strFieldName, m_pFields[i].m_lVal );
					break;
		case	DBVT_LONG:
					RFX_Long( pFX, strFieldName, m_pFields[i].m_lVal );
					break;
		case	DBVT_SINGLE:
					RFX_Single( pFX, strFieldName, m_pFields[i].m_fltVal );
					break;
		case	DBVT_DOUBLE:
					RFX_Double( pFX, strFieldName, m_pFields[i].m_dblVal );
					break;
		case	DBVT_DATE:
					RFX_Date( pFX, strFieldName, *m_pFields[i].m_pdate );
					break;
		case	DBVT_STRING:
				{
					CODBCFieldInfo	fi;
					GetODBCFieldInfo( i, fi );
					RFX_Text( pFX, strFieldName, *m_pFields[i].m_pstring, fi.m_nPrecision );
					break;
				}
		case	DBVT_BINARY:
					RFX_LongBinary( pFX, strFieldName, *(m_pFields[i].m_pbinary) );
					break;
		default:
			ASSERT( FALSE );
		}
		m_pFields[i].SetNull( FALSE != IsFieldStatusNull( i ) );
	}

	if(m_nReturnParam>0 )
	{
		pFX->SetFieldType( CFieldExchange::outputParam );
		RFX_Long( pFX, _T("[@RETURN_VALUE]"), m_pParams[0].m_lVal );
	}
	
	CString paramName;
	int nLength;
	if(m_nInputParams>0 )
	{
		pFX->SetFieldType( CFieldExchange::inputParam );
		for( i=0;i<m_nParams;i++)
		{
			if(m_mapParam[i].m_nColType != SQL_PARAM_INPUT)
				continue;
			paramName=m_mapParam[i].m_strParamName;
			nLength=m_mapParam[i].m_nLength;
			switch( m_pParams[i].m_dwType )
			{
				case	DBVT_NULL:
							break;
				case	DBVT_BOOL:
							RFX_Bool( pFX, paramName, m_pParams[i].m_boolVal );
							break;
				case	DBVT_UCHAR:
							RFX_Byte( pFX, paramName, m_pParams[i].m_chVal );
							break;
				case	DBVT_SHORT:
							m_pParams[i].m_dwType = DBVT_LONG;
							RFX_Long( pFX, paramName, m_pParams[i].m_lVal );
							break;
				case	DBVT_LONG:
							RFX_Long( pFX, paramName, m_pParams[i].m_lVal );
							break;
				case	DBVT_SINGLE:
							RFX_Single( pFX, paramName, m_pParams[i].m_fltVal );
							break;
				case	DBVT_DOUBLE:
							RFX_Double( pFX, paramName, m_pParams[i].m_dblVal );
							break;
				case	DBVT_DATE:
							RFX_Date( pFX, paramName, *m_pParams[i].m_pdate );
							break;
				case	DBVT_STRING:
						{
							RFX_Text( pFX, paramName, *m_pParams[i].m_pstring, nLength );
							break;
						}
				case	DBVT_BINARY:
							RFX_LongBinary( pFX, paramName, *(m_pParams[i].m_pbinary) );
							break;
				default:
					ASSERT( FALSE );
			}
		}
	}

	if(m_nOutputParams>0)
	{
		pFX->SetFieldType( CFieldExchange::outputParam );
		for(i=0;i<m_nParams;i++)
		{
			if(m_mapParam[i].m_nColType != SQL_PARAM_OUTPUT)
				continue;
			paramName=m_mapParam[i].m_strParamName;
			nLength=m_mapParam[i].m_nLength;
			switch( m_pParams[i].m_dwType )
			{
			case	DBVT_NULL:
						break;
			case	DBVT_BOOL:
						RFX_Bool( pFX, paramName, m_pParams[i].m_boolVal );
						break;
			case	DBVT_UCHAR:
						RFX_Byte( pFX, paramName, m_pParams[i].m_chVal );
						break;
			case	DBVT_SHORT:
						m_pParams[i].m_dwType = DBVT_LONG;
						RFX_Long( pFX, paramName, m_pParams[i].m_lVal );
						break;
			case	DBVT_LONG:
						RFX_Long( pFX, paramName, m_pParams[i].m_lVal );
						break;
			case	DBVT_SINGLE:
						RFX_Single( pFX, paramName, m_pParams[i].m_fltVal );
						break;
			case	DBVT_DOUBLE:
						RFX_Double( pFX, paramName, m_pParams[i].m_dblVal );
						break;
			case	DBVT_DATE:
						RFX_Date( pFX, paramName, *m_pParams[i].m_pdate );
						break;
			case	DBVT_STRING:
					{
						RFX_Text( pFX, paramName, *m_pParams[i].m_pstring,nLength );
						break;
					}
			case	DBVT_BINARY:
						RFX_LongBinary( pFX, paramName, *(m_pParams[i].m_pbinary) );
						break;
			default:
				ASSERT( FALSE );
			}
		}
	}
	
	if(m_nInputOutputParams>0)
	{
		pFX->SetFieldType( CFieldExchange::inoutParam );
		for(i=0;i<m_nParams;i++)
		{
			if(m_mapParam[i].m_nColType != SQL_PARAM_INPUT_OUTPUT)
				continue;
			paramName=m_mapParam[i].m_strParamName;
			nLength=m_mapParam[i].m_nLength;
			switch( m_pParams[i].m_dwType )
			{
			case	DBVT_NULL:
						break;
			case	DBVT_BOOL:
						RFX_Bool( pFX, paramName, m_pParams[i].m_boolVal );
						break;
			case	DBVT_UCHAR:
						RFX_Byte( pFX, paramName, m_pParams[i].m_chVal );
						break;
			case	DBVT_SHORT:
						m_pParams[i].m_dwType = DBVT_LONG;
						RFX_Long( pFX, paramName, m_pParams[i].m_lVal );
						break;
			case	DBVT_LONG:
						RFX_Long( pFX, paramName, m_pParams[i].m_lVal );
						break;
			case	DBVT_SINGLE:
						RFX_Single( pFX, paramName, m_pParams[i].m_fltVal );
						break;
			case	DBVT_DOUBLE:
						RFX_Double( pFX, paramName, m_pParams[i].m_dblVal );
						break;
			case	DBVT_DATE:
						RFX_Date( pFX, paramName, *m_pParams[i].m_pdate );
						break;
			case	DBVT_STRING:
					{
						RFX_Text( pFX, paramName, *m_pParams[i].m_pstring,nLength );
						break;
					}
			case	DBVT_BINARY:
						RFX_LongBinary( pFX, paramName, *(m_pParams[i].m_pbinary) );
						break;
			default:
				ASSERT( FALSE );
			}
		}
	}
}

CDBField&	CMyRecordset::Field( int nField ) 
{
	ASSERT( m_pFields != NULL );
	ASSERT( nField >= 0 );
	ASSERT( nField < GetODBCFieldCount() );
	ASSERT( IsOpen() );

	return	m_pFields[ nField ];
}

bool	CMyRecordset::GetBool( int nCol ) 
{
	return	Field( nCol ).AsBool();
}

unsigned char	CMyRecordset::GetChar( int nCol ) 
{
	return	Field( nCol ).AsChar();
}

short	CMyRecordset::GetShort( int nCol ) 
{
	return	Field( nCol ).AsShort();
}

int		CMyRecordset::GetInt( int nCol ) 
{
	return	Field( nCol ).AsInt();
}

long	CMyRecordset::GetLong( int nCol ) 
{
	return	Field( nCol ).AsLong();
}

float	CMyRecordset::GetFloat( int nCol ) 
{
	return	Field( nCol ).AsFloat();
}

double	CMyRecordset:: GetDouble( int nCol ) 
{
	return	Field( nCol ).AsDouble();
}

COleDateTime	CMyRecordset::GetDate( int nCol ) 
{
	return	Field( nCol ).AsDate();
}

CString	CMyRecordset::GetString( int nCol ) 
{
	return	Field( nCol ).AsString();
}

CLongBinary*	CMyRecordset::GetBinary( int nCol ) 
{
	return	Field( nCol ).AsBinary();
}

bool	CMyRecordset::PrepareStoreProcedure(CString strStoreProcedureName)
{
	CProcedureColumns splParamSet(m_pDatabase);
	BOOL bRet;
	bRet=splParamSet.Open(NULL,NULL,strStoreProcedureName,NULL);
	if(!bRet)
	{
		AfxMessageBox("Failed to open:CRecordset,PrepareStoreProcedure!");
		return false;
	}
	CString s1;
	int i=0;
	struct CParamInfo paramInfo;
	m_nInputParams=0;
	m_nOutputParams=0;
	m_nInputOutputParams=0;
	m_nReturnParam=0;
	m_mapParam.RemoveAll();
	while(!splParamSet.IsEOF())
	{
		paramInfo.m_strParamName = splParamSet.m_strColumnName;
		paramInfo.m_nColType = splParamSet.m_fColumnType;
		if(paramInfo.m_nColType == SQL_PARAM_INPUT)
			m_nInputParams++;
		if(paramInfo.m_nColType == SQL_PARAM_OUTPUT)
			m_nOutputParams++;
		if(paramInfo.m_nColType == SQL_PARAM_INPUT_OUTPUT)
			m_nInputOutputParams++;
		if(paramInfo.m_nColType == SQL_RETURN_VALUE)
			m_nReturnParam++;
		paramInfo.m_nDataType = splParamSet.m_nDataType ;
		paramInfo.m_nLength = splParamSet.m_nLength;
		m_mapParam.SetAt(i++,paramInfo);
		splParamSet.MoveNext();
	}
	splParamSet.Close();
	m_nParams =UINT( m_mapParam.GetCount());
	if(m_pParams != NULL)
	{
		delete[] m_pParams;
		m_pParams=NULL;
	}
	if(m_nParams == 0)
		return true;
	m_pParams = new CDBField[ m_nParams ];
	bRet=splParamSet.Open(NULL,NULL,strStoreProcedureName,NULL);
	if(!bRet)
	{
		AfxMessageBox("Failed to open!");
		return false;
	}
	i=0;
	CODBCFieldInfo fieldInfo;
	while(!splParamSet.IsEOF())
	{
		fieldInfo.m_nSQLType=splParamSet.m_nDataType;
		SetDBFieldType(m_pParams[i],fieldInfo);
		i++;
		splParamSet.MoveNext();
	}
	splParamSet.Close();
	return true;
}

bool	CMyRecordset::PrepareSQL(CString strSQL)
{
	SQLRETURN retcode;
	SQLHSTMT hstmt;

	retcode = SQLAllocHandle(SQL_HANDLE_STMT, m_pDatabase->m_hdbc, &hstmt);
	if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
	{
		return false;
	}
	retcode = SQLPrepare(hstmt,(SQLCHAR *)strSQL.GetBuffer(strSQL.GetLength()),SQL_NTS);
	if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO) 
		return false;
	SQLSMALLINT nParams;
	retcode = SQLNumParams(hstmt, &nParams);
	if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO) 
		return false;
	struct CParamInfo paramInfo;