Using XML with ADO.NET

 

Using XML with ADO.NET

To help you take advantage of the xml data type and XQuery in SQL Server 2005, ADO.NET and the classes in the System.Xml namespace provide you with helpful tools. Here is a list of some changes that have been made to the ADO.NET classes to enable or enhance the SQL Server XML experience.

  • The System.Data.SqlTypes namespace contains a new class called SqlXml. This is a nullable data type that contains a method called CreateReader, which returns a derived instance of XmlReader that you can use to read the XML data contained in the instance.

  • The System.Data.SqlDbType enumeration contains a new value called xml that indicates the SqlXml data type (described earlier).

  • The SqlDataReader class contains a method called GetSqlXml that returns a SqlXml data type instance based on the column index that you pass to this function. The SqlDataReader object can also read the schema information of the xml columns by using the GetSchemaTable method, which has three new columns called XmlSchemaCollectionDatabase, XmlSchemaCollectionOwningSchema, and XmlSchemaCollectionName.

  • The System.Data.Common.DataAdapter class contains a method called ReturnProvide pecificTypes that is set to true to read in XML data as a SqlXml type. If you use the default value of false, XML data is read into a string field.

Getting Started with the SqlXml Class

You can access instances of the SqlXml class by using the DataReader object's GetSqlXml method. After you have a SqlXml object, you can execute its CreateReader method to retrieve an object that derives from the XmlReader object.

To see how this works, create a Windows application named XmlTest and add a Button control and a DataGrid control to the form. Note that the DataGrid control is being used because it supports hierarchical data, whereas the DataGridView does not. If the DataGrid control is not in your ToolBox, you will need to add it by right-clicking in the ToolBox, clicking Choose Items, and selecting the DataGrid control from the .NET Framework Components tab. On the Button control, change the Text property to Get Vehicles. We are using the DataGrid control instead of the DataGridView control because the DataGrid control can display many tables by assigning a DataSet object to its DataSource property and leaving the DataMember property empty. Add the Northwind database to your project, and add the following App.config file to your project.

image from book

App.Config File

<?xml version="1.0" encoding="utf-8" ?> <configuration>     <connectionStrings>        <add name="NwString"            connectionString="Data Source=.\SQLEXPRESS;            AttachDbFilename=|DataDirectory|\northwnd.mdf;            Integrated Security=True;User Instance=True"            providerName="System.Data.SqlClient" />    </connectionStrings> </configuration> 
image from book

Run the script to add MyXmlTable to the Northwind database. The script, titled "SQL: Creating and Populating MyXmlTable," can be found earlier in the chapter.

Next, add a reference to the System.Configuration.dll, System.Data.dll, and System.Xml.dll assemblies. Double-click the button, and add the following code to read the vehicles into the DataSet object.

image from book

Visual Basic

Imports System.Data Imports System.Data.SqlClient Imports System.Data.SqlTypes Imports System.Configuration Public Class Form1    Private Sub button1_Click(ByVal sender As System.Object, _          ByVal e As System.EventArgs) Handles button1.Click       Dim ds As New DataSet()       Dim nwSetting As ConnectionStringSettings = _          ConfigurationManager.ConnectionStrings("NwString")       Using cn As New SqlConnection()          cn.ConnectionString = nwSetting.ConnectionString          Using cmd As SqlCommand = cn.CreateCommand()             cmd.CommandText = "SELECT vehicles FROM MyXmlTable"             cn.Open()             Using rdr As SqlDataReader = cmd.ExecuteReader()                While (rdr.Read())                   Dim vehicles As SqlXml = rdr.GetSqlXml(0)                   If (Not vehicles.IsNull) Then                      ds.ReadXml(vehicles.CreateReader())                   End If                End While             End Using          End Using       End Using       dataGrid1.DataSource = ds    End Sub End Class 
image from book

image from book

C#

