XML and ADO.NET C# Help

XMLis the glue that binds AOO.NET to the rest of the:world. ADO.NET was designed from the ground up to work within the XMLenvironment. XMLis used to transfer the data to and from the data store and the applition or Web page.

BecauseADO.NET uses XMLas the traIljpOrt in remoting scenarios, data can be exchanged with applications and systems that are not even aware of AOO.NET.Because of the importance of XMLin AOO.NET, there are some powerful features in AOO.NET that allow the reading and writing of XMLdocuments. The System.Xml namespace also contains classes that CiU\consume or utilize AOO.NET relational data.

The database that is used for the examples is from the AdventureWorksLT sample application, The sample database can be downloaded from codeplex. com/ SqlServerSamples, Note that there are several versions of the AdventureWorks database. Most will work, but the LTversion is the simplified version and is more than adequate for the purposes.

Converting ADO.NET Data to XML

The first example uses AOO.NET, streams, and XMLto pull some data from the database into a DataSet,load an XmlDocUlllentobject with the XMLfrom the DataSet, and load the XMLinto a text box. To run the next few examples, you need to add the following using statements:
using System.Data;
using System.Xml:
using system.Data.Sq~lient;
uliing System.IO;
The connection string is defined as a module-level variable.
string _connectString = ·Server-.\\SQLExprell;
The ADO.NET samples have a oataGri. ~ add,ed to’the forms: This will allow you to lee the data in the ADO.NET ~taSet because it is boufdto the grid, as well as the data from generated to XML.

documents that you load in the text box. Here is the code for the first example. The first step in the examples is to create the standard ADO NET objects to create a dataset. After the dataset has been created, it is bound to the grid.

