daodemoview.cpp

DAO访问SQL数据库

// DAODemoView.cpp : implementation of the CDAODemoView class
//

#include "stdafx.h"
#include "DAODemo.h"

#include "DAODemoDoc.h"
#include "DAODemoView.h"
#include "crack.h"
#include "RoomDlg.h"
#include "StuffDlg.h"
#include "CateDlg.h"


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

/////////////////////////////////////////////////////////////////////////////
// CDAODemoView

IMPLEMENT_DYNCREATE(CDAODemoView, CListView)

BEGIN_MESSAGE_MAP(CDAODemoView, CListView)
	//{{AFX_MSG_MAP(CDAODemoView)
	ON_WM_DESTROY()
	ON_COMMAND(ID_STUFF_VIEW, OnStuffView)
	ON_COMMAND(ID_STUFF_DELETE, OnStuffDelete)
	ON_COMMAND(ID_STUFF_REGISTER, OnStuffRegister)
	ON_COMMAND(ID_ROOM_DELETE, OnRoomDelete)
	ON_COMMAND(ID_ROOM_REGISTER, OnRoomRegister)
	ON_COMMAND(ID_ROOM_VIEW, OnRoomView)
	ON_COMMAND(ID_CATE_DELETE, OnCateDelete)
	ON_COMMAND(ID_CATE_REGISTER, OnCateRegister)
	ON_COMMAND(ID_CATE_VIEW, OnCateView)
	ON_COMMAND(ID_STATIC_CATE, OnStaticCate)
	ON_COMMAND(ID_STATIC_ROOM, OnStaticRoom)
	//}}AFX_MSG_MAP
	// Standard printing commands
	ON_COMMAND(ID_FILE_PRINT, CListView::OnFilePrint)
	ON_COMMAND(ID_FILE_PRINT_DIRECT, CListView::OnFilePrint)
	ON_COMMAND(ID_FILE_PRINT_PREVIEW, CListView::OnFilePrintPreview)
END_MESSAGE_MAP()

/////////////////////////////////////////////////////////////////////////////
// CDAODemoView construction/destruction

CDAODemoView::CDAODemoView()
{
	// TODO: add construction code here

}

CDAODemoView::~CDAODemoView()
{
}

BOOL CDAODemoView::PreCreateWindow(CREATESTRUCT& cs)
{
	// TODO: Modify the Window class or styles here by modifying
	//  the CREATESTRUCT cs
	cs.style |= LVS_REPORT;

	return CListView::PreCreateWindow(cs);
}

/////////////////////////////////////////////////////////////////////////////
// CDAODemoView drawing

void CDAODemoView::OnDraw(CDC* pDC)
{
	CDAODemoDoc* pDoc = GetDocument();
	ASSERT_VALID(pDoc);
	// TODO: add draw code for native data here
}

void CDAODemoView::OnInitialUpdate()
{
	CListView::OnInitialUpdate();

	CListCtrl& ctrlList = (CListCtrl&) GetListCtrl();
	ctrlList.SetExtendedStyle(LVS_EX_FULLROWSELECT); 
	

	// TODO: You may populate your ListView with items by directly accessing
	//  its list control through a call to GetListCtrl().
	m_pDatabase = new CDaoDatabase;
	try
	{
		m_pDatabase->Open("Assist.mdb");
		m_pRecordset = new CDaoRecordset(m_pDatabase);
	}
	catch (CDaoException* e)
	{
		e->ReportError();
		delete m_pDatabase;
		m_pDatabase = NULL;
		e->Delete();
		return;
	}
}

/////////////////////////////////////////////////////////////////////////////
// CDAODemoView printing

BOOL CDAODemoView::OnPreparePrinting(CPrintInfo* pInfo)
{
	// default preparation
	return DoPreparePrinting(pInfo);
}

void CDAODemoView::OnBeginPrinting(CDC* /*pDC*/, CPrintInfo* /*pInfo*/)
{
	// TODO: add extra initialization before printing
}

void CDAODemoView::OnEndPrinting(CDC* /*pDC*/, CPrintInfo* /*pInfo*/)
{
	// TODO: add cleanup after printing
}

