Programming with Microsoft Visual C++.NET 6ed [Electronic resources] نسخه متنی

ADO.NET Datasets

A forward-reading stream can be useful for collecting moderate amounts of data, but it's not always the best data access method—especially in high-volume situations or when you need random access to the contents of a database. ADO.NET's dataset answers these needs. The ADO.NET DataSet class manages a collection of in-memory tables that represent a result set. The dataset is similar to the classic ADO disconnected recordset, but provides more functionality.

The dataset basically represents a snapshot of a result set (or possibly several result sets). A dataset is not associated with a specific physical database. It contains one or more instances of the DataTable class, and each table can be from a different data source. The dataset can acquire data tables from several sources, including physical databases and XML files. You can also create and fill data tables and data sets programmatically, as you'll see in the following section. Figure 35-2 shows the architecture of the ADO.NET DataSet.

Figure 35-2: The architecture of the ADO.NET DataSet.

To access a dataset's tables, columns, and rows, you use a fairly regular collection syntax involving iterative calls to the get_Item method of the DataTableCollection, the DataColumnCollection, and the DataRowCollection. You can access any part of a dataset either by name or by ordinal.

Using the Data Adapter to Populate Datasets

ADO.NET includes classes that implement IDataAdapter, which is useful for loading datasets. The data adapter classes encapsulate a connection object and a set of command objects and take on the task of connecting to the database and constructing a dataset for you. The following listing shows how to use the SqlDataAdapter to fill a dataset.

void UseDataAdapter()
{
SqlDataAdapter* da = new SqlDataAdapter(
"select * from Employees", 
"server=localhost;uid=sa;database=CompanyDB");
DataSet* ds = new DataSet();
da->Fill(ds, "Employees");
}

This listing assumes that a database named CompanyDB is available through SQL Server and that there's a table named Employees. The SqlDataAdapter will select every row and every column from the Employees table and construct a dataset. The dataset will include a single table named Employees. Once the dataset has been loaded, you can easily enumerate the tables within the dataset and examine the contents of each row, as shown in the following listing:

void EnumDataSet(DataSet* ds)
{    
Console::WriteLine("Enumerating Tables in DataSet:");
for(int i = 0; i < ds->Tables->Count; i++)
{
Console::Write("Table Name: ");
DataTable* dt = ds->Tables->get_Item(i);
Console::WriteLine(dt->TableName);
for(int j = 0; j < dt->Rows->Count; j++)
{
DataRow* dr = dt->Rows->get_Item(j);
Console::Write("Column 1: ");
Console::Write(dr->get_Item(0)->ToString());
Console::Write("  Column 2: ");
Console::WriteLine(dr->get_Item(1)->ToString());
}
}
}

Data adapters aren't the only way to construct datasets. You can also create datasets in memory.

Creating In-Memory Datasets

Most of the data your application will work with probably live in a database somewhere, but sometimes it's useful to cook up datasets on the fly. For example, perhaps you have a small test scenario or you don't have a database available. Datasets are simply in-memory instances of table, row, and column collections, and you can construct them manually. The following listing shows how to create an in-memory dataset manually without going out to a database:

DataSet* ManufactureDataSet()
{
DataSet* ds;
ds = new DataSet();
DataTable* dt = new DataTable();
String* strType = S"Name";
Int32 n = 0; 
__box Int32* int32Type = __box(n);
dt->Columns->Add("Name", strType->GetType());
dt->Columns->Add("DeptID", int32Type->GetType());
ds->Tables->Add(dt);
DataRow* dr = dt->NewRow();
dr->set_Item(0, S"George Shepherd");
n = 132; 
int32Type = __box(n);
dr->set_Item(1, int32Type);
dt->Rows->Add(dr);
dr = dt->NewRow();
dr->set_Item(0, S"Helge Hoeing");
n = 132; 
int32Type = __box(n);
dr->set_Item(1, int32Type);
dt->Rows->Add(dr);
dr = dt->NewRow();
dr->set_Item(0, S"Lisa Jacobson");
n = 115; 
int32Type = __box(n);
dr->set_Item(1, int32Type);
dt->Rows->Add(dr);
dr = dt->NewRow();
dr->set_Item(0, S"Brian Burk");
n = 115; 
int32Type = __box(n);
dr->set_Item(1, int32Type);
dt->Rows->Add(dr);
dr = dt->NewRow();
dr->set_Item(0, S"Michael Allen");
n = 115; 
int32Type = __box(n);
dr->set_Item(1, int32Type);
dt->Rows->Add(dr);
return ds;
}

