Introduction to DataSets and working with XML files






4.50/5 (16 votes)
Jul 18, 2002
8 min read

357972

6616
This article gives you an introduction to .NET's DataSets and how you can use them with XML files
Introduction
This article gives you an introduction to using DataSets and how to use them with XML files. Working with them really makes your life easier when you want to share data from a data source and you are thinking of XML.
System Requirements
To compile the solution you need to have Microsoft Visual Studio .NET installed. To run any of the client executable you need to have the .NET framework installed.
The sample provided is a simple application written in C#. It displays a form with a DataGrid
. By default,
the application connects to a SQL Server, binds the Northwind database and fetches some parent records from table
Customers and some child records from table Orders. By default, the application assumes that you have
an instance of SQL Server installed on your machine. If this is not the case, then you must manually modify the
connection string and then rebuild the sample.
Then, you can save the dataset to XML file. Schema information is also saved.
What is a DataSet?
A DataSet
object is a very generic object that can store cached data from a database in a very efficient
way. It is member of the System::Data
namespace.
One obvious question is: When to use a dataset? Well, the answer is: it depends. You should consider that a dataset is a collection of in-memory cached data. So it's good to use datasets when:
- You are working with multiple separated tables or tables from different data sources.
- You are exchanging data with another application such as a Web Service.
- You perform extensive processing with the records in the database. If you use a SQL query every time you need to change something, processing each record may result in connection being held open which may affect performance.
- You want to perform XML/XSLT operations on the data.
You should not use a dataset if:
- You are using Web Forms in your application because Web Forms and their controls are recreated each time a page is requested by the client. Thus, creating, filling and destroying a dataset each time will be inefficient unless you plan to cache it between roundtrips.
A DataSet
has a DataTableCollection
object as a member that is nothing but a collection
of DataTable
objects. You declare a DataSet
object and add tables to it like this
(in Managed C++):
// Declare the DataSet object DataSet* MyDataSet = new DataSet ("MyDataSet"); // give it a name here // Add two tables to it // - add a Table named Table1 DataTable* Table1 = MyDataSet->Tables->Add ("Table1"); // - add a Table named Table2 DataTable* Table2 = MyDataSet->Tables->Add ("Table2");
You can refer the tables later either using the pointers returned by the Add
method or like this:
DataTable* table = MyDataSet->Tables->Item[0]; // or DataTable* table = MyDataSet->Tables->Item["Table1"]; // isn't this indexation cool?A
DataTable
object has two important members: Rows
and Columns
.
Rows
is a DataRowCollection
object and Columns
is a
DataColumnCollection
. DataRowCollection
is a collection of DataRow
objects
and DataColumnCollection
is a collection of DataColumn
objects. I am sure you can easily
figure out what these objects represent. :)
Adding data to a data set is straight-forward:
// adding data to the first table in the DataSet DataTable* Table1 = MyDataSet->Tables->Item[0]; // add two columns to the table Table1->Columns->Add ("Column1"); Table2->Columns->Add ("Column2"); // get the collection of rows DataRowCollection* drc = Table1->Rows; // create a vector of Objects that we will insert in current row Object* obj[] = new Object* [2]; obj[0] = new String ("Item 1"); obj[1] = new String ("Item 2"); // add them to the dataset drc->Add (obj);
If you want to specify the data type of a particular column you should use the DataColumn::DataType
property. You can set any of the following data types: Boolean
, Byte
, Char
,
DateTime
, Decimal
, Double
, Int16
, Int32
,
Int64
, SByte
, Single
, String
, TimeSpan
,
UInt16
, UInt32
, UInt64
.
That's it! Well, that's all you have to do if you want to build up your data set manually. This is not how it is done if you want to connect to a real data source.
Binding a Database
To connect to a database server (such as SQL Server) and fill a dataset from there you need three additonal
objects: a Connection
, a DataCommand
and a DataAdapter
object.
The Connection
object is used to connect to the database object. You must provide a connection string to
it. Also, if your application is using transactions, you must attach a transaction object to the connection.
The DataCommand
object is used to sending commands to the database server. In includes four
System::String
objects named SelectCommand
, InsertCommand
,
UpdateCommand
and DeleteCommand
. I believe it is obvious what these string objects
represent, nothing else but the four basic SQL operations.
The DataAdapter
object is the object that does all the magic work. It fills the DataSet
with data
from the database and updates data from the DataSet
to the database.
You may now wonder what are the classes that correspond to the objects described above. Well, Microsoft have
prepared two sets of classes - you can't say life is getting much easier in .NET can you? :). The first set is
based on OLE DB and is part of the System::Data::OleDb
namespace. It contains the following
classes: OleDbConnection
, OleDbCommand
and OleDbDataAdapter
. The other set
of classes is optimized for working with Microsoft SQL Server. It is part of the System::Data::SqlClient
namespace and its classes include: SqlConnection
, SqlCommand
and
SqlDataAdapter
.
Here is a complete example of how to connect to the database and fill the dataset. I have used the classes optimized for SQL Server. If you need to use OLE DB you only have to replace "Sql" with "OleDb". We try to fetch two tables Table1 and Table2 and set a parent-child relationship between them.
// Create a database connection string String* str = new String ("user id=sa;password=;initial catalog=MyDB;" "data source=(local)"); // Create the database connection object SqlConnection* sqlcon = new SqlConnection (str); sqlcon->Open (); // open it // create the first SQL query String* strTable1 = String::Format ("SELECT * FROM Table1 " "WHERE Field1 = {0}", FieldID.ToString ()); // FieldID is // an interger used to filter our query. // create the second SQL query. It joins the first table to select only those // fields in relation String* strTable2 = String::Format ("SELECT T2.* FROM Table2 T2 " "INNER JOIN Table1 T1 ON T2.ParendID = T1.ID " "WHERE T1.Field1 = {0}", Field1.ToString ()); // FieldID is // an interger used to filter our query. // create the SQL command objects. We pass in the contructor the // SqlConnection object and the query string SqlCommand* sqlTable1 = new SqlCommand (strTable1, sqlcon); SqlCommand* sqlTable2 = new SqlCommand (strTable2, sqlcon); // Create a data adapter for every table. We pass the SqlComand objects as parameters SqlDataAdapter* Table1Adapter = new SqlDataAdapter (sqlTable1); SqlDataAdapter* Table2Adapter = new SqlDataAdapter (sqlTable2); // now we create the dataset object and we give it a name DataSet* MyDataSet = new DataSet ("MyDataSet"); // we inform the dataset object about the tables it is going to contain // by adding those tables to the dataset. DataTable* Table1 = BackupDataSet->Tables->Add ("Table1"); DataTable* Table2 = BackupDataSet->Tables->Add ("Table2"); // now we are filling the Dataset using the Dataadapter objects // We need not say anything to the DataSet object about the // columns of the table and their data type. The DataAdapter objects // takes care of everything. Table1Adapter->Fill (Table1); Table2Adapter->Fill (Table2); // To ensure relationships between Tables we must add a DataRelation object // We assume that between column 0 in Table1 and column 1 in Table2 there is // a one-to-many relationship MyDataSet->Relations->Add (Table1->Columns->Item[0], Table2->Columns->Item[1]);
For details about relations and constraints you should read about the Constraint
class and the
two classes derived from it, ForeignKeyConstraint
and UniqueConstraint
.
Working with XML files
DataSet
s can work with XML files very easily. There are two methods to serialize a DataSet
object. These are DataSet::WriteXml
and DataSet::WriteXmlSchema
.
The first one writes data to the XML file and may include also schema information. It is useful when you want to
write an XML file that has schema information embedded. However, if you prefer schema information in a separate
(.xsd) file you should use the DataSet::WriteXmlSchema
method.
There are also plenty of classes in the System::Xml
namespace: XmlWriter
,
XmlReader
, XmlTextWriter
, XmlDataDocument
to name a few. You can use those
with a dataset to perform some advanced XML operations. For instance, if you want to write a dataset to an XML
file you can either use
// recevies a DataSet in constructor XmlDataDocument* xmlDoc = new XmlDataDocument(MyDataSet); XmlTextWriter* Output = new XmlTextWriter ("C:\\Myfile.xml", NULL); // perform some formatting Output->Formatting = Formatting::Indented; Output->Indentation = 2; // and write it xmlDoc->WriteTo (Output); Output->Close ();
or use the DataSet::WriteXml
method directly:
MyDataSet->WriteXml ("C:\\MyFile.xml", XmlWriteMode::WriteSchema);
In the latter situation I chose to embed schema information in the file by using one member of the
XmlWriteMode
enumeration. Other fields of the enumeration are XmlWriteMode::IgnoreSchema
if you do not want to include schema information, XmlWriteMode::DiffGram
if you want to include both
original values of the dataset and any changes. For reading an XML file we use another enumeration:
XmlReadMode
.
A DataRelation
has a property named Nested
. When this property is set to
true
then every time the DataSet
will write a record of a parent table, it will also nest all
corresponding records in all child tables.
Formatting data in an XML file is very flexible. By default, a new element is created for every column in every table. Assuming you have a table named Table1 with two columns named ID and Name the default output of an XML file will be:
<MyDataSet>
<Table1>
<ID>7</ID>
<Name>name 1</Name>
</Table1>
<Table1>
<ID>8</ID>
<Name>name 2</Name>
</Table1>
</MyDataSet>
If one column is to become an attribute of the node then you will set ColumnMapping
property
of DataColumn
class. For that you must have a look at the MappingType
enumeration. Its
fields are: Attribute
, Element
, Hidden
and SimpleContent
.
Choosing Attribute
will write the corresponding column as an attribute of the parent node. The output
will be like this:
<MyDataSet>
<Table1 ID="7">
<Name>name 1</Name>
</Table1>
<Table1 ID="8">
<Name>name 2</Name>
</Table1>
</MyDataSet>
SimpleContent
means that tags for one column will not be written. If you chose Hidden
then that column will not be written at all.
Of course you can combine them very easily. Doing this:
Table1->Columns->Item[0]->ColumnMapping = MappingType::Attribute; Table2->Columns->Item[1]->ColumnMapping = MappingType::SimpleContent;
will give you the following results:
<MyDataSet>
<Table1 ID="7">name1</Table1>
<Table1 ID="8">name2</Table1>
</MyDataSet>
Reading from an xml file is just as easy as writing. The simplest options is to use the ReadXml
method like this:
MyDataSet->ReadXml ("C:\\MyFile.xml", XmlReadMode::ReadSchema);
I have read also schema information from the file. This means that the DataSet
will automatically detect
data type for every column in all tables in the dataset and also any constraints or relations between tables. This
is really cool if you want to update a dataset. You change a value in the parent table and all child tables will
have the parent value updated. Also, trying to change a value in a child table that does not have a parent value
will throw an exception.
Updating the Database
Reading data into a dataset and then updating a database is just as easy as reading from a data source and filling a dataset. Assuming you are reading from an XML file and you are updating a SQL server, you must do the following:
- Create the
DataSet
object and read the XML file. If your XML file contains any schema information then the DataSet will detect and create the corresponding tables and enable any constraints automatically. - Create a
DataAdapter
object for every table you want to update. - Call the
Update
method for everyDataAdapter
object.
Remember that I mentioned four String
members of the SqlDataAdapter
class? These
are: SelectCommand
, InsertCommand
, UpdateCommand
and
DeleteCommand
. SelectCommand
is the query used to fetch data from the database. You can
define the other three objects if you want to perform custom update/insert/delete operations. If you do not want to
do that you can use either SqlCommandBuilder
or OleDbCommandBuilder
class. This class will
build those strings automatically.
Whenever you are writing data to a database is good practice to use a transaction to prevent concurrent writes to
the database. For this. the .NET framework provides two classes: OleDbTransaction
and
SqlTransaction
respectively.
Here is a sample of reading data from an XML file and the writing to a SQL Server.
SqlTransaction* SqlTrans; // declare a transaction object try { String* str = new String ("user id=sa;password=;initial catalog=MyDB;" "data source=(local)"); // Create the database connection object SqlConnection* sqlcon = new SqlConnection (str); sqlcon->Open (); // open it // create the data set object and give it a name DataSet* MyDataSet = new DataSet ("MyDataSet"); // read the XML file // I have also read the schema information file MyDataSet->ReadXml ("C:\\MyXmlFile.xml", XmlReadMode::ReadSchema); // Begin the transaction SqlTransaction = sqlcon->BeginTransaction (); // create the data adapters SqlDataAdapter* Table1Adapter = new SqlDataAdapter("SELECT * FROM Table1", sqlcon); SqlDataAdapter* Table2Adapter = new SqlDataAdapter("SELECT * FROM Table2", sqlcon); // we have provided only the SelectCommand strings. To update // the database we must provide the DeleteCommand, InsertCommand and // UpdateCommand also. // This can be done automatically with the command builder // create the command builders for each data adapter SqlCommandBuilder* Table1Command = new SqlCommandBuilder (Table1Adapter); SqlCommandBuilder* Table2Command = new SqlCommandBuilder (Table2Adapter); // we must specify the transaction used by these adapter. Table1Adapter->SelectCommand->Transaction = SqlTrans; Table2Adapter->SelectCommand->Transaction = SqlTrans; // update the database Table1Adapter->Update (MyDataSet, "Table1"); Table2Adapter->Update (MyDataSet, "Table2"); // don't forget to commit SqlTrans->Commit (); } catch (Exception* e) { // if we have started the transaction then rollback it if (SqlTrans != NULL) SqlTrans->Rollback(); }
Conclusion
DataSets
provide a very easy to use and powerful way to handle large amounts of data coming from different
tables or even data sources. A dataset will cache all data making it useful when you need to read data, perform
intensive operations with it and then update it. It also provides full XML support making your life easier to share
data across applications such as Web Services.