odbcdlg.cpp

VC++一个数据库的开发

// ODBCDlg.cpp : implementation file
//

#include "stdafx.h"
#include "ODBC.h"
#include "ODBCDlg.h"


#include <afxtempl.h>

#include "StuRs.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()

/////////////////////////////////////////////////////////////////////////////
// CODBCDlg dialog

CODBCDlg::CODBCDlg(CWnd* pParent /*=NULL*/)
	: CDialog(CODBCDlg::IDD, pParent)
{
	//{{AFX_DATA_INIT(CODBCDlg)
	m_strStuID = _T("");
	m_strStuName = _T("");
	m_iMath = 0;
	m_iChinese = 0;
	m_iEnglish = 0;
	//}}AFX_DATA_INIT
	// Note that LoadIcon does not require a subsequent DestroyIcon in Win32
	m_hIcon = AfxGetApp()->LoadIcon(IDR_MAINFRAME);
}

void CODBCDlg::DoDataExchange(CDataExchange* pDX)
{
	CDialog::DoDataExchange(pDX);
	//{{AFX_DATA_MAP(CODBCDlg)
	DDX_Control(pDX, IDC_LIST, m_ctlList);
	DDX_Text(pDX, IDC_STUID, m_strStuID);
	DDV_MaxChars(pDX, m_strStuID, 8);
	DDX_Text(pDX, IDC_STUNAME, m_strStuName);
	DDV_MaxChars(pDX, m_strStuName, 8);
	DDX_Text(pDX, IDC_MATH, m_iMath);
	DDV_MinMaxUInt(pDX, m_iMath, 0, 100);
	DDX_Text(pDX, IDC_CHINESE, m_iChinese);
	DDV_MinMaxUInt(pDX, m_iChinese, 0, 100);
	DDX_Text(pDX, IDC_ENGLISH, m_iEnglish);
	DDV_MinMaxUInt(pDX, m_iEnglish, 0, 100);
	//}}AFX_DATA_MAP
}

BEGIN_MESSAGE_MAP(CODBCDlg, CDialog)
	//{{AFX_MSG_MAP(CODBCDlg)
	ON_WM_SYSCOMMAND()
	ON_WM_PAINT()
	ON_WM_QUERYDRAGICON()
	ON_BN_CLICKED(IDB_CONNECT, OnConnect)
	ON_BN_CLICKED(IDB_DELETE, OnDelete)
	ON_BN_CLICKED(IDC_SAVE, OnSave)
	ON_BN_CLICKED(IDB_NEW, OnNew)
	ON_BN_CLICKED(IDB_EDIT, OnEdit)
	//}}AFX_MSG_MAP
END_MESSAGE_MAP()

/////////////////////////////////////////////////////////////////////////////
// CODBCDlg message handlers

BOOL CODBCDlg::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
	m_ctlList.InsertColumn(0,_T("学号"),LVCFMT_LEFT,120);
	m_ctlList.InsertColumn(1,_T("姓名"),LVCFMT_LEFT,80);
	m_ctlList.InsertColumn(2,_T("语文"),LVCFMT_LEFT,80);
	m_ctlList.InsertColumn(3,_T("数学"),LVCFMT_LEFT,80);
	m_ctlList.InsertColumn(4,_T("英语"),LVCFMT_LEFT,80);
	m_ctlList.InsertColumn(8,_T("总计"),LVCFMT_LEFT,100);

	//m_ctlList.SendMessage( LVM_SETEXTENDEDLISTVIEWSTYLE, 0,LVS_EX_FULLROWSELECT );
	m_ctlList.SetExtendedStyle(LVS_EX_GRIDLINES|LVS_EX_FULLROWSELECT);//|LVS_EX_FLATSB|LVS_REPORT|LVS_EX_HEADERDRAGDROP|LVS_EX_INFOTIP);
	return TRUE;  // return TRUE  unless you set the focus to a control
}

void CODBCDlg::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 CODBCDlg::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 CODBCDlg::OnQueryDragIcon()
{
	return (HCURSOR) m_hIcon;
}

void CODBCDlg::OnCancel()
{
	if(m_dbStu.IsOpen())
	{
		m_dbStu.Close();
	}
	CDialog::OnCancel();
}
void CODBCDlg::OnConnect() 
{
	if(!m_dbStu.IsOpen())
	{
		
		BOOL br=m_dbStu.Open(_T("stu_scores"),FALSE,FALSE,_T("ODBC;UID=sa;PWD=111111"));
		if(!br)
		{
			AfxMessageBox(_T("打开数据库失败!"));			
		}
		else
		{
			ShowData();
		}
	}
	else
	{
		AfxMessageBox(_T("数据库已经打开!"));
	}	
}

void CODBCDlg::ShowData()
{
	if(!m_dbStu.IsOpen())
		return;

	CRecordset rs;
	rs.m_pDatabase=&m_dbStu;
	CString strSql=_T("select * from scores");
	rs.Open(CRecordset::forwardOnly,strSql);
	while(!rs.IsEOF())
	{
		CDBVariant var;
		int index=m_ctlList.InsertItem(0,_T(""));
		for (int i=0;i<6;i++)
		{
			rs.GetFieldValue(i,var);
			CString str;
			switch(var.m_dwType)
			{
			case DBVT_STRING:
				str=*var.m_pstring;
				break;
			case DBVT_LONG:
				str.Format(_T("%d"),var.m_lVal);
				break;
			default:
				str=_T("?");
				break;
			}
			
			
			m_ctlList.SetItemText(index,i,str);
		}
		rs.MoveNext();
	}
	rs.Close();
}

