memodbsdlg.cpp

< Visual C+++SQL Server数据库开发与实例>>一书中的实例核心源程序提供了几个案例的编程源代码。

// MemoDBSDlg.cpp : implementation file
//

#include "stdafx.h"
#include "MemoDBS.h"
#include "MemoDBSDlg.h"
#include "LoginDlg.h"
#include "MemberDlg.h"
#include "MemoTypeDlg.h"
#include "MemoInfoDlg.h"
#include "BirthQueryDlg.h"
#include "MemoQueryDlg.h"
#include "EventDlg.h"

#ifdef _DEBUG
#define new DEBUG_NEW
#undef THIS_FILE
static char THIS_FILE[] = __FILE__;
#endif

/////////////////////////////////////////////////////////////////////////////
// CAboutDlg dialog used for App About

class CAboutDlg : public CDialog
{
public:
	CAboutDlg();

// Dialog Data
	//{{AFX_DATA(CAboutDlg)
	enum { IDD = IDD_ABOUTBOX };
	//}}AFX_DATA

	// ClassWizard generated virtual function overrides
	//{{AFX_VIRTUAL(CAboutDlg)
	protected:
	virtual void DoDataExchange(CDataExchange* pDX);    // DDX/DDV support
	//}}AFX_VIRTUAL

// Implementation
protected:
	//{{AFX_MSG(CAboutDlg)
	//}}AFX_MSG
	DECLARE_MESSAGE_MAP()
};

CAboutDlg::CAboutDlg() : CDialog(CAboutDlg::IDD)
{
	//{{AFX_DATA_INIT(CAboutDlg)
	//}}AFX_DATA_INIT
}

void CAboutDlg::DoDataExchange(CDataExchange* pDX)
{
	CDialog::DoDataExchange(pDX);
	//{{AFX_DATA_MAP(CAboutDlg)
	//}}AFX_DATA_MAP
}

BEGIN_MESSAGE_MAP(CAboutDlg, CDialog)
	//{{AFX_MSG_MAP(CAboutDlg)
		// No message handlers
	//}}AFX_MSG_MAP
END_MESSAGE_MAP()

/////////////////////////////////////////////////////////////////////////////
// CMemoDBSDlg dialog

CMemoDBSDlg::CMemoDBSDlg(CWnd* pParent /*=NULL*/)
	: CDialog(CMemoDBSDlg::IDD, pParent)
{
	//{{AFX_DATA_INIT(CMemoDBSDlg)
		// NOTE: the ClassWizard will add member initialization here
	//}}AFX_DATA_INIT
	// Note that LoadIcon does not require a subsequent DestroyIcon in Win32
	m_hIcon = AfxGetApp()->LoadIcon(IDR_MAINFRAME);
}

void CMemoDBSDlg::DoDataExchange(CDataExchange* pDX)
{
	CDialog::DoDataExchange(pDX);
	//{{AFX_DATA_MAP(CMemoDBSDlg)
	DDX_Control(pDX, IDC_LIST_MEMO_INFO, m_listMemoInfo);
	DDX_Control(pDX, IDC_LIST_MEMO_TYPE, m_listType);
	DDX_Control(pDX, IDC_LIST_COMPANY_MEMBER, m_listMember);
	//}}AFX_DATA_MAP
}

BEGIN_MESSAGE_MAP(CMemoDBSDlg, CDialog)
	//{{AFX_MSG_MAP(CMemoDBSDlg)
	ON_WM_SYSCOMMAND()
	ON_WM_PAINT()
	ON_WM_QUERYDRAGICON()
	ON_BN_CLICKED(IDC_BTN_ADD_MEMBER, OnBtnAddMember)
	ON_BN_CLICKED(IDC_BTN_MOD_MEMBER, OnBtnModMember)
	ON_BN_CLICKED(IDC_EXIT, OnExit)
	ON_BN_CLICKED(IDC_BTN_DEL_MEMBER, OnBtnDelMember)
	ON_BN_CLICKED(IDC_BTN_ADD_TYPE, OnBtnAddType)
	ON_BN_CLICKED(IDC_BTN_MOD_TYPE, OnBtnModType)
	ON_BN_CLICKED(IDC_BTN_DEL_TYPE, OnBtnDelType)
	ON_BN_CLICKED(IDC_BTN_ADD_MEMO, OnBtnAddMemo)
	ON_BN_CLICKED(IDC_BTN_MOD_MEMO, OnBtnModMemo)
	ON_BN_CLICKED(IDC_BTN_DEL_MEMO, OnBtnDelMemo)
	ON_BN_CLICKED(IDC_BTN_QUERY_BIRTH, OnBtnQueryBirth)
	ON_BN_CLICKED(IDC_BTN_MEMO_INFO, OnBtnMemoInfo)
	ON_BN_CLICKED(IDC_BTN_MEMO_EVENT, OnBtnMemoEvent)
	//}}AFX_MSG_MAP
