myado.cpp

该代码是对数据库操作的数据库是sql server

// MyAdo.cpp: implementation of the CMyAdo class.
//
//////////////////////////////////////////////////////////////////////

#include "stdafx.h"
#include "MyAdo.h"

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

//////////////////////////////////////////////////////////////////////
// Construction/Destruction
//////////////////////////////////////////////////////////////////////
char ComPuterName[20];
unsigned long ComPuterNameLong = 20;

CMyAdo::CMyAdo(CString DataBaseName,CString ServerName,BOOL IsNewWork )
{
	m_IsOpened = false;
	if (ServerName == "")
	{
	//	CString strTemp = "";
		memset(ComPuterName,0,20);
		::GetComputerName(ComPuterName,&ComPuterNameLong);
		ServerName.Format("%s",ComPuterName);
		
	}
	if (IsNewWork)
	{
		gStrConnect = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=" + DataBaseName + ";Data Source=" + ServerName;
		m_IsNetWork = true;
	}
	//Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=PJDataBase;Data Source=YHB
	else
	{
		gStrConnect ="Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=" + DataBaseName + ";Data Source=" + ServerName;
		m_IsNetWork = false;
	}	
	m_strSql = "";
}

CMyAdo::~CMyAdo()
{

}
//函数名称 : OpenDB()

//功能:打开数据库

//入口参数:无

//出口参数:BOOL是否成功执
BOOL CMyAdo::OpenDB(CString strDefaultTable)
{
	if(FAILED(::CoInitialize(NULL)))
	{
		return false;
	}
	m_Cn.CreateInstance( __uuidof(Connection) );
	m_Set.CreateInstance( __uuidof(Recordset) );
	m_SetTemp.CreateInstance( __uuidof(Recordset) );
//	m_cmd.CreateInstance(__uuidof(Command) );
	try
	{
		m_Cn ->Open(gStrConnect.AllocSysString(),"","",adModeUnknown);
	}
	catch(_com_error e)
	{
		TRACE(e.Description());		
		return false;
	}
	m_strSql = "select * from " + strDefaultTable;
	try
	{
		
		m_Set ->Open(_bstr_t(m_strSql),(IDispatch *)m_Cn,adOpenDynamic,adLockPessimistic,adCmdText);
		
	}
	catch(_com_error *e)
	{
		TRACE(e ->Description());
		return false;
	}
	try
	{
		m_SetTemp ->Open(_bstr_t(m_strSql),(IDispatch *)m_Cn,adOpenDynamic,adLockPessimistic,adCmdText);
		
	}
	catch(_com_error *e)
	{
		TRACE(e ->Description());
		return false;
	}
	
	m_cmd.CreateInstance(__uuidof(Command));
	m_cmd->ActiveConnection = m_Cn;
	m_cmd->CommandType = adCmdText;
	m_cmd->CommandText = "";
	m_IsOpened =true;
	return true;
}
/////////////////////////////////////////////////////////////////////////
//函数名称 : CloseDB()

//功能:关关闭数据库

//入口参数:无

//出口参数:BOOL是否成功执
BOOL CMyAdo::CloseDB()
{
	m_IsOpened = false;
	if(this ->m_Set ->GetState())
	{
		m_Set ->Close();
		m_Set.Release();
	}	
	if(this ->m_SetTemp ->GetState())
	{
		m_SetTemp ->Close();
		m_SetTemp .Release();
	}

	if(this ->m_Cn ->GetState())
	{
		m_Cn ->Close();
		m_Cn.Release();
	//	_ConnectionPtr ->Execute()
	}
	CoUninitialize();
	return true;
}
/////////////////////////////////////////////////////////////////////////
//函数名称 : ExecuteMySql()

//功能:执行sql语句

//入口参数:

//strSql:接收外部传入的sql语句

//IsUpdateSet:将结果存入m_Set对象或m_SetTemp对象

//出口参数:BOOL是否成功执sql语句
BOOL CMyAdo::ExecuteMySql(CString strSql, BOOL IsUpdateSet)
{
	//	try
	{
	//	m_Cn ->BeginTrans();
	//	m_Set = this ->m_Cn ->Execute(strSql.AllocSysString(),NULL,1);
	//	m_Cn ->CommitTrans();
		m_cmd->CommandText = _bstr_t(strSql);
		TRACE(strSql);
		TRACE("\n");
		if (IsUpdateSet)
		{
		//	m_Set =m_Cn ->Execute(strSql.AllocSysString(),NULL,1);
			m_Set = m_cmd ->Execute(NULL,NULL,adCmdText);
		}
		else
		{	//m_Cn ->Execute(strSql.AllocSysString(),NULL,1);
			m_cmd ->Execute(NULL,NULL,adCmdText);
		}
				
	}
//	catch(...)
	{
	//	AfxMessageBox("ExecuteMySql");
//		return false;
	}
	return true;
}
/////////////////////////////////////////////////////////////////////////
//函数名称 : GetDataSet()

//功能:返回m_Set

//入口参数:无

//出口参数:执行sql语句的结果集（m_Set）
_RecordsetPtr CMyAdo::GetDataSet()
{
	return m_Set;
}
/////////////////////////////////////////////////////////////////////////
//函数名称 : GetConnection()

//功能:返回数据库联接对象(m_Cn)

//入口参数:无

//出口参数:m_Cn数据库联接对象

