RDA Remote Data Access

This article describes how to use RDA (remotedataaccess Remote Data Access) to access data between SQL Server ce 2.0 and desktop SQL Server 2000 databases on the Pocket PC (PPC. We will use Visual Basic. NET 2003 for smart devices Program Development.

I. Overview

There are currently two programming methods for PPC program to communicate with the desktop PC: 1. Using socket programming to communicate with the desktop program; 2. Using RDA and Replication) program the database to access the desktop SQL Server database. Socket programming is actually used to communicate with the desktop PC through the TCP/IP protocol. It can easily transmit common types of data, such as strings, integers, and bytes, however, if you want to transmit typed data, You Need To encapsulate it by the programmer. If you want a smart device to return the data of a specified database table from the desktop database engine, you must write a desktop interface service program to query data and return the result to the smart device through socket. So how can I access a local database or even a remote desktop PC database on a Pocket PC like a desktop PC? By running SQL Server Ce on the smart device Pocket PC, we can easily access the SQL Server ce database placed on the Pocket PC, you can also use RDA in SQL Server ce or merge copies to quickly access the Remote Desktop SQL Server database from a smart device.

Ii. Technical Points

The full name of SQL Server CE is Microsoft SQL Server 2000 Windows CE edition. It provides a lightweight Database Access Solution for mobile smart devices and embedded devices. By using development tools such as Microsoft Visual Studio. NET or Microsoft Embedded Visual tools, we can extend SQL Server's enterprise data management capabilities to a Windows CE-based smart platform. SQL Server CE can be applied to three typical environments:

1. the development environment is used to develop a desktop PC based on the SQL Server ce program. The desktop PC must include Microsoft Visual Studio. NET, Microsoft Embedded Visual tools 3.0, and Pocket PC SDK development tools;

2. The client environment is a Pocket PC device used to run SQL Server Ce-based programs. When the device has no available network connection, microsoft ActiveSync can be used to connect to the desktop PC in the server environment;

3. The server environment is a computer running Microsoft Internet Information Service (IIS) and Microsoft SQL Server instances. You can deploy IIS and SQL server on the same computer, you can also configure multiple computing instances. RDA and merge replication all need to communicate with SQL Server through IIS.

SQL Server ce relies on several components to exchange data with SQL Server:

1. The database engine is used to manage data storage on Windows CE-based devices and track the addition, update, and deletion of database records;

2. the SQL Server ce client agent is a component running on a Windows CE device for connection, including copying objects, RDA objects, and database engines, you can use these object applications to control the connection to SQL Server;

3. the SQL Server ce Server Agent processes HTTP requests from the SQL Server ce client agent. When the SQL Server ce client agent sends a request to the SQL Server ce server agent over HTTP, the SQL Server ce Server Agent connects to the SQL Server and sends the queried record set back to the SQL Server ce client agent over HTTP. All data must be transmitted through IIS.

From the above communication process, we know that the remote connection and access of SQL Server CE must use the web transmission protocol HTTP or HTTPS, and the SQL Server ce client agent must run on a Windows CE device, the SQL Server ce Server Agent runs on a desktop PC, and the computer must also have IIS installed to use RDA or merge copies to communicate with SQL Server. SQL Server ce supports Ethernet, wireless LAN, and wireless WAN. By using Microsoft ActiveSync, the Pocket PC device can use a serial port, infrared ray, or USB to directly connect to the SQL server on the desktop PC, or test the connection between SQL Server ce and the desktop SQL Server.

The Remote Data Access (RDA) object is an ActiveX control used by Microsoft SQL Server 2000 Windows CE (SQL Server CE) for programmable Access to a remote Microsoft SQL Server 2000 or Microsoft SQL Server version 7.0 database, you can use RDA to access a remote database as easily as operating a local database on a desktop PC.

Iii. Design Ideas

We will use Visual Basic. Net to create the "task manager for PPC" project to demonstrate how to use RDA to connect and access the database between PPC and PC. A customer manager assistant or sales personnel must know what tasks are to be completed today and what arrangements the superiors have for themselves, even though they can receive and view them through email or IM programs, but if we only use the Pocket PC device, can we receive the task? Of course, the answer is yes.

On the Pocket PC, we create a database client program. To compile the RDA program, we need to use the sqlceremotedataaccess class in the system. Data. sqlserverce namespace in the. NET compression framework. We need to use RDA to query and retrieve record sets from the desktop PC to the Pocket PC. pull method. Pull has multiple overloaded versions. We use the most common version. localtablename is the name of the SQL Server ce local table that will receive extracted SQL server records. Sqlselectstring is any valid Transact-SQL statements, including select statements and stored procedures, they specify which tables, columns, and records are extracted from the remote SQL Server database and stored in the SQL Server ce database. Oledbconnectionstring is the ole db connection string used to connect to the SQL Server database. Trackoption indicates whether SQL Server ce tracks the changes made to the extracted table, and whether the indexes on the extracted table are forwarded to devices with primary key constraints. We use the following versions:

