accessdb.cpp

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

// MyDB.cpp: implementation of the CAccessDB class.
//
//////////////////////////////////////////////////////////////////////

#include "stdafx.h"

//#include <windows.h>
#include "AccessDB.h"

#ifdef _MYSYSTEMAPI_
#include "psapi.h"

#endif

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


//////////////////////////////////////////////////////////////////////
// Construction/Destruction
//////////////////////////////////////////////////////////////////////

CAccessDB::CAccessDB(char * DBPath,char *DBName,int OptFlag)
{
	if((AfxGetModuleState()->m_dwVersion)!=0x0601)
	{
		AfxGetModuleState()->m_dwVersion=0x0601;
		AfxDaoInit();
	}

//	m_DSName=NULL;
//	m_DataSetSql=NULL;
	m_TableCount=0;

	if(DBPath!=NULL&&DBName!=NULL)
	{
		sprintf(m_DBName,"%s",DBName);
		sprintf(m_DBPath,"%s",DBPath);
	}
	
	if(OptFlag)
	{
		try
		{
			CDaoWorkspace::CompactDatabase(_T(DBName),_T(".\\tmp.mdb"),dbLangGeneral,0);
			char szCommand[100];
			memset(szCommand,0,sizeof(szCommand));
			sprintf(szCommand,"del %s",DBName);
			system(szCommand);
			memset(szCommand,0,sizeof(szCommand));
			sprintf(szCommand,"rename tmp.mdb %s",DBName);
			system(szCommand);
		}
		catch(CDaoException *e)
		{
			AfxMessageBox(e->m_pErrorInfo->m_strDescription);
			e->Delete();
		}
	}

//	m_DataSet.m_pDatabase->Create("hello");

}

CAccessDB::~CAccessDB()
{
	if(m_DataSet.m_pDatabase->IsOpen())
		m_DataSet.m_pDatabase->Close();

	if(m_DataSet.IsOpen())
		m_DataSet.Close();

	if(m_DB.IsOpen())
		m_DB.Close();
}

int CAccessDB::ConnectDB(char * DsnName)
{
	CString path,DSNName;

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

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

	path.Format("DSN=%s;DBQ=%s\\%s;DEFAULTDIR=D:\\",
				DSNName.GetBuffer(0),
				m_DBPath,
				m_DBName);

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

	return 1;

}

int CAccessDB::OpenDB(LPCTSTR lpszName,BOOL bExclusive,
					   BOOL bReadOnly,LPCTSTR lpszConnect)
{
	int ret=0;
	CString ErrMsg;

	if(m_DB.IsOpen())
		return TRUE;

	try
	{
		m_DB.Open(lpszName,bExclusive,bReadOnly,lpszConnect);
//		m_DB.Open(lpszName);
	}
	catch(CDaoException *e)
	{
		ErrMsg.Format("\t%s!\n\t请检查数据库状态是否正常,或连接的用户密码是否正确!",
					e->m_pErrorInfo->m_strDescription);
        MessageBox(0,ErrMsg,"打开数据库失败",MB_OK|MB_ICONERROR);
// Delete the incomplete recordset object
		ret=e->m_pErrorInfo->m_lErrorCode;
        e->Delete();
//		CloseDB();
		return ret;
	}

	return (int)FreshDBInfo();

}

BOOL CAccessDB::CloseDB()
{
	if(m_DataSet.IsOpen())
		m_DataSet.Close();

	m_DB.Close();
	return 1;
}


int CAccessDB::ExecSQL(unsigned char *szSql)
{

	try
	{
		m_DB.Execute((const char *)(LPCTSTR)szSql);
	}
	catch(CDaoException * e)
	{
		CString msg;
		int retcode=e->m_pErrorInfo->m_lErrorCode;
		msg.Format("%s\n%s",
				(char *)szSql,
				e->m_pErrorInfo->m_strDescription);
		e->Delete();

		if(retcode==3065)
		{
			return 0;
		}
		AfxMessageBox(msg);

//		m_DB.Close();
		return 0;

	}
	return 1;
}


int CAccessDB::GetTableCount()
{

	return m_TableCount;
}

int CAccessDB::GetFieldCount(char *TbName)
{
	CDaoTableDef m_MyTable(&m_DB);
	m_MyTable.Open(TbName);
	int iCount=m_MyTable.GetFieldCount();

	m_MyTable.Close();

	return iCount;
}

