adodb.cpp

以SQL语句从数据库里查询出记录

//adodb.cpp
#include "stdafx.h"
#include "adodb.h"

char CAdoDb::m_szConnection[1500];
CAdoDb::CAdoDb()
{
	ConnectStruct conn;
	int n=0;
	strcpy(m_szConnection,conn.m_szConnection);
	int j=0;
	m_strLastError="";
	m_nErrorCode=0;

	if(OpenConnection()){
		CloseConnection();
	}
	else {
		CString strMsg;
		strMsg.Format("创建数据库连接失败:%s",m_strLastError);
		MessageBox(NULL,strMsg,"Message",0);
	}
}

CAdoDb::~CAdoDb()
{
	m_strLastError="";
	if (GetObjectState()!=adStateClosed)
		m_pConnection->Close();
}
CString CAdoDb::GetLastError()
{
	return m_strLastError;
}

BOOL CAdoDb::OpenConnection()
{
	if (GetObjectState()!=adStateClosed)
		return TRUE;
	BOOL bOpen=FALSE;
	HRESULT hr;
	try
	{
		hr=m_pConnection.CreateInstance(__uuidof(Connection));
		if (FAILED(hr))
			_com_issue_error(hr);
		//m_pConnection->CursorLocation=adUseClient;
		m_pConnection->Open(LPCSTR(m_szConnection),"","",NULL);
		bOpen=TRUE;
	}
	catch (_com_error &e)
	{
		m_nErrorCode=9;	//操作数据库错误
		m_strLastError.Format("打开数据库错误或者没有初始化COM!{错误号:%08lx 错误号意思:%s 错误描述:%s}",e.Error(),e.ErrorMessage(),(char*)e.Description());
		bOpen=FALSE;
	}
	catch(...)
	{
		m_nErrorCode=9;	//操作数据库错误
		m_strLastError.Format("打开数据库时未知错误!");
		bOpen=FALSE;
	}
	return bOpen;	
}

int CAdoDb::GetObjectState()
{
	int nState;
	try
	{
		nState=m_pConnection->State;
	}
	catch(...)
	{
		nState=adStateClosed;
	}
	return nState;
}


BOOL CAdoDb::BeginTransaction()
{
	BOOL bReturn=TRUE;
	try
	{
		m_pConnection->BeginTrans();
	}
	catch(...)
	{
		bReturn=FALSE;
	}
	return bReturn;
}

void CAdoDb::RollBackTransaction()
{
	try
	{
		m_pConnection->RollbackTrans();
	}
	catch(...)
	{
		;
	}
	return;
}

BOOL CAdoDb::CommitTransaction()
{
	BOOL bReturn=TRUE;
	try
	{
		m_pConnection->CommitTrans();
	}
	catch(...)
	{
		bReturn=FALSE;
	}
	return bReturn;
}