.... RDA. Pull ("itemlist", "select * From itemlist where emp_id = '" + empid + "'", remoteconnstring, rdatrackoption. trackingonwithindexes) ....

Trackingonwithindexes indicates that SQL Server ce tracks all changes to the extracted table. Create the index and primary key constraints on the SQL Server table at the same time on the local table.

The controls and classes used to write PPC database programs are similar to those used to write desktop database programs. sqlceconnection corresponds to sqlconnection, sqlcedataadapter corresponds to sqldataadapter, and sqlcecommand corresponds to sqlcommand, the sqlceconnection object indicates a connection to a data source on a smart device. A valid connection string needs to be passed to connectionstring, for example:

Localconnstring = "Data Source = \ program file \ task \ RDA. SDF"

The SDF file is an SQL Server ce database file. SQL Server ce supports only one connection at a time, but multiple commands can share the same connection. When the sqlceconnection connection is enabled, you can create a sqlcecommand object and set the commandtext attribute of the SQL statement used to execute or return the record set, the SQL statement called by sqlcecommand does not support passing parameter naming parameters. question marks (?) must be used (?) Placeholders can also be customized to form SQL statement strings, for example:

...... Dim conn as new sqlceconnection Conn. connectionstring = localconnstring Dim selectcmd as sqlcecommand = new sqlcecommand Selectcmd. commandtext = "Update itemlist set finished = 1 where id =" + id Conn. open () Selectcmd. executenonquery () ......

Applications can use the RDA. Push method to send the changes in the SQL Server ce trace extraction table back to the original SQL Server table. Localtablename refers to the name of the SQL Server ce local table of records that have been extracted from SQL Server. Oledbconnectionstring is the ole db connection string used to connect to the SQL Server database. Batchoption indicates whether the change that is being sent back to the SQL Server table forms a group that shares the same transaction or is applied separately. In our version, all rows need to be combined into one transaction and pushed to SQL Server.

RDA. Push ("itemlist", remoteconnstring, rdabatchoption. batchingon)

Iv. Environment configuration and program implementation

Many netizens fail to run SQL Server ce RDA and merge replication programs because of configuration problems. Correctly installing SQL ce and configuring IIS and SQL Server 2000 is the key to running SQL ce database programs. When installing SQL Server ce, you must note that the installation program will encounter errors when installing the server tool. This is because SQL Server ce and SQL Server 2000 have compatibility and security issues, the solution is to first ignore and then update the desktop SQL Server 2000 database engine to SP3 or SP4, based on the updated version, you also need to run the corresponding update program SQL Server ce 2.0 SP3 for SQL Server 2000 SP3 or SQL Server ce 2.0 SP4 for SQL Server 2000 SP4, in this way, the server components of SQL Server CE can be successfully installed on the computer, and then run the configure connectivity support in iis attached to it to create the IIS virtual directory sqlce, which points to the actual local path: c: \ Program Files \ Microsoft SQL Server ce 2.0 \ Server \, You can change the path of this folder to another one, but this folder must contain files related to the SQL Server ce server agent, such as sscesaw.dll. In the next step, you need to configure the user, permission, and authentication method to access the virtual directory. We can select Anonymous Access and accept the default Guest users of IIS, which generally starts with IUSR, in the access permission, select read and directory browsing. For details, see.

After the configuration is complete, we can check whether the SQL Server ce server agent works normally. Start IE and enter http: // localhost/sqlce/sscesaw.dll in the address bar, you can also change localhost to the actual host name. If the browser returns "SQL Server ce Server Agent", it indicates that the SQL Server ce Server Agent runs normally and IIS is correctly configured.

Next, we need to configure SQL Server 2000. In desktop SQL Server 2000, we need to create a task database, including the employee table and itemlist task table, and create a primary key and index for the ID field in the itemlist table, add the IIS Guest user set in IIS to access the sqlce virtual directory and its files, so that the user can access the SQL Server 2000 database through IIS.

