cpingdatabase.h

信息提醒小秘书软件的源代码

//版权有所：李巧平    2003年1月份  电子邮件：[e-mail]  个人主页：http://mengyuworkroom.y365.com  QQ:5542376
//封装类CPingDatabase是操作数据库的类，能插入、修改、查看、删除ACCESS数据库、SQLSERVER及所有的ODBC得用数据源来连接的数据库。
//以下代码在VC++6.0 ON WINDOWS2000 Professional上测试通过


//使用来类时出现一个警告，不用管它，那是微软的一个动态的原因。
//说明，以下方法返回值都为int型，具体含义如下列表：
//  正常：操作的实际记录数
//  -1  ：成功
//  -10 ：失败
//  -5　：参数类型不符合要求
//  -6　：未初始化
//  -7　：数据库未连接
#import "c:\program files\common files\system\ado\msado15.dll" no_namespace rename("EOF","adoEOF")
class CPingDatabase{
public:
	_ConnectionPtr	m_pConnection;

	//初始化组件，成功为1，失败为-5
	//只能初始化一次，否败会出错。
	int	_init()
	{
		return AfxOleInit()>0 ? -1:-10;
	}

	//连接数据库，type支持file和odbc和sqlserver，且为小写。为sqlserver时，message项传递ip地址，source为库
	int	_connectDatabase(char* type, char* source, char* user, char* password, char* message)
	{
		//先关闭连接
		_closeDatabase();
		try{	HRESULT	hr;
			CString	sql;
			if( strcmp( type, "file") == 0)
				sql.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=%s" ,source);
			else if( strcmp( type, "odbc")==0 )
				sql.Format("Data Source=%s;UID=%s;PWD=%s;",source, user, password);
			else if( strcmp( type, "sqlserver") == 0)
				sql.Format("driver={SQL Server};Server=%s;DATABASE=%s;UID=%s;PWD=%s", message, source, user, password);
			else
			{
				strcpy( message, "类型应该为type,odbc,sqlserver其中一种。");
				return -5;
			}
			hr = this->m_pConnection.CreateInstance("ADODB.Connection");
			if(SUCCEEDED(hr))
			{
				if( strcmp( type, "file") == 0)
					hr = this->m_pConnection->Open( (_bstr_t)sql, "", "",adModeUnknown);//hr = this->m_pConnection->Open( (_bstr_t)sql, (_bstr_t)user, (_bstr_t)password,adModeUnknown);
				else
					hr = this->m_pConnection->Open( (_bstr_t)sql,"","",adModeUnknown);
			}
			else
			{
				strcpy( message, "为初始化组件");
				return -6;
			}
		}catch(_com_error e)
		{
			strcpy( message, e.ErrorMessage() );
			return -10;
		}
		strcpy( message, "成功连接数据库！");
		return -1;
	}

	//通过到指定的表table中读出符合条件wide，指定的字段Fields到Values中，返回操作记录数
	int		_selectDataFromDatabase(char* tablename, char* wide, CStringArray* Fields, CStringArray* Values, int counts, char* message)
	{
		//检查是否已经连接
		if( !_checkDatabase() )
		{
			strcpy(message,"数据库还没有连接好，无效的数据操作！");
			return -7;
		}
		int i=0;
		try{	_RecordsetPtr	m_pRecordset;
			_variant_t	vTemp;
			//生成SQL语句
			CString		sql,temp;
			temp.Format("select ");
			for(i=0; i<Fields->GetSize(); i++)
				temp = temp + Fields->GetAt(i) +",";
			temp.TrimRight(",");
			sql.Format("%s from %s %s", temp, tablename, wide);
		
			m_pRecordset.CreateInstance("ADODB.Recordset");
			m_pRecordset->Open( (_variant_t)sql,_variant_t((IDispatch*)this->m_pConnection,true),adOpenStatic,adLockOptimistic,adCmdText);
			i=0;
			while( (!m_pRecordset->adoEOF) && (i < counts))
			{
				for(int j=0; j<Fields->GetSize(); j++)
				{
					vTemp = m_pRecordset->GetCollect(_variant_t( Fields->GetAt(j) ));
					if(vTemp.vt == VT_NULL)
						(Values+i)->Add("");
					else
						(Values+i)->Add((LPCTSTR)(_bstr_t)vTemp);
				}
				i++;
				m_pRecordset->MoveNext();
			}
			m_pRecordset->Close();
		}catch(_com_error e)
		{
			strcpy( message, e.ErrorMessage());
			return -10;
		}
		return i;
	}

