myadoclass.cpp

采用VC++编写访问数据库非常麻烦

// MyAdoClass.cpp : 实现文件
//

#include "stdafx.h"
#include "..\GeneralFiles\GeneralClassesAndFunctions.h"
#include "MyAdoClass.h"

// Need two distinct "empty" VARIANTs for Command::Execute
static VARIANT* pvtEmpty = static_cast<VARIANT*> (&vtMissing);
static _variant_t vtMissing2(DISP_E_PARAMNOTFOUND, VT_ERROR);
static VARIANT* pvtEmpty2 = static_cast<VARIANT*> (&vtMissing2);

CMyAdoClass::CMyAdoClass()
{
	hr = m_connection.CreateInstance(L"ADODB.Connection");
	hr = m_command.CreateInstance(__uuidof(Command));
	hr = m_recordset.CreateInstance(__uuidof(Recordset));
}

CMyAdoClass::~CMyAdoClass()
{
}

bool CMyAdoClass::Close(void)
{
	bool AllIsRight = true;
	try
	{
		if(FAILED(hr = m_connection->Close()))
			AllIsRight=false;
	}
	catch(...)
	{
		AllIsRight = false;
	}

	return AllIsRight;
}

bool CMyAdoClass::CloseRecordset(void)
{
	bool AllIsRight = true;
	try
	{
		if(FAILED(hr = m_recordset->Close()))
			AllIsRight=false;
	}
	catch(...)
	{
		AllIsRight = false;
	}

	return AllIsRight;
}

bool CMyAdoClass::Delete(void)
{
	bool AllIsRight = true;
	try
	{
		if(FAILED(hr = m_recordset->Delete(adAffectCurrent)))
			AllIsRight=false;
	}
	catch(...)
	{
		AllIsRight = false;
	}

	return AllIsRight;
}

bool CMyAdoClass::ExecuteCommand(VARIANT_BOOL bStoredProcedure, VARIANT_BOOL bChangeRec)
{
	bool AllIsRight = true;

	try
	{
		_Recordset* prec = 0;
		if (bStoredProcedure)
			hr = m_command->Execute(pvtEmpty, pvtEmpty2, adCmdStoredProc, &prec);
		else
			hr = m_command->Execute(pvtEmpty, pvtEmpty2, adCmdText, &prec);

		if (SUCCEEDED(hr))
		{
			if (bChangeRec)
				m_recordset = prec;
			else
				prec->Release();
		}
		else
			AllIsRight=false;
	}
	catch(...)
	{
		AllIsRight=false;
	}

	return AllIsRight;
}

bool CMyAdoClass::ExecuteConnection(BSTR query, VARIANT_BOOL bChangeRec)
{
	bool AllIsRight = true;
	try
	{
		_Recordset* prec = 0;
		hr = m_connection->Execute(query, pvtEmpty, adCmdText, &prec);
		if (SUCCEEDED(hr))
		{
			if (bChangeRec)
				m_recordset = prec;
			else
				prec->Release();
		}
		else
			AllIsRight=false;
	}
	catch(...)
	{
		AllIsRight=false;
	}

	return AllIsRight;
}

bool CMyAdoClass::MoveFirst()
{
	bool AllIsRight = true;
	try
	{
		if(FAILED(hr = m_recordset->MoveFirst()))
			AllIsRight=false;
	}
	catch(...)
	{
		AllIsRight = false;
	}

	return AllIsRight;
}

short CMyAdoClass::get_BOF()
{
	short  newVal;
	try
	{
		hr = m_recordset->get_BOF(&newVal);
	}
	catch(...)
	{
	}

	return newVal;
}

bool CMyAdoClass::get_CommandText(BSTR * newVal)
{
	bool AllIsRight = true;
	try
	{
		if(FAILED(hr = m_command->get_CommandText(newVal)))
			AllIsRight=false;
	}
	catch(...)
	{
		AllIsRight = false;
	}

	return AllIsRight;
}

short CMyAdoClass::get_Empty()
{
	short bEmpty;
	try
	{
		hr = m_recordset->get_ADOEOF(&bEmpty);
		if (SUCCEEDED(hr) && bEmpty)
			hr = m_recordset->get_BOF(&bEmpty);
	}
	catch(...)
	{
	}

	return bEmpty;
}