int CAccessDB::GetRowCount(char *TbName)
{
	for(int i=0;i<m_TableCount;i++)
	{
		CDaoTableDefInfo TableInfo;
		m_DB.GetTableDefInfo(i,TableInfo,AFX_DAO_ALL_INFO);
		if((strcmp(TableInfo.m_strName,TbName))!=0)
			continue;
		else
			return TableInfo.m_lRecordCount;
	}
	
	return -1;
}

LPCTSTR CAccessDB::Str2Bool(BOOL bFlag)
{
	return bFlag ? _T("TRUE") : _T("FALSE");
}

CString CAccessDB::Str2Variant(const COleVariant &var)
{
	CString strRet;
	strRet = _T("Fish");

	switch(var.vt)
	{
	case VT_EMPTY:
	case VT_NULL:
		strRet=_T("NULL");
		break;
	case VT_I2:
		strRet.Format(_T("%hd"),V_I2(&var));
		break;
	case VT_I4:
		strRet.Format(_T("%d"),V_I4(&var));
		break;
	case VT_R4:
		strRet.Format(_T("%e"),(double)V_R4(&var));
		break;
	case VT_R8:
//		strRet.Format(_T("%e"),V_R8(&var));
		strRet.Format(_T("%.2f"),V_R8(&var));
		break;
	case VT_CY:
		strRet=COleCurrency(var).Format();
		break;
	case VT_DATE:
		strRet=COleDateTime(var).Format(_T("%y-%m-%d"));
		break;
	case VT_BSTR:
		strRet=V_BSTRT(&var);
		break;
	case VT_DISPATCH:
		strRet=_T("VT_DISPATCH");
		break;
	case VT_ERROR:
		strRet=_T("VT_ERROR");
		break;
	case VT_BOOL:
		return Str2Bool(V_BOOL(&var));
	case VT_VARIANT:
		strRet=_T("VT_VARIANT");
		break;
	case VT_UNKNOWN:
		strRet=_T("VT_UNKNOWN");
		break;
	case VT_I1:
		strRet=_T("VT_I1");
		break;
	case VT_UI1:
		strRet.Format(_T("0x%02hX"),(unsigned short)V_UI1(&var));
		break;
	case VT_UI2:
		strRet=_T("VT_UI2");
		break;
	case VT_UI4:
		strRet=_T("VT_UI4");
		break;
	case VT_I8:
		strRet=_T("VT_I8");
		break;
	case VT_UI8:
		strRet=_T("VT_UI8");
		break;
	case VT_INT:
		strRet=_T("VT_INT");
		break;
	case VT_UINT:
		strRet=_T("VT_UINT");
		break;
	case VT_VOID:
		strRet=_T("VT_VOID");
		break;
	case VT_HRESULT:
		strRet=_T("VT_HRESULT");
		break;
	case VT_PTR:
		strRet=_T("VT_PTR");
		break;
	case VT_SAFEARRAY:
		strRet=_T("VT_SAFEARRAY");
		break;
	case VT_CARRAY:
		strRet=_T("VT_CARRAY");
		break;
	case VT_USERDEFINED:
		strRet=_T("VT_USERDEFINED");
		break;
	case VT_LPSTR:
		strRet=_T("VT_LPSTR");
		break;
	case VT_LPWSTR:
		strRet=_T("VT_LPWSTR");
		break;
	case VT_FILETIME:
		strRet=_T("VT_FILETIME");
		break;
	case VT_BLOB:
		strRet=_T("VT_BLOB");
		break;
	case VT_STREAM:
		strRet=_T("VT_STREAM");
		break;
	case VT_STORAGE:
		strRet=_T("VT_STORAGE");
		break;
	case VT_STREAMED_OBJECT:
		strRet=_T("VT_STREAMED_OBJECT");
		break;
	case VT_STORED_OBJECT:
		strRet=_T("VT_STORED_OBJECT");
		break;
	case VT_BLOB_OBJECT:
		strRet=_T("VT_BLOB_OBJECT");
		break;
	case VT_CF:
		strRet=_T("VT_CF");
		break;
	case VT_CLSID:
		strRet=_T("VT_CLSID");
		break;
	}
	WORD vt=var.vt;
	if(vt&VT_ARRAY)
	{
		vt=vt&~VT_ARRAY;
		strRet=_T("Array of ");
	}
	if(vt&VT_BYREF)
	{
		vt=vt&~VT_BYREF;
		strRet+=_T("Pointer to ");
	}
	if(vt!=var.vt)
	{
		switch(vt)
		{
		case VT_EMPTY:
			strRet+=_T("VT_EMPTY");
			break;
		case VT_NULL:
			strRet+=_T("NULL");
			break;
		case VT_I2:
			strRet+=_T("V_I2");
			break;
		case VT_I4:
			strRet+=_T("V_I4");
			break;
		case VT_R4:
			strRet+=_T("V_R4");
			break;
		case VT_R8:
			strRet+=_T("V_R8");
			break;
		case VT_CY:
			strRet+=_T("VT_CY");
			break;
		case VT_DATE:
			strRet+=_T("VT_DATE");
			break;
		case VT_BSTR:
			strRet+=_T("V_BSTRT");
			break;
		case VT_DISPATCH:
			strRet+=_T("VT_DISPATCH");
			break;
		case VT_ERROR:
			strRet+=_T("VT_ERROR");
			break;
		case VT_BOOL:
			strRet+=_T("VT_BOOL");
			break;
		case VT_VARIANT:
			strRet+=_T("VT_VARIANT");
			break;
		case VT_UNKNOWN:
			strRet+=_T("VT_UNKNOWN");
			break;
		case VT_I1:
			strRet+=_T("VT_I1");
			break;
		case VT_UI1:
			strRet+=_T("V_UI1");
			break;
		case VT_UI2:
			strRet+=_T("VT_UI2");
			break;
		case VT_UI4:
			strRet+=_T("VT_UI4");
			break;
		case VT_I8:
			strRet+=_T("VT_I8");
			break;
		case VT_UI8:
			strRet+=_T("VT_UI8");
			break;
		case VT_INT:
			strRet+=_T("VT_INT");
			break;
		case VT_UINT:
			strRet+=_T("VT_UINT");
			break;
		case VT_VOID:
			strRet+=_T("VT_VOID");
			break;
		case VT_HRESULT:
			strRet+=_T("VT_HRESULT");
			break;
		case VT_PTR:
			strRet+=_T("VT_PTR");
			break;
		case VT_SAFEARRAY:
			strRet+=_T("VT_SAFEARRAY");
			break;
		case VT_CARRAY:
			strRet+=_T("VT_CARRAY");
			break;
		case VT_USERDEFINED:
			strRet+=_T("VT_USERDEFINED");
			break;
		case VT_LPSTR:
			strRet+=_T("VT_LPSTR");
			break;
		case VT_LPWSTR:
			strRet+=_T("VT_LPWSTR");
			break;
		case VT_FILETIME:
			strRet+=_T("VT_FILETIME");
			break;
		case VT_BLOB:
			strRet+=_T("VT_BLOB");
			break;
		case VT_STREAM:
			strRet+=_T("VT_STREAM");
			break;
		case VT_STORAGE:
			strRet+=_T("VT_STORAGE");
			break;
		case VT_STREAMED_OBJECT:
			strRet+=_T("VT_STREAMED_OBJECT");
			break;
		case VT_STORED_OBJECT:
			strRet+=_T("VT_STORED_OBJECT");
			break;
		case VT_BLOB_OBJECT:
			strRet+=_T("VT_BLOB_OBJECT");
			break;
		case VT_CF:
			strRet+=_T("VT_CF");
			break;
		case VT_CLSID:
			strRet+=_T("VT_CLSID");
			break;
		}
	}

	return strRet;
}

