accessdb.cpp

论坛中有许多操作Access数据库的介绍、说明

	m_DataSet.GetFieldInfo(nIndex,FieldInfo,AFX_DAO_ALL_INFO);
	sprintf(OutValue,"%s",(const char *)(LPCTSTR)FieldInfo.m_strName);
//	strcpy(OutValue,(const char *)(LPCTSTR)FieldInfo.m_strName);

	return 0;
}


int CAccessDB::GetDataSetRowCount(int ForceAll,BOOL DoException)
{
	int AllCount=0;

	if(!m_DataSet.IsOpen())
		return -1;

/* 为了获取准确的记录条数，需要遍历记录。这种方法实际上效率是很低的，你可以尝试使用其他的方式来实现。*/
	if(!DoException)
	{
		if(!ForceAll)
			return m_DataSet.GetRecordCount();

		if(!m_DataSet.IsBOF())
			m_DataSet.MoveFirst();
		m_DataSet.MoveLast();
		AllCount=m_DataSet.GetRecordCount();
		m_DataSet.MoveFirst();
		return AllCount;
	}
	else
	{
		if(!ForceAll)
		{
			try
			{
				AllCount=m_DataSet.GetRecordCount();
			}
			catch(CDaoException *e)
			{
				if(e->m_pErrorInfo->m_lErrorCode==3021)
				{
					e->Delete();
					return DB_DATANOTEXIST;
				}
				else
				{
					e->Delete();
					return DB_OTHERERROR;
				}
			}
			return AllCount;
		}

		try
		{
			if(!m_DataSet.IsBOF())
				m_DataSet.MoveFirst();
			m_DataSet.MoveLast();
			AllCount=m_DataSet.GetRecordCount();
			m_DataSet.MoveFirst();
		}
		catch(CDaoException *e)
		{
			if(e->m_pErrorInfo->m_lErrorCode==3021)
			{
				e->Delete();
				return DB_DATANOTEXIST;
			}
			else
			{
				e->Delete();
				return DB_OTHERERROR;
			}
		}
		return AllCount;
	}
}

int CAccessDB::GetDataSetFieldValue(int RowIndex, int iFieldIndex, char *OutValue)
{
	memset(OutValue,0,sizeof(OutValue));
	if(!m_DataSet.IsOpen())
		return 0;

	CString strVar;
	int iCurrent=0;
/*
	if(m_DataSet.CanBookmark())
	{
		strVar=Str2Variant(m_DataSet.GetBookmark());
		iCurrent=atoi(strVar.GetBuffer(0));
	}
*/
	m_DataSet.MoveFirst();
	m_DataSet.Move(RowIndex);
	if(m_DataSet.IsEOF())
	{
		OutValue=NULL;
		return 0;
	}

	strVar=Str2Variant(m_DataSet.GetFieldValue(iFieldIndex));
	sprintf(OutValue,"%s",strVar.GetBuffer(0));

	return 1;
}

int CAccessDB::GetDataSetFieldValue(int RowIndex, char *FieldName, char *OutValue)
{
/*
	if((strcmp(DSName,m_DSName))!=0)
	{
		OutValue=NULL;
		return -1;
	}
*/
	int iCount=m_DataSet.GetFieldCount();

	for(int i=0;i<iCount;i++)
	{
		char szFieldVal[MAXFIELDNAME];
		memset(szFieldVal,0,sizeof(szFieldVal));

		GetDataSetFieldName(i,szFieldVal);
		if(!strcmp(FieldName,szFieldVal))
			return GetDataSetFieldValue(RowIndex,i,OutValue);
	}

	OutValue=NULL;
	return -1;
}

void CAccessDB::GetCurrentDateSetName(char *OutValue)
{
	sprintf(OutValue,"%s",m_DSName);
	
}

BOOL CAccessDB::DeleteDataSet(char *DSName)
{
	if(DSName!=NULL)
	{
		if((strcmp(DSName,m_DSName))!=0)
			return FALSE;
	}

	if(m_DataSet.IsOpen())
	{
//		m_DataSet.Delete();
		m_DataSet.Close();
		return TRUE;
	}

	return TRUE;
}

void CAccessDB::MoveFirst()
{
	m_DataSet.MoveFirst();
}

BOOL CAccessDB::IsEOF()
{
	return m_DataSet.IsEOF();
}

void CAccessDB::MoveNext()
{
	m_DataSet.MoveNext();
}

int CAccessDB::GetDataSetFieldValue(int iFieldIndex, char *OutValue)
{
	if(!m_DataSet.IsOpen())
		return -1;

	CString strVar;

	strVar=Str2Variant(m_DataSet.GetFieldValue(iFieldIndex));
	sprintf(OutValue,"%s",strVar.GetBuffer(0));

	return 0;

}