short CMyAdoClass::get_EOF()
{
	short newVal;
	try
	{
		hr = m_recordset->get_ADOEOF(&newVal);
	}
	catch(...)
	{
	}

	return newVal;
}

bool CMyAdoClass::get_Field(int FieldIndex,VARIANT* FieldValue)
{
	bool AllIsRight = true;

	try
	{
		Fields* fields = 0;
		hr = m_recordset->get_Fields(&fields);
		Field* field = 0;
		if (SUCCEEDED(hr))
		{
			_variant_t idx((long)FieldIndex);
			hr = fields->get_Item(idx, &field);
		}

		if (SUCCEEDED(hr))
			hr = field->get_Value(FieldValue);

		if(field)
			field->Release();
		if(fields)
			fields->Release();

		if(FAILED(hr))
			AllIsRight = false;
	}
	catch(...)
	{
		AllIsRight = false;
	}

	return AllIsRight;
}

bool CMyAdoClass::get_FieldCount(long * newVal)
{
	bool AllIsRight = true;
	*newVal = 0;

	try
	{
		Fields* fields = 0;
		hr = m_recordset->get_Fields(&fields);
		if (SUCCEEDED(hr))
			hr = fields->get_Count(newVal);
		if (SUCCEEDED(hr))
			fields->Release();
		else
			AllIsRight=false;
	}
	catch(...)
	{
		AllIsRight=false;
	}

	return AllIsRight;
}

bool CMyAdoClass::MoveLast()
{
	bool AllIsRight = true;
	try
	{
		if(FAILED(hr = m_recordset->MoveLast()))
			AllIsRight=false;
	}
	catch(...)
	{
		AllIsRight = false;
	}

	return AllIsRight;
}

bool CMyAdoClass::MoveNext()
{
	bool AllIsRight = true;
	try
	{
		if(FAILED(hr = m_recordset->MoveNext()))
			AllIsRight=false;
	}
	catch(...)
	{
		AllIsRight = false;
	}

	return AllIsRight;
}

bool CMyAdoClass::OpenRecordset(VARIANT query,bool isBatchUpdate)
{
	bool AllIsRight = true;
	VARIANT v;
	V_VT(&v) = VT_DISPATCH;
	V_DISPATCH(&v) = (IDispatch*) m_connection;

	// Need the AddRef() as VariantClear() calls Release(), unless fAddRef
	// false indicates we're taking ownership
	//

	try
	{
		V_DISPATCH(&v)->AddRef();

		if(isBatchUpdate)
			hr = m_recordset->Open(query, v, adOpenKeyset, adLockBatchOptimistic, adCmdText);
		else
			hr = m_recordset->Open(query, v, adOpenDynamic, adLockOptimistic, adCmdText);

		if(FAILED(hr))
			AllIsRight=false;
	}
	catch(...)
	{
		AllIsRight=false;
	}

	return AllIsRight;
}

bool CMyAdoClass::put_CommandText(BSTR newVal)
{
	bool AllIsRight = true;
	try
	{
		if(FAILED(m_command->put_CommandText(newVal)))
			AllIsRight=false;
	}
	catch(...)
	{
		AllIsRight = false;
	}

	return AllIsRight;
}

bool CMyAdoClass::put_Field(int idx, VARIANT newVal)
{
	bool AllIsRight =true;

	try
	{
		Fields* fields = 0;
		hr = m_recordset->get_Fields(&fields);
		Field* field = 0;
		if (SUCCEEDED(hr))
		{
			hr = fields->get_Item(_variant_t((long)idx), &field);
		}

		if (SUCCEEDED(hr))
			hr = field->put_Value(newVal);

		if(FAILED(hr))
			AllIsRight=false;

		if (field)
			field->Release();
		if (fields)
			fields->Release();
	}
	catch(...)
	{
		AllIsRight=false;
	}

	return AllIsRight;
}

