Encapsulation of access to sqlce by native C ++ in Windows Mobile

ArticleDirectory

• Turn on and off
• Create and delete
• Add, delete, modify, and delete operations
• Transaction Processing
• Query operations

Background

To develop Windows Mobile applications, you often need to access, store, and read databases. However, Microsoft does not provide native C ++ with encapsulation like ADO. net, so I encapsulated a sqlce handler class myself.

Brief Introduction

This article describes the implementation of native C ++'s encapsulation class for accessing sqlce in Windows Mobile and wince. Because Microsoft does not provide C ++ with images like ADO. net encapsulation makes it difficult for native C ++ to access sqlce. Therefore, it encapsulates oledb access to sqlce to facilitate SQL statement operations on sqlce.

Sqlce access technology overview ADO. net

. NET Compact framework has gradually become the mainstream for Windows Mobile and wince development. NET provides ADO.. Net Access to sqlce, and the access to sqlce is encapsulated in system. data. in sqlserverce, access to other databases (SQL Server and Oracle) is basically compatible, but dbproviderfactory is not supported. For exampleCodeIt cannot be compiled because there is no sqlceproviderfactory class.

 
Dbproviderfactory factory = sqlceproviderfactory. instance;
 

I have previously encapsulated a sqlce classification class for ADO. net. For details, refer to the use of SQL ce in. NET Compact framework and the unit testing class for sqlcehepler, see unit testing under. NET Compact framework.

Oledb

In the native world, you can access sqlce through oledb. Oledb is encapsulated inSqlceoledb35.dllTo use oledb, you need to install a cab package as follows:

 
C: \ Program Files \ Microsoft SQL Server compact edition \ v3.5 \ devices \ wce500 \ armv4i \ sqlce. REPL. PPC. wce5.armv4i. Cab
 

The preceding path varies with the platform.

Note thatSqlceoledb35.dllOnly sqlce 3.5 is supported. Microsoft does not have good compatibility with different versions of sqlce. For more information, see SQL Server express and SQL Server compact.. Net campact framework.

This article will focus on oledb in the future.

ADO

Microsoft does not officially support ado on Windows Mobile and wince platforms. But there isCommunityFor more information, see a set of adoce classes. I have not tried it, so it is difficult to comment.

 

Implementation

The following describes how to access sqlce Based on oledb.

Documentation and Samples

Microsoft provides online documentation that can be downloaded from Microsoft SQL Server compact 3.5 books online and samples. The above link only contains online documents and does not provide examplesProgramSo this name is a bit deceptive. Although online documents are provided, the quality of the documents is not high and some details are not discussed, such as callingIcommand: ExecuteThe entry parameters are not mentioned, so I can only try it myself.

Although the above link does not provide samples, we have another method to get samples. In

 
C: \ Program Files \ Microsoft SQL Server 2005 mobile edition \ samples \ northwindoledb
 

You can obtain the sample code on the premise that Microsoft SQL Server 2005 mobile edition is installed. However, note that different sqlce versions have different implementations. This code must be modified in conjunction with the online book above.

SQL statement-based implementation

Microsoft's example program, northwindoledb, provides a method to access sqlce through oledb, but does not provide encapsulation of SQL statements. It is highly efficient to directly access tables, but it is inconvenient. Because each operation encapsulates a function, each function requires at least 100 lines of code, and there are pointer and other resource allocation and release operations. Once not processed properly, memory leakage occurs. Therefore, I prefer to use a unified SQL statement interface to operate sqlce. Although it sacrifices some performance, it provides convenience. The following is the encapsulation class of the current version. This class is based onJo ã o Paulo FigueiraOledbclientlib3.

Turn on and off

BoolOpen (ConstCstring & dbpath );
VoidClose ();
 

The above operation is to open and close the sqlce database link, which is implemented as a long link here, that is, if close () is not called, the session of the database will be linked all the time, the advantage is to shorten the access time.

Create and delete

 
BoolCreatedatabase (ConstCstring & dbpath );
BoolDeletedatabase (ConstCstring & dbpath );
 