BOOL CAdoDb::ExecuteProduce(LPCSTR lpcszProduceName, const LPADOPARAMETER lpParamArray, const int nCount)
{
	BOOL bReturn=FALSE,bIsValid=FALSE,bIsOpened=TRUE;
	CString strError="",strConnection="";
	HRESULT hr;
	_CommandPtr pCommand=NULL;
	_ParameterPtr pPara=NULL;
	int i=0;
	char szParamName[50];	//参数名称
	int iParamType;			//参数类型
	int iParamDirection;	//参数输入输出方向
	long lParamSize;		//参数长度
	_variant_t vParamValue;	//参数值
	_bstr_t bszProduceName;
	if (lpcszProduceName==NULL)
	{
		m_nErrorCode=9;	//数据库错误
		m_strLastError="空存储过程名称";
		return FALSE;
	}
	bszProduceName=lpcszProduceName;
	try
	{
		if (GetObjectState()==adStateClosed)
			if (!OpenConnection()) return FALSE;
		hr=pCommand.CreateInstance(__uuidof(Command));
		if (FAILED(hr))
			_com_issue_error(hr);
		pCommand->CommandType=adCmdStoredProc;
		pCommand->CommandText=bszProduceName;
		for (i=0;i<nCount;i++)
		{
			memset(szParamName,0,50);
			memcpy(szParamName,lpParamArray[i].cParamName,50);
			iParamType=lpParamArray[i].iParamType;
			iParamDirection=lpParamArray[i].iParamDirection;
			lParamSize=lpParamArray[i].lParamSize;
			//memcpy(&vParamValue,&pParamArray[i].vParamValue,sizeof(pParamArray[i].vParamValue));
			if (iParamDirection==adParamInput)
			{
				vParamValue=lpParamArray[i].vParamValue;
				pPara=pCommand->CreateParameter(_bstr_t(szParamName),\
						(enum DataTypeEnum)iParamType,\
						(enum ParameterDirectionEnum)iParamDirection,\
						lParamSize,vParamValue);
			}
			else
			{
				pPara=pCommand->CreateParameter(_bstr_t(szParamName),\
						(enum DataTypeEnum)iParamType,\
						(enum ParameterDirectionEnum)iParamDirection,\
						lParamSize);
			}
			pCommand->Parameters->Append(pPara);
			pPara=NULL;
		}
		pCommand->ActiveConnection=m_pConnection;
		pCommand->Execute(NULL,NULL,adCmdStoredProc);
		_variant_t vIndex,vValue;
		vIndex.vt=VT_I4;
		for (i=0;i<nCount;i++)
		{
			vIndex.lVal=i;
			if (lpParamArray[i].iParamDirection>adParamInput)
			{
				vValue=pCommand->Parameters->Item[vIndex]->Value;
				lpParamArray[i].vParamValue=vValue;
			}
		}
		bReturn=TRUE;
	}
	catch(_com_error &e)
	{
		m_nErrorCode=9;	//数据库错误
		m_strLastError.Format("执行'%s'存储过程错误!{错误号:%08lx 错误号意思:%s 错误描述:%s}",lpcszProduceName,e.Error(),e.ErrorMessage(),(char*)e.Description());
		bReturn=FALSE;
	}
	catch(...)
	{
		m_nErrorCode=9;//数据库错误
		m_strLastError.Format("执行'%s'存储过程时未知错误!",lpcszProduceName);
		bReturn=FALSE;
	}
	return bReturn;
}


int CAdoDb::GetErrorCode(void)
{
	return m_nErrorCode;
}

// 设置错误码
void CAdoDb::SetErrorCode(const int nErrorCode)
{
	m_nErrorCode=nErrorCode;
	return;
}

BOOL CAdoDb::CloseConnection()
{
	m_strLastError="";
	try{
		if (GetObjectState()!=adStateClosed){
			HRESULT hr = m_pConnection->Close();
			if(FAILED(hr))return FALSE;
		}
	}
	catch(...){
		return FALSE;
	}
	
	return TRUE;
}

_RecordsetPtr CAdoDb::Execute(CString strSQL, long * plRecordsAffected, long Options)
{
	if(strSQL.IsEmpty())return NULL;
	try{
		if (GetObjectState()==adStateClosed)
			if (!OpenConnection()) return NULL;
	}
	catch(...){
		return NULL;
	}

	_variant_t RecordsAffected;
	RecordsAffected.plVal = plRecordsAffected;
	
	return m_pConnection->Execute(LPCTSTR(strSQL.GetBuffer(strSQL.GetLength())),&RecordsAffected,Options);
}

BOOL CAdoDb::ExecuteNoRec(CString strSQL)
{
	if(strSQL.IsEmpty())return FALSE;
	try{
		if (GetObjectState()==adStateClosed)
			if (!OpenConnection()) return FALSE;
	}
	catch(...){
		return FALSE;
	}
	_variant_t RecordsAffected;
	
	try{
		m_pConnection->Execute(_bstr_t(strSQL.GetBuffer(strSQL.GetLength())),&RecordsAffected,adCmdText);
	}
	catch(_com_error &e){
		MessageBox(NULL,(const char *)e.Description(),"数据库异常",0);
		return FALSE;
	}
	catch(...){
		MessageBox(NULL,"执行数据库操作时发生未知异常","数据库异常",0);
		return FALSE;
	}
	return TRUE;
}