bool CMyAdoClass::put_StoredProc(BSTR newVal)
{
	bool AllIsRight = true;

	try
	{
		bool NeedReleaseString = false;
		if (newVal == NULL)
		{
			newVal = ::SysAllocString(L"create procedure MyProc @i integer, @g varchar(25), @g varchar(80) into Guns (ID, Gun, [Gun Description]) values (@i, @g, @d) return");
			NeedReleaseString = true;
		}
		hr = m_command->put_CommandText(newVal);
		_Recordset* prec = 0;
		if (SUCCEEDED(hr))
			hr = m_command->Execute(pvtEmpty, pvtEmpty2, adCmdText, &prec);
		if (SUCCEEDED(hr))
			prec->Release();
		else
			AllIsRight=false;

		if(NeedReleaseString)
			::SysFreeString(newVal);
	}
	catch(...)
	{
		AllIsRight=false;
	}

	return AllIsRight;
}

bool CMyAdoClass::ParamQuery(BSTR query, long idx1, BSTR idx2, BSTR idx3)
{
	bool AllIsRight = true;

	try
	{
		hr = ChangeParameter(0, adInteger, (_variant_t) idx1, adParamInput, -1);
		if (SUCCEEDED(hr))
			hr = ChangeParameter(1, adVarChar, (_variant_t) idx2, adParamInput, 25);
		if (SUCCEEDED(hr))
			hr = ChangeParameter(2, adVarChar, (_variant_t) idx3, adParamInput, 80);
		if (SUCCEEDED(hr))
			hr = m_command->put_CommandText(query);
		_Recordset* prec = 0;
		if (SUCCEEDED(hr))
			hr = m_command->Execute(pvtEmpty, pvtEmpty2, adCmdText, &prec);
		if (SUCCEEDED(hr))
			prec->Release();
		else
			AllIsRight=false;
	}
	catch(...)
	{
		AllIsRight=true;
	}

	return AllIsRight;
}

bool CMyAdoClass::MovePrev()
{
	bool AllIsRight = true;
	try
	{
		if(FAILED(hr = m_recordset->MovePrevious()))
			AllIsRight=false;
	}
	catch(...)
	{
		AllIsRight = false;
	}

	return AllIsRight;
}

bool CMyAdoClass::Requery()
{
	bool AllIsRight = true;
	try
	{
		if(FAILED(hr = m_recordset->Requery(0)))
			AllIsRight=false;
	}
	catch(...)
	{
		AllIsRight = false;
	}

	return AllIsRight;
}

bool CMyAdoClass::ChangeParameter(long idx, enum DataTypeEnum type, VARIANT value, enum ParameterDirectionEnum where, long size)
{
	bool AllIsRight = true;
	try
	{
		Parameters* params = 0;
		hr = m_command->get_Parameters(&params);

		_Parameter* param = 0;
		VARIANT v;
		V_VT(&v) = VT_I4;
		V_I4(&v) = idx;
		if (SUCCEEDED(hr))
			hr = params->get_Item(v, &param);

		if (SUCCEEDED(hr))
			hr = param->put_Type(type);
		if (SUCCEEDED(hr))
			hr = param->put_Value(value);
		if (SUCCEEDED(hr))
			hr = param->put_Direction(where);
		if (SUCCEEDED(hr))
			hr = param->put_Size(size);

		if(param)
			param->Release();
		if(params)
			params->Release();

		if (FAILED(hr))
			AllIsRight= false;
	}
	catch(...)
	{
		AllIsRight= false;
	}

	return AllIsRight;
}

bool CMyAdoClass::ADORelease()
{
	m_command = 0;
	m_recordset = 0;
	m_connection = 0;
	hr = S_OK;
	return true;
}

bool CMyAdoClass::Open(const WCHAR* ConnectStr, const WCHAR* UserName, const WCHAR* Password)
{
	bool AllIsRight=true;

	try
	{
		hr = m_connection->Open(_bstr_t(ConnectStr),_bstr_t(UserName),_bstr_t(Password),0);
		if (SUCCEEDED(hr))
			hr = m_command->putref_ActiveConnection(m_connection);

		if(FAILED(hr))
			AllIsRight=false;
	}
	catch(...)
	{
		AllIsRight=false;
	}

	return AllIsRight;
}

