VC操作SQL数据库记录
各位大侠，我想在MFC中操作SQLServer数据库记录。主要是要能够遍历查询数据库记录，并能取出符合条件的记录中的某一列的值
求帮忙，小弟在线等！
[解决办法]
- C/C++ code
//////////////////////////////////////////////////////////////////////////// AdoHelper.h//////////////////////////////////////////////////////////////////////////#pragma once#import "C:\Program Files\Common Files\System\ado\msado15.dll"no_namespace rename("EOF","adoEOF")//rename("BOF","adoBOF")CString GetAppPath();CString ConvertVariantToString(const _variant_t& vt);class AdoHelper{protected: _ConnectionPtr m_pConnection; _RecordsetPtr m_pRecordset;public: AdoHelper(); virtual ~AdoHelper(); bool Move(int nRecordNum); //移动记录 bool MovePrevious(); //向上移动 bool MoveLast(); //最后一条 bool MoveNext(); //向下移动 bool MoveFirst(); //第一条 bool Open(CString srecordset, UINT adCmd); // void close(); void resOpen(CString TSQL); //打开记录集 int GetRecordCount(); //获得记录个数 void GetErrors(_com_error eErrors); //获得错误信息 CString GetFieldValue(CString Field); //获得字段值 void ExecuteSQL(CString SQL); //执行SQL语句 };
[解决办法]
- C/C++ code
//////////////////////////////////////////////////////////////////////////// AdoHelper.cpp//////////////////////////////////////////////////////////////////////////#include "AdoHelper.h"AdoHelper::AdoHelper(){ ::CoInitialize(NULL); try { CString strDBPath = GetAppPath(); CString strConnection; strConnection.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=%s%s",strDBPath,"Account.accdb"); m_pConnection.CreateInstance(__uuidof(Connection)); m_pConnection->Open((_bstr_t)strConnection, "", "", adConnectUnspecified); } catch(_com_error e) { AfxMessageBox(e.Description()); }}AdoHelper::~AdoHelper(){ ::CoUninitialize();}void AdoHelper::close(){ m_pRecordset->Close(); m_pConnection->Close(); m_pRecordset = NULL; m_pConnection = NULL; }bool AdoHelper::Open( CString srecordset, UINT adCmd ){ try { m_pRecordset = m_pConnection->Execute((_bstr_t)srecordset, NULL, adCmd); } catch(_com_error&e) { this->GetErrors(e); return false; } return true;}int AdoHelper::GetRecordCount(){ int nCount = 0; try { m_pRecordset->MoveFirst(); } catch (...) { return 0; } if (m_pRecordset->adoEOF) { return 0; } while(!m_pRecordset->adoEOF) { m_pRecordset->MoveNext(); nCount = nCount + 1; } m_pRecordset->MoveFirst(); return nCount; }void AdoHelper::GetErrors( _com_error eErrors ){ ErrorsPtr pErrors = m_pConnection->GetErrors(); if(pErrors->GetCount() == 0) { MessageBox(NULL, eErrors.ErrorMessage(),"错误", MB_OK|MB_ICONEXCLAMATION); } else { for (int i = 0; i < pErrors->GetCount(); i++) { _bstr_t desc = pErrors->GetItem((long)i)->GetDescription(); MessageBox(NULL, desc, "错误", MB_OK|MB_ICONEXCLAMATION); } }}void AdoHelper::resOpen( CString TSQL ){ try { _bstr_t bstrSQL = TSQL.AllocSysString(); m_pRecordset.CreateInstance(__uuidof(Recordset)); m_pRecordset->Open(bstrSQL, (IDispatch*)m_pConnection, adOpenDynamic, adLockOptimistic, adCmdText); } catch(_com_error e) { m_pRecordset = m_pConnection->Execute((_bstr_t)TSQL, NULL, adCmdText); }}CString AdoHelper::GetFieldValue( CString Field ){ _variant_t Thevalue; CString temp; Thevalue = m_pRecordset->GetCollect((_bstr_t)Field); if (Thevalue.vt == VT_EMPTY || Thevalue.vt == VT_NULL) { temp = ""; } else { temp = (char*)(_bstr_t)Thevalue; // temp = ConvertVariantToString(Thevalue); temp.Trim(); } return temp;}void AdoHelper::ExecuteSQL( CString TSQL ){ try { m_pConnection->Execute((_bstr_t)TSQL, NULL, adCmdText); } catch(_com_error e) { AfxMessageBox(e.Description()); }}bool AdoHelper::Move( int nRecordNum ){ try { if (!m_pRecordset->BOF) { m_pRecordset->MoveFirst(); } m_pRecordset->Move(nRecordNum); } catch(_com_error e) { AfxMessageBox(e.Description()); return false; } return true;}bool AdoHelper::MovePrevious(){ try { m_pRecordset->MovePrevious(); } catch(_com_error e) { AfxMessageBox(e.Description()); return false; } return true;}bool AdoHelper::MoveLast(){ try { m_pRecordset->MoveLast(); } catch(_com_error e) { AfxMessageBox(e.Description()); return false; } return true;}bool AdoHelper::MoveNext(){ try { m_pRecordset->MoveNext(); } catch(_com_error e) { AfxMessageBox(e.Description()); return false; } return true;}bool AdoHelper::MoveFirst(){ try { m_pRecordset->MoveFirst(); } catch(_com_error e) { AfxMessageBox(e.Description()); return false; } return true;}CString GetAppPath(){ char path_buffer[_MAX_PATH]; char drive[_MAX_DRIVE]; char dir[_MAX_DIR]; char fname[_MAX_FNAME]; char ext[_MAX_EXT]; GetModuleFileName(AfxGetApp()->m_hInstance,path_buffer,_MAX_PATH); //非mfc环境下，第一个参数填NULL即可 _splitpath_s( path_buffer, drive, dir, fname, ext ); CString sPath; sPath=drive; sPath+=dir; return sPath;}CString ConvertVariantToString(const _variant_t& vt){ CString str; switch (vt.vt) { case VT_UI1: str.Format(_T("%d"), V_UI1(&vt)); break; case VT_I2: str.Format(_T("%d"), V_I2(&vt)); break; case VT_I4: str.Format(_T("%d"), V_I4(&vt)); break; case VT_R4: str.Format(_T("%f"), V_R4(&vt)); break; case VT_R8: str.Format(_T("%f"), V_R8(&vt)); break; case VT_I1: str.Format(_T("%d"), V_I1(&vt)); break; case VT_UI2: str.Format(_T("%d"), V_UI2(&vt)); break; case VT_UI4: str.Format(_T("%u"), V_UI4(&vt)); break; case VT_INT: str.Format(_T("%d"), V_INT(&vt)); break; case VT_UINT: str.Format(_T("%u"), V_UINT(&vt)); break; case VT_BOOL: str = (V_BOOL(&vt) == VARIANT_TRUE) ? _T("1") : _T("0"); break; case VT_BSTR: str = V_BSTR(&vt); break; default: break; } return str;}