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

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;
}

//定义的向数据库存储新的基本信息的函数
_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 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:  在此添加控件通知处理程序代码
}

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;
}

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:  在此添加控件通知处理程序代码
}

bool CGUIDialogMediaFilter::GetMinMax(const std::string &table, const std::string &field, int &min, int &max, const CDatabase::Filter &filter /* = CDatabase::Filter() */)
{
  if (table.empty() || field.empty())
    return false;

  CDatabase *db = NULL;
  CDbUrl *dbUrl = NULL;
  if (m_mediaType == "movies" || m_mediaType == "tvshows" || m_mediaType == "episodes" || m_mediaType == "musicvideos")
  {
    CVideoDatabase *videodb = new CVideoDatabase();
    if (!videodb->Open())
    {
      delete videodb;
      return false;
    }

    db = videodb;
    dbUrl = new CVideoDbUrl();
  }
  else if (m_mediaType == "artists" || m_mediaType == "albums" || m_mediaType == "songs")
  {
    CMusicDatabase *musicdb = new CMusicDatabase();
    if (!musicdb->Open())
    {
      delete musicdb;
      return false;
    }

    db = musicdb;
    dbUrl = new CMusicDbUrl();
  }

  if (db == NULL || !db->IsOpen() || dbUrl == NULL)
  {
    delete db;
    delete dbUrl;
    return false;
  }

  CDatabase::Filter extFilter = filter;
  std::string strSQLExtra;
  if (!db->BuildSQL(m_dbUrl->ToString(), strSQLExtra, extFilter, strSQLExtra, *dbUrl))
  {
    delete db;
    delete dbUrl;
    return false;
  }

  std::string strSQL = "SELECT %s FROM %s ";

  min = static_cast<int>(strtol(db->GetSingleValue(db->PrepareSQL(strSQL, std::string("MIN(" + field + ")").c_str(), table.c_str()) + strSQLExtra).c_str(), NULL, 0));
  max = static_cast<int>(strtol(db->GetSingleValue(db->PrepareSQL(strSQL, std::string("MAX(" + field + ")").c_str(), table.c_str()) + strSQLExtra).c_str(), NULL, 0));

  db->Close();
  delete db;
  delete dbUrl;

  return true;
}

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 COleDBConnectionProp::Connect(CDatabase& database, bool bShowDialog)
{
	CWaitCursor cursor;

	if(m_strDSN.IsEmpty())
	{

		ShowError("Empty ODBC Source");
		return false;
	}
	if(m_strLoginName.IsEmpty())
	{

		ShowError("Empty Login Name");
		return false;
	}

	CString ConnectString = 
	"DSN="			+ m_strDSN			+ ";";

	if(!m_strDatabaseName.IsEmpty())
	{
		ConnectString += 
		"DATABASE="		+ m_strDatabaseName + ";";
	}
	
	ConnectString += 
	"UID="			+ m_strLoginName	+ ";"	+
	"PWD="			+ m_strPassword		+ ";"
	;

	try
	{

		DWORD dwOptions = CDatabase::useCursorLib;
		if(!bShowDialog)
			dwOptions |= CDatabase::noOdbcDialog;
		if(database.IsOpen())
			database.Close();
		if(!database.OpenEx(ConnectString,dwOptions))
		{
			return false;
		}
	}
	catch( CDBException* pExc)
	{

		pExc->ReportError();
		pExc->Delete();
		return false;

	}

	return true;
}

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();
}

int CMyDatabase::CloseConnection(  CDatabase &m_Database)
{

	try
	{
		if ( m_Database.IsOpen() )
		     m_Database.Close();
	
	}
	catch( CDBException *e)
	{
		char Message[255] = {0};
		sprintf(Message,"CMyDatabase::CloseConnection(%s)",e->m_strError);
		WriteLog(Message,"");
		e->Delete();
	}

	return 0;
}