SolidWidgets Database Engine

Introduction

Free for PERSONAL and COMMERCIAL use

The SolidWidgets library includes three classes for accessing and manipulating databases, memory, SQL, and the SolidWidgets native database engine. All three classes implement the swDatabase interface, so they all have the exact same function signatures. To switch your code from using the memory database to SQL, all you have to do is declare an instance of swDbADO instead of swDbCache, and the rest of your code remains exactly the same. The same goes for native database engine too. This is extremely useful if you ever decide to change the type of underlying database that your application uses, you will not have to change a single line of code, except the type of the connection instance. For example, let's assume you initially wanted your application to use the native database engine, and you write all your database access code against that engine; some time in the future, you decide to allow your users to configure your application to connect to SQL Server instead of the native engine, you can code your application to declare an instance of swDatabase, and at initialization, you read some configuration file to determine the type of database to connect to, and then create the proper database instance to connect to that database. Following is an example of how to do just that:

Using the SolidWidgets Database classes

Declare an instance of swDatabase

#include <swDbADO.h>
#include <swDb.h>

class MyClassHeader
{
    swDatabase *m_connection;
    
public:

    MyClassHeader();
    virtual ~MyClassHeader();
};

Create the instance of the database, and create/open a connection to the database

#include <MyClassHeader.h>

MyClassHeader::MyClassHeader()
{
    // Read your application configuration file to determine the type
    // of database configured for your application to connect to:
    swString databaseType,connectionStrOrDbPath;
    YourReadConfigFile(databaseType,connectionStrOrDbPath);
    
    // Intantiate the proper database connection instance
    if(databaseType.equalsIgnoreCase(L"SQLSERVER"))
    {
        m_connection = new swDbADO(); // SQL SERVER
        m_connection->SetDatabaseType(L"SQLSERVER"); // set the database dialect
    }
    else if(databaseType.equalsIgnoreCase(L"DB2"))
    {
        m_connection = new swDbADO(); // DB2
        m_connection->SetDatabaseType(L"DB2"); // set the database dialect
    }
    else
        m_connection = new swDb(); // Native engine
    
    // Connect to the database:
    BOOL MustCreateDbFirst = FALSE;
    if(MustCreateDbFirst)
    {
        // If the database does not already exist, create it
        m_connection->CreateDatabase(connectionStrOrDbPath,L"");
    }
    if(m_connection->OpenDatabase(connectionStrOrDbPath))
        TestDatabase();
}

MyClassHeader::~MyClassHeader()
{
    // Cleanup
    delete m_connection;
}

Add table to the database

// Add a new table and append some columns to it
m_connection->AppendTable(L"Customer");
m_connection->AppendColumn(L"Customer",L"FirstName",WCS_CHAR,100,35); // 35 characters long
m_connection->AppendColumn(L"Customer",L"LastName",WCS_CHAR,100,35); // 35 characters long
m_connection->AppendColumn(L"Customer",L"Address",WCS_CHAR,100,85); // 85 characters long
m_connection->AppendColumn(L"Customer",L"EmailAddress",WCS_CHAR,100,100); // 100 characters long
// Add another table and append some columns to it
m_connection->AppendTable(L"Employee");
m_connection->AppendColumn(L"Employee",L"FirstName",WCS_CHAR,100,35); // 35 characters long
m_connection->AppendColumn(L"Employee",L"LastName",WCS_CHAR,100,35); // 35 characters long
m_connection->AppendColumn(L"Employee",L"DateOfBirth",WCS_CHAR,100,10); // 10 characters long
m_connection->AppendColumn(L"Employee",L"EmailAddress",WCS_CHAR,100,100); // 100 characters long

Add couple records to the customer table

// Open a recordset against the customer table, add a couple of record, and save them:
long rsCustomer;
if(m_connection->CreateRecordset(rsCustomer))
{
    if(m_connection->RecordsetOpen(rsCustomer,L"Customer"))
    {
        // Add a new Record
        if(m_connection->RecordsetAddRow(rsCustomer))
        {
            // Set the new record fields:
            m_connection->RecordsetSetFieldValue(rsCustomer,L"FirstName",L"JOHN");
            m_connection->RecordsetSetFieldValue(rsCustomer,L"LastName",L"DOE");
            m_connection->RecordsetSetFieldValue(rsCustomer,L"Address",L"1000 Main Street");
            m_connection->RecordsetSetFieldValue(rsCustomer,L"EmailAddress",L"JohnDoe@gmail.com");
        }
        // Add another Record
        if(m_connection->RecordsetAddRow(rsCustomer))
        {
            // Set the new record fields:
            m_connection->RecordsetSetFieldValue(rsCustomer,L"FirstName",L"JANE");
            m_connection->RecordsetSetFieldValue(rsCustomer,L"LastName",L"DOE");
            m_connection->RecordsetSetFieldValue(rsCustomer,L"Address",L"1000 Main Street");
            m_connection->RecordsetSetFieldValue(rsCustomer,L"EmailAddress",L"JaneDoe@gmail.com");
        }
        // Finally, save the records to the database
        m_connection->RecordsetUpdate(rsCustomer);
    }
    // Close the recordset
    m_connection->RecordsetClose(rsCustomer);
}