/////////////////////////////////////////////////////////////////////////////
// CDAODemoView diagnostics

#ifdef _DEBUG
void CDAODemoView::AssertValid() const
{
	CListView::AssertValid();
}

void CDAODemoView::Dump(CDumpContext& dc) const
{
	CListView::Dump(dc);
}

CDAODemoDoc* CDAODemoView::GetDocument() // non-debug version is inline
{
	ASSERT(m_pDocument->IsKindOf(RUNTIME_CLASS(CDAODemoDoc)));
	return (CDAODemoDoc*)m_pDocument;
}
#endif //_DEBUG

/////////////////////////////////////////////////////////////////////////////
// CDAODemoView message handlers

void CDAODemoView::OnDestroy() 
{
	CListView::OnDestroy();
	
	// TODO: Add your message handler code here
	if(m_pRecordset){
		if (m_pRecordset->IsOpen()) m_pRecordset->Close();
		delete m_pRecordset;
	}

	if(m_pDatabase){
		if (m_pDatabase->IsOpen())	m_pDatabase->Close();
		delete m_pDatabase;	
	}
}

void CDAODemoView::OnStuffView() 
{
	// TODO: Add your command handler code here
	m_strTableName = _T("物品");
	if(!m_pDatabase->IsOpen()) return;
	if(!m_pRecordset) return;
	if(m_pRecordset->IsOpen()) m_pRecordset->Close();
	
	CListCtrl& ctlList = (CListCtrl&)GetListCtrl();
	EraseList();
	// get table information
	CDaoFieldInfo fieldInfo;
	int nFields;

	CDaoTableDef td(m_pDatabase);
	try
	{
		td.Open(m_strTableName);
		nFields = td.GetFieldCount();
		for (int j=0; j < nFields; j++)
		{
			td.GetFieldInfo(j,fieldInfo);
			int nWidth = ctlList.GetStringWidth(fieldInfo.m_strName) + 15;
			ctlList.InsertColumn(j,fieldInfo.m_strName, LVCFMT_LEFT, nWidth);
		}
	}
	catch (CDaoException* e)
	{
		e->ReportError(); 
		e->Delete();
		return;
	}
	td.Close();

	int nItem = 0;
	try
	{
		CString strSelect(_T("Select * From ["));
		strSelect += m_strTableName;
		strSelect += _T("]");
		m_pRecordset->Open(dbOpenDynaset,strSelect);
		while (!m_pRecordset->IsEOF()) {
			COleVariant var;
			var = m_pRecordset->GetFieldValue(0);
			ctlList.InsertItem(nItem,CCrack::strVARIANT(var));
			for (int i=0; i < nFields; i++)
			{
				var = m_pRecordset->GetFieldValue(i);
				ctlList.SetItemText( nItem,i,CCrack::strVARIANT(var));
			}
			nItem++;
			m_pRecordset->MoveNext();
		}
	}
	catch (CDaoException* e)
	{
		e->ReportError(); 
		e->Delete();
		return;
	}

	CString strRecCount;
	strRecCount.Format(_T("显示了%d条记录。"),nItem);
	UpdateWindow();
	if (nItem>=MAXRECORDS)	MessageBox(strRecCount);
	((CFrameWnd *) AfxGetMainWnd())->SetMessageText(strRecCount);	
}

void CDAODemoView::OnStuffDelete() 
{
	// TODO: Add your command handler code here
	m_strTableName = _T("物品");
	if(!m_pDatabase->IsOpen()) return;
	if(!m_pRecordset) return;
	if(m_pRecordset->IsOpen()) m_pRecordset->Close();
	CListCtrl& ctlList = (CListCtrl&)GetListCtrl();
	UINT i, uSelectedCount = ctlList.GetSelectedCount();
	int  nItemSel = -1;	
	if (uSelectedCount > 0)
	{
		// get selected item
		for (i=0;i < uSelectedCount;i++)
			nItemSel = ctlList.GetNextItem(nItemSel, LVNI_SELECTED);
		CString strID;
		strID = ctlList.GetItemText(nItemSel, 0);
		CString strSQL;
		strSQL.Format("delete from %s where 物品ID=%s",m_strTableName, strID);
		try{
			m_pDatabase->Execute(strSQL);
		}
		catch (CDaoException* e)
		{
			e->ReportError(); 
			e->Delete();
			return;
		}
		ctlList.DeleteItem(nItemSel);
		UpdateWindow();
	}
}

