testdbdlg.cpp

连接并使用Oracle。可以创建表、预览数据集、删除、修改数据集。将这些操作封装在类里

// TestDBDlg.cpp : implementation file
//

#include "stdafx.h"
#include "TestDB.h"
#include "TestDBDlg.h"
#include "StudentSet.h"

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

extern CTestDBApp theApp;
/////////////////////////////////////////////////////////////////////////////
// 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()

/////////////////////////////////////////////////////////////////////////////
// CTestDBDlg dialog

CTestDBDlg::CTestDBDlg(CWnd* pParent /*=NULL*/)
	: CDialog(CTestDBDlg::IDD, pParent)
{
	//{{AFX_DATA_INIT(CTestDBDlg)
	m_strNo = _T("");
	m_strName = _T("");
	m_strAge = _T("");
	m_strSex = _T("");
	m_strAddress = _T("");
	m_nMode = 0;
	//}}AFX_DATA_INIT
	// Note that LoadIcon does not require a subsequent DestroyIcon in Win32
	m_hIcon = AfxGetApp()->LoadIcon(IDR_MAINFRAME);
}

void CTestDBDlg::DoDataExchange(CDataExchange* pDX)
{
	CDialog::DoDataExchange(pDX);
	//{{AFX_DATA_MAP(CTestDBDlg)
	DDX_Control(pDX, IDC_RECORD_LIST, m_ListCtl);
	DDX_Text(pDX, IDC_NO_EDIT, m_strNo);
	DDV_MaxChars(pDX, m_strNo, 5);
	DDX_Text(pDX, IDC_NAME_EDIT, m_strName);
	DDV_MaxChars(pDX, m_strName, 8);
	DDX_Text(pDX, IDC_AGE_EDIT, m_strAge);
	DDV_MaxChars(pDX, m_strAge, 2);
	DDX_Text(pDX, IDC_SEX_EDIT, m_strSex);
	DDV_MaxChars(pDX, m_strSex, 2);
	DDX_Text(pDX, IDC_ADDRESS_EDIT, m_strAddress);
	DDV_MaxChars(pDX, m_strAddress, 100);
	DDX_Radio(pDX, IDC_OCI_RADIO, m_nMode);
	//}}AFX_DATA_MAP
}

BEGIN_MESSAGE_MAP(CTestDBDlg, CDialog)
	//{{AFX_MSG_MAP(CTestDBDlg)
	ON_WM_SYSCOMMAND()
	ON_WM_PAINT()
	ON_WM_QUERYDRAGICON()
	ON_BN_CLICKED(IDC_READ_BUTTON, OnReadButton)
	ON_NOTIFY(LVN_ITEMCHANGED, IDC_RECORD_LIST, OnItemchangedRecordList)
	ON_BN_CLICKED(IDC_ADD_BUTTON, OnAddButton)
	ON_BN_CLICKED(IDC_EDIT_BUTTON, OnEditButton)
	ON_BN_CLICKED(IDC_DEL_BUTTON, OnDelButton)
	//}}AFX_MSG_MAP
END_MESSAGE_MAP()

/////////////////////////////////////////////////////////////////////////////
// CTestDBDlg message handlers

BOOL CTestDBDlg::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_ListCtl.InsertColumn(1,"学号",LVCFMT_LEFT,50);
	m_ListCtl.InsertColumn(2,"姓名",LVCFMT_LEFT,50);
	m_ListCtl.InsertColumn(3,"性别",LVCFMT_LEFT,50);
	m_ListCtl.InsertColumn(4,"年龄",LVCFMT_LEFT,50);
	m_ListCtl.InsertColumn(5,"家庭住址",LVCFMT_LEFT,100);
	m_ListCtl.SetExtendedStyle(LVS_EX_FULLROWSELECT|LVS_EX_GRIDLINES);
	
	return TRUE;  // return TRUE  unless you set the focus to a control
}

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

void CTestDBDlg::OnReadButton() 
{
	UpdateData(TRUE);
	m_ListCtl.DeleteAllItems();
	if(m_nMode==0) ReadRecordByOci();
	if(m_nMode==1) ReadRecordByOdbc();
}

