C++ CDatabase::Open方法代码示例

void main(void)

{
	// open with datasource name.
	MyDB.Open("WoofBase");
	
	// creation of the record set must happen AFTER the Database open!
	CMyRecordset	MyRecordset("SELECT Text1,Key1,Number1 FROM WOOFTABLE WHERE Number1 = 2",
								&MyDB);


	// open as a static snapshot
	MyRecordset.Open(CRecordset::snapshot);

	while(!MyRecordset.IsEOF())
	{
		MyRecordset.PrintRecord();
		MyRecordset.MoveNext();
	}

	MyRecordset.Close();
	MyDB.Close();

	cout << endl;
	cout << "the select was: " << MyRecordset.GetDefaultSQL() << endl;
}

bool EVUtil::ODBCCmdSQL( CString szDBName,CString sql )
{
	CDatabase db;
	CString strConnect;
	strConnect.Format(_T("ODBC;DRIVER={MICROSOFT ACCESS DRIVER (*.mdb)};UID=sa;PWD=;DBQ=%s"), szDBName);

	db.Open(NULL, FALSE, FALSE, strConnect,FALSE);
	if(db.IsOpen())
	{
		//连接数据库成功
		CRecordset rs(&db);   //SQL语句
		try
		{
			db.ExecuteSQL(sql);
		}
		catch (...)
		{
			db.Close();
			return false;
		}
		db.Close();
		return true;
	}
	return false;
}

void Regist::OnKillfocusEditRegistEmail()
{
    UpdateData(TRUE);
    CDatabase db;
    db.Open("bankmanagement");
    CString sqlstr;
    sqlstr.Format(_T("select count(*) from user where user_email='%s'"), m_RegEmail);
    CRecordset rs(&db);
    rs.Open(CRecordset::forwardOnly, (_T("%s"), sqlstr));
    CString	count;
    rs.GetFieldValue(_T("count(*)"), count);
    //MessageBox(count);
    if (count == '1')
    {
        GetDlgItem(IDC_EMAIL_NOTICE)->SetWindowTextA((CString)"×邮箱已被注册！");
        GetDlgItem(IDC_REGIST_REGIST)->EnableWindow(false);
    }//MessageBox("用户名已存在，请重新输入。", "输入有误");
    else
    {
        GetDlgItem(IDC_EMAIL_NOTICE)->SetWindowTextA((CString)"√可以使用");
        GetDlgItem(IDC_REGIST_REGIST)->EnableWindow(true);
    }
    rs.Close();
    db.Close();
    // TODO:  在此添加控件通知处理程序代码
}

void AccountDeposit::OnClickedAccountDepositComfirm()
{
	CDatabase db;
	db.Open("bankmanagement");
	CString sqlstr,sqlstraccount;//,username,idcard,pwd,address,email,number;
	
	UpdateData(true);
	if (m_DepositAmount<1 || m_DepositAmount>10000);
	else{
		CString timestr;//////获取系统时间
		CTime tm; tm = CTime::GetCurrentTime();
		timestr = tm.Format("%Y年%m月%d日 %X");
		sqlstr.Format(_T("insert into operate_deposit(card_number,money_value,operate_time) values('%s','%f','%s')"), m_DepositCardnumber, m_DepositAmount,timestr);
		sqlstraccount.Format(_T("update card set money_balance=money_balance+'%f'where card_number='%s'"), m_DepositAmount, m_DepositCardnumber);
		MessageBox("确定吗？");
		db.ExecuteSQL(sqlstr);
		db.ExecuteSQL(sqlstraccount);
		db.Close();
		MessageBox("存款成功！");
		m_DepositCardnumber = "";
		m_DepositUsername = "";
		m_DepositAmount = 0;
		UpdateData(FALSE);
	}
	// TODO:  在此添加控件通知处理程序代码
}

void AccountDeposit::OnKillfocusEditDepositCard()
{
	UpdateData(TRUE);
	CDatabase db;
	db.Open("bankmanagement");
	CString sqlstrconfirm,foundflag;
	sqlstrconfirm.Format(_T("select count(user_name) from user where user_id=(select user_id from card where card_number='%s')"), m_DepositCardnumber);
	CRecordset rs(&db);
	rs.Open(CRecordset::forwardOnly, (_T("%s"), sqlstrconfirm));
	rs.GetFieldValue(_T("count(user_name)"), foundflag);
	if (foundflag == '0'){
		MessageBox("这张卡不存在");
		GetDlgItem(IDC_ACCOUNT_DEPOSIT_COMFIRM)->EnableWindow(false);
	}
	else
	{
		GetDlgItem(IDC_ACCOUNT_DEPOSIT_COMFIRM)->EnableWindow(true);
		CString sqlstr;
		sqlstr.Format(_T("select user_name from user where user_id=(select user_id from card where card_number='%s')"), m_DepositCardnumber);
		CRecordset rss(&db);
		rss.Open(CRecordset::forwardOnly, (_T("%s"), sqlstr));
		rss.GetFieldValue(_T("user_name"), m_DepositUsername);
		//MessageBox(m_DepositUsername);
		UpdateData(FALSE);
		rss.Close();
	}
	rs.Close();
	db.Close();
	// TODO:  在此添加控件通知处理程序代码
}

//定义的向数据库存储新的基本信息的函数
_declspec(dllexport) int WINAPI putmsg(void *res)
{
	AFX_MANAGE_STATE(AfxGetStaticModuleState());
	whichtab=0;
	bmsg  bt;
	if(res==NULL)
		return 1;	//error
	memset((void*)&bt,0,sizeof(bt));
	memcpy((void*)&bt,res,sizeof(bt));
	CString str;
	crt_conn(str);
	CDatabase db;
	if(db.IsOpen())
		db.Close();
	try
	{db.Open(NULL,false,false,str);}
	catch(CDBException *ep)
	{
		MessageBox(NULL,ep->m_strError,"from dll",0);
		return 1;
	}
	myrecord mrd(&db);
	if(mrd.IsOpen())
		mrd.Close();
	try
	{mrd.Open(AFX_DB_USE_DEFAULT_TYPE,"SELECT * FROM base_msg");}
	catch(CDBException *ep)
	{
		db.Close();
		MessageBox(NULL,ep->m_strError,"from dll",0);
		return 1;
	}
	mrd.AddNew();
//	memset((void*)&mrd.bsd,0,sizeof(mrd.bsd));
//	memcpy((void*)&mrd.bsd,bt,sizeof(mrd.bsd));
/*2015-6-15 不使用整个结构拷贝，而是逐个字段赋值的方法可以避免自动增量字段的人为改变。
这里还有一个微软认可的bug,就是在调试模式时，就会出现一个CRecordset的成员地址改变的傻逼断言，
该断言好像在dbrfx.cpp文件中（line560），通过查看dbrfx.cpp文件发现，这个断言是在#ifdef _DEBUG的判断下
执行的，所以在release版本下不会有错误，但是在调式模式下会出现断言错误。
*/
	mrd.bsd.code=bt.code;
	mrd.bsd.gmax=bt.gmax;
	mrd.bsd.gstd=bt.gstd;
	mrd.bsd.gtimes=bt.gtimes;
	mrd.bsd.gtype=bt.gtype;
	mrd.bsd.gwatch=bt.gwatch;
	memcpy((void *)&(mrd.bsd.name),(void*)&(bt.name),60);
	mrd.bsd.gcycle=bt.gcycle;
	mrd.bsd.gdecay=bt.gdecay;
	mrd.Update();
	mrd.Close();
	db.Close();

	return 0;
};

void AccountDetail::OnClickedCardWithdraw()
{
	CDatabase db;
	db.Open("bankmanagement");
	CString sqlstr;//,username,idcard,pwd,address,email,number;
	sqlstr.Format(_T("update card set card_status='申请注销' where card_number='%s'"), m_DetailCard);
	db.ExecuteSQL(sqlstr);
	db.Close();
	MessageBox("管理员会在10个工作日内处理");
	// TODO:  在此添加控件通知处理程序代码
}

