jmdb.cpp

ado and access operatiion such as insert delet update

// JmDB.cpp: implementation of the CJmDB class.
//
//////////////////////////////////////////////////////////////////////

#include "stdafx.h"
#include "jmChat_S.h"
#include "JmDB.h"

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

//////////////////////////////////////////////////////////////////////
// Construction/Destruction
//////////////////////////////////////////////////////////////////////
//jm::StdAfx.h中的最后插入
//#import "c:\program files\common files\system\ado\msado15.dll" no_namespace rename("EOF","adoEOF")
//jm 插头文件：：#include   <comdef.h>
CJmDB::CJmDB()
{

}

CJmDB::~CJmDB()
{

}
//BOOL CJmDB::Seldb();
/*{	*/
//		int nItem;
//	_variant_t vUsername,vBirthday,vID,vOld;
//	try
//	{
//		m_pRecordset.CreateInstance("ADODB.Recordset");
//		m_pRecordset->Open("SELECT * FROM users",_variant_t((IDispatch*)theApp.m_pConnection,true),adOpenStatic,adLockOptimistic,adCmdText);
//		m_bSuccess = TRUE;
//		while(!m_pRecordset->adoEOF)
//		{
//			vID = m_pRecordset->GetCollect("ID");
//			vUsername = m_pRecordset->GetCollect("username");
//			vOld = m_pRecordset->GetCollect("old");
//			vBirthday = m_pRecordset->GetCollect("birthday");
//			nItem=m_userlist.InsertItem(0xffff,(_bstr_t)vID);
//			m_userlist.SetItem(nItem,1,1,(_bstr_t)vUsername,NULL,0,0,0);
//			m_userlist.SetItem(nItem,2,1,(_bstr_t)vOld,NULL,0,0,0);
//			m_userlist.SetItem(nItem,3,1,(_bstr_t)vBirthday,NULL,0,0,0);
//
//			m_pRecordset->MoveNext();
//		}
//	}
//	catch(_com_error e)///捕捉异常
//	{
//		AfxMessageBox("读取数据库失败!");///显示错误信息
//	}

/*}*/
extern CJmChat_SApp theApp;
BOOL CJmDB::Seldb(CString strID, CString &strPwd)
{
	//int nItem;
	_variant_t vID,vName,vPwd;
	try
	{
		m_pRecordset.CreateInstance("ADODB.Recordset");
		m_pRecordset->Open("SELECT * FROM users",_variant_t((IDispatch*)theApp.m_pConnection,true),adOpenStatic,adLockOptimistic,adCmdText);
		m_bSuccess = TRUE;
		while(!m_pRecordset->adoEOF)
		{
			var = m_pRecordset->GetCollect("ID");
			var = m_pRecordset->GetCollect("username");
			var = m_pRecordset->GetCollect("old");
			var = m_pRecordset->GetCollect("birthday");
			//JM ::(_bstr_t)vID是将＿varient_t  转换为字符型.
			

			//m_pRecordset->MoveNext();
		}
	}
	catch(_com_error e)//捕捉异常
	{
		AfxMessageBox("读取数据库失败!");//显示错误信息
	}


	return TRUE;
}
//jm:将下面函数创建到C*App类中，并在初始化当中调中

BOOL CJmDB::Initdb()
{/*
	AfxEnableControlContainer();
	AfxOleInit();///初始化COM库
	////////////连接数据库//////////////
	HRESULT hr;
	try
	{
		hr=m_pConnection.CreateInstance("ADODB.Connection");
		///创建Connection对象//反回零表示成功
		if(SUCCEEDED(hr))
		{
		hr = m_pConnection->Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=us.mdb","","",adModeUnknown);///连接数据库
		///上面一句中连接字串中的Provider是针对ACCESS2000环境的，对于ACCESS97,需要改为:Provider=Microsoft.Jet.OLEDB.3.51;  }
		}
	}
	catch(_com_error e)///捕捉异常
	{
		CString err;
		err.Format("连接数据库失败!\r\n错误信息:%s",e.ErrorMessage());
		AfxMessageBox(err);///显示错误信息
		return FALSE;
	} 
*/
	return TRUE;
}

BOOL CJmDB::Insertdb(CUserInfo &m_info)
{
	if(bExist(m_info.uID)==TRUE)
	{
		AfxMessageBox("此数据存在，不能插入");
		return FALSE;
	}
	CString strSQL;
	strSQL="INSERT INTO userInfo(uID,uName,uPwd) VALUES(\""+m_info.uID+"\",\""+
			m_info.uName+"\",\""+m_info.uPwd+"\")";
	var=strSQL;
	_variant_t varTmp;
	theApp.m_pConnection->Execute((_bstr_t)var,&varTmp,adCmdText);
	//theApp.m_pConnection->Execute("SELECT * FROM userInfo",&varTmp,adCmdText);
		/*
	var = m_info.uID;
	m_pRecordset->PutCollect("uID",var);
	var = m_info.uName;
	m_pRecordset->PutCollect("uName",var);
	var = m_info.uPwd;
	m_pRecordset->PutCollect("uPwd",var);*/
	return TRUE;
}

