A set of Ado classes

A set of ADO classes. version 2.04

I created these classes to make it easy to work with ado. For this I created the CADODatabseclass and the CADORecordset class

The CADODatabase Class

The CADODatabase class has a set of functions that corresponds to the _ConnectionPtr

CADODatabase::CADODatabase

Creates a CADODatabase object.

CADODatabase();

CADODatabase::Open

The Open function Opens a connection to a Database.

BOOL Open(LPCTSTR lpstrConnection = _T(""));

Parameters

LPCTSTR lpstrConnectionThe connection string.

See sample

Note: the class has the function SetConnectionString. You can insert the connecction string through this function. In this case you can do the following.

//Sample with Connection string for SQL Server

CADODatabase pAdoDb;
CString strConnection = _T("");

strConnection = _T("Provider=MSDASQL;PersistSecurityInfo=False;"
                   "Trusted_Connection=Yes;"
                   "Data Source=Access Sql Server;catalog=sampledb");
pAdoDb.SetConnectionString(strConnection);

if(pAdoDb.Open())
DoSomething();
.
.
.

//Sample with Connection String for Access database

CADODatabase pAdoDb;
CString strConnection = _T("");

strConnection = _T("Provider=Microsoft.Jet.OLEDB.4.0;"
              "Data Source=C:\\VCProjects\\ADO\\Test\\dbTest.mdb");
pAdoDb.SetConnectionString(strConnection);

if(pAdoDb.Open())
DoSomething();
.
.
.

CADODatabase::Execute

The Execute function executes a sql statement in the open database.

BOOL Execute(LPCTSTR lpstrExec)

Parameters

LPCTSTR lpstrExec A string pointer containinig the sql statement to execute.

CADODatabase pAdoDb;
CString strConnection = _T("");

strConnection = _T("Provider=MSDASQL;"
                   "PersistSecurityInfo=False;"
                   "Trusted_Connection=Yes"
                "Data Source=Access Sql Server;catalog=sampledb");

if(pAdoDb.Open(strConnection))
 pAdoDb.Execute("Delete From tblClients Where Cheker = 3");

Return Value

The function returns TRUE if was succesfully.

CADODatabase::GetRecordsAffected

The GetRecordsAffcted function returns the number of records affected to the last sql statement executed.

int GetRecordsAffected();

CADODatabase::GetActiveConnection

The GetActiveConnection returns the active connection.

_ConnectionPtr GetActiveConnection();

CADODatabase::GetRecordCount

GetRecordCount returns the number of records affected in a query.

DWORD GetRecordCount(_RecordsetPtr m_pRs);

Parameters

_RecorsetPtr m_Prs The recordset.

CADODatabase::BeginTransaction

Call this function to initiate a transaction. After you call BeginTransaction, updates you make to your data take effect when you commit the transaction.

long BeginTransaction();

CADODatabase::CommitTransaction

Call CommitTransaction function to commit a transaction for example save a group of edits and updates to one or more databases.

long CommitTransaction();

CADODatabase::RollbackTransaction

Call RollbackTransaction function to end the current transaction and restore all databases to their condition before the transaction was begun.

long RollbackTransaction();

CADODatabase::IsOpen

The IsOpen funtion returns the status of the connection with the database.

BOOL IsOpen();

Return Value

The function returns TRUE if the connection to database is open.

CADODatabase::Close

The Close function close the connection to the database.

void Close();

CADODatabase::SetConnectionString

With the SetConnectionString You can insert the connection string.

void SetConnectionString(LPCTSTR lpstrConnection);

Parameters

LPCTSTR lpstrConnection A connection string used for opening the database.

CADODatabase::GetConnectionString

The GetConnectionString function returns the connection string used for make a connection with a database.

CString GetConnectionString();

CADODatabase::GetLastError

The GetLastError function returns the last error code.

DWORD GetLastError();

CADODatabase::GetLastErrorString

The GetLastErrorString function returns the last error string.

CString GetLastErrorString();

The CADORecordset Class

The CADORecordset class has a set of functions that corresponds to the _RecordsetPtr.

CADORecordset::CADORecordset

Creates a CADORecordset object.

CADODatabase();
CADORecordset(CADODatabase* pAdoDatabase);

Parameters

CADODatabase* pAdoDatabase A CADODatabase object pointer.

CADORecordset::Open

The Open function opens a recordset

BOOL Open(_ConnectionPtr mpdb, LPCTSTR lpstrExec = _T(""), 
          int nOption = CADORecordset::openUnknown);
BOOL Open(LPCTSTR lpstrExec = _T(""), 
          int nOption = CADORecordset::openUnknown);

Parameters

_ConnectionPtr mpdb A connection pointer.

LPCTSTR lpstrExec A string pointer containinig an SQL select statement.

int nOption An integer that defines the access mode. The values are as follows:

CADORecordset::openUnknown

CADORecordset::openQuery

CADORecordset::openTable

CADORecordset::openStoredProc

Return Value

