丰富自己的代码库-SqlServerHelper(Ado)

2024-03-12 18:39:14

设计思路很简单，就是把数据封装为DataTable类，封装了类型转换，使用者可以不必考虑任何类型转换，而使用VC提供的CString即可。封装了按行号查找功能，先看代码

#pragma once

#import "c:\Program Files\Common Files\System\ado\msado15.dll" no_namespace rename("EOF","adoEOF") rename("BOF","adoBOF")

#ifndef _SQLSERVERHELPER_H

#define _SQLSERVERHELPER_H

#include<list>

#include "stdafx.h"

struct PageInfo

{

    int PageCount;

    int PageSize;

    int PageIndex;

};

class DataTable

{

public:

    DataTable();

    DataTable(_RecordsetPtr& ptr);

    ~DataTable(void);

    void SetPtr(_RecordsetPtr& ptr);

    UINT GetColumnCount();

    UINT GetRowCount();

    BOOL Update();

    BOOL GetColumnName(long columnIndex,CString &str);

    BOOL GetColumnValue(long columnIndex,CString &str);

    BOOL SetColumnValue(long columnIndex,CString &str);

    BOOL SetValue(CString colName,CString str);

    BOOL GetValue(CString colName,CString &str);

    BOOL GetValue(long rowIndex,CString colName,CString &str);

    BOOL SetValue(long rowIndex,CString colName,CString str);

    template<class T>

    BOOL SetGenValue(CString colName,T val);

    template<class T>

    BOOL GetGenValue(CString colName,T& val);

    void MoveToRow(long rowIndex);

    BOOL GetColumnValue(long columnIndex,long rowIndex,CString &str);

    BOOL SetColumnValue(long columnIndex,long rowIndex,CString &str);

    _RecordsetPtr& GetSet();

private:

    _RecordsetPtr m_pSet;

    void NoUserFun();

};

class AdoHelper

{

public:

    AdoHelper(void);

    ~AdoHelper(void);

    BOOL Close();

    void ClearError();

    BOOL Open(CString str);

    _com_error GetLastError();

    BOOL Open(CString str,BOOL openTrans);

    BOOL OpenSql(CString sql,DataTable &table);

    BOOL OpenTable(CString tableName,DataTable &table);

    BOOL GetPagInfo(CString tableName,CString whereStr,PageInfo &info,DataTable &table);

    FieldsPtr& GetTable();

    UINT ExcuteNoQuery(CString sqlStr);

    //UINT ExcuteNoQuery(CString sqlStr,void* params,int paramLength);

    BOOL Excute(CString sqlStr,DataTable &table);

private:

    BOOL useTrans;

    _CommandPtr m_pCmd;

    _RecordsetPtr m_pSet;

    _ConnectionPtr m_pCon;

    std::list<_com_error> erorList;

};

#endif

以下是实现代码

#include "stdafx.h"

#include "SqlServerHelper.h"

void DataTable::NoUserFun()

{

	this->SetGenValue<int>(L"",0);

	this->SetGenValue<long>(L"",0l);

	int val;

	this->GetGenValue<int>(L"",val);

	long b;

	this->GetGenValue<long>(L"",b);

}

void DataTable::MoveToRow(long rowIndex)

{

	this->m_pSet->MoveFirst();

	for(int i=0;i<rowIndex;i++)

		this->m_pSet->MoveNext();

}

template<class T>

BOOL DataTable::SetGenValue(CString colName,T val)

{

	if(!m_pSet)return FALSE;

	this->m_pSet->PutCollect(_variant_t(colName),_variant_t(val));

	return TRUE;

}

template<class T>

BOOL DataTable::GetGenValue(CString colName,T& val)

{

	if(!m_pSet)return FALSE;

	val = (T)(_variant_t)(this->m_pSet->GetCollect(_variant_t(colName)));

	return TRUE;

}

DataTable::DataTable(_RecordsetPtr& ptr)

{

	this->m_pSet.CreateInstance(__uuidof(Recordset));

	this->m_pSet = ptr;

}

_RecordsetPtr& DataTable::GetSet()

{

	return this->m_pSet;

}

BOOL DataTable::Update()

{

	HRESULT hr;

	hr = this->m_pSet->Update();

	return SUCCEEDED(hr);

}

void DataTable::SetPtr(_RecordsetPtr& ptr)

{

	this->m_pSet = ptr;

}

DataTable::DataTable()