void CDAODemoView::OnStuffRegister() 
{
	// TODO: Add your command handler code here
	if(!m_pDatabase->IsOpen()) return;
	if(!m_pRecordset) return;
	if(m_pRecordset->IsOpen()) m_pRecordset->Close();
	CString strSelect;
	CStuffDlg StuffDlg;
	// set stuff dialog data
	try
	{
		// cate
		m_strTableName = _T("类别");
		strSelect = _T("Select 类别ID,类别名称 From [");
		strSelect += m_strTableName;
		strSelect += _T("]");
		m_pRecordset->Open(dbOpenDynaset,strSelect);
		while (!m_pRecordset->IsEOF()) {
			COleVariant var;
			var = m_pRecordset->GetFieldValue(0);
			StuffDlg.m_uaCate.Add(var.lVal);
			var = m_pRecordset->GetFieldValue(1);
			StuffDlg.m_saCate.Add(CCrack::strVARIANT(var));
			m_pRecordset->MoveNext();
		}
		// close recordset
		m_pRecordset->Close();
		// room
		m_strTableName = _T("房间");
		strSelect = _T("Select 房间ID,房间名称 From [");
		strSelect += m_strTableName;
		strSelect += _T("]");
		m_pRecordset->Open(dbOpenDynaset,strSelect);
		while (!m_pRecordset->IsEOF()) {
			COleVariant var;
			var = m_pRecordset->GetFieldValue(0);
			StuffDlg.m_uaRoom.Add(var.lVal);
			var = m_pRecordset->GetFieldValue(1);
			StuffDlg.m_saRoom.Add(CCrack::strVARIANT(var));
			m_pRecordset->MoveNext();
		}
	}
	catch (CDaoException* e)
	{
		e->ReportError(); 
		e->Delete();
		return;
	}

	if(IDOK != StuffDlg.DoModal()) return; 
	// add stuff to database
	CString	strStuffDesc = StuffDlg.m_strStuffDesc;
	CString	strMaker = StuffDlg.m_strMaker;
	CString	strModel = StuffDlg.m_strModel;
	CString	strModelNum = StuffDlg.m_strModelNum;
	CString	strSerialNo = StuffDlg.m_strSerialNo;
	CString	strDate = StuffDlg.m_strDate;
	CString	strPlace = StuffDlg.m_strPlace;
	CString	strPrice = StuffDlg.m_strPrice;
	CString	strAPrice = StuffDlg.m_strAPrice;
	BOOL	bChkInsurance = StuffDlg.m_bChkInsurance;
	CString	strStuffMem = StuffDlg.m_strStuffMem;

	int nCateID = StuffDlg.m_nCateID;
	int nRoomID = StuffDlg.m_nRoomID;
	int nPrice =  atoi(strPrice);
	int nAPrice = atoi(strAPrice);
	
	// insert record to the "物品" table
	CString strSql;
	strSql.Format("insert into 物品(类别ID,房间ID,物品描述,制造商,模型,模型号码,\
		序列号,购买日期,购买地方,单价,折扣,是否保险,备注) \
		values(%d,%d,'%s','%s','%s','%s','%s','%s','%s',%d,%d,%d,'%s')",
		nCateID,nRoomID,strStuffDesc,strMaker,strModel,strModelNum,strSerialNo,
		strDate,strPlace,nPrice,nAPrice,
		bChkInsurance,strStuffMem);

	try{
	if(m_pDatabase->CanUpdate())
		m_pDatabase->Execute(strSql, dbDenyWrite|dbConsistent);
	}
	catch(CDaoException* e){
		e->ReportError();
		e->Delete();
		return;
	}
	// refresh listview
	OnStuffView();