
// Programmers Notebook: Helper class for _RecordsetPtr objects and 
// ADO database access.

class CPNADORecordset : public _RecordsetPtr
{
public:
	
	CPNADORecordset()
	{
		// Initialize the _RecordsetPtr base class
		CreateInstance( "ADODB.Recordset" );
	}

	static HRESULT PNExecute( const char* Command )
	{
		// Execute a SQL command
		_bstr_t bstrCommand = Command;
		_variant_t Records;
		
		// Note: Documentation incorrectly states Execute requires 4 parameters,
		// when in reality it only requires 3.
		return( m_Connection->Execute(bstrCommand, &Records, -1 ) );
	}

	static HRESULT PNConnect( const char* Datasource, const char* Provider=0 )
	{
		// Connect to a database, initializing COM and the m_Connection member
		static bool Initialized=false;

		if( !Initialized )
		{
			// Initialize COM.  Not a problem if done repeatedly.
			CoInitialize(0);
			m_Connection.CreateInstance( "ADODB.Connection" );
			Initialized = true;
		}

		if( m_Connection->GetState() & adStateOpen )
			m_Connection->Close();

		CString StrConnect;
		StrConnect.Format( "Provider=%s; Data Source=%s;", 
			Provider?Provider : "Microsoft.Jet.OLEDB.4.0",
			Datasource );
		_bstr_t bstrConnect = (LPCSTR)StrConnect;

		return( m_Connection->Open( (LPCSTR)StrConnect, "", "", -1 ) );
	}

	HRESULT PNOpen( const _variant_t & Query ) 
	{
		// Open a table or SQL Select statement
		HRESULT Result;
		if( (*(_RecordsetPtr*)this)->GetState() & adStateOpen )
			(*(_RecordsetPtr*)this)->Close();
		Result = (*(_RecordsetPtr*)this)->Open( Query, m_Connection.GetInterfacePtr(), adOpenDynamic, adLockOptimistic, -1 );
		return( Result );
	}

	HRESULT PNOpen( const char* Query ) 
	{ // Overloaded for CString ease of use
		HRESULT Result;
		if( (*(_RecordsetPtr*)this)->GetState() & adStateOpen )
			(*(_RecordsetPtr*)this)->Close();

		Result = (*(_RecordsetPtr*)this)->Open( Query, m_Connection.GetInterfacePtr(), adOpenDynamic, adLockOptimistic, -1 );
		return( Result );
	}

	bool PNGetField( const char* Fieldname, CString& Dest )
	{
		// Retrieves a field value, and converts it into a CString
		bool IsNull;
		_variant_t FieldVal = (*(_RecordsetPtr*)this)->GetCollect( Fieldname );
		IsNull = FieldVal.vt == VT_NULL;

		if( IsNull )
			Dest.Empty();
		else
			Dest = (char*) _bstr_t( FieldVal );
		return( !IsNull );
	}
	void PNSetField( const char* FieldName, const char* Src )
	{
		// Sets a field value from a string
		(*(_RecordsetPtr*)this)->PutCollect( FieldName, Src );
	}

	bool PNGetField( const char* Fieldname, int& Dest )
	{
		// Retrieves a field value, and converts it into an integer
		bool IsNull=false;
		_variant_t FieldVal;
		if( (*(_RecordsetPtr*)this)->GetState() & adStateOpen )
		{
			FieldVal = (*(_RecordsetPtr*)this)->GetCollect( Fieldname );
			IsNull = FieldVal.vt == VT_NULL;
		}

		if( IsNull )
			Dest = 0;
		else
		{
			FieldVal.ChangeType( VT_INT );
			Dest = FieldVal.intVal;
		}
		return( !IsNull );
	}
	void PNSetField( const char* FieldName, int Src )
	{
		// Sets a field value from an integer
		(*(_RecordsetPtr*)this)->PutCollect( FieldName, (long)Src );
	}

	bool PNGetField( const char* Fieldname, double& Dest )
	{
		// Gets a field vlaue from a recordset, into a double variable
		bool IsNull=false;
		_variant_t FieldVal;
		if( (*(_RecordsetPtr*)this)->GetState() & adStateOpen )
		{
			FieldVal = (*(_RecordsetPtr*)this)->GetCollect( Fieldname );
			IsNull = FieldVal.vt == VT_NULL;
		}

		if( IsNull )
			Dest = 0;
		else
		{
			FieldVal.ChangeType( VT_R8 );
			Dest = FieldVal.dblVal;
		}
		return( !IsNull );
	}
	
	void PNSetField( const char* FieldName, double Src )
	{
		// Sets a field value from a double 
		(*(_RecordsetPtr*)this)->PutCollect( FieldName, Src );
	}

	bool PNGetField( const char* Fieldname, COleDateTime& Dest )
	{
		// Gets a field vlaue from a recordset, into a COleDateTime variable
		bool IsNull=false;
		_variant_t FieldVal;

		if( (*(_RecordsetPtr*)this)->GetState() & adStateOpen )
		{
			FieldVal = (*(_RecordsetPtr*)this)->GetCollect( Fieldname );
			IsNull = FieldVal.vt == VT_NULL;
		}

		if( IsNull )
			Dest = COleDateTime();
		else
		{
			FieldVal.ChangeType( VT_DATE );
			Dest = FieldVal.date;
		}
		return( !IsNull );
	}
	void PNSetField( const char* FieldName, COleDateTime& Src )
	{
		// Sets a field value from a COleDateTime object
		(*(_RecordsetPtr*)this)->PutCollect( FieldName, (DATE)Src );
	}


	int PNGetNextID( const char* Tablename )
	{
		// Helper function to retrieve the next unique ID for a table,
		// from the TableIDs table.  Demonstrate record insertion and editing.
		int Ret=0;
		CString Str;
		Str.Format( "SELECT * from TableIDs where Tablename='[%s]'", (LPCSTR)Tablename );

		CPNADORecordset TableIDs;
		if( TableIDs.PNOpen( Str ) == S_OK )
		{
			if( TableIDs->GetIsEOF() )
			{
				TableIDs->AddNew();
				Ret=1;
				TableIDs.PNSetField( "LastID", Ret );
				Str.Format( "[%s]", Tablename );
				TableIDs.PNSetField( "TableName", Str );
			}
			else
			{
				TableIDs.PNGetField( "LastID", Ret );
				Ret++;
				TableIDs.PNSetField( "LastID", Ret );
			}
			TableIDs->Update();
		}
		return( Ret );
	}

	static _ConnectionPtr m_Connection;

};