Returns TRUE if was successfully.

See sample

CADORecordset::Execute

The Execute function opens a recordset

BOOL Execute(CADOCommand* pCommand);

Parameters

CADOCommand* pCommand A CADOCommand pointer.

Return Value

Returns TRUE if was successfully.

See sample

CADORecordset::GetQuery;

GetQuery returns the string containing the SQL Select statement.

CString GetQuery();

CADORecordset::SetQuery

void SetQuery(LPCSTR strQuery);

Parameters

LPCTSTR strQuery A string pointer containinig an SQL Select statement.

CADORecordset::RecordBinding

BOOL RecordBinding(CADORecordBinding pAdoRecordBinding);

Parameters

CADORecordBinding pAdoRecordBinding

CADORecordset::GetRecordCount

GetRecordCount returns the number of records accessed in the recordset

DWORD GetRecordCount();

See sample

CADORecordset::IsOpen

IsOpen determines if the recordset is open.

BOOL IsOpen();

Return Value

This member function returns TRUE if the recordset has not been closed.

CADORecordset::Close

The Close function closes the recordset.

void Close();

See sample

CADORecordset::GetFieldCount

The GetFieldCount function returns the number of fields in the recordset.

long GetFieldCount();

CADORecordset::GetFieldValue

The GetFieldValue function returns a value that contains the value of a field.

BOOL GetFieldValue(LPCTSTR lpFieldName, int nValue);
BOOL GetFieldValue(int nIndex, int nValue);
BOOL GetFieldValue(LPCTSTR lpFieldName, long lValue);
BOOL GetFieldValue(int nIndex, long lValue);
BOOL GetFieldValue(LPCTSTR lpFieldName, unsigned long ulValue);
BOOL GetFieldValue(int nIndex, unsigned long ulValue);
BOOL GetFieldValue(LPCTSTR lpFieldName, double dbValue);
BOOL GetFieldValue(int nIndex, double dbValue);
BOOL GetFieldValue(LPCTSTR lpFieldName, CString strValue, 
                   CString strDateFormat = _T(""));
BOOL GetFieldValue(int nIndex, CString strValue, 
                   CString strDateFormat = _T(""));
BOOL GetFieldValue(LPCTSTR lpFieldName, COleDateTime time);
BOOL GetFieldValue(int nIndex, COleDateTime time);
BOOL GetFieldValue(LPCTSTR lpFieldName, bool bValue);
BOOL GetFieldValue(int nIndex, bool bValue);
BOOL GetFieldValue(LPCTSTR lpFieldName, COleCurrency cyValue);
BOOL GetFieldValue(int nIndex, COleCurrency cyValue);

Parameters

LPCTSTR lpFieldName A pointer to a string that contains the name of a field.

int nIndex A zero-based index of the field in the recordset’s Fields collection, for lookup by index.

double dbValue A reference to a object that will store the value of a field.

long lValue A reference to a object that will store the value of a field.

unsigned long ulValue A reference to a object that will store the value of a field.

int nValue A reference to a object that will store the value of a field.

CString strValue A reference to a object that will store the value of a field.

CString strDateFormat A formatting time string similar to the strftime formatting string. The more common are:

%a Abbreviated weekday name
%A Full weekday name
%b Abbreviated month name
%B Full month name
%c Date and time representation appropriate for locale
%d Day of month as decimal number (01 - 31)
%H Hour in 24-hour format (00 - 23)
%I Hour in 12-hour format (01 - 12)
%j Day of year as decimal number (001 - 366)
%m Month as decimal number (01 - 12)
%M Minute as decimal number (00 - 59)
%p Current locale’s A.M./P.M. indicator for 12-hour clock
%S Second as decimal number (00 - 59)
%U Week of year as decimal number, with Sunday as first day of week (00 - 53)
%w Weekday as decimal number (0 - 6; Sunday is 0)
%W Week of year as decimal number, with Monday as first day of week (00 - 53)
%x Date representation for current locale
%X Time representation for current locale
%y Year without century, as decimal number (00 - 99)
%Y Year with century, as decimal number

COleDateTime time A reference to a object that will store the value of a field.

bool bValue A reference to a object that will store the value of a field.

COleCurrency cyValue A reference to a object that will store the value of a field.

See sample

CADORecordset::IsFieldNull

The IsFieldNull function determines if the field data is null.

BOOL IsFieldNull(LPCTSTR lpFieldName);
BOOL IsFieldNull(int nIndex);

Parameters

LPCTSTR lpFieldName A pointer to a string that contains the name of a field.

int nIndex A zero-based index of the field in the recordset’s Fields collection, for lookup by index.

Return Value

This function returns TRUE if the field data is Null.

CADORecordset::IsFieldEmpty

The IsFieldEmpty function determines if the field data is Empty.

BOOL IsFieldEmpty(LPCTSTR lpFieldName);
BOOL IsFieldEmpty(int nIndex);

Parameters

LPCTSTR lpFieldName A pointer to a string that contains the name of a field.

