Introduction to DataSets and Working with XML Files
By Alexandru Savescu
Introduction to the use of data sets and XML files
This Article Gives You An Introduction To .Net's DataSets and How You Can Use Them with XML FILES
This article introduces the .NET dataset and church how you use them to use the XML files
Introduction
Introduction
......................... ..
This article introduces the .NET dataset and teach you how to use them with XML files. This will be a very relaxed thing when you want to share data from the data source and think of using XML.
System Requirements
System Requirements
TO Compile The Solution You NEED To Have Microsoft Visual Studio .NET Installed. To Run Any The Client Executable You Need To Have The .NET Framework Installed.
For compilers, you must install Microsoft Visual Studio .NET. At the same time, for the execution of the client program, the .NET framework is also indispensable.
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. ...........................
It is provided here that a small program written in C #. It uses a DataGrid to display the data content. By default, the program links to the SQL Server server, helps the Northwind database, remove the primary data from the Customers table, and take the slave table data from the ORDERS. Here, you have installed the SQL Server database server. If this is not this, you must manually modify the connection string and re-establish it.
THEN, You CAN Save The Dataset To Xml File. Schema Information is Also Saved.
Then you can save the data set to the XML file, the architecture information will also be saved.
What is a dataset?
What is a data set?
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. A dataset object is a data set to store high-speed buffer data from database cache. General object. It is a 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 datasten:
A obvious question is: When do you use the data set? The answer is this: it depends on. You should understand that the data set is stored in the memory buffer. So use the data set in these cases:
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.
l You use multiple separate data sheets or data tables from different data sources.
l You want to exchange data exchanges like Web Service.
l You have to handle a large number of records of databases. If you use a SQL query each time you use a SQL query to operate the database, this will affect performance.
l You want to operate the data using XML / XSLT.
You Should NOT USE A DataSet IF:
If you are in this case, you should use the data set:
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.
You use web forms in the program, while the web forms and their controls are re-established at the client each time you build. In this way, if you don't intend to put the data set in the buffer, you will affect efficiency every time you build, fill, and destroy data sets.
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 ):. DataTableCollection data set has a data table storing a set of objects. You can declare a dataset object and add a table to the table below.
// 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 point of the point l t: a a
You can also reference the data table with the result of the ADD method or the returned result:
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. :)
There are two important members: rows and columns (ROWS / Column). Rows is a DATAROWCOLLECTION object, while columns is a DatacolumnColleTion object. DataRowCollection and DatacolunmCollection are DataRow and Datacolumn objects. I believe you can easily understand the meaning of these objects. J
Adding Data to a data set is straight-forward:
One-way filling data
// adding data to the first table in the dataset
DataTable * Table1 = myDataSet-> Tables-> item [0];
// Add Two columns to the Table
Table1-> Column-> Add ("column1");
Table2-> Column-> Add ("column2");
// Get The Collection of RowsDataRowCollection * 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 the the 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.
If you want to specify the data type of a column, you can use Datacolumn :: DataType. You can also set the following data types: Boolean, Byte, Char, DataTime, 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 it is done if you want to connect to a real data source.
Just these content, if you want to create a data set manually, this is what you need to know. If you have to connect to a real data source, it is not the case.
Binding a catabase
Help 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.
To connect to a database server (such as SQL Server) and populate the data set, you need three additional objects: Connection, Datacommand, DataAdapter.
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.
Connection is used to connect the database object. You must provide a connection string. If your program uses a transaction, you have to attach the transaction object to the connection.
The DataCommand object is used to sending commands to the database server. It 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. Datacommand objects are used to send a command to the database server. It contains four typestem :: string type objects: SELECTCOMMAND, INSERTCOMMAND, UPDATECMMAND, DELETECMMMAND. Their meaning is obvious, that is, four basic SQL operations.
...................... ..
DataAdapter object is very strange. It can take out data from the database and fill the data set, and you can refill the contents of the dataset update 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 not 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.
You may now want to know what is the class corresponding to the above description? Microsoft has prepared two sets of classes to us - you have to say that the life of the .Net era is so easy, isn't it? J The first group is based on OLE DB, and is also part of the System :: Data :: OLEDB namespace. It contains the following classes: OLEDBConnection, OLEDBCommand, and OLEDBDataAdapter. Another group is a class optimized for Microsoft SQL Server. It is part of the System :: Data :: SqlClient namespace, its class contains: 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. How to connect the database and populate the full code of the data set. I used the class optimized for SQL Server. If you want to use OLE DB, you can use "SQL" to replace "OLEDB". We tried to take out two tables of Table1 and Table2 and establish a master 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 ou query.
// Create The Second Sql Query. It Join the First Table To Select Only Those
// fields in reason
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 ou query.
// Create the SQL Command Objects. WE Pass in The Contructor THE
// SqlConnection Object and the query string
SQLCommand * sqltable1 = new sqlcommand (start1, sqlcon);
SQLCommand * sqltable2 = new sqlcommand (start2, 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");
// Weinform the dataset object about the tables it is going to contact
// 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 dataset object
// column 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-> Column-> item [0],
Table2-> Column-> item [1]);
For Details About Relations and continraints You Should Read About The ConsTraint Class and The Two Classes Derived from It, ForeignKeyConstRAINT AND UNQUECONSTRAINT.
About relationships and constraints You can refer to the Constraint class and the ForeignKeyConstraint and UNIQUECONSTRAINT classes from it from it.
Working with XML Files
Use XML files
DataSets 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 That Has Schema Information Embedded. However, if You Prefer Schema Information In A Separate (.xsd) File You Should Use The Dataset :: WriteXmlschema Method. The dataset is easy to work with XML. Is there two ways? ? ? ? ? ? Data set objects. DataSet :: Writexml and DataSet :: Writexmlschema. The first is written to the XML file or contains architectural information. However, if you want to store the architecture information in a separate file (.xsd), you can 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 each use
System :: XML Namespace has many classes: XMLWriter, XmlReader, XmlTextWriter, XmlDataDocument, etc. You can use these mating datasets to perform some advanced XML operations. For example, if you want to write the data set to the XML file, you can use it.
// Recevies a DataSet in Constructor
XmlDataDocument * xmldoc = new xmldatadocument (myDataSet);
XmlTextWriter * Output = New XMLTextWriter ("c: //myfile.xml", null;
// Perform Some Formatting
Output-> Formatting = formatting :: indeted;
Output-> indeentation = 2;
// and write it
XMLDoc-> Writeto (OUTPUT);
Output-> close ();
Or use the dataset :: Writexml method Directly:
You can also use DataSet :: Writexml directly
MyDataSet-> Writexml ("c: //myfile.xml", XMLWRITE :: 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 Another Enumeration: XmlReadmode. I used XMLWRITEMODE a member embedded in the architecture information in the file. You can use XMLWRITE :: DiffGram using XMLWRITE :: DiffGram with XMLWRITE :: DiffGram with XMLWRITE :: Ignoreschema, including data set. Read the XML file To use XmlReadMode.
A DataRelation Has a Property Named Named Named. When this property is set to True The Every Time The Dataset Will Write a Record of a Parent Table, IT Will Also Nest All Corresponding Records in All Child Tables.
Data relationship has a nested property. When its value is TRUE, the data set will also embed all the records corresponding to all slabs each time the main table record is written.
. 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.:
The formatted data in the XML file is flexible. In general, each column in each table is created a corresponding element. Assume that you have a table called Table1, with the ID and Name columns, the default output XML is as follows:
. 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: If a column is required as a node property, you need to set the columnmapping property in the Datacolumn class. You should look at MappingType in advance. Its domain value is: Attribute, Element, Hidden, SimpleContent. If the property is selected, the column is used as an attribute of the parent node. Output is like this:
SimpleContent Means That Tags for One Column Will Not Be Written. If You Chose Hidden THAT COLUMN WILL NOT BE WRITTEN AT ALL.
SimpleContent means that the tag of the column will not write, only value. If you choose Hidden, the column will be completely ignored.
Of Course You CAN Combine Them Very Easily. Doing this:
Of course, you can easily combine them. this way:
Table1-> column-> item [0] -> columnmapping = mappingtype :: attribute;
Table2-> column-> item [1] -> columnmapping = mappingtype :: SimpleContent
Will Give You The Following Results:
The results are as follows:
Reading
From An Xml File Is Just As EasPlease Is To Use The Readxml Method Like this:
Reading with XML files is as easy. The simplest choice is to use the READXML method:
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. I read a schema from a file information. This means that DataSet automatically discovers the data type and relationship between the columns of each table in the data set. If you want to update the data set, this is really cool. You can change a value of the primary table, and all from the table will be updated. Similarly, if the value from the table is repaired into a corresponding primary table will throw an exception.
Updating the database
Update 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.:
Reading data into a dataset and updating the database is as simple as the data source read and fill the data set. Suppose you read data from the XML file and update a SQL Server, you must perform the following steps:
Create The Dataset Object and read the xml file. If Your XML File Contains Any Schema Information 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 Every DataAptapter Object.
l Establish a DataSet object and read an XML file. If your XML file contains schema information, DataSet automatically discovers and creates a corresponding table to generate a corresponding constraint.
l Create a DataAdapter object for each table you want to update.
l Call the UPDATE method for each DataAdapter 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. remember SqlDataAdapter class I mentioned in the four strings member yet? SelectCommand, INSERTCOMMAND, UPDATECOMMAND, and DeleteCommand. SELECTCOMMAND is used to extract data from the database. If you want to perform your own Update / INSERT / DELETE, you can define three other objects. You can also use SQLCommandBuilder or OLEDBCommandbuilder class if you don't want it. This class automatically creates these strings.
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.
Whether you write the data into the database, use a transaction to prevent both the write database from being written to the database. For this, the .NET framework provides two classes: OLEDBTRANSACTION and SQLTRANSACTION.
Here Is A Sample of Reading Data from an XML File and The Writing to a SQL Server.
This is an example of reading data from an XML file and then writing to a database.
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 transactionsqltransaction = Sqlcon-> begintransaction ();
// Create the data adapters
SqlDataAdapter * table1adapter = new SqldataAdapter ("Select * from table1", sqlcon);
SqlDataAdapter * Table2adapter = New SqldataAdapter ("Select * from Table2", Sqlcon);
// we have advantly the selectcommand strings. To update
// The Database We Must Provide The DeleteCommand, InsertCommand and
// UpdateCommand Also.
// this can be done automaticly with the commit builder
// Create The Command Builders for Each Data Adapter
Sqlcommandbuilder * table1command = new sqlcommandbuilder (table1adapter);
Sqlcommandbuilder * table2command = new sqlcommandbuilder (Table2adapter);
// Weme 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
in 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.
The data set provides a powerful method for powerful and practical functions to operate a large number of data from different tables or even different data sources. A data set buffer can store large amounts of data, you can read, operate, and update. It also provides complete
Xml
Support, making you easier to implement like Web Service
Such a cross-platform data sharing.
