adodb.cpp

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

		while(!pRec->adoEOF){
			if(nFieldsCount==2){
				VariantInit(&var);
				var = pRec->Fields->Item[1L]->Value;
				if(var.vt!=VT_NULL)strValue = _T((LPCTSTR)_bstr_t(var));
				nIndex = pCombo->InsertString(nIndex,strValue);
				long lData = 0L;
				var = pRec->Fields->Item[0L]->Value;
				lData = var.lVal;
				pCombo->SetItemData(nIndex,(DWORD)lData);
			}
			else
			{
				VariantInit(&var);
				var = pRec->Fields->Item[0L]->Value;
				if(var.vt!=VT_NULL)strValue = _T((LPCTSTR)_bstr_t(var));
				nIndex = pCombo->InsertString(nIndex,strValue);
			}			
			
			pRec->MoveNext();
		}
	}
	catch(_com_error &e){
		MessageBox(NULL,(const char *)e.Description(),"Excption",0);
		return FALSE;
	}

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

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

	
	_RecordsetPtr  pRec;

	//删除所有的列表控件中的列
	pListBox->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;
		
		while(!pRec->adoEOF){
			if(nFieldsCount==2){
				VariantInit(&var);
				var = pRec->Fields->Item[1L]->Value;
				if(var.vt!=VT_NULL)strValue = _T((LPCTSTR)_bstr_t(var));
				nIndex = pListBox->InsertString(nIndex,strValue);
				long lData = 0L;
				var = pRec->Fields->Item[0L]->Value;
				lData = var.lVal;
				pListBox->SetItemData(nIndex,(DWORD)lData);
			}
			else
			{
				VariantInit(&var);
				var = pRec->Fields->Item[0L]->Value;
				if(var.vt!=VT_NULL)strValue = _T((LPCTSTR)_bstr_t(var));
				nIndex = pListBox->InsertString(nIndex,strValue);
			}			
			
			pRec->MoveNext();
		}
	}
	catch(_com_error &e){
		MessageBox(NULL,(const char *)e.Description(),"Excption",0);
		return FALSE;
	}

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


// 追加一个记录数据
BOOL CAdoDb::AddRecordValues(CString strSQL, CStringArray &arrValues)
{
	if(strSQL.IsEmpty())return FALSE;
	try{
		if (GetObjectState()==adStateClosed)
			if (!OpenConnection()) return FALSE;
	}
	catch(...){
		return FALSE;
	}

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

		long lFieldCount = 0;
		Fields *fields = pRec->GetFields();
		HRESULT result = fields->get_Count(&lFieldCount);
#ifdef _DEBUG
		TRACE("\n%ld, %ld", lFieldCount, arrValues.GetSize());
#endif
		ASSERT(lFieldCount == arrValues.GetSize());
		lFieldCount = arrValues.GetSize();

		pRec->AddNew();
		enum DataTypeEnum dte;
		for (long iCurField = 0; iCurField < lFieldCount; iCurField++){
			pRec->Fields->Item[(long)iCurField]->get_Type(&dte);
			//处理Binary和Image类型的特殊情况...
			if(dte==adVarBinary){
				//将数据送去加密...
								//将数据送去加密...	
				CString strValue = arrValues.GetAt(iCurField);
				BYTE* pbuf;
				long nLength = strValue.GetLength();
				pbuf = new BYTE[nLength+2];
				if(pbuf == NULL)return FALSE;          //allocate memory error;
				memset(pbuf,0,(nLength+2)*sizeof(BYTE));
				memcpy(pbuf,strValue.GetBuffer(strValue.GetLength()),nLength);
				
				BYTE *pBufEx;
				pBufEx = pbuf;

				//build a SAFFERRAY
				SAFEARRAY* psa;
				SAFEARRAYBOUND rgsabound[1];
				rgsabound[0].lLbound = 0;
				rgsabound[0].cElements = nLength;
				psa = SafeArrayCreate(VT_UI1, 1, rgsabound);
				
				for (long i = 0; i < nLength; i++)
					SafeArrayPutElement (psa, &i, pBufEx++);
				VARIANT varBLOB;
				varBLOB.vt = VT_ARRAY | VT_UI1;
				varBLOB.parray = psa;

				pRec->put_Collect(_variant_t(iCurField), varBLOB);								
			}
			else if(adLongVarBinary==dte){
				//BLOB类型的数据
				CFile imagefile;
				if(0 == imagefile.Open(arrValues.GetAt(iCurField),CFile::modeRead))return FALSE;
				_variant_t varChunk;
				BYTE* pbuf;
				long nLength = imagefile.GetLength();
				pbuf = new BYTE[nLength+2];
				if(pbuf == NULL)return FALSE;          //allocate memory error;
				imagefile.Read(pbuf,nLength);          //read the file into memory
				
				BYTE *pBufEx;
				pBufEx = pbuf;

				//build a SAFFERRAY
				SAFEARRAY* psa;
				SAFEARRAYBOUND rgsabound[1];
				rgsabound[0].lLbound = 0;
				rgsabound[0].cElements = nLength;
				psa = SafeArrayCreate(VT_UI1, 1, rgsabound);
				
				for (long i = 0; i < nLength; i++)
					SafeArrayPutElement (psa, &i, pBufEx++);
				VARIANT varBLOB;
				varBLOB.vt = VT_ARRAY | VT_UI1;
				varBLOB.parray = psa;

				pRec->Fields->GetItem(_variant_t(iCurField))->AppendChunk(varBLOB);
			}
			else pRec->put_Collect(_variant_t(iCurField), _variant_t(arrValues.GetAt(iCurField)));			
		}

		pRec->Update();
	}
	catch(_com_error &ee){
		MessageBox(NULL,(const char *)ee.Description(),"入库出错",0);
		return FALSE;
	}
	catch (...) {
		//pRec.Release();		
		return FALSE;
	}
	
	pRec->Close();
	pRec.Release();
	return TRUE;
}