int nIndex A zero-based index of the field in the recordset’s Fields collection, for lookup by index.

Return Value

This function returns TRUE if the field data is Empty.

CADORecordset::IsEof

BOOL IsEof();

Return Value

This function returns TRUE if the current position contains no records.

See sample

CADORecordset::IsBof

BOOL IsBof();

Return Value

This function returns TRUE if the current position is the bottom of the recordset.

CADORecordset::MoveFirst
CADORecordset::MoveNext
CADORecordset::MovePrevious
CADORecordset::MoveLast

This functions make the First/Next/Previous/or Last record of the recordset the current record.

void MoveFirst();
void MoveNext();
void MovePrevious();
void MoveLast();

See sample

CADORecordset::GetAbsolutePage
CADORecordset::SetAbsolutePage

Indicates on which page the current record resides.

long GetAbsolutePage();
void SetAbsolutePage(int nPage);

Parameters

int nPage The number of the page starting from 1.

See sample

CADORecordset::GetPageCount

GetPageCount returns the number of pages in the recordset.

long GetPageCount();

See sample

CADORecordset::GetPageSize
CADORecordset::SetPageSize

Indicates the number of records per page.

long GetPageSize();
void SetPageSize(int nSize);

Parameters

int nSize Set the number of records per page.

For example

CADORecordset pRs(&pDb);

if(pRs.Open("MyBigTable", CADORecordset::openTable))
{
    pRs.SetPageSize(5);
    for(register int nPageIndex = 1; nPageIndex <= pRs.GetPageCount();
        nPageIndex++)
    {
        pRs.SetAbsolutePage(nPageIndex);
        for(register int nRecNumber = 0; nRecNumber < pRs.GetPageSize();
            nRecNumber++)
        {
            long lVal;
            pRs.GetFieldValue("ID", lVal);
            pRs.MoveNext();

            if(pRs.IsEof())
                break;
        }
    }
    pRs.Close();
}

CADORecordset::GetAbsolutePosition
CADORecordset::SetAbsolutePosition

Indicates the position of the record in the recordset.

long GetAbsolutePosition();
void SetAbsolutePosition(int nPosition);

Parameters

int nPosition Move to the position in the recordset.

GetAbsolutePosition() can returns the position of the record or one of the following values:

CADORecordset::positionUnknown

CADORecordset::positionBOF

CADORecordset::positionEOF

CADORecordset::GetFieldInfo

GetFieldInfo returns the attributes of a field.

BOOL GetFieldInfo(LPCTSTR lpFieldName, CAdoFieldInfo* fldInfo);
BOOL GetFieldInfo(int nIndex, CAdoFieldInfo* fldInfo);

Parameters

LPCTSTR lpFieldName A pointer to a string that contains the name of a field.

int nIndex A zero-based index of the field in the recordset's Fields collection, for lookup by index.

CAdoFieldInfo* fldInfo A struct that returns the field attributes.

struct CADOFieldInfo
{
    char m_strName[30];
    short m_nType;
    long m_lSize;
    long m_lDefinedSize;
    long m_lAttributes;
    short m_nOrdinalPosition;
    BOOL m_bRequired;
    BOOL m_bAllowZeroLength;
    long m_lCollatingOrder;
};

The element m_nType of the class CADOFieldInfo can be one of the following values

CADORecordset::typeEmpty

CADORecordset::typeTinyInt

CADORecordset::typeSmallInt

CADORecordset::typeInteger

CADORecordset::typeBigInt

CADORecordset::typeUnsignedTinyInt

CADORecordset::typeUnsignedSmallInt

CADORecordset::typeUnsignedInt

CADORecordset::typeUnsignedBigInt

CADORecordset::typeSingle

CADORecordset::typeDouble

CADORecordset::typeCurrency

CADORecordset::typeDecimal

CADORecordset::typeNumeric

CADORecordset::typeBoolean

CADORecordset::typeError

CADORecordset::typeUserDefined

CADORecordset::typeVariant

CADORecordset::typeIDispatch

CADORecordset::typeIUnknown

CADORecordset::typeGUID

CADORecordset::typeDate

CADORecordset::typeDBDate

CADORecordset::typeDBTime

CADORecordset::typeDBTimeStamp

CADORecordset::typeBSTR

CADORecordset::typeChar

CADORecordset::typeVarChar

CADORecordset::typeLongVarChar

CADORecordset::typeWChar

CADORecordset::typeVarWChar

CADORecordset::typeLongVarWChar

CADORecordset::typeBinary

CADORecordset::typeVarBinary

CADORecordset::typeLongVarBinary

CADORecordset::typeChapter

CADORecordset::typeFileTime

CADORecordset::typePropVariant

CADORecordset::typeVarNumeric

CADORecordset::typeArray

For example

