Click here to Skip to main content
Click here to Skip to main content

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

, 2 Mar 2003
Rate this:
Please Sign up or sign in to vote.
Add, edit, and delete records/table/database in MS SQL Server CE v1.0/v2.0.


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. Smile | :)

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.

// 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

// 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.

// Drop the existing column with reference to
// the pass in information in TABLECOLUMNINFO structure.

// Create new index with reference to
// the pass in information in TABLEINDEXINFO structure.

// Drop the existing index with reference to
// the pass in information in TABLEINDEXINFO structure.

// Execute the SQL Statement with does not require to
// return a RowSet object.
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);
    // Get OLE DB error message
    GetErrorMessage(szErrMessage, wcslen(szErrMessage));
    if (E_OUTOFMEMORY == hr)
      // Compose result message
         TEXT("(0x%x) Out of memory!"),

    // 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.


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.


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

About the Author

Web Developer
Malaysia Malaysia
Hackzai was graduated from Double-E, and being certified as MCSD (VS6) at spring 2003. But move forward with ASP.NET, VB.NET, C#, Silverlight, LINQ, XML

Comments and Discussions

QuestionThanks / Some modifications PinpremiumAndy Bantly3-Jul-14 10:48 
Answer100% workin code for Sql Server CE [modified] Pinmembercodemobile31-Oct-08 19:39 
Generalproblem insert value into sqlserver mobile edition PinmemberMember 438636515-Sep-08 23:59 
GeneralDisconnect problem.. Pinmemberenderkaradag7-Sep-08 2:23 
QuestionCheck if table exists Pinmembersanjaybal4-Sep-08 2:49 
Questionselect * from table_name where ColName='abc' Query fails. Pinmembersanjaybal4-Sep-08 2:48 
Generalerror in demo mysqlce.cpp Pinmembersubodh kumar8329-May-08 0:36 
Generalnot getting location of created database file Pinmembersubodh kumar8329-May-08 0:30 
QuestionHow to insert data into table......? Pinmemberstevenaustin368826-May-08 11:15 
Dear all
I am new to sql ce programming..beg ur pardon for asking stupid question....
after doing some searching..I finally can run the two demo project well...
but these two project can only create and drop table,column.....for me
and I had spent a lot of time trying to insert data into table....
I am wondering if there is anyone can show me a sample to teach me how to use
what I am trying is
create table data,and create column ID,then the InsertExecutionLog function is like the following...
HRESULT InsertExecutionLog(CExecutionLog *execLog)
CString strSQL;
strSQL.Format(L"Insert Into data(ID) Values('%d')",execLog->SiteNo);
hr = ExecuteSQL((LPTSTR)(LPCTSTR)strSQL);
return hr;
can anyone tell me what's wrong with code.....
please ..and thanks in advance,especially for the author's effort.....
Questionprocess rowset Pinmembersivaturing9-Aug-07 15:34 
Generalproblem with creating the Database PinmemberLillian Bose8-Mar-07 19:33 
GeneralConnecting SQL CE DB PinmemberLillian Bose6-Mar-07 22:24 
GeneralRe: Connecting SQL CE DB Pinmembersubodh kumar8329-May-08 0:55 
GeneralThanks, man! Pinmembermwfolz20-Feb-07 16:17 
Generalproblem with label1.Text = (string)objectRS.Fields[1].Value; Pinmemberadmin_vlad2-Feb-07 22:49 
GeneralVS 2005 PinmemberAlexEvans29-Jan-07 14:10 
QuestionBinary Image update Pinmembermmm2221113-Nov-06 14:17 
QuestionHow creating a AuthPwd for a new Database? PinmemberRio118-Oct-06 4:34 
AnswerRe: How creating a AuthPwd for a new Database? Pinmemberhackzai18-Oct-06 15:40 
GeneralRe: How creating a AuthPwd for a new Database? PinmemberRio119-Oct-06 4:25 
GeneralAccessing MS SQL Server CE v1.0/v2.0 without the ADO object. PinsussAnonymous14-Oct-05 3:10 
GeneralConnecting to my database Pinmemberdavidvargas21-Sep-05 23:36 
GeneralRe: Connecting to my database Pinmembersunny_2218-Apr-07 18:54 
Generalsql ce 2.0,ppc2003, init connection Pinmembercrozet20-Jul-05 10:01 
GeneralRe: sql ce 2.0,ppc2003, init connection Pinmemberhackzai20-Jul-05 14:34 
GeneralRe: sql ce 2.0,ppc2003, init connection Pinmembercrozet21-Jul-05 4:12 
Generalhelp needed Pinmemberkrishnamca2000@yahoo.com16-Jul-05 4:17 
GeneralRe: help needed PinsussAnonymous26-Jul-05 3:38 
Generalproblems with disconnection Pinmemberluca98112-Jul-05 22:44 
GeneralRe: problems with disconnection PinmemberErick 'El Matador' Demers12-Aug-05 6:06 
GeneralRe: problems with disconnection Pinmemberluca98129-Aug-05 5:13 
GeneralRe: problems with disconnection PinmemberRio118-Oct-06 4:24 
GeneralException PinsussMarcio Camargo Oliveira27-May-05 14:06 
GeneralRe: Exception PinmembershritiParashars2-Jan-07 23:29 
GeneralArticle code fix Pinmembermaxxel4-Apr-05 4:53 
GeneralProcessRowset GetData Pinmemberjsdljasfdlfj19-Mar-05 22:06 
GeneralRe: ProcessRowset GetData Pinmemberjsdljasfdlfj19-Mar-05 23:20 
GeneralRe: ProcessRowset GetData Pinmemberjsdljasfdlfj20-Mar-05 14:19 
GeneralRe: ProcessRowset GetData Pinmemberjsdljasfdlfj21-Mar-05 21:30 
GeneralUnexpected end of file PinmemberWenZhong He16-Dec-04 21:41 
GeneralRe: Unexpected end of file Pinmemberjsdljasfdlfj24-Mar-05 1:38 
GeneralLINK ERRORS + MS PATCH Pinmemberkillgec6-Dec-04 8:18 
GeneralLINK ERROR! PinsussAnonymous4-Nov-04 3:20 
Generalerror while link obj Pinmemberjesselian27-Oct-04 23:42 
Generalcompiling error - please help PinmemberMario Barfus24-Aug-04 5:39 
QuestionMissing files? PinmemberKybert11-Aug-04 1:30 
AnswerRe: Missing files? Pinmemberhackzai11-Aug-04 4:01 
GeneralCan't install MS SQL Server CE, Help me! Pinmemberlam cuong2-Aug-04 23:39 
GeneralRe: Can't install MS SQL Server CE, Help me! Pinmemberjakejang24-Mar-05 17:13 
GeneralRe: Can't install MS SQL Server CE, Help me! PinsussAnonymous2-Oct-05 0:53 

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

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

| Advertise | Privacy | Mobile
Web03 | 2.8.140721.1 | Last Updated 3 Mar 2003
Article Copyright 2002 by CT CHANG
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid