DataSet Server CE |
作者:Andy Sjostrom 来源:businessanyplace.net | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Applies to: 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. Note DataSet 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. IntroductionA 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:
DataSet Server CE DescribedThe 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
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 Client and Server EnvironmentsDataSet 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 ADO.NET DataSetThe .NET Compact Framework ADO.NET classes manage the data store on the Windows CE-based device. DataSet Server CEThe 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. DataSet Web Service Server AgentThe 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. Supported Connectivity SolutionsDataSet 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 ClassesDataSet 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
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. Smartphone Demonstration Client WalkthroughThe 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." MainFormFigure 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 = OptionsFormFigure 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); } PullForm, PushForm, and SubmitSQLFormFigures 5, 6, 7, and 8 illustrate the PullForm, PushForm and SubmitSQLForm forms. Note The 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 non–row returning SQL statements Pocket PC Demonstration Client WalkthroughThe Pocket PC Demonstration Client sample was created with Microsoft Visual Studio .NET 2003 C# and .NET Compact Framework. Premium Choice: SQL Server 2000 Windows CE EditionSQL 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. Form ClassesThe following forms are used to implement the user interface:
MainFormFigure 9 illustrates the MainForm form. Figure 9 Access to all remote data access features Code WalkthroughNote All 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 = 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. OptionsFormFigure 10 illustrates the OptionsForm form. Figure 10 Remote data access settings Code WalkthroughAll 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 = 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 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, PullFormFigure 11 illustrates the PullForm form. Figure 11 Local DataTable name, SQL string, and tracking options Code WalkthroughThe 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; 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 PushFormFigure 13 illustrates the PushForm form. Figure 13 Pushing back changes to remote SQL server Code WalkthroughThe 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. SubmitSQLFormFigure 14 illustrates the SubmitSQLForm form. Figure 14 Executing non–row returning SQL statements Code WalkthroughThe 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; DataSet Server CE Code WalkthroughThe 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; Engine classThe 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. Remote Data Access ClassThe 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 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. Private method PullThe 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); Private method PushThe 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. Private Method submitSqlThe private method submitSql simply passes a valid non–row 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); DataSet Web Service Server Agent Code WalkthroughThe 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. DataSetServerAgent ClassThe 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. Private Method PullThe 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. Private Method PushThe 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 = 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 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] 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 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 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 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(), 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{} Private Method submitSqlThe private method submitSql is responsible for passing non–row 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 ConclusionThis 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. AppendixThe following appendix provides a detailed documentation of each class, method, and property in the client-side DataSet Server CE component. DataSetEngine MembersTable 3 Public Constructors
Table 4 Public Properties
Table 5 Public Methods
DataSetEngine.CreateDatabase Method Creates a new database. [C#] public void CreateDatabase(); DataSetRemoteDataAccess MembersTable 6 Public Constructors
Table 7 Public Properties
Table 8 Public Methods
DataSetRemoteDataAccess.Pull Method (String, String, String)Extracts data from a SQL Server database and stores it in a DataTable object in a DataSet XML file. [C#] public void Pull( string localTableName, string sqlSelectString, string oledbConnectionString ); ParameterslocalTableName The name of the DataTable object that will receive the extracted SQL Server records. An error occurs if the table already exists. sqlSelectString Any SELECT statements that specify which table, columns, and records to extract from the SQL Server database for storing in the DataSet XML file. oledbConnectionString The OLE DB connection string used when connecting to the SQL Server database. RemarksThe InternetLogin and InternetPassword properties must be specified if the virtual directory that contains the DataSet Web Service Server Agent is configured to use Basic authentication or Integrated Windows authentication. sqlSelectString controls which data is pulled from the SQL Server table. An error occurs if the resulting recordset contains an unsupported data type, such as timestamp. sqlSelectString can specify a WHERE clause to control which records are returned. For example, SELECT * FROM Customers WHERE State='CA' would retrieve only customers who live in California. If SQL Server Authentication is used, the user ID specified in the OLE DB connection string must have the authority to read the SQL Server table. If Windows Authentication is used by setting INTEGRATED SECURITY="SSPI" in the OLE DB connection string, the Internet user must have the authority to read the SQL Server table. The following identifies the Internet user, based on the authentication method:
DataSetRemoteDataAccess.Pull Method (String, String, String, RdaTrackOption)Extracts data from a SQL Server database and stores it in a DataTable object in a DataSet XML file. Specifies whether changes made to the pulled table should be tracked. [C#] public void Pull( string localTableName, string sqlSelectString, string oledbConnectionString, RdaTrackOption trackOption ); ParameterslocalTableName The name of the DataTable object that will receive the extracted SQL Server records. An error occurs if the table already exists. sqlSelectString Any valid SELECT statements that specify which table, columns, and records to extract from the SQL Server database for storing in the DataSet XML file. oledbConnectionString The OLE DB connection string used when connecting to the SQL Server database. trackOption Indicates whether DataSet Server CE tracks changes made to the pulled table and if the indexes that exist on the table being pulled are brought down to the device with the PRIMARY KEY constraints. RemarksThe InternetLogin and InternetPassword properties must be specified if the virtual directory that contains the DataSet Web Service Server Agent is configured to use Basic authentication or Integrated Windows authentication. sqlSelectString controls which data is pulled from the SQL Server table. An error occurs if the resulting recordset contains an unsupported data type, such as timestamp. sqlSelectString can specify a WHERE clause to control which records are returned. For example, SELECT * FROM Customers WHERE State='CA' would retrieve only customers who live in California. Valid trackOption values are described in Table 9. Table 9 trackOption values
If SQL Server Authentication is used, the user ID specified in the OLE DB connection string must have the authority to read the SQL Server table. If Windows Authentication is used by setting INTEGRATED SECURITY="SSPI" in the OLE DB connection string, the Internet user must have the authority to read the SQL Server table. The following identifies the Internet user based on the authentication method: When the IIS virtual directory is configured to use Anonymous access, the Internet user runs under the identity of the Internet Guest Account (IUSR_computername). If you configure another Windows user account as the Internet Guest Account, the Internet user runs under the identity of that account. When the IIS virtual directory is configured to use Basic authentication, the Internet user runs under the identity of the Windows user account for which the client supplied the Internet user name and password. When the IIS virtual directory is configured to use Integrated Windows authentication, the Internet user runs under the identity of the Windows user account for which the client supplied the Internet user name and password. DataSetRemoteDataAccess.Push Method (String, String)Transmits changes from a pulled tracked DataTable object in the DataSet XML file back to the originating SQL Server table. These changes are individually applied to the server in separate transactions. [C#] public void Push( string localTableName, string oledbConnectionString ); ParameterslocalTableName The name of the DataTable object that has the extracted SQL Server records. oledbConnectionString The OLE DB connection string used when connecting to the SQL Server database. RemarksApplications call the Push method to transmit changes from a pulled tracked DataTable object back to a SQL Server table. The application must have created the local DataTable object by calling the Pull method with trackOption set to TrackingOn. oledbConnectionString specifies all the connect information used while connecting to SQL Server. If SQL Server Authentication is used, the user ID specified in the OLE DB connection string must have the authority to read the SQL Server table. If Windows Authentication is used by setting INTEGRATED SECURITY="SSPI" in the OLE DB connection string, the Internet user must have the authority to read the SQL Server table. The following identifies the Internet user based on the authentication method:
DataSetRemoteDataAccess.Push Method (String, String, RdaBatchOption)Transmits changes from a pulled tracked DataTable object in the DataSet XML file back to the SQL Server table. These changes can be either individually applied to the server or batched together in a single transaction. [C#] public void Push( string localTableName, string oledbConnectionString, RdaBatchOption batchOption ); ParameterslocalTableName The name of the DataTable object that has the extracted SQL Server records. oledbConnectionString The OLE DB connection string used when connecting to the SQL Server database. batchOption Indicates whether the changes being sent back to the SQL Server table should be batched together in a single transaction or individually applied. RemarksApplications call the Push method to transmit changes from a pulled tracked DataTable object back to a SQL Server table. The application must have created the local DataTable object by calling the Pull method with trackOption set to TrackingOn. Valid batchOption values are described in Table 10. Table 10 batchOption values
oledbConnectionString specifies all the connect information used while connecting to SQL Server. If SQL Server Authentication is used, the user ID specified in the OLE DB connection string must have the authority to read the SQL Server table. If Windows Authentication is used by setting INTEGRATED SECURITY="SSPI" in the OLE DB connection string, the Internet user must have the authority to read the SQL Server table. The following identifies the Internet user based on the authentication method:
DataSetRemoteDataAccess.submitSql MethodSubmits SQL statements for execution on a database in Microsoft SQL Server on a remote server. [C#] public void submitSql( string sqlString, string oledbConnectionString ); ParameterssqlString Any SQL statement that does not return rows. oledbConnectionString The OLE DB connection string for the remote SQL Server database. RemarksThe InternetLogin and InternetPassword properties must be specified if the virtual directory that contains the DataSet Web Service Server Agent is configured to use Basic authentication or Integrated Windows authentication. If SQL Server Authentication is used, the user ID specified in the OLE DB connection string must have the authority to read the SQL Server table. If Windows Authentication is used by setting INTEGRATED SECURITY="SSPI" in the OLE DB connection string, the Internet user must have the authority to read the SQL Server table. The following identifies the Internet user based on the authentication method:
|