BOOL CAdoDb::GetRecordValues(CString strSQL, CStringArray &arrValues)
{
	if(strSQL.IsEmpty())return FALSE;
	try{
		if (GetObjectState()==adStateClosed)
			if (!OpenConnection()) return FALSE;
	}
	catch(...){
		return FALSE;
	}

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

		arrValues.RemoveAll();
		
		long lFieldCount = 0;
		Fields *fields = pRec->GetFields();
		HRESULT result = fields->get_Count(&lFieldCount);

		if (!pRec->adoEOF) {
			pRec->MoveFirst();

			enum DataTypeEnum dte;
			for (long i = 0; i < lFieldCount; i++) {				
				pRec->Fields->Item[(long)i]->get_Type(&dte);
				//处理Binary和Image类型的特殊情况...
				if(dte==adVarBinary){
					_variant_t var = pRec->GetCollect(_variant_t(i));
					long lDataSize = var.parray->rgsabound[0].cElements;
					BYTE *pBuf;
					//得到指向数据的指针
					SafeArrayAccessData(var.parray,(void **)&pBuf);
					
					/*****在这里我们可以对pBuf中的数据进行处理*****/
					char *passwordBuf =new char[lDataSize+1];
					memset(passwordBuf,0,lDataSize+1);
					memcpy(passwordBuf,pBuf,lDataSize);
					
					SafeArrayUnaccessData (var.parray);	
					
					//将passwordBuf在这里送去解密...decrypt(passwordBuf,passwordBuf);

					CString strPasswordCmp = _T(passwordBuf);
					delete passwordBuf;
					
					arrValues.Add(strPasswordCmp);
				}
				else if(adLongVarBinary==dte){
					long lDataLength = pRec->Fields->GetItem(_variant_t(i))->ActualSize;

					if(!lDataLength)continue;

					HANDLE hMem = NULL;
					char tmpPath[_MAX_PATH+1];
					GetTempPath(_MAX_PATH,tmpPath);
					char szFileName[_MAX_PATH+1];
					GetTempFileName(tmpPath,"_gen",0,szFileName);
					CString strFileName = szFileName;
					DeleteFile(szFileName);
					strFileName.Replace(".tmp",".jpg");					
					
					_variant_t varBLOB = pRec->GetFields()->GetItem(_variant_t(i))->GetChunk(lDataLength);

					SafeArrayAccessData(varBLOB.parray,(void **)(&hMem)); 
					
					CFile outFile(strFileName,CFile::modeCreate|CFile::modeWrite);
					BYTE* buffer = (BYTE *)GlobalLock(hMem);
					outFile.WriteHuge(buffer,lDataLength);
					GlobalUnlock(hMem);
					GlobalFree(hMem);
					outFile.Close();
					
					SafeArrayUnaccessData (varBLOB.parray);
					SafeArrayDestroyData(varBLOB.parray);

					arrValues.Add(strFileName);

				}
				else {
					_variant_t result = pRec->GetCollect(_variant_t(i));
					arrValues.Add(_bstr_t(result));
				}
			}
		}
	}
	catch(_com_error &ee)
	{
		MessageBox(NULL,(const char *)ee.Description(),"读取数据出错",0);
		return FALSE;
	}
	catch (...) {
		return FALSE;
	}
	
	pRec->Close();
	pRec.Release();
	pRec = NULL;
	
	return TRUE;
}


