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.
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.
HRESULT CreateSqlSvrCeProvider (void);
HRESULT CreateDBSession (void);
HRESULT GetErrorMessage (LPTSTR lpszBuffer, int MaxBuffer);
HRESULT ConnectDB (LPTSTR lpszDBName);
HRESULT DisconnectDB (LPTSTR lpszDBName);
HRESULT CreateDB (LPTSTR lpszDBName);
HRESULT DeleteDB (LPTSTR lpszDBName);
HRESULT CompactDB (LPTSTR lpszDBName);
HRESULT CreateTable (LPTABLECOLUMNINFO lptci);
HRESULT DropTable (LPTSTR lpszTableName);
HRESULT CreateColumn (LPTABLECOLUMNINFO lptci);
HRESULT DropColumn (LPTABLECOLUMNINFO lptci);
HRESULT CreateIndex (LPTABLEINDEXINFO lptii);
HRESULT DropIndex (LPTABLEINDEXINFO lptii);
HRESULT ExecuteSQL (LPTSTR lpszQuery);
HRESULT GetRowset (LPTSTR lpszQuery);
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
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:
hr = CreateDB(szDBName);
MessageBox(hWnd, TEXT("Database successful created."),
if (E_OUTOFMEMORY == hr)
TEXT("(0x%x) Out of memory!"),
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.
A total of 6 new functions is implemented based on the previous source file, and these 6 functions are:
These 6 functions, will give you an easy way to manage your database with writing the complex SQL-92 command.