char * CAccessDB::GetTableName(int TbIndex)
{
	return m_TableName[TbIndex];
}

char * CAccessDB::GetFieldName(int TableIndex, int FieldIndex)
{
	if(TableIndex>GetTableCount()||FieldIndex>MAXFIELD)
		return NULL;

	return m_FieldName[TableIndex][FieldIndex];
}

char * CAccessDB::GetFieldName(char *TableName, int FieldIndex)
{
	int iTableCount;
	iTableCount=GetTableCount();

	for(int i=0;i<iTableCount;i++)
	{
		if(!strcmp(TableName,m_TableName[i]))
			return m_FieldName[i][FieldIndex];
	}

	return NULL;
}

//////////////////////////////////////////////////////////////////////
//用字段名换标签
char * SwitchName2Lab(char *Name,char *SectionName,char *FileName,char *AppName)
{
	char buf[50];
	CString strFile,PathName;
	unsigned char obPath[512];
	DWORD nSize=512;

	memset(buf,0,sizeof(buf));
	int ret=0;
	if(FileName==NULL)
	{
		strFile.Format("%s",NAMETB);
		strFile.Replace(".\\","");
		memset(obPath,0,sizeof(obPath));
#ifdef _MYSYSTEMAPI_
		GetCurAppExecPath((char *)obPath,AppName);
#else
		GetCurrentDirectory(512,(char *)obPath);
#endif
		PathName.Format("%s\\%s",obPath,strFile);
	}
	else
		PathName.Format("%s",FileName);

	
	
	memset(buf,0,sizeof(buf));
	if(SectionName==NULL)
	{
		if((ret=GetPrivateProfileString("TableName",Name,"",buf,MAXFIELDNAME,PathName))>0)
		{
			memset(Name,0,sizeof(Name));
			sprintf(Name,"%s",buf);
			return Name;
		}
		//D:\DEV-ENV\CompareTrigger
		else if((ret=GetPrivateProfileString("FieldName",Name,"",buf,MAXFIELDNAME,PathName))>0)
		{
			memset(Name,0,sizeof(Name));
			sprintf(Name,"%s",buf);
			return Name;
		}
		else if((ret=GetPrivateProfileString("DepRef",Name,"",buf,MAXFIELDNAME,PathName))>0)
		{
			memset(Name,0,sizeof(Name));
			sprintf(Name,"%s",buf);
			return Name;
		}

		else
			return Name;
	}
	else
	{
		if((ret=GetPrivateProfileString(SectionName,Name,"",buf,MAXFIELDNAME,PathName))>0)
		{
			memset(Name,0,sizeof(Name));
			sprintf(Name,"%s",buf);
			return Name;
		}
		else
			return Name;
	}

}


//用标签换字段名
char * SwitchLab2Name(char *Lab,char *SectionName)
{
	char buf[50];
	memset(buf,0,sizeof(buf));
	int ret=0;

	if(SectionName==NULL)
	{
		if((ret=GetPrivateProfileString("TableLab",Lab,"",buf,MAXFIELDNAME,NAMELB))>0)
		{
			memset(Lab,0,sizeof(Lab));
			sprintf(Lab,"%s",buf);
			return Lab;
		}
		else if((ret=GetPrivateProfileString("FieldLab",Lab,"",buf,MAXFIELDNAME,NAMELB))>0)
		{
			memset(Lab,0,sizeof(Lab));
			sprintf(Lab,"%s",buf);
			return Lab;
		}
		else if((ret=GetPrivateProfileString("DepRef",Lab,"",buf,MAXFIELDNAME,NAMELB))>0)
		{
			memset(Lab,0,sizeof(Lab));
			sprintf(Lab,"%s",buf);
			return Lab;
		}
		else 
			return Lab;
	}
	else
	{
		if((ret=GetPrivateProfileString(SectionName,Lab,"",buf,MAXFIELDNAME,NAMELB))>0)
		{
			memset(Lab,0,sizeof(Lab));
			sprintf(Lab,"%s",buf);
			return Lab;
		}
		else
			return Lab;
	}
}

/* 当Access数据库文件变得庞大，且执行SQL很慢时，可以用这个函数来优化数据库*/
void CAccessDB::OptimizeDB()
{
	try
	{
		CDaoWorkspace::CompactDatabase(_T(m_DBName),_T(".\\tmp.mdb"),dbLangGeneral,0);
		char szCommand[100];
		memset(szCommand,0,sizeof(szCommand));
		sprintf(szCommand,"del %s",m_DBName);
		system(szCommand);
		memset(szCommand,0,sizeof(szCommand));
		sprintf(szCommand,"rename tmp.mdb %s",m_DBName);
		system(szCommand);
	}
	catch(CDaoException *e)
	{
//		AfxMessageBox(e->m_pErrorInfo->m_strDescription);
		e->Delete();
	}

}

