Using DataSets

DataSets are similar to traditional ADO recordsets. They are the client's representation of the database and are used to manipulate data on the client. In ADO.NET, DataSets are always disconnected, which means that they are not concerned with the actual source of the data. In this environment, you use a DataAdapter to connect the DataSet to the actual data source.

The traditional use of a DataSet follows these steps:

Create a DataAdapter.
Create a DataSet.
Use DataAdapter.Fill to populate the DataSet.

A DataSet contains a Tables property, which returns a collection of tables ( DataTableCollection ). This allows a DataSet to have data consisting of more than one connection from more than one source.

Using Multiple-Table DataSets Through DataTables

A DataTable represents a single table in a DataSet. This is new in that DataSets can now contain multiple tables from multiple sources. The following example demonstrates using multiple-table DataSets.

Code Example: Using Mutiple-Table DataSets

using System;
using System.Data;
using System.Data.OleDb;

namespace Client.Chapter_13___ADO.NET
{
          class UsingMultiTabledDatasets
          {
                static void Main(string[] args)
                {
                      OleDbConnection MyConnection =
                        new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;"
                        + " Data Source = c:\MyAccessDB.mdb");
                      OleDbDataAdapter MyAdapter =
                        new OleDbDataAdapter("SELECT Column1, Column2, Column3"
                        + " FROM MyTable", MyConnection);
                      DataSet MyDataSet = new DataSet();

                      MyAdapter.Fill(MyDataSet, "MyTable");
                      foreach (DataTable MyTable in MyDataSet.Tables)
                      {
                             foreach (DataColumn MyColumn in MyTable.Columns)
                             {
                                  foreach (DataRow MyRow in MyTable.Rows)
                                  {
                                  }
                             }
                      }
                }
          }
}

Updating a Data Source Quickly and Simply

As noted earlier, you must use a DataAdapter to connect a DataSet to a data source. To update a data source, you need to create a new DataAdapter. The following example demonstrates how to update a data source.

Code Example: Updating a Data Source

using System;
using System.Data;
using System.Data.OleDb;

namespace Client.Chapter_13___ADO.NET
{
          class UpdatingADataSource
          {
                static void Main(string[] args)
                {
                       OleDbConnection MyConnection =
                       new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;"
                             + " Data Source = c:\MyAccessDB.mdb");
                       OleDbDataAdapter MyAdapter =
                       new OleDbDataAdapter("SELECT Column1, Column2, Column3"
                            + " FROM MyTable", MyConnection);
                       DataSet MyDataSet = new DataSet();
                       MyAdapter.Fill(MyDataSet, "MyTable");
                       MyDataSet.Tables[0].Rows[3]["Column3"] = "Test";
                       OleDbCommandBuilder MyBuilder =
                          new OleDbCommandBuilder(MyAdapter);
                       MyAdapter.Update(MyDataSet.Tables[0]);
                }
          }
}

Using Persisted DataSets

The standard persistence format of a DataSet is XML, and thus a DataSet can be persisted to an XML file, as follows:

MyDataSet.WriteXml("@c:\MyDatSet.xml");

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

namespace Client.Chapter_13___ADO.NET
{
          class UsingDatasets
          {
                static void Main(string[] args)
                {
                       SqlConnection MyConnection =
                         new SqlConnection(@"Data Source=(local); Initial Catalog"
                          + "= CaseManager; Integrated Security=true");
                       SqlDataAdapter MyAdapter =
                       new SqlDataAdapter("SELECT * FROM CaseInfo", MyConnection);
                       DataSet MyDataSet = new DataSet();
                       MyAdapter.Fill(MyDataSet, "MyTable");
                       MyDataSet.WriteXml(@"c:\MyDatSet.xml");
                }
          }
}

The following examples demonstrate persisting a data set to an XML file and reading XML into a data set.

Code Example: Persisting a DataSet to an XML File

using System;
using System.Data;
using System.Data.OleDb;

namespace Client.Chapter_13___ADO.NET
{
          class PersistingADatasetToAnXMLFile
          {
                 static void Main(string[] args)
                 {
                        OleDbConnection MyConnection =
                        new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;"
                                + " Data Source = c:\MyAccessDB.mdb");
                        OleDbDataAdapter MyAdapter =
                        new OleDbDataAdapter("SELECT Column1, Column2, Column3"
                               + " FROM MyTable", MyConnection);
                        DataSet MyDataSet = new DataSet();
                        MyAdapter.Fill(MyDataSet, "MyTable");
                        MyDataSet.WriteXml(@"c:\MyDatSet.xml");
                 }
          }
}

Code Example: Reading XML into a DataSet

using System;
using System.IO;
using System.Data;

namespace Client.Chapter_13___ADO.NET
{
          class ReadingAnXMLFileIntoADataset
          {
                static void Main(string[] args)
                {
                       string MyXMLDoc = @"<?xml version=’1.0’>?"
                                      + @"<title> MyExample</title>";
                       StringReader MyStringReader = new StringReader(MyXMLDoc);
                       DataSet MyDataSet = new DataSet();
                       MyDataSet.ReadXml(MyStringReader);
                }
          }
}

Creating a Strong-Typed DataSet Using the IDE

To create a strong-typed DataSet, using Visual Studio, follow these steps:

Create a new project.
Use drag-and-drop to create a DataAdapter. Complete the dialog boxes to establish a connection.
Access the database using a stored procedure or a SQL query.
Use the Generate DataSet dialog box to create the DataSet. This dialog box can be accessed via the Data menu.
Save the XSD and .cs files that are generated.
Copy the TableMappings from the Windows Form's InitializeComponents method to the Clipboard.
Create or open the desired project.
Add the XSD and DataSet.cs files you saved in step 5.
Paste the TableMappings into the Form's constructor.
Write your DataAccess code.
Declare the DataSet by referencing the XSD file that you generated.