CADORecordset prs(&m_pDb);
if(prs.Open("Clients", CADORecordset::openTable))
{
    CADOFieldInfo pInfo;

    prs.GetFieldInfo("Description", &pInfo);

    if(pInfo.m_nType == CADORecordset::typeVarChar)
        AfxMessageBox("The type Description Field Is VarChar");
}

if(prs.Open("TestTable", CADORecordset::openTable))
{
    CADOFieldInfo* fInfo = new CADOFieldInfo;

    prs.GetFieldInfo(0, fInfo);
    CString strFieldName = fInfo->m_strName;
    prs.Close();
}

Return Value

Returns TRUE if was successfully.

CADORecordset::GetChunk

This function returns all, or a portion, of the contents of a large text or binary data Field object.

BOOL GetChunk(LPCTSTR lpFieldName, CString& strValue);
BOOL GetChunk(int nIndex, CString& strValue);
BOOL GetChunk(LPCTSTR lpFieldName, LPVOID pData);
BOOL GetChunk(int nIndex, LPVOID pData);

Parameters

LPCTSTR lpFieldName A pointer to a string that contains the name of a field.

int nIndex A zero-based index of the field in the recordset's Fields collection, for lookup by index.

CString& strValue A string pointer that contains the data that returns from the object.

LPVOID pData A pointer that contains the data that returns from the object.

Return Value

Returns TRUE if was successfully.

See sample

CADORecordset::AppendChunk

This function appends data to a large text or binary data Field.

BOOL AppendChunk(LPCTSTR lpFieldName, LPVOID lpData, UINT nBytes);
BOOL AppendChunk(int nIndex, LPVOID lpData, UINT nBytes);

Parameters

LPCTSTR lpFieldName A pointer to a string that contains the name of a field.

int nIndex A zero-based index of the field in the recordset's Fields collection, for lookup by index.

LPVOID lpData A pointer that contains the data to append to the object.

UINT nBytes A UINT that indicates the size of the data to be inserted.

Return Value

Returns TRUE if was successfully.

For example

//Sample of AppendChunck
prs.AddNew();
prs.SetFieldValue("ID", 5);
prs.SetFieldValue("Description", "Client 05");
prs.SetFieldValue("Checker", 1);
prs.AppendChunk("Document", "This Document is the story of Bob and his Friends...", 37);
prs.Update();

//Sample of GetChunck
char data[1024];
prs.GetChunk("Document", (LPVOID)&data);

CADORecordset::GetString

This function returns a recordset as a string.

CString GetString(LPCTSTR lpCols, LPCTSTR lpRows, LPCTSTR lpNull, 
                  long numRows = 0);

Parameters

LPCTSTR lpCols A columns delimiter.

LPCTSTR lpRows A rows delimiter.

LPCTSTR lpNull A expression that represents a null value.

long numRows The number of rows affected.

CADORecordset::GetLastError

The GetLastError function returns the last error code.

DWORD GetLastError();

CADORecordset::GetLastErrorString

The GetLastErrorString function returns the last error string.

CString GetLastErrorString();

CADORecordset::AddNew

The AddNew function adds a record in the open recordset.

BOOL AddNew();

Return Value

Returns TRUE if was successfully.

See sample

CADORecordset::Edit

The Edit function allow changes to the current record in the open recordset.

void Edit();

CADORecordset::Delete

The Delete function deletes the current record in the open recordset.

BOOL Delete();

Return Value

Returns TRUE if was successfully.

CADORecordset::Update

The Update function updates the pending updates in the current record.

BOOL Update();

Return Value

Returns TRUE if was successfully.

See sample

CADORecordset::CancelUpdate

The CancelUpdate function cancels any pending update in the open recordset.

void CancelUpdate();

CADORecordset::SetFieldValue

The SetFieldValue function sets the value of a field.

BOOL SetFieldValue(int nIndex, int nValue);
BOOL SetFieldValue(LPCTSTR lpFieldName, int nValue);
BOOL SetFieldValue(int nIndex, long lValue);
BOOL SetFieldValue(LPCTSTR lpFieldName, long lValue);
BOOL SetFieldValue(int nIndex, unsigned long ulValue);
BOOL SetFieldValue(LPCTSTR lpFieldName, unsigned long ulValue);
BOOL SetFieldValue(int nIndex, double dblValue);
BOOL SetFieldValue(LPCTSTR lpFieldName, double dblValue);
BOOL SetFieldValue(int nIndex, CString strValue);
BOOL SetFieldValue(LPCTSTR lpFieldName, CString strValue);
BOOL SetFieldValue(int nIndex, COleDateTime time);
BOOL SetFieldValue(LPCTSTR lpFieldName, COleDateTime time);
BOOL SetFieldValue(int nIndex, bool bValue);
BOOL SetFieldValue(LPCTSTR lpFieldName, bool bValue);
BOOL SetFieldValue(int nIndex, COleCurrency cyValue);
BOOL SetFieldValue(LPCTSTR lpFieldName, COleCurrency cyValue);

Parameters

LPCTSTR lpFieldName A pointer to a string that contains the name of a field.