void CTestDBDlg::ReadRecordByOci()
{
	cursor cr;
	if(!cr.open(&theApp.m_conn)) 
	{
		MessageBox("打开游标失败!","提示",MB_ICONINFORMATION);
		return;
	}

	CString sql;
	sql.Format("select 学号,姓名,性别,年龄,家庭住址 from 学生表2 order by 学号");

	char no[6]={0};
	char name[9]={0};
	char sex[3]={0};
	int  age=0;
	char address[101]={0};

	BOOL bSucc=TRUE;
	bSucc&=cr.parse(sql);
	bSucc&=cr.define_by_position(1,no,sizeof(no),STRING_TYPE);
	bSucc&=cr.define_by_position(2,name,sizeof(name),STRING_TYPE);
	bSucc&=cr.define_by_position(3,sex,sizeof(sex),STRING_TYPE);
	bSucc&=cr.define_by_position(4,&age,sizeof(int),INT_TYPE);
	bSucc&=cr.define_by_position(5,address,sizeof(address),STRING_TYPE);
	bSucc&=cr.execute();
	while(cr.fetch())
	{
		CString strAge;
		strAge.Format("%d",age);

		int index=m_ListCtl.GetItemCount();
		m_ListCtl.InsertItem(index,no);
		m_ListCtl.SetItemText(index,1,name);
		m_ListCtl.SetItemText(index,2,sex);
		m_ListCtl.SetItemText(index,3,strAge);
		m_ListCtl.SetItemText(index,4,address);
	}
	cr.close();
}

void CTestDBDlg::OnItemchangedRecordList(NMHDR* pNMHDR, LRESULT* pResult) 
{
	NM_LISTVIEW* pNMListView = (NM_LISTVIEW*)pNMHDR;

	int index=m_ListCtl.GetNextItem(-1,LVNI_SELECTED);
	if(index>=0)
	{
		m_strNo=m_ListCtl.GetItemText(index,0);
		m_strName=m_ListCtl.GetItemText(index,1);
		m_strSex=m_ListCtl.GetItemText(index,2);
		m_strAge=m_ListCtl.GetItemText(index,3);
		m_strAddress=m_ListCtl.GetItemText(index,4);
		UpdateData(FALSE);
	}
	
	*pResult = 0;
}

void CTestDBDlg::OnAddButton() 
{
	UpdateData(TRUE);
	if(m_nMode==0) AddRecordByOci();
	if(m_nMode==1) AddRecordByOdbc();
}

void CTestDBDlg::AddRecordByOci()
{
	UpdateData(TRUE);

	//判断学号是否输入
	if(m_strNo.IsEmpty())
	{
		MessageBox("学号不能为空!","提示",MB_ICONINFORMATION);
		GetDlgItem(IDC_NO_EDIT)->SetFocus();
		return;
	}
	//判断姓名是否输入
	if(m_strName.IsEmpty())
	{
		MessageBox("姓名不能为空!","提示",MB_ICONINFORMATION);
		GetDlgItem(IDC_NAME_EDIT)->SetFocus();
		return;
	}

	cursor cr;
	if(!cr.open(&theApp.m_conn)) return;

	CString sql;
	sql.Format("insert into 学生表2 values('%s','%s','%s','%s','%s')",
		        m_strNo,m_strName,m_strSex,m_strAge,m_strAddress);

	BOOL bSucc=TRUE;
	bSucc&=cr.parse(sql);
	bSucc&=cr.execute();
	bSucc&=cr.ExecuteSQL("commit");

	cr.close();
}

void CTestDBDlg::OnEditButton() 
{
	UpdateData(TRUE);
	if(m_nMode==0) EditRecordByOci();
	if(m_nMode==1) EditRecordByOdbc();
}

void CTestDBDlg::EditRecordByOci()
{
	int index=m_ListCtl.GetNextItem(-1,LVNI_SELECTED);
	if(index<0) return;

	UpdateData(TRUE);
	
	//判断学号是否输入
	if(m_strNo.IsEmpty())
	{
		MessageBox("学号不能为空!","提示",MB_ICONINFORMATION);
		GetDlgItem(IDC_NO_EDIT)->SetFocus();
		return;
	}
	//判断姓名是否输入
	if(m_strName.IsEmpty())
	{
		MessageBox("姓名不能为空!","提示",MB_ICONINFORMATION);
		GetDlgItem(IDC_NAME_EDIT)->SetFocus();
		return;
	}

	//取得修改前的学号
	CString strOldNo;
	strOldNo=m_ListCtl.GetItemText(index,0);

	cursor cr;
	if(!cr.open(&theApp.m_conn)) return;

	CString sql;
	sql.Format("update 学生表2 set 学号='%s',姓名='%s',性别='%s',年龄='%s',家庭住址='%s' "
			   "where 学号='%s'",m_strNo,m_strName,m_strSex,m_strAge,m_strAddress,strOldNo);

	cr.parse(sql);
	cr.execute();
	cr.ExecuteSQL("commit");

	cr.close();
}

