DataSet
This is a collection of DataTables
. We use the DataSet
type to store many DataTables
in a single collection. Conceptually, the DataSet
acts as a set of DataTable
instances.
DataSet
simplifies programs that use many DataTables
. To effectively use the DataSet
, you will need to have some DataTables
handy. We begin by creating some.
In this program, we create 2 DataTables
. One stores 2 rows of patient information. And the second stores 2 rows of medication information.
DataSet
with the DataSet
constructor. Then we add the 2 DataTables
to the DataSet
instance. Finally we print the XML.using System; using System.Data; class Program { static void Main() { // Create 2 DataTable instances. DataTable table1 = new DataTable("patients"); table1.Columns.Add("name"); table1.Columns.Add("id"); table1.Rows.Add("sam", 1); table1.Rows.Add("mark", 2); DataTable table2 = new DataTable("medications"); table2.Columns.Add("id"); table2.Columns.Add("medication"); table2.Rows.Add(1, "atenolol"); table2.Rows.Add(2, "amoxicillin"); // Create a DataSet and put both tables in it. DataSet set = new DataSet("office"); set.Tables.Add(table1); set.Tables.Add(table2); // Visualize DataSet. Console.WriteLine(set.GetXml()); } }<office> <patients> <name>sam</name> <id>1</id> </patients> <patients> <name>mark</name> <id>2</id> </patients> <medications> <id>1</id> <medication>atenolol</medication> </medications> <medications> <id>2</id> <medication>amoxicillin</medication> </medications> </office>
Dispose
, using DataSet
You can put your DataSet
in a using block. This ensures the Dispose
method is called as soon as possible when the DataSet
is no longer being used.
using System.Data; class Program { static void Main() { // Create a DataSet in using statement. using (DataSet set = new DataSet("office")) { // Put code that adds stuff to DataSet here. // ... The DataSet will be cleaned up outside the block. } } }
Namespace
, PrefixOne important use of the DataSet
is to encode data in XML format. Often, XML data needs to have an XML namespace of a tag element prefix.
DataSet
provides the Namespace
and Prefix properties to specify this.Namespace
and the Prefix, and you can see them appear in the output.using System; using System.Data; class Program { static void Main() { DataTable table1 = new DataTable("patients"); table1.Columns.Add("name"); table1.Columns.Add("id"); table1.Rows.Add("sam", 1); // Create a DataSet. DataSet set = new DataSet("office"); set.Tables.Add(table1); set.Namespace = "y"; set.Prefix = "x"; // Visualize DataSet. Console.WriteLine(set.GetXml()); } }<x:office xmlns:x="y"> <patients xmlns="y"> <name>sam</name> <id>1</id> </patients> </x:office>
DataSetName
Every DataSet
can have a name specified. Usually, it is easiest to specify this inside the DataSet
constructor. But you don't always know the name at this time.
DataSetName
property. You can also read the DataSetName
property.using System; using System.Data; class Program { static void Main() { // Create a DataSet. DataSet set = new DataSet("office"); // Show original name. Console.WriteLine(set.DataSetName); // Change its name. set.DataSetName = "unknown"; Console.WriteLine(set.DataSetName); } }office unknown
DataSet
has a Clear method that clears all the DataTables
in the set. It also provides a Copy method that will make a deep copy of all the DataTables
in the set.
using System; using System.Data; class Program { static void Main() { DataTable table1 = new DataTable("patients"); table1.Columns.Add("name"); table1.Columns.Add("id"); table1.Rows.Add("sam", 1); DataTable table2 = new DataTable("medications"); table2.Columns.Add("id"); table2.Columns.Add("medication"); table2.Rows.Add(1, "atenolol"); // Create a DataSet. DataSet set = new DataSet("office"); set.Tables.Add(table1); set.Tables.Add(table2); // Copy the DataSet. DataSet copy = set.Copy(); // Clear the first DataSet. set.Clear(); // Show contents. Console.WriteLine("set: {0}", set.GetXml()); Console.WriteLine("copy: {0}", copy.GetXml()); } }set: <office /> copy: <office> <patients> <name>sam</name> <id>1</id> </patients> <medications> <id>1</id> <medication>atenolol</medication> </medications> </office>
This property returns an instance of a DataTableCollection
. You can use the Count
property and indexer on this sub-collection to access all the individual tables.
DataTable
from the Tables collection with its name. We use set.Tables
["medications"] to show this behavior.using System; using System.Data; class Program { static void Main() { DataTable table1 = new DataTable("patients"); table1.Columns.Add("name"); table1.Columns.Add("id"); table1.Rows.Add("sam", 1); DataTable table2 = new DataTable("medications"); table2.Columns.Add("id"); table2.Columns.Add("medication"); table2.Rows.Add(1, "atenolol"); table2.Rows.Add(6, "trifluoperazine"); // Create a DataSet. DataSet set = new DataSet("office"); set.Tables.Add(table1); set.Tables.Add(table2); // Loop over DataTables in DataSet. DataTableCollection collection = set.Tables; for (int i = 0; i < collection.Count; i++) { DataTable table = collection[i]; Console.WriteLine("{0}: {1}", i, table.TableName); } // Write name of first table. Console.WriteLine("x: {0}", set.Tables[0].TableName); // Write row count of medications table. Console.WriteLine("y: {0}", set.Tables["medications"].Rows.Count); } }0: patients 1: medications x: patients y: 2
GetXml
It is possible to convert a DataSet
to a string
representation in XML syntax. The GetXml()
method on the DataSet
instance is ideal for this.
DataSet
instance with the name "Hospital".DataTable
to this set. This DataTable
has four rows and five columns.GetXml
instance method is invoked on the DataSet
, and the result is printed to the screen.DataSet
, DataTable
and DataColumns
.using System; using System.Data; class Program { static DataTable Table() { DataTable table = new DataTable("Prescription"); table.Columns.Add("Dosage", typeof(int)); table.Columns.Add("Drug", typeof(string)); table.Columns.Add("Patient", typeof(string)); table.Columns.Add("Date", typeof(DateTime)); table.Rows.Add(25, "Indocin", "David", DateTime.Now); table.Rows.Add(50, "Enebrel", "Sam", DateTime.Now); table.Rows.Add(10, "Hydralazine", "Christoff", DateTime.Now); table.Rows.Add(21, "Combivent", "Janet", DateTime.Now); table.Rows.Add(100, "Dilantin", "Melanie", DateTime.Now); return table; } static void Main() { // Create DataSet instance. DataSet set = new DataSet("Hospital"); // Add new table. set.Tables.Add(Table()); // Write xml data. Console.WriteLine(set.GetXml()); } }<Hospital> <Prescription> <Dosage>25</Dosage> <Drug>Indocin</Drug> <Patient>David</Patient> <Date>2010-06-17T08:39:41.0879713-06:00</Date> </Prescription> <Prescription> <Dosage>50</Dosage> <Drug>Enebrel</Drug> <Patient>Sam</Patient> <Date>2010-06-17T08:39:41.0879713-06:00</Date> </Prescription> <Prescription> <Dosage>10</Dosage> <Drug>Hydralazine</Drug> <Patient>Christoff</Patient> <Date>2010-06-17T08:39:41.0879713-06:00</Date> </Prescription> <Prescription> <Dosage>21</Dosage> <Drug>Combivent</Drug> <Patient>Janet</Patient> <Date>2010-06-17T08:39:41.0879713-06:00</Date> </Prescription> <Prescription> <Dosage>100</Dosage> <Drug>Dilantin</Drug> <Patient>Melanie</Patient> <Date>2010-06-17T08:39:41.0879713-06:00</Date> </Prescription> </Hospital>
GetXmlSchema
An XML schema indicates the structure of an XML document. GetXmlSchema()
on the DataSet
type generates an XML schema from the known structure encoded in your DataSet
.
DataSet
and then add a DataTable
instance to it. We call the GetXmlSchema
instance method, which reveals the XML schema.using System; using System.Data; class Program { static DataTable Table() { DataTable table = new DataTable("Prescription"); table.Columns.Add("Dosage", typeof(int)); table.Columns.Add("Drug", typeof(string)); table.Columns.Add("Patient", typeof(string)); table.Columns.Add("Date", typeof(DateTime)); table.Rows.Add(25, "Indocin", "David", DateTime.Now); table.Rows.Add(50, "Enebrel", "Sam", DateTime.Now); table.Rows.Add(10, "Hydralazine", "Christoff", DateTime.Now); table.Rows.Add(21, "Combivent", "Janet", DateTime.Now); table.Rows.Add(100, "Dilantin", "Melanie", DateTime.Now); return table; } static void Main() { // Create DataSet instance. DataSet set = new DataSet("Hospital"); // Add new table. set.Tables.Add(Table()); // Write xml schema data. Console.WriteLine(set.GetXmlSchema()); } }<?xml version="1.0" encoding="utf-16"?> <xs:schema id="Hospital" xmlns="" xmlns:xs="" xmlns:msdata=""> <xs:element name="Hospital" msdata:IsDataSet="true" msdata:UseCurrentLocale="true"> <xs:complexType> <xs:choice minOccurs="0" maxOccurs="unbounded"> <xs:element name="Prescription"> <xs:complexType> <xs:sequence> <xs:element name="Dosage" type="xs:int" minOccurs="0" /> <xs:element name="Drug" type="xs:string" minOccurs="0" /> <xs:element name="Patient" type="xs:string" minOccurs="0" /> <xs:element name="Date" type="xs:dateTime" minOccurs="0" /> </xs:sequence> </xs:complexType> </xs:element> </xs:choice> </xs:complexType> </xs:element> </xs:schema>
What can we do with an XML schema? The DataSet
type provides 2 methods, InferXmlSchema
and ReadXmlSchema
, which can load a file we specify that contains the schema data.
DataSet
with all the appropriate constraints in it. After this, we could use ReadXml
on an XML file of the data itself.This property is a way to specify many DataRelations
on the DataTables
. A DataRelation
indicates which tables are dependent on other tables (sub-tables).
CaseSensitive
The DataSet
provides the CaseSensitive
property. The default value is False. There may be cases where you want string
lookups on your DataSet
to be case-sensitive.
DataSet
collects many DataTables
in a single place. It provides useful methods for acting upon those DataTables
. Thus DataSet
introduces a key abstraction.