int nIndex A zero-based index of the field in the recordset’s Fields collection, for lookup by index.

int nValue A pointer to a object containing the value of the field.

long lValue A pointer to a object containing the value of the field.

unsigned long lValue A pointer to a object containing the value of the field.

double dbValue A pointer to a object containing the value of the field.

CString strValue A pointer to a object containing the value of the field.

COleDateTime time A pointer to a object containing the value of the field.

bool bValue A pointer to a object containing the value of the field.

COleCurrency cyValue A pointer to a object containing the value of the field.

Return Value

Returns TRUE if was successfully.

See sample

For example

CADORecordset prs(&m_pAdoDb);
prs.Open("Test", openTable);

prs.AddNew();
prs.SetFieldValue(0, "dataU");
prs.SetFieldValue(1, "data01");
prs.SetFieldValue(2, (long)51000);
COleDateTime time = COleDateTime(2001,6,15, 10, 8, 30);
prs.SetFieldValue(3, time);
prs.Update();

CADORecordset::Find

The Find function locates a string from the current position in the open recordset using a operator of comparison.

BOOL Find(LPCTSTR lpFind, 
          int nSearchDirection = CADORecordset::searchForward);

Parameters

LPCTSTR lpFind A string expression used to locate the record.

int nSearchDirection A value that indicate the type of operation. The possible values are:

CADORecordset::searchForward Find the next location

CADORecordset::searchBackward Find the previous location

Return Value

Returns TRUE if was successfully.

For example

if(prs.Find("Field0 LIKE 'dataU%'"))
{
prs.Delete();
while(prs.FindNext())
prs.Delete();
}

CADORecordset::FindFirst

The FindFirst function locates a string from the begin in the open recordset using a operator of comparison.

BOOL FindFirst(LPCTSTR lpFind);

Parameters

LPCTSTR lpFind A string expression used to locate the record.

Return Value

Returns TRUE if was successfully.

CADORecordset::FindNext

The FindNext function locates a string from the last position in the open recordset using the operator of comparison used in FindFirst or Find functions.

BOOL FindNext();

Return Value

Returns TRUE if was successfully.

See sample

CADORecordset::GetBookmark

The GetBookmark function saves the position of the current record.

BOOL GetBookmark();

Return Value

Returns TRUE if was successfully.

CADORecordset::SetBookmark

The SetBookmark function returns to the position saved at any time.

void SetBookmark();

Return Value

Returns TRUE if was successfully.

CADORecordset::SetFilter

The SetFilter Indicates a filter for data in a open Recordset.

BOOL SetFilter(LPCTSTR strFilter);

Parameters

LPCTSTR strFilter a string compose by one or more individual clauses concatenated with AND or OR operators.

Return Value

Returns TRUE if was successfully.

For example

CADORecordset m_pRs;

m_pRs = CADORecordset(&m_pDb);

if(m_pRs.Open("tblTest", CADORecordset::openTable))
{
    CString strFilter = _T("LastName = 'Smith' And Age > 30");
    m_pRs.SetFilter(strFilter);
    .
    .
    m_pRs.Close();
}

CADORecordset::SetSort

The SetSort function sets the sort order for records in a CADORecordset object.

BOOL SetSort(LPCTSTR lpstrCriteria);

Parameters

LPCTSTR lpstrCriteria A String that contains the ORDER BY clause of an SQL statement

Return Value

Returns TRUE if was successfully.

CADORecordset::GetRecordset

The GetRecordset function returns a pointer to an open recordset.

_RecordsetPtr GetRecordset();

CADORecordset::GetActiveConnection

The GetActiveConnection returns the active connection.

_ConnectionPtr GetActiveConnection();

CADORecordset::Clone

The Clone function Creates a duplicate CADORecordset object from an existing CADORecordset object.

BOOL Clone(CADORecordset pAdoRecordset);

Parameters

CADORecordset pAdoRecordset is an existing CADORecordset Object.

Return Value

Returns TRUE if was successfully.

For example

CADORecordset m_pRs; //Original Recordset
CADORecordset RS;   //Duplicate Recordset

m_pRs = CADORecordset(&m_pDb);

if(m_pRs.Open("tblTest", CADORecordset::openTable)) //Open the Original
Recordset
{
    m_pRs.Clone(RS); //Create the clone of the original Recordset

    long lClonRecs = RS.GetRecordCount();
    long lOrigRecs = m_pRs.GetRecordCount();
    .
    .
    RS.Close();
    m_pRs.Close();
}

CADORecordset::SaveAsXML

The SaveAsXML function Save the open recordset in a file with XML Format.

BOOL SaveAsXML(LPCTSTR lpstrXMLFile);

Parameters

LPCTSTR strXMLFile a string that indicate the complete path name of the file where the Recordset to be saved.

Return Value

Returns TRUE if was successfully.

CADORecordset::OpenXML

The OpenXML function Open a XML File Format in a recordset.

BOOL OpenXML(LPCTSTR lpstrXMLFile);