The code constructs a new dataset in the normal fashion. At this point, the dataset is empty. Datasets are collections of data tables, and the first thing the dataset needs is to have a table added to it. The data table is constructed in the normal fashion and added to the dataset's table collection. Once the table is added to the dataset, the code adds two columns to the table. These data columns are constructed with a column name and a data type. Managed C++ is finicky about how GetType is called (even though it is a static method). The previous code listing works around the problem by creating actual instances of the types that are supposed to make up the column. The rest of the code adds several rows to the table in the dataset.

Once the dataset has been constructed, you can march through its contents—just as you would any other dataset, as shown in the following listing:

void UseDataSet()
{
DataSet* ds = ManufactureDataSet();
Console::WriteLine("Enumerating Tables in DataSet:");
for(int i = 0; i < ds->Tables->Count; i++)
{
Console::Write("Table Name: ");
DataTable* dt = ds->Tables->get_Item(i);
Console::WriteLine(dt->TableName);
for(int j = 0; j < dt->Rows->Count; j++)
{
DataRow* dr = dt->Rows->get_Item(j);
Console::Write("Column 1: ");
Console::Write(dr->get_Item(0)->ToString());
Console::Write("  Column 2: ");
Console::WriteLine(dr->get_Item(1)->ToString());
}
}
}

Writing XML from Datasets

Fetching data from a database is one of the most common operations within modern applications, but at times you'll need to export your data for consumption by other applications. In the late 1980s and the early 1990s, the way to import and export data was through comma-delimited files. Comma-delimited files expressed their contents as lines of text whose embedded elements were separated by commas.

But soon comma-delimited files were out and XML files were in. XML has become the standard for expressing data in an interoperable format. ADO.NET's dataset marshals (serializes) as well-formed XML files and optionally as XML schema. To export a dataset's contents as XML, you simply call the DataSet's WriteXML method, as shown in the following listing:

void UseDataSet()
{
DataSet* ds = ManufactureDataSet();
ds->WriteXml(S"C:\\CompanyDB.xml", XmlWriteMode::IgnoreSchema);
ds->WriteXmlSchema(S"C:\\CompanyDB.xsd");
}

Dumping a dataset as XML yields the following XML file, given the in-memory dataset constructed in the previous section:

<?xml version="1.0" standalone="yes"?>
<NewDataSet>
<Table1>
<Name>George Shepherd</Name>
<DeptID>132</DeptID>
</Table1>
<Table1>
<Name>Helge Hoeing</Name>
<DeptID>132</DeptID>
</Table1>
<Table1>
<Name>Lisa Jacobson</Name>
<DeptID>115</DeptID>
</Table1>
<Table1>
<Name>Brian Burk</Name>
<DeptID>115</DeptID>
</Table1>
<Table1>
<Name>Michael Allen</Name>
<DeptID>115</DeptID>
</Table1>
</NewDataSet>

In addition to writing out XML files, the ADO.NET DataSet also serializes the schema so you (or some data consumer) can understand what types the tables in the dataset are composed of.

<?xml version="1.0" standalone="yes"?>
<xs:schema id="NewDataSet" xmlns="   
xmlns:xs="http://www.w3.org/2001/XMLSchema" 
xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xs:element name="NewDataSet" msdata:IsDataSet="true">
<xs:complexType>
<xs:choice maxOccurs="unbounded">
<xs:element name="Table1">
<xs:complexType>
<xs:sequence>
<xs:element name="Name" 
type="xs:string" minOccurs="0" />
<xs:element name="DeptID" 
type="xs:int" minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>

We've only scratched the surface of ADO.NET in this chapter. ADO.NET offers tremendous data access capabilities for any application written for the .NET platform. For a complete discussion of ADO.NET, see Microsoft ADO.NET (Core Reference) by David Sceppa (Microsoft Press, 2002).