adb.open(dsn) ;; DB 연결 (CompanyAccDB) adb.open(null, false, false, strdsn) ;; DB 연결 (DSN=CompanyAccDB) adb.executesql(strsql) ;; 결과가없는연산자 (Ins/Del/Upd) adb.close() ;; DB 연결끊음 CRecordset aqryset(&adb) ;; DB에질의결과를저장 aqryset.open(ntype, strsql) ;; 결과가있는연산자 ( 검색 ) aqryset.movefirst,...prev,...next,...last ;; tuple 선택 aqryset.getfieldvalue(nndx, strvalue) ;; 선택된 tuple의값 aqryset.close() ;; 질의마감 Visual C++ MFC MFC 응용프로그램 Name: DatabaseApp 응용프로그램종류 : 대화상자기반 SDL(Security Development Lifecycle) 검사리소스언어 : 영어 ( 미국 ) About Box 대화상자제목 : Company Database Application Class Name: CDatabaseApp 프로젝트가생성되면사용되는문자가자동으로유니코드를사용하게됩니다. 스트링을끝에 NULL을정확하게인식하기위해서는 ASCII 문자로설정해야하는데다음과같다. 솔루션탐색기 DatabaseApp 속성구성 : 모든구성플랫폼 : 모든플랫폼구성속성 일반 문자집합 : 설정안함 ( 오른쪽위의그림참조 ) Resource View DatabaseApp DatabaseApp.rc Dialog IDD_DATABASE_DIALOG
Comtrol ID 레이블 Hotkey Callback 함수 ⑴ Button, IDC_BTN_DEPT, &Department, OnBtnDepartment ⑵ Button, IDC_BTN_EMP, &Employee, OnBtnEmployee ⑶ Button, IDC_BTN_PRJCT, &Project, OnBtnProject ⑷ Button, IDC_BTN_WRKSN, &WorksOn, OnBtnWorksOn ⑸ Button, IDC_BTN_DEPTLOC, Dept&Locations, OnBtnDeptLocations ⑹ Button, IDC_BTN_DPNDNT, Depe&ndent, OnBtnDependent ⑺ Button, IDC_BTN_VW1, View &1, OnBtnView1 ⑻ Button, IDC_BTN_VW2, View &2, OnBtnView2 ⑼ Button, IDC_BTN_VW3, View &3, OnBtnView3 ⑽ Button, IDC_BTN_VW4, View &4, OnBtnView4 ⑾ Button, IDOK, Check Mnemonic Tab Order Tap Stop Group [DatabaseApp.rc] &Quit ;; 단축키의중복체크 ;; Control( 버튼 ) 들의순서를지정 ;; Tab 으로 Control 들사이를이동 ;; Control 들의그룹 IDD_DATABASEAPP_DIALOG DIALOGEX 0, 0, 143, 140 STYLE DS_SETFONT DS_FIXEDSYS WS_POPUP WS_VISIBLE WS_CAPTION WS_SYSMENU WS_THICKFRAME EXSTYLE WS_EX_APPWINDOW CAPTION "Company Database Application" FONT 8, "MS Shell Dlg", 0, 0, 0x1 BEGIN PUSHBUTTON "&Department",IDC_BTN_DEPT,7,7,64,21,WS_GROUP PUSHBUTTON "&Employee",IDC_BTN_EMP,7,28,64,21,NOT WS_TABSTOP PUSHBUTTON "&Project",IDC_BTN_PRJCT,7,49,64,21,NOT WS_TABSTOP PUSHBUTTON "&WorksOn",IDC_BTN_WRKSN,7,70,64,21,NOT WS_TABSTOP PUSHBUTTON "Dept&Locations",IDC_BTN_DEPTLOC,7,91,64,21,NOT WS_TABSTOP PUSHBUTTON "Depe&ndent",IDC_BTN_DPNDNT,7,112,64,21,NOT WS_TABSTOP PUSHBUTTON "View &1",IDC_BTN_VW1,72,7,64,21,WS_GROUP PUSHBUTTON "View &2",IDC_BTN_VW2,72,28,64,21,NOT WS_TABSTOP PUSHBUTTON "View &3",IDC_BTN_VW3,72,49,64,21,NOT WS_TABSTOP PUSHBUTTON "View &4",IDC_BTN_VW4,72,70,64,21,NOT WS_TABSTOP PUSHBUTTON "&Quit",IDOK,79,103,57,30,WS_GROUP END [Resource.h] #define IDD_DATABASEAPP_DIALOG 102 #define IDR_MAINFRAME 128 #define IDC_BTN_DEPT 1000 #define IDC_BTN_EMP 1001 #define IDC_BTN_PRJCT 1002 #define IDC_BTN_WRKSN 1003 #define IDC_BTN_DEPTLOC 1004 #define IDC_BTN_DPNDNT 1005 #define IDC_BTN_VW1 1006 #define IDC_BTN_VW2 1007 #define IDC_BTN_VW3 1008 #define IDC_BTN_VW4 1009 Resource View DatabaseApp DatabaseApp.rc Dialog IDD_DATABASE_DIALOG IDD_DATABASE_DIALOG Class Wizard... Callback 함수 OnBtnDepartment OnBtnEmployee OnBtnProject OnBtnWorksOn OnBtnDeptLocations OnBtnDependent OnBtnView1 OnBtnView2 OnBtnView3 OnBtnView4 [DatabaseAppDlg.h]
public: afx_msg HCURSOR OnQueryDragIcon(); DECLARE_MESSAGE_MAP() afx_msg void OnBtnDepartment(); afx_msg void OnBtnEmployee(); afx_msg void OnBtnProject(); afx_msg void OnBtnWorksOn(); afx_msg void OnBtnDeptLocations(); afx_msg void OnBtnDependent(); afx_msg void OnBtnView1(); afx_msg void OnBtnView2(); afx_msg void OnBtnView3(); afx_msg void OnBtnView4(); ; [DatabaseAppDlg.cpp] BEGIN_MESSAGE_MAP(CDatabaseAppDlg, CDialogEx) ON_WM_PAINT() ON_WM_QUERYDRAGICON() ON_BN_CLICKED(IDC_BTN_DEPT, &CDatabaseAppDlg::OnBtnDepartment) ON_BN_CLICKED(IDC_BTN_EMP, &CDatabaseAppDlg::OnBtnEmployee) ON_BN_CLICKED(IDC_BTN_PRJCT, &CDatabaseAppDlg::OnBtnProject) ON_BN_CLICKED(IDC_BTN_WRKSN, &CDatabaseAppDlg::OnBtnWorksOn) ON_BN_CLICKED(IDC_BTN_DEPTLOC, &CDatabaseAppDlg::OnBtnDeptLocations) ON_BN_CLICKED(IDC_BTN_DPNDNT, &CDatabaseAppDlg::OnBtnDependent) ON_BN_CLICKED(IDC_BTN_VW1, &CDatabaseAppDlg::OnBtnView1) ON_BN_CLICKED(IDC_BTN_VW2, &CDatabaseAppDlg::OnBtnView2) ON_BN_CLICKED(IDC_BTN_VW3, &CDatabaseAppDlg::OnBtnView3) ON_BN_CLICKED(IDC_BTN_VW4, &CDatabaseAppDlg::OnBtnView4) END_MESSAGE_MAP() HCURSOR CDatabaseAppDlg::OnQueryDragIcon() return static_cast<hcursor>(m_hicon); void CDatabaseAppDlg::OnBtnDepartment() void CDatabaseAppDlg::OnBtnEmployee() void CDatabaseAppDlg::OnBtnProject() void CDatabaseAppDlg::OnBtnWorksOn() void CDatabaseAppDlg::OnBtnDeptLocations() void CDatabaseAppDlg::OnBtnDependent() void CDatabaseAppDlg::OnBtnView1() void CDatabaseAppDlg::OnBtnView2()
void CDatabaseAppDlg::OnBtnView3() void CDatabaseAppDlg::OnBtnView4() 1 단계저장압축파일 (1. DatabaseApp.zip) [ 제어판 관리도구 ODBC 데이터원본 (32비트)] 시스템 DSN 추가 Microsoft Access Driver(*.mdb, *.accdb) 데이터원본이름 : CompanyAccDB 선택 Company.accdb를선택한다. [DatabaseApp.h] #include <afxdb.h> #include "resource.h" // main symbols void ErrorMessage(CDBException *e, CString smsg); [DatabaseApp.cpp] void ErrorMessage(CDBException *e, CString smsg) CString strerrmsg = smsg + _T(".\15\12") + e->m_strerror; AfxMessageBox(strErrMsg, MB_OK MB_ICONINFORMATION); [DatabaseAppDlg.h] // Dialog Data #ifdef AFX_DESIGN_TIME enum IDD = IDD_DATABASEAPP_DIALOG ; #endif CDatabase *m_pdb; BOOL ConnectDB(void); protected: virtual void DoDataExchange(CDataExchange* pdx); // DDX/DDV support [DatabaseAppDlg.cpp] CDatabaseAppDlg::CDatabaseAppDlg(CWnd* pparent /*=NULL*/) : CDialogEx(CDatabaseAppDlg::IDD, pparent) static CDatabase adb; m_pdb = &adb; m_hicon = AfxGetApp()->LoadIcon(IDR_MAINFRAME); BOOL CDatabaseAppDlg::OnInitDialog() CDialogEx::OnInitDialog(); // Set the icon for this dialog. The framework does this automatically // when the application's main window is not a dialog SetIcon(m_hIcon, TRUE); // Set big icon
SetIcon(m_hIcon, FALSE); // Set small icon // TODO: Add extra initialization here ConnectDB(); return TRUE; // return TRUE unless you set the focus to a control BOOL CDatabaseAppDlg::ConnectDB() int bisok = true; try m_pdb->open(_t("companyaccdb")); // m_pdb->open(null, false, false, "DSN=CompanyAccDB"); catch (CDBException* e) ErrorMessage(e, _T(" 데이타베이스에연결할수없습니다.")); e->delete(); bisok = false; return bisok; 질의결과의튜플을보여주는기능으로모든대화상자에사용된다. Class View 클래스마법사 프로젝트 : DatabaseApp, 클래스추가... Class Name: CIterator [Iterator.h] #include <afxdb.h> #pragma once class CIterator public: CIterator(void); ~CIterator(void); CDatabase *m_pdb; CRecordset *m_piterset; int m_nndx; int m_nndxmax; virtual void FetchTuple(BOOL bfetch = true) = 0; virtual CWnd *GetFirstButton(void) = 0; virtual CWnd *GetPrevButton(void) = 0; virtual CWnd *GetNextButton(void) = 0; virtual CWnd *GetLastButton(void) = 0; virtual CString &GetIterLabel(void) = 0; ; void EnableIterator(void); void FirstFetch(void); void PrevFetch(void); void NextFetch(void); void LastFetch(void); [Iterator.cpp] #include "stdafx.h" #include "Iterator.h" CIterator::CIterator(void)
CIterator::~CIterator(void) void CIterator::FirstFetch(void) m_piterset->movefirst(); m_nndx = 1; FetchTuple(); void CIterator::PrevFetch(void) m_piterset->moveprev(); m_nndx--; FetchTuple(); void CIterator::NextFetch(void) m_piterset->movenext(); m_nndx++; FetchTuple(); void CIterator::LastFetch(void) m_piterset->movelast(); m_nndx = m_nndxmax; FetchTuple(); void CIterator::EnableIterator(void) BOOL isenable = m_nndxmax >= 1 && m_nndx!= 1; GetFirstButton()->EnableWindow(isEnable); GetPrevButton()->EnableWindow(isEnable); isenable = m_nndxmax >= 1 && m_nndx!= m_nndxmax; GetNextButton()->EnableWindow(isEnable); GetLastButton()->EnableWindow(isEnable); CString strlabel("iteration"); if (m_nndxmax >= 1) CString strcount; strcount.format(cstring(" (%d/%d)"), m_nndx, m_nndxmax); strlabel += strcount; GetIterLabel() = strlabel; DB 에연산 ( 검색 / 삽입 / 삭제 / 수정 ) 을실행하기위하여대화상자와 CRecordset 에서유도되 는질의집합을생성한다. 2 단계저장압축파일 (2. Iterator.zip)
Department 테이블에대한연산 Insert/Delete/Update/Query 를하기위하여위와같은 대화상자를생성한다. 이를위하여 ⑴ 질의의결과를저장할집합클래스를생성하고, ⑵ 대화상자를디자인하고, ⑶ 테이블의튜플을 I/O 하기위하여대화상자클래스를생성한다. 바로앞에서언급한것처럼 Department 에연산하기위하여 CDeptDlg 와 CDeptSet 클래 스를생성해야한다. Class View Class Wizard Add Class... MFC ODBC 소비자... Data Source... 컴퓨터데이터원본 CompanyAccDB Tables Department Class: CDeptSet, DeptSet.h, DeptSet.cpp [DeptSet.h] CStringW CString [DeptSet.cpp] #error 삭제 #error Security Issue: The connection string may contain a password CString CDeptSet::GetDefaultConnect() return _T("DSN=CompanyAccDB"); CString CDeptSet::GetDefaultSQL() return _T("SELECT * FROM Department"); Resource View DatabaseApp DatabaseApp.rc Dialog Insert Dialog ID: IDD_DEPT_DLG Comtrol ID 레이블 Hotkey Callback 함수 / 멤버변수 Static, IDC_STATIC, DN&ame Edit, IDC_EDT_DNAME, m_strdname Static, IDC_STATIC, DNum&ber Edit, IDC_EDT_DNMBR, m_strdnumber Static, IDC_STATIC, M&grSSN Edit, IDC_EDT_MGRSSN, m_strmgrssn Static, IDC_STATIC, Mg&rStartDate
Edit, IDC_EDT_MGRDATE, m_strmgrstartdate Static, IDC_STATIC, &Where Edit, IDC_EDT_WHERE, m_strwhere Static, IDC_STATIC, &Set Edit, IDC_EDT_SET, m_strset Group, IDC_STATIC, Operation Button, IDC_BTN_QUERY, &Query, OnBtnQuery Button, IDC_BTN_INSERT, &Insert, OnBtnInsert Button, IDC_BTN_DELETE, &Delete, OnBtnDelete Button, IDC_BTN_UPDATE, &Update, OnBtnUpdate Group, IDC_STC_ITER, Iteration, m_striteration Button, IDC_BTN_FIRST, &First, OnBtnFirst Button, IDC_BTN_PREV, &Prev, OnBtnPrev Button, IDC_BTN_NEXT, &Next, OnBtnNext Button, IDC_BTN_LAST, &Last, OnBtnLast Button, IDOK, Qui&t Check Mnemonic ;; 단축키의중복체크 Tab Order ;; Control( 버튼 ) 들의순서를지정 Tap Stop ;; Tab으로 Control들사이를이동 Group ;; Control들의그룹 [DatabaseApp.rc] IDD_DEPT_DLG DIALOGEX 0, 0, 257, 142 STYLE DS_SETFONT DS_MODALFRAME DS_FIXEDSYS WS_POPUP WS_CAPTION WS_SYSMENU CAPTION "Department Table" FONT 8, "MS Shell Dlg", 400, 0, 0x1 BEGIN RTEXT "DN&ame",IDC_STATIC,7,7,45,12 EDITTEXT IDC_EDT_DNAME,59,7,76,12,ES_AUTOHSCROLL RTEXT "DNum&ber",IDC_STATIC,7,28,45,12 EDITTEXT IDC_EDT_DNMBR,59,28,18,12,ES_AUTOHSCROLL RTEXT "M&grSSN",IDC_STATIC,7,49,45,12 EDITTEXT IDC_EDT_MGRSSN,59,49,52,12,ES_AUTOHSCROLL RTEXT "Mg&rStartDate",IDC_STATIC,7,70,45,12 EDITTEXT IDC_EDT_MGRDATE,59,70,52,12,ES_AUTOHSCROLL RTEXT "&Where",IDC_STATIC,7,105,45,12 EDITTEXT IDC_EDT_WHERE,59,105,128,12,ES_AUTOHSCROLL RTEXT "&Set",IDC_STATIC,7,123,45,12 EDITTEXT IDC_EDT_SET,59,120,128,12,ES_AUTOHSCROLL GROUPBOX "Operation",IDC_STATIC,145,7,104,56 PUSHBUTTON "&Query",IDC_BTN_QUERY,155,21,84,17,WS_GROUP PUSHBUTTON "&Insert",IDC_BTN_INSERT,155,39,28,17,NOT WS_TABSTOP PUSHBUTTON "&Delete",IDC_BTN_DELETE,183,39,28,17,NOT WS_TABSTOP PUSHBUTTON "&Update",IDC_BTN_UPDATE,211,39,28,17,NOT WS_TABSTOP GROUPBOX "Iteration",IDC_STC_ITER,145,66,104,37,WS_GROUP PUSHBUTTON "&First",IDC_BTN_FIRST,155,79,20,16,WS_GROUP PUSHBUTTON "&Prev",IDC_BTN_PREV,176,79,20,16,NOT WS_TABSTOP PUSHBUTTON "&Next",IDC_BTN_NEXT,197,79,20,16,NOT WS_TABSTOP PUSHBUTTON "&Last",IDC_BTN_LAST,218,79,20,16,NOT WS_TABSTOP PUSHBUTTON "Qui&t",IDOK,200,105,50,27,WS_GROUP END [Resource.h] #define IDR_MAINFRAME 128 #define IDD_DATABASEAPP_DIALOG 102 #define IDD_DEPT_DLG 129
#define IDC_BTN_DEPT 1000 #define IDC_BTN_EMP 1001 #define IDC_EDT_DNAME 1001 #define IDC_BTN_PRJCT 1002 #define IDC_EDT_DNMBR 1002 #define IDC_BTN_WRKSN 1003 #define IDC_EDT_MGRSSN 1003 #define IDC_BTN_DEPTLOC 1004 #define IDC_EDT_MGRDATE 1004 #define IDC_BTN_DPNDNT 1005 #define IDC_EDT_WHERE 1005 #define IDC_BTN_VW1 1006 #define IDC_EDT_SET 1006 #define IDC_BTN_VW2 1007 #define IDC_BTN_QUERY 1007 #define IDC_BTN_VW3 1008 #define IDC_BTN_INSERT 1008 #define IDC_BTN_VW4 1009 #define IDC_BTN_DELETE 1009 #define IDC_BTN_UPDATE 1010 #define IDC_STC_ITER 1011 #define IDC_BTN_FIRST 1012 #define IDC_BTN_PREV 1013 #define IDC_BTN_NEXT 1014 #define IDC_BTN_LAST 1015 IDD_DEPT_DLG Add Class... Class Name: CDeptDlg IDD_DEPT_DLG Class Wizard... virtual function Callback 함수 멤버변수 OnInitDialog OnBtnQuery OnBtnInsert OnBtnDelete OnBtnUpdate OnBtnFirst OnBtnPrev OnBtnNext OnBtnLast m_strdname m_strdnumber m_strmgrssn m_strmgrstartdate m_strwhere m_strset m_striteration 가상함수, callback 함수, 멤버변수를설정한결과는다음과같다. [DeptDlg.h] protected: virtual void DoDataExchange(CDataExchange* pdx); // DDX/DDV 지원입니다. public: ; DECLARE_MESSAGE_MAP() virtual BOOL OnInitDialog(); afx_msg void OnBtnQuery(); afx_msg void OnBtnInsert(); afx_msg void OnBtnDelete(); afx_msg void OnBtnUpdate(); afx_msg void OnBtnFirst(); afx_msg void OnBtnPrev(); afx_msg void OnBtnNext(); afx_msg void OnBtnLast(); CString m_strdname; CString m_strdnumber; CString m_strmgrssn; CString m_strmgrstartdate; CString m_strwhere; CString m_strset; CString m_striteration; [DeptDlg.cpp] CDeptDlg::CDeptDlg(CWnd* pparent /*=NULL*/) : CDialogEx(IDD_DEPT_DLG, pparent), m_strdname(_t("")), m_strdnumber(_t("")), m_strmgrssn(_t("")), m_strmgrstartdate(_t(""))
, m_strwhere(_t("")), m_strset(_t("")), m_striteration(_t("")) CDeptDlg::~CDeptDlg() void CDeptDlg::DoDataExchange(CDataExchange* pdx) CDialogEx::DoDataExchange(pDX); DDX_Text(pDX, IDC_EDT_DNAME, m_strdname); DDX_Text(pDX, IDC_EDT_DNMBR, m_strdnumber); DDX_Text(pDX, IDC_EDT_MGRSSN, m_strmgrssn); DDX_Text(pDX, IDC_EDT_MGRDATE, m_strmgrstartdate); DDX_Text(pDX, IDC_EDT_WHERE, m_strwhere); DDX_Text(pDX, IDC_EDT_SET, m_strset); DDX_Text(pDX, IDC_STC_ITER, m_striteration); BEGIN_MESSAGE_MAP(CDeptDlg, CDialogEx) ON_BN_CLICKED(IDC_BTN_QUERY, &CDeptDlg::OnBtnQuery) ON_BN_CLICKED(IDC_BTN_INSERT, &CDeptDlg::OnBtnInsert) ON_BN_CLICKED(IDC_BTN_DELETE, &CDeptDlg::OnBtnDelete) ON_BN_CLICKED(IDC_BTN_UPDATE, &CDeptDlg::OnBtnUpdate) ON_BN_CLICKED(IDC_BTN_FIRST, &CDeptDlg::OnBtnFirst) ON_BN_CLICKED(IDC_BTN_PREV, &CDeptDlg::OnBtnPrev) ON_BN_CLICKED(IDC_BTN_NEXT, &CDeptDlg::OnBtnNext) ON_BN_CLICKED(IDC_BTN_LAST, &CDeptDlg::OnBtnLast) END_MESSAGE_MAP() // CDeptDlg 메시지처리기입니다. BOOL CDeptDlg::OnInitDialog() CDialogEx::OnInitDialog(); // TODO: 여기에추가초기화작업을추가합니다. return TRUE; // return TRUE unless you set the focus to a control // 예외 : OCX 속성페이지는 FALSE 를반환해야합니다. void CDeptDlg::OnBtnQuery() void CDeptDlg::OnBtnInsert() void CDeptDlg::OnBtnDelete() void CDeptDlg::OnBtnUpdate() void CDeptDlg::OnBtnFirst() void CDeptDlg::OnBtnPrev()
void CDeptDlg::OnBtnNext() void CDeptDlg::OnBtnLast() 이제준비작업이완료되었으니본겨적인데이터베이스연산작업을시작한다. 먼저질의 의결과로생성된튜플들의순회하기위한 iterator 를장착한다. [DeptDlg.h] #include "Iterator.h" #include "DeptSet.h" #pragma once // CDeptDlg dialog class CDeptDlg : public CDialogEx, public CIterator DECLARE_DYNAMIC(CDeptDlg) public: CDeptDlg(CWnd* pparent = NULL); virtual ~CDeptDlg(); // standard constructor // Dialog Data #ifdef AFX_DESIGN_TIME enum IDD = IDD_DEPT_DLG ; #endif CDeptSet *m_pset; virtual void FetchTuple(BOOL bfetch = true); virtual CWnd *GetFirstButton(void); virtual CWnd *GetPrevButton(void); virtual CWnd *GetNextButton(void); virtual CWnd *GetLastButton(void); virtual CString &GetIterLabel(void); protected: virtual BOOL OnInitDialog(); virtual void DoDataExchange(CDataExchange* pdx); // DDX/DDV support [DeptDlg.cpp] CDeptDlg::CDeptDlg(CWnd* pparent /*=NULL*/) : CDialogEx(CDeptDlg::IDD, pparent), m_strdname(_t("")), m_strdnumber(_t("")), m_strmgrssn(_t("")), m_strmgrstartdate(_t("")), m_strwhere(_t("")), m_strset(_t("")), m_striteration(_t("iteration")) static CDeptSet aqueryset; m_piterset = m_pset = &aqueryset; // CDeptDlg message handlers BOOL CDeptDlg::OnInitDialog() CDialogEx::OnInitDialog(); // TODO: Add extra initialization here
m_nndxmax = 0; return TRUE; // return TRUE unless you set the focus to a control // EXCEPTION: OCX Property Pages should return FALSE CWnd *CDeptDlg::GetFirstButton(void) return GetDlgItem(IDC_BTN_FIRST); CWnd *CDeptDlg::GetPrevButton(void) return GetDlgItem(IDC_BTN_PREV); CWnd *CDeptDlg::GetNextButton(void) return GetDlgItem(IDC_BTN_NEXT); CWnd *CDeptDlg::GetLastButton(void) return GetDlgItem(IDC_BTN_LAST); CString &CDeptDlg::GetIterLabel(void) return m_striteration; void CDeptDlg::OnBtnQuery() UpdateData(); try if (m_pset->isopen()) m_pset->close(); CString strsql = m_pset->getdefaultsql(); if (!m_strwhere.trim().isempty()) strsql += _T(" WHERE ") + m_strwhere; m_pset->open(crecordset::dynaset, strsql); while (!m_pset->iseof()) m_pset->movenext(); m_nndxmax = m_pset->getrecordcount(); FirstFetch(); UpdateData(false); catch (CDBException *e) ErrorMessage(e, _T("Query Failure")); e->delete(); m_nndxmax = 0; void CVw1Dlg::OnBtnFirst() FirstFetch(); UpdateData(false); void CVw1Dlg::OnBtnPrev() PrevFetch(); UpdateData(false); void CVw1Dlg::OnBtnNext() NextFetch(); UpdateData(false); void CVw1Dlg::OnBtnLast()
LastFetch(); UpdateData(false); void CDeptDlg::FetchTuple(BOOL bfetch) if (bfetch) else [DatabaseAppDlg.cpp] #include "stdafx.h" #include "DatabaseApp.h" #include "DatabaseAppDlg.h" #include "afxdialogex.h" #include "DeptDlg.h" // adlg <--- arecordset m_strdname = m_pset->m_dname.trimright(); m_strdnumber.format(_t("%d"), m_pset->m_dnumber); m_strmgrssn = m_pset->m_mgrssn.trimright(); m_strmgrstartdate = m_pset->m_mgrstartdate.trimright(); // arecordset <--- adlg m_pset->m_dname = (LPCTSTR)m_strDName; m_pset->m_dnumber = atoi((const char *)(LPCTSTR)m_strDNumber); m_pset->m_mgrssn = (LPCTSTR)m_strMgrSSN; m_pset->m_mgrstartdate = (LPCTSTR)m_strMgrStartDate; void CDatabaseAppDlg::OnBtnDepartment() CDeptDlg adlg; adlg.m_pdb = m_pdb; adlg.domodal(); 3 단계저장압축파일 (3. Department.zip) strsql = INSERT INTO Department VALUES(...) m_pdb->executesql(strsql); [DeptDlg.cpp] void CDeptDlg::OnBtnInsert() UpdateData(); try if (m_pset->isopen()) m_pset->close(); CString strsql = _T("INSERT INTO Department VALUES ("); CString *attrs = &m_strdname; int nctr = (int)m_pset->m_nfields; for (int nndx = 0; nndx < nctr; nndx++) strsql += (nndx)? _T(", ") : _T(""); strsql += (attrs[nndx].trim().isempty())? _T("NULL") : _T("'") + attrs[nndx] + _T("'"); strsql += _T(")"); m_pdb->executesql(strsql); AfxMessageBox(_T("Success...")); catch (CDBException* e) ErrorMessage(e, _T("Department 를삽입할수없습니다 ")); e->delete(); m_nndxmax = 0;
CRecordset m_pset이 open되어있는상태에서 m_pset->addnew() 를호출한후에 m_pset의애트리뷰트들에값들을전달하고 m_pset->update() 를호출한다. [DeptDlg.cpp] void CDeptDlg::OnBtnInsert() UpdateData(); try if (!m_pset->isopen()) m_pset->open(); m_pset->addnew(); FetchTuple(false); m_pset->update(); m_pset->close(); AfxMessageBox(_T("Success...")); catch (CDBException* e) ErrorMessage(e, _T("Department 를삽입할수없습니다 ")); e->delete(); m_nndxmax = 0; // default SQL // arecordset <-- adlg strsql = DELETE FROM Department WHERE DNumber= %s m_pdb->executesql(strsql); [DeptDlg.cpp] void CDeptDlg::OnBtnDelete() UpdateData(); try if (m_pset->isopen()) m_pset->close(); CString strsql = _T("DELETE FROM Department WHERE DNumber=") + m_strdnumber; m_pdb->executesql(strsql); AfxMessageBox(_T("Success...")); catch (CDBException* e) ErrorMessage(e, _T("Department 를삭제할수없습니다 ")); e->delete(); m_nndxmax = 0; CRecordset m_pset 이 open 되어있는상태에서 m_pset->delete() 를호출한다. [DeptDlg.cpp] void CDeptDlg::OnBtnDelete() UpdateData(); try m_pset->delete();
AfxMessageBox(_T("Success...")); catch (CDBException* e) ErrorMessage(e, _T("Department 를삭제할수없습니다 ")); e->delete(); m_nndxmax = 0; strsql = UPDATE Department SET %s WHERE DNumber=%s 를작성하여 m_pdb->executesql(strsql); [DeptDlg.cpp] void CDeptDlg::OnBtnUpdate() UpdateData(); try if (m_pset->isopen()) m_pset->close(); if (m_strset.isempty()) AfxMessageBox(_T("SET 절이없습니다.")); return; CString strsql = _T("UPDATE Department SET ") + m_strset + _T(" WHERE DNumber=") + m_strdnumber; m_pdb->executesql((lpctstr)strsql); AfxMessageBox(_T("Success...")); catch (CDBException* e) ErrorMessage(e, _T("Department 를수정할수없습니다 ")); e->delete(); m_nndxmax = 0; 키값으로해당튜플을검색하여 CRecordset m_pset 이 open 되어있는상태에서 m_pset->edit() 를호출한후에 m_pset 의애트리뷰트들에값들을전달하고 m_pset->update() 를호출한다. [DeptDlg.cpp] void CDeptDlg::OnBtnUpdate() UpdateData(); try if (m_pset->isopen()) m_pset->close(); if (m_strset.isempty()) AfxMessageBox(_T("SET 절이없습니다.")); return; CString strsql = m_pset->getdefaultsql() + _T(" WHERE DNumber=") + m_strdnumber; m_pset->open(crecordset::dynaset, strsql); m_pset->movefirst(); m_pset->edit(); FetchTuple(false); m_pset->update(); m_pset->close(); AfxMessageBox(_T("Success...")); catch (CDBException* e) // arecordset <--- adlg
ErrorMessage(e, _T("Department 를수정할수없습니다 ")); e->delete(); m_nndxmax = 0; 4 단계저장압축파일 (4. InsDelUpd.zip) View에대한연산 Query를하기위하여위와같은대화상자를생성한다. 이를위하여 ⑴ 질의의결과를저장할집합클래스를생성하고, ⑵ 대화상자를디자인하고, ⑶ 테이블의튜플을 I/O하기위하여대화상자클래스를생성한다. 데이터베이스에다음과같은 View WorksOnView가정의되어있다고가정한다. CREATE VIEW WorksOnView AS SELECT FName, LName, PName, Hours FROM (Employee JOIN WorksOn ON SSN=ESSN) JOIN Project ON DNum=PNumber 바로앞에서언급한것처럼 WorksOnView에연산하기위하여 CVw1Dlg와 CVw1Set 클래스를생성해야한다. Class View Class Wizard Add Class... MFC ODBC Consumer... Data Source... 컴퓨터데이터원본 CompanyAccDB Views WorksOnView Class: CVw1Set, Vw1Set.h, Vw1Set.cpp [Vw1Set.h] CStringW CString [Vw1Set.cpp] #error 삭제 #error Security Issue: The connection string may contain a password CString CVw1Set::GetDefaultConnect() return _T("DSN=CompanyAccDB"); CString CVw1Set::GetDefaultSQL() return _T("SELECT * FROM WorksOnView"); Resource View DatabaseApp DatabaseApp.rc Dialog Insert Dialog ID: IDD_VIEW1_DLG
Comtrol ID 레이블 Hotkey Callback 함수 / 멤버변수 Static, &FName Edit, IDC_EDT_FNAME, m_strfname Static, &LName Edit, IDC_EDT_LNAME, m_strlname Static, &PName Edit, IDC_EDT_PNAME, m_strpname Static, &Hours Edit, IDC_EDT_HOURS, m_strhours Static, &Where Edit, IDC_EDT_WHERE, m_strwhere Button, IDC_BTN_QUERY, &Query, OnBtnQuery Button, IDOK, &Quit Group, ID: IDC_STC_ITER, Iteration, m_striteration Button, IDC_BTN_FIRST, &First, OnBtnFirst Button, IDC_BTN_PREV, &Prev, OnBtnPrev Button, IDC_BTN_PREV, &Next, OnBtnNext Button, IDC_BTN_LAST, &Last, OnBtnLast Check Mnemonic ;; 단축키의중복체크 Tab Order ;; Control( 버튼 ) 들의순서를지정 Tap Stop ;; Tab으로 Control들사이를이동 Group ;; Control들의그룹 [DatabaseApp.rc] IDD_VIEW1_DLG DIALOGEX 0, 0, 211, 94 STYLE DS_SETFONT DS_MODALFRAME DS_FIXEDSYS WS_POPUP WS_CAPTION WS_SYSMENU CAPTION "Employee~WorksOn~Project" FONT 8, "MS Shell Dlg", 400, 0, 0x1 BEGIN RTEXT "FN&ame:",IDC_STATIC,7,7,27,12 EDITTEXT IDC_EDT_FNAME,38,7,55,12,ES_AUTOHSCROLL RTEXT "LNa&me:",IDC_STATIC,7,23,27,12 EDITTEXT IDC_EDT_LNAME,38,23,55,12,ES_AUTOHSCROLL RTEXT "PNam&e:",IDC_STATIC,7,39,27,12 EDITTEXT IDC_EDT_PNAME,38,39,55,12,ES_AUTOHSCROLL RTEXT "&Hours:",IDC_STATIC,7,55,27,12 EDITTEXT IDC_EDT_HOURS,38,55,55,12,ES_AUTOHSCROLL RTEXT "&Where:",IDC_STATIC,7,75,27,12 EDITTEXT IDC_EDT_WHERE,38,75,166,12,ES_AUTOHSCROLL PUSHBUTTON "&Query",IDC_BTN_QUERY,103,7,56,21 PUSHBUTTON "Q&uit",IDOK,168,7,36,21 GROUPBOX "Iteration",IDC_STC_ITER,98,30,106,42,WS_GROUP PUSHBUTTON "&First",IDC_BTN_FIRST,103,44,24,21,WS_GROUP PUSHBUTTON "&Prev",IDC_BTN_PREV,127,44,24,21,NOT WS_TABSTOP PUSHBUTTON "&Next",IDC_BTN_NEXT,151,44,24,21,NOT WS_TABSTOP PUSHBUTTON "&Last",IDC_BTN_LAST,175,44,24,21,NOT WS_TABSTOP END [Resource.h] #define IDD_DATABASEAPP_DIALOG 102 #define IDR_MAINFRAME 128 #define IDD_DEPT_DLG 129 #define IDD_VIEW1_DLG 130 #define IDC_BTN_DEPT 1000
#define IDC_BTN_EMP 1001 #define IDC_EDT_DNAME 1001 #define IDC_EDT_FNAME 1001 #define IDC_BTN_PRJCT 1002 #define IDC_EDT_DNMBR 1002 #define IDC_EDT_LNAME 1002 #define IDC_BTN_WRKSN 1003 #define IDC_EDT_MGRSSN 1003 #define IDC_EDT_PNAME 1003 #define IDC_BTN_DEPTLOC 1004 #define IDC_EDT_MGRDATE 1004 #define IDC_EDT_HOURS 1004 #define IDC_BTN_DPNDNT 1005 #define IDC_EDT_WHERE 1005 #define IDC_BTN_VW1 1006 #define IDC_EDT_SET 1006 #define IDC_BTN_VW2 1007 #define IDC_BTN_QUERY 1007 #define IDC_BTN_VW3 1008 #define IDC_BTN_INSERT 1008 #define IDC_BTN_VW4 1009 #define IDC_BTN_DELETE 1009 #define IDC_BTN_UPDATE 1010 #define IDC_STC_ITER 1011 #define IDC_BTN_FIRST 1012 #define IDC_BTN_PREV 1013 #define IDC_BTN_NEXT 1014 #define IDC_BTN_LAST 1015 IDD_VIEW1_DLG Add Class... Class Name: CVw1Dlg IDD_VIEW1_DLG Class Wizard... virtual function Callback 함수멤버변수 OnInitDialog OnBtnQuery OnBtnFirst OnBtnPrev OnBtnNext OnBtnLast m_strfname m_strlname m_strpname m_strhours m_strwhere m_striteration 가상함수, callback 함수, 멤버변수를설정한결과는다음과같다. [Vw1Dlg.h] public: ; DECLARE_MESSAGE_MAP() virtual BOOL OnInitDialog(); afx_msg void OnBtnQuery(); afx_msg void OnBtnFirst(); afx_msg void OnBtnPrev(); afx_msg void OnBtnNext(); afx_msg void OnClickedBtnLast(); CString m_strfname; CString m_strlname; CString m_strpname; CString m_strhours; CString m_strwhere; CString m_striteration; [Vw1Dlg.cpp] CVw1Dlg::CVw1Dlg(CWnd* pparent /*=NULL*/) : CDialogEx(IDD_VIEW1_DLG, pparent), m_strfname(_t("")), m_strlname(_t("")), m_strpname(_t("")), m_strhours(_t("")), m_strwhere(_t("")), m_striteration(_t("")) void CVw1Dlg::DoDataExchange(CDataExchange* pdx)
CDialogEx::DoDataExchange(pDX); DDX_Text(pDX, IDC_EDT_FNAME, m_strfname); DDX_Text(pDX, IDC_EDT_LNAME, m_strlname); DDX_Text(pDX, IDC_EDT_PNAME, m_strpname); DDX_Text(pDX, IDC_EDT_HOURS, m_strhours); DDX_Text(pDX, IDC_EDT_WHERE, m_strwhere); DDX_Text(pDX, IDC_STC_ITER, m_striteration); BEGIN_MESSAGE_MAP(CVw1Dlg, CDialogEx) ON_BN_CLICKED(IDC_BTN_QUERY, &CVw1Dlg::OnBtnQuery) ON_BN_CLICKED(IDC_BTN_FIRST, &CVw1Dlg::OnBtnFirst) ON_BN_CLICKED(IDC_BTN_PREV, &CVw1Dlg::OnBtnPrev) ON_BN_CLICKED(IDC_BTN_NEXT, &CVw1Dlg::OnBtnNext) ON_BN_CLICKED(IDC_BTN_LAST, &CVw1Dlg::OnClickedBtnLast) END_MESSAGE_MAP() BOOL CVw1Dlg::OnInitDialog() CDialogEx::OnInitDialog(); // TODO: 여기에추가초기화작업을추가합니다. return TRUE; // return TRUE unless you set the focus to a control // 예외 : OCX 속성페이지는 FALSE 를반환해야합니다. void CVw1Dlg::OnBtnQuery() void CVw1Dlg::OnBtnFirst() void CVw1Dlg::OnBtnPrev() void CVw1Dlg::OnBtnNext() void CVw1Dlg::OnClickedBtnLast() 이제준비작업이완료되었으니본겨적인데이터베이스연산작업을시작한다. 먼저질의 의결과로생성된튜플들의순회하기위한 iterator 를장착한다. [Vw1Dlg.h] #include "Iterator.h" #include "Vw1Set.h" #pragma once // CDeptDlg dialog class CDeptDlg : public CDialogEx, public CIterator DECLARE_DYNAMIC(CDeptDlg) public: CDeptDlg(CWnd* pparent = NULL); virtual ~CDeptDlg(); // standard constructor
#ifdef AFX_DESIGN_TIME enum IDD = IDD_VIEW1_DLG ; #endif CVw1Set *m_pset; virtual void FetchTuple(BOOL bfetch = true); virtual CWnd *GetFirstButton(void); virtual CWnd *GetPrevButton(void); virtual CWnd *GetNextButton(void); virtual CWnd *GetLastButton(void); virtual CString &GetIterLabel(void); protected: virtual void DoDataExchange(CDataExchange* pdx); // DDX/DDV support [Vw1Dlg.cpp] CVw1Dlg::CVw1Dlg(CWnd* pparent /*=NULL*/) : CDialogEx(CVw1Dlg::IDD, pparent), m_strfname(_t("")), m_strlname(_t("")), m_strpname(_t("")), m_strhours(_t("")), m_strwhere(_t("")), m_striteration(_t("iteration")) static CVw1Set aqueryset; m_piterset = m_pset = &aqueryset; BOOL CVw1Dlg::OnInitDialog() CDialogEx::OnInitDialog(); // TODO: Add extra initialization here m_nndxmax = 0; return TRUE; // return TRUE unless you set the focus to a control // EXCEPTION: OCX Property Pages should return FALSE CWnd *CVw1Dlg::GetFirstButton(void) return GetDlgItem(IDC_BTN_FIRST); CWnd *CVw1Dlg::GetPrevButton(void) return GetDlgItem(IDC_BTN_PREV); CWnd *CVw1Dlg::GetNextButton(void) return GetDlgItem(IDC_BTN_NEXT); CWnd *CVw1Dlg::GetLastButton(void) return GetDlgItem(IDC_BTN_LAST); CString &CVw1Dlg::GetIterLabel(void) return m_striteration; void CVw1Dlg::OnBtnQuery() UpdateData(); try if (m_pset->isopen()) m_pset->close();
CString strsql = m_pset->getdefaultsql(); if (!m_strwhere.trim().isempty()) strsql += _T(" WHERE ") + m_strwhere; m_pset->open(crecordset::dynaset, strsql); while (!m_pset->iseof()) m_pset->movenext(); m_nndxmax = m_pset->getrecordcount(); FirstFetch(); UpdateData(false); catch (CDBException *e) ErrorMessage(e, _T("Query Failure")); e->delete(); m_nndxmax = 0; void CVw1Dlg::OnBtnFirst() FirstFetch(); UpdateData(false); void CVw1Dlg::OnBtnPrev() PrevFetch(); UpdateData(false); void CVw1Dlg::OnBtnNext() NextFetch(); UpdateData(false); void CVw1Dlg::OnBtnLast() LastFetch(); UpdateData(false); void CVw1Dlg::FetchTuple(BOOL bfetch) if (bfetch) m_strfname = m_pset->m_fname.trimright(); m_strlname = m_pset->m_lname.trimright(); m_strpname = m_pset->m_pname.trimright(); m_strhours.format(_t("%d"), m_pset->m_hours); [DatabaseAppDlg.cpp] #include "stdafx.h" #include "DatabaseApp.h" #include "DatabaseAppDlg.h" #include "afxdialogex.h" #include "DeptDlg.h" #include "Vw1Dlg.h" void CDatabaseAppDlg::OnBtnView1() CVw1Dlg adlg; adlg.m_pdb = m_pdb; adlg.domodal(); 5 단계저장압축파일 (5. WorksOnView.zip)
CRecordset Window Control CDialog class CRecordset database Windows Dialog의 Control( 주로 Edit) adlg.updatedata(true) adlg.updatedata(false) CDialog-derived class(cdeptdlg) 멤버변수 (adlg.m_strdname) qrset.addnew() 두클래스의대응변수들로이동 qrset.delete() (FetchTuple 함수참조 ) qrset.edit() CRecordset-derived class(cdeptset) 멤버변수 (qrset.m_dname) DoFieldExchange(CFieldExchange *pfx) qrset.update() qrset.open(ntype, strsql) Database (Disk의파일 ) 연결 : adb.open(strdsn) adb.open(null, false, false, strdsn) 변경 : adb.execute(strsql) // Insert/Delete/Update 검색 : qrset.open(ntype, strsql) // MoveFirst, Last, Prev, Next 끊기 : adb.close()