DataSet Server CE: Database Connectivity for Windows Mobile-Based Devices

  • Article

 

Andy Sjostrom
businessanyplace.net

June 2004

Applies to:
   Windows Mobile-based devices
   Windows Mobile 2003 Second Edition software for Pocket PCs
   Windows Mobile 2003 Second Edition software for Smartphones
   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 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.

Contents

Introduction
DataSet Server CE Described
Smartphone Demonstration Client Walkthrough
Pocket PC Demonstration Client Walkthrough
DataSet Server CE Code Walkthrough
DataSet Web Service Server Agent Code Walkthrough
Conclusion
Appendix

Introduction

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 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:

  • DataSet Server CE client engine (DataSetServerCE.csdproj) — Class component that implements a RemoteDataAccess class with the same interfaces as SQL Server CE.
  • DataSet Web Service Server Agent (DataSetServerAgent.csdproj) — Server-side Web service that is similar to the SQL Server CE Server Agent. Manages communication with remote databases and implements the synchronization and tracking features.
  • Smartphone 2003 demonstration client (dssceClientSMP.csdproj) — A Windows Mobile-based Smartphone client that uses the DataSet Server CE client to execute Pull, Push, and submitSQL statements.
  • Pocket PC 2003 demonstration client (dssceClientPPC.csdproj) — A Windows Mobile-based Pocket PC client that uses the DataSet Server CE client to execute Pull, Push, and submitSQL statements.

The sample code illustrates the following key areas:

  • How to use DataSet and DataTable objects to manage local data.
  • How to pull data from a remote SQL Server to a Smartphone or Pocket PC running Windows Mobile. Again, SQL Server CE is the premiere choice for Pocket PC-based database applications. The only scenario where DataSet Server CE would be appropriate is when the amount of data stored and transferred is very low combined with very aggressive memory requirements (that is, low footprint). The footprint of DataSet Server CE is 12 kilobytes.
  • How to push tracked data changes back to the remote SQL Server.
  • How to pass non–row returning SQL statements, such as UPDATE and DELETE, to a remote SQL Server.
  • How to use the most key objects in the System.Data.OleDb namespace.
  • How to manage exception throughout a complete solution, including client, client component (DLL), Web service, and SQL Server.

DataSet Server CE Described

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-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
  • Pull
  • Push
  • submitSQL

X

X

X

X

X

X

DataSet Server can return number of affected rows.
Pull
  • TrackingOff
  • TrackingOn
  • TrackingOnWithIndexes
  • TrackingOffWithIndexes

X

X

X

X

X

X

(X)

(X)

Same as TrackingOn.

Same as TrackingOff but primary keys are also created.
Push
  • BatchingOff
  • BatchingOn

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 Client and Server Environments

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

ADO.NET DataSet

The .NET Compact Framework ADO.NET classes manage the data store on the Windows CE-based device.

DataSet Server CE

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.

DataSet Web Service Server Agent

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.

Supported Connectivity Solutions

DataSet Server CE works with the following network connectivity mechanisms as long as they are configured to support HTTP:

  • Ethernet
  • Wireless LANs
  • Wireless WANs

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:

  1. Download the source code (client component, server Web service, SQL to create the DataSetServerCETracking table, and demonstration clients).
  2. If you intend to use the Push feature, create the table DataSetServerCETracking by using the SQL file.
  3. Install the Web service either by using the DLL or by opening up the ASP.NET Web Service project and compiling it.
  4. Configure IIS with appropriate rights for the users you intend to use. This is not necessary if you will accept anonymous access and use user ID and password properties in the OleDb connection string; however, this is not a secure way to architect your infrastructure.
  5. Include the DataSet Server CE client component in your project, either as source code or by adding a reference to the compiled DLL.

DataSet Server CE Classes

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.

Click here for larger image

Figure 2 How the DataSet Server CE classes interact. Click the thumbnail for a larger image.

Smartphone Demonstration Client Walkthrough

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 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:

  • User interface — The Smartphone has a smaller screen and limited input mechanisms.
  • Local storage path — The Smartphone Demonstration Client illustrates how to retrieve a correct path to the nonvolatile flash memory by using the SHGetSpecialFolderPath function and the CSIDL_APPDATA constant.

