adlg.cpp

用ADO操作数据库的方法步骤源代码,含详细说明

// ADlg.cpp : implementation file
//

#include "stdafx.h"
#include "A.h"
#include "ADlg.h"

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



#define CATCH_ERROR												\
			catch (_com_error e)								\
			{													\
				CString strComError;							\
				strComError.Format("错误编号: %08lx\r\n\r\n错误信息: %s\r\n\r\n错误源: %s\r\n\r\n错误描述: %s", \
									e.Error(),                  \
									e.ErrorMessage(),           \
									(LPCSTR) e.Source(),        \
									(LPCSTR) e.Description());  \
				SetDlgItemText(IDC_MESSAGE,strComError);		\
			}
#define SET_TEXT SetDlgItemText(IDC_MESSAGE,"!\r\n\r\n\r\n\r\n\t\t操作成功完成!");


CADlg::CADlg(CWnd* pParent /*=NULL*/)
	: CDialog(CADlg::IDD, pParent)
{
	//{{AFX_DATA_INIT(CADlg)
		// NOTE: the ClassWizard will add member initialization here
	//}}AFX_DATA_INIT
	// Note that LoadIcon does not require a subsequent DestroyIcon in Win32
	m_hIcon = AfxGetApp()->LoadIcon(IDR_MAINFRAME);
}

CADlg::~CADlg()
{
	m_pConnection->Close();
	m_pConnection.Release();
}

void CADlg::DoDataExchange(CDataExchange* pDX)
{
	CDialog::DoDataExchange(pDX);
	//{{AFX_DATA_MAP(CADlg)
		// NOTE: the ClassWizard will add DDX and DDV calls here
	//}}AFX_DATA_MAP
}

BEGIN_MESSAGE_MAP(CADlg, CDialog)
	//{{AFX_MSG_MAP(CADlg)
	ON_BN_CLICKED(IDC_CREATET_TABLE, OnCreatetTable)
	ON_BN_CLICKED(IDC_DROP_TABLE, OnDropTable)
	ON_BN_CLICKED(IDC_ADD_ITEM, OnAddItem)
	ON_BN_CLICKED(IDC_DEL_ITEM, OnDelItem)
	ON_BN_CLICKED(IDC_ADD_RECORDS, OnAddRecords)
	ON_BN_CLICKED(IDC_OLD_ADD1, OnOldAdd1)
	ON_BN_CLICKED(IDC_SUM, OnSum)
	ON_BN_CLICKED(IDC_SET_ID_INDEX, OnSetIdIndex)
	ON_BN_CLICKED(IDC_OLD_SUM, OnOldSum)
	ON_BN_CLICKED(IDC_OLD_SUM2, OnOldSum2)
	ON_BN_CLICKED(IDC_OLD_AVERAGE, OnOldAverage)
	//}}AFX_MSG_MAP
END_MESSAGE_MAP()

/////////////////////////////////////////////////////////////////////////////
// CADlg message handlers

BOOL CADlg::OnInitDialog()
{
	CDialog::OnInitDialog();

	m_font.CreateFont(14, 0,0,0,FW_NORMAL, 0,0,0,
		DEFAULT_CHARSET, OUT_CHARACTER_PRECIS, CLIP_CHARACTER_PRECIS,
		DEFAULT_QUALITY, DEFAULT_PITCH | FF_DONTCARE, "宋体");

	GetDlgItem(IDC_MESSAGE)->SetFont(&m_font);

	
	try
	{
		m_pConnection.CreateInstance("ADODB.Connection");//创建Connection对象
		m_pConnection->Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=test.mdb","","",adModeUnknown);///连接数据库
	}
	CATCH_ERROR;
	
	
	return TRUE;  // return TRUE  unless you set the focus to a control
}



void CADlg::OnCreatetTable() 
{
	try
	{		
		m_pConnection->Execute("CREATE TABLE new(ID INTEGER,username TEXT,old INTEGER)",NULL,adCmdText);
		SET_TEXT;
	}
	CATCH_ERROR;
	

}

void CADlg::OnDropTable() 
{	
	try
	{
		m_pConnection->Execute("DROP TABLE new",NULL,adCmdText);
		SET_TEXT;
	}
	CATCH_ERROR;


}

