db.cpp

一个与金融方面有关的问题

	_ConnectionPtr pConn;
	pConn=ConnectDB();
	//2）设置命令对象
	_CommandPtr pCmd;
	pCmd.CreateInstance("ADODB.Command");
	pCmd->ActiveConnection=pConn;
	pCmd->CommandType=adCmdStoredProc;
	pCmd->CommandText=_bstr_t("outMoney_proc");
	//3）定义参数集合成员
	_ParameterPtr paramAccountId,paramAccountPwd,
		paramMoneyNum,paramOpBankIP,paramRetVal;
	//4）创建参数
	paramAccountId=pCmd->CreateParameter(_bstr_t("accountId"),adChar,adParamInput,19,_variant_t(accountId));
	paramAccountPwd=pCmd->CreateParameter(_bstr_t("accountPwd"),adChar,adParamInput,17,_variant_t(accountPwd));
	paramMoneyNum=pCmd->CreateParameter(_bstr_t("moneyNum"),adChar,adParamInput,10,_variant_t(moneyNum));
	paramOpBankIP=pCmd->CreateParameter(_bstr_t("BankIP"),adChar,adParamInput,17,_variant_t(opBankIP));
	paramRetVal=pCmd->CreateParameter(_bstr_t("retVal"),adInteger,adParamReturnValue,sizeof(long),NULL);
	//5）添加参数
	pCmd->Parameters->Append(paramRetVal);
	pCmd->Parameters->Append(paramAccountId);
	pCmd->Parameters->Append(paramAccountPwd);
	pCmd->Parameters->Append(paramMoneyNum);
	pCmd->Parameters->Append(paramOpBankIP);
	//6）执行
	try
	{
		_variant_t vNull;
		vNull.vt=VT_ERROR;
		vNull.scode=DISP_E_PARAMNOTFOUND;
			
		pCmd->Execute(&vNull,&vNull,adCmdStoredProc);
	}catch(_com_error e)
	{
		pCmd.Detach();
		pConn->Close();
		pConn.Release();
		return 0;
	}
	//7）获得返回
	int ret=(int)pCmd->Parameters->GetItem(_bstr_t("RetVal"))->GetValue();
	//8）释放
	pCmd.Detach();
	pConn->Close();
	pConn.Release();

	return ret;
}
/*转帐函数
返回值：
	-1--转出账号或密码错误
	-2--转入账号不存在
	-3--没有取款银行
	-4 --余额不足
	1--成功  0-- 失败
 */