	//成功插入，返回实际插入条数
	int		_insertDataToDatabase(char* tablename, CStringArray* Fields, CStringArray* Values, int counts, char* message)
	{
		//检查是否已经连接
		if( !_checkDatabase() )
		{
			strcpy(message,"数据库还没有连接好，无效的数据操作！");
			return -7;
		}
		int i=0;
		try{	_RecordsetPtr	m_pRecordset;
			_variant_t	vTemp;
			//生成SQL语句
			CString		sql;
			sql.Format("select * from %s",tablename);

			m_pRecordset.CreateInstance("ADODB.Recordset");
			m_pRecordset->Open( (_variant_t)sql,_variant_t((IDispatch*)this->m_pConnection,true),adOpenStatic,adLockOptimistic,adCmdText);
			for(i=0; i<counts; i++)
			{
				m_pRecordset->AddNew();
				for(int j=0; j<Fields->GetSize(); j++)
				{
					if( (Values+i)->GetAt(j).IsEmpty() || (Values+i)->GetAt(j)=="")
						vTemp.vt = VT_NULL;
					else
						vTemp = (_variant_t)(Values+i)->GetAt(j);
					m_pRecordset->PutCollect( (_variant_t)Fields->GetAt(j),  vTemp);
				}
			}
			m_pRecordset->AddNew();
//			m_pRecordset->Close();
		}catch(_com_error e)
		{
			strcpy( message, e.ErrorMessage());
			return -10;
		}
		return i;
	}

	//修改一组符合条件的数据，成功则返回修改的数目，失败为0，出错为-1，不支持修改数组型
	int 	_updateRecord(char* tablename,char* wide,  CStringArray *Fields, CStringArray *Values, int counts, char* message)
	{
		//检查是否已经连接
		if( !_checkDatabase() )
		{
			strcpy(message,"数据库还没有连接好，无效的数据操作！");
			return -7;
		}
		int i=0;
		try
		{	CString sql,temp;
			temp = "select ";
			for(int j=0;j< Fields->GetSize() ;j++)
				temp = temp + Fields->GetAt(j) + ",";
			temp.TrimRight(",");
			sql.Format("%s from %s %s", temp, tablename, wide);
			_RecordsetPtr m_pRecordset;
			m_pRecordset.CreateInstance("ADODB.Recordset");
			m_pRecordset->Open( (_variant_t)sql,_variant_t((IDispatch*)this->m_pConnection,true),adOpenStatic,adLockOptimistic,adCmdText);
			while( (!m_pRecordset->adoEOF) && (i < counts))///这里为什么是adoEOF而不是EOF呢?还记得rename("EOF","adoEOF")这一句吗?
			{
				for(int j=0; j<Fields->GetSize(); j++)
					m_pRecordset->PutCollect(_variant_t( Fields->GetAt(j) ),_variant_t( Values->GetAt(j) ));//修改
				m_pRecordset->Update();///保存到库中
				m_pRecordset->MoveNext();///移到下一条记录
				i++;
			}
			m_pRecordset->Close();
		}catch(_com_error e)///捕捉异常
		{
			strcpy( message, e.ErrorMessage());
			return -10;
		}
		return i;
	}