// 修改当前记录数据
BOOL CAdoDb::AmendRecordValues(CString strSQL, CStringArray &arrValues)
{
	if(strSQL.IsEmpty())return FALSE;
	try{
		if (GetObjectState()==adStateClosed)
			if (!OpenConnection()) return FALSE;
	}
	catch(...){
		return FALSE;
	}

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

		long lFieldCount = 0;
		Fields *fields = pRec->GetFields();
		HRESULT result = fields->get_Count(&lFieldCount);
#ifdef _DEBUG
		TRACE("\n%ld, %ld", lFieldCount, arrValues.GetSize());
#endif
		ASSERT(lFieldCount == arrValues.GetSize());
		lFieldCount = arrValues.GetSize();

		enum DataTypeEnum dte;
		for (long iCurField = 0; iCurField < lFieldCount; iCurField++){
			pRec->Fields->Item[(long)iCurField]->get_Type(&dte);
			//处理Binary和Image类型的特殊情况...
			if(dte==adVarBinary){
				//将数据送去加密...	
				CString strValue = arrValues.GetAt(iCurField);
				BYTE* pbuf;
				long nLength = strValue.GetLength();
				pbuf = new BYTE[nLength+2];
				if(pbuf == NULL)return FALSE;          //allocate memory error;
				memset(pbuf,0,(nLength+2)*sizeof(BYTE));
				memcpy(pbuf,strValue.GetBuffer(strValue.GetLength()),nLength);
				
				BYTE *pBufEx;
				pBufEx = pbuf;

				//build a SAFFERRAY
				SAFEARRAY* psa;
				SAFEARRAYBOUND rgsabound[1];
				rgsabound[0].lLbound = 0;
				rgsabound[0].cElements = nLength;
				psa = SafeArrayCreate(VT_UI1, 1, rgsabound);
				
				for (long i = 0; i < nLength; i++)
					SafeArrayPutElement (psa, &i, pBufEx++);
				VARIANT varBLOB;
				varBLOB.vt = VT_ARRAY | VT_UI1;
				varBLOB.parray = psa;

				pRec->put_Collect(_variant_t(iCurField), varBLOB);
			}
			else if(adLongVarBinary==dte){
				//BLOB类型的数据
				CFile imagefile;
				if(0 == imagefile.Open(arrValues.GetAt(iCurField),CFile::modeRead))return FALSE;
				_variant_t varChunk;
				BYTE* pbuf;
				long nLength = imagefile.GetLength();
				pbuf = new BYTE[nLength+2];
				if(pbuf == NULL)return FALSE;          //allocate memory error;
				imagefile.Read(pbuf,nLength);          //read the file into memory
				
				BYTE *pBufEx;
				pBufEx = pbuf;

				//build a SAFFERRAY
				SAFEARRAY* psa;
				SAFEARRAYBOUND rgsabound[1];
				rgsabound[0].lLbound = 0;
				rgsabound[0].cElements = nLength;
				psa = SafeArrayCreate(VT_UI1, 1, rgsabound);
				
				for (long i = 0; i < nLength; i++)
					SafeArrayPutElement (psa, &i, pBufEx++);
				VARIANT varBLOB;
				varBLOB.vt = VT_ARRAY | VT_UI1;
				varBLOB.parray = psa;

				pRec->Fields->GetItem(_variant_t(iCurField))->AppendChunk(varBLOB);
			}
			else pRec->put_Collect(_variant_t(iCurField), _variant_t(arrValues.GetAt(iCurField)));			
		}

		pRec->Update();
	}
	catch (...) {
		pRec.Release();
		return FALSE;
	}
	
	pRec->Close();
	pRec.Release();
	return TRUE;
}

