数据库作业view.cpp

用vc实现的连接sql数据库程序

// 数据库作业View.cpp : implementation of the CMyView class
//

#include "stdafx.h"
#include "数据库作业.h"
#include "StudentInfomation.h"
#include "StudentClassR.h"
#include "ClassInfomation.h"
#include "数据库作业Set.h"
#include "数据库作业Doc.h"
#include "数据库作业View.h"

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

/////////////////////////////////////////////////////////////////////////////
// CMyView

IMPLEMENT_DYNCREATE(CMyView, CRecordView)

BEGIN_MESSAGE_MAP(CMyView, CRecordView)
	//{{AFX_MSG_MAP(CMyView)
	ON_BN_CLICKED(IDC_BUTTON1, OnGetSQL)
	ON_NOTIFY(LVN_COLUMNCLICK, IDC_LIST_REPORT, OnColumnclickListReport)
	ON_BN_CLICKED(IDC_BUTTON2, OnQueryStudent)
	ON_BN_CLICKED(IDC_BUTTON3, OnQueryClssInfo)
	ON_BN_CLICKED(IDC_BUTTON4, OnQuerySCInfo)
	ON_BN_CLICKED(IDC_BUTTON5, OnInsertStudent)
	ON_BN_CLICKED(IDC_BUTTON6, OnInsertClass)
	ON_BN_CLICKED(IDC_BUTTON7, OnInsertSC)
	ON_COMMAND(ID_POPMENU_ADD, OnPopmenuAdd)
	ON_COMMAND(ID_POPMENU_DEL, OnPopmenuDel)
	ON_COMMAND(ID_POPMENU_EDIT, OnPopmenuEdit)
	ON_NOTIFY(NM_RCLICK, IDC_LIST_REPORT, OnRclickListReport)
	ON_UPDATE_COMMAND_UI(ID_POPMENU_DEL, OnUpdatePopmenuDel)
	ON_UPDATE_COMMAND_UI(ID_POPMENU_EDIT, OnUpdatePopmenuEdit)
	ON_BN_CLICKED(IDC_BUTTON8, OnDeleteSQLText)
	//}}AFX_MSG_MAP
	// Standard printing commands
	ON_COMMAND(ID_FILE_PRINT, CRecordView::OnFilePrint)
	ON_COMMAND(ID_FILE_PRINT_DIRECT, CRecordView::OnFilePrint)
	ON_COMMAND(ID_FILE_PRINT_PREVIEW, CRecordView::OnFilePrintPreview)
END_MESSAGE_MAP()

/////////////////////////////////////////////////////////////////////////////
// CMyView construction/destruction

CMyView::CMyView()
	: CRecordView(CMyView::IDD)
{
	//{{AFX_DATA_INIT(CMyView)
	m_pSet = NULL;
	m_strSQL = _T("");
	nColums = 0;
	m_nTableFlags = -1;
	m_bUICanDel = TRUE;
	m_bUICanEdit = TRUE;
	//}}AFX_DATA_INIT
	// TODO: add construction code here

}

CMyView::~CMyView()
{
}

void CMyView::DoDataExchange(CDataExchange* pDX)
{
	CRecordView::DoDataExchange(pDX);
	//{{AFX_DATA_MAP(CMyView)
	DDX_Control(pDX, IDC_LIST_REPORT, m_ResultList);
	DDX_Text(pDX, IDC_EDIT_SQL, m_strSQL);
	//}}AFX_DATA_MAP
}

BOOL CMyView::PreCreateWindow(CREATESTRUCT& cs)
{
	// TODO: Modify the Window class or styles here by modifying
	//  the CREATESTRUCT cs

	return CRecordView::PreCreateWindow(cs);
}

void CMyView::OnInitialUpdate()
{
	m_pSet = &GetDocument()->m_mySet;
	CRecordView::OnInitialUpdate();
	GetParentFrame()->RecalcLayout();
	ResizeParentToFit();
	m_ResultList.SetExtendedStyle(LVS_EX_FULLROWSELECT);
	//m_ResultList.SetFont();

}

/////////////////////////////////////////////////////////////////////////////
// CMyView printing

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

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

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

/////////////////////////////////////////////////////////////////////////////
// CMyView diagnostics