Since sqlce is an in-process database, it is physically represented as a file that can be easily created and deleted. The above interface is to create and delete the sqlce database. After the database is created, the senssion is automatically enabled.

Add, delete, modify, and delete operations

 
IntExecutenonquery (ConstCstring & SQL );
 

Call executenonquery () to execute the add, delete, and modify statements.

Sqlcehelper dB;
Hresult hR = dB. Open (db_file_name );
 If (HR <0)
{
Cstring CSTR = dB. geterrorsmessage ();
 Char * STR = New char [CSTR. getlength () + 1];
Sprintf (STR, "% S" , CSTR );
Fail (STR );
 Delete STR;
}

Cstring sqlstr;
 For ( Int I = 0; I <Max; ++ I)
{
Systemtime currenttime;
Getlocaltime (& currenttime );
Sqlstr. Format (L "Insert into T1 (F1, F2, F3) values (% d, 'str % d', '% d-% d: % D ')" ,
I, I, currenttime. wyear, currenttime. wmonth, currenttime. wday, currenttime. whour, currenttime. wminute, currenttime. wsecond );
HR = dB. executenonquery (sqlstr );
 If (HR <0)
{
Cstring CSTR = dB. geterrorsmessage ();
 Char * STR = New char [CSTR. getlength () + 1];
Sprintf (STR, "% S" , CSTR );
Fail (STR );
 Delete STR;
}
}

DB. Close ();

The above is an example of an insert statement.

 

Transaction Processing

 
VoidBegintransaction ();
VoidCommittransaction ();
VoidRollbacktransaction ();
 

However, the current version does not support transactions.

Query operations

In fact, the implementation of query operations among so many operations is the most complicated.

 
IntExecutereader (ConstCstring & SQL );
 

Executing executereader () Will initialize a result set based on the SELECT statement, which is different from the implementation of ado.net. This result set is not returned directly through dataset or datareader, it is maintained in the internal irowset structure. For ease of use, sqlcehelper implements a read-only and forward data reading method.

 
BoolIsendofrecordset ();
VoidMovenext ();
VoidClosereader ();

IntGetrowint (Const longOrdinal );
DoubleGetrowdouble (Const longOrdinal );
Cstring getrowstr (Const longOrdinal );
 

After executereader () is called, you can call isendofrecordset () to determine whether the result set is empty. Movenext () is used to move to the next record. Getrowint (), getrowdouble (), and getrowstr () are the data for reading the current record. Closereader () closes the result set.

Sqlcehelper dB;
Hresult hR = dB. Open (db_file_name );
If (HR <0)
{
Cstring CSTR = dB. geterrorsmessage ();
 Char * STR = New char [CSTR. getlength () + 1];
Sprintf (STR, "% S" , CSTR );
Fail (STR );
 Delete STR;
}

Cstring sqlstr;
Sqlstr. Format (L "Select * from T1" );
HR = dB. executereader (sqlstr );
If (HR <0)
{
Cstring CSTR = dB. geterrorsmessage ();
 Char * STR = New char [CSTR. getlength () + 1];
Sprintf (STR, "% S" , CSTR );
Fail (STR );
 Delete STR;
}

 While (! DB. isendofrecordset ())
{
Wprintf (L "F1 = [% d], f2 = [% s], F3 = [% s] \ n" , DB. getrowint (1), DB. getrowstr (2), DB. getrowstr (3 ));
DB. movenext ();
}
DB. closereader ();
DB. Close ();

The above demonstrates the entire read process.

Unit Test

TDD is used in project development. For details about unit test, referUnit Testing of native C ++ in Windows Mobile and Windows MobileAnd use cppunitlite on Windows Mobile to output the test results.

To-Do-list

** Support to open the database with Password

** Support to specify the Open Mode

** Support to load ole db without com registry.

** Support transactions

** Support parameters

About Projects

I moved the project host to codeplex. The project homepage link is as follows:

Sqlcehelper-native C ++ SQL helper class for SQL Server compact

The link for checking and downloading the latest version is as follows:

http://sqlcehelper.codeplex.com/SourceControl/ListDownloadableCommits.aspx