database.cpp

连接SQL数据库的系统源码,对于初学者十分有参考价值哦

		char m_szTmp[1024] ;
		sprintf(m_szTmp, "执行==>%s<==, 数据库操作失败,错误原因：%s\n",m_szSql, LPCTSTR(e.Description()));
	}
}

void CDatabase::InitOperatorData(CComboBox *m_hCmbRole)
{
	_variant_t v(0L) ;
	_RecordsetPtr m_Rsp ;
	char m_szSql[512] ;
	sprintf(m_szSql, "select * from tb_roleinfo") ;
	try{
		
			
		m_Rsp = m_Connection->Execute(_bstr_t(m_szSql), &v, adCmdText) ;
		while(!m_Rsp->GetadoEOF())
		{
			v = m_Rsp->GetCollect("rolename") ;
			m_hCmbRole->AddString(_bstr_t(v)) ;

			m_Rsp->MoveNext() ;
		}
	}
	catch(_com_error & e) 
	{
		char m_szTmp[1024] ;
		sprintf(m_szTmp, "执行==>%s<==, 数据库操作失败,错误原因：%s\n",m_szSql, LPCTSTR(e.Description()));
	}
}

void CDatabase::ListOperatorToCtrl(CListCtrl *m_hListCtrl)
{
	m_hListCtrl->DeleteAllItems() ;

	_variant_t v(0L) ;
	_RecordsetPtr m_Rsp ;
	char m_szSql[512] ;
	sprintf(m_szSql, "select * from tb_operator") ;
	try{
		
		m_Rsp = m_Connection->Execute(_bstr_t(m_szSql), &v, adCmdText) ;
		while(!m_Rsp->GetadoEOF())
		{
			m_hListCtrl->InsertItem(0, "") ;

			v = m_Rsp->GetCollect("id") ;
			m_hListCtrl->SetItemText(0, 0, _bstr_t(v)) ;
			v = m_Rsp->GetCollect("Name") ;
			m_hListCtrl->SetItemText(0, 1, _bstr_t(v)) ;
			v = m_Rsp->GetCollect("password") ;
			m_hListCtrl->SetItemText(0, 2, _bstr_t(v)) ;
			v = m_Rsp->GetCollect("role") ;
			m_hListCtrl->SetItemText(0, 3, _bstr_t(v)) ;

			m_Rsp->MoveNext() ;
		}
	}
	catch(_com_error & e) 
	{
		char m_szTmp[1024] ;
		sprintf(m_szTmp, "执行==>%s<==, 数据库操作失败,错误原因：%s\n",m_szSql, LPCTSTR(e.Description()));
	}
}

void CDatabase::UpdateOperatorData(char *m_szID, char *m_szName, char *m_szPassword, char *m_szRole)
{
	char m_szSql[512] ;
	_variant_t v(0L) ;
	sprintf(m_szSql, "select * from tb_operator where id = '%s'", m_szID) ;
	_RecordsetPtr m_Rsp ;
	try{
		m_Rsp = m_Connection->Execute(_bstr_t(m_szSql), &v, adCmdText) ;
		if(!m_Rsp->GetadoEOF())
		{//存在数据，更新
			sprintf(m_szSql, "update tb_operator set Name = '%s', \
				password = '%s', role = '%s' where id = '%s'", 
				m_szName,m_szPassword,m_szRole,m_szID) ;
			m_Connection->Execute(_bstr_t(m_szSql), &v, adCmdText) ;
		}
		else
		{//不存在数据，增加
			sprintf(m_szSql, "insert into tb_operator (Name,password,role,id) \
				values ('%s', '%s', '%s','%s')" ,
				m_szName,m_szPassword,m_szRole,m_szID) ;
			m_Connection->Execute(_bstr_t(m_szSql), &v, adCmdText) ;
		}
	}
	catch(_com_error & e) 
	{
		char m_szTmp[1024] ;
		sprintf(m_szTmp, "执行==>%s<==, 数据库操作失败,错误原因：%s\n",m_szSql, LPCTSTR(e.Description()));
	}
}