#ifdef _DEBUG
void CMyView::AssertValid() const
{
	CRecordView::AssertValid();
}

void CMyView::Dump(CDumpContext& dc) const
{
	CRecordView::Dump(dc);
}

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

/////////////////////////////////////////////////////////////////////////////
// CMyView database support
CRecordset* CMyView::OnGetRecordset()
{	
	return m_pSet;
}


/////////////////////////////////////////////////////////////////////////////
// CMyView message handlers

void CMyView::OnGetSQL()
{
	// TODO: Add your control notification handler code here
	m_pSet->Close();
	CString strSQL;
	GetDlgItemText(IDC_EDIT_SQL,strSQL);
	ExecuteSQL(strSQL);
	m_pSet->Close();
	m_nTableFlags = -1;
}



void CMyView::ShowDate()
{
	m_ResultList.DeleteAllItems();   //初始化列表控件
	while(m_ResultList.DeleteColumn(0));   //删除所有列
	CArray<CString,CString>sFieldName;
	ASSERT(m_pSet->IsOpen());
	if(m_pSet->IsOpen())
	{ 
		nColums = m_pSet->GetODBCFieldCount();  //获得属性列个数
		CODBCFieldInfo odbcinfo;
		for(int j = 0; j<nColums; j++)
		{	
			m_pSet->GetODBCFieldInfo(j,odbcinfo);   //获得属性列名称
			m_ResultList.InsertColumn(j,odbcinfo.m_strName,LVCFMT_LEFT,100);
			sFieldName.Add(odbcinfo.m_strName);
		}
		int i = 0;
		while (!m_pSet->IsEOF())
		{
			CString temp = "";
			m_ResultList.InsertItem(i,temp);
			for (int n = 0; n<sFieldName.GetSize(); n++)
			{
				m_pSet->GetFieldValue(n,temp);  //获得数据保存在temp中
				m_ResultList.SetItemText(i,n,temp);
			}
			i++;
			m_pSet->MoveNext();
		}
		const CString strTableName = m_pSet->GetTableName();
		SetDlgItemText(IDC_EDIT1,strTableName);
		CString temp;
		temp.Format("%d",m_pSet->GetRecordCount());
		SetDlgItemText(IDC_EDIT6,temp);

	}
}

void CMyView::OnColumnclickListReport(NMHDR* pNMHDR, LRESULT* pResult) 
{
	NM_LISTVIEW* pNMListView = (NM_LISTVIEW*)pNMHDR;
	// TODO: Add your control notification handler code here
	*pResult = 0;
}

void CMyView::OnQueryStudent() 
{
	// TODO: Add your control notification handler code here
	m_pSet->Close();
	ExecuteSQL("SELECT * FROM S");
	//ShowDate();
	m_nTableFlags = 1;
	m_pSet->Close();
}

void CMyView::OnQueryClssInfo() 
{
	// TODO: Add your control notification handler code here
	m_pSet->Close();
	ExecuteSQL("SELECT * FROM C");
	//ShowDate();
	m_nTableFlags = 2;
	m_pSet->Close();
}

void CMyView::OnQuerySCInfo() 
{
	// TODO: Add your control notification handler code here
	m_pSet->Close();
	ExecuteSQL("SELECT * FROM SC");
	//ShowDate();
	m_nTableFlags = 3;
	m_pSet->Close();
}

void CMyView::OnInsertStudent() 
{
	// TODO: Add your control notification handler code here
	CStudentInfomation dlg;
	studentinfo data;
	if(dlg.DoModal()==IDOK)
	{
		if (dlg.GetDateStatus())
		{
			data = dlg.GetDate();
		}
		CString SQL;
		SQL.Format("INSERT INTO S VALUES('%s','%s',%d,'%s','%s')",
				data.no,data.name,data.age,data.sex,data.dept);
		ExecuteSQL(SQL);
		OnQueryStudent();
	}
}

