Applies to:
Microsoft® Windows Mobile® 2003 software for Smartphones
Microsoft Windows Mobile 2003 software for Pocket PCs
Microsoft Visual Studio® .NET 2003
Microsoft .NET Compact Framework 1.0
Summary: Learn how to use the sample project DataSet Server CE to reach and use remote databases from Smartphones and Pocket PCs running Windows Mobile 2003 software. (48 printed pages)
Download DataSet Server CE.msi from the Microsoft Download Center.
NoteDataSet Server CE is not a product and is not supported. DataSet Server CE is a sample project provided with source code that illustrates how you might perform data synchronization between a Windows Mobile-based Smartphone and a Microsoft SQL Server. Microsoft plans to support SQL CE in a future version of Windows Mobile. THE INFORMATION PROVIDED IN THIS ARTICLE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND.
A significant number of Windows Mobile-based Pocket PC enterprise applications use Microsoft SQL Server 2000 Windows CE Edition (SQL Server CE) for data storage and data synchronization. Developers choose SQL Server CE for a variety of reasons, including its high performance, security, stability, easy programmatic access, and direct links to server-side Microsoft SQL Server 2000 databases. The purpose of this article is to show how to use the sample project DataSet Server CE to get data store and data synchronization features, similar to those of SQL Server CE, in application development for Smartphones running Windows Mobile 2003 software. DataSet Server CE uses ADO.NET DataSet and DataTable objects to store and manage data. The source code supplied with this article consists of the following projects:
The sample code illustrates the following key areas:
The DataSet Server CE sample project is a connectivity and database integration solution for .NET Compact Framework applications. Microsoft SQL Server 2000 Windows CE Edition (SQL Server CE) provides a solid foundation for disconnected, connected, synchronization, data transfer, and replication scenarios. However, while the Windows Mobile 2003-based Smartphone includes the .NET Compact Framework in ROM, SQL Server CE is not yet supported.
DataSet Server CE mimics the behavior of a SQL Server CE Remote Data Access class to pull and push data, as well as in submitting SQL statements to remote SQL Servers. The technical interfaces of the DataSet Server CE interfaces are, as far as technically possible, identical to those of SQL Server CE 2.0. The underlying data technologies used to accomplish this are ADO.NET DataSet and DataTable objects. The feature set of DataSet Server CE is designed to resemble that of SQL Server CE as closely as possible, including names of methods, properties, enumerations, and behavior.
Table 1 illustrates the similarities and differences between DataSet Server CE and SQL Server CE 2.0.
Table 1. DataSet Server CE versus SQL Server CE 2.0
| Feature | SQL Server CE 2.0 | DataSet Server CE | Comments |
|---|---|---|---|
RemoteDataAccess
| X X X | X X X | DataSet Server can return number of affected rows. |
Pull
| X X X X | X X (X) (X) | Same as TrackingOn. Same as TrackingOff but primary keys are also created. |
Push
| X X | X X | |
| MergeReplication | X | ||
| ErrorsTable (created when Pull implemented) | X | ||
| Handles High Data Volume with no loss of performance | X | ||
| Supports Local Data Encryption | X | ||
| Compresses Data to Reduce Transit Times | X |
It is important to note that the similarities between DataSet Server CE and SQL Server CE are found only in the Remote Data Access classes; SQL Server CE outperforms DataSet Server CE from read, write, and remote communication perspectives.
DataSet Server CE relies on several components to exchange data from a .NET Compact Framework application to an instance of SQL Server. Figure 1 shows the relationship between the different components.
Figure 1 DataSet Server CE client and server
The .NET Compact Framework ADO.NET classes manage the data store on the Windows CE-based device.
The DataSet Server CE is the primary component for connectivity on the Windows CE-based device. It implements the DataSetRemoteDataAccess (DSRDA) object and the Engine object. By using these objects, applications can programmatically control connections to SQL Server.
The DataSet Web Service Server Agent is a Web service that handles the HTTP requests made by DataSet Server CE. When an application makes a request, DataSet Server CE sends the request to the DataSet Web Service Server Agent through HTTP. The DataSet Web Service Server Agent connects to SQL Server and returns the resulting recordset to the DataSet Server CE through HTTP. Additional SQL Server connectivity components are involved in this process and are also located on the server computer running Microsoft Internet Information Services (IIS), although they are not shown in the preceding illustration.
The DataSet Web Service Server Agent is handled by IIS and the ASP engine. For low data volumes, the performance impact in IIS and ASP of using DataSet Server CE is minimal, mainly because the amount of data transferred to and from the server is not substantial. There is a scalability bottleneck in the Push method when the pushed DataTable object was pulled as a "Tracked" table because the Push method contains all the synchronization logic. However, since the amount of data is minimal in typical Smartphone scenarios, this is normally not an issue.
SQL Server CE also performs compression on data at the IIS and device end to reduce the time to send traffic. DataSet Server CE does not do this.
For small data volume and from a SQL Server 2000 perspective, there is no noticeable difference in performance impact between SQL Server CE and DataSet Server CE, because both have the server execute the same SQL statements. For larger data volumes The performance of DataSet Server CE may suffer.
By maintaining a SQL Server table called DataServerCETracking, the DataSet Web Service Server Agent can track all database records that are inserted, updated, or deleted.
DataSet Server CE works with the following network connectivity mechanisms as long as they are configured to support HTTP:
By using Microsoft ActiveSync® 3.5 (or later) and a Pocket PC 2002 (or later) or Smartphone 2003 (or later) device connected using serial, infrared, or USB, you can directly access SQL Server through the network connection of the desktop computer.
To use DataSet Server CE:
DataSet Server CE consists of three classes; two client side and one server side. Table 2 provides an overview of these classes.
Table 2 Overviews of DataSet Server CE's classes
| Class | Environment | Namespace | Description |
|---|---|---|---|
| DataSetEngine | Client | Microsoft.Sample.Data.DataSetServerCE | Used to create a DataSet XML file. |
| DataSetRemoteDataAccess | Client | Microsoft.Sample.Data.DataSetServerCE | Enables programmatic access to remote Microsoft SQL Server 2000 or Microsoft SQL Server version 7.0 databases and manages local DataSet XML files. |
| DataSetServerAgent | Server | Microsoft.Sample.Data.DataSetServerAgent | Used to communication with the client component and the server database. |
Figure 2 shows how the classes interact with local DataSet XML files, with each other, and with remote databases. The only four public methods that are called from the .NET Compact Framework application are CreateDatabase, Pull, Push, and submitSQL.
Figure 2 How the DataSet Server CE classes interact. Click the thumbnail for a larger image.
The Smartphone Demonstration Client sample was created with Microsoft Visual Studio .NET 2003 C#, and .NET Compact Framework.
With no SQL Server CE present, DataSet Server CE provides a comprehensive as well as extensible feature set for remote database synchronization. Smartphones running Windows Mobile 2003 support the .NET Compact Framework and the DataSet Server CE client component, either compiled or added as a source code class file. The following fundamental aspects are highlighted in this walkthrough:
The following section provides a user interface walkthrough. Detailed code walkthrough is found under the section titled "Pocket PC Demonstration Client Walkthrough."
Figure 3 illustrates the MainForm form.
Figure 3: Access to all remote data access features
The MainForm form, along with all other forms, implements the standard Nina 11-pt font. The feature set in the Smartphone Demonstration Client is similar to its Pocket PC sibling.
The Smartphone client does not enable the user to change the OLE DB connection string to the remote SQL Server or Internet Url to the Server Agent. Most Smartphone applications in the enterprise will most likely be developed with a particular database and Web service in mind. The default values of these properties are set in the MainForm form's constructor:
// Default values for this demo application.Common.Common.Values.InternetLogin = @"";Common.Common.Values.InternetPassword = @"";Common.Common.Values.LocalConnectionString = @"";Common.Common.Values.InternetUrl =
@"http://server/DataSetServerAgent1/DataSetServerAgent.asmx";Common.Common.Values.oledbConnectionString = "Provider=sqloledb;
Integrated Security=SSPI; Data Source=(local); Initial
Catalog=Northwind;";
Figure 4 illustrates the OptionsForm form.
Figure 4 Remote data access settings
As mentioned earlier, the Smartphone client does not enable the user to change the OLE DB connection string to the remote SQL Server or Internet URL to the Server Agent. To obtain the path to the nonvolatile folder of the Smartphone, a call to getSpecialFolderPath is made when the OptionsForm form loads. The application directory is created if it does not exist, as shown in the following example:
我要上传// Get folder path to local storage.string path = getSpecialFolderPath(CSIDL.CSIDL_APPDATA);// Add application specific folder to path.path = path + @"\dssceClientXMP\";// If directory does not exist, then create!if(!Directory.Exists(path)){Directory.CreateDirectory(path);}Figures 5, 6, 7, and 8 illustrate the PullForm, PushForm and SubmitSQLForm forms.
NoteThe code base for these forms is the same as that of the Pocket PC Demonstration Client Walkthrough, except that it implements a slightly different user interface.
Figure 5 Local DataTable name, SQL string and tracking options
Figure 6 MainForm populated after pull
Figure 7 Pushing back changes to remote SQL server
Figure 8 Executing nonCrow returning SQL statements
The Pocket PC Demonstration Client sample was created with Microsoft Visual Studio .NET 2003 C# and .NET Compact Framework.
SQL Server CE is the best choice for the local data store in application development Windows Mobile-based Pocket PCs. SQL Server CE provides fast access to stored data, structured interfaces through SQL grammar, similar to that in SQL Server 2000, which you can use to query a database and to insert, update, and delete data in tables in a database. Again, the similarities between the DataSet Server CE sample project and SQL Server CE are found only in the Remote Data Access classes; SQL Server CE outperforms DataSet Server CE from read, write, and remote communication perspectives.
The Pocket PC demonstration client is provided to illustrate striking similarities between development for Pocket PCs and Smartphones, primarily with regard to managing local XML files, ADO.NET DataSet and DataTable objects, and Web service consumption, as well as to show that the two platforms can share the same code base from a component perspective. The Pocket PC demonstration client also illustrates how to implement a DataSet-based storage scenario when there is only a modest amount of data.
The following forms are used to implement the user interface:
Figure 9 illustrates the MainForm form.
Figure 9 Access to all remote data access features
NoteAll lines of code that are commented in this article are highlighted, using a bold font, in the actual code blocks.
All forms, including the MainForm form, reference the following two classes:
using Microsoft.Sample.Common;using Microsoft.Sample.Data.DataSetServerCE;
The MainForm form is the startup object and begins by setting default common application values, as shown in the following example:
// Default values for this demo application.Common.Common.Values.InternetLogin = @"";Common.Common.Values.InternetPassword = @"";Common.Common.Values.LocalConnectionString = @"";Common.Common.Values.InternetUrl =
@"http://server/DataSetServerAgent1/DataSetServerAgent.asmx";Common.Common.Values.oledbConnectionString = "Provider=sqloledb;
Integrated Security=SSPI; Data Source=(local); Initial
Catalog=Northwind;";
The MainForm form displays the contents, both data and schema, of the local DataSet XML file. The two textboxes are populated by the private method updateXML. To enable use of the DataSet.ReadXml XmlReadMode argument, the DataSet object is read by using a FileStream object. DataSet.GetXml fetches the XML data, and DataSet.GetXmlSchema fetches the schema. The exception handler throws any exception to the caller. This strategy is implemented throughout the demonstration client as well as in all code within all actual DataSet Server CE classes.
/// summary>/// Update the MainForm's textbox./// /summary>private void updateXML(){try{Cursor.Current=Cursors.WaitCursor;DataSet ds = new DataSet();// Open local DataSet XML file and Schema.// Create the FileStream to read and write with.FileStream fs = new FileStream(Common.Common.Values.LocalConnectionString,FileMode.Open,FileAccess.ReadWrite);// Create an XmlTextReader with the FileStream.XmlTextReader xtr = new XmlTextReader(fs);// Read from the file with the ReadXml method.ds.ReadXml(xtr, XmlReadMode.ReadSchema); xtr.Close();this.txtDataSetXML.Text = ds.GetXml(); this.txtDataSetXSD.Text = ds.GetXmlSchema();}catch(Exception Ex){// Throw exception to caller. throw Ex;}finally{Cursor.Current=Cursors.Default;}}Figure 10 illustrates the OptionsForm form.
Figure 10 Remote data access settings
All settings are read from the Common singleton class when the OptionsForm form loads. The parentForm is whatever form (for example, MainForm) called the OptionsForm form. A reference to the parent form is passed when the OptionsForm form is instantiated. The reference is used to enable an easy way back to the calling form.
private void OptionsForm_Load(object sender, System.EventArgs e){this.txtInternetLogin.Text = Common.Common.Values.InternetLogin;this.txtInternetPassword.Text =
Common.Common.Values.InternetPassword;this.txtLocalConnectionString.Text = Common.Common.Values.LocalConnectionString;this.txtOLEDBConnectionString.Text = Common.Common.Values.oledbConnectionString;this.txtInternetUrl.Text = Common.Common.Values.InternetUrl;parentForm.Hide();Cursor.Current = Cursors.Default;}A new local DataSet XML file is created when the user taps the Create database button. The DataSetEngine.LocalConnectionString property is set, and then the DataSetEngine.CreateDatabase method is called. The btnCreate_Click event code is the last caller and gets any exceptions thrown from the DataSetEngine class. These exceptions are shown to the user.
private void btnCreate_Click(object sender, System.EventArgs e){try
{Cursor.Current = Cursors.WaitCursor;DataSetEngine dse = new DataSetEngine();dse.LocalConnectionString = this.txtLocalConnectionString.Text; dse.CreateDatabase();}catch(Exception Ex){// First caller! MessageBox.Show("An error occurred! Message: " +
Ex.Message.ToString()); }finally{Cursor.Current=Cursors.Default;}}The form closes when the user taps the form's OK button. All settings are read back to the Common class. Lastly, the parentForm form is made visible.
private void OptionsForm_Closing(object sender,
System.ComponentModel.CancelEventArgs e){Common.Common.Values.InternetLogin = this.txtInternetLogin.Text;Common.Common.Values.InternetPassword =
this.txtInternetPassword.Text;Common.Common.Values.LocalConnectionString = this.txtLocalConnectionString.Text;Common.Common.Values.oledbConnectionString = this.txtOLEDBConnectionString.Text;Common.Common.Values.InternetUrl = this.txtInternetUrl.Text;parentForm.Show();}
Figure 11 illustrates the PullForm form.
Figure 11 Local DataTable name, SQL string, and tracking options
The PullForm form is the first form that uses the DataSet Server CE Remote Data Access class in this walkthrough. The first thing that happens when the user taps Pull-button is that the trackOption value is determined. The trackOption value specifies whether DataSet Server CE should do the following:
The DataSet Server CE Remote Data Access class is instantiated, and its properties are set. The Pull method does not return anything itself, but it populates the defined local DataSet XML file with data by adding a new DataTable object to the DataSet object. Any exceptions thrown from the DataSet Web Service Server Agent are shown to the user.
private void btnPull_Click(object sender, System.EventArgs e){RdaTrackOption trackOption;// Determine what RdaTrackOption to use. if(this.chkIndexes.Checked) { if(this.chkTracking.Checked) {trackOption = RdaTrackOption.TrackingOnWithIndexes;} else{trackOption = RdaTrackOption.TrackingOffWithIndexes;} }else { if(this.chkTracking.Checked) {trackOption = RdaTrackOption.TrackingOn;} else{trackOption = RdaTrackOption.TrackingOff;} }try{Cursor.Current=Cursors.WaitCursor;DataSetRemoteDataAccess dsrda = new DataSetRemoteDataAccess();// Set DataSetRemoteDataAccess properties.dsrda.InternetUrl = Common.Common.Values.InternetUrl;dsrda.InternetLogin=Common.Common.Values.InternetLogin;dsrda.InternetPassword=Common.Common.Values.InternetPassword;dsrda.LocalConnectionString=Common.Common.Values.LocalConnectionString;// Pull data. dsrda.Pull(this.txtLocalTableName.Text, this.txtPullSQL.Text, Common.Common.Values.oledbConnectionString, trackOption);}catch(Exception Ex){// First caller! MessageBox.Show("An error occurred! Message: " + Ex.Message.ToString());}finally{Cursor.Current=Cursors.Default;}}If the user specified that DataSet Server CE should track changes, a table named DataSetServerCETracking in the remote database was populated with the following information:
This information is used when the user later pushes changes back to the server, because it is the DataSet Web Service Server Agent that implements the actual tracking.
As shown in Figure 12, when the data has been pulled and the user returns to the main form, the DataSet XML data and schema is shown in the textboxes.
Figure 12 MainForm populated after pull
Figure 13 illustrates the PushForm form.
Figure 13 Pushing back changes to remote SQL server
The DataSet XML data can be updated or deleted, and new data rows can be inserted. The user can then choose to push these changes back, if tracking was selected when the Pull method was executed. If the Batching check box is checked, all changes are committed as one single transaction. Any uncommitted changes will be rolled back if an exception occurs. All changes will be committed individually if the Batching checkbox is unchecked.
private void btnPush_Click(object sender, System.EventArgs e){try{Cursor.Current=Cursors.WaitCursor;string localTableName = this.txtLocalTableName.Text;string localConnectionString = Common.Common.Values.LocalConnectionString;DataSetRemoteDataAccess dsrda = new DataSetRemoteDataAccess();// Set DataSetRemoteDataAccess properties.dsrda.InternetUrl = Common.Common.Values.InternetUrl;dsrda.InternetLogin=Common.Common.Values.InternetLogin;dsrda.InternetPassword=Common.Common.Values.InternetPassword;dsrda.LocalConnectionString=localConnectionString;// Push data. BatchingOn/BatchingOff, depending on checkbox.if(this.chkBatching.Checked) { dsrda.Push(localTableName, Common.Common.Values.oledbConnectionString, RdaBatchOption.BatchingOn); } else {dsrda.Push(localTableName, Common.Common.Values.oledbConnectionString, RdaBatchOption.BatchingOff); }}catch(Exception Ex){// First caller!MessageBox.Show("An error occurred! Message: " + Ex.Message.ToString());}finally{Cursor.Current=Cursors.Default;}Figure 14 illustrates the SubmitSQLForm form.
Figure 14 Executing nonCrow returning SQL statements
The SubmitSQLForm is similar to both the Pull and the Push forms. The user can enter an SQL statement, for example―an UPDATE or DELETE statement―and have the DataSet Web Service Server Agent execute the statement on the remote SQL Server.
To stay as similar as possible to SQL Server CE Remote Data Access, no data is returned. However, the Server Agent can easily be modified to return the number of affected rows. This will be further explained in the code walkthrough of the Server Agent.
private void btnSubmitSQL_Click(object sender, System.EventArgs e){try{Cursor.Current=Cursors.WaitCursor;// Set DataSetRemoteDataAccess properties.DataSetRemoteDataAccess dsrda = new DataSetRemoteDataAccess();dsrda.InternetLogin=Common.Common.Values.InternetLogin;dsrda.InternetPassword=Common.Common.Values.InternetPassword;dsrda.InternetUrl = Common.Common.Values.InternetUrl;dsrda.submitSql(this.txtSubmitSQL.Text, Common.Common.Values.oledbConnectionString);}catch(Exception Ex){// First caller!MessageBox.Show("An error occurred! Message: " + Ex.Message.ToString());}finally{Cursor.Current=Cursors.Default;}}The DataSetServerCE.cs file contains the Engine and the Remote Data Access classes. DataSetServerCE references the DataSet Web Service Server Agent, so there is no need for applications using DataSet Server CE to reference the DataSet Web Service Server Agent separately.
using DataServerCE.net.dataserveragent;
The Engine class implements just one public method: CreateDatabase. CreateDatabase creates a new local DataSet XML file with inline XML schema. A FileStream object and an XmlTextWriter object are used to create the file. To get the schema included in the XML file, the DataSet.WriteXml method is called using the XmlWriteMode.WriteSchema argument. Any exception will be thrown to the caller. This will be the case if a file with the same filename already exists in the specified directory.
/// summary>/// Creates a new database (local DataSet XML file)./// /summary>public void CreateDatabase(){try{// New DataSet with the same name, "DataSetServerCE".DataSet ds = new DataSet("DataSetServerCE");// Create the FileStream to write with.FileStream fsWrite = new FileStream(localConnectionString, FileMode.CreateNew, FileAccess.ReadWrite);// Create an XmlTextWriter with the FileStream.XmlTextWriter xtw = new XmlTextWriter(fsWrite, System.Text.Encoding.Unicode); // Write to the file with the WriteXml method. ds.WriteXml(xtw, XmlWriteMode.WriteSchema);// Close.xtw.Close();fsWrite.Close();}catch(Exception Ex){// Throw exception to caller.throw Ex;}finally {}}The Remote Data Access class is where all management of the local DataSet XML files occurs. The class is responsible for adding DataTable objects to the DataSet object during Pull operations, returning DataTable objects to the server during Push operations, and passing SQL statements to the server.
The private properties used when calling the DataSet Web Service Server Agent are set in the class constructors. The method summary and parameter documentation resemble that of SQL Server CE equivalence.
/// summary>/// Initializes a new instance of the DataStRemoteDataAccess object and
configures it for a Basic or Integrated Windows authentication to
Microsoft Internet Information Services (IIS)./// /summary>/// param name="internetUrl">The URL used to connect to the DataSet
Web Service Server Agent./param>/// param name="internetLogin">Specifies the IIS login name used when
connecting to the DataSet Web Service Server Agent./param>/// param name="internetPassword">Specifies the IIS password used when
connecting to the DataSet Web Service Server Agent./param>/// param name="localConnectionString">Specifies the path to the local
DataSet XML file./param>public DataSetRemoteDataAccess(string internetUrl,string internetLogin,string internetPassword,string localConnectionString){// Set private variables.internetUrl = internetUrl;internetLogin = internetLogin;internetPassword = internetPassword;localConnectionString = localConnectionString;}
The public methods of the class just pass on the calls to the private methods. The reason is both to handle overloaded public methods as well as enable pre-processing code assertions if needed. The following code walkthroughs will focus on the three private methods: Pull, Push, and submitSql.
The private method Pull pulls data from the remote server and adds it to the DataSet XML file in the form of a DataTable object. The key sequence to understand is the following. The code comments are highlighted in the code.
private void pull(string localTableName,string sqlSelectString,string oledbConnectionString,DataSetServerCE.RdaTrackOption trackOption){try{// Open local DataSet XML file and Schema.DataSet ds = new DataSet();// Create the FileStream to read with.FileStream fsRead = new FileStream(localConnectionString,FileMode.Open,FileAccess.Read);// Create an XmlTextReader with the FileStream.XmlTextReader xtr = new XmlTextReader(fsRead);// Read from the file with the ReadXml method. ds.ReadXml(xtr, XmlReadMode.ReadSchema); // Close.xtr.Close();fsRead.Close();// Instantiate DataSet Web Service Server Agent.DataSetServerAgent dssa = new DataSetServerAgent();// Set the InternetUrl.dssa.Url = internetUrl;// Set NetworkCredentials if InternetLogin has been set. // Blank password is OK.if(internetLogin != ""){dssa.Credentials = new NetworkCredential(internetLogin, internetPassword);}// Convert trackOption to Web service trackOption.DataServerCE.net.dataserveragent.RdaTrackOption
trackOptionServer = DataServerCE.net.dataserveragent.RdaTrackOption.TrackingOff;switch(trackOption) { case DataSetServerCE.RdaTrackOption.TrackingOff:trackOptionServer =
DataServerCE.net.dataserveragent.RdaTrackOption.TrackingOff;break; case DataSetServerCE.RdaTrackOption.TrackingOffWithIndexes: trackOptionServer =
DataServerCE.net.dataserveragent.RdaTrackOption.TrackingOffWithIndexes;break; case DataSetServerCE.RdaTrackOption.TrackingOn: trackOptionServer =
DataServerCE.net.dataserveragent.RdaTrackOption.TrackingOn;break; case DataSetServerCE.RdaTrackOption.TrackingOnWithIndexes: trackOptionServer =
DataServerCE.net.dataserveragent.RdaTrackOption.TrackingOnWithIndexes;break; }// Retrieve DataSet from server.DataSet dsServer = dssa.Pull(sqlSelectString, oledbConnectionString, trackOptionServer);// Get DataTable from the server DataSet.DataTable dtServer = dsServer.Tables[0];// Remove the DataTable from the server DataSet.dsServer.Tables.Remove(dtServer);// Change the the DataTable name to localTableName.dtServer.TableName = localTableName;// Add the DataTable to the local DataSet.ds.Tables.Add(dtServer);// Save local DataSet XML file and Schema.ds.AcceptChanges();// Create the FileStream to write with.FileStream fsWrite = new FileStream(localConnectionString,FileMode.Open,FileAccess.ReadWrite);// Create an XmlTextWriter with the FileStream.XmlTextWriter xtw = new XmlTextWriter(fsWrite,System.Text.Encoding.Unicode);// Write to the file with the WriteXml method.ds.WriteXml(xtw, XmlWriteMode.WriteSchema); // Close.xtw.Close();fsWrite.Close();}catch(Exception Ex){// Throw exception to caller. throw Ex;}finally {}}The private method Push pushes back any changes made to the data in a specified DataTable object. This is done by passing it to the Server Agent. The Server Agent can obtain the original data that the DataTable object contained by querying the DataSetServerCETracking table since a unique trackingID value was added to its ExtendedProperties collections when it was pulled.
The following lines of code show what has not already been shown in previous code segments. The DataTable object passed to the Server Agent has to be detached from the local DataSet XML file. Because DataTable.Copy is not supported in the .NET Compact Framework, the following process takes place:
// Get DataTable from dsALL.dtClient = dsAll.Tables[localTableName];// Remove DataTable from dsAll.dsAll.Tables.Remove(dtClient);// Add DataTable to DataSet that will be pushed to server.dsClient.Tables.Add(dtClient);// Instantiate DataSet Web Service Server Agent.DataSetServerAgent dssa = new DataSetServerAgent();// Set the InternetUrl.dssa.Url = internetUrl;// Set NetworkCredentials if InternetLogin has been set. // Blank password is OK.if(internetLogin != ""){dssa.Credentials = new NetworkCredential(internetLogin,
internetPassword");}// Convert batchOption to Web service batchOption.DataServerCE.net.dataserveragent.RdaBatchOption
batchOptionServer = DataServerCE.net.dataserveragent.RdaBatchOption.BatchingOff;switch(batchOption) { case DataSetServerCE.RdaBatchOption.BatchingOff:batchOptionServer =
DataServerCE.net.dataserveragent.RdaBatchOption.BatchingOff;break; case DataSetServerCE.RdaBatchOption.BatchingOn: batchOptionServer = DataServerCE.net.dataserveragent.RdaBatchOption.BatchingOn;break;}// Push DataSet to serverdssa.Push(dsClient, oledbConnectionString, batchOptionServer);}catch(Exception Ex){// Throw exception to caller.throw Ex;}finally{// Reject changes to dsAll.dsAll.RejectChanges();}}
The private method submitSql simply passes a valid nonCrow returning SQL statement to the remote SQL Server―for example, UPDATE or DELETE statements. Note that the private method gets the number of rows affected returned from Server Agent. This number is not passed to the calling public method submitSql, which in turn does not pass it to the application. The reason is simply to stay similar to SQL Server CE, but you can add a return statement to this method if you wish.
// Submit SQL. Get RowsAffect back but don't pass it on, // to stay SqlCe-like.int RowsAffected = dssa.submitSql(sqlString, oledbConnectionString);
The Server Agent is implemented as a Web service. The Web.config file has to be updated to implement Basic Authentication or Integrated Windows Security. The "identity impersonate" setting enables the ASP.NET process to impersonate the login, for which the credentials (login name and password) were passed from the client.
authentication mode="Windows" /> identity impersonate="true" />
The Server Agent implements all database calls through classes in the System.Data.OleDb namespace instead of the System.Data.SqlClient namespace. The reason is to stay similar to SQL Server CE, which relies on the use of OLE DB connection strings. The OLE DB classes used can easily be changed to corresponding SqlClient classes because the interfaces are almost identical.
The DataSetServerAgent class is responsible for interaction between the client and the remote SQL Server.
The public methods of the class just pass on the calls to the private methods, both to handle overloaded public methods and to enable pre-processing code assertions if needed. The following code walkthroughs will focus on the three private methods: Pull, Push, and submitSql.
The code walkthrough of the DataSetServerAgent class will cover the first level of procedures. Sublevel procedures are comment documented in the source code.
The private method Pull extracts data from the SQL Server by using the specified OLE DB connection string and SQL SELECT string. The following aspects of the code block below need to be noted:
private DataSet pull(string sqlSelectString,string oledbConnectionString,RdaTrackOption trackOption){try{// DataSet that will be returned.DataSet ds = new DataSet("DataSetServerCE"); OleDbConnection oledbConnection = new OleDbConnection(oledbConnectionString);OleDbDataAdapter oledbDataAdapter = new OleDbDataAdapter(sqlSelectString, oledbConnection);// DataTable to hold data.DataTable dt = new DataTable();// Add primary keys if TrackingOn, TrackingOnWithIndexes, // or TrackingOffWithIndexes.if(trackOption == RdaTrackOption.TrackingOff){// Get data and put into DataTable.oledbDataAdapter.Fill(dt);}else{// Get schema. oledbDataAdapter.FillSchema(dt, SchemaType.Mapped); // Get data and put into DataTable. oledbDataAdapter.Fill(dt);}// Add DataTable to DataSet.ds.Tables.Add(dt);// If TrackingOn or TrackingOnWithIndexes, store TrackingID, // sqlSelectString, and DataSet XML in database.if(trackOption == RdaTrackOption.TrackingOn | trackOption == RdaTrackOption.TrackingOnWithIndexes){// GUID that uniquely identifies the tracked pull. Guid trackingID = System.Guid.NewGuid(); // Add GUID to DataTable's ExtendedProperties. ds.Tables[0].ExtendedProperties.Add("TrackingID", trackingID.ToString()); // Store tracking information to database. addTracking(trackingID, sqlSelectString, ds,
oledbConnection); }// Return DataSetreturn ds;}catch(Exception Ex){// Throw exception to caller. throw Ex;}finally{}}The private method Push is responsible for updating the SQL Server table based on changes that have occurred in the DataTable object passed from the client. This is by far the most interesting method in the DataSet Server CE implementation. Because of its scope, the code walkthrough of the Push method will be done step-by-step instead of everything in one go.
The method accepts three parameters: the DataSet object from the client containing the changed DataTable object, the oledbConnectionString value, and a batch option. The batch option specifies whether all changes that are to be applied to the SQL Server should be committed or rolled back all together in one single transaction or whether each change should be applied individually.
private void push(DataSet dsClient,string oledbConnectionString,RdaBatchOption batchOption)
A new DataTable object is created based on the first DataTable object in the passed DataSet object. The trackingID value is obtained from the DataTable object's ExtendedProperties collection:
// DataTable from dsClient.DataTable dtClient = dsClient.Tables[0];// TrackingID that was created when the data was pulled.string TrackingID =
dtClient.ExtendedProperties["TrackingID"].ToString();
Two OleDbConnection objects are created. One is for the batching on scenario, and the other is for the batching off scenario.
// Create OleDbConnection.OleDbConnection oledbConnection = new OleDbConnection(oledbConnectionString);OleDbConnection oledbConnectionTrans = new
OleDbConnection(oledbConnectionString);
A new DataSet object, dsTracking, is created based on the data stored in the SQL Server table DataSetServerCETracking.
// Get tracking data (original sqlSelectString and DataSetXML).DataSet dsTracking = getTrackingData(TrackingID, oledbConnection);
The sqlSelectString value is retrieved from the dsTracking DataSet object. The name of the table to update is identified based on the sqlSelectString value, so it is important that the initial SQL SELECT statement contain a valid table name right after the FROM keyword.
// Get SqlSelectString from original Pull.string SqlSelectString = dsTracking.Tables["TrackingDataTable"].Rows[0]
["SqlSelectString"].ToString();// Get TableName from SQL SELECT string.string TableName = getTableName(SqlSelectString);
The dsTracking DataSet object also contains the XML data and schema that was initially sent to the client during the Pull process. A DataSet object, dsServer, is created based on this information by calling the getDataSetServer method.
// Get DataSetXML, and create new DataSet based on stored XML and
schema.DataSet dsServer = getDataSetServer(dsTracking);
The most important line of code in the getDataSetServer method is the following, which reads the XML data and schema that are stored in a DataSet object row:
ds.ReadXml( new
StringReader(dsTracking.Tables["TrackingDataTable"].Rows[0]
["DataSetXML"].ToString()), XmlReadMode.ReadSchema );
The dtServer DataTable object is created in the getDataTableServer method. This is the most critical DataTable object in the process. The dtServer DataTable object contains the data that was initially sent to the client. getDataTableServer adds a new column, S_Operation, to the DataTable object. The column is populated with the keyword UPDATE, DELETE, or INSERT, depending on what action to take. The DataRows object with the word INSERT is populated by using the dtClient DataTable object, which is also passed to the method. These are DataRow objects that were added on the client. The primary key columns are identified once the dtServer object is returned.
// Get DataTable from dsServer with data on what database operations
(SQL statements) to perform.DataTable dtServer = getDataTableServer(dtClient, dsServer);// Collection of PrimaryKey columns.DataColumn[] dcPrimaryKey = dtServer.PrimaryKey;
The primary key columns are important because of what goes on in the code moving forward:
It is now time to open the oledbConnection object. The following code segment opens connections, creates an OleDbTransaction object for the batching scenario, and begins a transaction:
// Open connection if closed.// One connection for each scenario: BatchingOn or BatchingOff.if(oledbConnectionTrans.State == ConnectionState.Closed)oledbConnectionTrans.Open();if(oledbConnection.State == ConnectionState.Closed)oledbConnection.Open();// Transaction object if BatchingOn.OleDbTransaction oledbTransaction;// Transaction object.oledbTransaction = oledbConnectionTrans.BeginTransaction(IsolationLevel.ReadCommitted);
All database updates are performed by using OleDbCommand and OleDbParameter objects. The following code builds and executes the INSERT statements. The format of an INSERT statement is: INSERT INTO TableName(c1, c2, c3) VALUES(?1, ?2, ?3). The rows to INSERT into the SQL Server are fetched from the dtServer DataTable object using the DataTable.Select method, which finds the rows based on a search criteria ("S_Operation = 'INSERT'"). The column list and values list are built. The command is then executed.
// Use dtClient.Columns instead of dtServer.Columns to // not get the S_Operation column.// Format of InsertStatement:// INSERT INTO TableName(c1, c2, c3) VALUES(?1, ?2, ?3).string InsertColumnList = "";string ValueList = "";OleDbCommand cmdInsert = new OleDbCommand();// Make InsertColumnList and add VALUE parameters.foreach(DataColumn dataColumn in dtClient.Columns){InsertColumnList = InsertColumnList + dataColumn.ColumnName.ToString() + ", ";ValueList = ValueList + "?, ";// Add parameter to command.cmdInsert.Parameters.Add(new OleDbParameter(dataColumn.ColumnName.ToString(),
convertToOleDb(dataColumn.DataType)));}// Get InsertStatement string.string InsertStatement = getInsertStatement(TableName, InsertColumnList, ValueList);// Set OleDbCommand for Insert.cmdInsert.CommandText = InsertStatement;// Connection property depending on Batching scenario.if(batchOption == RdaBatchOption.BatchingOn) {cmdInsert.Connection = oledbConnectionTrans;}else{cmdInsert.Connection = oledbConnection;}// Use the Select method to find all rows matching the filter.DataRow[] drFoundRowsInsert; drFoundRowsInsert = dtServer.Select("S_Operation = 'INSERT'");foreach(DataRow drFound in drFoundRowsInsert){// Populate parameter values.foreach(DataColumn dataColumn in dtClient.Columns){
cmdInsert.Parameters[dataColumn.ColumnName.ToString()].Value = drFound[dataColumn.ColumnName.ToString()].ToString();}// BatchingOn or BatchingOff.if(batchOption == RdaBatchOption.BatchingOn){cmdInsert.Transaction = oledbTransaction;}// Execute insert command.int num = cmdInsert.ExecuteNonQuery();}The UPDATE and DELETE processes are quite similar. It is worth noting that the UPDATE process can, with few code changes, implement a column level update instead of the SQL Server CE-like row level update. Finally, the last code block from the Push method contains the catch and finally statements. You can see that for the batching scenario, a rollback is executed if any exception occurs.
catch(Exception Ex){if(batchOption == RdaBatchOption.BatchingOn){try{oledbTransaction.Rollback();}catch (OleDbException OleDbEx){if (oledbTransaction.Connection != null){throw OleDbEx;}}}// Throw out exception.throw Ex;}finally {// Close connections.if(oledbConnection.State == System.Data.ConnectionState.Open)oledbConnection.Close();if(oledbConnectionTrans.State == System.Data.ConnectionState.Open)oledbConnectionTrans.Close();}}catch(Exception Ex){// Throw exception to caller.throw Ex;}finally{}The private method submitSql is responsible for passing nonCrow returning SQL statements to the server.
The sqlString value is passed to the SQL Server using an OleDbCommand object and its ExecuteNonQuery method. Any exception is thrown to the caller.
private int submitSql(string sqlString,string oledbConnectionString){try{OleDbConnection cn = new
OleDbConnection(oledbConnectionString);OleDbCommand cmd = new OleDbCommand(sqlString, cn);// Open connection.cn.Open();// Execute the SQL.int RowsAffected = cmd.ExecuteNonQuery();// Return rows affected.return RowsAffected;}catch(Exception Ex){// Throw exception to caller.throw Ex;}finally{}}This article provided information, source code, and reusable components to address database connectivity needs in application development Windows Mobile 2003-based Smartphones and Pocket PCs. Until Windows Mobile provides built-in SQL support, the DataSet Server CE sample project provides a solution you can use in your project. DataSet Server CE is not a product; it is a sample project provided "as is". The sample project illustrates that the combined use of local XML files, a Web service, ADO.NET, and a remote SQL Server can provide a basis for database synchronization between a Smartphone running Windows Mobile 2003 software and a remote database server.
The following appendix provides a detailed documentation of each class, method, and property in the client-side DataSet Server CE component.
Table 3 Public Constructors
 DataSetEngine | Overloaded. Initializes a new instance of the DataSetEngine class. |
Table 4 Public Properties
 LocalConnectionString | Gets or sets the path to the local DataSet XML file. |
Table 5 Public Methods
 CreateDatabase | Creates a new local DataSet XML file. |
 Equals (inherited from Object) | Overloaded. Determines whether two Object instances are equal. |
 GetHashCode (inherited from Object) | Serves as a hash function for a particular type, suitable for use in hashing algorithms and data structures like a hash table. |
 GetType (inherited from Object) | Gets the Type of the current instance. |
 ToString (inherited from Object) | Returns a String that represents the current Object. |
DataSetEngine.CreateDatabase Method
Creates a new database.
[C#]
public void CreateDatabase();
<