Navigate the records in the customer table

long rsCustomer;
if(m_connection->CreateRecordset(rsCustomer))
{
    if(m_connection->RecordsetOpen(rsCustomer,L"Customer"))
    {
        if(m_connection->RecordsetMoveFirst(rsCustomer))
        {
            swString firstName,lastName,address,emailAddress;
            do{
                // Get the current record fields:
                m_connection->RecordsetGetFieldValue(rsCustomer,L"FirstName",firstName);
                m_connection->RecordsetGetFieldValue(rsCustomer,L"LastName",lastName);
                m_connection->RecordsetGetFieldValue(rsCustomer,L"Address",address);
                m_connection->RecordsetGetFieldValue(rsCustomer,L"EmailAddress",emailAddress);
            }while(m_connection->RecordsetMoveNext(rsCustomer));
        }
    }
    // Close the recordset
    m_connection->RecordsetClose(rsCustomer);
}

Find records in the customer table

long rsCustomer;
if(m_connection->CreateRecordset(rsCustomer))
{
    if(m_connection->RecordsetOpen(rsCustomer,L"Customer"))
    {
        // FIND a specific record(s) by last name
        if(m_connection->RecordsetFindRow(rsCustomer,L"LastName",L"DOE"))
        {
            swString firstName,lastName,address,emailAddress;
            do{
                // Get the current record fields:
                m_connection->RecordsetGetFieldValue(rsCustomer,L"FirstName",firstName);
                m_connection->RecordsetGetFieldValue(rsCustomer,L"LastName",lastName);
                m_connection->RecordsetGetFieldValue(rsCustomer,L"Address",address);
                m_connection->RecordsetGetFieldValue(rsCustomer,L"EmailAddress",emailAddress);
            }while(m_connection->RecordsetMoveNext(rsCustomer));
        }
        // Find a specific record(s) by first name, and DELETE the record
        if(m_connection->RecordsetFindRow(rsCustomer,L"FirstName",L"JOHN"))
        {
            if(m_connection->RecordsetDeleteCurrentRow(rsCustomer))
            {
                // Save the changes
                m_connection->RecordsetUpdate(rsCustomer);
            }
        }
    }
    // Close the recordset
    m_connection->RecordsetClose(rsCustomer);
}

Open a recordset with a FILTER against the employee table

long rsEmployee;
if(m_connection->CreateRecordset(rsEmployee))
{
    // Retrieve only those employees whose BIRTHDATE is on of after 10/17/1980
    m_connection->RecordsetAddFilterColumn(rsEmployee,L"DateOfBirth",L">=",L"19801017"
    if(m_connection->RecordsetOpen(rsEmployee,L"Employee"))
    {
        if(m_connection->RecordsetMoveFirst(rsEmployee))
        {
            swString firstName,lastName,dob,emailAddress;
            do{
                // Get the current record fields:
                m_connection->RecordsetGetFieldValue(rsEmployee,L"FirstName",firstName);
                m_connection->RecordsetGetFieldValue(rsEmployee,L"LastName",lastName);
                m_connection->RecordsetGetFieldValue(rsEmployee,L"DateOfBirth",dob);
                m_connection->RecordsetGetFieldValue(rsEmployee,L"EmailAddress",emailAddress);
            }while(m_connection->RecordsetMoveNext(rsEmployee));
        }
    }
    // Close the recordset
    m_connection->RecordsetClose(rsEmployee);
}

Retrieve the SCHEMA of a table in the database

// Retrieve the schema of the customer table, and print the names of the columns:
TABLERECORD tableSchema;
if(m_connection->getTableSchema(L"Customer",&tableSchema))
{
    for(long col=0;col<tableSchema.columnCount;col++)
    {
        wprintf(L"%s\n",tableSchema.columnName[col].c_str());
    }
}

Points of Interest

I found the database classes in the SolidWidgets library to be powerful, flexible, and very easy to use, hope you have the same pleasant experience using them as I did. Good Luck!

Conclusion

I hope this tutorial helps someone who needs to implement such functionality. Best of luck!