END_MESSAGE_MAP()

/////////////////////////////////////////////////////////////////////////////
// CMemoDBSDlg message handlers

BOOL CMemoDBSDlg::OnInitDialog()
{
	CDialog::OnInitDialog();

	// Add "About..." menu item to system menu.

	// IDM_ABOUTBOX must be in the system command range.
	ASSERT((IDM_ABOUTBOX & 0xFFF0) == IDM_ABOUTBOX);
	ASSERT(IDM_ABOUTBOX < 0xF000);

	CMenu* pSysMenu = GetSystemMenu(FALSE);
	if (pSysMenu != NULL)
	{
		CString strAboutMenu;
		strAboutMenu.LoadString(IDS_ABOUTBOX);
		if (!strAboutMenu.IsEmpty())
		{
			pSysMenu->AppendMenu(MF_SEPARATOR);
			pSysMenu->AppendMenu(MF_STRING, IDM_ABOUTBOX, strAboutMenu);
		}
	}

	// 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
	//定义登录对话框
	CLoginDlg	dlg;
	BOOL		login = TRUE,DB_SYS_EXIT = FALSE;
	while(login){
		if(dlg.DoModal() == IDOK){
		CString strConnect;
		strConnect.Format("DSN=%s;UID=%s;PWD=%s",dlg.m_strDBSource,dlg.m_strDBUser,dlg.m_strDBPassword);
		//打开数据库的连接,并且捕获异常
		
		TRY{
			m_db.OpenEx(strConnect,CDatabase::noOdbcDialog);
			
			login=FALSE;
		}
		CATCH(CDBException,ex)
		{	
			AfxMessageBox (ex->m_strError);
			AfxMessageBox (ex->m_strStateNativeOrigin);		
		}
		AND_CATCH(CMemoryException,pEx)
		{	
			pEx->ReportError();
			AfxMessageBox ("memory exception");
		}
		AND_CATCH(CException,e)
		{	
			TCHAR szError[100];
			e->GetErrorMessage(szError,100);
			AfxMessageBox (szError);
		}
		END_CATCH	
		}
		else {
			login = FALSE;
			DB_SYS_EXIT = TRUE;
		}
	}
	if(DB_SYS_EXIT)
		CDialog::OnCancel();
	else{
		InitControl();
		InitCtrlData();
	}
	return TRUE;  // return TRUE  unless you set the focus to a control
}
void CMemoDBSDlg::InitControl()
{
	//设置列表控件扩展风格
	DWORD dwExStyle = LVS_EX_FULLROWSELECT | LVS_EX_GRIDLINES |
		LVS_EX_HEADERDRAGDROP | LVS_EX_ONECLICKACTIVATE | LVS_EX_UNDERLINEHOT;
	m_listMember.SetExtendedStyle(dwExStyle);
	m_listMemoInfo.SetExtendedStyle(dwExStyle);
	m_listType.SetExtendedStyle(dwExStyle);
	//初始化公司成员列表控件
	m_listMember.InsertColumn(0,"ID",LVCFMT_CENTER,30);
	m_listMember.InsertColumn(1,"姓名",LVCFMT_CENTER,60);
	m_listMember.InsertColumn(2,"职位",LVCFMT_CENTER,60);
	m_listMember.InsertColumn(3,"出生日期",LVCFMT_CENTER,60);
	m_listMember.InsertColumn(4,"性别",LVCFMT_CENTER,40);
	m_listMember.InsertColumn(5,"籍贯",LVCFMT_CENTER,100);
	//初始化备注类型列表控件
	m_listType.InsertColumn(0,"类型ID",LVCFMT_CENTER,60);
	m_listType.InsertColumn(1,"类型名称",LVCFMT_CENTER,100);
	//初始化备注信息列表控件
	m_listMemoInfo.InsertColumn(0,"备忘录ID",LVCFMT_CENTER,60);
	m_listMemoInfo.InsertColumn(1,"姓名",LVCFMT_CENTER,60);
	m_listMemoInfo.InsertColumn(2,"备忘录操作日期",LVCFMT_CENTER,140);
	m_listMemoInfo.InsertColumn(3,"备忘录发生日期",LVCFMT_CENTER,140);
	m_listMemoInfo.InsertColumn(4,"大事件",LVCFMT_CENTER,60);
	m_listMemoInfo.InsertColumn(5,"事件花费",LVCFMT_CENTER,60);
	m_listMemoInfo.InsertColumn(6,"备注类型",LVCFMT_CENTER,60);
	m_listMemoInfo.InsertColumn(7,"备注内容",LVCFMT_CENTER,200);
}
void CMemoDBSDlg::InsertMemberItem(int id, CString name, CString position,CString date,CString gender,CString hometown)
{
	//获取当前的显示条数
	int nIndex = m_listMember.GetItemCount();
	LV_ITEM lvItem;
	lvItem.mask = LVIF_TEXT ;	
	lvItem.iItem = nIndex;				
	lvItem.iSubItem = 0;
	CString temp ;
	temp.Format("%d",id);
	lvItem.pszText = (char*)(LPCTSTR)temp;	
	//在nIndex一行插入数据.
	m_listMember.InsertItem(&lvItem);	
	m_listMember.SetItemText(nIndex,1,name);
	m_listMember.SetItemText(nIndex,2,position);
	m_listMember.SetItemText(nIndex,3,date);
	m_listMember.SetItemText(nIndex,4,gender);
	m_listMember.SetItemText(nIndex,5,hometown);
}
void CMemoDBSDlg::InitCtrlData()
{
	TRY{
		CRecordset rs;
		rs.m_pDatabase = &m_db;
		//向成员列表控件中添加成员记录信息
		CString sql = "Select * from company_member_tab";
		//获取所有的公司成员记录集
		rs.Open(CRecordset::dynaset, sql);
		int id;
		CString name, position,date,gender, hometown;
		while (!rs.IsEOF()) {
			CDBVariant var;
			//获取成员ID字段值
		    rs.GetFieldValue((short)0, var, SQL_C_SLONG);
			if (var.m_dwType != DBVT_NULL)	
				id = var.m_iVal;
			var.Clear();
			//获取姓名字段值
			rs.GetFieldValue(1, name);
			//获取职位字段值
			rs.GetFieldValue(2, position);
			//获取出生日期字段值
			rs.GetFieldValue(3, date);
			//获取性别字段值
			rs.GetFieldValue(4, gender);
			//获取籍贯字段值
			rs.GetFieldValue(5, hometown);
			//把记录值插入到成员列表控件中.
			InsertMemberItem(id,name,position,date.Left(10),gender,hometown);
			rs.MoveNext();	
		}
		rs.Close();
		//向备忘录类型列表控件中添加备忘录信息
		sql = "Select * from MEMO_TYPE_TAB";
		//获取所有的备忘录类型记录
		rs.Open(CRecordset::dynaset, sql);
		while (!rs.IsEOF()) {
			CDBVariant var;
			//获取备忘录类型ID字段值.
			rs.GetFieldValue((short)0, var, SQL_C_SLONG);
			if (var.m_dwType != DBVT_NULL)	
				id = var.m_iVal;
			var.Clear();
			//获取备忘录类型名称字段值
			rs.GetFieldValue(1, name);
			//向备忘录类型列表控件中加入新的纪录信息.
			InsertMemoTypeItem(id,name);
			rs.MoveNext();	
		}
		rs.Close();
		//向备注列表控件中加入所有的备忘录信息
		sql = "Select * from MEMO_INFO_TAB";
		//打开所有的备忘录信息的记录.
		rs.Open(CRecordset::dynaset, sql);
		while (!rs.IsEOF()) {
			CDBVariant var;
			int memoID,memberID,isEvent,nType;
			CString happenDate,operDate,memoTxt,strName,strType,cost;
			//获取备忘录ID字段值
			rs.GetFieldValue((short)0, var, SQL_C_SLONG);
			if (var.m_dwType != DBVT_NULL)	
				memoID = var.m_iVal;
			var.Clear();
			//获取成员ID字段值
			rs.GetFieldValue(1, var, SQL_C_SLONG);
			if (var.m_dwType != DBVT_NULL)	
				memberID = var.m_iVal;
			var.Clear();
			//获取操作日期字段值
			rs.GetFieldValue(2, operDate);
			//获取发生日期字段值
			var.Clear();
			rs.GetFieldValue(3, happenDate);
			var.Clear();
			//获取是否大事记字段值
			rs.GetFieldValue(4, var, SQL_C_SLONG);
			if (var.m_dwType != DBVT_NULL)	
				isEvent = var.m_iVal;
			var.Clear();
			//获取事件的花费
			rs.GetFieldValue(5, cost);
			
			
			//获取备忘录类型ID值.
			rs.GetFieldValue(6, var, SQL_C_SLONG);
			if (var.m_dwType != DBVT_NULL)	
				nType = var.m_iVal;
			var.Clear();
			//获取备忘录内容字段值.
			rs.GetFieldValue(7, memoTxt);
			CRecordset rs2(&m_db);
			//根据成员ID获取成员的姓名
			CString temp;
			temp.Format("Select MEMBER_NAME from COMPANY_MEMBER_TAB where "
				"MEMBER_ID = %d",memberID);
			rs2.Open(CRecordset::dynaset,temp);
			if(!rs2.IsEOF()) 
				rs2.GetFieldValue((short)0, strName);
			rs2.Close();
			//根据类型ID获取类型名称.
			temp.Format("Select TYPE_NAME from MEMO_TYPE_TAB where "
				"TYPE_ID = '%d'",nType);
			rs2.Open(CRecordset::dynaset,temp);
			if(!rs2.IsEOF())
				rs2.GetFieldValue((short)0, strType);		
			rs2.Close();
			//向列表控件中加入一条新的记录信息.
						
			InsertMemoInfoItem(memoID,strName,operDate.Left(19),happenDate.Left(19),isEvent,cost,strType,memoTxt);
			rs.MoveNext();	
		}
	}
	CATCH(CDBException,ex)
	{
		AfxMessageBox (ex->m_strError);
		AfxMessageBox (ex->m_strStateNativeOrigin);		
	}
	AND_CATCH(CMemoryException,pEx)
	{
		pEx->ReportError();
		AfxMessageBox ("memory exception");
	}
	AND_CATCH(CException,e)
	{
		TCHAR szError[100];
		e->GetErrorMessage(szError,100);
		AfxMessageBox (szError);
	}
	END_CATCH   
}
void CMemoDBSDlg::InsertMemoTypeItem(int id, CString name)
{
	//获取当前的纪录条数.
	int nIndex = m_listType.GetItemCount();
	LV_ITEM lvItem;
	lvItem.mask = LVIF_TEXT ;	
	lvItem.iItem = nIndex;				
	lvItem.iSubItem = 0;
	CString temp ;
	temp.Format("%d",id);
	lvItem.pszText = (char*)(LPCTSTR)temp;		
	//在最后一行插入备注类型数据.
	m_listType.InsertItem(&lvItem);	
	m_listType.SetItemText(nIndex,1,name);
}
void CMemoDBSDlg::InsertMemoInfoItem(int id, CString name, CString operDate, CString happenDate, int isEvent, CString cost,CString type, CString memoTxt)
{
	//获取当前的纪录条数.
	int nIndex = m_listMemoInfo.GetItemCount();
	LV_ITEM lvItem;
	lvItem.mask = LVIF_TEXT ;	
	lvItem.iItem = nIndex;				//行数
	lvItem.iSubItem = 0;
	CString temp ;
	temp.Format("%d",id);
	lvItem.pszText = (char*)(LPCTSTR)temp;		//第一列
	//在最后一行插入记录值.
	m_listMemoInfo.InsertItem(&lvItem);	
	//设置该的其他列的值.
	m_listMemoInfo.SetItemText(nIndex,1,name);
	m_listMemoInfo.SetItemText(nIndex,2,operDate);	
	m_listMemoInfo.SetItemText(nIndex,3,happenDate);	
	m_listMemoInfo.SetItemText(nIndex,4,isEvent==0?"否":"是");	
	m_listMemoInfo.SetItemText(nIndex,5,cost);
	m_listMemoInfo.SetItemText(nIndex,6,type);	
	m_listMemoInfo.SetItemText(nIndex,7,memoTxt);	
}
void CMemoDBSDlg::OnSysCommand(UINT nID, LPARAM lParam)
{
	if ((nID & 0xFFF0) == IDM_ABOUTBOX)
	{
		CAboutDlg dlgAbout;
		dlgAbout.DoModal();
	}
	else
	{
		CDialog::OnSysCommand(nID, lParam);
	}
}