The following section provides a user interface walkthrough. Detailed code walkthrough is found under the section titled "Pocket PC Demonstration Client Walkthrough."

MainForm

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 = @"https://server/DataSetServerAgent1/DataSetServerAgent.asmx";
Common.Common.Values.oledbConnectionString = "Provider=sqloledb; Integrated Security=SSPI; Data Source=(local); Initial Catalog=Northwind;";

OptionsForm

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);
}

PullForm, PushForm, and SubmitSQLForm

Figures 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 Walkthrough

The 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 Edition

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.

Form Classes

The following forms are used to implement the user interface:

  • MainForm — The Main form is the startup form. It loads and shows the XML data and XML schema found in the local DataSet XML file defined by LocalConnectionString.
  • OptionsForm — The Options form provides features to change settings such as LocalConnectionString, InternetLogin, InternetPassword, InternetUrl, and oledbConnectionString. The user can also create new local DataSet XML files from the options form. This is the only form that uses the DataSet Server CE Engine class. The class is used to create local DataSet XML files, just as the SQL Server CE Engine class is used to create local SQL Server CE databases.
  • PullForm — The Pull form is used to pull data from the remote SQL Server to the client. The form allows the user enter a local table name, implemented as a DataTable object, the sqlSelectString value used when querying the remote SQL Server and tracking option.
  • PushForm — The Push form allows the user to push back any changes made in a local table. The user can choose to have the DataSet Web Service Server Agent execute each change individually or all changes as a batch in one single transaction.
  • SubmitSQLForm — The SubmitSQLForm enables the user to execute non–row returning SQL statements in the remote SQL Server.

MainForm

Figure 9 illustrates the MainForm form.

Figure 9 Access to all remote data access features

Code Walkthrough

Note 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:

  • DataSetServerCE — A referenced DLL containing the DataSet Server CE Remote Data Access Engine class. This class implements all code that communicates with the DataSet Web Service Server Agent.
  • Common — A singleton class that holds defined Remote Data Access settings as common application values.
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 = @"https://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;
    }
}

OptionsForm

Figure 10 illustrates the OptionsForm form.

Figure 10 Remote data access settings

Code Walkthrough

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();
}

PullForm

Figure 11 illustrates the PullForm form.

Figure 11 Local DataTable name, SQL string, and tracking options

Code Walkthrough

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:

  • Track the changes made to the local data.
  • Include primary key constraints with the schema used to create the DataTable object.

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:

  • A unique ID (GUID)
  • The sqlSelectString value used to pull the data
  • The actual DataSet XML data and schema used to create the DataTable object

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

PushForm

Figure 13 illustrates the PushForm form.

Figure 13 Pushing back changes to remote SQL server

Code Walkthrough

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;
    }

SubmitSQLForm

Figure 14 illustrates the SubmitSQLForm form.

Figure 14 Executing non–row returning SQL statements

Code Walkthrough

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;
    }
}

DataSet Server CE Code Walkthrough

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;

Engine class

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 {}
}

Remote Data Access Class

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.

Private method Pull

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.

  • The DataSet file is opened using XmlReadMode.ReadSchema.
  • The Server Agent is instantiated and its InternetUrl property is set.
  • If an InternetLogin property has been set, a NetworkCredential class is created and used when calling the Web service. The Web service then lets ASP.NET impersonate the specified user. If integrated security is used when connecting to the SQL Server, the credentials are passed to the database.
  • The RdaTrackOption enumeration is defined in the DataSet Server CE class. The same enumeration exists in the server-side DataSet Web Service Server Agent. The client side enumeration is mapped to the server-side enumeration.
  • The DataTable object returned will have a unique trackingID (GUID) value added to its ExtendedProperties collection if tracking is set to on. The trackingID value is stored in the SQL Server table DataSetServerCETracking along with the original DataTable table that is returned to the client. This enables the Server Agent to implement the tracking logic when the Push method is called.
  • The call is made to the Server Agent, and the returned data is added as a DataTable object to the local DataSet XML file. The DataTable object is also renamed to the specified TableName value.
  • Finally, the DataSet XML file is saved.
  • Any exceptions that might occur are thrown up to the caller.
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 {}
}

