myrecordset.cpp

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

	m_nInputParams=0;
	m_nOutputParams=0;
	m_nInputOutputParams=0;
	m_nReturnParam=0;
	m_mapParam.RemoveAll();
	if(m_pParams != NULL)
	{
		delete[] m_pParams;
		m_pParams=NULL;
	}
	m_nParams=nParams;
	m_nInputParams=nParams;
	if(m_nParams==0)
		return true;
	m_pParams = new CDBField[ m_nParams ];
	SQLSMALLINT DataType, DecimalDigits, Nullable;
	SQLUINTEGER   ParamSize;
	CODBCFieldInfo fieldInfo;
	SQLCHAR	SqlState[6],Msg[SQL_MAX_MESSAGE_LENGTH];
	SQLINTEGER	NativeError;
	SQLSMALLINT	n,MsgLen;
	SQLRETURN retcode2;
	for(UINT i=0;i<m_nParams;i++)
	{
		retcode=SQLDescribeParam(hstmt, i+1, &DataType, &ParamSize, &DecimalDigits, &Nullable);
		if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
		{
			n = 1;
			CString strMsg;
			while ((retcode2 = SQLGetDiagRec(SQL_HANDLE_STMT, hstmt, n, SqlState, &NativeError,Msg, sizeof(Msg), &MsgLen)) != SQL_NO_DATA) 
			{
				strMsg=Msg;
				AfxMessageBox(strMsg);
				n++;
			}
			return false;
		}
		paramInfo.m_nColType = 	SQL_PARAM_INPUT;
		paramInfo.m_nDataType = DataType;
		paramInfo.m_nLength = ParamSize;
		(paramInfo.m_strParamName).Format("%d",i);
		paramInfo.m_nDecimalDigits = DecimalDigits;
		m_mapParam.SetAt(i,paramInfo);
		fieldInfo.m_nSQLType=DataType;
		SetDBFieldType(m_pParams[i],fieldInfo);
	}
	SQLFreeHandle(SQL_HANDLE_STMT,hstmt);

	return true;
}

void	CMyRecordset::SetParam_Long(UINT nParamId,long nParamValue)
{
	ASSERT(nParamId>=0);
	ASSERT(nParamId<m_nParams);
	m_pParams[nParamId].m_lVal = nParamValue;
}

int	CMyRecordset::GetParam_Long(UINT nParamId)
{
	ASSERT(nParamId>=0);
	if(m_mapParam[nParamId].m_nColType == SQL_PARAM_OUTPUT || m_mapParam[nParamId].m_nColType == SQL_PARAM_INPUT_OUTPUT ||m_mapParam[nParamId].m_nColType == SQL_RETURN_VALUE)
		return m_pParams[nParamId].m_lVal;
	else
	{
		ASSERT(FALSE);
		return -1;
	}
}

void	CMyRecordset::SetParam_Float(UINT nParamId,float fltParamValue)
{
	ASSERT(nParamId>=0);
	ASSERT(nParamId<m_nParams);
	m_pParams[nParamId].m_fltVal = fltParamValue;
}

float	CMyRecordset::GetParam_Float(UINT nParamId)
{
	ASSERT(nParamId>=0);
	if(m_mapParam[nParamId].m_nColType == SQL_PARAM_OUTPUT || m_mapParam[nParamId].m_nColType == SQL_PARAM_INPUT_OUTPUT)
		return m_pParams[nParamId].m_fltVal;
	else
	{
		ASSERT(FALSE);
		return 0.0;
	}
}

void	CMyRecordset::SetParam_Double(UINT nParamId,double dblParamValue)
{
	ASSERT(nParamId>=0);
	ASSERT(nParamId<m_nParams);
	m_pParams[nParamId].m_dblVal = dblParamValue;
}

double	CMyRecordset::GetParam_Double(UINT nParamId)
{
	ASSERT(nParamId>=0);
	if(m_mapParam[nParamId].m_nColType == SQL_PARAM_OUTPUT || m_mapParam[nParamId].m_nColType == SQL_PARAM_INPUT_OUTPUT)
		return m_pParams[nParamId].m_dblVal;
	else
	{
		ASSERT(FALSE);
		return 0.0;
	}
}

void	CMyRecordset::SetParam_Bool(UINT nParamId,BOOL bParamValue)
{
	ASSERT(nParamId>=0);
	ASSERT(nParamId<m_nParams);
	m_pParams[nParamId].m_boolVal = bParamValue;
}