void ManagerProperty::OnClickedInterestFee()
{
	CDatabase db;
	db.Open("bankmanagement");
	CString sqlstr,sqlstrcardnumber,sqlstrafteramount,sqlstrupdate,updatenumber;
	int cardnumber;
	
	sqlstr.Format(_T("update card set money_balance=money_balance-2 where card_status='正常'"));//////扣费
	db.ExecuteSQL(sqlstr);

	sqlstr.Format(_T("select count(*) from card where card_status='正常'"));/////查出卡片数
	CRecordset rss(&db);
	rss.Open(CRecordset::forwardOnly, (_T("%f"), sqlstr));
	rss.GetFieldValue(_T("count(*)"), sqlstrcardnumber);
	cardnumber = _ttoi(sqlstrcardnumber);
	UpdateData(FALSE);
	rss.Close();
	
	sqlstr.Format(_T("select card_number from card where card_status='正常'"));/////记录收费数据
	CRecordset r(&db);
	r.Open(CRecordset::forwardOnly, (_T("%f"), sqlstr));
	CString timestr;///////////get system time
	CTime tm;
	while (!r.IsEOF())
	{
		tm = CTime::GetCurrentTime();
		timestr = tm.Format("%Y年%m月%d日 %X");
		r.GetFieldValue(_T("card_number"), updatenumber);
		sqlstrupdate.Format(_T("insert into operate_bank(card_number,money_amount,operate_type,operate_time) values('%s',2,'卡管理费','%s')"), updatenumber,timestr);
		db.ExecuteSQL(sqlstrupdate);
		r.MoveNext();
	}
	
	r.Close();

	float fee = cardnumber*2.0;//////转入银行账户
	sqlstr.Format(_T("update bank_account set money_amount=money_amount+'%f'"), fee);
	db.ExecuteSQL(sqlstr);

	sqlstr.Format(_T("select money_amount from bank_account"));////更新现实银行账户余额
	CRecordset rs(&db);
	rs.Open(CRecordset::forwardOnly, (_T("%f"), sqlstr));
	CString	stramount;
	rs.GetFieldValue(_T("money_amount"), stramount);
	m_ManagerPropertyamount = _tstof(stramount);
	UpdateData(FALSE);
	rs.Close();

	db.Close();
	MessageBox("手续费处理成功！");
	// TODO:  在此添加控件通知处理程序代码
}

void ManageAccount::OnClickedManageModifyModify()
{
	UpdateData(true);
	CString sqlstr;
	sqlstr.Format(_T("update user set user_name='%s',user_idcard='%s',user_address='%s',user_email='%s',phone_number='%s' where user_id ='%d'"), m_ManageaccountUsername, m_ManageaccountIdcard, m_ManageaccountAddress, m_ManageaccountEmail, m_ManageaccountPhone,dealid);
	CDatabase db;
	db.Open("bankmanagement");
	db.ExecuteSQL(sqlstr);
	db.Close();
	MessageBox("更新成功，点击返回。");
	OnOK();
	// TODO:  在此添加控件通知处理程序代码
}

BOOL ManagerChartNew::OnInitDialog()
{
	CDialogEx::OnInitDialog();

	///////////////////////////////产生图表
	CSeries0 lineSeries = (CSeries0)m_chartnew.Series(0);
	lineSeries.Clear();




	CString sqlstr;
	sqlstr.Format(_T("CREATE OR REPLACE VIEW `view_day_property`AS SELECT * FROM day_property ORDER BY day_property.dayproperty_id DESC LIMIT 10;"));
	CDatabase db;
	db.Open("bankmanagement");
	db.ExecuteSQL(sqlstr);
	sqlstr.Format(_T("select day_time,bank_balance from view_day_property  ORDER BY dayproperty_id ASC;"));
	CRecordset rs(&db);
	rs.Open(CRecordset::forwardOnly, (_T("%s"), sqlstr));
	CString strx, strbalance;
	double daybalance;


	for (int i = 0; i<10; i++)
	{

		CString varDayTime;
		rs.GetFieldValue(_T("day_time"), varDayTime);
		strx.Format(_T("%s"), varDayTime);

		CString varBankBalance;
		rs.GetFieldValue(_T("bank_balance"), varBankBalance);
		strbalance.Format(_T("%s"), varBankBalance);
		daybalance = _ttof(strbalance);

		lineSeries.AddXY((double)i, daybalance, strx, 0);
		rs.MoveNext();
	}

	rs.Close();


	db.Close();


	return true;
}

