080426dbdialogdlg.cpp

一个很好的用ADO操作数据库的例子

// 080426dbdialogDlg.cpp : implementation file
//

#include "stdafx.h"
#include "080426dbdialog.h"
#include "080426dbdialogDlg.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()

/////////////////////////////////////////////////////////////////////////////
// CMy080426dbdialogDlg dialog

CMy080426dbdialogDlg::CMy080426dbdialogDlg(CWnd* pParent /*=NULL*/)
	: CDialog(CMy080426dbdialogDlg::IDD, pParent)
{
	//{{AFX_DATA_INIT(CMy080426dbdialogDlg)
	m_name = _T("");
	m_stuNO = _T("");
	m_chinese = _T("");
	m_math = _T("");
	m_english = _T("");
	m_timer = _T("");
	//}}AFX_DATA_INIT
	// Note that LoadIcon does not require a subsequent DestroyIcon in Win32
	m_hIcon = AfxGetApp()->LoadIcon(IDR_MAINFRAME);
    nUpdate=0;
}

void CMy080426dbdialogDlg::DoDataExchange(CDataExchange* pDX)
{
	CDialog::DoDataExchange(pDX);
	//{{AFX_DATA_MAP(CMy080426dbdialogDlg)
	DDX_Control(pDX, IDC_COMBO1, m_comboNO);
	DDX_Control(pDX, IDC_LIST1, m_ListCtrl);
	DDX_Text(pDX, IDC_EDIT1, m_name);
	DDV_MaxChars(pDX, m_name, 20);
	DDX_CBString(pDX, IDC_COMBO1, m_stuNO);
	DDV_MaxChars(pDX, m_stuNO, 20);
	DDX_Text(pDX, IDC_EDIT2, m_chinese);
	DDX_Text(pDX, IDC_EDIT3, m_math);
	DDX_Text(pDX, IDC_EDIT4, m_english);
	DDX_Text(pDX, IDC_EDIT5, m_timer);
	//}}AFX_DATA_MAP
}

BEGIN_MESSAGE_MAP(CMy080426dbdialogDlg, CDialog)
	//{{AFX_MSG_MAP(CMy080426dbdialogDlg)
	ON_WM_SYSCOMMAND()
	ON_WM_PAINT()
	ON_WM_QUERYDRAGICON()
	ON_WM_DESTROY()
	ON_BN_CLICKED(IDC_ADD, OnAdd)
	ON_BN_CLICKED(IDC_DELETE, OnDelete)
	ON_BN_CLICKED(IDC_BTOK, OnBtok)
	ON_BN_CLICKED(IDC_UPDATE, OnUpdate)
	ON_WM_TIMER()
	//}}AFX_MSG_MAP
END_MESSAGE_MAP()

/////////////////////////////////////////////////////////////////////////////
// CMy080426dbdialogDlg message handlers

BOOL CMy080426dbdialogDlg::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
	SetTimer(1,1000,NULL);
	
	// TODO: Add extra initialization here
	CoInitialize(NULL);
	CString combo;
	for(int i=1;i<51;i++)
	{
	combo.Format("2015451%02d",i);
    m_comboNO.InsertString(i-1,combo);
	}
	m_comboNO.SetCurSel(0);
	CString sql;
	try
	{
		m_pConnection.CreateInstance(__uuidof(Connection));
		sql.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=student.mdb");
		_bstr_t sql_=sql;
		m_pConnection->Open(sql_,"","",-1);
	}
    catch(_com_error& e)
	{
		AfxMessageBox(e.ErrorMessage());
		this->EndDialog(0);
	}
	CString header[7]={"学号","姓名","语文","数学","英语","平均分","时间"};
	for(i=0;i<7;i++)
	{
	m_ListCtrl.InsertColumn(i,header[i],LVCFMT_CENTER,70);
	}
    m_ListCtrl.SetExtendedStyle(LVS_EX_FULLROWSELECT|LVS_LIST);
	_bstr_t str1("select * from students");
     i=0;
	_variant_t temp;
	//CString strchange;
	try{
	m_pRecordset.CreateInstance(__uuidof(Recordset));
	m_pRecordset = m_pConnection->Execute(str1,NULL,adCmdText);
    m_pRecordset->MoveFirst();
	
	while(!m_pRecordset->adoEOF)//在一些常用的功能上，MFC一定会有很简单的方法的~
		{
			temp = m_pRecordset->GetCollect("number");
			if(temp.vt != VT_NULL)
			   m_ListCtrl.InsertItem(i,(char*)(_bstr_t)temp);
			temp =m_pRecordset->GetCollect("name");
			if(temp.vt != VT_NULL)
				m_ListCtrl.SetItemText(i,1,(char*)(_bstr_t)temp);
			temp =m_pRecordset->GetCollect("chinexe");
			if(temp.vt != VT_NULL)  
				m_ListCtrl.SetItemText(i,2,(char*)(_bstr_t)temp);
			temp =m_pRecordset->GetCollect("math");
			if(temp.vt != VT_NULL)
            m_ListCtrl.SetItemText(i,3,(char*)(_bstr_t)temp);
			
			temp =m_pRecordset->GetCollect("english");
			if(temp.vt != VT_NULL)
				m_ListCtrl.SetItemText(i,4,(char*)(_bstr_t)temp);
			temp =m_pRecordset->GetCollect("average");
			if(temp.vt!=VT_NULL)
				m_ListCtrl.SetItemText(i,5,(char*)(_bstr_t)temp);
			temp =m_pRecordset->GetCollect("timer");
			if(temp.vt!=VT_NULL)
				m_ListCtrl.SetItemText(i,6,(char*)(_bstr_t)temp);


			
			   
			   //strchange.Format("%f",temp2);
			 
			   
			   
			m_pRecordset->MoveNext();
	}
       m_pRecordset->Close();
	}
    catch(_com_error& e)
	{
		 AfxMessageBox(e.ErrorMessage());
	}
	m_ListCtrl.SetRedraw(TRUE);


	


	return TRUE;  // return TRUE  unless you set the focus to a control
}

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