BOOL	CMyRecordset::GetParam_Bool(UINT nParamId)
{
	ASSERT(nParamId>=0);
	if(m_mapParam[nParamId].m_nColType == SQL_PARAM_OUTPUT || m_mapParam[nParamId].m_nColType == SQL_PARAM_INPUT_OUTPUT)
		return m_pParams[nParamId].m_boolVal;
	else
	{
		ASSERT(FALSE);
		return FALSE;
	}
}

void	CMyRecordset::SetParam_DateTime(UINT nParamId,TIMESTAMP_STRUCT dtParamValue)
{
	ASSERT(nParamId>=0);
	ASSERT(nParamId<m_nParams);
	m_pParams[nParamId].m_pdate->year = dtParamValue.year;
	m_pParams[nParamId].m_pdate->month = dtParamValue.month;
	m_pParams[nParamId].m_pdate->day = dtParamValue.day;
	m_pParams[nParamId].m_pdate->hour = dtParamValue.hour;
	m_pParams[nParamId].m_pdate->minute = dtParamValue.minute;
	m_pParams[nParamId].m_pdate->second = dtParamValue.second;
	m_pParams[nParamId].m_pdate->fraction = dtParamValue.fraction;
}

TIMESTAMP_STRUCT	CMyRecordset::GetParam_DateTime(UINT nParamId)
{
	ASSERT(nParamId>=0);
	if(!(m_mapParam[nParamId].m_nColType == SQL_PARAM_OUTPUT || m_mapParam[nParamId].m_nColType == SQL_PARAM_INPUT_OUTPUT))
		ASSERT(FALSE);
	return *(m_pParams[nParamId].m_pdate);
}

void	CMyRecordset::SetParam_String(UINT nParamId,CString strParamValue)
{
	ASSERT(nParamId>=0);
	ASSERT(nParamId<m_nParams);
	*(m_pParams[nParamId].m_pstring)=strParamValue;
}


////////////////////////////////////////////////////////////////
//	CDBField
///////////////////////////////////////////////////////////////

CDBField::CDBField() 
{
	m_bIsNull = true;
}

CDBField::~CDBField() 
{
}

bool	CDBField::AsBool()	const
{
	if( IsNull() )
		return	false;

	CString	cValue;
	switch( m_dwType ) {
	case	DBVT_NULL:
				return	false;

	case	DBVT_BOOL:
				return	(m_boolVal == TRUE);

	case	DBVT_UCHAR:
				return	(m_chVal == 'T' || m_chVal == '1');

	case	DBVT_SHORT:
				return	(m_iVal != 0);

	case	DBVT_LONG:
				return	(m_lVal != 0);

	case	DBVT_SINGLE:
				return	(m_fltVal != 0.0);

	case	DBVT_DOUBLE:
				return	(m_dblVal != 0.0);

	case	DBVT_DATE:
				//	Cannot convert date to bool
				ASSERT( FALSE );
				break;

	case	DBVT_STRING:
				ASSERT( m_pstring != NULL );
				if( m_pstring->GetLength() < 1 )
					return	false;
				return	((*m_pstring)[0] == 'T' || (*m_pstring)[0] == '1');

	case	DBVT_BINARY:
				//	Cannot convert long binary to bool
				ASSERT( FALSE );
				break;
	}
	//	Undefined data type
	ASSERT( FALSE );
	return	false;
}

unsigned char	CDBField::AsChar()	const
{
	if( IsNull() )
		return	' ';

	switch( m_dwType ) {
	case	DBVT_NULL:
				return	' ';

	case	DBVT_BOOL:
				return	(m_boolVal) ? 'T' : 'F';

	case	DBVT_UCHAR:
				return	m_chVal;

	case	DBVT_SHORT:
				return	(unsigned char)m_iVal;

	case	DBVT_LONG:
				return	(unsigned char)m_lVal;

	case	DBVT_SINGLE:
				return	(unsigned char)m_fltVal;

	case	DBVT_DOUBLE:
				return	(unsigned char)m_dblVal;

	case	DBVT_DATE:
				//	Cannot convert date to unsigned char
				ASSERT( FALSE );
				break;

	case	DBVT_STRING:
				ASSERT( m_pstring != NULL );
				if( m_pstring->GetLength() < 1 )
					return	' ';
				return	(unsigned char)((*m_pstring)[0]);

	case	DBVT_BINARY:
				//	Cannot convert long binary to unsigned char
				ASSERT( FALSE );
				break;
	}
	//	Undefined data type
	ASSERT( FALSE );
	return	' ';
}