_ConnectionPtr CMyAdo::GetConnection()
{
	return m_Cn;
}
/////////////////////////////////////////////////////////////////////////
//函数名称 : GetCommand()

//功能:返回命令行对象(m_cmd)

//入口参数:无

//出口参数:m_cmd令行对象
_CommandPtr CMyAdo::GetCommand()
{
	return m_cmd;
}
/////////////////////////////////////////////////////////////////////////
//函数名称 : GetNewID()

//功能:返回新的ID(GUID类型)

//入口参数:strSql暂无用(默认为空字符串)

//出口参数:CString对象(内存新的ID)

CString CMyAdo::GetNewID(CString strSql)
{
	m_Set = m_Cn ->Execute("select newid() as a",NULL,1);
	return (m_Set->GetCollect("a").bstrVal);
}
/////////////////////////////////////////////////////////////////////////
//函数名称 : GetDataSet()

//功能:返回m_SetTemp

//入口参数:无

//出口参数:执行sql语句的结果集（m_SetTemp）
_RecordsetPtr CMyAdo::GetDataTempSet()
{
	return m_SetTemp;
}

BOOL CMyAdo::BeginTran()
{
	if(!ExecuteMySql("if @@TRANCOUNT>0 ROLLBACK"))
	{
		return false;
	}
	if(!ExecuteMySql("BEGIN TRANSACTION"))
	{
		return false;
	}
	return true;
}

BOOL CMyAdo::CommitTran()
{
	if(!ExecuteMySql("if @@TRANCOUNT>0 ROLLBACK"))
	{
		return false;
	}
	if(ExecuteMySql("COMMIT TRANSACTION"))
	{
		return true;
	}
	else
	{
		return false;
	}
}

BOOL CMyAdo::RollBackTran()
{
	return ExecuteMySql("ROLLBACK TRANSACTION");
}

BOOL CMyAdo::WriteWorkDataToDataBase(char *m_pFileBuffer, long DataLong)
{
	SAFEARRAY		*m_psa;
    SAFEARRAYBOUND	m_rgsabound[1];
	VARIANT			m_varBLOB;
	try
	{
	//	m_Cn ->BeginTrans();
		ExecuteMySql("delete from Worker");
		if(m_pFileBuffer)
		{    
			if (!OpenTable("Worker"))
			{
				return false;
			}
			m_Set ->AddNew();
			m_rgsabound[0].lLbound = 0;
			m_rgsabound[0].cElements = DataLong;
			m_psa = SafeArrayCreate(VT_UI1, 1, m_rgsabound);
			for (long i = 0; i < (long)DataLong; i++)
			{
				SafeArrayPutElement (m_psa, &i, m_pFileBuffer++);
			}
			m_varBLOB.vt = VT_ARRAY | VT_UI1;
			m_varBLOB.parray = m_psa;
			m_Set->GetFields()->GetItem("WorkeData")->AppendChunk(m_varBLOB);
			m_Set ->Update();
			
			::VariantClear(&m_varBLOB);
			::SafeArrayDestroyData( m_psa);
		//	return true;
		}
		else
		{
			return false;
		}
	//	m_Cn ->CommitTrans();
	}
	catch (_com_error *e)
	{
	//	m_Cn ->RollbackTrans();
		AfxMessageBox(e ->Description());
		return false;
	}
	catch (...) 
	{
	//	m_Cn ->RollbackTrans();
		AfxMessageBox("保存会计信息失败,请重新设置或与软件提出供商联系!!!");
		return false;
	}
}

BOOL CMyAdo::OpenTable(CString strTable, BOOL IsSql)
{
		if (IsSql)
	{
		m_strSql.Format("select * from %s",strTable);
	}
	else
	{
		m_strSql = strTable;
	}
	
	try
	{
		if(this ->m_Set ->GetState())
		{
			m_Set ->Close();
		}
		m_Set ->Open((_bstr_t)m_strSql,(IDispatch *)m_Cn,adOpenDynamic,adLockPessimistic,adCmdText);

	//	m_Set ->MovePrevious();
			return true;
	}
	catch(_com_error *e)
	{
		AfxMessageBox(e ->Description());
		return false;
	}
	return true;
}

BOOL CMyAdo::GetWorkerDataFromDataBase(char *pBuf)
{
		try
	{
	//	ExecuteMySql("delete from Worker");
	//	m_Cn ->BeginTrans();
		ExecuteMySql("select * from Worker",true);
		if (!m_Set ->adoEOF)
		{
			long lDataSize = m_Set->GetFields()->GetItem(_variant_t("WorkeData"))->ActualSize;
			char *BufTemp = NULL;
			if(lDataSize > 0)
			{			
				_variant_t			varBLOB;
				varBLOB = m_Set->GetFields()->GetItem(_variant_t("WorkeData"))->GetChunk(lDataSize);
				if(varBLOB.vt == (VT_ARRAY | VT_UI1))
				{
					SafeArrayAccessData(varBLOB.parray,(void **)&BufTemp);
					memcpy(pBuf,BufTemp,lDataSize);		
					SafeArrayUnaccessData (varBLOB.parray);
				}
				return true;
			}
			else
			{
				return FALSE;
			}
		}
	}
	catch (...)
	{
		AfxMessageBox("读取会计信息失败,请重新设置或与软件提出供商联系!!!");
	}
}

BOOL CMyAdo::IsNetWorkServer()
{
	return m_IsNetWork;
}

BOOL CMyAdo::IsOpenDB()
{
	return m_IsOpened;
}