void CMy080426dbdialogDlg::OnDestroy() 
{
	CDialog::OnDestroy();
	m_pConnection->Close();
	CoUninitialize();
	
	// TODO: Add your message handler code here
	
}

void CMy080426dbdialogDlg::OnAdd() 
{
	// TODO: Add your control notification handler code here
	UpdateData(true);
//	_CommandPtr m_pCommand;
	//m_pCommand.CreateInstance(__uuidof(Command));
	_variant_t T1,T2,T3;
	float t1,t2,t3,m_Average;
	
	T1 = m_chinese;
	t1=(float)T1;
	T2 = m_math;
	t2=(float)T2;
	T3 = m_english;
	t3=(float)T3;
	m_Average =t1+t2;
	m_Average =m_Average +t3;
	m_Average = m_Average/3;
	CString strItemText1;
    CString strItemText2;
	int nCount = m_ListCtrl.GetItemCount();
	nIndex = nCount;
 if(nCount>=0)
 {
 GetDlgItem(IDC_DELETE)->EnableWindow(true);
 }
 for(int i=0;i<nCount;i++)
 {
 strItemText1 = m_ListCtrl.GetItemText(i,0);
 strItemText2 = m_ListCtrl.GetItemText(i,1);
 if(strItemText1==m_stuNO||strItemText2==m_name)
 {
 MessageBox("the item have been inserted!");
 return;
 }
 }
	if(""==m_stuNO||""==m_name||t1<0.0||t1>100.0||t2<0.0||t2>100.0||t3<0.0||t3>100.0)
	{
	MessageBox("请你按格式输入你的学名，姓名，和各科合理分数！");
	return;
	}
	else{
	CString sql;
    /*_variant_t T1,T2,T3;
	T1 = m_chinese;
	T2 = m_math;
	T3 = m_english;*/
	 //sql_.Format("SELECT * FROM students");
      // sql_.Format("INSERT INTO students (number,name,chinexe,math,english)\
	   // VALUES('%s','%s',%ld,%ld,%ld)",m_stuNO,m_name,(long)T1,(long)T2,(long)T3);
		//	_bstr_t sql = sql_;
			 _variant_t  t;
			 t=m_stuNO;
	try{        m_pRecordset.CreateInstance(__uuidof(Recordset));
		        	//m_pConnection.CreateInstance(__uuidof(Connection));
		        sql.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=student.mdb");
		        _bstr_t sql_=sql;
		        //m_pConnection->Open(sql_,"","",-1);  
				
				m_pRecordset->Open("SELECT* FROM students",sql_,adOpenStatic,adLockOptimistic,adCmdText);
				m_pRecordset->AddNew();
				t=m_stuNO;
				m_pRecordset->PutCollect("number",&t);
				//nUpdate = m_pRecordset->Update();
				t=m_name;
                m_pRecordset->PutCollect("name",&t);
				t=m_chinese;
				m_pRecordset->PutCollect("chinexe",&t);
				t=m_math;
				m_pRecordset->PutCollect("math",&t);
				t=m_english;
				m_pRecordset->PutCollect("english",&t);
				t=m_Average;
				m_pRecordset->PutCollect("average",&t);
			    t=m_timer;
				m_pRecordset->PutCollect("timer",&t);
				nUpdate = m_pRecordset->UpdateBatch(adAffectCurrent);
			    
			    //m_pConnection->Execute(sql,NULL,adCmdText);
				//m_pRecordset->Open(sql,"","",adCmdText);
				//m_pRecordset->MoveFirst();
			}
			catch(_com_error& e)
			{   MessageBox("插入失败！");
				AfxMessageBox(e.ErrorMessage());
				return;
			} 
			
			m_pRecordset->Close();
			MessageBox("插入数据成功！");
			//if(nUpdate!=0)
			CMy080426dbdialogDlg::Refresh();
			//CMy080426dbdialogDlg::OnInitDialog();
			
			
	}
	
}