void CMyView::OnInsertClass() 
{
	// TODO: Add your control notification handler code here
	CClassInfomation dlg;
	classinfo data;
	if(dlg.DoModal()==IDOK)
	{
		if (dlg.GetDateStatus())
		{
			data = dlg.GetDate();
		}
		CString SQL;
		SQL.Format("INSERT INTO C VALUES(%d,'%s',%d,%d)",
				data.no,data.name,data.prve,data.cedit);
		ExecuteSQL(SQL);
		OnQueryClssInfo();
	}
}

void CMyView::OnInsertSC() 
{
	// TODO: Add your control notification handler code here
	CStudentClassR dlg;
	scinfo data;
	if(dlg.DoModal()==IDOK)
	{
		if (dlg.GetDateStatus())
		{
			data = dlg.GetDate();
		}
		CString SQL;
		SQL.Format("INSERT INTO SC VALUES('%s',%d,%d)",data.sno,data.cno,data.grade);
		ExecuteSQL(SQL);
		OnQuerySCInfo();
	}
}

void CMyView::ExecuteSQL(CString strSQL)
{
	CString sTemp;
	sTemp = strSQL.Left(strSQL.Find(" ",0));
	if(sTemp.CompareNoCase("SELECT")==0)
	{
		m_pSet->Open(CRecordset::dynaset,strSQL);   //以dynaset方式打开查询语句
		ShowDate();
	}
	else
	{
		try
		{
			m_pSet->m_pDatabase->ExecuteSQL(strSQL);    //CDataBase执行SQL,方便执行修改删除操作
		}
		catch (CDBException e)
		{
			AfxMessageBox(e.m_strError,MB_OK | MB_ICONSTOP);
			e.Delete();
		}
	}
}

void CMyView::OnPopmenuAdd() 
{
	// TODO: Add your command handler code here
	switch(m_nTableFlags)
	{
	case 1:OnInsertStudent();
		break;
	case 2:OnInsertClass();
	    break;
	case 3:OnInsertSC();
	    break;
	default:
	    break;
	}
}

void CMyView::OnPopmenuDel() 
{
	// TODO: Add your command handler code here
	if (AfxMessageBox(_T("纪录删除后将无法恢复，确定删除？"),MB_OKCANCEL|MB_ICONEXCLAMATION)==IDCANCEL)
		return;
	CString temp;
	CString SQL;
	POSITION ps = m_ResultList.GetFirstSelectedItemPosition();
	int select = m_ResultList.GetNextSelectedItem(ps);
	switch(m_nTableFlags)
	{
	case 1:
		{
			temp = m_ResultList.GetItemText(select,0);
			SQL.Format("DELETE * FROM S WHERE SNO='%s'",temp);
			ExecuteSQL(SQL);
			m_ResultList.DeleteItem(select);
			m_ResultList.UpdateWindow();
		}
		break;
	case 2:
		{
			temp = m_ResultList.GetItemText(select,0);
			SQL.Format("DELETE * FROM C WHERE CNO=%d",atoi(temp));
			ExecuteSQL(SQL);
			m_ResultList.DeleteItem(select);
			m_ResultList.UpdateWindow();
		}
		break;
	case 3:
		{
			temp = m_ResultList.GetItemText(select,0);
			CString tempA;
			tempA = m_ResultList.GetItemText(select,1);
			SQL.Format("DELETE * FROM SC WHERE S_NO='%s' AND C_NO=%d",temp,atoi(tempA));
			ExecuteSQL(SQL);
			m_ResultList.DeleteItem(select);
			m_ResultList.UpdateWindow();
		}
	    break;
	default:
	    break;
	}
}