void CODBCDlg::OnDelete() 
{
	// TODO: Add your control notification handler code here
	int index=m_ctlList.GetSelectionMark();
	if(index!=-1)
	{
		CString str_id=m_ctlList.GetItemText(index,0);

		if(!m_dbStu.IsOpen())
			return;
		else
		{
			CString msg=_T("你真的要删除学号为:")+str_id+_T("的学生成绩记录吗?");
			if(IDYES==AfxMessageBox(msg,MB_YESNO|MB_ICONSTOP))
			{
				CString strSql=_T("delete from scores where stu_id='"+str_id+"'");
				
				TRY 
				{					
						m_dbStu.ExecuteSQL(strSql);					
				}
				CATCH (CDBException,err)
				{
					TCHAR err_msg[256];
					err->GetErrorMessage(err_msg,256);
					CString msg;
					msg.Format(_T("删除失败：\n%s"),err_msg);
					AfxMessageBox(msg);
					return;
				}
				END_CATCH
				m_ctlList.DeleteItem(index);
			}
		}
		
	}
	else
	{
		AfxMessageBox(_T("没有选中任何数据记录!"));
	}	
}

void CODBCDlg::OnSave() 
{
	if(!m_dbStu.IsOpen())
		return;

	//添加新数据
	if(m_bIsNewData)
	{
		UpdateData(TRUE);
	
		CString strFormat=_T("insert into scores(stu_id,stu_name,Chinese,Math,English,total) \
			values('%s','%s',%d,%d,%d,%d)");
		CString strSql;
		UINT total=m_iChinese+m_iMath+m_iEnglish;
		strSql.Format(strFormat,m_strStuID,m_strStuName,m_iChinese,m_iMath,m_iEnglish,total);
		TRY 
		{
			m_dbStu.ExecuteSQL(strSql);
		}
		CATCH (CDBException,err)
		{
			TCHAR err_msg[256];
			err->GetErrorMessage(err_msg,256);
			CString msg;
			msg.Format(_T("添加数据失败：\n%s"),err_msg);
			AfxMessageBox(msg);
			return;
		}
		END_CATCH

		//显示添加的数据
		int index=m_ctlList.InsertItem(0,_T(""));
		CString text;
		m_ctlList.SetItemText(index,0,m_strStuID);
		m_ctlList.SetItemText(index,1,m_strStuName);

		text.Format(_T("%d"),m_iChinese);
		m_ctlList.SetItemText(index,2,text);
		text.Format(_T("%d"),m_iMath);
		m_ctlList.SetItemText(index,3,text);
		text.Format(_T("%d"),m_iEnglish);
		m_ctlList.SetItemText(index,4,text);
		text.Format(_T("%d"),total);
		m_ctlList.SetItemText(index,5,text);
	}
	//修改已有数据
	else
	{
		UpdateData(TRUE);
	
		CString strFormat=_T("update scores set stu_name='%s',Chinese=%d,Math=%d,English=%d,total=%d where stu_id=%s");
		CString strSql;
		UINT total=m_iChinese+m_iMath+m_iEnglish;
		strSql.Format(strFormat,m_strStuName,m_iChinese,m_iMath,m_iEnglish,total,m_strStuID);
		TRY 
		{
			m_dbStu.ExecuteSQL(strSql);
		}
		CATCH (CDBException,err)
		{
			TCHAR err_msg[256];
			err->GetErrorMessage(err_msg,256);
			CString msg;
			msg.Format(_T("修改数据失败：\n%s"),err_msg);
			AfxMessageBox(msg);
			return;
		}
		END_CATCH

		//显示修改的数据
		LVFINDINFO lv;
		lv.flags=LVFI_STRING;
		lv.psz=m_strStuID;
		int index=m_ctlList.FindItem(&lv);
		CString text;
		if(index>=0)
		{
			m_ctlList.SetItemText(index,1,m_strStuName);
			
			text.Format(_T("%d"),m_iChinese);
			m_ctlList.SetItemText(index,2,text);
			
			text.Format(_T("%d"),m_iMath);
			m_ctlList.SetItemText(index,3,text);
			text.Format(_T("%d"),m_iEnglish);
			m_ctlList.SetItemText(index,4,text);
			text.Format(_T("%d"),total);
			m_ctlList.SetItemText(index,5,text);
		}
		
	}
}

void CODBCDlg::OnNew() 
{
	m_bIsNewData=TRUE;
	
	m_strStuID=_T("");
	m_strStuName=_T("");
	m_iMath=0;
	m_iChinese=0;
	m_iEnglish=0;
	
	this->GetDlgItem(IDC_STUID)->EnableWindow(TRUE);
	UpdateData(FALSE);
}

void CODBCDlg::OnEdit() 
{
	int index=m_ctlList.GetSelectionMark();
	if(index!=-1)
	{
		m_bIsNewData=FALSE;	
		m_strStuID=m_ctlList.GetItemText(index,0);
		m_strStuName=m_ctlList.GetItemText(index,1);
		
		m_iChinese=atoi(m_ctlList.GetItemText(index,2));
		m_iMath=atoi(m_ctlList.GetItemText(index,3));
		m_iEnglish=atoi(m_ctlList.GetItemText(index,4));
		
		this->GetDlgItem(IDC_STUID)->EnableWindow(FALSE);
		
		UpdateData(FALSE);
	}
	else
	{
		AfxMessageBox(_T("没有选中任何数据记录!"));
	}
}