Recommendation: Classic tutorial area this article describes how to use RDA (RemoteDataAccess Remote Data Access) to achieve data access between SQLServerCE2.0 and the desktop SQLServer2000 database on the PocketPC (PPC. We will use VisualBasic. Net2003 for program development on smart devices. I,
Recommendation: Classic tutorial area this article describes how to use RDA (RemoteDataAccess Remote Data Access) to implement a Pocket PC (PPC) the data access between SQL Server CE 2.0 and the desktop SQL Server 2000 database. We will use Visual Basic. Net 2003 for program development on smart devices. I,
Recommendation: Classic tutorial Area
This topic describes how to use RemoteDataAccess
Data
Access)
ImplementationSQL Server CE 2.0 and desktop SQL Server 2000 on Pocket PC (PPC)
DataInter-Database
Data
Access. We will use Visual Basic. Net 2003 for program development on smart devices.
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
DataLibrary programming and desktop SQL Server
DataLibrary
Access. Socket programming is actually to communicate with the desktop PC through the TCP/IP protocol, it can easily transfer the general type
DataSuch as string, integer, and byte.
DataThe programmer needs to encapsulate it by himself.
DataThe Database Engine returns the specified
DataLibrary table
Data, You must write a desktop interface service program to query
DataAnd return the result to the smart device through Socket. So how can we achieve the same on a Pocket PC as on a desktop PC?
AccessLocal
DataDatabase or even remote desktop PC
DataLibrary? By running SQL Server CE on the smart device Pocket PC, we can easily
AccessSQL Server CE placed on the Pocket PC
DataDatabase, you can also use RDA in SQL Server CE or merge and copy quickly
ImplementationFrom smart devices
AccessRemote Desktop SQL Server2000
DataLibrary.
Ii. Technical Points
The full name of SQL Server CE is Microsoft SQL Server 2000 Windows CE Edition. It provides
AccessLightweight
DataLibrary solution. By using Microsoft Visual Studio. NET or Microsoft eMbedded Visual Tools and other development Tools, we can
DataThe management capability is extended 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 work with SQL Server
DataExchange:
1,
DataThe library engine is used to manage
DataStorage and tracking
DataAdding, updating, and deleting 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
DataDatabase Engine, using these object applications can control the connection with 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.
DataThe transmission of all depends on IIS.
We know from the above communication process that SQL Server CE's remote connection and
AccessThe Web transmission protocol Http or Https is required. the SQL Server CE Client Agent must run on a Windows CE device, and the SQL Server CE Server Agent must run on a desktop PC, in addition, IIS must be installed on the computer 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 Microsoft SQL Server 2000 Windows CE (SQL Server CE) used for programmable
AccessRemote Microsoft SQL Server 2000 or Microsoft SQL Server version 7.0
DataLibrary ActiveX control, we can use RDA
AccessRemote
DataThe database is like operating locally on a desktop PC.
DataLibrary is as simple.
Iii. Design Ideas
We will use Visual Basic. Net to create the "task manager For PPC" project to demonstrate how to use RDA to complete
DataLibrary connection and
Access. 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
DataLibrary client program, writing RDA program requires the SqlCeRemoteDataAccess class in the. net compression framework System. Data. SqlServerCe namespace. 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, which specify
DataWhich tables, columns, and records are extracted from the database to store in SQL Server CE?
DataLibrary. OledbConnectionString is used to connect to SQL Server
DataThe ole db connection string used in the 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.
Write PPC
DataControls and classes used by the Library Program and desktop Programming
DataLibrary programs are similar. SqlCeConnection corresponds to SqlConnection, SqlCeDataAdapter corresponds to SqlDataAdapter, SqlCeCommand corresponds to SqlCommand, etc. SqlCeConnection objects represent
DataA connection from the source must pass a valid connection string to the ConnectionString, for example:
| LocalConnString = "Data Source = \ Program File \ Task \ RDA. sdf" |
The Sdf file is SQL Server CE
DataLibrary 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 used to connect to SQL Server
DataThe ole db connection string used in the 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) |