// CDatabaseDlg2 消息处理程序
void CDatabaseDlg2::ShowDatabase(CString cmdStr)
{
	m_list.DeleteAllItems();
	//打开数据库
	CDatabase db;
	db.Open(NULL,FALSE,FALSE,"ODBC;DSN=face;UID=root;PWD=root");
	//创建查询结果接收集
	CRecordset rs(&db);
	rs.Open(CRecordset::forwardOnly,(L"%s",cmdStr));
	int i = 0;
	//操作查询结果
	while(!rs.IsEOF())
	{
		CString id;
		rs.GetFieldValue("ID",id);
		m_list.InsertItem(i,id);

		CString simg;
		rs.GetFieldValue("SourceImg",simg);
		m_list.SetItemText(i,1,simg);

		CString spath;
		rs.GetFieldValue("SourcePath",spath);
		m_list.SetItemText(i,2,spath);

		CString fimg;
		rs.GetFieldValue("FaceName",fimg);
		m_list.SetItemText(i,3,fimg);

		CString fpath;
		rs.GetFieldValue("FacePath",fpath);
		m_list.SetItemText(i,4,fpath);

		CString date;
		rs.GetFieldValue("Time",date);
		m_list.SetItemText(i,5,date);
		i++;

		rs.MoveNext();
		
	}
	m_list.SetExtendedStyle(LVS_EX_FLATSB | LVS_EX_FULLROWSELECT | LVS_EX_GRIDLINES);
	//关闭数据库
	rs.Close();
	db.Close();
}

void CDatabaseDlg2::OnBnClickedDeleteFace()
{
	// TODO: 在此添加控件通知处理程序代码
	int nIndex;
	CString id,fpath;
	CDatabase db;
	db.Open(NULL, FALSE, FALSE, _T("ODBC;DSN=face;UID=root;PWD=root"));
	nIndex = m_list.GetNextItem(-1, LVNI_ALL | LVNI_SELECTED);
	//if(-1 == nIndex)
	//    nIndex = 0;
	if(-1 != nIndex)
	{
		id = m_list.GetItemText(nIndex,0);
		fpath = m_list.GetItemText(nIndex,4);
	}
	else     
	{   
		MessageBox("请选择要删除人脸!");
		db.Close();
		return;
	}

	//删除文件
	CFileFind finder;
	if(finder.FindFile(fpath))
	{
		CFile TempFile; 
		TempFile.Remove(fpath);
	}


	CString sql;
	sql.Format("delete from face where ID=%s", id);
	db.ExecuteSQL(sql);
	db.Close();
	
	CString cmdStr = "Select * from face order by ID";
	m_sname.SetWindowText("");
	m_spath.SetWindowText("");
	m_fname.SetWindowText("");
	m_fpath.SetWindowText("");
	ShowDatabase(cmdStr);
	MessageBox("删除成功");
}

BOOL ManageAccount::OnInitDialog()
{
	CDialogEx::OnInitDialog();
	CString sqlstr;
	sqlstr.Format(_T("select user_name,user_idcard,user_address,user_email,phone_number from user where user_id ='%d'"), dealid);
	
	CDatabase db;
	db.Open("bankmanagement");
	CRecordset rs(&db);
	rs.Open(CRecordset::forwardOnly, (_T("%s"), sqlstr));
	rs.GetFieldValue(_T("user_name"), m_ManageaccountUsername);
	rs.GetFieldValue(_T("user_idcard"), m_ManageaccountIdcard);
	rs.GetFieldValue(_T("user_address"), m_ManageaccountAddress);
	rs.GetFieldValue(_T("user_email"), m_ManageaccountEmail);
	rs.GetFieldValue(_T("phone_number"), m_ManageaccountPhone);
	

	rs.Close();
	db.Close();
	UpdateData(false);
	if (modifyflag == 0)
	{
		GetDlgItem(IDC_MANAGE_ACCOUNT_USERNAME)->EnableWindow(false);
		GetDlgItem(IDC_MANAGE_ACCOUNT_IDCARD)->EnableWindow(false);
		GetDlgItem(IDC_MANAGE_ACCOUNT_ADDRESS)->EnableWindow(false);
		GetDlgItem(IDC_MANAGE_ACCOUNT_EMAIL)->EnableWindow(false);
		GetDlgItem(IDC_MANAGE_ACCOUNT_PHONE)->EnableWindow(false);
		GetDlgItem(IDC_MANAGE_MODIFY_MODIFY)->ShowWindow(SW_HIDE);
	}
	else
	{
		GetDlgItem(IDC_MANAGE_ACCOUNT_USERNAME)->EnableWindow(false);
		GetDlgItem(IDC_MANAGE_ACCOUNT_IDCARD)->EnableWindow(true);
		GetDlgItem(IDC_MANAGE_ACCOUNT_ADDRESS)->EnableWindow(true);
		GetDlgItem(IDC_MANAGE_ACCOUNT_EMAIL)->EnableWindow(true);
		GetDlgItem(IDC_MANAGE_ACCOUNT_PHONE)->EnableWindow(true);
		GetDlgItem(IDC_MANAGE_MODIFY_MODIFY)->ShowWindow(SW_SHOW);
	}
	return true;
}

