精通
英语
和
开源
，
擅长
开发
与
培训
，
胸怀四海
第一信赖
精通
英语
和
开源
，
擅长
开发
与
培训
，
胸怀四海
第一信赖
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.DataSet介绍和结合XML使用DataSet。当要和数据源共享数据时要想到DataSet，用XML时也要想到。
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 tableCustomers 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.显示了带有DataGrid的窗体。连接SQL Server获取数据。
Then, you can save the dataset to XML file. Schema information is also saved.
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 adataset is a collection of in-memory cached data. So it's good to use datasets when:是内存缓存数据集合，有如下使用场合：
You should not use a dataset if:
A DataSet has a DataTableCollection object as a member that is nothing but a collection of DataTableobjects. You declare a DataSet object and add tables to it like this (in Managed C++):有DataTableCollect成员，管理多个表。定义DataSet对象和添加表的示例如下：
ion
// 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:
Hide Copy Code
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 andDataColumnCollection is a collection of DataColumn objects. I am sure you can easily figure out what these objects represent. DataTable有2个重要的成员：Rows和Columns。Rows是DataRowCollection，它包含多个DataRow;而Columns是DataColumnCollection ，它有多个DataColumn。
Adding data to a data set is straight-forward:
// 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::DataTypeproperty. 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.
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 fourSystem::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 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.
DataSets can work with XML files very easily. There are two methods to serialize a DataSet object. These areDataSet::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 theDataSet::WriteXmlSchema method. DataSet::WriteXml会写入数据且可能包含结构信息。
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 XMLoperations. For instance, if you want to write a dataset to an XML file you can either use
// 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:
In the latter situation I chose to embed schema information in the file by using one member of theXmlWriteMode 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.上面代码行会写入结构信息，如果不想则用XmlWriteMode::IgnoreSchema。
A DataRelation has a property named Nested. When this property is set to true then every time the DataSetwill write a record of a parent table, it will also nest all corresponding records in all child tables. DataRelation有Nested属性，当它为真时，当写父表时，也会处理关联子表。
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 anXML file will be:
If one column is to become an attribute of the node then you will set ColumnMapping property of DataColumnclass. 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:
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:
will give you the following results:
Reading from an xml file is just as easy as writing. The simplest options is to use the ReadXml method like this:
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.上面代码行也会读取结构信息，意味着DataSet自动检测列数据类型、约束和关系。
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:
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 orOleDbCommandBuilderclass. 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 SqlTransactionrespectively.
Here is a sample of reading data from an XML file and the writing to a SQL Server.
// 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();
}
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.DataSet提供了容易的方法来处理来自多个表的大量数据。一个dataset对象能缓存所有数据，在读取数据、进行密集操作和更新时非常有用。它同样提供了完善的XML支持，让你易于跨应用共享数据，比如Web服务。