void CDatabase::EditOperatorToCtrl(char *m_szID, CEdit *m_hEditID,CEdit *m_hEditName,CEdit *m_hEditPassword,CComboBox* m_hCmbRole)
{
	_variant_t v(0L) ;
	_RecordsetPtr m_Rsp, m_Rsp1 ;
	char m_szSql[512] ;
	sprintf(m_szSql, "select * from tb_operator where id = '%s'", m_szID) ;
	try{
		
		m_Rsp = m_Connection->Execute(_bstr_t(m_szSql), &v, adCmdText) ;
		while(!m_Rsp->GetadoEOF())
		{
			v = m_Rsp->GetCollect("id") ;
			m_hEditID->SetWindowText(_bstr_t(v)) ;

			v = m_Rsp->GetCollect("Name") ;
			m_hEditName->SetWindowText(_bstr_t(v)) ;

			v = m_Rsp->GetCollect("passwrod") ;
			m_hEditPassword->SetWindowText(_bstr_t(v)) ;

			v = m_Rsp->GetCollect("role") ;
			m_hCmbRole->SetWindowText(_bstr_t(v)) ;

			m_Rsp->MoveNext() ;
		}
	}
	catch(_com_error & e) 
	{
		char m_szTmp[1024] ;
		sprintf(m_szTmp, "执行==>%s<==, 数据库操作失败,错误原因：%s\n",m_szSql, LPCTSTR(e.Description()));
	}
}

void CDatabase::ListSpxxToCtrl(CListCtrl *m_hListCtrl)
{
	m_hListCtrl->DeleteAllItems() ;

	_variant_t v(0L) ;
	_RecordsetPtr m_Rsp ;
	char m_szSql[512] ;
	sprintf(m_szSql, "select * from tb_machandiseinfo") ;
	try{					
		m_Rsp = m_Connection->Execute(_bstr_t(m_szSql), &v, adCmdText) ;
		while(!m_Rsp->GetadoEOF())
		{
			m_hListCtrl->InsertItem(0, "") ;

			v = m_Rsp->GetCollect("ID") ;
			m_hListCtrl->SetItemText(0, 0, _bstr_t(v)) ;

			v = m_Rsp->GetCollect("name") ;
			m_hListCtrl->SetItemText(0, 1, _bstr_t(v)) ;

			v = m_Rsp->GetCollect("spec") ;
			m_hListCtrl->SetItemText(0, 2, _bstr_t(v)) ;

			v = m_Rsp->GetCollect("shortmane") ;
			m_hListCtrl->SetItemText(0, 3, _bstr_t(v)) ;

			v = m_Rsp->GetCollect("defaultprice") ;
			m_hListCtrl->SetItemText(0, 4, _bstr_t(v)) ;

			v = m_Rsp->GetCollect("manufacturer") ;
			m_hListCtrl->SetItemText(0, 5, _bstr_t(v)) ;

			v = m_Rsp->GetCollect("memo") ;
			m_hListCtrl->SetItemText(0, 6, _bstr_t(v)) ;

			m_Rsp->MoveNext() ;
		}
	}
	catch(_com_error & e) 
	{
		char m_szTmp[1024] ;
		sprintf(m_szTmp, "执行==>%s<==, 数据库操作失败,错误原因：%s\n",m_szSql, LPCTSTR(e.Description()));
	}
}

void CDatabase::EditSpxxToCtrl(char *ID, CEdit *m_hEditZjm, CEdit *m_hEditName,	CEdit *m_hEditMrjg, CEdit *m_hEditGg, CEdit *m_hEditCj,	CEdit *m_hEditBz, CEdit *m_hEditSpbh)
{
	_variant_t v(0L) ;
	_RecordsetPtr m_Rsp ;
	char m_szSql[512] ;
	sprintf(m_szSql, "select * from tb_machandiseinfo where ID = '%s'", ID) ;
	try{
		
		m_Rsp = m_Connection->Execute(_bstr_t(m_szSql), &v, adCmdText) ;
		while(!m_Rsp->GetadoEOF())
		{
			v = m_Rsp->GetCollect("ID") ;
			m_hEditSpbh->SetWindowText(_bstr_t(v)) ;

			v = m_Rsp->GetCollect("name") ;
			m_hEditName->SetWindowText(_bstr_t(v)) ;

			v = m_Rsp->GetCollect("spec") ;
			m_hEditGg->SetWindowText(_bstr_t(v)) ;

			v = m_Rsp->GetCollect("shortmane") ;
			m_hEditZjm->SetWindowText(_bstr_t(v)) ;

			v = m_Rsp->GetCollect("defaultprice") ;
			m_hEditMrjg->SetWindowText(_bstr_t(v)) ;

			v = m_Rsp->GetCollect("manufacturer") ;
			m_hEditCj->SetWindowText(_bstr_t(v)) ;

			v = m_Rsp->GetCollect("memo") ;
			m_hEditBz->SetWindowText(_bstr_t(v)) ;

			m_Rsp->MoveNext() ;
		}
	}
	catch(_com_error & e) 
	{
		char m_szTmp[1024] ;
		sprintf(m_szTmp, "执行==>%s<==, 数据库操作失败,错误原因：%s\n",m_szSql, LPCTSTR(e.Description()));
	}
}