int CAccessDB::ConnectDB(const char *DsnName, const char *UserName, const char *Password)
{
	CString ConnStr,DSNName;
	int ret=0;

	if(DsnName==NULL)
		DSNName.Format("%s",m_DBName);
	else
		DSNName.Format("%s",DsnName);

	DSNName.Replace(".mdb","");


	ConnStr.Format("DSN=%s;DBQ=%s\\%s;DEFAULTDIR=D:\\;UID=%s;PWD=%s",
				DSNName.GetBuffer(0),
				m_DBPath,
				m_DBName,
				UserName,
				Password);


	ret=SQLConfigDataSource(NULL,ODBC_ADD_SYS_DSN,
						"Microsoft Access Driver (*.mdb)\0",
						ConnStr.GetBuffer(0));

	ret=GetLastError();

	return 1;

}

BOOL CAccessDB::IsDBOpen()
{
	return m_DB.IsOpen();
}

short CAccessDB::GetFieldType(char *TableName, char *FieldName)
{
	int iTableCount,iFieldCount;

	iTableCount=GetTableCount();

	for(int i=0;i<iTableCount;i++)
	{
		if(!strcmp(TableName,m_TableName[i]))
		{
			iFieldCount=GetFieldCount(TableName);
			for(int j=0;j<iFieldCount;j++)
			{
				if(!strcmp(FieldName,GetFieldName(i,j)))
					return m_FieldType[i][j];
			}
			break;
		}
	}

	return -1;
}

short CAccessDB::GetFieldType(char *TableName, int FieldIndex)
{
	int iTableCount;
	iTableCount=GetTableCount();

	for(int i=0;i<iTableCount;i++)
	{
		if(!strcmp(TableName,m_TableName[i]))
			return m_FieldType[i][FieldIndex];
	}

	return -1;

}

#ifdef _MYSYSTEMAPI_

#endif


int CAccessDB::AddRecord(char *tbName, CStringArray &fldName, CStringArray &fldValues)
{
	// TODO: Add your command handler code here
	if(fldValues.GetSize()<=0)
		return 0;

	int i=0,j=0,len=0;

	unsigned char szSql[1500];
	unsigned char flds[500];
	unsigned char values[500];
	short nFieldType;

	if(fldName.GetSize()!=fldValues.GetSize())
		return 0;

	memset(flds,0,sizeof(flds));
	memset(values,0,sizeof(values));

	len=0;
	for(i=0;i<fldName.GetSize();i++)
	{
		if(!i)
			sprintf((char*)flds+len,"%s",fldName.GetAt(i));
		else
			sprintf((char*)flds+len,",%s",fldName.GetAt(i));
		len=strlen((char*)flds);
	}

	len=0;
	for(i=0;i<fldValues.GetSize();i++)
	{
		nFieldType=GetFieldType(tbName,fldName.GetAt(i).GetBuffer(0));
		if(!i)
		{
			if(nFieldType==dbText||nFieldType==dbMemo||nFieldType==dbDate)
				sprintf((char*)values+len,"'%s'",fldValues.GetAt(i));
			else
				sprintf((char*)values+len,"%s",fldValues.GetAt(i));
		}
		else
		{
			if(nFieldType==dbText||nFieldType==dbMemo||nFieldType==dbDate)
				sprintf((char*)values+len,",'%s'",fldValues.GetAt(i));
			else
				sprintf((char*)values+len,",%s",fldValues.GetAt(i));
		}
		len=strlen((char*)values);
	}

	memset(szSql,0,sizeof(szSql));
	sprintf((char*)szSql,"insert into %s (%s) values (%s)",tbName,(char*)flds,(char*)values);

	if(!ExecSQL(szSql))
	{
		return -1;
	}
	
	return 1;

}

int CAccessDB::DelRecord(char *tbName, CStringArray &KeyName, CStringArray &KeyValues, int flag)
{
	unsigned char szSql[1500];
	unsigned char flds[500];
	unsigned char values[500];
	unsigned char condition[500];
	unsigned char msg[1500];
	int i,len=0;
	short nFieldType;

	if(flag==0)
		return 0;

	
	if(KeyName.GetSize()<=0)
		return 0;

	memset(flds,0,sizeof(flds));
	memset(values,0,sizeof(values));
	memset(msg,0,sizeof(msg));

	for(i=0;i<KeyName.GetSize();i++)
	{
		nFieldType=GetFieldType(tbName,KeyName.GetAt(i).GetBuffer(0));
		if(!i)
		{
			if(nFieldType==dbText||nFieldType==dbMemo)
				sprintf((char *)condition+len,"%s='%s'",KeyName.GetAt(i),KeyValues.GetAt(i));
			else if(nFieldType==dbDate)
				sprintf((char *)condition+len,"%s between #%s# and #%s#",KeyName.GetAt(i),KeyValues.GetAt(i),KeyValues.GetAt(i));
			else
				sprintf((char *)condition+len,"%s=%s",KeyName.GetAt(i),KeyValues.GetAt(i));
		}
		else
		{
			if(nFieldType==dbText||nFieldType==dbMemo)
				sprintf((char *)condition+len," and %s='%s'",KeyName.GetAt(i),KeyValues.GetAt(i));
			else if(nFieldType==dbDate)
				sprintf((char *)condition+len," and %s between #%s# and #%s#",KeyName.GetAt(i),KeyValues.GetAt(i),KeyValues.GetAt(i));
			else
				sprintf((char *)condition+len," and %s=%s",KeyName.GetAt(i),KeyValues.GetAt(i));
		}
		len=strlen((char *)condition);
		
	}

	memset((char *)szSql,0,sizeof(szSql));
	sprintf((char *)szSql,"delete * from %s where %s",tbName,(char *)condition);

	if(!ExecSQL(szSql))
	{
		return -1;
	}

	
	return 1;


}