void CMyView::OnPopmenuEdit() 
{
	// TODO: Add your command handler code here
	CString temp;
	CString SQL;
	POSITION ps = m_ResultList.GetFirstSelectedItemPosition();
	int select = m_ResultList.GetNextSelectedItem(ps);
	switch(m_nTableFlags)
	{
	case 1:
		{
			temp = m_ResultList.GetItemText(select,0);
			CString tempA;
			tempA = m_ResultList.GetItemText(select,1);
			CString tempB;
			tempB = m_ResultList.GetItemText(select,2);
			CString tempC;
			tempC = m_ResultList.GetItemText(select,3);
			CString tempD;
			tempD = m_ResultList.GetItemText(select,4);
			studentinfo data;
			data.no = temp;
			data.name = tempA;
			data.age = atoi(tempB);
			data.sex = tempC;
			data.dept = tempD;
			CStudentInfomation dlg;
			dlg.SetDate(data);
			dlg.m_sWindowTitle = _T("修改课程信息");
			if (dlg.DoModal()==IDOK)
			{
				if (dlg.GetDateStatus())
				{
					data = dlg.GetDate();
				}
				CString SQL;
				SQL.Format("UPDATE S SET SNO='%s',SNAME='%s',SAGE=%d,SSEX='%s',SDEPT='%s' WHERE SNO='%s'",
								data.no,data.name,data.age,data.sex,data.dept,temp);
				ExecuteSQL(SQL);
				OnQueryStudent();
			}
		}
		break;
	case 2:
		{
			temp = m_ResultList.GetItemText(select,0);
			CString tempA;
			tempA = m_ResultList.GetItemText(select,1);
			CString tempB;
			tempB = m_ResultList.GetItemText(select,2);
			CString tempC;
			tempC = m_ResultList.GetItemText(select,3);
			classinfo data;
			data.no = atoi(temp);
			data.name = tempA;
			data.prve = atoi(tempB);
			data.cedit = atoi(tempC);
			CClassInfomation dlg;
			dlg.SetDate(data);
			dlg.m_sWindowTitle = _T("修改课程信息");
			if (dlg.DoModal()==IDOK)
			{
				if (dlg.GetDateStatus())
				{
					data = dlg.GetDate();
				}
				CString SQL;
				SQL.Format("UPDATE C SET CNO=%d,CNAME='%s',CPRVE=%d,CCREDIT=%d WHERE CNO=%d",
								data.no,data.name,data.prve,data.cedit,atoi(temp));
				ExecuteSQL(SQL);
				OnQueryClssInfo();
			}
		}
		break;
	case 3:
		{

			temp = m_ResultList.GetItemText(select,0);
			CString tempA;
			tempA = m_ResultList.GetItemText(select,1);
			CString tempB;
			tempB = m_ResultList.GetItemText(select,2);
			scinfo data;
			data.cno = atoi(tempA);
			data.sno = temp;
			data.grade = atoi(tempB);
			CStudentClassR dlg;
			dlg.SetDate(data);
			dlg.m_sWindowTitle = _T("修改学生信息");
			if (dlg.DoModal()==IDOK)
			{
				if (dlg.GetDateStatus())
				{
					data = dlg.GetDate();
				}
				CString SQL;
				SQL.Format("UPDATE SC SET S_NO='%s',C_NO=%d,GRADE=%d WHERE S_NO='%s' AND C_NO=%d",
								data.sno,data.cno,data.grade,temp,atoi(tempA));
				ExecuteSQL(SQL);
				OnQuerySCInfo();
			}
		}
	    break;
	default:
	    break;
	}
}

void CMyView::OnRclickListReport(NMHDR* pNMHDR, LRESULT* pResult) 
{
	// TODO: Add your control notification handler code here
	if(m_nTableFlags!=-1)
	{
		if (m_ResultList.GetSelectedCount()==0)
		{
			m_bUICanEdit = m_bUICanDel = FALSE;
		}
		else
		{
			m_bUICanEdit = m_bUICanDel = TRUE;
		}
		CMenu menu;
		CMenu *popMneu;
		menu.LoadMenu(IDR_MENU1);
		popMneu = menu.GetSubMenu(0);
		CPoint pt;
		GetCursorPos(&pt);
		popMneu->TrackPopupMenu(TPM_LEFTALIGN|TPM_RIGHTBUTTON,pt.x,pt.y,this);
	}
	*pResult = 0;
}

void CMyView::OnUpdatePopmenuDel(CCmdUI* pCmdUI) 
{
	// TODO: Add your command update UI handler code here
	pCmdUI->Enable(m_bUICanDel);
}

void CMyView::OnUpdatePopmenuEdit(CCmdUI* pCmdUI) 
{
	// TODO: Add your command update UI handler code here
	pCmdUI->Enable(m_bUICanEdit);
}

void CMyView::OnDeleteSQLText() 
{
	// TODO: Add your control notification handler code here
	SetDlgItemText(IDC_EDIT_SQL,_T(""));
}