void CDatabase::UpdateSpxxData(char *m_szZjm, char *m_szName, char *m_szID, char *m_szGg, char *m_szMrjg, char *m_szCj, char *m_szBz)
{
	char m_szSql[512] ;
	_variant_t v(0L) ;
	sprintf(m_szSql, "select * from tb_machandiseinfo where id = '%s'", m_szID) ;
	_RecordsetPtr m_Rsp ;
	try{
		m_Rsp = m_Connection->Execute(_bstr_t(m_szSql), &v, adCmdText) ;
		if(!m_Rsp->GetadoEOF())
		{//存在数据，更新
			sprintf(m_szSql, "update tb_machandiseinfo set name = '%s', spec = '%s', \
				shortmane = '%s', defaultprice = %s, manufacturer = '%s', memo = '%s' \
				where ID = '%s'", m_szName,m_szGg,m_szZjm,m_szMrjg,m_szCj,m_szBz,m_szID) ;
			m_Connection->Execute(_bstr_t(m_szSql), &v, adCmdText) ;
		}
		else
		{//不存在数据，增加
			sprintf(m_szSql, "insert into tb_machandiseinfo (ID, name, spec, shortmane, defaultprice, manufacturer, memo) \
				values ('%s', '%s', '%s', '%s', %s, '%s', '%s')" ,m_szID, m_szName,m_szGg,m_szZjm,m_szMrjg,m_szCj,m_szBz) ;
			m_Connection->Execute(_bstr_t(m_szSql), &v, adCmdText) ;
		}
	}
	catch(_com_error & e) 
	{
		char m_szTmp[1024] ;
		sprintf(m_szTmp, "执行==>%s<==, 数据库操作失败,错误原因：%s\n",m_szSql, LPCTSTR(e.Description()));
	}
}

void CDatabase::ListKcglToCtrl(CListCtrl *m_hListCtrl)
{
	m_hListCtrl->DeleteAllItems() ;

	_variant_t v(0L) ;
	_RecordsetPtr m_Rsp ;
	char m_szSql[512] ;
	sprintf(m_szSql, "select * from tb_merchandisestorage") ;
	try{					
		m_Rsp = m_Connection->Execute(_bstr_t(m_szSql), &v, adCmdText) ;
		while(!m_Rsp->GetadoEOF())
		{
			m_hListCtrl->InsertItem(0, "") ;

			v = m_Rsp->GetCollect("merchandiseID") ;
			m_hListCtrl->SetItemText(0, 0, _bstr_t(v)) ;

			v = m_Rsp->GetCollect("storagename") ;
			m_hListCtrl->SetItemText(0, 1, _bstr_t(v)) ;

			v = m_Rsp->GetCollect("Storagenum") ;
			m_hListCtrl->SetItemText(0, 2, _bstr_t(v)) ;

			m_Rsp->MoveNext() ;
		}
	}
	catch(_com_error & e) 
	{
		char m_szTmp[1024] ;
		sprintf(m_szTmp, "执行==>%s<==, 数据库操作失败,错误原因：%s\n",m_szSql, LPCTSTR(e.Description()));
	}
}

void CDatabase::EditKcToCtrl(char *merchandiseID, CEdit *m_hEditSpbh, CEdit *m_hEditKcmc, CEdit *m_hEditKcsl)
{
	_variant_t v(0L) ;
	_RecordsetPtr m_Rsp ;
	char m_szSql[512] ;
	sprintf(m_szSql, "select * from tb_merchandisestorage where merchandiseID = '%s'", merchandiseID) ;
	try{
		
		m_Rsp = m_Connection->Execute(_bstr_t(m_szSql), &v, adCmdText) ;
		while(!m_Rsp->GetadoEOF())
		{
			v = m_Rsp->GetCollect("merchandiseID") ;
			m_hEditSpbh->SetWindowText(_bstr_t(v)) ;

			v = m_Rsp->GetCollect("storagename") ;
			m_hEditKcmc->SetWindowText(_bstr_t(v)) ;

			v = m_Rsp->GetCollect("Storagenum") ;
			m_hEditKcsl->SetWindowText(_bstr_t(v)) ;

			m_Rsp->MoveNext() ;
		}
	}
	catch(_com_error & e) 
	{
		char m_szTmp[1024] ;
		sprintf(m_szTmp, "执行==>%s<==, 数据库操作失败,错误原因：%s\n",m_szSql, LPCTSTR(e.Description()));
	}

}