int CAccessDB::GetValueInField(char * TbName,char * lpszName,char *OutValue)
{
	int iFieldCount = GetFieldCount(TbName);
	CDaoTableDef m_MyTable(&m_DB);
	m_MyTable.Open(TbName);

	for(int i=0;i<iFieldCount;i++)
	{
		CDaoFieldInfo FieldInfo;
		m_MyTable.GetFieldInfo(i,FieldInfo,AFX_DAO_ALL_INFO);
		if((strcmp((const char *)(LPCTSTR)
			FieldInfo.m_strName,lpszName))!=0)
			continue;
		else
		{
			m_MyTable.Close();
			strcpy(OutValue,(char *)(LPCTSTR)FieldInfo.m_strName);
			return 0;
		}
	}

	m_MyTable.Close();
	return -1;

}

int CAccessDB::GetValueInField(char * TbName,int nIndex,char *OutValue)
{
	CDaoTableDef m_MyTable(&m_DB);
	m_MyTable.Open(TbName);

	CDaoFieldInfo FieldInfo;
	m_MyTable.GetFieldInfo(nIndex,FieldInfo,AFX_DAO_ALL_INFO);
	m_MyTable.Close();

	strcpy(OutValue,(char *)(LPCTSTR)FieldInfo.m_strName);
	return 0;

}