// If you add a minimize button to your dialog, you will need the code below
//  to draw the icon.  For MFC applications using the document/view model,
//  this is automatically done for you by the framework.

void CMemoDBSDlg::OnPaint() 
{
	if (IsIconic())
	{
		CPaintDC dc(this); // device context for painting

		SendMessage(WM_ICONERASEBKGND, (WPARAM) dc.GetSafeHdc(), 0);

		// Center icon in client rectangle
		int cxIcon = GetSystemMetrics(SM_CXICON);
		int cyIcon = GetSystemMetrics(SM_CYICON);
		CRect rect;
		GetClientRect(&rect);
		int x = (rect.Width() - cxIcon + 1) / 2;
		int y = (rect.Height() - cyIcon + 1) / 2;

		// Draw the icon
		dc.DrawIcon(x, y, m_hIcon);
	}
	else
	{
		CDialog::OnPaint();
	}
}

// The system calls this to obtain the cursor to display while the user drags
//  the minimized window.
HCURSOR CMemoDBSDlg::OnQueryDragIcon()
{
	return (HCURSOR) m_hIcon;
}

void CMemoDBSDlg::OnBtnAddMember() 
{
	// TODO: Add your control notification handler code here
	//创建一个成员对话框实例
	CMemberDlg dlg;
	//打开成员对话框
	if(dlg.DoModal() == IDOK){
		//从对话框中获取姓名和生日参数.
		CString strName = dlg.m_strName;
		CString strPosition = dlg.m_strPosition;
		CString strBirth = dlg.m_dtBirth.Format("%Y-%m-%d");
		CString strGender = dlg.m_strGender;
		CString strHometown = dlg.m_strHometown;
		TRY{
			//打开记录集,获取最大的成员ID值.
			CRecordset rs(&m_db);
			rs.Open(CRecordset::dynaset, 
				"Select max(member_id) from company_member_tab");
			//设置新添加记录的成员ID值.
			int newMemberID = 1;
			//如果数据库里面已经有记录了,则新的成员ID是成员ID最大值+1
			if(!rs.IsEOF()) {
				CDBVariant var;
				rs.GetFieldValue((short)0, var, SQL_C_SLONG);
				if (var.m_dwType != DBVT_NULL)	
					newMemberID = var.m_iVal + 1;
			}
			//创建插入新记录的字符串.
			CString sql ;
			sql.Format("Insert into company_member_tab("
				"member_name,member_position,birth_date,member_gender,member_hometown) "
				"VALUES("
				" '%s','%s','%s','%s','%s')",strName,strPosition,strBirth,strGender,strHometown);
			TRACE(sql);
			
			//把新记录插入到数据库中.
			m_db.ExecuteSQL(sql);	
			//把新记录的信息显示在成员列表控件中.
			InsertMemberItem(newMemberID,strName,strPosition,strBirth,strGender,strHometown);
		}
		CATCH(CDBException,ex)
		{
			AfxMessageBox (ex->m_strError);
			AfxMessageBox (ex->m_strStateNativeOrigin);		
		}
		AND_CATCH(CException,e)
		{
			TCHAR szError[100];
			e->GetErrorMessage(szError,100);
			AfxMessageBox (szError);
		}
		END_CATCH
	}
}

void CMemoDBSDlg::OnBtnModMember() 
{
	// TODO: Add your control notification handler code here
	//选择要修改的记录项.
	int nItem = m_listMember.GetNextItem(-1, LVNI_SELECTED);
	if(nItem == -1){
		AfxMessageBox("没有选择要修改的成员");
		return;