//版权有所:李巧平 2003年1月份 电子邮件:liqiaoping@163.com 个人主页:http://mengyuworkroom.y365.com
//封装类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, (_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)
{
str
评论0