void CMy080426dbdialogDlg::OnDelete() 
{
	// TODO: Add your control notification handler code here
	UpdateData(true);
	CString sql,sqldelete, selectID;
	 sql.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=student.mdb");
	 
		        _bstr_t sql_=sql;

	int nCount = m_ListCtrl.GetSelectionMark();
	
	if(nCount==-1)
	{
	MessageBox("please choose a item!");
	
	return;
	}
	else{
     selectID = m_ListCtrl.GetItemText(nCount,0);
	 sqldelete.Format("SELECT* FROM students WHERE number = '%s'",selectID);
	 m_pRecordset->Open((_bstr_t)sqldelete,sql_,adOpenDynamic,adLockOptimistic,adCmdUnspecified);
	 m_ListCtrl.DeleteItem(nCount);
	 m_pRecordset->Delete(adAffectCurrent);
	 //m_pRecordset->MoveNext();
	 m_pRecordset->Update();
	 m_pRecordset->MoveNext();
	 m_ListCtrl.SetSelectionMark(-1);
     }
	nIndex--;
	if(nIndex==0)
		GetDlgItem(IDC_DELETE)->EnableWindow(false);
    m_pRecordset->Close();
    //Refresh();
	UpdateData(false);
	
}

void CMy080426dbdialogDlg::OnBtok() 
{
	// TODO: Add your control notification handler code here
	OnOK();
}

void CMy080426dbdialogDlg::Refresh()
{         
	      //UpdateData(true);
	      m_ListCtrl.DeleteAllItems();
	      m_ListCtrl.SetRedraw(FALSE);
	       _bstr_t SQL("SELECT * FROM students");
	      _RecordsetPtr Ptr;
	      int i=0;
	      try
		  {  
		     Ptr.CreateInstance(__uuidof(Recordset));//这一个时候应该确保数据库没有被打开；不然将出错！
		     Ptr = m_pConnection->Execute(SQL,NULL,adCmdText);
			 //Ptr->Update();
			 
		     _variant_t roader;
		     while(!Ptr->adoEOF)
			 {  
                roader = Ptr->GetCollect("number");
			    if(roader.vt != VT_NULL)
				  m_ListCtrl.InsertItem(i,(char*)(_bstr_t)roader);
			    roader = Ptr->GetCollect("name");
			    if(roader.vt != VT_NULL)
				  m_ListCtrl.SetItemText(i,1,(char*)(_bstr_t)roader);
                roader = Ptr->GetCollect("chinexe");
			    if(roader.vt != VT_NULL)
				  m_ListCtrl.SetItemText(i,2,(char*)(_bstr_t)roader);
				 roader = Ptr->GetCollect("math");
				 if(roader.vt != VT_NULL)
                 m_ListCtrl.SetItemText(i,3,(char*)(_bstr_t)roader);
				 roader = Ptr->GetCollect("english");
				 if(roader.vt != VT_NULL)
				 m_ListCtrl.SetItemText(i,4,(char*)(_bstr_t)roader);
				  roader = Ptr->GetCollect("average");
				 if(roader.vt != VT_NULL)
				 m_ListCtrl.SetItemText(i,5,(char*)(_bstr_t)roader);
				  roader = Ptr->GetCollect("timer");
				 if(roader.vt != VT_NULL)
				 m_ListCtrl.SetItemText(i,6,(char*)(_bstr_t)roader);
				 Ptr->MoveNext();
			    
			 }
		  }
          catch(_com_error& e)
		  {  //MessageBox("yid");
       	     AfxMessageBox(e.ErrorMessage());
		     m_ListCtrl.SetRedraw(TRUE);	
		     return;
		  } 
		  m_ListCtrl.SetRedraw(TRUE);
		  UpdateData(false);
		  Ptr->Close();

}      

void CMy080426dbdialogDlg::OnUpdate() 
{
	// TODO: Add your control notification handler code here
     CMy080426dbdialogDlg::Refresh();
}

void CMy080426dbdialogDlg::OnTimer(UINT nIDEvent) 
{
	// TODO: Add your message handler code here and/or call default
	//CString str;
	CTime time;
	time = CTime::GetCurrentTime();
	m_timer = time.Format("%H:%M:%S");
	SetDlgItemText(IDC_EDIT5,m_timer);
	//UpdateData(false);
	CDialog::OnTimer(nIDEvent);
}