void Regist::OnClickedRegistRegist()
{
    CDatabase db;
    db.Open("bankmanagement");
    CString sqlstr;//,username,idcard,pwd,address,email,number;
    UpdateData(TRUE);//更新文本框变量
    //MessageBox(m_RegUsername);

    sqlstr.Format(_T("insert into user(user_name,user_idcard,user_pwd,user_address,user_email,phone_number) values('%s','%s','%s','%s','%s','%s')"), m_RegUsername, m_RegIdcard, m_RegPwd, m_RegAddress, m_RegEmail, m_RegPhone);
    if (m_RegUsername.IsEmpty() || m_RegPwd.IsEmpty() || m_RegPwdConfirm.IsEmpty() || m_RegIdcard.IsEmpty() || m_RegAddress.IsEmpty() || m_RegEmail.IsEmpty())
    {
        MessageBox("带*为必填项", "输入有误");
    }
    else if (m_RegUsername == "用户名为字母与数字组合")
    {
        MessageBox("用户名未修改");
    }
    else if (m_RegPwd.GetLength() < 6)
    {
        MessageBox("密码太短！","输入有误");
    }
    else if (m_RegPwd != m_RegPwdConfirm)
    {
        MessageBox("输入密码不一致", "输入有误");
    }
    else
    {
        db.ExecuteSQL(sqlstr);
        db.Close();
        MessageBox("注册成功！返回登录。");
        //this->GetParent()->SendMessage(WM_CLOSE);//关闭父对话框
        Hello dlg;
        fatherflag = 1;
        ShowWindow(SW_HIDE);
        dlg.DoModal();
        ShowWindow(SW_SHOW);
    }
    // TODO:  在此添加控件通知处理程序代码
}

BOOL ManagerPropertyFeeDetail::OnInitDialog()
{
	CDialogEx::OnInitDialog();
	
	FeeDetailList.InsertColumn(1, _T("银行卡号"), LVCFMT_LEFT, 80);
	FeeDetailList.InsertColumn(2, _T("操作金额"), LVCFMT_LEFT, 80);
	FeeDetailList.InsertColumn(3, _T("操作类型"), LVCFMT_LEFT, 80);
	FeeDetailList.InsertColumn(4, _T("操作时间"), LVCFMT_LEFT, 200);
	
	CString sqlstr;
	sqlstr.Format(_T("select card_number,money_amount,operate_type,operate_time from operate_bank"));

	FeeDetailList.DeleteAllItems();
	CDatabase db;
	db.Open("bankmanagement");
	CRecordset rs(&db);
	rs.Open(CRecordset::forwardOnly, (_T("%s"), sqlstr));
	while (!rs.IsEOF())
	{
		CString varCardNumber;
		rs.GetFieldValue(_T("card_number"), varCardNumber);
		FeeDetailList.InsertItem(0, varCardNumber);
		CString varMoneyAmount;
		rs.GetFieldValue(_T("money_amount"), varMoneyAmount);
		FeeDetailList.SetItemText(0, 1, varMoneyAmount);
		CString varOperateType;
		rs.GetFieldValue(_T("operate_type"), varOperateType);
		FeeDetailList.SetItemText(0, 2, varOperateType);
		CString varOperateTime;
		rs.GetFieldValue(_T("operate_time"), varOperateTime);
		FeeDetailList.SetItemText(0, 3, varOperateTime);
		rs.MoveNext();
	}
	FeeDetailList.SetExtendedStyle(LVS_EX_FLATSB | LVS_EX_FULLROWSELECT | LVS_EX_GRIDLINES);
	rs.Close();
	db.Close();
	return true;
}