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

By , 2 Mar 2003
Rate this:
Please Sign up or sign in to vote.

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

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)

About the Author

hackzai
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

 
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 
Questionprocess rowset Pinmembersivaturing9-Aug-07 15:34 
Generalproblem with creating the Database PinmemberLillian Bose8-Mar-07 19:33 
The code is compiled without errors but at the point of Database creation its throwing error "User Breakpoint called from code at 0x7c901230".
 
The error is thrown at the exection of the code :
hr = pIDBDataSourceAdmin->CreateDataSource(1,dbpropset,NULL,IID_IDBProperties, NULL);
 
Kindly provide with the solution to overcome the problem.
 
Thanks in advance.
 
Regards,
Bose.
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 
GeneralRe: Can't install MS SQL Server CE, Help me! Pinmemberthanhthuyvn_vtajkhskjakjd10-Oct-05 23:38 

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.140415.2 | Last Updated 3 Mar 2003
Article Copyright 2002 by hackzai
Everything else Copyright © CodeProject, 1999-2014
Terms of Use
Layout: fixed | fluid