A DataSet holds a collection of DataTable objects. Each DataTable object represents the data that was retrieved using a SELECT statement or stored procedure execution. The data in a DataSet can be written out or read as XML. A DataSet also stores schema information, constraints, and relationships among multiple DataTable objects. Through a DataSet, you can add, edit, and delete data; therefore, a DataSet makes an ideal data transfer object in the .NET Framework, especially when you add the requirement to display the DataSet in controls on a form.

Because the .NET Framework already implements the DataSet, the remainder of this implementation strategy focuses on how to fill a DataSet from a data source and how to use the resulting DataSet in the Web Form.

Filling a DataSet from the Database

This example demonstrates how to fill the DataSet with the data that the sample application requires by using a database query. This includes the recording record as well as all the track records that are associated by the recordingId.

Assembler.cs

The Assembler class is a specialized instance of the Mapper pattern [Fowler03]. Its purpose is to isolate the DTO from the rest of the system. The following code example shows how the DTO is created from the database:

 

using System;
using System.Data;
using System.Data.SqlClient;

public class Assembler
{
   public static DataSet CreateRecordingDto(long id)
   {
      string selectCmd = 
         String.Format(
         "select * from recording where id = {0}",
         id);

      SqlConnection myConnection = 
         new SqlConnection(
         "server=(local);database=recordings;Trusted_Connection=yes");
      SqlDataAdapter myCommand = new SqlDataAdapter(selectCmd, 
         myConnection);

      DataSet ds = new DataSet();
      myCommand.Fill(ds, "recording");

      String trackSelect = 
         String.Format(
         "select * from Track where recordingId = {0} order by Id",
         id);

      SqlDataAdapter trackCommand = 
         new SqlDataAdapter(trackSelect, myConnection);
      trackCommand.Fill(ds, "track");

      ds.Relations.Add("RecordingTracks",
         ds.Tables["recording"].Columns["id"],
         ds.Tables["track"].Columns["recordingId"]);

      return ds;
   }
}
 

 

Note: The example shown here does not describe the only way to fill the DataSet. There are many ways to retrieve this data from the database. For example, you could use a stored procedure.

 

Using a DataSet in an ASP.NET Page

When using the .NET user interface controls (Web Forms or Windows Forms) a DataSet is a natural choice. For example, the sample application page uses two DataGrid controls, RecordingGrid and TrackGrid. Because you need to retrieve both the recording and the tracks for the recording, it makes sense to use a single DataSet that contains multiple tables.

Given the DataSet that was built by the Assembler class, this code displays how to assign a DataSet to the DataSource property of the two grid controls:

 

using System;
using System.Data;

public class RetrieveForm : System.Web.UI.Page
{
   private RecordingCatalog catalog = new RecordingCatalog();

   // 
 
   protected void Button1_Click(object sender, System.EventArgs e)
   {
      string stringId = TextBox1.Text;
      long id = Convert.ToInt64(stringId);

      DataSet ds = catalog.Get(id);
      RecordingGrid.DataSource = ds.Tables["recording"];
      RecordingGrid.DataBind();

      TrackGrid.DataSource = ds.Tables["track"];
      TrackGrid.DataBind();
   }
}
 

Tests

Because the DataSet is provided by the .NET Framework, you do not need to write tests to verify that it functions correctly. You could argue this point, but you should assume that classes provided by the Framework are innocent until proven guilty; therefore, what you need to test is the code that assembles the DataSet, which in this case is the Assembler class.

RecordingAssemblerFixture.cs

This fixture tests that the contents of the DataSet are filled and that the relationship between recording and track is defined correctly:

 

using NUnit.Framework;
using System.Data;

[TestFixture]
public class RecordingAssemblerFixture
{
   private DataSet ds;
   private DataTable recordingTable; 
   private DataRelation relationship;
   private DataRow[] trackRows; 

   [SetUp]
   public void Init()
   {
      ds = Assembler.CreateRecordingDto(1234);
      recordingTable = ds.Tables["recording"];
      relationship = recordingTable.ChildRelations[0];
      trackRows = recordingTable.Rows[0].GetChildRows(relationship);
   }

   [Test]
   public void RecordingCount()
   {
      Assert.Equals(1, recordingTable.Rows.Count);
   }

   [Test]
   public void RecordingTitle()
   {
      DataRow recording = recordingTable.Rows[0];
      string title = (string)recording["title"];
      Assert.Equals("Up", title.Trim());
   }


   [Test]
   public void RecordingTrackRelationship()
   {
      Assert.Equals(10, trackRows.Length);
   }

   [Test]
   public void TrackContent()
   {
      DataRow track = trackRows[0];

      string title = (string)track["title"];
      Assert.Equals("Darkness", title.Trim());
   }

   [Test]
   public void InvalidRecording()
   {
      DataSet ds = Assembler.CreateRecordingDto(-1);
      Assert.Equals(0, ds.Tables["recording"].Rows.Count);
      Assert.Equals(0, ds.Tables["track"].Rows.Count);
   }
}
 

Resulting Context

The following are the benefits and liabilities related to using a DataSet as a data transfer object:

Benefits

Liabilities

Related Patterns

For more information, see the following related patterns:

Acknowledgments

[Fowler03] Fowler, Martin. Patterns of Enterprise Application Architecture. Addison-Wesley, 2003.

[Powell03] Powell, Matt. "DataSets, Web Services, DiffGrams, Arrays, and Interoperability." MSDN Library, February, 2003. Available at: http://www.msdn.microsoft.com/library/default.asp?url=/library/en-us/dnservice/html/service02112003.asp.