private void buttonl_Click(object sender, EventArgs e)
XmlDecumentdoc •• new XmlDocwnent (I;
oataSet de •• new DataSet(O~Productl·l:
SqlConnection conn 2 new SqlConnection(_connectString):
SqlDataAdapter da •• new SqlDataAdapter
(“SELECTName, StandardCost FROMSalesLT. Product” , conn) ;
Ilfill the data set
da.Fill(ds, “Products”l;
Ilload data into grid
dataGridViewl.Dat.Source – de.Tablas[“Products”l:
After you create theADO.Net objectsand bind to the grid, you instantiate a MtIIIIOryStreamobject,a
StreamReader object,and a Str..-tri ter ob;ert The StreamReader and StreamWriter objectswill use the Stream to move the XML around:
StreamReader strmRe.d-new Strea.Reader(rnemStral:
Streamwriter -new Streamwriter(memStrm):
YouJISea M_oryStream so that you don’t have to write anything to disk; however, you could have used any object,thatwaSbased on the Stream class, such FileStream.

This next step is where the XML is generated. Youcall the wri teXml (I method from the oat.Set class. This method generates an XML document. WriteXml (I has two overloads: one takes a string with the file path and name, and the other adds a mode parameter. This mode is an XmlWriteMode enumeration, with the following possible values:

  1. IgnoreSchema
  2. WriteSchema
  3. .WriteSchema

ignoreSchema is used if you don’t want WriteXml (I to write an inline schema at the start of your XML file; the Writeschema, parameter if you do want one ADiffGramshows the data before and after an edit in DataSet.

Figure 28-5 shows the data in the list as well as the bound data grid.

Figure 28-5

Figure 28-5

If you had wanted only the schema, you could have called WriteXmlSchema() instead ofWriteXml (), This method has four overloads. One takes a string, which is the path and file name of where to write the XML document. The,second overload uses an object that is based on the XmlWriter class, The third overload uses an object based on the TextWriter class. The fourth overload is derived from the Stream class, Also, if you wanted to persist the XML document to disk, you would have used something like this:

string file = ‘c:\\test\\product.xml’;
This would give you a well-formed XML 10cument on disk that could be read in by another stream, or by DataSet, or used by another application or Web site. Because no XmlMode parameter is specified.this XmlDocument would have the schema included. In this example, you use the stream as a parameter to the xmlDocument . Load () method. You now have two views of the data, but more important, you can manipulate the data using two different models. You can use the sys cem, Data -iamespace to use the data, or you can use the System.

Xml namespace on the data. This can lead to some very flexible dec;igr>”iT) vour anplications, because now you are not tied to just one object model to program with. This is the real power to the ADO.NET and System. Xml combination.

You have multiple views of the same data and multiple ways to access the data,The following example simplifies the process by eliminating the three streams and by using some of the ADO capabilities built into the System. Xml namespace. You will needto change the module-level line of code:

private XmlDocument doc = new XmlDocument();
private XmlDataDocument doc:

You need this because you are now using the XmlDataDocument, Here is the code (which you can finding the ADOSample2 folder):

As you can see, the code to load the DataSet object into the XML document has been simplified, Instead of using the XmIDocument class, you are using the XmlDataDocument class. This class was built specifically Ear using data with a DataSet object, The XmlDataDocument is based on the XmlDocument class, so it has all of the functionality that the XmlDocument class has. One of the main differences is the overloaded constructor that the XmIDataDocument has. Note the line of code that instantiates XmIDataDocument (doc):
doc = new XmIDataDocument(ds);
It passes in the DataSet object that you created, ds, as a parameter, This creates the XML document from the DataSet, and you don’t have to use the Load () method. In fact, if you instantiate a new XmIDataDocument object without passing in a DataSet as the parameter, it will contain a DataSet with the name NewDataSet that has no DataTables in the tables collection. There is also a DataSet property, which you can set after an XmlDataDocument-based object is created, Suppose that you add the following line of code after the DataSet. Fill () call:

Only the first couple of Products elements are shown, The actual XMLfile would contain all of the produts in the Products table of Northwind database.

ConvertIng RelatIonal Data

This looks simple enough for a single table, but what about relational fhta. such as multiple DataTable & and Relations in the DataSet? It all still works the same way, Here is an example Usings two related tables: “

In this sample you arecreating,two DataTables inthe XMLProducts DataSet: Products and Categories. You createa new relationon the ProductCategoryID column inboth tables.

By making the same Wri teXml () method call that you did in the previous example, you wil get the followingXML file(SuppProd.xml):

The schema includes both DataTables that were in the DataSet. In addition, the data includes all of them data from both tables. For the sake of brevity, only the first Products and ProductCategory records are shown here, As before, you could have saved just the schema or just the data by passing in the correct XmlWriteMode parameter.

Converting XML to ADO.NET Data

Suppose that you have an XML document that you would like to get into an ADO.NET DataSet You would want to do this so that you could load the XML into a database, or perhaps bind the data to a .NET data control such as a DataGrid, This way, you could actually use the X¥L document as your data store and eliminate the overhead of the database altogether, If your data is reasonably small in size, this is an attractive possibility, Here is some code to get you started (ADOSampleS):

It is that easy, You instantiate a new DataSet voject. Then you call the ReadXml () method, and you have XML in a DataTable in your DataSet. As with the wri teXml () methods, ReadXml () has an XmlReadMode earameter. ReadXml () has a few more options in the XmlReadMode, as shown in the following table.

There is also the ReadXmlSchema() method. This reads in a standalone schema and creates the tables, columns, and relations. You use this if your schema is not inline with your data. ReadXmlSchema ( ).has the same four overloads: a string with file and path name, a Stream-based object, a TextReader-based object, and an XmlAeader-based object, To show thatthe data tables are getting created properly we iterate through the tables and columns and display the names in the text box. You can compare this to the database and see that all is well, The last foreach loops perform this task, Figure 28-6 shows the output.

Looking at the list box, you can check that the data tables were created with the columns all having the correct names and data types.

Something else you might want to note is that because the previous two examples didn’t transfer any data to or from a database, no SqlDataAdapter or SqlConnection was defined, This shows the real flexibility of both the System, Xmlnamespace and ADO.NET:You can look at the same data in multiple formats, If you need to do a transform and show the data in HTML format, or if you need to bind the data to a grid, you can take the same data and, with just a method call, have it in the required format.

Figure 28-6

Figure 28-6

Posted on October 31, 2015 in Manipulating XML

Share the Story

Back to Top
Share This