	//删除一组合条件的数据，成功返回删除的数目，失败为0，出错为-1
	int 	_deleteRecord(char* tablename, char* wide, int counts, char* message)
	{
		//检查是否已经连接
		if( !_checkDatabase() )
		{
			strcpy( message,"数据库还没有连接好，无效的数据操作！");
			return -7;
		}
		int i=0;
		try
		{	CString sql;
			sql.Format("select * from %s %s",tablename,wide);
			_RecordsetPtr m_pRecordset;
			m_pRecordset.CreateInstance("ADODB.Recordset");
			m_pRecordset->Open( (_variant_t)sql,_variant_t((IDispatch*)this->m_pConnection,true),adOpenStatic,adLockOptimistic,adCmdText);
			while( (!m_pRecordset->adoEOF) && (i < counts))///这里为什么是adoEOF而不是EOF呢?还记得rename("EOF","adoEOF")这一句吗?
			{
				m_pRecordset->Delete(adAffectCurrent);///删除当前记录
				m_pRecordset->MoveNext();
				i++;
			}
			m_pRecordset->Close();
		}catch(_com_error e)///捕捉异常
		{
			strcpy( message,e.ErrorMessage());
			return -10;
		}
		return i;
	}

	//生存ID，若成功，则ID在message中,Field为数据插入日期字段
	int	_selectRecordId(char* tablename, char* Field, char* message)
	{
		//检查是否已经连接
		if( !_checkDatabase() )
		{
			strcpy(message,"数据库还没有连接好，无效的数据操作！");
			return -7;
		}
		CTime	nowtime = CTime::GetCurrentTime();
		CString temp = nowtime.Format(_T("%Y-%m-%d"));
		try{	_RecordsetPtr	m_pRecordset;
			_variant_t	vTemp;
			CString		sql;
			sql.Format("select count(*) from %s where %s like '%s%%'", tablename, Field, temp);
			m_pRecordset.CreateInstance("ADODB.Recordset");
			m_pRecordset->Open( (_variant_t)sql,_variant_t((IDispatch*)this->m_pConnection,true),adOpenStatic,adLockOptimistic,adCmdText);
			if(!m_pRecordset->adoEOF)
			{
				vTemp = m_pRecordset->GetCollect(_variant_t((long)0));
				if(vTemp.vt == VT_NULL)
					temp = "0";
				else
					temp = (LPCTSTR)(_bstr_t)vTemp;
			}
			m_pRecordset->Close();
			sql.Format("%6d",atoi(temp)+1);
			temp = nowtime.Format(_T("%Y%m%d"));
			temp = temp + sql;
			temp.Replace(" ","0");
		}catch(_com_error e)
		{
			strcpy( message, e.ErrorMessage());
			return -10;
		}
		strcpy( message, temp);
		return -1;
	}

	//读出符合条件的记录数
	int	_selectCountsFromDatabase(char* tablename, char* wide, char* message)
	{
		//检查是否已经连接
		if( !_checkDatabase() )
		{
			strcpy(message,"数据库还没有连接好，无效的数据操作！");
			return -7;
		}
		int i=0;
		try{	_RecordsetPtr	m_pRecordset;
			_variant_t	vTemp;
			CString		sql;
			sql.Format("select count(*) from %s %s", tablename, wide);
			m_pRecordset.CreateInstance("ADODB.Recordset");
			m_pRecordset->Open( (_variant_t)sql,_variant_t((IDispatch*)this->m_pConnection,true),adOpenStatic,adLockOptimistic,adCmdText);
			if(!m_pRecordset->adoEOF)
			{
				vTemp = m_pRecordset->GetCollect(_variant_t((long)0));
				if(vTemp.vt == VT_NULL)
					i = 0;
				else
					i = atoi( (LPCTSTR)(_bstr_t)vTemp);
			}
			m_pRecordset->Close();
		}catch(_com_error e)
		{
			strcpy( message, e.ErrorMessage());
			return -10;
		}
		return i;
	}

	//检查数据库是否已经连接好
	bool	_checkDatabase()
	{
		//检查是否已经连接
		try
		{
			if((this->m_pConnection)->State)
				return true;
		}catch(...){;}
		return false;
	}

	//关闭数据库
	void	_closeDatabase()
	{
		try
		{
			if((this->m_pConnection)->State)
				this->m_pConnection->Close(); ///如果已经打开了连接则关闭它
		}catch(...){;}
	}
	
};