short	CDBField::AsShort()	const
{
	if( IsNull() )
		return	0;

	switch( m_dwType ) {
	case	DBVT_NULL:
				return	0;

	case	DBVT_BOOL:
				return	m_boolVal ? 1 : 0;

	case	DBVT_UCHAR:
				return	(short)m_chVal;

	case	DBVT_SHORT:
				return	m_iVal;

	case	DBVT_LONG:
				return	(short)m_lVal;

	case	DBVT_SINGLE:
				return	(short)m_fltVal;

	case	DBVT_DOUBLE:
				return	(short)m_dblVal;

	case	DBVT_DATE:
				//	Cannot convert date to short
				ASSERT( FALSE );
				break;

	case	DBVT_STRING:
				ASSERT( m_pstring != NULL );
				return	(short)atoi( *m_pstring );

	case	DBVT_BINARY:
				//	Cannot conver long binary to short
				ASSERT( FALSE );
				break;
	}
	//	Undefined data type
	ASSERT( FALSE );
	return	0;
}

int		CDBField::AsInt()	const
{
	return	AsLong();
}

long	CDBField::AsLong()	const
{
	if( IsNull() )
		return	0;

	switch( m_dwType ) {
	case	DBVT_NULL:
				return	0;

	case	DBVT_BOOL:
				return	m_boolVal ? 1 : 0;

	case	DBVT_UCHAR:
				return	(long)m_chVal;

	case	DBVT_SHORT:
				return	(long)m_iVal;

	case	DBVT_LONG:
				return	m_lVal;

	case	DBVT_SINGLE:
				return	(long)m_fltVal;

	case	DBVT_DOUBLE:
				return	(long)m_dblVal;

	case	DBVT_DATE:
				//	Cannot convert date to long
				ASSERT( FALSE );
				break;

	case	DBVT_STRING:
				ASSERT( m_pstring != NULL );
				return	atol( *m_pstring );

	case	DBVT_BINARY:
				//	Cannot conver long binary to long
				ASSERT( FALSE );
				break;
	}
	//	Undefined data type
	ASSERT( FALSE );
	return	0;
}

float	CDBField::AsFloat()	const
{
	if( IsNull() )
		return	0.0;

	switch( m_dwType ) {
	case	DBVT_NULL:
				return	0.0;

	case	DBVT_BOOL:
				return	(float)(m_boolVal ? 1.0 : 0.0);

	case	DBVT_UCHAR:
				return	(float)m_chVal;

	case	DBVT_SHORT:
				return	(float)m_iVal;

	case	DBVT_LONG:
				return	(float)m_lVal;

	case	DBVT_SINGLE:
				return	m_fltVal;

	case	DBVT_DOUBLE:
				return	(float)m_dblVal;

	case	DBVT_DATE:
				//	Cannot convert date to float
				ASSERT( FALSE );
				break;

	case	DBVT_STRING:
				ASSERT( m_pstring != NULL );
				return	(float)atof( *m_pstring );

	case	DBVT_BINARY:
				//	Cannot conver long binary to float
				ASSERT( FALSE );
				break;
	}
	//	Undefined data type
	ASSERT( FALSE );
	return	0.0;
}

double	CDBField::AsDouble()	const
{
	if( IsNull() )
		return	0.0;

	switch( m_dwType ) {
	case	DBVT_NULL:
				return	0.0;

	case	DBVT_BOOL:
				return	m_boolVal ? 1.0 : 0.0;

	case	DBVT_UCHAR:
				return	(double)m_chVal;

	case	DBVT_SHORT:
				return	(double)m_iVal;

	case	DBVT_LONG:
				return	(double)m_lVal;

	case	DBVT_SINGLE:
				return	(double)m_fltVal;

	case	DBVT_DOUBLE:
				return	m_dblVal;

	case	DBVT_DATE:
				//	Cannot convert date to double
				ASSERT( FALSE );
				break;

	case	DBVT_STRING:
				ASSERT( m_pstring != NULL );
				return	atof( *m_pstring );

	case	DBVT_BINARY:
				//	Cannot conver long binary to double
				ASSERT( FALSE );
				break;
	}
	//	Undefined data type
	ASSERT( FALSE );
	return	0.0;
}

