Synchronize sqlce and SQL Server databases using RDA

Abstract: This article uses a simple example to describe in. in the. NET environment, how to use the remote data access (RDA) method to synchronize databases between sqlce and SQL Server, and how to use the related classes of sqlce to create and access sqlce databases.

Key words: RDA, sqlce, SQL Server

1 Introduction

SQL Server Ce (sqlce) is a lightweight local database engine running on Windows CE devices. It provides solutions for occasional data access on mobile devices. For example, before conducting inspection tasks, the Business Administration personnel can use Win CE mobile devices to copy the database (SQL Server) in the desktop computer to Win CE's database (sqlce; when performing a task, you can modify the record on the mobile device. After the task is completed, you can upload the database on the mobile device to the database on the desktop computer, synchronize the data of the two databases. Remote Data Access (RDA) provides a fast and simple solution for synchronization between sqlce and SQL Server databases on Win CE mobile devices. This article uses an example to demonstrate how to use RDA to synchronize databases.

2 Development Environment

The instance program development environment in this article is as follows: Microsoft Visual Studio 2005, the programming language C #, the database uses

SQL Server2000, SQL Server mobile edition 2005 (sqlce3.0), the program testing environment is Windows ce5.0 (use the Pocket PC 2003 se simulation program on ).

The SP3 patch must be installed for SQL Server 2005. The installation file for SQL Server mobile edition is in the Microsoft Visual Studio 8 \ smartdevices \ SDK \ SQL Server \ mobile \ V3.0 directory and the file name is sqlce30setupcn. MSI. After installing SQL Server mobile edition 2005, use the "Configure web Synchronization Wizard" in the program menu to create a virtual directory named "sqls3. the access method is" anonymous ". Note: after creating a virtual directory, you must enable IIS to add the write permission to the access permission of the virtual directory "SQL". Otherwise, RDA synchronization fails.

3. Program Implementation

Run SQL Server2000 Server Manager. Open the Enterprise Manager and create a new database named "ABC". Create a data table named "person" under "ABC". The table fields are shown in:

 

Figure 1 person table

Run Microsoft Visual Studio 2005 to create a new project named "Visual C #-> smart devices-> Windows ce5.0-> Device Applications" and named "rda_test ". Next, write the program.

Step 1: introduce two namespaces: system. Io and system. Data. sqlserverce. Create an empty local sqlce database on the Windows CE device. The Code is as follows:

// Create an empty local database file,

String dbpathname = @ "\ RDA. SDF ";

If (file. exists (dbpathname ))

File. Delete (dbpathname );

String strcon = "Data Source =" + dbpathname + "; Password = ";

Sqlceengine engine = new sqlceengine (strcon );

Engine. createdatabase ();

Engine. Dispose ();

 

The sqlceengine class is used in the code to create a sqlce database. Pay attention to the parameter used when creating the sqlceengine object. this parameter is a database connection string, and pay attention to its format.

Step 2: Synchronize the sqlce database with the database in SQL Server2000. The Code is as follows:

Sqlceremotedataaccess RDA = NULL;

// Ip address of the host where SQL server is located

String remoteip = tbremoteip. Text. Trim ();

// The database in SQL Server, named ABC

String remotedb = "ABC ";

// User and password of database ABC

String user = "sa ";

String Pwd = "sa ";

// Path and name of the local sqlce database, which exists in the Win CE system.

// Dbpathname = @ "\ RDA. SDF ";

String localdb = dbpathname;

// Password of the local sqlce Database

String localpwd = "";

// Name of the data table to be synchronized

String table = "person ";

// String used to connect to the SQL Server database

String rdaoledbconnectstring = @ "provider = sqloledb; Data Source =" + remoteip +

"; Initial catalog =" + remotedb + "; user id =" + User + "; Password =" + PWD;

// The string connecting to the local sqlce database as a parameter of the sqlceremotedataaccess object

String localconnectstring = @ "Data Source =" + localdb + "; Password =" + localpwd;

RDA = new sqlceremotedataaccess ();

// Because the access method set in the virtual directory sql2010is anonymous, the login name and password can be ignored.

RDA. internetlogin = "";

RDA. internetpassword = "";

RDA. interneturl = "http: //" + remoteip + "/sqltms/ sqlcesa30.dll ";

RDA. localconnectionstring = localconnectstring;

// Execute synchronization and synchronize SQL Server to sqlce

// The rdatrackoption. trackingon parameter indicates that SQL Server mobile tracks all changes to the extracted table.

RDA. Pull (table, "select * from" + Table, rdaoledbconnectstring, rdatrackoption. trackingon );

 

 

RDA uses the sqlceremotedataaccess class for database synchronization. Update the local sqlce database from another database and use the pull method of the sqlceremotedataaccess class. The rdatrackoption parameter in this method is used. trackingon allows sqlce to track changes to local data tables, so that SQL ce can be used to synchronize SQL Server databases.

Step 3: Modify the sqlce database. This step is only used to demonstrate how to use sqlce to synchronize the SQL Server database. The Code is as follows:

Const string strdatabase = @ "Data Source = \ RDA. SDF ";

Sqlceconnection con = NULL;

Con = new sqlceconnection (strdatabase );

Con. open ();

// Modify existing records

String strupdate = "Update person set age = 18 where name = 'wang yi '";

Sqlcecommand cmd = new sqlcecommand (strupdate, con );

Cmd. executenonquery ();

Cmd. Dispose ();

// Insert a new record

Strupdate = "insert into person (card_id, name, age) values (10003, 'lily', 23 )";

Cmd = new sqlcecommand (strupdate, con );

Cmd. executenonquery ();

Cmd. Dispose ();

Step 4: Use sqlce to synchronize the SQL Server2000 Database, that is, update the modified records of the sqlce database to the SQL Server2000 Database. The code is basically the same as the Code listed in the first step, but only the code for the last two lines of the Code in the first step on the pull method is changed to the push method code. The Code is as follows:

// Execute synchronization and synchronize sqlce to SQL Server

// The parameter rdabatchoption. batchingon indicates that SQL Server mobile will push the/all rows in SQL Server into a batch and merge them into a transaction.

RDA. Push ("person", rdaoledbconnectstring, rdabatchoption. batchingon );

4 program running results

(1) Figure 2 shows the person table of the original data table on SQL Server2000 before the program is running.

 

Figure 2 raw data of the person table in the database in SQL Server

(2) Figure 3 shows the program running interface of the Pocket PC 2003 se simulation program. Click the three buttons from top to bottom. The program running result is shown in 3, the person table record 4 of database ABC in SQL Server2000 after synchronization is shown in:

 

Figure 3 display the program on the running simulator page

 

Figure 4 data in the person table of the database in SQL Server after slqce Synchronization

5. Conclusion

From the above we can see that it is very easy to use RDA for SQL ce and SQL Server for Database Synchronization, and Microsoft Visual Studio 2005 is a powerful development environment, it is very easy to develop Windows CE mobile device applications on a PC. In combination with sqlce3.0, it greatly reduces the workload of developing database applications.

References:

[1] Lu Guizhou, Xia mingfei, and Li Ming. Development of database applications based on Windows CE & Pocket PC [J] information on micro-computer, 2003, (02 ).

[2] Chen Fu, Zhou Shujie, Lin Xiaozhu, Shi Guangjun. Study on Database Access Technology for Embedded Systems Based on WinCE [J] computer system application, 2004, (04 ).

[3] Zhang Hua; Huang Liping. Development and application of SQL Server Ce on handheld devices [J]. Computer System Application, 2003 (12 ).