void CTestDBDlg::OnDelButton() 
{
	UpdateData(TRUE);
	if(m_nMode==0) DelRecordByOci();
	if(m_nMode==1) DelRecordByOdbc();
}

void CTestDBDlg::DelRecordByOci()
{
	int index=m_ListCtl.GetNextItem(-1,LVNI_SELECTED);
	if(index<0) return;

	CString strNo;
	strNo=m_ListCtl.GetItemText(index,0);

	cursor cr;
	if(!cr.open(&theApp.m_conn)) return;

	CString sql;
	sql.Format("delete from 学生表2 where 学号='%s'",strNo);

	cr.parse(sql);
	cr.execute();
	cr.ExecuteSQL("commit");

	cr.close();
}

void CTestDBDlg::ReadRecordByOdbc()
{
	CStudentSet set(&theApp.m_db);

	//打开记录集
	//set.m_strFilter.Format("年龄>25");
	//set.m_strSort.Format("学号 desc");
	set.Open();
	while(!set.IsEOF())
	{
		int index=m_ListCtl.GetItemCount();
		m_ListCtl.InsertItem(index,set.m_column1);
		m_ListCtl.SetItemText(index,1,set.m_column2);
		m_ListCtl.SetItemText(index,2,set.m_column3);
		m_ListCtl.SetItemText(index,3,set.m_column4);
		m_ListCtl.SetItemText(index,4,set.m_column5);

		set.MoveNext();
	}

	set.Close();
}

void CTestDBDlg::AddRecordByOdbc()
{
	UpdateData(TRUE);
	
	//判断学号是否输入
	if(m_strNo.IsEmpty())
	{
		MessageBox("学号不能为空!","提示",MB_ICONINFORMATION);
		GetDlgItem(IDC_NO_EDIT)->SetFocus();
		return;
	}
	//判断姓名是否输入
	if(m_strName.IsEmpty())
	{
		MessageBox("姓名不能为空!","提示",MB_ICONINFORMATION);
		GetDlgItem(IDC_NAME_EDIT)->SetFocus();
		return;
	}
	
	CStudentSet set(&theApp.m_db);

	set.Open();

	set.AddNew();
	set.m_column1=m_strNo;
	set.m_column2=m_strName;
	set.m_column3=m_strSex;
	set.m_column4=m_strAge;
	set.m_column5=m_strAddress;
	set.Update();

	set.Close();
}

void CTestDBDlg::EditRecordByOdbc()
{
	int index=m_ListCtl.GetNextItem(-1,LVNI_SELECTED);
	if(index<0) return;
	
	UpdateData(TRUE);
	
	//判断学号是否输入
	if(m_strNo.IsEmpty())
	{
		MessageBox("学号不能为空!","提示",MB_ICONINFORMATION);
		GetDlgItem(IDC_NO_EDIT)->SetFocus();
		return;
	}
	//判断姓名是否输入
	if(m_strName.IsEmpty())
	{
		MessageBox("姓名不能为空!","提示",MB_ICONINFORMATION);
		GetDlgItem(IDC_NAME_EDIT)->SetFocus();
		return;
	}
	
	//取得修改前的学号
	CString strOldNo;
	strOldNo=m_ListCtl.GetItemText(index,0);
	
	CStudentSet set(&theApp.m_db);

	set.m_strFilter.Format("学号=%s",strOldNo);
	set.Open();
	if(!set.IsEOF())
	{
		set.Edit();
		set.m_column1=m_strNo;
		set.m_column2=m_strName;
		set.m_column3=m_strSex;
		set.m_column4=m_strAge;
		set.m_column5=m_strAddress;
		set.Update();
	}

	set.Close();
}

void CTestDBDlg::DelRecordByOdbc()
{
	int index=m_ListCtl.GetNextItem(-1,LVNI_SELECTED);
	if(index<0) return;
	
	CString strNo;
	strNo=m_ListCtl.GetItemText(index,0);

	CStudentSet set(&theApp.m_db);

	set.m_strFilter.Format("学号=%s",strNo);
	set.Open();
	if(!set.IsEOF())
	{
		set.Delete();
	}

	set.Close();
}