COleDateTime	CDBField::AsDate()	const
{
	COleDateTime	date;
	if( IsNull() ) {
		date.SetStatus( COleDateTime::null );
		return	date;
	}

	switch( m_dwType ) {
	case	DBVT_NULL:
			date.SetStatus( COleDateTime::null );
			return	date;

	case	DBVT_BOOL:
			date.SetStatus( COleDateTime::invalid );
			return	date;

	case	DBVT_UCHAR:
			date.SetStatus( COleDateTime::invalid );
			return	date;

	case	DBVT_SHORT:
			return	COleDateTime( (time_t)m_iVal );

	case	DBVT_LONG:
			return	COleDateTime( (time_t)m_lVal );

	case	DBVT_SINGLE:
			return	COleDateTime( (time_t)m_fltVal );

	case	DBVT_DOUBLE:
			return	COleDateTime( (time_t)m_dblVal );

	case	DBVT_DATE:
			ASSERT( m_pdate != NULL );
			return	COleDateTime(	m_pdate->year, m_pdate->month, m_pdate->day,
									m_pdate->hour, m_pdate->minute, m_pdate->second );

	case	DBVT_STRING:
			ASSERT( m_pstring != NULL );
			date.ParseDateTime( *m_pstring );
			return	date;

	case	DBVT_BINARY:
			//	Cannot conver long binary to date
			ASSERT( FALSE );
			break;
	}
	//	Undefined data type
	ASSERT( FALSE );
	date.SetStatus( COleDateTime::invalid );
	return	date;
}

CString		CDBField::AsString()
{
	CString	cValue;
	if( IsNull() ) 
		return cValue;

	switch( m_dwType ) {
	case	DBVT_NULL:
			return	cValue;

	case	DBVT_BOOL:
			return	CString( m_boolVal ? "T" : "F" );

	case	DBVT_UCHAR:
			unsigned char s1[2];
			s1[0]=m_chVal;
			s1[1]='\0';
			return	CString( s1 );

	case	DBVT_SHORT:
			cValue.Format( "%hd", m_iVal );
			return	cValue;

	case	DBVT_LONG:
			cValue.Format( "%ld", m_lVal );
			return	cValue;

	case	DBVT_SINGLE:
			cValue.Format( "%f", m_fltVal );
			return	cValue;

	case	DBVT_DOUBLE:
			cValue.Format( "%f", m_dblVal );
			return	cValue;

	case	DBVT_DATE:
		{
			ASSERT( m_pdate != NULL );
			COleDateTime	date( m_pdate->year, m_pdate->month, m_pdate->day,
								  m_pdate->hour, m_pdate->minute, m_pdate->second );
			return	date.Format();
		}
	case	DBVT_STRING:
			ASSERT( m_pstring != NULL );
			return	*m_pstring;

	case	DBVT_BINARY:
			ASSERT( m_pbinary != NULL );
			BinaryToString( cValue,true );
			return	cValue;
	}
	//	Undefined data type
	ASSERT( FALSE );
	return	cValue;
}

CLongBinary*	CDBField::AsBinary()	const
{
	if( IsNull() )
		return NULL;

	switch( m_dwType ) 
	{
	case	DBVT_NULL:
			return	NULL;

	case	DBVT_BOOL:
	case	DBVT_UCHAR:
	case	DBVT_SHORT:
	case	DBVT_LONG:
	case	DBVT_SINGLE:
	case	DBVT_DOUBLE:
	case	DBVT_DATE:
	case	DBVT_STRING:
			//	Cannot convert to long binary
			ASSERT( FALSE );
			break;

	case	DBVT_BINARY:
			return	m_pbinary;
	}
	//	Undefined data type
	ASSERT( FALSE );
	return	m_pbinary;
}

bool	CDBField::IsNull() const
{ 
	return m_bIsNull || m_dwType == DBVT_NULL; 
}

void	CDBField::SetNull( bool bIsNull )
{
	m_bIsNull = bIsNull; 
}

void	CDBField::BinaryToString( CString& cStr, bool bExpand )
{
	if(m_pbinary == NULL)
		return;

	int	nSize = min( (int)m_pbinary->m_dwDataLength, cStr.GetLength() );
	if( bExpand )
		nSize = m_pbinary->m_dwDataLength;

	if( nSize < 1 ) {
		cStr.Empty();
		return;
	}

	void*	p = ::GlobalLock( m_pbinary->m_hData );
	if( p == NULL )
		::AfxThrowMemoryException();
	
	char* pStr = cStr.GetBuffer( nSize );
	memcpy( pStr, p, nSize );
	cStr.ReleaseBuffer( nSize );
	::GlobalUnlock( m_pbinary->m_hData );
}