Parameters

LPCTSTR strXMLFile a string that indicate the complete path name of the XML file to be opened.

Return Value

Returns TRUE if was successfully.

For example

CADORecordset pRs;

if(prs.OpenXML("C:\\My.XML"))
{
    CString strClient = _T("");
    double dblVal = 0;
    long lrecs = prs.GetRecordCount();

    if(!prs.IsEof())
        prs.MoveFirst();

    prs.GetFieldValue("Budget", dblVal);
    prs.GetFieldValue("ClientName", strClient);

    prs.Close();
}

Sample 01:

CADODatabase pAdoDb;
CString strConnection = "";

strConnection = _T("Provider=MSDASQL;"
                  "PersistSecurityInfo=False;Trusted_Connection=Yes
Data Source=Access Sql Server;catalog=sampledb");

if(pAdoDb.Open((LPCTSTR)strConnection))
{
    CString strQry = _T("");
    int numRecords;

    strQry.Format(_T("sp_StoreClientFields_ps '%s', %d"),
                  (LPCTSTR)strParam1, nParam2);

    CADORecordset pRs(&pAdoDb);

    if(!pRs.Open((LPCTSTR)strQry))
        return FALSE;

    numRecords = pRs.GetRecordCount();
    while(!pRs.IsEof())
    {
      CString strVal = _T("");
      int nVal = 0;
      //Get Numeric Field Value
      pRs.GetFieldValue("NumField1", nVal)

      //Get String Field Data
      pRs.GetFieldValue("StrField..", strVal)
        DoSomething(nVal, strVal);

        pRs.MoveNext();
    }
    pRs.Close();
}
else
return FALSE;

The CADOParameter Class

The CADOParameter class has a set of functions that corresponds to the _ParameterPtr.

CADOParameter::CADOParameter

Creates a CADOParameter object.

CADOParameter(int nType, long lSize = 0, int nDirection = paramInput, 
              CString strName = _T(""));

Parameters

int nType A int value that specifies the data type of the CADOParameter object. Can be one of the values specified in CADORecordset::GetFieldInfo. If are using CADORecordset::typeNumeric or CADORecordset::typeDecimal, must to define the precision and scale values.

long lSize = 0 A optional long value that specifies the maximum length for the parameter value in Bytes or characters.

int nDirection = paramInput A optional int value that specifies the direction of the CADOParameter object. Can be one of following values:

CADOParameter::paramUnknown Indicates that the parameter direction is unknown.

CADOParameter::paramInput Default. Indicates that the parameter represents an input parameter.

CADOParameter::paramOutput Indicates that the parameter represents an output parameter.

CADOParameter::paramInputOutput Indicates that the parameter represents both an input and output parameter.

CADOParameter::paramReturnValue Indicates that the parameter represents a return value.

CString strName = _T("") A optional string that specifies the name of the CADOParameter object.

See sample

CADOParameter::SetValue

The SetValue function sets the value for the CADOParameter object.

BOOL SetValue(int nValue);
BOOL SetValue(long lValue);
BOOL SetValue(double dbValue);
BOOL SetValue(CString strValue);
BOOL SetValue(COleDateTime time);
BOOL SetValue(_variant_t vtValue);

Parameters

int nValue A int value containing the parameter value.

long lValue A long value containing the parameter value.

double dbValue A double value containing the parameter value.

CString strValue A string value containing the parameter value.

COleDateTime time A time value containing the parameter value.

_variant_t vtValue A variant value containing the parameter value.

Return Value

Returns TRUE if was successfully.

See sample

CADOParameter::SetPrecision

The SetPrecison function sets the precision for the CADOParameter object.

void SetPrecision(int nPrecision);

CADOParameter::SetScale

The SetScale function sets the scale for the CADOParameter object.

void SetScale(int nScale);

CADOParameter::GetValue

The GetValue function returns the value of the CADOParameter object.

BOOL GetValue(int& nValue);
BOOL GetValue(long& lValue);
BOOL GetValue(double& dbValue);
BOOL GetValue(CString& strValue, CString strDateFormat = _T(""));
BOOL GetValue(COleDateTime& time);
BOOL GetValue(_variant_t& vtValue);

Parameters

int& nValue A reference to a int that will store the value of the parameter.

long& lValue A reference to a long that will store the value of the parameter.

double& dbValue A reference to a double that will store the value of the parameter.

CString& strValue A reference to a strings that will store the value of the parameter.

CString strDateFormat = _T("") A formatting time string similar to the strftime formatting string.

COleDateTime& time A reference to a time object that will store the value of the parameter.

_variant_t& vtValue A reference to a variant object that will store the value of the parameter.

Return Value

Returns TRUE if was successfully.

See sample

CADOParameter::SetName

The SetName function sets the name of the CADOParameter object

CString SetName(CString strName);

Parameters

CString strName A string specifing the parameter name.

CADOParameter::GetName

The GetName function returns the CADOParameter object.

CString GetName();

CADOParameter::GetType

The GetType function returns the type of the CADOParameter object.

int GetType();

CADOParameter::GetParameter

The GetParameter function returns a pointer to a _Parameter object

_ParameterPtr GetParameter();

The CADOCommand Class

The CADOCommand class has a set of functions that corresponds to the _CommandPtr.

CADOCommand::CADOCommand

Creates a CADOCommand object.

CADOCommand(CADODatabase* pAdoDatabase, CString strCommandText = _T(""), 
            int nCommandType = typeCmdStoredProc);

Parameters

CADODatabase* pAdoDatabase A CADODatabase object pointer.

CString strCommandText = _T("") A optional string that indicates the text of the CADOCcommand object.

int nCommandType = typeCmdStoredProc A optional int value that indicates the type of the CADOCommand object. Can be one of the following values:

CADOCommand::typeCmdText Evaluates CommandText as a textual definition of a command or stored procedure call.

CADOCommand::typeCmdTable Evaluates CommandText as a table name whose columns are all returned by an internally generated SQL query.

CADOCommand::typeCmdTableDirect Evaluates CommandText as a table name whose columns are all returned.

CADOCommand::typeCmdStoredProc Default. Evaluates CommandText as a stored procedure name.

CADOCommand::typeCmdUnknown Indicates that the type of command in the CommandText property is not known.

CADOCommand::typeCmdFile Evaluates CommandText as the file name of a persistently stored Recordset. Used with Recordset.Open or Requery only.

See sample

CADOCommand::AddParameter

The AddParameter function

BOOL AddParameter(CADOParameter* pAdoParameter);
BOOL AddParameter(CString strName, int nType, int nDirection, 
                  long lSize, int nValue);
BOOL AddParameter(CString strName, int nType, int nDirection, 
                  long lSize, long lValue);
BOOL AddParameter(CString strName, int nType, int nDirection, 
                  long lSize, double dblValue, int nPrecision = 0, 
                  int nScale = 0);
BOOL AddParameter(CString strName, int nType, int nDirection, 
                  long lSize, CString strValue);
BOOL AddParameter(CString strName, int nType, int nDirection, 
                  long lSize, COleDateTime time);
BOOL AddParameter(CString strName, int nType, int nDirection, 
                  long lSize, _variant_t vtValue, int nPrecision = 0, 
                  int nScale = 0);

Parameters

CADOParameter* pAdoParameter A pointer to a CADOParameter object

CString strName a string that specifies the name of the parameter.

int nType A int value that specifies the data type of the CADOParameter object. Can be one of the values specified in CADORecordset::GetFieldInfo. If are using CADORecordset::typeNumeric Or CADORecordset::typeDecimal, must to define the precision and scale values.

int nDirection A int value that specifies the direction of the CADOParameter object. Can be one of the values specified in CADOParameter::CADOParameter

long lSize A long value that specifies the maximum length for the parameter value in Bytes or characters.

int nValue A int value containing the parameter value.

long lValue A long value containing the parameter value.

double dblValue A double value containing the parameter value.

int nPrecision A int value containing the precision of the parameter value.

int nScale A int value containing the scale of the parameter value.

CString strValue A string value containing the parameter value.

COleDateTime time A time value containing the parameter value.

_variant_t vtValue A variant value containing the parameter value.

Return Value

Returns TRUE if was successfully.

See sample

CADOCommand::SetText

The SetText function sets the command text of the CADOCommand object.

void SetText(CString strCommandText);

Parameters

CString strCommandText A string that indicates the command text.

CADOCommand::GetText

The GetText function returns the command text of the CADOCommand object.

CString GetText();

CADOCommand::SetType

The SetType function sets the type of the CADOCommand object.

void SetType(int nCommandType);

Parameters

int nCommandType A int value that indicates the type of command.

CADOCommand::GetType

The GetType function returns the type of the CADOCommand object.

int GetType();

CADOCommand::GetCommand

The GetCommand function returns a Command pointer

_CommandPtr GetCommand();

CADOCommand::Execute

The Execute function executes a command text.

BOOL Execute();

Return Value

Returns TRUE if was successfully.

CADOCommand::GetRecordsAffected

The GetRecordsAffcted function returns the number of records affected to the last command executed.

int GetRecordsAffected();

Sample 02:

//SQL SCRIPT...
Create Procedure sp_OutputTest
@IN1 int,
@OutInt int Output,
@OutChar varchar(20) Output
As
    SELECT
        @OutChar = 'Hello World'
    SELECT
        @OutInt = 10 * @IN1

    return (90)
GO

//Visual C++ Code...
CADORecordset prs(&pDb);

CADOParameter pParamRetVal(CADORecordset::typeInteger, sizeof(int),
                           CADOParameter::paramReturnValue);
CADOParameter pParamIn(CADORecordset::typeInteger, sizeof(int));
CADOParameter pParamOutInt(CADORecordset::typeInteger, sizeof(int),
                           CADOParameter::paramOutput);
CADOParameter pParamOutChar(CADORecordset::typeChar, sizeof(char) * 20,
                            CADOParameter::paramOutput);

pParamIn.SetValue(2);

CADOCommand pCmd(&pDb, "sp_OutputTest");

pCmd.AddParameter(&pParamRetVal);
pCmd.AddParameter(&pParamIn);
pCmd.AddParameter(&pParamOutInt);
pCmd.AddParameter(&pParamOutChar);

CADORecordset pRs(&pDb);

if(pRs.Execute(&pCmd))
{
    int nVal = 0;
    int nRetVal = 0;
    CString str = _T("");

    pParamRetVal.GetValue(nRetVal);
    pParamOutInt.GetValue(nVal);
    pParamOutChar.GetValue(str);
}

Common Questions:

How Bind the MS Data Grid Control to CADORecordset:

First: You need to add the MS Data Grid Control to your Application.
Second: Apply the CADORecordset::GetRecordset function to the SetRefDataSource Function of the data Control class.

if(m_pRs.Open("tblTest", CADORecordset::openTable))
{
    //m_datagridctrl is the CDataGrid Class...
    m_datagridctrl.SetCaption("Clients");
    m_datagridctrl.SetRefDataSource(NULL);
    m_datagridctrl.SetRefDataSource((LPUNKNOWN)m_pRs.GetRecordset());
    m_datagridctrl.Refresh();
}

How do I resort a Recordset?

The Answer is Easy. You have several functions and properties that are part of _RecordsetPtr.
Using CADORecordset::GetRecordset() you get a pointer to _RecordsetPtr, You can use it for set the Sort Property.

Note: You don't need to use _RecordsetPtr::Refresh()

if(m_pRs.Open("tblTest", CADORecordset::openTable))
{
    m_pRs.GetRecordset()->PutSort("Field01 DESC");
}

Now: you have the function CADORecordset::SetSort();

fatal error C1010: unexpected end of file while looking for precompiled header directive

For solve this common problem You need to use automatic use of precompliled headers. Select project/settings (or ALT+F7) then, in the Project Settings Dialog, select the C/C++ tab, then select the Precompiled Headers item in the combo box, and select the Automatic use of precompliled headers option.

How do you get the return codes from your stored procedures in SQL Server?

You must to use the CADOParameter Class. CADOParameter::paramReturnValue gets the value that is returned by the Stored.
Always must be an Integer and must be in the First Place in the list of Parameters. If you are not using parameters of input or output, you only need to use this parameter....

See sample

I've been trying to work with the CADOCommand class and be able to pass in a UUID field. I've not be very successful so far. Do you by chance have an example that shows this?

You are talking about uniqueidentifier type Field?
Okey, the problem is the following:
First you can update the table using the SQL Syntax NEWID().
That command will do for you a unique identifier string like: "{B6B83A8C-F92C-4FA7-9641-631143E6056C}"...
If you want to insert data into that type of field you need to use a string like that...
In conclution we can say:
You need to use a string parameter Type, with the GUID Format, and must to be Unique... Are a lot of condition!!!...

But I wrote a Sample:
In the following sample you can see that I used the UUID parameter as string.
You can see in the Stored procedure that the parameter is uniqueidentifier type.

//Visual C++ Sample
CADOParameter pParamRet(CADORecordset::typeInteger, sizeof(int),
                        CADOParameter::paramReturnValue);
CADOParameter pParamInChar(CADORecordset::typeChar, sizeof(char) * 40,
                           CADOParameter::paramInput);
CADOParameter pParamID(CADORecordset::typeInteger, sizeof(int));

CString strUUID = CString("{B6B83A8C-F92C-4FA7-9641-631143E6056C}");
pParamInChar.SetValue(strUUID);
pParamID.SetValue(1);
CADOCommand pCmd(&m_pDB, "sp_UUIDTest");

pCmd.AddParameter(&pParamRet);
pCmd.AddParameter(&pParamInChar);
pCmd.AddParameter(&pParamID);

if(pRs.Execute(&pCmd))
{
    int nVal = 0;
    pParamRet.GetValue(nVal);
}

//SQL SCRIPT
Create Procedure sp_UUIDTest
@UUID uniqueidentifier = NULL,
@ID integer
AS
    UPDATE
        systable01
    SET
        UUID = @UUID
    WHERE
        sysfield00 = @ID

    if @@Error = 0
        return 1
    else
        return 99
GO

Carlos A. Antollini.

Descargar fuente de las clases: AdoClass.zip (10 Kb).

Descargar fuente de un ejemplo: DataBindingSample.zip (98 Kb).

Para ver éste y otros artículos de Carlos Antollini también puede visitar Code Project:

http://www.codeproject.com/script/Articles/list_articles.asp?userid=7401

Carlos Antollini es argentino y ha desarrollado un software para datawarehousing íntegramente en Visual C++, llamado Pi Five. www.pifive.com.ar

Volver a la página principal