using System; using System.Data; using System.Windows.Forms; using System.Data.SqlClient; using System.Configuration; using System.Data.SqlTypes; namespace XmlTest {    public partial class Form1 : Form    {       public Form1()       {          InitializeComponent();       }       private void button1_Click(object sender, EventArgs e)       {          DataSet ds = new DataSet();          ConnectionStringSettings nwSetting =             ConfigurationManager.ConnectionStrings["NwString"];          using (SqlConnection cn = new SqlConnection())          {             cn.ConnectionString = nwSetting.ConnectionString;             using (SqlCommand cmd = cn.CreateCommand())             {                cmd.CommandText = "SELECT vehicles FROM MyXmlTable";                cn.Open();                using (SqlDataReader rdr = cmd.ExecuteReader())                {                   while (rdr.Read())                   {                      SqlXml vehicles = rdr.GetSqlXml(0);                      if (!vehicles.IsNull)                         ds.ReadXml(vehicles.CreateReader());                   }                }             }          }          dataGrid1.DataSource = ds;       }    } } 
image from book

When you run this code, a connection is opened to the database and a query is executed to retrieve the vehicles. Using the SqlDataReader object, you can read each row and execute the GetSqlXml method to retrieve an instance of the SqlXml class. As long as the SqlXml object is not null, the XML data is read into the DataSet object by executing the CreateReader method on the SqlXml object, which creates an XmlSqlBinaryReader object. The XmlSqlBinaryReader object is passed into the ReadXml method on the DataSet object. Each SqlXml object that is read into the DataSet object appends to existing XML data. Finally, the DataSet object is assigned to the DataSource property of the DataGrid control. The resulting DataGrid object displays a plus sign indicating that tables are available. If you click the plus sign, you'll see that the DataSet object converted the XML data into DataTable objects for cars, trucks, and repairs (as shown in Figure 15-8).

image from book
Figure 15-8: The XML data that was read into the DataSet object was automatically converted to DataTable objects.

Assigning and Retrieving the Schema

Working with typed and untyped xml data is essentially the same on the client by default, because typed xml columns do not automatically transfer their schema to the client. This means that schema validation does not occur until the modified XML data is sent to SQL Server. The problem is that in the previous example, the XML data was loaded into a DataSet object and all of the data types were inferred to be string data types. If you navigate to the repair table and change a code to a nonnumeric value, the new value is accepted. This probably isn't the behavior you want.

You can get the schema from SQL Server, which gives you the opportunity to perform clientside validation. You do this by using the SqlDataReader object's GetSchemaTable method to discover the schema information, and then you can retrieve the schema.

To prepare the SQL Server data for this example, we must add a schema to SQL Server for the vehicles and then alter MyXmlTable to use a typed xml column based on the new schema. Start by executing the following SQL script to create the Schema Collection called VehicleSchema in SQL Server.

image from book

SQL: Creating the VehicleSchema

CREATE XML SCHEMA COLLECTION VehicleSchema AS '<?xml version="1.0" standalone="yes"?> <xs:schema  xmlns=""       xmlns:xs="http://www.w3.org/2001/XMLSchema"       xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">   <xs:element name="repair">     <xs:complexType>       <xs:sequence>         <xs:element name="id" type="xs:int" />         <xs:element name="description" type="xs:string" minOccurs="0" />         <xs:element name="cost" type="xs:decimal" minOccurs="0" />       </xs:sequence>     </xs:complexType>   </xs:element>   <xs:element name="car">     <xs:complexType>       <xs:sequence>         <xs:element ref="repair" minOccurs="0" maxOccurs="unbounded" />       </xs:sequence>       <xs:attribute name="vin" type="xs:string" />       <xs:attribute name="make" type="xs:string" />       <xs:attribute name="model" type="xs:string" />     </xs:complexType>   </xs:element>   <xs:element name="truck">     <xs:complexType>       <xs:sequence>         <xs:element ref="repair" minOccurs="0" maxOccurs="unbounded" />       </xs:sequence>       <xs:attribute name="vin" type="xs:string" />       <xs:attribute name="make" type="xs:string" />       <xs:attribute name="model" type="xs:string" />     </xs:complexType>   </xs:element>   <xs:element name="vehicles"       msdata:IsDataSet="true" msdata:UseCurrentLocale="true">     <xs:complexType>       <xs:choice minOccurs="0" maxOccurs="unbounded">         <xs:element ref="repair" />         <xs:element ref="car" />         <xs:element ref="truck" />       </xs:choice>     </xs:complexType>   </xs:element> </xs:schema>' 
image from book

This schema is somewhat generic, but it contains an IsDataSet attribute set to indicate that this schema can be loaded into a DataSet object before the data is loaded, to enable type checking on numeric columns (such as the repair cost).

If you retrieve the schema you just added, you will find that the IsDataSet attribute was not stored. Try the following command.

