adohandler.cpp

基础数据管理工具 主要针对政务系统的基础数据管理、分析、应用等功能！ 项目代码

// ADOHandler.cpp: implementation of the CADOHandler class.
//
//////////////////////////////////////////////////////////////////////

#include "stdafx.h"
#include "BaseFunc.h"
#include "ADOHandler.h"
#include "CommonUtil.h"
#ifdef _DEBUG
#undef THIS_FILE
static char THIS_FILE[]=__FILE__;
#define new DEBUG_NEW
#endif

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

CADOHandler::CADOHandler()
{
	OracleConStr="Provider=OraOLEDB.Oracle;Data Source=1;User ID=2; Password=3;";
	SQLServerConStr="Provider=SQLOLEDB;Data Source=1;User ID=2;Password=3;Initial Catalog=4;";
	AccessConStr="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=1;";

	m_ebProviderType = EBNULL;
	m_ConnectionOpen = false;
}

CADOHandler::~CADOHandler()
{

}

bool CADOHandler::InitInstance(LPCTSTR sConnectionString)
{
	try
	{
		if(m_ConnectionOpen)
		{
			m_pConnection->Close();
			m_ConnectionOpen = false;
		}
		m_ebProviderType = GetProviderType(sConnectionString);
		if(m_ebProviderType == EBNULL)
			return(false);
		m_pConnection.CreateInstance(__uuidof(Connection));
		if(m_ebProviderType == EBAccess)
			m_pConnection->Open((_bstr_t)sConnectionString,"Admin","",adModeUnknown);
		else
			m_pConnection->Open((_bstr_t)sConnectionString,"","",-1);
		m_ConnectionOpen = true;
	}
	catch(_com_error &e)
	{
		_bstr_t err = e.Description();
		return(false);
	}
	return(true);
}

bool CADOHandler::InitInstance(_ConnectionPtr pConnection, EBConectionProviderType ebType)
{
	try
	{
		if(pConnection == NULL)
			return(false);
		m_pConnection = pConnection;
		m_ebProviderType = ebType;
	}
	catch(_com_error &e)
	{
		_bstr_t err = e.Description();
		return(false);
	}
	return(true);
}

_RecordsetPtr CADOHandler::GetRecordset(LPCTSTR sSQL)
{
	_RecordsetPtr pRec;
	pRec.CreateInstance(__uuidof(Recordset));	
	pRec->Open(
		_variant_t(sSQL), _variant_t(m_pConnection,true),
		adOpenKeyset,adLockOptimistic,adCmdText);
	return(pRec);
}

bool CADOHandler::ExecuteSQL(LPCTSTR sSQL)
{
	try
	{
		m_pConnection->BeginTrans();
		_CommandPtr pCmd;
		pCmd.CreateInstance(__uuidof(Command));
		pCmd->PutCommandText(_bstr_t(sSQL));
		pCmd->ActiveConnection = m_pConnection;
		pCmd->Execute(NULL,NULL,adCmdText);
		m_pConnection->CommitTrans();
	}
	catch(_com_error& e)
	{
		m_pConnection->RollbackTrans();
		_bstr_t err = e.Description();
		return(false);
	}
	return(true);
}

_variant_t CADOHandler::GetColumnValue(LPCTSTR TableName, LPCTSTR ColumnName, CString Condition)
{
	_variant_t tColumnValue;
	tColumnValue.vt = VT_NULL;
	try
	{
		_RecordsetPtr pRecordset;
		CString strSQL;
		if(Condition.IsEmpty())
			strSQL.Format ("Select %s From %s",ColumnName,TableName);
		else
			strSQL.Format ("Select %s From %s Where %s",ColumnName,TableName,Condition);
		pRecordset = GetRecordset(strSQL);
		if (pRecordset->GetadoEOF()) return tColumnValue;
		tColumnValue= pRecordset->GetCollect((long)0);		
	}
	catch(_com_error &e)
	{
		_bstr_t err = e.Description();
	}
	return(tColumnValue);
}

void CADOHandler::Clean()
{
	if(m_pConnection != NULL)
		m_pConnection.Release();
	m_ConnectionOpen = false;
}

