Accessing MS SQL Server CE v1.0/v2.0 without ADO

• Download source files - 8.88 KB
• Download demo project - 42.3 KB

Introduction

I'm sure for those who are going to implement or already implemented MS SQL Server CE v1.0/2.0 have used the free tool (isqlw_wce.exe) from Microsoft and always have a big question mark in the mind about how to write such an application right?

So, this might be the right article for those who intend to find out more about this. At the end of this article, you will manage to access the MS SQL Server CE database which you have installed in your PocketPC device with your own valuable information and made your application more powerful and towards the enterprise level. :)

Why do we need MS SQL Server CE as compared to CD database? Because MS SQL Server CE offers a better performance and supports the SQL DDM/DDL that we are all familiar all the time. Which means, it will speed up our application/product development cycle.

Background

A minimum knowledge of SQL DDM and DDL is a must in order to utilize the various ready made functions. Because you require to write your own SQL statement to create your table, add/update/delete records from any table.

Using the code

Before you start looking at the code, you need to remember that MS SQL Server CE only supports single connection to any database at anytime.

When you look into the SqlSvrCE.cpp/.h you will get all the ready made functions that you eventually will call from your own WIN32API or MFC application and that is up to your choice.

Before you start downloading the sample code, let's have a look on what function you can have in this free source.

// Create a new SQL Server CE database provider.
HRESULT CreateSqlSvrCeProvider  (void);

// Create a new SQL Server CE database session right
// after successful connect to the pass in database
// name (*.sdf) in fullpath.
HRESULT CreateDBSession      (void);

// Retrieve the last known OLE DB error message, due to
// error occur through out the database access process.
HRESULT GetErrorMessage (LPTSTR lpszBuffer, int MaxBuffer);

// Connect the SQL Server CE database with reference to
// the pass in database name (*.sdf) in fullpath.
HRESULT  ConnectDB      (LPTSTR lpszDBName);

// Disconnect from the current open SQL Server CE database
// with reference to the pass in database name (*.sdf) in
// fullpath under the CreateDB/ConnectDB function.
HRESULT DisconnectDB      (LPTSTR lpszDBName);

// Create the SQL Server CE database with reference to
// the pass in database name (*.sdf) in fullpath.
HRESULT  CreateDB      (LPTSTR lpszDBName);

// Delete the SQL Server CE database with reference to
// the pass in database name (*.sdf) in fullpath.
HRESULT DeleteDB      (LPTSTR lpszDBName);

// Compact the SQL Server CE database with reference to
// the pass in database name (*.sdf) in fullpath.
HRESULT CompactDB      (LPTSTR lpszDBName);

// Create new table with reference to
// the pass in table name and first column information
// DUE TO SQL SERVER CE 2.0 MUST HAVE AT LEAST 1
// COLUMN IN THE CREATED TABLE.
HRESULT CreateTable      (LPTABLECOLUMNINFO lptci);

// Delete the existing table with reference to
// the pass in table name..
HRESULT DropTable      (LPTSTR lpszTableName);

// Create new column with reference to
// the pass in information in TABLECOLUMNINFO structure.
HRESULT CreateColumn (LPTABLECOLUMNINFO lptci);

// Drop the existing column with reference to
// the pass in information in TABLECOLUMNINFO structure.
HRESULT DropColumn (LPTABLECOLUMNINFO lptci);    

// Create new index with reference to
// the pass in information in TABLEINDEXINFO structure.
HRESULT CreateIndex (LPTABLEINDEXINFO lptii);

// Drop the existing index with reference to
// the pass in information in TABLEINDEXINFO structure.
HRESULT DropIndex  (LPTABLEINDEXINFO lptii);

// Execute the SQL Statement with does not require to
// return a RowSet object.
// EXAMPLE:
// SELECT, DELETE, CREATE TABLE, DROP TABLE, INSERT INTO & etc...
HRESULT ExecuteSQL      (LPTSTR lpszQuery);

// Load the records into a RowSet object with reference
// to the pass in SQL statement.
HRESULT  GetRowset      (LPTSTR lpszQuery);

// Process the Rowset object created by the GetRowset function.
HRESULT ProcessRowset      (IRowset *pRowset);

There is a total of eleven functions as shown above that you require to remember and call through out your database application.

But be aware that the ProcessRowset is fully customized to fit the attached sample application requirement, which will display all the retrieved records and column headers into the provided listview control. Therefore, you need to modify the ProcessRowset function or even write your own ProcessRowset function to interpret those data you have read from the MS SQL Server CE database.

All the necessary note is available in the source file and with this note, I'm sure it will make your life easy when you start to modify or write your own ProcessRowset function.

Therefore, to use the above function is pretty straight forward. All you need is just call the relevant function with the correct corresponding argument.

For instance, you wish to create a database in \Windows folder with the name as MyDb.sdf. All you need is just call the CreateDB as below:

_tcscpy(szDBName, TEXT("\\windows\\MyDb.sdf"));
hr = CreateDB(szDBName);
// Validation
if (!FAILED(hr))
    // Display result message text.
    MessageBox(hWnd, TEXT("Database successful created."), 
                                       TEXT("Infor"), MB_OK);
else
{
    // Get OLE DB error message
    GetErrorMessage(szErrMessage, wcslen(szErrMessage));
    //
    if (E_OUTOFMEMORY == hr)
    {
      // Compose result message
      wsprintf(szErrMessage,
         TEXT("(0x%x) Out of memory!"),
         hr);
    }

    // Display result message text.
    MessageBox(hWnd, szErrMessage, TEXT("Error"), 
                               MB_ICONSTOP | MB_OK);
}

You may notice that the sample program user interface will not fit in the MS PocktePC (240x320) screen. This is because the sample application is developed base on Windows CE .NET platform and all I did is just create a new PocketPC application and direct copy all the relevant cpp/h files. Hope you don't mind about that.

Last but not least, I wish you enjoy the sample code and hope it will help you in any one of your application development cycles.

Update

A total of 6 new functions is implemented based on the previous source file, and these 6 functions are:

• CreateTable
• DropTable
• CreateColumn
• DropColumn
• CreateIndex
• DropIndex

These 6 functions, will give you an easy way to manage your database with writing the complex SQL-92 command.