- 关于SQL server的数据库操作
- 1.导入两个文件
- 以下是:(DatabaseAccess.h 文件)
- // DatabaseAccess.h: interface for the CDatabaseAccess class.
- //
- //
- #if !defined(AFX_DATABASEACCESS_H__19163588_441F_4B21_B354_877412B2B46A__INCLUDED_)
- #define AFX_DATABASEACCESS_H__19163588_441F_4B21_B354_877412B2B46A__INCLUDED_
- #if _MSC_VER > 1000
- #pragma once
- #endif // _MSC_VER > 1000
- #include <Afxtempl.h>
- // 描述_ParameterPtr对象信息的结构
- typedef struct tagParameterInfo{ // 参数信息
- _bstr_t Name; // 参数名字
- enum DataTypeEnum Type; // 参数类型
- enum ParameterDirectionEnum Direction; // 参数方向(输入、输出)
- long Size; // 参数尺寸
- _variant_t Value; // 参数值。
- }PARAMETERINFO, *PPARAMETERINFO;
- // 使用前在StdAfx.h中加入#import "C:\Program Files\Common Files\System\ado\msado15.dll" no_namespace rename("EOF", "adoEOF")
- // 访问数据库前先用::CoInitialize(NULL)初始化COM环境。
- // 程序退出时使用::CoUninitialize()释放资源。
- class CDatabaseAccess
- {
- private:
- bool m_bIsOracle; //ORACLE与SQLServer切换标志。默认是ORACLE数据库
- _ConnectionPtr m_pCon; //连接对象
- public:
- int GetRecordSetCount(_RecordsetPtr m);
- //连接本地数据库(无密码)
- BOOL ConnectDB(char *DatabaseName);
- //连接本地数据库(有密码) 通过ODBC方式连接
- BOOL ConnectDB(const char *DatabaseName,char *PassWord);
- CDatabaseAccess();
- virtual ~CDatabaseAccess();
- // 连接到数据库服务器,DSN方式 1成功 0失败
- BOOL ConnectDBSQL(char *pcDSNName,char *pcUserName,char *pcUserPassword);
- // 连接到数据库服务器,需要IP,端口和Oracle全局数据库名或SID的连接方式。1成功 0失败
- BOOL ConnectDB(const char *pcUserName ,const char *pcUserPassword ,const char *pcServerIP ,const char *pcServerPort="1024" ,const char *pcConnectServiceName = "MFD");
- //关闭与数据库的连接
- void CloseConnect();
- //设置数据库类型(ORACLE与SQLServer)
- void SetDatabaseType(bool bIsOracle=true);
- // 执行存储过程(普通存储过程,即非包中的),非零-正确执行;0-参数个数为负数
- BOOL ExecuteProcedure(const char *pcProcedureName , PARAMETERINFO aPrameterInfo[],BYTE bCount);
- // 执行存储过程返回记录集的地址。在此函数中使用约定包名:mfd_callpkg。字段列表用"逗号"隔开如:"empid,empname"
- _RecordsetPtr *CDatabaseAccess::ExecProGetRecordset(const char *pcProcedureName , PARAMETERINFO aParameterInfo[] , BYTE bParametersCount , const char *pcFieldsList , const char *pcPackageName = "mfd_callpkg");
- // 无参数的存储过程(普通存储过程,非包中)
- _RecordsetPtr *CDatabaseAccess::ExecProGetRecordset(const char *pcProcedureName);
- // 无参数的存储过程
- _RecordsetPtr *CDatabaseAccess::ExecProGetRecordset(const char *pcProcedureName , const char *pcFieldsList);
- // 执行SQL语句
- _RecordsetPtr *ExecuteSQLSentence( const char *pcSentence);
- };
- #endif // !defined(AFX_DATABASEACCESS_H__19163588_441F_4B21_B354_877412B2B46A__INCLUDED_)
- 以下是(DatabaseAccess.cpp 文件)
- // DatabaseAccess.cpp: implementation of the CDatabaseAccess class.
- //
- //
- #include "stdAfx.h"
- #include "DatabaseAccess.h"
- #ifdef _DEBUG
- #undef THIS_FILE
- static char THIS_FILE[]=__FILE__;
- #define new DEBUG_NEW
- #endif
- //
- // Construction/Destruction
- //
- CDatabaseAccess::CDatabaseAccess()
- {
- CoInitialize(NULL);
- m_bIsOracle=true; //默认是使用ORACLE数据库
- try
- {
- m_pCon.CreateInstance(_uuidof(Connection));
- }
- catch(_com_error &e)
- {
- TRACE("--CDatabaseAccess::CDatabaseAccess()-- 创建COM实例失败%s,%s\n",(char *)e.Description(),(char *)e.ErrorMessage());
- return;
- }
- catch (...) {
- TRACE("--CDatabaseAccess::CDatabaseAccess()-- 未知错误,创建COM实例失败\n");
- return;
- }
- }
- CDatabaseAccess::~CDatabaseAccess()
- {
- CloseConnect();
- }
- // 关闭与数据库的连接
- void CDatabaseAccess::CloseConnect()
- {
- try
- {
- if(m_pCon ->State)
- m_pCon ->Close();
- }
- catch(_com_error &e)
- {
- TRACE("--CDatabaseAccess::CloseConnect()-- %s,%s\n",(char *)e.Description(),(char *)e.ErrorMessage());
- return;
- }
- catch (...) {
- TRACE("--CDatabaseAccess::CloseConnect()-- 不明错误\n");
- return;
- }
- }
- // 设置数据库类型
- void CDatabaseAccess::SetDatabaseType(bool bIsOracle/*=true*/)
- {
- m_bIsOracle=bIsOracle;
- }
- //连接到数据库服务器,采用系统DSN方式 1成功 0失败
- BOOL CDatabaseAccess::ConnectDBSQL(char *pcDSNName,char *pcUserName,char *pcUserPassword)
- {
- //连接Sql Server数据库
- if (!m_bIsOracle)
- {
- try
- {
- CString strConnection; //连接字符串
- strConnection.Format("DSN=%s;UID=%s;PWD=%s" ,pcDSNName ,
- pcUserName ,pcUserPassword);
- HRESULT hResult=m_pCon->Open(strConnection.GetBuffer(0) ,"" ,"" ,adModeUnknown);
- strConnection.ReleaseBuffer();
- if(SUCCEEDED(hResult))
- return 1; //连接成功!
- else
- return 0; //连接失败!
- }
- catch(_com_error &e)
- {
- TRACE("--CDatabaseAccess::ConnectDB()-- %s,%s\n",(char *)e.Description() ,(char *)e.ErrorMessage());
- return 0; //连接失败
- }
- catch (...) {
- TRACE("--CDatabaseAccess::ConnectDB()-- 不明错误\n");
- return 0;
- }
- }
- }
- // 连接到数据库服务器,需要IP,端口和Oracle全局数据库名或SID的连接方式。1成功 0失败.
- BOOL CDatabaseAccess::ConnectDB(const char *pcUserName ,const char *pcUserPassword ,const char *pcServerIP ,const char *pcServerPort ,const char *pcGlobalDatabaseName/* = "MFD"*/)
- {
- if(!m_bIsOracle)
- {
- try
- {
- CString strConnection; //连接字符串
- strConnection.Format("Provider=SQLOLEDB.1;Password=%s;Persist Security Info=True;User ID=%s;Initial Catalog=%s;Data Source=%s" ,
- pcUserPassword,pcUserName,pcGlobalDatabaseName,pcServerIP);
- TRACE("%s\n" , strConnection.GetBuffer(0));
- HRESULT hResult=m_pCon->Open(strConnection.GetBuffer(0),"","",adModeUnknown);
- strConnection.ReleaseBuffer();
- if(SUCCEEDED(hResult))
- return 1; //连接成功!
- else
- return 0; //连接失败!
- }
- catch(_com_error &e)
- {
- TRACE("--CDatabaseAccess::ConnectDB()-- %s,%s\n",(char *)e.Description() ,(char *)e.ErrorMessage());
- return 0; //连接失败
- }
- catch (...) {
- TRACE("--CDatabaseAccess::ConnectDB()-- 不明错误\n");
- return 0;
- }
- }
- else
- {
- try
- {
- CString strConnection; //连接字符串
- strConnection.Format("Provider=MSDAORA.1;Data Source=(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = %s)(PORT = %s)))(CONNECT_DATA =(SERVICE_NAME = %s)));User ID=%s;Password=%s" ,
- pcServerIP ,pcServerPort ,pcGlobalDatabaseName ,pcUserName , pcUserPassword);
- TRACE("%s\n" , strConnection.GetBuffer(0));
- HRESULT hResult=m_pCon->Open(strConnection.GetBuffer(0),"","",adModeUnknown);
- strConnection.ReleaseBuffer();
- if(SUCCEEDED(hResult))
- return 1; //连接成功!
- else
- return 0; //连接失败!
- }
- catch(_com_error &e)
- {
- TRACE("--CDatabaseAccess::ConnectDB()-- %s,%s\n",(char *)e.Description() ,(char *)e.ErrorMessage());
- return 0; //连接失败
- }
- catch (...) {
- TRACE("--CDatabaseAccess::ConnectDB()-- 不明错误\n");
- return 0;
- }
- }
- }
- // 执行存储过程(普通存储过程,即非包中的),非零-正确执行;0-失败(参数个数为负数)。
- BOOL CDatabaseAccess::ExecuteProcedure(const char *pcProcedureName , PARAMETERINFO aParameterInfo[] , BYTE bCount)
- {
- _CommandPtr pCmd;
- HRESULT hRet = pCmd.CreateInstance(_uuidof(Command));
- if(FAILED(hRet))
- {
- TRACE("--CDatabaseAccess::ExecuteProcedure()-- 创建COMMAND对象失败!");
- return 0;
- }
- _ParameterPtr *aParameterPtr = NULL;
- if(bCount > 0)
- {
- // 动态创建参数对象
- aParameterPtr = new _ParameterPtr[bCount];
- memset(aParameterPtr , 0 ,sizeof(_ParameterPtr) * bCount);
- }
- try
- {
- _bstr_t bstrSPName(pcProcedureName); // 存储过程名
- if(bCount<=0) // 无参数
- {
- if(bCount<0)
- {
- return 0; // 出错
- }
- pCmd->ActiveConnection = m_pCon; // 设置COMMAND对象的属性
- pCmd->CommandText = bstrSPName;
- pCmd->CommandType = adCmdStoredProc;
- // 动态创建记录集对象。
- _RecordsetPtr *ppRs = new _RecordsetPtr();
- (*ppRs) = pCmd->Execute(NULL ,NULL ,NULL); // 执行存储过程
- return (BOOL)ppRs;
- }
- pCmd->ActiveConnection = m_pCon; // 设置COMMAND对象的属性
- pCmd->CommandText = bstrSPName;
- pCmd->CommandType = adCmdStoredProc;
- //创建参数实例并添加之
- for(int i=0; i < bCount; i++)
- {
- aParameterPtr[i] = pCmd->CreateParameter(aParameterInfo[i].Name , aParameterInfo[i].Type , aParameterInfo[i].Direction , aParameterInfo[i].Size);
- pCmd->Parameters->Append(aParameterPtr[i]);
- }
- if(!m_bIsOracle) // SQLServer中需要!
- pCmd->Parameters->Refresh(); // 资源集中的操作
- // 对输入参数的处理
- for(i=0; i < bCount; i++)
- {
- if(aParameterInfo[i].Direction==adParamInput || aParameterInfo[i].Direction == adParamInputOutput)
- {
- if(m_bIsOracle) //oracle数据库
- {
- pCmd->Parameters->GetItem(_variant_t(aParameterInfo[i].Name) )->Value = aParameterInfo[i].Value;
- }
- else //SQLserver数据库
- {
- CString str;
- str.Format("@%s",((char *)aParameterInfo[i].Name));
- pCmd->Parameters->GetItem(_variant_t(_bstr_t(str.AllocSysString())))->Value = aParameterInfo[i].Value;
- }
- }
- }
- pCmd->Execute(NULL ,NULL ,NULL); //通过COMMAND对象来执行存储过程。
- // 对输出参数的处理
- for(i=0; i < bCount; i++)
- {
- if(aParameterInfo[i].Direction==adParamOutput || aParameterInfo[i].Direction == adParamInputOutput)
- {
- if(m_bIsOracle) //oracle数据库
- {
- aParameterInfo[i].Value = pCmd->Parameters->GetItem(_variant_t(aParameterInfo[i].Name) )->Value;
- }
- else //SQLserver数据库
- {
- CString str;
- str.Format("@%s",((char *)aParameterInfo[i].Name));
- aParameterInfo[i].Value = pCmd->Parameters->GetItem(_variant_t(_bstr_t(str.AllocSysString())))->Value;
- }
- }
- }
- if(bCount > 0)
- delete []aParameterPtr;
- return 1;
- }
- catch(_com_error &e)
- {
- TRACE("--CDatabaseAccess::ExecuteProcedure()-- %s,%s\n",(char *)e.Description(),(char *)e.ErrorMessage());
- if(bCount > 0)
- delete []aParameterPtr;
- return 0;
- }
- catch (...) {
- TRACE("--CDatabaseAccess::ExecuteProcedure()-- 不明错误\n");
- if(bCount > 0)
- delete []aParameterPtr;
- return 0;
- }
- }
- // 执行存储过程返回记录集的地址。在此函数中使用约定包名:mfd_callpkg。字段列表用"逗号"隔开如:"empid,empname"。
- // 存储过程格式约定:包名.存储过程名(输入参数 , 输入参数 , 输出参数 , 结果集)。
- _RecordsetPtr *CDatabaseAccess::ExecProGetRecordset(const char *pcProcedureName , PARAMETERINFO aParameterInfo[] , BYTE bParametersCount ,
- const char *pcFieldsList , const char *pcPackageName/* = "mfd_callpkg"*/)
- {
- _RecordsetPtr *ppRs = new _RecordsetPtr; // 动态创建记录集对象
- HRESULT hpRecordset = (*ppRs).CreateInstance(_uuidof(Recordset));
- if(FAILED(hpRecordset))
- {
- TRACE("--CDatabaseAccess::ExecProGetRecordset()-- 创建RECORDSET对象失败!");
- return NULL;
- }
- _CommandPtr pCmd;
- HRESULT hRet = pCmd.CreateInstance(_uuidof(Command));
- if(FAILED(hRet))
- {
- TRACE("--CDatabaseAccess::ExecProGetRecordset()-- 创建COMMAND对象失败!");
- return NULL;
- }
- const CString strResultsetNumber = "1000"; // 结果集中记录的个数.好像是上限
- CString strCallScript; // 调用格式.
- strCallScript = "{call ";
- if(strlen(pcPackageName)) // 如果在包中。则连入包名。
- {
- strCallScript += pcPackageName;
- strCallScript += ".";
- }
- strCallScript += pcProcedureName;
- strCallScript += "(";
- for(int i=1 ; i<=bParametersCount ; i++)
- {
- strCallScript += "?,";
- }
- strCallScript += "{resultset ";
- strCallScript += strResultsetNumber;
- strCallScript += ",";
- strCallScript += pcFieldsList;
- strCallScript += "})}";
- TRACE("%s\n" , strCallScript.GetBuffer(0));
- _ParameterPtr *aParameterPtr = NULL;
- if(bParametersCount > 0)
- { // 动态创建参数对象
- aParameterPtr=new _ParameterPtr[bParametersCount];
- memset(aParameterPtr , 0 ,sizeof(_ParameterPtr) * bParametersCount);
- }
- try
- {
- if(bParametersCount <= 0) // 无参数
- {
- if(bParametersCount < 0)
- {
- delete ppRs;
- return (_RecordsetPtr*)NULL; // 出错
- }
- pCmd->ActiveConnection = m_pCon; // 设置COMMAND对象的属性
- pCmd->CommandText = strCallScript.AllocSysString();
- pCmd->CommandType = adCmdText;
- _variant_t vtEmpty (DISP_E_PARAMNOTFOUND, VT_ERROR); //变体类型空值
- (*ppRs)->CursorLocation = adUseClient;
- (*ppRs)->Open((IDispatch*)pCmd , vtEmpty, adOpenKeyset, adLockOptimistic,adOpenRecordUnspecified);
- return ppRs;
- }
- pCmd->ActiveConnection = m_pCon; // 设置COMMAND对象的属性
- pCmd->CommandText = strCallScript.AllocSysString();
- pCmd->CommandType = adCmdText;
- //创建参数实例并添加之
- for(int i=0; i < bParametersCount; i++)
- {
- aParameterPtr[i] = pCmd->CreateParameter(aParameterInfo[i].Name , aParameterInfo[i].Type , aParameterInfo[i].Direction , aParameterInfo[i].Size);
- pCmd->Parameters->Append(aParameterPtr[i]);
- }
- if(!m_bIsOracle) // SQLServer中需要!
- pCmd->Parameters->Refresh(); // 资源集中的操作
- // 对输入参数的处理
- for(i=0; i < bParametersCount; i++)
- {
- if(aParameterInfo[i].Direction==adParamInput || aParameterInfo[i].Direction == adParamInputOutput)
- {
- if(m_bIsOracle) //oracle数据库
- {
- pCmd->Parameters->GetItem(_variant_t(aParameterInfo[i].Name) )->Value = aParameterInfo[i].Value;
- }
- else //SQLserver数据库
- {
- CString str;
- str.Format("@%s",((char *)aParameterInfo[i].Name));
- pCmd->Parameters->GetItem(_variant_t(_bstr_t(str.AllocSysString())))->Value = aParameterInfo[i].Value;
- }
- }
- }
- _variant_t vtEmpty (DISP_E_PARAMNOTFOUND, VT_ERROR); //变体类型空值
- (*ppRs)->CursorLocation = adUseClient;
- (*ppRs)->Open((IDispatch*)pCmd , vtEmpty, adOpenKeyset/*adOpenForwardOnly*/, adLockOptimistic,adOpenRecordUnspecified);
- // 对输出参数的处理
- for(i=0; i < bParametersCount; i++)
- {
- if(aParameterInfo[i].Direction==adParamOutput || aParameterInfo[i].Direction == adParamInputOutput)
- {
- if(m_bIsOracle) //oracle数据库
- {
- aParameterInfo[i].Value = pCmd->Parameters->GetItem(_variant_t(aParameterInfo[i].Name) )->Value;
- }
- else //SQLserver数据库
- {
- CString str;
- str.Format("@%s",((char *)aParameterInfo[i].Name));
- aParameterInfo[i].Value = pCmd->Parameters->GetItem(_variant_t(_bstr_t(str.AllocSysString())))->Value;
- }
- }
- }
- if(bParametersCount > 0)
- delete []aParameterPtr;
- return ppRs; //返回记录集对象的指针.
- }
- catch(_com_error &e)
- {
- TRACE("--CDatabaseAccess::ExecProGetRecordset()-- %s,%s %x\n",(char *)e.Description(),(char *)e.ErrorMessage(),e.Error());
- if(e.Error() == 0x80004005)
- {
- TRACE("到数据库的网络连接可能已经断开。\n");
- }
- if(bParametersCount > 0)
- delete []aParameterPtr;
- delete ppRs;
- return NULL;
- }
- catch (...) {
- TRACE("--ExecProGetRecordset()-- 不明错误\n");
- if(bParametersCount > 0)
- delete []aParameterPtr;
- delete ppRs;
- return NULL;
- }
- }
- // 无参数的存储过程(普通存储过程,非包中)。
- _RecordsetPtr *CDatabaseAccess::ExecProGetRecordset(const char *pcProcedureName)
- {
- return (_RecordsetPtr *)ExecuteProcedure(pcProcedureName, (PARAMETERINFO *)NULL , 0);
- }
- // 无参数的存储过程
- _RecordsetPtr *CDatabaseAccess::ExecProGetRecordset(const char *pcProcedureName , const char *pcFieldsList)
- {
- return ExecProGetRecordset(pcProcedureName, (PARAMETERINFO *)NULL , 0 , pcFieldsList);
- }
- // 执行SQL语句
- _RecordsetPtr *CDatabaseAccess::ExecuteSQLSentence( const char *pcSentence)
- {
- _RecordsetPtr *ppRs=new _RecordsetPtr; // 动态创建记录集对象。
- try
- {
- (*ppRs).CreateInstance(_uuidof(Recordset));
- (*ppRs)->Open(_variant_t(pcSentence) , m_pCon.GetInterfacePtr() , adOpenKeyset , adLockOptimistic, adCmdText);
- return ppRs;
- }
- catch(_com_error &e)
- {
- TRACE("--CDatabaseAccess::ExecuteSQLSentence()-- %s,%s\n", (char *)e.Description() ,(char *)e.ErrorMessage());
- delete ppRs;
- return NULL;
- }
- catch (...) {
- TRACE("--CDatabaseAccess::ExecuteSQLSentence()-- 不明错误\n");
- delete ppRs;
- return NULL;
- }
- }
- //连接内地数据库方式(无用户名和密码格式)(Access数据库)
- BOOL CDatabaseAccess::ConnectDB(char *DatabaseName)
- {
- if (!m_bIsOracle)
- {
- try
- {
- CString strConnection; //连接字符串
- strConnection.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=%s;Persist Security Info=False",DatabaseName);
- HRESULT hResult=m_pCon->Open(strConnection.GetBuffer(0) ,"" ,"" ,adModeUnknown);
- strConnection.ReleaseBuffer();
- if(SUCCEEDED(hResult))
- return 1; //连接成功!
- else
- return 0; //连接失败!
- }
- catch(_com_error &e)
- {
- TRACE("--CDatabaseAccess::ConnectDB()-- %s,%s\n",(char *)e.Description() ,(char *)e.ErrorMessage());
- return 0; //连接失败
- }
- catch (...) {
- TRACE("--CDatabaseAccess::ConnectDB()-- 不明错误\n");
- return 0;
- }
- }
- }
- BOOL CDatabaseAccess::ConnectDB(const char *DatabaseName,char *PassWord)
- {
- //连接ACCESS数据库,带密码
- if (!m_bIsOracle)
- {
- try
- {
- CString strConnection; //连接字符串
- strConnection.Format("DSN=%s;PWD=%s",DatabaseName,PassWord);
- HRESULT hResult=m_pCon->Open(strConnection.GetBuffer(0) ,"" ,"" ,adModeUnknown);
- strConnection.ReleaseBuffer();
- if(SUCCEEDED(hResult))
- return 1; //连接成功!
- else
- return 0; //连接失败!
- }
- catch(_com_error &e)
- {
- TRACE("--CDatabaseAccess::ConnectDB()-- %s,%s\n",(char *)e.Description() ,(char *)e.ErrorMessage());
- return 0; //连接失败
- }
- catch (...) {
- TRACE("--CDatabaseAccess::ConnectDB()-- 不明错误\n");
- return 0;
- }
- }
- }
- int CDatabaseAccess::GetRecordSetCount(_RecordsetPtr m)
- {
- int i=0;
- if(!(m->GetRecordCount()==0))
- {
- m->MoveFirst();
- while (!m->adoEOF) {
- i++;
- m->MoveNext();
- }
- }
- return i;
- }
- 2.在stfAfx.h文件中导入
- #import "C:\Program Files\Common Files\System\ado\msado15.dll" no_namespace rename("EOF", "adoEOF")
- 在APP () 中 定义 CDatabaseAccess ado;
- 3.在APP的Initlnstance()
- 中添加
- BOOL CTextApp::InitInstance()
- {
- // Standard initialization
- if (!AfxOleInit())
- {
- //AfxMessageBox(IDP_OLE_INIT_FAILED);
- return FALSE;
- }
- ado.SetDatabaseType(false);
- ado.ConnectDB("sa","root","172.20.3.23","1433","student");
- AfxEnableControlContainer();
- 4.添加
- void CTextDlg::OnButton1() //添加
- {
- // TODO: Add your control notification handler code here
- UpdateData(TRUE);
- CString strSQL;
- _variant_t var;
- _RecordsetPtr ptr;
- int index=0;
- int age=atoi(m_nAge);
- int nScore=atoi(m_nScore);
- strSQL.Format("insert into student values('%s','%s',%d,%d)",m_strID,m_strName,age,nScore);// 添加语句
- if (AfxMessageBox("你确定要添加这条记录吗?",MB_OKCANCEL,0)==IDOK)
- { //zhongdian
- ptr=((CTextApp*)AfxGetApp())->ado.ExecuteSQLSentence(strSQL);//执行语句
- }
- }
- 5.删除记录
- void CTextDlg::OnButton2() //删除
- {
- // TODO: Add your control notification handler code here
- UpdateData(true);
- CString strSQL;
- _RecordsetPtr ptr;//额外添加
- int nitem;
- strSQL.Format("delete from student where ID=%s",m_strID);
- if (AfxMessageBox("你确定要添删除这条记录吗?",MB_OKCANCEL,0)==IDOK)
- {
- ptr=((CTextApp*)AfxGetApp())->ado.ExecuteSQLSentence(strSQL);//执行语句
- }
- }
-
- 5.初始化CListCtrl控件
- CString temp[4]={"ID","stuName","age","score"};
- for (int i=0;i<4;i++)
- {
- m_listctrl.InsertColumn(i,temp[i],LVCFMT_LEFT,100);
- }
- CListCtrl *pmyListCtrl=(CListCtrl *)GetDlgItem(IDC_LIST2);
- DWORD dwStyle =GetWindowLong(pmyListCtrl->m_hWnd,GWL_STYLE);
- SetWindowLong(pmyListCtrl->m_hWnd,GWL_STYLE,dwStyle|LVS_REPORT);
- DWORD styles=pmyListCtrl->GetExtendedStyle();
- pmyListCtrl->SetExtendedStyle(styles|LVS_EX_FULLROWSELECT|LVS_EX_GRIDLINES);
- 6.查找后显示
- void CTextDlg::showtable()
- {
- CString strSQL;
- CString strID,strName,strAge,strScore;
- _variant_t var;
- _RecordsetPtr ptr;//额外添加
- UpdateData(true);
- int index=0;
- m_listctrl.DeleteAllItems();
- strSQL.Format("select * from student"); //格式化SQL语句
- //zhongdian
- ptr=((CTextApp*)AfxGetApp())->ado.ExecuteSQLSentence(strSQL);
- //
- if(!ptr->BOF)
- {
- ptr->MoveFirst();
- }
- else
- return;
- while(!ptr->adoEOF) //当记录集指针不是指向最后一条的下面时
- {
- var=ptr->GetCollect("ID"); //获取字段值ID的值
- strID="";
- if (var.vt!=NULL)
- {
- strID=(LPCSTR)_bstr_t(var); // 将获取的值赋给strID
- }
- var=ptr->GetCollect("stuName");
- strName="";
- if (var.vt!=NULL)
- {
- strName=(LPCSTR)_bstr_t(var);
- }
- var=ptr->GetCollect("age");
- strAge="";
- if (var.vt!=NULL)
- {
- strAge=(LPCSTR)_bstr_t(var);
- }
- var=ptr->GetCollect("score");
- strScore="";
- if (var.vt!=NULL)
- {
- strScore=(LPCSTR)_bstr_t(var);
- }
- m_listctrl.InsertItem(index,strID);
- m_listctrl.SetItemText(index,1,strName);
- m_listctrl.SetItemText(index,2,strAge);
- m_listctrl.SetItemText(index,3,strScore);
- ptr->MoveNext();
- index++;
- UpdateData(false);
- }
- }
- 7.修改记录集
- void CTextDlg::OnButton4() //改
- {
- // TODO: Add your control notification handler code here
- CString strSQL;
- CString strID,strName,strAge,strScore;
- _variant_t var;
- _RecordsetPtr ptr;//额外添加
- UpdateData(true);
- int index=0;
- strSQL.Format("update student set stuName='%s' where ID='%s'",m_strName,m_strID); //格式化SQL语句
- //zhongdian
- ptr=((CTextApp*)AfxGetApp())->ado.ExecuteSQLSentence(strSQL);
- }
- 8.查找记录集
- void CTextDlg::OnButton3() //查找
- {
- // TODO: Add your control notification handler code here
- // showtable();
- CString strSQL;
- CString strID,strName,strAge,strScore;
- _variant_t var;
- _RecordsetPtr ptr;//额外添加
- UpdateData(true);
- int index=0;
- m_listctrl.DeleteAllItems();
- strSQL.Format("select * from student where ID='%s'",m_strID); //格式化SQL语句
- //zhongdian
- ptr=((CTextApp*)AfxGetApp())->ado.ExecuteSQLSentence(strSQL);
- //
- if(!ptr->BOF)
- {
- ptr->MoveFirst();
- }
- else
- return;
- while(!ptr->adoEOF) //当记录集指针不是指向最后一条的下面时
- {
- var=ptr->GetCollect("stuName");
- //获取字段值ID的值
- strName="";
- if (var.vt!=NULL)
- {
- strName=(LPCSTR)_bstr_t(var); // 将获取的值赋给strID
- }
- m_strName=strName;
- UpdateData(false);
- break;
- }
- }