BOOL CJmDB::LookupInfo(CUserInfo &m_info)
{
	if(bExist(m_info.uID)==FALSE)
	{
		AfxMessageBox("此数据不存在");
		return FALSE;
	}
	//int nItem;
	CString strSQL;
	strSQL="SElECT * FROM userInfo WHERE uID=\""+m_info.uID+"\"";
	var=strSQL;
	try
	{
		m_pRecordset.CreateInstance("ADODB.Recordset");
		m_pRecordset->Open(var,_variant_t((IDispatch*)theApp.m_pConnection,true),adOpenStatic,adLockOptimistic,adCmdText);
		//m_pRecordset->Open("SELECT * FROM userInfo",_variant_t((IDispatch*)theApp.m_pConnection,true),adOpenStatic,adLockOptimistic,adCmdText);
		m_bSuccess=TRUE;
		if(!m_pRecordset->adoEOF)
		{
			var = m_pRecordset->GetCollect("uName");
			m_info.uName.Format("%s",(const   char*)(_bstr_t)var);
			var = m_pRecordset->GetCollect("uPwd");
			m_info.uPwd.Format("%s",(const   char*)(_bstr_t)var);
			//JM ::(_bstr_t)vID是将＿varient_t  转换为字符型.
			//m_pRecordset->MoveNext();
			
			
			
		}
	}
	catch(_com_error e)//捕捉异常
	{
		AfxMessageBox("读取数据库失败!");//显示错误信息
	}
	
	return TRUE;
	
}

BOOL CJmDB::bExist(CString &uID)
{
	CString strSQL;
	strSQL="SElECT * FROM userInfo WHERE uID=\""+uID+"\"";
	var=strSQL;
	try
	{
		m_pRecordset.CreateInstance("ADODB.Recordset");
		m_pRecordset->Open(var,_variant_t((IDispatch*)theApp.m_pConnection,true),adOpenStatic,adLockOptimistic,adCmdText);
		//m_pRecordset->Open("SELECT * FROM userInfo",_variant_t((IDispatch*)theApp.m_pConnection,true),adOpenStatic,adLockOptimistic,adCmdText);
		m_bSuccess=TRUE;
		if(m_pRecordset->adoEOF)
			return FALSE;
		else
			return TRUE;
	}
	catch(_com_error e)//捕捉异常
	{
		AfxMessageBox("读取数据库失败!");//显示错误信息
	}
	
	return TRUE;
}

BOOL CJmDB::ModifyDb(CUserInfo &info)
{
	if(bExist(info.uID)==FALSE)
	{
		AfxMessageBox("此数据不存在，不能修改");
		return FALSE;
	}
	CString strSQL;//UPDATE <table> set .. where 
	strSQL="UPDATE userInfo SET uName=\""+info.uName+"\",uPwd=\""+info.uPwd+"\" WHERE uID=\""+info.uID+"\"";
	var=strSQL;
	_variant_t varTmp;
	theApp.m_pConnection->Execute((_bstr_t)var,&varTmp,adCmdText);

	return TRUE;
}

BOOL CJmDB::DelDb(CString &uID)
{
	if(bExist(uID)==FALSE)
	{
		AfxMessageBox("此数据不存在，不能删除");
		return FALSE;
	}
	CString strSQL;//DELETE FROM <table> where .. 
	strSQL="DELETE FROM userInfo WHERE uID=\""+uID+"\"";
	var=strSQL;
	_variant_t varTmp;
	theApp.m_pConnection->Execute((_bstr_t)var,&varTmp,adCmdText);

	return TRUE;
}

BOOL CJmDB::getIP(CString &uID, CString &uIP)
{
	if(bExist(uID)==FALSE)
	{
		AfxMessageBox("非法操作，此用户ID不存在");
		return FALSE;
	}
	CString strSQL;
	strSQL="SElECT uIP FROM userInfo WHERE uID=\""+uID+"\"";
	var=strSQL;
	try
	{
		m_pRecordset.CreateInstance("ADODB.Recordset");
		m_pRecordset->Open(var,_variant_t((IDispatch*)theApp.m_pConnection,true),adOpenStatic,adLockOptimistic,adCmdText);
		//m_pRecordset->Open("SELECT * FROM userInfo",_variant_t((IDispatch*)theApp.m_pConnection,true),adOpenStatic,adLockOptimistic,adCmdText);
		m_bSuccess=TRUE;
		if(!m_pRecordset->adoEOF)
		{
			var = m_pRecordset->GetCollect("uIP");
			uIP.Format("%s",(const   char*)(_bstr_t)var);
			//JM ::(_bstr_t)vID是将＿varient_t  转换为字符型.
			//m_pRecordset->MoveNext();
			
			
			
		}
	}
	catch(_com_error e)//捕捉异常
	{
		AfxMessageBox("读取数据库失败!");//显示错误信息
	}
	
	return TRUE;

	return TRUE;
}

BOOL CJmDB::SetIP(CString &uID, CString &uIP)
{
	if(bExist(uID)==FALSE)
	{
		AfxMessageBox("非法操作，此用户ID不存在");
		return FALSE;
	}
	CString strSQL;//UPDATE <table> set .. where 
	strSQL="UPDATE userInfo SET uIP=\""+uIP+"\" WHERE uID=\""+uID+"\"";
	var=strSQL;
	_variant_t varTmp;
	theApp.m_pConnection->Execute((_bstr_t)var,&varTmp,adCmdText);

	return TRUE;
}

BOOL CJmDB::DeLIP(CString &uID)
{
	if(bExist(uID)==FALSE)
	{
		AfxMessageBox("非法操作，此用户ID不存在");
		return FALSE;
	}
	CString strSQL;//UPDATE <table> set .. where 
	strSQL="UPDATE userInfo SET uIP=\"\" WHERE uID=\""+uID+"\"";
	var=strSQL;
	_variant_t varTmp;
	theApp.m_pConnection->Execute((_bstr_t)var,&varTmp,adCmdText);

	return TRUE;
}