void CDatabase::UpdateKcData(char *merchandiseID, char *storagename, char *Storagenum)
{
	UpdateKczjData(storagename) ;
	char m_szSql[512] ;
	_variant_t v(0L) ;
	sprintf(m_szSql, "select * from tb_merchandisestorage where merchandiseID = '%s'", merchandiseID) ;
	_RecordsetPtr m_Rsp ;
	try{
		m_Rsp = m_Connection->Execute(_bstr_t(m_szSql), &v, adCmdText) ;
		if(!m_Rsp->GetadoEOF())
		{//存在数据，更新
			sprintf(m_szSql, "update tb_merchandisestorage set storagename = '%s', \
				Storagenum = '%s' where merchandiseID = '%s'", 
				storagename, Storagenum, merchandiseID) ;
			m_Connection->Execute(_bstr_t(m_szSql), &v, adCmdText) ;
		}
		else
		{//不存在数据，增加
			sprintf(m_szSql, "insert into tb_merchandisestorage (storagename, Storagenum, merchandiseID) \
				values ('%s', '%s', '%s')" , storagename, Storagenum, merchandiseID) ;
			m_Connection->Execute(_bstr_t(m_szSql), &v, adCmdText) ;
		}
	}
	catch(_com_error & e) 
	{
		char m_szTmp[1024] ;
		sprintf(m_szTmp, "执行==>%s<==, 数据库操作失败,错误原因：%s\n",m_szSql, LPCTSTR(e.Description()));
	}
}

void CDatabase::UpdateKczjData(char *storagename)
{
	char m_szSql[512] ;
	_variant_t v(0L) ;
	sprintf(m_szSql, "select * from tb_storageinfo where storagename = '%s'", storagename) ;
	_RecordsetPtr m_Rsp ;
	try{
		m_Rsp = m_Connection->Execute(_bstr_t(m_szSql), &v, adCmdText) ;
		if(m_Rsp->GetadoEOF())
		{//不存在数据，增加
			sprintf(m_szSql, "insert into tb_storageinfo (storagename) \
				values ('%s')" , storagename) ;
			m_Connection->Execute(_bstr_t(m_szSql), &v, adCmdText) ;
		}
	}
	catch(_com_error & e) 
	{
		char m_szTmp[1024] ;
		sprintf(m_szTmp, "执行==>%s<==, 数据库操作失败,错误原因：%s\n",m_szSql, LPCTSTR(e.Description()));
	}
}

void CDatabase::ListStockApplyToCtrl(CListCtrl *m_hListCtrl)
{
	m_hListCtrl->DeleteAllItems() ;

	_variant_t v(0L) ;
	_RecordsetPtr m_Rsp ;
	char m_szSql[512] ;
	sprintf(m_szSql, "select * from tb_StockApply_main") ;
	try{		
		m_Rsp = m_Connection->Execute(_bstr_t(m_szSql), &v, adCmdText) ;
		while(!m_Rsp->GetadoEOF())
		{
			char m_szAppID[30+1] ;
			m_hListCtrl->InsertItem(0, "") ;

			v = m_Rsp->GetCollect("AppID") ;
			strcpy(m_szAppID, _bstr_t(v)) ;
			m_hListCtrl->SetItemText(0, 0, m_szAppID) ;

			v = m_Rsp->GetCollect("Department") ;
			m_hListCtrl->SetItemText(0, 1, _bstr_t(v)) ;

			v = m_Rsp->GetCollect("proposer") ;
			m_hListCtrl->SetItemText(0, 2, _bstr_t(v)) ;

			v = m_Rsp->GetCollect("billmaker") ;
			m_hListCtrl->SetItemText(0, 3, _bstr_t(v)) ;

			v = m_Rsp->GetCollect("memo") ;
			m_hListCtrl->SetItemText(0, 4, _bstr_t(v)) ;

//			v = m_Rsp->GetCollect("AppDate") ;
//			m_hListCtrl->SetItemText(0, 5, _bstr_t(v)) ;

			sprintf(m_szSql, "select * from tb_stockApp_sub where appID = '%s'", m_szAppID) ;
			_RecordsetPtr m_Rsp1 = m_Connection->Execute(_bstr_t(m_szSql), &v, adCmdText) ;
			if(!m_Rsp1->GetadoEOF())
			{
				v = m_Rsp1->GetCollect("merchandiseID") ;
				m_hListCtrl->SetItemText(0, 5, _bstr_t(v)) ;

				v = m_Rsp1->GetCollect("unitprice") ;
				m_hListCtrl->SetItemText(0, 6, _bstr_t(v)) ;

				v = m_Rsp1->GetCollect("sum") ;
				m_hListCtrl->SetItemText(0, 7, _bstr_t(v)) ;

				v = m_Rsp1->GetCollect("Paymoney") ;
				m_hListCtrl->SetItemText(0, 8, _bstr_t(v)) ;

				v = m_Rsp1->GetCollect("Rebate") ;
				m_hListCtrl->SetItemText(0, 9, _bstr_t(v)) ;

				v = m_Rsp1->GetCollect("purpose") ;
				m_hListCtrl->SetItemText(0, 10, _bstr_t(v)) ;