EBConectionProviderType CADOHandler::GetProviderType(LPCTSTR sConnectionString)
{
	CString sConString = sConnectionString;
	int		iStart;
	int		iEnd;
	int		iCount;
	CString sProviderType;
	iStart = sConString.Find("Provider=");
	iStart = iStart + 9;// 9为"Provider="的长度
	iEnd = sConString.Find(";");
	iCount = iEnd - iStart;
	sProviderType = sConString.Mid(iStart,iCount);
	if(sProviderType.CompareNoCase("SQLOLEDB") == 0)
		return(EBSQLServer);
	else if(sProviderType.CompareNoCase("OraOLEDB.Oracle") == 0)
		return(EBOracle);
	else if(sProviderType.CompareNoCase("Microsoft.Jet.OLEDB.4.0") == 0)
		return(EBAccess);
	return(EBNULL);
}

CString CADOHandler::GetFieldType(ADODB::FieldPtr pField)
{
	CString sType;
	ADODB::DataTypeEnum adoFileType = pField->GetType();
	switch(adoFileType)
	{
	case ADODB::adNumeric:
	case ADODB::adVarNumeric:
		sType = "NUMBER";
		break;
	case ADODB::adVarWChar:
		sType = "VARCHAR";
		break;
	case adDBTimeStamp:
		sType = "DATE";
		break;
	case adLongVarBinary:
		sType = "BLOB";
		break;
	case adLongVarWChar:
		sType = "CLOB";
		break;
	default:
		sType = "UNKOWN";
		break;
	}
	return(sType);
}

bool CADOHandler::InsertDataFromOtherDataBase(CString sAimTableName,CString sDataBasePath,CString sDataBaseTableName,
							bool bDelAimDate,LPCTSTR pzsDebarFieldName)
{
	//Insert Into Check_ThisYear select * From  Check_ThisYear In 'D:\退耕还林\程序\Afforestation\Debug\Map\绥化市\青冈县\梧桐河.mdb'
	if(m_ebProviderType != EBAccess)
		return false;
	CString sSQL;
	if(bDelAimDate)
	{
		sSQL.Format("Delete From %s",sAimTableName);
		ExecuteSQL(sSQL);
	}
	CString sDebarName = pzsDebarFieldName;
	if(sDebarName == "")	//不用排除ID字段的情况,可以直接追加
	{
		sSQL.Format("Insert INTO %s  SELECT * FROM %s In '%s'",
				sAimTableName,sDataBaseTableName,sDataBasePath);
		return ExecuteSQL(sSQL);
	}

	//排除ID字段的情况,需要先复制到本地数据库的临时表内，再逐行赋值
	//先复制到临时表
	CString sTempTableName = "Tmp_TempTable";
	if(!CreateTableFromOtherDataBase(sTempTableName,sDataBasePath,sDataBaseTableName))
		return false;
	try
	{
		sSQL.Format("Select * From %s Where 1=2",sAimTableName);
		_RecordsetPtr pAimRec = GetRecordset(sSQL);
		ADODB::FieldsPtr pFields = pAimRec->GetFields();

		_RecordsetPtr pTempRec = GetRecordset("Select * From " + sTempTableName);
		while(!pTempRec->GetadoEOF())
		{
			pAimRec->AddNew();
			for(int i=0;i<pFields->GetCount();i++)
			{
				CString sFieldName = (const TCHAR*)pFields->GetItem((long)i)->GetName();
				if(sFieldName != sDebarName)
					pAimRec->PutCollect((_bstr_t)sFieldName,pTempRec->GetCollect((_bstr_t)sFieldName));
			}
			pAimRec->Update();

			pTempRec->MoveNext();
		}
		
	}
	catch(_com_error &e)
	{
		_bstr_t err = e.Description();
		return false;
	}
	ExecuteSQL("Drop Table " + sTempTableName);
	return true;
}

bool CADOHandler::CreateTableFromOtherDataBase(CString sAimTableName,CString sDataBasePath,CString sDataBaseTableName)
{
	if(m_ebProviderType != EBAccess)
		return false;
	CString sSQL;
	sSQL.Format("SELECT * INTO %s From %s IN '%s'",sAimTableName,sDataBaseTableName,sDataBasePath);
	return ExecuteSQL(sSQL);
}