bool CMyAdoClass::GetFieldsName(long *pFieldsCount,LPWSTR** ppFieldsNameArray)
{
	bool AllIsRight = true;
	*pFieldsCount = 0;

	if(!get_FieldCount(pFieldsCount))
		return false;

	LPWSTR* ppp = (LPWSTR*)::CoTaskMemAlloc(sizeof(LPWSTR)**pFieldsCount);
	*ppFieldsNameArray = ppp;
	for(long i=0;i<*pFieldsCount;i++)
		ppp[i] = NULL;

	try
	{
		Fields* fields = 0;
		if(SUCCEEDED(hr))
			hr = m_recordset->get_Fields(&fields);

		for(long i=0;i<(*pFieldsCount);i++)
		{
			Field* field = 0;
			VARIANT idx;
			idx.vt = VT_I4;
			idx.lVal = i;
			if (SUCCEEDED(hr))
				hr = fields->get_Item(idx, &field);
			if (SUCCEEDED(hr))
			{
				_bstr_t name;
				hr = field->get_Name(name.GetAddress());
				long Len = (long)wcslen(name);
				ppp[i] = (LPWSTR)::CoTaskMemAlloc(sizeof(TCHAR)*(Len+1));
				wcscpy_s(ppp[i],Len+1,name);
			}

			if (field)
				field->Release();
		}

		if (fields)
			fields->Release();

		if(FAILED(hr))
			AllIsRight=false;
	}
	catch(...)
	{
		AllIsRight=false;
	}

	return AllIsRight;
}

bool CMyAdoClass::GetFieldsValue(long *pFieldsCount,VARIANT** ppFieldsValue)
{
	*pFieldsCount = 0;
	*ppFieldsValue = NULL;

	Fields* fields = 0;
	bool AllIsRight = true;

	try
	{
		hr = m_recordset->get_Fields(&fields);
		if(SUCCEEDED(hr))
			hr = fields->get_Count(pFieldsCount);

		VARIANT* pvt = (VARIANT*)::CoTaskMemAlloc(sizeof(VARIANT)**pFieldsCount);
		*ppFieldsValue = pvt;
		for(long i=0;i<*pFieldsCount;i++)
			::VariantInit(&pvt[i]);

		for(long i=0;i<*pFieldsCount;i++)
		{
			Field* field = 0;
			VARIANT idx;
			idx.vt = VT_I4;
			idx.lVal = i;
			if (SUCCEEDED(hr))
				hr = fields->get_Item(idx, &field);
			if (SUCCEEDED(hr))
				field->get_Value(&(pvt[i]));
			if (field)
				field->Release();
		}

		if (fields)
			fields->Release();

		if(FAILED(hr))
			AllIsRight=false;
	}
	catch(...)
	{
		AllIsRight=false;
	}

	return AllIsRight;
}

// 按照需要设置指定列的值
bool CMyAdoClass::PutFieldsValue(DWORD FieldIndexCount, long *FieldIndexArray,VARIANT* FieldsValue)
{
	bool AllIsRight = true;

	try
	{
		Fields* fields = 0;
		hr = m_recordset->get_Fields(&fields);

		for(DWORD i=0;i<FieldIndexCount;i++)
		{
			Field* field = 0;
			if (SUCCEEDED(hr))
				hr = fields->get_Item(_variant_t(FieldIndexArray[i]), &field);
			if (SUCCEEDED(hr))
				hr = field->put_Value(FieldsValue[i]);

			if (field)
				field->Release();
		}

		if (fields)
			fields->Release();

		if(FAILED(hr))
			AllIsRight=false;
	}
	catch(...)
	{
		AllIsRight=true;
	}

	return AllIsRight;
}

bool CMyAdoClass::AddNewRecord(void)
{
	bool AllIsRight = true;
	try
	{
		hr = m_recordset->AddNew();
		if(FAILED(hr))
			AllIsRight=false;
	}
	catch(...)
	{
		AllIsRight= false;
	}

	return AllIsRight;
}

