daodemoview.cpp

DAO访问SQL数据库

}

void CDAODemoView::OnRoomDelete() 
{
	// 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();
		}
		ctlList.DeleteItem(nItemSel);
		UpdateWindow();
	}
}

void CDAODemoView::OnRoomRegister() 
{
	// TODO: Add your command handler code here
	CRoomDlg RoomDlg;
	if(IDOK != RoomDlg.DoModal()) return; 
	// add room to database
	CString strRoomName = RoomDlg.m_strRoomName; 
	CString strSql;
	strSql.Format("insert into 房间(房间名称) values('%s')",
		strRoomName);

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

void CDAODemoView::OnRoomView() 
{
	// 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();
	}
	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::OnCateDelete() 
{
	// 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();
		}
		ctlList.DeleteItem(nItemSel);
		UpdateWindow();
	}
}

void CDAODemoView::OnCateRegister() 
{
	// TODO: Add your command handler code here
	CCateDlg CateDlg;
	if(IDOK != CateDlg.DoModal()) return; 
	// add stuff to database
	CString strCateName = CateDlg.m_strCateName; 
	CString strSql;
	strSql.Format("insert into 类别(类别名称) values('%s')",
		strCateName);

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

void CDAODemoView::OnCateView() 
{
	// 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();
	}
	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::EraseList()
{
	CListCtrl& ctlList = (CListCtrl&) GetListCtrl();
	ctlList.DeleteAllItems();
	while(ctlList.DeleteColumn(0));
	UpdateWindow();
}

void CDAODemoView::OnStaticCate() 
{
	// TODO: Add your command handler code here
	if(!m_pDatabase->IsOpen()) return;
	if(!m_pRecordset) return;
	if(m_pRecordset->IsOpen()) m_pRecordset->Close();
	CListCtrl& ctlList = (CListCtrl&)GetListCtrl();
	EraseList();
	// add column
	CString strColName;
	int nWidth;
	strColName = _T("类别名称");
	nWidth = ctlList.GetStringWidth(strColName) + 15;
	ctlList.InsertColumn(0, strColName, LVCFMT_LEFT, nWidth);
	strColName = _T("金额总计");
	nWidth = ctlList.GetStringWidth(strColName) + 15;
	ctlList.InsertColumn(1, strColName, LVCFMT_LEFT, nWidth);
	
	// get data
	int nItem = 0;
	try
	{
		CString strSelect;
		strSelect = _T("SELECT DISTINCTROW 类别.类别名称, Sum(物品.单价) AS [总额] \
			FROM 类别 RIGHT JOIN 物品 ON 类别.类别ID = 物品.类别ID \
			GROUP BY 类别.类别名称");
		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 < 2; 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;
	}
}

void CDAODemoView::OnStaticRoom() 
{
	// TODO: Add your command handler code here
	if(!m_pDatabase->IsOpen()) return;
	if(!m_pRecordset) return;
	if(m_pRecordset->IsOpen()) m_pRecordset->Close();
	CListCtrl& ctlList = (CListCtrl&)GetListCtrl();
	EraseList();
	// add column
	CString strColName;
	int nWidth;
	strColName = _T("房间名称");
	nWidth = ctlList.GetStringWidth(strColName) + 15;
	ctlList.InsertColumn(0, strColName, LVCFMT_LEFT, nWidth);
	strColName = _T("金额总计");
	nWidth = ctlList.GetStringWidth(strColName) + 15;
	ctlList.InsertColumn(1, strColName, LVCFMT_LEFT, nWidth);
	
	// get data
	int nItem = 0;
	try
	{
		CString strSelect;
		strSelect = _T("SELECT DISTINCTROW 房间.房间名称, Sum(物品.单价) AS [总额] \
       			        FROM 房间 RIGHT JOIN 物品 ON 房间.房间ID = 物品.房间ID \
			            GROUP BY 房间.房间名称");
		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 < 2; 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;
	}
}