void CADlg::OnAddItem() 
{
	try
	{		
		m_pConnection->Execute("ALTER TABLE new ADD newcolumn1 INTEGER",NULL,adCmdText); 
		SET_TEXT;
	}
	CATCH_ERROR;
}

void CADlg::OnDelItem() 
{
	try
	{
		m_pConnection->Execute("ALTER TABLE new ADD newcolumn1 INTEGER", NULL,adCmdText); 
		SET_TEXT;
	}
	CATCH_ERROR;
	
}

void CADlg::OnAddRecords() 
{
	try
	{
		for(int i = 1;i < 10; i ++)
		{
			CString strSQL;
			strSQL.Format("INSERT INTO new(ID,username,old) VALUES (%d, '程红秀',%d)",i,i*3);
			m_pConnection->Execute((_bstr_t)strSQL,NULL,adCmdText); 
		
			SET_TEXT;			
		}
	}
		CATCH_ERROR;
}

void CADlg::OnOldAdd1() 
{	
	try
	{
		m_pConnection->Execute("UPDATE new SET old = old+1", NULL,adCmdText); 
		SET_TEXT;
	}
	CATCH_ERROR;
}

void CADlg::OnSum() 
{
	try
	{
		_RecordsetPtr m_pRecordset;
		_variant_t RecordsAffected;
		
		m_pRecordset =m_pConnection->Execute("SELECT COUNT(*) FROM new where ID > 0",&RecordsAffected,adCmdText);
		
		_variant_t vCount = m_pRecordset->GetCollect((_variant_t)(long)0); 	//取得第一个字段的值放入vCount变量
		
		
		CString str;
		str.Format("!\r\n\r\n\r\n\t\t\t共有%d条记录",vCount.lVal);
		SetDlgItemText(IDC_MESSAGE,str);
		
		m_pRecordset->Close();	 
	}
	CATCH_ERROR;
	
}

void CADlg::OnSetIdIndex() 
{
	try
	{
		m_pConnection->Execute("CREATE UNIQUE INDEX id ON new(ID)",NULL,adCmdText); 
		SET_TEXT;
	}
	CATCH_ERROR;
	
}

void CADlg::OnOldSum() 
{
	try
	{
		_RecordsetPtr m_pRecordset;
		_variant_t RecordsAffected;
		m_pRecordset =m_pConnection->Execute("select MAX(old) from new",&RecordsAffected,adCmdText);
	 
		_variant_t vCount = m_pRecordset->GetCollect((_variant_t)(long)0);
		

		m_pRecordset->Close();
		m_pRecordset.Release();

		CString Message;
		Message.Format("!\r\n\r\n\r\n\t\t\t最大值是%d",vCount.lVal);
		SetDlgItemText(IDC_MESSAGE,Message);
	}
	CATCH_ERROR;	
}

void CADlg::OnOldSum2() 
{
	try
	{
		_RecordsetPtr m_pRecordset;
		_variant_t RecordsAffected;
	
		m_pRecordset =m_pConnection->Execute("select SUM(old) from new",&RecordsAffected,adCmdText);
		_variant_t vIndex = (long)0;
		_variant_t vCount = m_pRecordset->GetCollect(vIndex);
	

		m_pRecordset->Close();
		m_pRecordset.Release();
		CString Message;
		Message.Format("!\r\n\r\n\r\n\t\t\t总和是%d",(long)vCount);
		SetDlgItemText(IDC_MESSAGE,Message);
	}
	CATCH_ERROR;
}


void CADlg::OnOldAverage() 
{
	
	try
	{
		_RecordsetPtr m_pRecordset;
		_variant_t RecordsAffected;
		m_pRecordset =m_pConnection->Execute("select AVG(old) from new",&RecordsAffected,adCmdText);
		_variant_t vIndex = (long)0;
		_variant_t vCount = m_pRecordset->GetCollect(vIndex);
	

		m_pRecordset->Close();
		m_pRecordset.Release();
		CString Message;
		Message.Format("!\r\n\r\n\r\n\t\t\t平均值是%d",(long)vCount);
		SetDlgItemText(IDC_MESSAGE,Message);
		 
	}
	CATCH_ERROR;
}