MFC ADO数据库.doc

任务5 数据库编程一、综合课程实习目的1掌握模式Visual C+对于数据库的操作。2掌握常用控件的使用方法。3熟悉数据库操作语句（insert/delete/select）。4熟悉操作数据库的对象。二、综合课程实习内容创建一个MFC应用程序TeacherMIS，进行教师数据的显示。三、综合课程实习指导1. 新建对话框工程，命名为TeacherMIS，删除TeacherMIS对话框中除了“确定”按钮的所有控件，对话框的字体为：宋体9号2. 导入ADO：在stdafx.h文件中添加：#import c:program filescommon filessystemadomsado15.dll no_namespace rename(EOF,adoEOF)3. 在工程项目类CTeacherMISApp中添加连接和记录集变量：_RecordsetPtr m_pRec;_ConnectionPtr m_pConn;4. 在CTeacherMISApp类的InitInstance函数中添加如下代码：:CoInitialize(NULL);trym_pConn.CreateInstance(_uuidof(Connection);/创建连接对象m_pConn-Open(Provider=Microsoft.Jet.OLEDB.4.0;Data Source=db1.mdb,adModeUnknown);/连接数据库catch(_com_error e)AfxMessageBox(数据库连接失败!);return false;m_pRec.CreateInstance(_uuidof(Recordset);5. 在CTeacherMISApp类中添加虚函数ExitInstance()然后编辑代码int CTeacherMISApp:ExitInstance() / TODO: Add your specialized code here and/or call the base classif(m_pConn-State)m_pConn-Close();m_pConnection= NULL;return CWinApp:ExitInstance();6. 在CTeacherMISApp类的头文件中添加一个工程类对象：extern CTeacherMISApp theApp;7. 在CTeacherMISDlg:OnOK()函数中要释放连接对象和记录集对象void CTeacherMISDlg:OnOK() / TODO: Add extra validation hereif(theApp.m_pConn-State)theApp.m_pRec-Close();theApp.m_pRec.Release();if(theApp.m_pConn-State)theApp.m_pConn-Close();theApp.m_pConn.Release();:CoUninitialize();CDialog:OnOK();8. 添加一个“登陆”对话框：宋体9号对该对话框建立类向导，并在AppWizzard中添加两个编辑框和“登陆”“取消”按钮消息以及OnInitDialog（），OnPain（）消息：在OnPaint（）函数中添加如下代码：void CLoginDlg:OnPaint() CFont NewFont;NewFont.CreateFont(30,0,0,0,700,TRUE,FALSE,0,ANSI_CHARSET,OUT_DEFAULT_PRECIS,CLIP_DEFAULT_PRECIS,DEFAULT_QUALITY,DEFAULT_PITCH|FF_SWISS,楷体);dc.SetBkMode(TRANSPARENT);CFont *pOldFont=dc.SelectObject(&NewFont);dc.SetTextColor(RGB(0,0,255);dc.TextOut(60,20,教职工信息管理系统);dc.SelectObject(pOldFont);在“登陆”按钮消息中添加如下代码：void CLoginDlg:OnOK() / TODO: Add extra validation hereUpdateData(true);_variant_t v_strQuery,v_strCount;v_strQuery=select count(*) from LoginUser where User_Name=+m_strUserName+ and User_Psw=+m_strUserPsw+;/select count(*) as CountUser from LoginUser where User_Name=+m_strUserName+ and User_Psw=+m_strUserPsw+; theApp.m_pRec-CursorType=adOpenStatic;theApp.m_pRec-CursorLocation=adUseClient;theApp.m_pRec-Open(v_strQuery,theApp.m_pConn.GetInterfacePtr(),adOpenDynamic,adLockOptimistic,adCmdText);v_strQuery=theApp.m_pRec-GetCollect(Expr1000);CString strCount=(char*)_bstr_t(v_strQuery);if(strCount!=1)MessageBox(用户名或密码错误!,登陆失败);theApp.m_pRec-Close();theApp.m_pRec=NULL;return;CDialog:OnOK();9. 在CTeacherMISApp的头文件中加入：#include LoginDlg.h，然后在InitInstance函数中加入代码CLoginDlg类的对象并显示：10. CLoginDlg dlglogin;11. int ret = dlglogin.DoModal();12. if(ret=IDCANCEL)13. return FALSE;14. 建立教职工基本信息对话框：（宋体9号）15. 对话框的ID为：IDD_DIALOG_BASEINFO；标题为：教职工基本信息添加控件：控 件ID 号Caption或Data连接变量列表控件IDC_LIST1m_BaseInfoList（CListCtrl）分组框IDC_STATIC查询条件静态文本IDC_STATIC按照组合框IDC_COMBO_SEARCHKEYData属性：职工号，姓名，部门，职称，学历m_strSearchKey（CString）编辑框IDC_EDIT_KEYVALUEm_strKeyValue（CString）命令按钮IDC_BUTTON_SEARCH查询命令按钮IDC_BUTTON_ADD添加命令按钮IDC_BUTTON_MODIFY修改命令按钮IDC_BUTTON_DEL删除然后对该对话框建立一个新类CBaseInfoDlg，其基类为CPropertyPage，继承方式为Public，然后添加连接变量（上表）和消息函数Object IDs消息Message消息处理函数IDC_BUTTON_SEARCH按钮BN_CLICKEDOnButtonSearch()IDC_BUTTON_ADD按钮BN_CLICKEDOnButtonAdd()IDC_BUTTON_MODIFY按钮BN_CLICKEDOnButtonModify()IDC_BUTTON_DEL按钮BN_CLICKEDOnButtonDel()CbaseInfoDlg对话框WM_INITDIALOGOnInitDialog()16. 将该CPropertyPage加入到建立工程时默认对话框CTeacherMISDlg中，在CTeacherMISDlg类中添加变量：CBaseInfoDlg m_BaseInfoPage;CPropertySheet m_Sheet;然后在CTeacherMISDlg的OnInitDialog()函数中添加如下代码：BOOL CTeacherMISDlg:OnInitDialog()m_Sheet.AddPage(&m_BaseInfoPage);m_Sheet.Create(this,WS_CHILD|WS_VISIBLE,0);m_Sheet.ModifyStyleEx(0,WS_EX_CONTROLPARENT);m_Sheet.ModifyStyle(0,WS_TABSTOP);m_Sheet.SetWindowsPos(NULL,0,0,0,0,SWP_NOZORDER|SWP_NOSIZE|SWP_NOACTIVATE);return TRUE; / return TRUE unless you set the focus to a control17. 初始化对话框：BOOL CBaseInfoDlg:OnInitDialog() CPropertyPage:OnInitDialog();/ TODO: Add extra initialization herem_BaseInfoList.ModifyStyle(0,LVS_REPORT|LVS_SINGLESEL|LVS_SHOWSELALWAYS);m_BaseInfoList.SetExtendedStyle(LVS_EX_GRIDLINES|LVS_EX_FULLROWSELECT|LVS_EX_ONECLICKACTIVATE|LVS_EX_HEADERDRAGDROP);m_BaseInfoList.SetTextColor(RGB(200,200,0);m_BaseInfoList.SetBkColor(RGB(240,247,233);m_BaseInfoList.InsertColumn(0,职工编号);m_BaseInfoList.InsertColumn(1,职工姓名);m_BaseInfoList.InsertColumn(2,所在部门);m_BaseInfoList.InsertColumn(3,职工性别);m_BaseInfoList.InsertColumn(4,出生日期);m_BaseInfoList.InsertColumn(5,职称);m_BaseInfoList.InsertColumn(6,学历);m_BaseInfoList.InsertColumn(7,取得学历时间);m_BaseInfoList.InsertColumn(8,毕业学校);m_BaseInfoList.InsertColumn(9,开始工作时间);m_BaseInfoList.InsertColumn(10,党员);RECT rectList;m_BaseInfoList.GetWindowRect(&rectList);int width=(rectList.right-rectList.left)/11;for(int i=0;iState)theApp.m_pRec-Close();theApp.m_pRec-Open(v_strSQL,theApp.m_pConn.GetInterfacePtr(),adOpenDynamic,adLockOptimistic,adCmdText);if(!theApp.m_pRec-BOF)theApp.m_pRec-MoveFirst();while(!theApp.m_pRec-adoEOF)CString strvalue;v_theValue=theApp.m_pRec-GetCollect(ID);strvalue=(char*)_bstr_t(v_theValue);/MessageBox(strvalue);m_BaseInfoList.InsertItem(i,strvalue);v_theValue=theApp.m_pRec-GetCollect(Name);strvalue=(char*)_bstr_t(v_theValue);m_BaseInfoList.SetItemText(i,1,strvalue);v_theValue=theApp.m_pRec-GetCollect(Depart);strvalue=(char*)_bstr_t(v_theValue);m_BaseInfoList.SetItemText(i,2,strvalue);v_theValue=theApp.m_pRec-GetCollect(Sex);strvalue=(char*)_bstr_t(v_theValue);m_BaseInfoList.SetItemText(i,3,strvalue);v_theValue=theApp.m_pRec-GetCollect(Birthday);strvalue=(char*)_bstr_t(v_theValue);m_BaseInfoList.SetItemText(i,4,strvalue);v_theValue=theApp.m_pRec-GetCollect(Job);strvalue=(char*)_bstr_t(v_theValue);m_BaseInfoList.SetItemText(i,5,strvalue);v_theValue=theApp.m_pRec-GetCollect(Degree);strvalue=(char*)_bstr_t(v_theValue);m_BaseInfoList.SetItemText(i,6,strvalue);v_theValue=theApp.m_pRec-GetCollect(GraduteDate);strvalue=(char*)_bstr_t(v_theValue);m_BaseInfoList.SetItemText(i,7,strvalue);v_theValue=theApp.m_pRec-GetCollect(School);strvalue=(char*)_bstr_t(v_theValue);m_BaseInfoList.SetItemText(i,8,strvalue);v_theValue=theApp.m_pRec-GetCollect(WorkDate);strvalue=(char*)_bstr_t(v_theValue);m_BaseInfoList.SetItemText(i,9,strvalue);v_theValue=theApp.m_pRec-GetCollect(IsParty);strvalue=(char*)_bstr_t(v_theValue);m_BaseInfoList.SetItemText(i,10,strvalue);theApp.m_pRec-MoveNext();catch(_com_error *e) AfxMessageBox(e-ErrorMessage();19. 添加一个“添加教师基本信息对话框”：IDD_DIALOG_BASEINFOADD，Caption属性为：添加教师基本信息；并建立类为：CBaseInfoAddDlg。同时建立该类控件的属性和连接变量控件名称及需修改属性（其余默认）ID号连接变量职工号编辑框IDC_EDIT_IDCStringm_strID姓名编辑框IDC_EDIT_NAMECStringm_strName部门组合框IDC_COMBO_DEPARTCStringm_strDepart单选按钮男；属性：Tab、GroupIDC_RADIO_NANintm_nSex单选按钮女；属性：TabIDC_RADIO_NV出生日期组合框年：Data：从1960至2000IDC_COMBO_BIRTHYEARCStringm_strBirthYear出生日期组合框月：Data：112IDC_COMBO_BIRTHMONTHCStringm_strBirthMonth出生日期组合框日：Data：131IDC_COMBO_BIRTHDAYCStringm_strBirthDay职称组合框：Data：助教，讲师，副教授，教授IDC_COMBO_JOBCStringm_strJob学历组合框：Data：中专，大专，本科，硕士，博士IDC_COMBO_DEGREECStringm_strDegree取得学历时间组合框年：Data：从1960至2000IDC_COMBO_GRADUATEYEARCStringm_strGraduateDay取得学历时间组合框月：Data：112IDC_COMBO_GRADUATEMONTHCStringm_strGraduatMonth取得学历时间组合框日：Data：131IDC_COMBO_GRADUATEDAYCStringm_strGraduatYear毕业学校编辑框IDC_EDIT_SCHOOLCStringm_strSchool开始工作时间组合框年：Data：从1960至2000IDC_COMBO_WORKYEARCStringm_strWorkYear开始工作时间组合框月：Data：112IDC_COMBO_WORKMONTHCStringm_strWorkMonth开始工作时间组合框日：Data：131IDC_COMBO_WORKDAYCStringm_strWorkDay党员复选框IDC_CHECK_PARTYBOOLm_bParty在该类中添加一个自定义的标识变量：int m_nDlgType，然后针对“教职工基本信息管理（CPropertyPage）”对话框中的“添加”按钮编写代码：#include BaseInfoAddDlg.hvoid CBaseInfoDlg:OnButtonAdd() / TODO: Add your control notification handler code hereCBaseInfoAddDlg BaseDlg;BaseDlg.m_nDlgType=0;if(IDOK!=BaseDlg.DoModal()return;RefreshList();MessageBox(添加成功!);针对“添加教师基本信息对话框（CBaseInfoAddDlg）”增加 “确定”按钮的代码：void CBaseInfoAddDlg:OnOK() / TODO: Add extra validation hereUpdateData(true);if(m_strID.IsEmpty()MessageBox(职工号不能为空!);return;if(m_strName.IsEmpty()MessageBox(姓名不能为空!);return;if(theApp.m_pRec-State)theApp.m_pRec-Close();_variant_t v_sqlID;v_sqlID=select * from BaseInfo where ID=+m_strID+;theApp.m_pRec-CursorType = adOpenStatic;theApp.m_pRec-CursorLocation = adUseClient;theApp.m_pRec-Open(v_sqlID,theApp.m_pConn.GetInterfacePtr(),adOpenDynamic,adLockOptimistic,adCmdText);if(0=m_nDlgType)if(theApp.m_pRec-GetRecordCount()!=0)theApp.m_pRec-Close();MessageBox(同一职工已经存在!);return;elsetheApp.m_pRec-Close();CString sex,birth,graduate,work,party;if(m_nSex)sex=女;elsesex=男;birth=m_strBirthYear+-+m_strBirthMonth+-+m_strBirthDay;graduate=m_strGraduateYear+-+m_strGraduateMonth+-+m_strGraduateDay;work=m_strWorkYear+-+m_strWorkMonth+-+m_strWorkDay;if(m_bParty)party=是;elseparty=否;_variant_t v_insBaseInfo;CString str=insert into BaseInfo(ID,Name,Depart,Sex,Birthday,Job,Degree,GraduateDate,School,WorkDate,IsParty) values(+m_strID+,+m_strName+,+m_strDepart+,+sex+,+birth+,+m_strJob+,+m_strDegree+,+graduate+,+m_strSchool+,+work+,+party+);v_insBaseInfo=str;if(theApp.m_pRec-Open(v_insBaseInfo,theApp.m_pConn.GetInterfacePtr(),adOpenDynamic,adLockOptimistic,adCmdText)MessageBox(添加记录成功!);m_strID=m_strName=m_strDepart=;UpdateData(false);else_variant_t v_strModifyRecord;CString strsex,strbirth,strgraduate,strwork,strparty;if(m_nSex)strsex=女;elsestrsex=男;strbirth=m_strBirthYear+-+m_strBirthMonth+-+m_strBirthDay;strgraduate=m_strGraduateYear+-+m_strGraduateMonth+-+m_strGraduateDay;strwork=m_strWorkYear+-+m_strWorkMonth+-+m_strWorkDay;if(m_bParty)strparty=是;elsestrparty=否;_variant_t v_ModifyRecord;v_ModifyRecord=Update BaseInfo set Name=+m_strName+,Depart=+m_strDepart+,Sex=+strsex+,Birthday=+strbirth+,Job=+m_strJob+,Degree=+m_strDegree+,GraduateDate=+strgraduate+,WorkDate=+strwork+,IsParty=+strparty+ where ID like +m_strID+;if(theApp.m_pRec-State)theApp.m_pRec-Close();if(theApp.m_pRec-Open(v_ModifyRecord,theApp.m_pConn.GetInterfacePtr(),adOpenDynamic,adLockOptimistic,adCmdText)MessageBox(修改记录成功!);CDialog:OnOK();20. “教职工基本信息管理（CPropertyPage）”对话框中的“修改”按钮，添加一个int类型 的m_nDlgType变量，并添加一个CBaseInfoAddDlg:OnInitDialog() 成员函数，再在该函数中添加如下代码：BOOL CBaseInfoAddDlg:OnInitDialog( () CDialog:OnInitDialog();/ TODO: Add extra initialization hereif(m_nDlgType=0)SetWindowText(添加教师基本信息);GotoDlgCtrl(GetDlgItem(IDC_EDIT_ID);else if(m_nDlgType=1)SetWindowText(修改教师基本信息);GetDlgItem(IDC_EDIT_ID)-EnableWindow(false);GotoDlgCtrl(GetDlgItem(IDC_EDIT_NAME);return TRUE; / return TRUE unless you set the focus to a control / EXCEPTION: OCX Property Pages should return FALSE21. “教职工基本信息管理（CPropertyPage）”对话框中的“修改”按钮的代码：void CBaseInfoDlg:OnButtonModify() / TODO: Add your control notification handler code hereint i=m_BaseInfoList.GetSelectionMark();if(-1=i)MessageBox(请选中一条记录!);return;CBaseInfoAddDlg BaseDlg;BaseDlg.m_nDlgType=1;BaseDlg.m_strID=m_BaseInfoList.GetItemText(i,0);/MessageBox(BaseDlg.m_strID);BaseDlg.m_strName=m_BaseInfoList.GetItemText(i,1);BaseDlg.m_strDepart=m_BaseInfoList.GetItemText(i,2);if(m_BaseInfoList.GetItemText(i,3)=男)BaseDlg.m_nSex=1;elseBaseDlg.m_nSex=0;CString strBirth=m_BaseInfoList.GetItemText(i,4);BaseDlg.m_strBirthYear=strBirth.Left(4);BaseDlg.m_strBirthMonth=strBirth.Mid(5,2);BaseDlg.m_strBirthDay=strBirth.Right(2);BaseDlg.m_strJob=m_BaseInfoList.GetItemText(i,5);BaseDlg.m_strDegree=m_BaseInfoList.GetItemText(i,6);CString strgradute=m_BaseInfoList.GetItemText(i,7);BaseDlg.m_strGraduateYear=strgradute.Left(4);BaseDlg.m_strGraduateMonth=strgradute.Mid(5,2);BaseDlg.m_strGraduateDay=strgradute.Right(2);BaseDlg.m_strSchool=m_BaseInfoList.GetItemText(i,8);CString strwork=m_BaseInfoList.GetItemText(i,9);BaseDlg.m_strWorkYear=strgradute.Left(4);BaseDlg.m_s