SELECT xml_schema_namespace('dbo', 'VehicleSchema') 

This command returns the following schema information.

image from book

Retrieved Schema: Missing IsDataSet Attribute

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">    <xsd:element name="car">       <xsd:complexType>          <xsd:complexContent>             <xsd:restriction base="xsd:anyType">                <xsd:sequence>                   <xsd:element ref="repair" minOccurs="0"                      maxOccurs="unbounded" />                </xsd:sequence>                <xsd:attribute name="vin" type="xsd:string" />                <xsd:attribute name="make" type="xsd:string" />                <xsd:attribute name="model" type="xsd:string" />             </xsd:restriction>          </xsd:complexContent>       </xsd:complexType>    </xsd:element>    <xsd:element name="repair">       <xsd:complexType>          <xsd:complexContent>             <xsd:restriction base="xsd:anyType">                <xsd:sequence>                   <xsd:element name="id" type="xsd:int" />                   <xsd:element name="description"                      type="xsd:string" minOccurs="0" />                   <xsd:element name="cost" type="xsd:decimal" minOccurs="0" />                </xsd:sequence>             </xsd:restriction>          </xsd:complexContent>       </xsd:complexType>    </xsd:element>    <xsd:element name="truck">       <xsd:complexType>          <xsd:complexContent>             <xsd:restriction base="xsd:anyType">                <xsd:sequence>                   <xsd:element ref="repair"                      minOccurs="0" maxOccurs="unbounded" />                </xsd:sequence>                <xsd:attribute name="vin" type="xsd:string" />                <xsd:attribute name="make" type="xsd:string" />                <xsd:attribute name="model" type="xsd:string" />             </xsd:restriction>          </xsd:complexContent>       </xsd:complexType>    </xsd:element>    <xsd:element name="vehicles">       <xsd:complexType>          <xsd:complexContent>             <xsd:restriction base="xsd:anyType">                <xsd:choice minOccurs="0" maxOccurs="unbounded">                   <xsd:element ref="repair" />                   <xsd:element ref="car" />                   <xsd:element ref="truck" />                </xsd:choice>             </xsd:restriction>          </xsd:complexContent>       </xsd:complexType>    </xsd:element> </xsd:schema> 
image from book

The problem is that the schema was parsed and stored based on what SQL Server needed. The original schema is not stored in the Schema Collection, so you must store it to a file or to a text field in the database. In this example, we want to use the same schema that SQL Server is using, so we will add code to insert the IsDataSet attribute into the schema when it is retrieved from the database.

Now that the schema is loaded into SQL Server, we can modify MyXmlTable to use the schema. Open the table definition, and change the XmlTypeSpecification to use dbo.VehicleSchema, as shown in Figure 15-9.

image from book
Figure 15-9: Changing MyXmlTable to use the VehicleSchema

You can add code to discover the schema information and retrieve it. The code that discovers the schema and retrieves it uses the same connection object, which means you must enable the MultipleActiveResultSets (MARS) option in the connection string, as follows.

image from book

App.Config File

<?xml version="1.0" encoding="utf-8" ?> <configuration>     <connectionStrings>         <add name="NwString"             connectionString="Data Source=.\SQLEXPRESS;             AttachDbFilename=|DataDirectory|\northwnd.mdf;             Integrated Security=True;User Instance=True;             MultipleActiveResultSets=True"             providerName="System.Data.SqlClient" />     </connectionStrings> </configuration> 
image from book

To discover the schema information and retrieve it, we will add a new method, called GetSchema, which reads the schema from the database and adds the IsDataSet attribute, as shown in the following code.

image from book

Visual Basic