{

	this->m_pSet.CreateInstance(__uuidof(Recordset));

}

DataTable::~DataTable(void)

{

	this->m_pSet->Close();

}

UINT DataTable::GetRowCount()

{

	if(!m_pSet)return 0;

	return this->m_pSet->GetRecordCount();

}

UINT DataTable::GetColumnCount()

{

	if(!m_pSet)return 0;

	return this->m_pSet->Fields->Count;

}

BOOL DataTable::GetColumnName(long columnIndex,CString &str)

{

	if(!m_pSet)return FALSE;

	str = (LPCSTR)this->m_pSet->Fields->GetItem(columnIndex)->Name;

	return TRUE;

}

BOOL DataTable::GetColumnValue(long columnIndex,CString &str)

{

	if(!m_pSet)return FALSE;

	str = (LPCSTR)(_bstr_t)this->m_pSet->Fields->GetItem(columnIndex)->Value;

	return TRUE;

}

BOOL DataTable::SetColumnValue(long columnIndex,CString &str)

{

	if(!m_pSet)return FALSE;

	this->m_pSet->Fields->GetItem(columnIndex)->Value = (_bstr_t)str;

	return TRUE;

}

BOOL DataTable::GetColumnValue(long columnIndex,long rowIndex,CString &str)

{

	if(!m_pSet)return FALSE;

	if(rowIndex>=this->m_pSet->GetRecordCount())return FALSE;

	this->MoveToRow(rowIndex);

	str = (LPCSTR)(_bstr_t)this->m_pSet->Fields->GetItem(columnIndex)->Value;

	return TRUE;

}

BOOL DataTable::SetColumnValue(long columnIndex,long rowIndex,CString &str)

{

	if(!m_pSet)return FALSE;

	if(rowIndex>=this->m_pSet->GetRecordCount())return FALSE;

	this->MoveToRow(rowIndex);

	this->m_pSet->Fields->GetItem(columnIndex)->Value = (_bstr_t)str;

	return TRUE;

}

BOOL DataTable::SetValue(CString colName,CString str)

{

	if(!m_pSet)return FALSE;

	this->m_pSet->PutCollect(_variant_t(colName),_variant_t(str));

	return TRUE;

}

BOOL DataTable::GetValue(CString colName,CString &str)

{

	if(!m_pSet)return FALSE;

	str = (LPCTSTR)(_bstr_t)(this->m_pSet->GetCollect(_variant_t(colName)));

	return TRUE;

}

BOOL DataTable::GetValue(long rowIndex,CString colName,CString &str)

{

	if(!m_pSet)return FALSE;

	this->MoveToRow(rowIndex);

	str = (LPCTSTR)(_bstr_t)(this->m_pSet->GetCollect(_variant_t(colName)));

	return TRUE;

}

BOOL DataTable::SetValue(long rowIndex,CString colName,CString str)

{

	if(!m_pSet)return FALSE;

	this->MoveToRow(rowIndex);

	this->m_pSet->PutCollect(_variant_t(colName),_variant_t(str));

	return TRUE;

}

AdoHelper::AdoHelper(void)

{

	::CoInitialize(NULL);

	HRESULT hr;

	hr = this->m_pCon.CreateInstance(__uuidof(Connection));

	ASSERT(SUCCEEDED(hr));

	hr = this->m_pSet.CreateInstance(__uuidof(Recordset));

	ASSERT(SUCCEEDED(hr));

	hr = this->m_pCmd.CreateInstance(__uuidof(Command));

	ASSERT(SUCCEEDED(hr));

}

AdoHelper::~AdoHelper(void)

{

	try

	{

		m_pSet->Close();

	}

	catch(_com_error e)

	{

	}

	try

	{

		m_pCon->Close();

	}

	catch(_com_error e)

	{

	}

	::CoUninitialize();

}

void AdoHelper::ClearError()

{

	this->erorList.clear();

}

BOOL AdoHelper::Open(CString str)

{

	HRESULT hr;

	try

	{

		hr = m_pCon->Open((_bstr_t)str,"","",adConnectUnspecified);

		return SUCCEEDED(hr);

	}

	catch(_com_error e)

	{

		AfxMessageBox(e.Description());

		this->erorList.push_back(e);

		return FALSE;

	}

}

BOOL AdoHelper::Open(CString str,BOOL openTrans)

