Click here to Skip to main content
15,867,686 members
Articles / Mobile Apps / Windows Mobile

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

Rate me:
Please Sign up or sign in to vote.
4.53/5 (17 votes)
2 Mar 2003CPOL3 min read 369.1K   840   43   139
Add, edit, and delete records/table/database in MS SQL Server CE v1.0/v2.0.

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer
Australia Australia
Passion to be a software architect and solution researcher in enterprise solutions by simplify and unify the existing complex manual paper works into an automated environment friendly, comprehensive and dynamic workflow process system.

Comments and Discussions

 
GeneralMy vote of 4 Pin
sanqima8-Aug-17 19:44
sanqima8-Aug-17 19:44 
QuestionThanks / Some modifications Pin
Andy Bantly3-Jul-14 10:48
Andy Bantly3-Jul-14 10:48 
Generalproblem insert value into sqlserver mobile edition Pin
Member 438636515-Sep-08 23:59
Member 438636515-Sep-08 23:59 
GeneralDisconnect problem.. Pin
enderkaradag7-Sep-08 2:23
enderkaradag7-Sep-08 2:23 
QuestionCheck if table exists Pin
sanjaybal4-Sep-08 2:49
sanjaybal4-Sep-08 2:49 
Questionselect * from table_name where ColName='abc' Query fails. Pin
sanjaybal4-Sep-08 2:48
sanjaybal4-Sep-08 2:48 
Generalerror in demo mysqlce.cpp Pin
subodh kumar8329-May-08 0:36
subodh kumar8329-May-08 0:36 
Generalnot getting location of created database file Pin
subodh kumar8329-May-08 0:30
subodh kumar8329-May-08 0:30 
QuestionHow to insert data into table......? Pin
stevenaustin368826-May-08 11:15
stevenaustin368826-May-08 11:15 
Questionprocess rowset Pin
sivadomacha9-Aug-07 15:34
sivadomacha9-Aug-07 15:34 
Generalproblem with creating the Database Pin
Lillian Bose8-Mar-07 19:33
Lillian Bose8-Mar-07 19:33 
GeneralConnecting SQL CE DB Pin
Lillian Bose6-Mar-07 22:24
Lillian Bose6-Mar-07 22:24 
GeneralRe: Connecting SQL CE DB Pin
subodh kumar8329-May-08 0:55
subodh kumar8329-May-08 0:55 
GeneralThanks, man! Pin
mwfolz20-Feb-07 16:17
mwfolz20-Feb-07 16:17 
Generalproblem with label1.Text = (string)objectRS.Fields[1].Value; Pin
admin_vlad2-Feb-07 22:49
admin_vlad2-Feb-07 22:49 
GeneralVS 2005 Pin
AlexEvans29-Jan-07 14:10
AlexEvans29-Jan-07 14:10 
QuestionBinary Image update Pin
mmm2221113-Nov-06 14:17
mmm2221113-Nov-06 14:17 
QuestionHow creating a AuthPwd for a new Database? Pin
Rio118-Oct-06 4:34
Rio118-Oct-06 4:34 
AnswerRe: How creating a AuthPwd for a new Database? Pin
CT CHANG18-Oct-06 15:40
CT CHANG18-Oct-06 15:40 
GeneralRe: How creating a AuthPwd for a new Database? Pin
Rio119-Oct-06 4:25
Rio119-Oct-06 4:25 
GeneralAccessing MS SQL Server CE v1.0/v2.0 without the ADO object. Pin
Anonymous14-Oct-05 3:10
Anonymous14-Oct-05 3:10 
GeneralConnecting to my database Pin
davidvargas21-Sep-05 23:36
davidvargas21-Sep-05 23:36 
GeneralRe: Connecting to my database Pin
sunny_2218-Apr-07 18:54
sunny_2218-Apr-07 18:54 
Generalsql ce 2.0,ppc2003, init connection Pin
crozet20-Jul-05 10:01
crozet20-Jul-05 10:01 
GeneralRe: sql ce 2.0,ppc2003, init connection Pin
CT CHANG20-Jul-05 14:34
CT CHANG20-Jul-05 14:34 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.