bool CMyAdoClass::Update(bool isUpdateOrCancelUpdate)
{
	bool AllIsRight = true;

	try
	{
		if(isUpdateOrCancelUpdate)
			hr =  m_recordset->Update();
		else
			hr =  m_recordset->CancelUpdate();
		if(FAILED(hr))
			AllIsRight=false;
	}
	catch(...)
	{
		AllIsRight=false;
	}

	return AllIsRight;
}

bool CMyAdoClass::UpdateBatch(bool isUpdateOrCancelUpdate)
{
	bool AllIsRight = true;

	try
	{
		if(isUpdateOrCancelUpdate)
			hr =  m_recordset->UpdateBatch(adAffectAll);
		else
			hr =  m_recordset->CancelBatch(adAffectAll);
		if(FAILED(hr))
			AllIsRight=false;
	}
	catch(...)
	{
		AllIsRight=false;
	}

	return AllIsRight;
}

bool CMyAdoClass::GetRecordCount(LONG *pRecordCount)
{
	bool AllIsRight = true;
	try
	{
		if(FAILED(hr = m_recordset->get_RecordCount(pRecordCount)))
			AllIsRight=false;
	}
	catch(...)
	{
		AllIsRight = false;
	}

	return AllIsRight;
}

bool CMyAdoClass::DatabaseIsOpen(void)
{
	bool IsOpen = false;
	LONG ObjState=0;
	if(SUCCEEDED(m_connection->get_State(&ObjState)))
	{
		if(ObjState&(adStateOpen))
			IsOpen = true;
	}
	return IsOpen;
}

bool CMyAdoClass::RecordsetIsOpen(void)
{
	bool IsOpen = false;
	LONG ObjState=0;
	if(SUCCEEDED(m_recordset->get_State(&ObjState)))
	{
		if(ObjState&adStateOpen)
			IsOpen = true;
	}
	return IsOpen;
}

bool CMyAdoClass::GetRecordsetAllFieldsValue(long *pRowCount,long *pFieldCount,VARIANT** ppFieldsValue)
{
	*pRowCount = 0;
	get_FieldCount(pFieldCount);
	*ppFieldsValue = NULL;

	if(MoveFirst())
	{
		bool AllIsRight = true;
		NS_GeneralClassesAndFunction::CSimpleDynamicMemAlloctor<_variant_t*> AllRetBuff;
		AllRetBuff.SetExtraAllocMemCount(100);
		while(get_EOF()==0)
		{
			long ThisTimeRetCount = 0;
			VARIANT* pThisTimeRetValue = 0;
			if(GetFieldsValue(&ThisTimeRetCount,&pThisTimeRetValue))
			{
				if(ThisTimeRetCount==*pFieldCount)
				{
					(*pRowCount)++;
					for(long i=0;i<ThisTimeRetCount;i++)
					{
						_variant_t* pvt = new _variant_t(&pThisTimeRetValue[i]);
						AllRetBuff.Add(pvt);
					}
				}
				else
					AllIsRight = false;
			}
			else
				AllIsRight = false;

			if(pThisTimeRetValue)
			{
				for(long i=0;i<ThisTimeRetCount;i++)
					::VariantClear(&pThisTimeRetValue[i]);
				::CoTaskMemFree(pThisTimeRetValue);
			}

			MoveNext();
		}

		if(AllIsRight)
		{
			VARIANT* pvt = (VARIANT*)::CoTaskMemAlloc(sizeof(VARIANT)*AllRetBuff.GetCount());
			*ppFieldsValue = pvt;
			for(DWORD i=0;i<AllRetBuff.GetCount();i++)
			{
				::VariantInit(&pvt[i]);
				::VariantCopy(&pvt[i],AllRetBuff[i]);
			}
		}
		else
		{
			*pRowCount = 0;
			*pFieldCount = 0;
			*ppFieldsValue = NULL;
		}

		for(DWORD i=0;i<AllRetBuff.GetCount();i++)
		{
			_variant_t* pvt = AllRetBuff[i];
			delete pvt;
		}
		AllRetBuff.RemoveAll();

		return AllIsRight;
	}
	else
	{
		return false;
	}
}