Private method Push

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:

  1. Get DataTable object from local DataSet object.
  2. Remove DataTable object from local DataSet object.
  3. Add DataTable object to DataSet object that will be passed to the Server Agent.
  4. Do work and pass it to the Server Agent.
  5. Reject changes to local DataSet object. This will return the DataTable object to the local DataSet object. Exception handling will also return the DataTable object to the local DataSet object.
        // 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 server
        dssa.Push(dsClient, oledbConnectionString, batchOptionServer);

    }
    catch(Exception Ex)
    {
        // Throw exception to caller.
        throw Ex;
    }
    finally
    {
        // Reject changes to dsAll.
        dsAll.RejectChanges();
    }
}

Private Method submitSql

The 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 Walkthrough

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.

DataSetServerAgent Class

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.

Private Method Pull

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:

  • A new DataSet object is created and named DataSetServerCE.
  • An OleDbConnection object is created using the oledbConnectionString value.
  • Fills the DataSet object with data using the sqlSelectString value.
  • Adds primary key constraints, using the OleDbDataAdapter.FillSchema method, to the DataTable object if tracking or indexes are not set to off.
  • If tracking is set to on, create a new unique trackingID value and add it to the DataTable object's ExtendedProperties collection. Store the trackingID value and the XML data and schema to the SQL Server table DataSetServerCETracking for future push management.
  • Return the DataSet object to the caller.
  • Return any exception to the caller.
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 DataSet
        return ds;
    }
    catch(Exception Ex)
    {
        // Throw exception to caller.
        throw Ex;
    }
    finally{}
}

Private Method Push

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:

  • UPDATE and DELETE statements use primary key columns in their WHERE clauses.
  • An INSERT statement uses primary key columns in the column list.

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{}

Private Method submitSql

The 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 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{}
}

Conclusion

This article provided information, source code, and reusable components to address database connectivity needs in application development Windows Mobile-based devices. 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 software and a remote database server.

Appendix

The following appendix provides a detailed documentation of each class, method, and property in the client-side DataSet Server CE component.

DataSetEngine Members

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();

DataSetRemoteDataAccess Members

Table 6 Public Constructors

DataSetRemoteDataAccess Overloaded. Initializes a new instance of the DataSetRemoteDataAccess class.

Table 7 Public Properties

InternetLogin Specifies the IIS login name used when connecting to the DataSet Web Service Server Agent.
InternetPassword Specifies the IIS password used when connecting to the DataSet Web Service Server Agent.
InternetUrl Specifies the URL used to connect to the DataSet Web Service Server Agent. This property must be specified.
LocalConnectionString Specifies the path to the local DataSet XML file.

Table 8 Public Methods

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.
Pull Overloaded.
Push Overloaded.
submitSql Submits SQL statements for execution on a database in Microsoft SQL Server on a remote server.
ToString (inherited from Object) Returns a String that represents the current Object.

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
);

Parameters

localTableName

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.

Remarks

The 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:

  • 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.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
);

Parameters

localTableName

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.

Remarks

The 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

trackOption values Comment
TrackingOff Tracking disabled. Returned DataTable object will not contain constraints or primary keys.
TrackingOn Tracking enabled. Returned DataTable object will contain primary key constraints.
TrackingOnWithIndexes Tracking enabled. Returned DataTable object will contain primary key constraints. Same as TrackingOn.
TrackingOffWithIndexes Tracking disabled. Returned DataTable object will contain primary key constraints.

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
);

Parameters

localTableName

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.

Remarks

Applications 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:

  • 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, 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
);

Parameters

localTableName

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.

Remarks

Applications 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

batchOption value Comment
BatchingOff All changes being sent back will be individually applied.
BatchingOn All changes being sent back will be batched together in a single transaction.

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:

  • 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.submitSql Method

Submits SQL statements for execution on a database in Microsoft SQL Server on a remote server.

[C#]

public void submitSql(
    string sqlString,
    string oledbConnectionString
);

Parameters

sqlString

Any SQL statement that does not return rows.

oledbConnectionString

The OLE DB connection string for the remote SQL Server database.

Remarks

The 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:

  • 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.