int CAccessDB::UpdateRecord(char *tbName, CStringArray &KeyName, CStringArray &KeyValues, CStringArray &fldName, CStringArray &fldValues)
{
	unsigned char szSql[1500];
	unsigned char flds[500];
	unsigned char values[500];
	unsigned char condition[500];
	unsigned char msg[1500];
	int i,len=0,iCount;
	short nFieldType;

	if(KeyName.GetSize()<=0)
		return 0;
	if(fldName.GetSize()!=fldValues.GetSize())
		return 0;

	memset(flds,0,sizeof(flds));
	memset(values,0,sizeof(values));
	memset(msg,0,sizeof(msg));

	for(i=0;i<KeyName.GetSize();i++)
	{
		nFieldType=GetFieldType(tbName,KeyName.GetAt(i).GetBuffer(0));
		if(!i)
		{
			if(nFieldType==dbText||nFieldType==dbMemo)
				sprintf((char *)condition+len,"%s='%s'",KeyName.GetAt(i),KeyValues.GetAt(i));
			else if(nFieldType==dbDate)
				sprintf((char *)condition+len,"%s between #%s# and #%s#",KeyName.GetAt(i),KeyValues.GetAt(i),KeyValues.GetAt(i));
			else
				sprintf((char *)condition+len,"%s=%s",KeyName.GetAt(i),KeyValues.GetAt(i));
		}
		else
		{
			if(nFieldType==dbText||nFieldType==dbMemo)
				sprintf((char *)condition+len," and %s='%s'",KeyName.GetAt(i),KeyValues.GetAt(i));
			else if(nFieldType==dbDate)
				sprintf((char *)condition+len," and %s between #%s# and #%s#",KeyName.GetAt(i),KeyValues.GetAt(i),KeyValues.GetAt(i));
			else
				sprintf((char *)condition+len," and %s=%s",KeyName.GetAt(i),KeyValues.GetAt(i));
		}
		len=strlen((char *)condition);
		
	}

	len=0;
	for(i=0;i<fldName.GetSize();i++)
	{
		nFieldType=GetFieldType(tbName,fldName.GetAt(i).GetBuffer(0));
		if(!i)
		{
			if(nFieldType==dbText||nFieldType==dbMemo||nFieldType==dbDate)
				sprintf((char *)flds+len,"%s='%s'",fldName.GetAt(i),fldValues.GetAt(i));
			else	//如果是数字型的字段
				sprintf((char *)flds+len,"%s=%s",fldName.GetAt(i),fldValues.GetAt(i));
		}
		else
		{
			if(nFieldType==dbText||nFieldType==dbMemo||nFieldType==dbDate)
				sprintf((char *)flds+len,",%s='%s'",fldName.GetAt(i),fldValues.GetAt(i));
			else	//如果是数字型的字段
				sprintf((char *)flds+len,",%s=%s",fldName.GetAt(i),fldValues.GetAt(i));
		}
		len=strlen((char *)flds);
	}

	memset((char *)szSql,0,sizeof(szSql));
	sprintf((char *)szSql,"select * from %s where %s",tbName,(char *)condition);

	if(!SetDataSet(NULL,(char *)szSql))
		return -1;
	try
	{
		iCount=GetDataSetRowCount(1);
	}
	catch(CDaoException *e)
	{
		if(e->m_pErrorInfo->m_lErrorCode==3021)
		{
			e->Delete();
			return DB_DATANOTEXIST;
		}
	}

	if(iCount<=0)
		return DB_DATANOTEXIST;
	

	memset(szSql,0,sizeof(szSql));
	sprintf((char *)szSql,"update %s set %s where %s",tbName,(char *)flds,(char *)condition);

	if(!ExecSQL(szSql))
	{
		return -1;
	}

	return 1;

}