Microsoft ActiveSync or wireless wi-fi 802.1x can be used for the network connection between the Pocket PC and the desktop PC. Because we are connected to a single desktop PC, We need to install IIS and SQL Server 2000 on it at the same time. The actual use of the server tool that SQL Server ce 2.0 runs in the desktop service environment has some problems with the connection of SQL Server 2000. Microsoft's solution to this problem is: first, set SQL Server 2000 to SP3 or SP4, and patch SQL Server 2000 to SQL Server 2.0 for SQL Server 2000 SP3 or SQL Server 2000 SP4, the SQL Server ce server components can work smoothly. The above updates and patches have been downloaded on the Microsoft website.

After the configuration is complete, start Visual Studio. NET 2003, click File-New-project-Visual Basic Project-smart device application, and create an empty project. The entire program consists of a form mainform, which contains two panel components and a button component. We divide the program into two main interfaces: a logon interface and an operation interface, they correspond to two panel controls. When you log on to a panel, the operation is hidden by panel. When you log on to a panel, the display properties of the two panels are the opposite. The button control provides the exit operation. The logon panel contains textbox and button controls for you to enter the web address of the SQL Server ce Server Agent, the name of the Remote SQL Server 2000 Server to be connected, and the username and password, and necessary logon information such as the employee ID and password. The following figure shows the actual device running:

When you click "Log on", the program first creates the SQL Server ce local database SDF file on the smart device. All the tables downloaded by this program are saved in this database:

...... 'Create a local user database file based on the specified SDF file path for RDA pull En = new sqlceengine ("Data Source =" & localdatabasefile) En. createdatabase () ......

Create an instance of the RDA object and enter the information used to communicate with the remote SQL Server ce Server Agent. Download valid user information data that allows you to log on to PPC to a local table:

RDA. pull ("userinfo", "select * from employee where emp_id = '" + empid + "' and emp_password = '" + password + "'", remoteconnstring, rdatrackoption. trackingoff)

The userinfo table returns the logged-on user's record information. Note that the value of rdatrackoption is trackingoff. Because we have not set the operation to Change User Information on PPC, we do not need to track this table. If the user name and password are verified, the number of records in the userinfo table is 1, that is, the value is greater than 0. Otherwise, no records are recorded, then, we use sqlceconnection, sqlcedataadapter, sqlcedatareader, and datatable to return all records in the userinfo table. If the number of records is 0, logon is not allowed:

If dtlocaluserinfo. Rows. Count> 0 then 'is verified Currentlogonuserid = dtlocaluserinfo. Rows (0). Item ("emp_id"). tostring Currentlogonusername = dtlocaluserinfo. Rows (0). Item ("emp_name"). tostring Return true Else 'verification failed, return false Return false End if

After successful logon, only relevant task records are returned Based on the login user name, and the task records are saved in the table named "itemlist:

RDA. Pull ("itemlist", "select * From itemlist where emp_id = '" + empid + "'", remoteconnstring, rdatrackoption. trackingonwithindexes)

Since RDA does not keep record locks, the push operation will unconditionally apply all data changes to SQL Server, which may cause loss of updated data of other users in the current database. We need to filter records of interest based on certain filtering conditions to obtain a unique and different set of records returned by other users. Trackingonwithindexes indicates that changes to the task status on PPC will be tracked, and changes will be updated to the remote database based on the changes in subsequent synchronization operations.

After the task records are obtained successfully, we hide the logon panel and place the operation panel at the top layer for adding, updating, and deleting the task records that have been synchronized.

The listview control is used to display task records. We traverse the returned itemlist table, create a listviewitem for each record, and add it to the listview for display:

While reader. Read Dim ls as new listviewitem (reader. Item ("ID"). tostring) Ls. subitems. Add (TRIM (reader. Item ("taskname"). tostring )) Ls. subitems. Add (TRIM (reader. Item ("taskcontent"). tostring )) Ls. subitems. Add (reader. Item ("finished"). tostring) Lvitemlist. Items. Add (LS) End while

Adding, deleting, and updating an SQL Server ce table is similar to a desktop database operation, for example, adding an SQL Server ce table:

Selectcmd. commandtext = "insert into itemlist (ID, taskname, taskcontent, finished, emp_id) Values ("+ _ Textbox1.text + "," + _ "'" + Taskname + "'" + "," + _ "'" + Taskcontent + "'" + "," + "0" + "," + "'" + currentlogonuserid + "'" + ")" Selectcmd. commandtimeout = 30 Conn. open () Selectcmd. executenonquery ()

After updating the local table of the smart device, you must save the changes to the remote SQL Server.Code:

RDA. Push ("itemlist", remoteconnstring, rdabatchoption. batchingon)

Push changes in batches to a remote SQL server. Note: Only the table to be tracked can be pushed. The following figure shows the actual device running:

The program runs successfully in Windows XP SP2 and Dell x3i (Pocket PC 2003) environments.