Imports System.Data Imports System.Data.SqlClient Imports System.Data.SqlTypes Imports System.Configuration Imports System.Xml Imports System.IO Public Class Form1    Private Sub button1_Click(ByVal sender As System.Object, _          ByVal e As System.EventArgs) Handles button1.Click       Dim ds As New DataSet()       Dim nwSetting As ConnectionStringSettings = _          ConfigurationManager.ConnectionStrings("NwString")       Using cn As New SqlConnection()          cn.ConnectionString = nwSetting.ConnectionString          Using cmd As SqlCommand = cn.CreateCommand()             cmd.CommandText = "Select vehicles from MyXmlTable"             cn.Open()             Using rdr As SqlDataReader = cmd.ExecuteReader()                GetSchema("vehicles", ds, cn, rdr)                While (rdr.Read())                   Dim vehicles As SqlXml = rdr.GetSqlXml(0)                   If (Not vehicles.IsNull) Then                      ds.ReadXml(vehicles.CreateReader())                   End If                End While             End Using          End Using       End Using       dataGrid1.DataSource = ds    End Sub    Private Sub GetSchema(ByVal xmlColumn As String, _          ByVal ds As DataSet, ByVal cn As SqlConnection, _          ByVal rdr As SqlDataReader)       'discover and retrieve schema       Dim schema As DataTable = rdr.GetSchemaTable()       Dim row As DataRow = schema.Select("columnName='" + xmlColumn + "'")(0)       Using schemaCmd As SqlCommand = cn.CreateCommand()          schemaCmd.CommandText = _             "SELECT xml_schema_namespace(@owner, @name) FOR XML PATH('')"          schemaCmd.Parameters.AddWithValue(_             "@owner", row("XmlSchemaCollectionOwningSchema"))          schemaCmd.Parameters.AddWithValue(_             "@name", row("XmlSchemaCollectionName"))          'load into XmlDocument to fix IsDataSet attribute          Dim doc As New XmlDocument()          doc.Load(schemaCmd.ExecuteXmlReader())          Dim mgr As New XmlNamespaceManager(doc.NameTable)          mgr.AddNamespace("xsd", "http://www.w3.org/2001/XMLSchema")          Dim vehicles As XmlNode = doc.SelectSingleNode(_             "//xsd:element[@name=""" + xmlColumn + """ ]", mgr)          Dim dataSetAtt As XmlAttribute = doc.CreateAttribute("msdata", _             "IsDataSet", "urn:schemas-microsoft-com:xml-msdata")          dataSetAtt.Value = "true"          vehicles.Attributes.Append(dataSetAtt)          Dim tempStream As New MemoryStream()          doc.Save(tempStream)          tempStream.Position = 0          ds.ReadXmlSchema(tempStream)       End Using    End Sub End Class 
image from book

image from book

C#

using System; using System.Data; using System.Windows.Forms; using System.Data.SqlClient; using System.Configuration; using System.Data.SqlTypes; //using System.Collections; using System.Collections.Generic; using System.Xml; using System.Xml.XPath; using System.IO; namespace XmlTest {    public partial class Form1 : Form    {       public Form1()       {          InitializeComponent();       }       private void button1_Click(object sender, EventArgs e)       {          DataSet ds = new DataSet();          ConnectionStringSettings nwSetting =             ConfigurationManager.ConnectionStrings["NwString"];          using (SqlConnection cn = new SqlConnection())          {             cn.ConnectionString = nwSetting.ConnectionString;             using (SqlCommand cmd = cn.CreateCommand())             {                cmd.CommandText = "SELECT vehicles FROM MyXmlTable WHERE id=1";                cn.Open();                using (SqlDataReader rdr = cmd.ExecuteReader())                {                   GetSchema("vehicles", ds, cn, rdr);                   while (rdr.Read())                   {                      SqlXml vehicles = rdr.GetSqlXml(0);                      if (!vehicles.IsNull)                         ds.ReadXml(vehicles.CreateReader());                   }                }             }          }          dataGrid1.DataSource = ds;       }       private static void GetSchema(string xmlColumn,          DataSet ds, SqlConnection cn, SqlDataReader rdr)       {          //discover and retrieve schema          DataTable schema = rdr.GetSchemaTable();          DataRow row = schema.Select("columnName='" + xmlColumn + "'")[0];          using (SqlCommand schemaCmd = cn.CreateCommand())          {             schemaCmd.CommandText =                "SELECT xml_schema_namespace(@owner, @name) FOR XML PATH('')";             schemaCmd.Parameters.AddWithValue(                "@owner", row["XmlSchemaCollectionOwningSchema"]);             schemaCmd.Parameters.AddWithValue(                "@name", row["XmlSchemaCollectionName"]);             //load into XmlDocument to fix IsDataSet attribute             XmlDocument doc = new XmlDocument();             doc.Load(schemaCmd.ExecuteXmlReader());             XmlNamespaceManager mgr = new XmlNamespaceManager(doc.NameTable);             mgr.AddNamespace("xsd", "http://www.w3.org/2001/XMLSchema");             XmlNode vehicles = doc.SelectSingleNode(                "//xsd:element[@name=\"" + xmlColumn + "\"]", mgr);             XmlAttribute dataSetAtt = doc.CreateAttribute("msdata",                "IsDataSet", "urn:schemas-microsoft-com:xml-msdata");             dataSetAtt.Value = "true";             vehicles.Attributes.Append(dataSetAtt);             MemoryStream tempStream = new MemoryStream();             doc.Save(tempStream);             tempStream.Position = 0;             ds.ReadXmlSchema(tempStream);          }       }    } } 
image from book

The new GetSchema method discovers the schema name by calling the GetSchemaTable method and then selecting the metadata row for the column we are interested in (in this case, the vehicles column). This information is plugged into the call to xml_schema_namespace, which retrieves the schema from the database. Instead of loading the schema directly into the DataSet object, we load it into an XmlDocument object and perform a search for the vehicles element, and then we add the IsDataSet attribute to it, along with its required namespace. The DataSet object can read the schema from either a file or a stream, so the XmlDocument object is saved to a temporary MemoryStream object and the DataSet object uses the MemoryStream object to read the schema.

If you run this sample application and click the button to load the vehicles data, the schema is retrieved from the database and then the schema is loaded into the DataSet object. Then the data is loaded and displayed.

With the schema information at the client, navigate to the repair table and try to change one of the cost values to a nonnumeric value. When you leave the cost field, it is validated. Validation will fail, and the value will revert to its original numeric value.

Passing an SqlXml Object Parameter to Update the Database

When it's time to update the database, you can create a parameter that is an SqlXml type and issue an UPDATE statement to the database with this object.

For the following example, add a Button control to the form. Change the Text property to Update Vehicles, and add the following code to its Click event handler.

image from book

Visual Basic

Private Sub button2_Click(ByVal sender As System.Object, _       ByVal e As System.EventArgs) Handles button2.Click    Dim ds As DataSet = CType(dataGrid1.DataSource, DataSet)    Dim nwSetting As ConnectionStringSettings = _       ConfigurationManager.ConnectionStrings("NwString")    Using cn As New SqlConnection()       cn.ConnectionString = nwSetting.ConnectionString       Using cmd As SqlCommand = cn.CreateCommand()          cmd.CommandText = _             "UPDATE MyXmlTable SET vehicles = @veh WHERE id=1"          Dim ms As New MemoryStream()          ds.WriteXml(ms)          ms.Position = 0          Dim r As XmlReader = XmlReader.Create(ms)          Dim x As New SqlXml(r)          cmd.Parameters.AddWithValue("@veh", x)          cn.Open()          cmd.ExecuteNonQuery()       End Using    End Using    MessageBox.Show("Done.") End Sub 
image from book

image from book

C#

private void button2_Click(object sender, EventArgs e) {    DataSet ds = (DataSet)dataGrid1.DataSource;    ConnectionStringSettings nwSetting =       ConfigurationManager.ConnectionStrings["NwString"];    using (SqlConnection cn = new SqlConnection())    {       cn.ConnectionString = nwSetting.ConnectionString;       using (SqlCommand cmd = cn.CreateCommand())       {          cmd.CommandText =             "UPDATE MyXmlTable SET vehicles = @veh WHERE id=1";          MemoryStream ms = new MemoryStream();          ds.WriteXml(ms);          ms.Position = 0;          XmlReader r = XmlReader.Create(ms);          SqlXml x = new SqlXml(r);          cmd.Parameters.AddWithValue("@veh", x);          cn.Open();          cmd.ExecuteNonQuery();       }    }    MessageBox.Show("Done."); } 
image from book

 


Programming Microsoft ADO. NET 2.0 Applications. Advanced Topics
Linux Application Development (2nd Edition)
ISBN: 735621411
EAN: 2147483647
Year: 2004
Pages: 85
Authors: Michael K. Johnson, Erik W. Troan
BUY ON AMAZON

Similar book on Amazon
Beginning Linux Programming
Beginning Linux Programming
Advanced Programming in the UNIX(R) Environment (Addison-Wesley Professional Computing Series)
Advanced Programming in the UNIX(R) Environment (Addison-Wesley Professional Computing Series)
Tom Swan's GNU C++ for Linux (Professional Dev. Guide)
Tom Swan's GNU C++ for Linux (Professional Dev. Guide)

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net