CString CAdoDb::g_GetTodayString()
{
	CString strToday;
	strToday.Empty();
	_RecordsetPtr pRecToday;
	pRecToday.CreateInstance(__uuidof(Recordset));
	
	CString strSQL;
	strSQL.Format("select convert(varchar(16),getdate(),112) as today");

	long lAffected = 0L;
	pRecToday = Execute(strSQL,&lAffected);
	
	try{
		pRecToday->MoveFirst();
		COleVariant var;
		VariantInit(&var);

		var = pRecToday->Fields->Item[0L]->Value;

		strToday = var.bstrVal;		

		pRecToday->Close();
		pRecToday.Release();
	}
	catch(...){
		return _T("");
	}

	return strToday;
}



_ConnectionPtr CAdoDb::GetConnection()
{
	OpenConnection();
	return m_pConnection;
}
//按年月日＋流水号的业务规则生成最大的本日序号
CString CAdoDb::GetMaxSerialNo(CString strTableName, CString strVarcharFieldName)
{
	if(strTableName.IsEmpty())return _T("");
	if(strVarcharFieldName.IsEmpty())return _T("");
	try{
		if (GetObjectState()==adStateClosed)
			if (!OpenConnection()) return _T("");
	}
	catch(...){
		return _T("");
	}
	
    //为数据库的兼容性和可移植性，不使用存储过程
	CString strToday = g_GetTodayString();

	CString strMaxNo;
	strMaxNo.Empty();
	_RecordsetPtr pRecMaxNo;
	pRecMaxNo.CreateInstance(__uuidof(Recordset));

	CString strSQL;
	strSQL.Format("select %s from %s WHERE %s in (select  max(%s) \
        from  %s WHERE %s like '%s%%')",
		strVarcharFieldName.GetBuffer(strVarcharFieldName.GetLength()),
		strTableName.GetBuffer(strTableName.GetLength()),
		strVarcharFieldName.GetBuffer(strVarcharFieldName.GetLength()),
		strVarcharFieldName.GetBuffer(strVarcharFieldName.GetLength()),
		strTableName.GetBuffer(strTableName.GetLength()),
		strVarcharFieldName.GetBuffer(strVarcharFieldName.GetLength()),
		strToday.GetBuffer(strToday.GetLength()));

	try{
		BeginTransaction();
		pRecMaxNo->Open(strSQL.GetBuffer(strSQL.GetLength()),
			m_pConnection.GetInterfacePtr(),adOpenDynamic,adLockPessimistic,adCmdText);
		
		if(!pRecMaxNo->adoEOF){
			pRecMaxNo->MoveFirst();
			COleVariant var;
			VariantInit(&var);

			var = pRecMaxNo->Fields->Item[0L]->Value;
			strMaxNo = var.bstrVal;

			CString strTodayMaxNo = strMaxNo.Right(3);
			
			int nTodayMaxNo;
			sscanf(strTodayMaxNo.GetBuffer(strTodayMaxNo.GetLength()),"%d",&nTodayMaxNo);

			nTodayMaxNo+=1;
			strTodayMaxNo.Format("%03d",nTodayMaxNo);
			
			strMaxNo = strMaxNo.Left(8);
			strMaxNo+=strTodayMaxNo;			

			pRecMaxNo->AddNew();
			pRecMaxNo->PutCollect(strVarcharFieldName.GetBuffer(strVarcharFieldName.GetLength()),
				strMaxNo.GetBuffer(strMaxNo.GetLength()));
		}
		else{
			strMaxNo = strToday+_T("001");
			pRecMaxNo->AddNew();
			pRecMaxNo->PutCollect(strVarcharFieldName.GetBuffer(strVarcharFieldName.GetLength()),
				strMaxNo.GetBuffer(strMaxNo.GetLength()));
		}
		pRecMaxNo->Update();
		CommitTransaction();
		pRecMaxNo->Close();
		pRecMaxNo.Release();
	}
	catch(_com_error &e){
		MessageBox(NULL,(const char *)e.Description(),"获取本日最大序号出错",0);
		RollBackTransaction();
		return _T("");
	}	

	return strMaxNo;
}