{

	BOOL openSucceeded = this->Open(str);

	this->useTrans = openTrans;

	if(!this->m_pCon)return FALSE;

	long result = this->m_pCon->BeginTrans();

	return result==0;

}

BOOL AdoHelper::Close()

{

	if(useTrans)

	{

		if(!erorList.empty())

			this->m_pCon->RollbackTrans();

		else

			this->m_pCon->CommitTrans();

	}

	return TRUE;

}

_com_error AdoHelper::GetLastError()

{

	_com_error error = this->erorList.front();

	this->erorList.pop_front();

	return error;

}

BOOL AdoHelper::OpenTable(CString tableName,DataTable& table)

{

	if(!m_pCon)return FALSE;

	try

	{

		HRESULT hr;

		hr = m_pSet->Open(_variant_t(tableName),(IDispatch*)m_pCon,adOpenKeyset,adLockOptimistic,adCmdTable);

		table.SetPtr(m_pSet);

		return SUCCEEDED(hr);

	}

	catch(_com_error e)

	{

		this->erorList.push_back(e);

		return FALSE;

	}

}

BOOL AdoHelper::OpenSql(CString sql,DataTable &table)

{

	if(!m_pCon)return FALSE;

	try

	{

		HRESULT hr;

		hr = m_pSet->Open(_variant_t(sql),(IDispatch*)m_pCon,adOpenKeyset,adLockOptimistic,adCmdText);

		table.SetPtr(m_pSet);

		return SUCCEEDED(hr);

	}

	catch(_com_error e)

	{

		this->erorList.push_back(e);

		return FALSE;

	}

}

FieldsPtr& AdoHelper::GetTable()

{

	ASSERT(m_pSet!=NULL);

	return this->m_pSet->Fields;

}

UINT AdoHelper::ExcuteNoQuery(CString sqlStr)

{

	try

	{

		this->m_pCmd->ActiveConnection= this->m_pCon;

		this->m_pCmd->CommandText=(_bstr_t)sqlStr;

		this->m_pCmd->CommandType=adCmdText;

		this->m_pCmd->Parameters->Refresh();

		variant_t count;

		this->m_pCmd->Execute(&count,NULL,adCmdText);

		return (int)count;

	}

	catch(_com_error e)

	{

		this->erorList.push_back(e);

		return 0;

	}

}

BOOL AdoHelper::Excute(CString sqlStr,DataTable& table)

{

	try

	{

		this->m_pCmd->ActiveConnection= this->m_pCon;

		this->m_pCmd->CommandText=(_bstr_t)sqlStr;

		this->m_pCmd->CommandType=adCmdText;

		this->m_pCmd->Parameters->Refresh();

		variant_t count;

		table.SetPtr(this->m_pCmd->Execute(&count,NULL,adCmdText));

		return (int)count;

	}

	catch(_com_error e)

	{

		this->erorList.push_back(e);

		return 0;

	}

}

BOOL AdoHelper::GetPagInfo(CString tableName,CString whereStr,PageInfo &info,DataTable &table)

{

	if(!m_pCon)return FALSE;

	try

	{

		CString sql;

		sql.Format(L"select count(*) c from %s where 1=1 %s;",tableName,whereStr);

		_RecordsetPtr ptr;

		this->m_pCmd->ActiveConnection= this->m_pCon;

		this->m_pCmd->CommandText=(_bstr_t)sql;

		this->m_pCmd->CommandType=adCmdText;

		this->m_pCmd->Parameters->Refresh();

		variant_t count;

		ptr=this->m_pCmd->Execute(&count,NULL,adCmdText);

		int allCount = atoi((_bstr_t)ptr->GetCollect("c"));

		info.PageCount=(allCount+info.PageSize-1)/info.PageSize;

		ptr->Close();

		HRESULT hr;

		sql.Format(L"select top %d * from (select row_number() over (order by [Id]) rownumber,* from %s where 1=1 %s) tt where rownumber>%d;",info.PageSize,tableName,whereStr,(info.PageIndex-1)*(info.PageSize));

		hr = m_pSet->Open(_variant_t(sql),(IDispatch*)m_pCon,adOpenStatic,adLockReadOnly,adCmdText);

		table.SetPtr(m_pSet);

		return SUCCEEDED(hr);

	}

	catch(_com_error e)

	{

		AfxMessageBox(e.Description());

		this->erorList.push_back(e);

		return FALSE;

	}

}

码农公寓

相关文章