CString CAdoDb::Format(_variant_t var)
{
	DATE dt;
	COleDateTime da;
	CString strValue;
	
	switch (var.vt)
	{
	case VT_BSTR://字符串
	case VT_LPSTR://字符串
	case VT_LPWSTR://字符串
		strValue = (LPCTSTR)(_bstr_t)var;
		break;
	case VT_I2://短整型
		strValue.Format("%d", var.iVal);
		break;
	case VT_UI2://无符号短整型
		strValue.Format("%d", var.uiVal);
		break;
	case VT_VOID://
	case VT_INT://整型
	case VT_I4://整型
		strValue.Format("%d", var.intVal);
		break;
	case VT_I8://长整型
		strValue.Format("%d", var.lVal);
		break;
	case VT_UINT://无符号整型
	case VT_UI4://无符号整型
		strValue.Format("%d", var.uintVal);
		break;
	case VT_UI8://无符号长整型
		strValue.Format("%d", var.ulVal);
		break;
	case VT_R4://浮点型
		strValue.Format("%.4f", var.fltVal);
		break;
	case VT_R8://双精度型
		strValue.Format("%.8f", var.dblVal);
		break;
	case VT_DECIMAL: //小数
		strValue.Format("%.4f", var.decVal);
		break;
	case VT_BOOL://布尔型
		strValue = var.lVal ? "TRUE" : "FALSE";
		break;
	case VT_DATE: //日期型
		dt = var.date;
		da = COleDateTime(dt); 
		strValue = da.Format("%Y-%m-%d %H:%M:%S");
		break;
	case VT_NULL://NULL值
		strValue = "";
		break;
	case VT_EMPTY://空
		strValue = "";
		break;
	case VT_UNKNOWN://未知类型
	default:
		strValue = "UN_KNOW";
		break;
	}
	return strValue;
}

BOOL CAdoDb::GetValueByIndex(CString strFieldIndex,CString strValueField,
	CString strTableName,CString strIndexValue,CString &strValue)
{
	try{
		if (GetObjectState()==adStateClosed)
			if (!OpenConnection()) return FALSE;
	}
	catch(...){
		return FALSE;
	}

	CString strSQL;
	strSQL.Format("SELECT %s from %s WHERE %s = '%s'",
	  strValueField.GetBuffer(strValueField.GetLength()),
	  strTableName.GetBuffer(strTableName.GetLength()),
	  strFieldIndex.GetBuffer(strFieldIndex.GetLength()),
	  strIndexValue.GetBuffer(strIndexValue.GetLength())
		);

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

		if(!pRec->adoEOF){
			pRec->MoveFirst();
			COleVariant var;

			VariantInit(&var);
			var = pRec->Fields->Item[0L]->Value;
			if(var.vt==VT_NULL)strValue.Empty();else strValue = _T((LPCTSTR)_bstr_t(var));
		}
		
		pRec->Close();
		pRec.Release();		
	}
	catch(_com_error &e){
		MessageBox(NULL,(const char *)e.Description(),"创建序号出错",0);
		RollBackTransaction();
		return FALSE;
	}

	return TRUE;
}