BOOL CAdoDb::QueryToListCtrl(CString strSQL,CListCtrl *pListCtrl)
{
	if(strSQL.IsEmpty())return FALSE;
	try{
		if (GetObjectState()==adStateClosed)
			if (!OpenConnection()) return FALSE;
	}
	catch(...){
		return FALSE;
	}

	_RecordsetPtr  pRec;
	pListCtrl->SetRedraw(FALSE);	//防止列表闪烁
	pListCtrl->DeleteAllItems();	//删除所有的列表控件中的列

	DWORD dwExStyles = pListCtrl->GetExtendedStyle();
	dwExStyles |=	LVS_EX_FULLROWSELECT |
					LVS_EX_GRIDLINES |
					LVS_EX_FLATSB;
	pListCtrl->SetExtendedStyle(dwExStyles);

	int nColumnCount = pListCtrl->GetHeaderCtrl()->GetItemCount();
	for (int i = 0;i < nColumnCount; i++)
		pListCtrl->DeleteColumn(0);

	pRec.CreateInstance(__uuidof(Recordset));
	try {
		pRec->Open(strSQL.GetBuffer(strSQL.GetLength()),
			m_pConnection.GetInterfacePtr(),
			adOpenDynamic,adLockOptimistic,adCmdText);

		CString strEditString;
		_variant_t var;
		long nFieldsCount=0;
		pRec->Fields->get_Count(&nFieldsCount);

		//插入这些新的列进去
		BSTR bstrFieldName;
		CString strValue;
		CString strFieldName;
		long nFieldLength;
		long lAllListColumnWidth = 0;
		for (int i = 0; i < nFieldsCount; i++)
		{
			pRec->Fields->Item[(long)i]->get_Name(&bstrFieldName);			
			pRec->Fields->Item[(long)i]->get_ActualSize(&nFieldLength);			
			strFieldName = bstrFieldName;
			nFieldLength = 12;//(nFieldLength < 0 ? -nFieldLength : nFieldLength) * 10;
			lAllListColumnWidth += nFieldLength;
			pListCtrl->InsertColumn(i,strFieldName,LVCFMT_LEFT,nFieldLength);
		}

		CRect rc;
		pListCtrl->GetWindowRect(&rc);
		double a = (double)lAllListColumnWidth / (double)rc.Width();
		for (i = 0; i < nFieldsCount; i++)
		{
			nFieldLength = long(pListCtrl->GetColumnWidth(i) / a);
			pListCtrl->SetColumnWidth(i, nFieldLength);
		}

		//向列表控件中加入数据
		long RecNo = 0L;
		while(!pRec->adoEOF){
			LVITEM lvi;
			lvi.mask =   LVIF_TEXT;
			lvi.iItem = RecNo++;

			for(int i = 0;i<nFieldsCount;i++){
				VariantInit(&var);
				var = pRec->Fields->Item[(long)i]->Value;
				strValue.Empty();
                if(var.vt!=VT_NULL)strValue = _T((LPCTSTR)_bstr_t(var));

				strValue.TrimLeft(" ");
				strValue.TrimRight(" ");

				lvi.iSubItem = i;
				lvi.pszText = strValue.GetBuffer(strValue.GetLength());
				if(i==0)
					pListCtrl->InsertItem(&lvi);
				else
					pListCtrl->SetItem(&lvi);
			}
			pRec->MoveNext();
		}
	}
	catch(_com_error &e){
		MessageBox(NULL,(const char *)e.Description(),"Excption",0);
		pListCtrl->SetRedraw(TRUE);
		return FALSE;
	}

	pRec->Close();
	pRec.Release();
	pListCtrl->SetRedraw(TRUE);	
	pListCtrl->Invalidate();
	return TRUE;
}

BOOL CAdoDb::QueryToComboBox(CString strSQL,CComboBox *pCombo){
	if(strSQL.IsEmpty())return FALSE;
	try{
		if (GetObjectState()==adStateClosed)
			if (!OpenConnection()) return FALSE;
	}
	catch(...){
		return FALSE;
	}

	_RecordsetPtr  pRec;

	//删除所有的列表控件中的列
	pCombo->ResetContent();

	pRec.CreateInstance(__uuidof(Recordset));

	try{
		pRec->Open(strSQL.GetBuffer(strSQL.GetLength()),m_pConnection.GetInterfacePtr(),adOpenDynamic,adLockOptimistic,adCmdText);

		CString strEditString;
		_variant_t var;

		long nFieldsCount=0;

		pRec->Fields->get_Count(&nFieldsCount);

		if(nFieldsCount<2)

		//向列表控件中加入数据
		long RecNo = 0L;
		CString strValue;
		int nIndex = 0L;