BOOL CAccessDB::FreshDBInfo()
{
	if(!m_DB.IsOpen())
		return FALSE;

	CDaoRecordset tmpDataSet(&m_DB);
	memcpy(&m_DataSet,&tmpDataSet,sizeof(tmpDataSet));

	int nTableCount=m_DB.GetTableDefCount();
	for(int i=0;i<nTableCount;i++)
	{
		CDaoTableDefInfo TableInfo;
		m_DB.GetTableDefInfo(i,TableInfo,AFX_DAO_ALL_INFO);
		if(TableInfo.m_lAttributes!=0)
			continue;
		else
		{
			m_TableCount+=1;
			sprintf(m_TableName[m_TableCount-1],
					"%s",
					(const char*)(LPCTSTR)TableInfo.m_strName);
			CDaoTableDef m_MyTable(&m_DB);
			m_MyTable.Open(TableInfo.m_strName);

			int nFieldCount=m_MyTable.GetFieldCount();
			for(int j=0;j<nFieldCount;j++)
			{
				CDaoFieldInfo FieldInfo;
				m_MyTable.GetFieldInfo(j,FieldInfo,AFX_DAO_ALL_INFO);
				sprintf(m_FieldName[m_TableCount-1][j],"%s",FieldInfo.m_strName);
				m_FieldType[m_TableCount-1][j]=FieldInfo.m_nType;
			}
			m_MyTable.Close();
		}
	}

	
	return TRUE;
}

BOOL CAccessDB::FreshTBInfo(char *TbName)
{
//	CDaoTableDef m_MyTable(&m_DB);
//	m_MyTable.Open(TbName);
	return TRUE;
//	m_
}

int CAccessDB::GetDataSetFieldCount()
{
	long retcode;

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

	try
	{
		m_DataSet.MoveFirst();
	}
	catch(CDaoException *e)
	{
		CString msg;
		msg.Format("%s",e->m_pErrorInfo->m_strDescription);
		retcode=e->m_pErrorInfo->m_lErrorCode;
		e->Delete();
		if(retcode==3021)
			return 0;
		else
		{
			AfxMessageBox(msg);
			return -1;
		}
	}

	return m_DataSet.GetFieldCount();
}

int CAccessDB::SetDataSet(char *DSName,char *szSql, int szOpenMode)
{
	if(!m_DB.IsOpen())
	{
		m_DB.Open(m_DBName);
	}

//	if(m_DataSet.IsOpen())
//		m_DataSet.Close();


	memset(m_DataSetSql,0,sizeof(m_DataSetSql));
	memset(m_DSName,0,sizeof(m_DSName));

	if(m_DataSet.IsOpen())
	{
		if(DSName!=NULL)
			sprintf(m_DSName,"%s",DSName);
		m_DataSet.Close();
		m_DataSet.Open(szOpenMode,szSql);
	}
	else
	{
		try
		{
			m_DataSet.Open(szOpenMode,szSql);
			if(DSName!=NULL)
				sprintf(m_DSName,"%s",DSName);
		}
		catch(CDaoException *e)
		{
			CString msg;
			msg.Format("%s\n%s",
					szSql,
					e->m_pErrorInfo->m_strDescription);

			AfxMessageBox(msg);
			e->Delete();

			return 0;
		}
	}

	return 1;
}

int CAccessDB::GetDataSetFieldName(int nIndex, char *OutValue)
{
	if(!m_DataSet.IsOpen())
		return -1;
/*
	if((strcmp(DataSetName,m_DSName))!=0)
	{
		OutValue=NULL;
		return -1;
	}
*/
	CDaoFieldInfo FieldInfo;
//	m_DataSet.MoveFirst();