int CDB::TransferMoney(char accountId[],
				 char accountPwd[],
				 char moneyNum[],
				 char opBankIP[],
				 char inAccountId[])
{
	//1）连接数据库
	_ConnectionPtr pConn;
	pConn=ConnectDB();
	//2）设置命令对象
	_CommandPtr pCmd;
	pCmd.CreateInstance("ADODB.Command");
	pCmd->ActiveConnection=pConn;
	pCmd->CommandType=adCmdStoredProc;
	pCmd->CommandText=_bstr_t("transfer_proc");
	//3）定义参数集合成员
	_ParameterPtr paramAccountId,paramAccountPwd,
				paramMoneyNum,paramOpBankIP,
				paramInAccountId,paramRetVal;
	//4）创建参数
	paramAccountId=pCmd->CreateParameter(_bstr_t("accountId"),adChar,adParamInput,19,_variant_t(accountId));
	paramAccountPwd=pCmd->CreateParameter(_bstr_t("accountPwd"),adChar,adParamInput,17,_variant_t(accountPwd));
	paramMoneyNum=pCmd->CreateParameter(_bstr_t("moneyNum"),adChar,adParamInput,10,_variant_t(moneyNum));
	paramOpBankIP=pCmd->CreateParameter(_bstr_t("BankIP"),adChar,adParamInput,17,_variant_t(opBankIP));
	paramInAccountId=pCmd->CreateParameter(_bstr_t("inAccountId"),adChar,adParamInput,19,_variant_t(inAccountId));
	paramRetVal=pCmd->CreateParameter(_bstr_t("RetVal"),adInteger,adParamReturnValue,sizeof(long),NULL);
	//5）添加参数到参数集合
	pCmd->Parameters->Append(paramRetVal);
	pCmd->Parameters->Append(paramAccountId);
	pCmd->Parameters->Append(paramAccountPwd);
	pCmd->Parameters->Append(paramInAccountId);
	pCmd->Parameters->Append(paramMoneyNum);
	pCmd->Parameters->Append(paramOpBankIP);
	//6）执行
	try
	{
		_variant_t vNull;
		vNull.vt=VT_ERROR;
		vNull.scode=DISP_E_PARAMNOTFOUND;
			
		pCmd->Execute(&vNull,&vNull,adCmdStoredProc);
	}catch(_com_error e)
	{
		pCmd.Detach();
		pConn->Close();
		pConn.Release();
		return 0;
	}
	//7）获得返回
	int ret=(int)pCmd->Parameters->GetItem(_bstr_t("RetVal"))->GetValue();
	//8）释放
	pCmd.Detach();
	pConn->Close();
	pConn.Release();
	
	return ret;
}
/*
	查询余额
	返回值 
	成功	1
	失败	0
*/
int CDB::findBalance(char accountId[],char accountPwd[],char moneyNum[])
{///**$&
	//1）连接数据库
	_ConnectionPtr pConn;
	pConn=ConnectDB();
	//2）设置命令对象
	_CommandPtr pCmd;
	pCmd.CreateInstance("ADODB.Command");
	//3）定义参数集合成员
	pCmd->ActiveConnection=pConn;
	pCmd->CommandType=adCmdStoredProc;
	pCmd->CommandText=_bstr_t("findBalance_proc");
	//4）申明参数 创建参数 添加参数到集合
	_ParameterPtr paramaccountId;
	if(FAILED(paramaccountId.CreateInstance("ADODB.Parameter")))
		return 0;
	paramaccountId=pCmd->CreateParameter(_bstr_t("accountId"),adChar,adParamInput,19,_variant_t(accountId));
	pCmd->Parameters->Append(paramaccountId);
	//5）执行命令
	try{
		_variant_t vNull;
		vNull.vt=VT_ERROR;
		vNull.scode=DISP_E_PARAMNOTFOUND;
			
		_RecordsetPtr pRs=pCmd->Execute(&vNull,&vNull,adCmdStoredProc);
		strcpy(moneyNum,(char*)_bstr_t(pRs->GetCollect(_variant_t("Balance"))));
	}catch(_com_error e)
	{
		pCmd.Detach();
		pConn->Close();
		pConn.Release();	
		return 0;
	}
	//6）释放
	pCmd.Detach();
	pConn->Close();
	pConn.Release();
	return 1;
}
/*修改密码
	返回值 
	成功	1
	失败	0
*/
int CDB::notifyPassword(char accountId[],
					char oldpassword[],
					char newpassword[])
{
	//1）连接数据库
	_ConnectionPtr pConn;
	pConn=ConnectDB();
	//2）设置命令对象
	_CommandPtr pCmd;
	pCmd.CreateInstance("ADODB.Command");
	pCmd->ActiveConnection=pConn;
	pCmd->CommandType=adCmdStoredProc;
	pCmd->CommandText=_bstr_t("notify_proc");
	//3）定义参数集合成员
	_ParameterPtr paramAccountId,paramRetVal,paramOldPassword,paramNewPassword;
	//4）创建参数
	paramAccountId=pCmd->CreateParameter(_bstr_t("accountId"),adChar,adParamInput,19,_variant_t(accountId));
	paramOldPassword=pCmd->CreateParameter(_bstr_t("oldpassword"),adChar,adParamInput,19,_variant_t(oldpassword));
	paramNewPassword=pCmd->CreateParameter(_bstr_t("newpassword"),adChar,adParamInput,19,_variant_t(newpassword));
	paramRetVal=pCmd->CreateParameter(_bstr_t("RetVal"),adInteger,adParamReturnValue,sizeof(long),NULL);
	//5）添加参数到集合
	pCmd->Parameters->Append(paramRetVal);
	pCmd->Parameters->Append(paramAccountId);
	pCmd->Parameters->Append(paramOldPassword);
	pCmd->Parameters->Append(paramNewPassword);
	//6）执行命令
	try
	{
		_variant_t vNull;
		vNull.vt=VT_ERROR;
		vNull.scode=DISP_E_PARAMNOTFOUND;
			
		pCmd->Execute(&vNull,&vNull,adCmdStoredProc);
	}catch(_com_error e)
	{
		pCmd.Detach();
		pConn->Close();
		pConn.Release();
		return 0;
	}
	//7）获得返回值
	int ret=(int)pCmd->Parameters->GetItem(_bstr_t("RetVal"))->GetValue();
	//8）释放
	pCmd.Detach();
	pConn->Close();
	pConn.Release();
	
	return ret;

}
/*创建新用户
	返回值 
	成功	1
	失败	0
*/
int CDB::CreateNewAccount(char accountId[],
					char password[])
{
	//1）连接数据库
	_ConnectionPtr pConn;
	pConn=ConnectDB();
	//2）设置命令对象
	_CommandPtr pCmd;
	pCmd.CreateInstance("ADODB.Command");
	pCmd->ActiveConnection=pConn;
	pCmd->CommandType=adCmdStoredProc;
	pCmd->CommandText=_bstr_t("createAcc_proc");
	//3）定义参数集合成员
	_ParameterPtr paramAccountId,paramRetVal,paramNewPassword;
	//4）创建参数
	paramAccountId=pCmd->CreateParameter(_bstr_t("accountId"),adChar,adParamInput,19,_variant_t(accountId));
	paramNewPassword=pCmd->CreateParameter(_bstr_t("newpassword"),adChar,adParamInput,19,_variant_t(password));
	paramRetVal=pCmd->CreateParameter(_bstr_t("RetVal"),adInteger,adParamReturnValue,sizeof(long),NULL);
	//5）添加参数到参数集合
	pCmd->Parameters->Append(paramRetVal);
	pCmd->Parameters->Append(paramAccountId);
	pCmd->Parameters->Append(paramNewPassword);
	//6）执行命令
	try
	{
		_variant_t vNull;
		vNull.vt=VT_ERROR;
		vNull.scode=DISP_E_PARAMNOTFOUND;
			
		pCmd->Execute(&vNull,&vNull,adCmdStoredProc);
	}catch(_com_error e)
	{
		pCmd.Detach();
		pConn->Close();
		pConn.Release();
		return 0;
	}
	//7）获得返回值
	int ret=(int)pCmd->Parameters->GetItem(_bstr_t("RetVal"))->GetValue();
	//8）释放
	pCmd.Detach();
	pConn->Close();
	pConn.Release();
	
	return ret;
}
/*
	查询客户信息
	参数customerName是查询条件，如果customerName="全部"
	则显示customer_table全部信息
*/
int CDB::findCustomerInfo(char customerName[],CListCtrl &m_ListInfo)
{
	//1）连接数据库
	_ConnectionPtr pConn;
	pConn=ConnectDB();
	//2）设置命令对象
	_CommandPtr pCmd;
	pCmd.CreateInstance("ADODB.Command");
	pCmd->ActiveConnection=pConn;
	pCmd->CommandType=adCmdStoredProc;
	pCmd->CommandText=_bstr_t("find_customer_proc");
	//3）定义参数集合成员
	_ParameterPtr paramName;
	paramName=pCmd->CreateParameter(_bstr_t("customerName"),adChar,adParamInput,11,_variant_t(customerName));
	pCmd->Parameters->Append(paramName);
	try
	{//4）执行
		_variant_t vNull;
		vNull.vt=VT_ERROR;
		vNull.scode=DISP_E_PARAMNOTFOUND;
			
		_RecordsetPtr pRs=pCmd->Execute(&vNull,&vNull,adCmdStoredProc);
		//显示
		CString str;
		int i=0;
		m_ListInfo.DeleteAllItems();
		while(!pRs->EndOfFile)
		{	
			str.Format("%d",++i);
			m_ListInfo.InsertItem(0,str);
			
			m_ListInfo.SetItemText(0,1,(char*)_bstr_t(pRs->GetCollect(_variant_t("customer_name"))));
			m_ListInfo.SetItemText(0,2,(char*)_bstr_t(pRs->GetCollect(_variant_t("customer_real_name"))));
			m_ListInfo.SetItemText(0,3,(char*)_bstr_t(pRs->GetCollect(_variant_t("customer_phone"))));
			m_ListInfo.SetItemText(0,4,(char*)_bstr_t(pRs->GetCollect(_variant_t("customer_addr"))));
			m_ListInfo.SetItemText(0,5,(char*)_bstr_t(pRs->GetCollect(_variant_t("customer_email"))));
			m_ListInfo.SetItemText(0,6,(char*)_bstr_t(pRs->GetCollect(_variant_t("customer_score"))));
			m_ListInfo.SetItemText(0,7,(char*)_bstr_t(pRs->GetCollect(_variant_t("customer_grade"))));
			m_ListInfo.SetItemText(0,8,(char*)_bstr_t(pRs->GetCollect(_variant_t("customer_regbank_ip"))));
			//m_ListInfo.SetItemText(0,2,(char*)_bstr_t(pRs->GetCollect(_variant_t("customer_phone"))));
			
			pRs->MoveNext();
		}
	
	}catch(_com_error e)
	{
		pCmd.Detach();
		pConn->Close();
		pConn.Release();
		return 0;
	}
	//7）释放
	pCmd.Detach();
	pConn->Close();
	pConn.Release();
	
	return 1;
}