How to Create an ADO.NET* DataSet for a Mobilized Application

Submit New Article

December 9, 2008 11:00 PM PST


Challenge

Create an ADO.NET DataSet and populate it with data. ADO.NET differs from its predecessors because its DataSet object is designed primarily for disconnected operation. While retaining the ability to download data from a remote source, it has more comprehensive local capabilities than ADO and more advanced abilities to track locally made changes.

The DataSet is a complex object that contains several other interrelated objects, all of which are implemented in the System.Data namespace. The DataSet can be thought of as an in-memory representation of some tables extracted from a relational database. It offers many of the same features as a relational database, such as tables consisting of rows and columns, with key data items and constraints on the values that are allowed in particular columns. It also stores relationships between items in different tables.

Solution

Create and fill the DataTables in a DataSet in any of the following ways:

  • Create from scratch in memory
  • Create by downloading from an external data source
  • Create by loading a suitably formatted XML file
  • Create from a Web Service method

 

When you create a DataSet from scratch in memory, in outline, you build DataTable objects from DataColumns and DataRows and define any DataRelations and DataViews that are required.

To retrieve data from an external source to create the DataSet, use the objects in one of the .NET* Data Provider components. The following example shows the .NET Data Provider for Microsoft SQL Server* (which is implemented in the System.Data.SqlClient namespace). Here, a SqlDataAdapter object is used to fill a DataSet from a central SQL Server:

using System.Data.SqlClient;

dsTitles = new DataSet();

string sConn = @"Server=MyServer;database=pubs;Integrated Security=SSPI";

string sSQL = @"SELECT title_id,royalty FROM titles";

SqlDataAdapter da = new SqlDataAdapter(sSQL,sConn);

da.Fill(dsTitles);

 

Another way of transferring a DataSet to a machine elsewhere on the network, such as a mobile client, is to copy it using a Web Service. DataSets have a close affinity with XML, and the Web Service client classes in the .NET Framework handle the serialization to XML for transfer in the Web Service payload and the deserialization back to a DataSet object at its destination, without you having to write any special handling code.

One of the most powerful features of a DataSet is its ability to be bound to user-interface controls for the display of data. Update of the control with the data from the DataSet is automatic and completely local, and the Data Bound control reflects any changes due to local data updates, sorting, or filtering. For example, the following code binds a DataTable to a DataGrid control:

dataGrid1.DataSource = dsTitles.Tables[0];

 

This feature makes it easy to create forms-driven applications that allow a user to modify data, with all data updates stored in one or more DataSet objects.

This item may be most useful when used in conjunction with the following, separate items:

 

Source

Data Synchronization with the ADO.NET* DataSet