SQLXML 4 et la prise en charge des classes managées SQLXML dans le Framework .NET

Le composant SQLXML offre une multitude de fonctionnalités permettant de traiter facilement et avec souplesse les données XML. Parmi ces fonctionnalités nous pouvons citer entre autres :

• prise en charge de SQL Server Native Client : SQLXML 4.0 prend en charge les fournisseurs OLEDB et SQL Server native client ;
• prise en charge des données introduites dans SQL Server 2005 - 2008 : SQLXML 4.0 prend en charge le nouveau type de données XML introduit par SQL Server 2005 ainsi que les types Date, Time, DateTime2 et DateTimeOffset introduits pas SQL Server 2008 qui seront activés par celui-ci sous la forme de types scalaires intégrés lors d'une utilisation avec le fournisseur OLE DB SQL Server 2008 Native Client ;
• SQLXML 4.0 et MSXML : SQLXML 4.0 n'installe pas MSXML. SQLXML 4.0 utilise MSXML 6.0, qui est installé avec SQL Server 2005 ou SQL Server 2008 ;
• prise en charge de l'interface ISAPI SQLXML 3.0 et des types de données introduits dans SQL Server 2005 : la prise en charge de l'interface ISAPI SQLXML 3.0 a été supprimée dans SQXML4.0. Dans le cas où votre solution nécessite des fonctionnalités améliorées de typage de données introduites par SQL Sever 2005, notamment le type de données XML, vous devez utiliser une autre solution comme par exemple les classes managées SQLXML ;
• migration pour FOR XML côté client : en raison des modifications apportées au moteur d'exécution, SQL Server 2005 et SQL Server 2008 peuvent retourner des valeurs dans les métadonnées pour une table de base différente de celle retournée par la requête FOR XML exécutée sous SQL Server 2000. Si cela se produit, la mise en forme côté client des résultats de la requête FOR XML aura une sortie différente selon la version sur laquelle la requête est exécutée ;
• les classes managées SQLXML : SQLXML 4.0 offre les classes managées SQLXML pour le traitement facile des données XML dans une application .NET.

Si une requête FOR XML est exécutée côté client à l'aide de SQLXML 3.0 sur une colonne de type de données XML, les données dans les résultats reviendront sous la forme d'une chaîne entièrement décomposée. Dans SQLXML 4.0, si SQL Server Native Client (SQLNCLI10) est spécifié en tant que fournisseur, les données seront retournées sous forme de données XML.

La clause FOR XML, introduite dans les versions de SQL Server 2000 et améliorée dans SQL Server 2005, permet de publier et traiter les données sous un format XML. L'introduction de FOR XML dans une requête Select permet de récupérer le résultat d'une requête SQL sous forme de code XML. Elles peuvent s'exécuter directement ou à partir d'une fonction ou procédure stockée définie par l'utilisateur.

La syntaxe de base FOR XML est la suivante :

FOR XML mode
   [, XMLDATA][, ELEMENTS][, BINARY BASE64] [, TYPE ]
   [, ROOT [ ('RootName') ] ]

IV-A-1. Arguments optionnels ▲

IV-A-2. Arguments obligatoires ▲

SELECT EmployeeID, 
BirthDate, 
FirstName, 
MiddleName, 
LastName
FROM HumanResources.Employee E 
inner join Person.Contact C
on E.ContactID = C.ContactID
ORDER BY EmployeeID, FirstName
FOR XML RAW

<row EmployeeID="1" BirthDate="1972-05-15T00:00:00" FirstName="Guy" MiddleName="R" LastName="Gilbert" />
<row EmployeeID="2" BirthDate="1977-06-03T00:00:00" FirstName="Kevin" MiddleName="F" LastName="Brown" />
<row EmployeeID="3" BirthDate="1964-12-13T00:00:00" FirstName="Roberto" LastName="Tamburello" />
<row EmployeeID="4" BirthDate="1965-01-23T00:00:00" FirstName="Rob" LastName="Walters" />

SELECT EmployeeID, 
BirthDate, 
FirstName, 
MiddleName, 
LastName
FROM HumanResources.Employee E 
inner join Person.Contact C
on E.ContactID = C.ContactID
ORDER BY EmployeeID, FirstName
FOR XML RAW('Employee')

<Employee EmployeeID="1" BirthDate="1972-05-15T00:00:00" FirstName="Guy" MiddleName="R" LastName="Gilbert" />
<Employee EmployeeID="2" BirthDate="1977-06-03T00:00:00" FirstName="Kevin" MiddleName="F" LastName="Brown" />
<Employee EmployeeID="3" BirthDate="1964-12-13T00:00:00" FirstName="Roberto" LastName="Tamburello" />
<Employee EmployeeID="4" BirthDate="1965-01-23T00:00:00" FirstName="Rob" LastName="Walters" />

SELECT EmployeeID, 
BirthDate, 
FirstName, 
MiddleName, 
LastName
FROM  HumanResources.Employee E
inner join Person.Contact C
on E.ContactID = C.ContactID
ORDER BY EmployeeID, FirstName
FOR XML AUTO

<E EmployeeID="1" BirthDate="1972-05-15T00:00:00">
  <C FirstName="Guy" MiddleName="R" LastName="Gilbert" />
</E>
<E EmployeeID="2" BirthDate="1977-06-03T00:00:00">
  <C FirstName="Kevin" MiddleName="F" LastName="Brown" />
</E>
<E EmployeeID="3" BirthDate="1964-12-13T00:00:00">
  <C FirstName="Roberto" LastName="Tamburello" />
</E>
<E EmployeeID="4" BirthDate="1965-01-23T00:00:00">
  <C FirstName="Rob" LastName="Walters" />
</E>

ElementName!TagNumber!AttributeName

SELECT 1    as Tag,
       NULL as Parent,
       EmployeeID as [Employee!1!EmpID],
       BirthDate  as [Employee!1!BDate],
       NULL       as [Name!2!FName],
       NULL       as [Name!2!MName],
       NULL       as [Name!2!LName]
FROM   HumanResources.Employee E
inner join Person.Contact C 
on E.ContactID = C.ContactID
UNION ALL
SELECT 2 as Tag,
       1 as Parent,
       EmployeeID,
       BirthDate,
       FirstName,
       MiddleName, 
       LastName 
FROM   HumanResources.Employee E
inner join Person.Contact C 
on E.ContactID = C.ContactID
ORDER BY [Employee!1!EmpID],[Name!2!FName]
FOR XML EXPLICIT

<Employee EmpID="1" BDate="1972-05-15T00:00:00">
  <Name FName="Guy" MName="R" LName="Gilbert" />
</Employee>
<Employee EmpID="2" BDate="1977-06-03T00:00:00">
  <Name FName="Kevin" MName="F" LName="Brown" />
</Employee>
<Employee EmpID="3" BDate="1964-12-13T00:00:00">
  <Name FName="Roberto" LName="Tamburello" />
</Employee>
<Employee EmpID="4" BDate="1965-01-23T00:00:00">
  <Name FName="Rob" LName="Walters" />
</Employee>

SELECT EmployeeID, 
BirthDate, 
FirstName, 
MiddleName, 
LastName
FROM   HumanResources.Employee E
inner join Person.Contact C 
on E.ContactID = C.ContactID
ORDER BY EmployeeID, FirstName
FOR XML PATH

<row>
  <EmployeeID>1</EmployeeID>
  <BirthDate>1972-05-15T00:00:00</BirthDate>
  <FirstName>Guy</FirstName>
  <MiddleName>R</MiddleName>
  <LastName>Gilbert</LastName>
</row>
<row>
  <EmployeeID>2</EmployeeID>
  <BirthDate>1977-06-03T00:00:00</BirthDate>
  <FirstName>Kevin</FirstName>
  <MiddleName>F</MiddleName>
  <LastName>Brown</LastName>
</row>
<row>
  <EmployeeID>3</EmployeeID>
  <BirthDate>1964-12-13T00:00:00</BirthDate>
  <FirstName>Roberto</FirstName>
  <LastName>Tamburello</LastName>
</row>
<row>
  <EmployeeID>4</EmployeeID>
  <BirthDate>1965-01-23T00:00:00</BirthDate>
  <FirstName>Rob</FirstName>
  <LastName>Walters</LastName>
</row>

Beaucoup de développeurs choisissent le plus souvent de stocker leurs données au format XML directement dans des colonnes de type VarChar ou Text, cette approche présente plusieurs inconvénients tels que :

• la non-validation des données ;
• l'incapacité des requêtes sur des nœuds spécifiques ;
• la modification des valeurs spécifiques ;
• le stockage des données qui ne respecte pas le schéma spécifié.

En gardant tout cela à l'esprit, SQL Server 2005 introduit un type de données natif appelé XML de première classe, tout comme INT ou VARCHAR. L'utilisateur peut créer une table composée d'une ou plusieurs colonnes de type XML en plus des colonnes relationnelles, les variables et les paramètres XML sont également autorisés. L'utilisation peut être faite comme avec n'importe quel type de données standard de SQL Server : type de colonnes, variables ou paramètres dans une fonction ou procédure stockée.

Vous pouvez créer une table comportant une colonne à l'aide de l'instruction usuelle CREATE TABLE

CREATE TABLE XmlTable (pk INT PRIMARY KEY, xCol XML not null)

Une table peut être également créée avec une ou plusieurs colonnes XML et avec ou sans clé primaire.

Si dans une collection de schémas XML qui décrit vos données, vous disposez de schémas de ce type, vous pouvez associer la collection de schémas à la colonne XML pour obtenir du XML typé. Les schémas XML permettent de valider les données, d'effectuer des vérifications et d'optimiser le stockage et le traitement des requêtes.

Vous pouvez créer un schéma comme suit :

CREATE XML SCHEMA COLLECTION MyCollection AS
N'<?xml version="1.0" encoding="UTF-16"?>
<xsd:schema elementFormDefault="unqualified"
    attributeFormDefault="unqualified"
    xmlns:xsd="http://www.w3.org/2001/XMLSchema" >
   <xsd:element name="SomeElement">
      <xsd:complexType mixed="false">
      <xsd:sequence>
         <xsd:element name="ChildElement" 
            type="xsd:string"/>
         <xsd:element name="SecondChildElement" 
            type="xsd:string"/>
      </xsd:sequence>                      
      </xsd:complexType>
   </xsd:element>
</xsd:schema>';

Et la syntaxe pour la création de la table doit être modifiée comme suit :

CREATE TABLE XmlTable (pk INT PRIMARY KEY, xCol XML(CONTENT MyCollection))

Bien que le type de données XML soit un type de données de haut niveau, il fonctionne comme les autres types, et plusieurs méthodes ont été prévues pour interroger et mettre à jour les données stockées dans une colonne ou variable XML.

IV-B-1. Limites des types de données XML▲

Bien que ADO.NET dans sa version 2.0 a permis de charger les données en temps que XML et sérialiser leur contenu sous la forme d'une nouvelle classe SQLXML, il présente toujours des limites comparé à SQLXML :

• il n'est pas possible de faire la mise en forme XML côté client ;
• on ne peut pas charger un code XML avec un schéma trop complexe dans un DataSet ;
• impossible de faire des requêtes Xpath sur du contenu XML.

SQLXML, en somme, est un ensemble de composants qui ont été créés pour permettre aux développeurs de travailler avec souplesse et facilité sur du contenu XML dans SQL Server plutôt qu'avec ADO.NET.

Les modèles objets des classes managées SQLXML sont mis en œuvre dans l'assembly Microsoft.Data.SqlXml(couramment sauvegarder dans le répertoire "C:\Program Files\SQLXML 4.0\bin").

Pour utiliser les classes managées SQLXML dans une application .NET, vous devez au préalable ajouter une référence à l'espace de nom Microsoft.Data.SqlXml.

Les classes qui sont disponibles sont les suivantes :

• SqlXmlCommand ;
• SqlXmlParameter ;
• SqlXmlAdapter.

VI-B-1. L'objet SqlXmlCommand▲

Provider=SQLNCLI;Data Source=Adresse_Serveur\NOM_INSTANCE; userid=USER_ID;password=PWD;Initial Catalog=NOM_BD;"

public void ReadXmlData(string ConnectionString)
            {
                    DataSet Ds = new DataSet();
                    SqlXmlCommand SqlCmd = new SqlXmlCommand(ConnectionString);
                    SqlCmd.CommandType = SqlXmlCommandType.Sql;
                    SqlCmd.CommandText = "select Demographics from"
+ "Sales.Store WHERE CustomerID = 9"; 
                    
                    XmlReader XmlR = SqlCmd.ExecuteXmlReader();
                    Ds.ReadXml(XmlR);
                
                    dataGridView1.DataSource = Ds.Tables[0];   
            }

VI-B-2. L'objet SqlXmlParameter▲

SqlParameter Param = new SqlParameter();

SqlCmd.Parameters.Add(Param);

SqlXmlParameter Param;
Param = SqlXmlCmd.CreateParameter();
Param.Value = "Valeur";

public void ReadXmlData(string ConnectionString)
          {
              DataSet Ds = new DataSet();
               SqlXmlCommand SqlCmd = new SqlXmlCommand(ConnectionString);
              SqlCmd.RootTag = "Person";
              SqlCmd.CommandType = SqlXmlCommandType.Sql;
              SqlCmd.CommandText = "SELECT FirstName, MiddleName, LastName FROM Person.Contact"
              + "WHERE LastName=? FOR XML AUTO";
              SqlXmlParameter Para;
              Para = SqlCmd.CreateParameter();
              Para.Value = "Jenkins";
              XmlReader XmlR = SqlCmd.ExecuteXmlReader();
              Ds.ReadXml(XmlR);
              dataGridView1.DataSource = Ds.Tables[0];
          }

VI-B-3. L'objet SqlXmlAdapter▲

SqlXmlAdapter(SqlXmlCommand SqlXmlCmd)

SqlXmlAdapter(
                     string commandText, 
                     SqlXmlCommandType cmdType, 
                     string connectionString
                      )

SqlXmlAdapter(
                     Stream commandStream, 
                     SqlXmlCommandType cmdType, 
                     string connectionString
                      )

<?xml version="1.0" encoding="utf-8" ?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
            xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
  <xsd:element name="Production.Product" sql:relation="Production.Product" >
    <xsd:complexType>
      <xsd:attribute name="Name"
                   sql:field="Name"
                   type="xsd:string" />
      <xsd:attribute name="ProductNumber"
                   sql:field="ProductNumber"
                   type="xsd:string" />
      <xsd:attribute name="Color"
                   sql:field="Color"
                   type="xsd:string" />
    </xsd:complexType>
  </xsd:element>
</xsd:schema>

DataSet Ds = new DataSet();
              SqlXmlCommand SqlCmd = new SqlXmlCommand(ConnectionString);
              SqlCmd.CommandText = "Production.Product[@Color='Black']";
              SqlCmd.CommandType = SqlXmlCommandType.XPath;
              SqlCmd.SchemaPath =  "Production.Product.xsd";
              SqlXmlAdapter SqlAdap = new SqlXmlAdapter(SqlCmd);
              SqlAdap.Fill(Ds);
              dataGridView1.DataSource = Ds.Tables[0];

SqlCmd.CommandText = "Production.Product[@Color='Black']";

Avec le même schéma annoté que nous avons défini dans la section précédente, nous allons maintenant utiliser Xpath pour faire des opérations de mise à jour sur nos données XML.

Le code C# de mise à jour qui permettra de modifier la colonne Name de la première ligne de données dans le DataSet est le suivant :

DataSet Ds = new DataSet();
              SqlXmlCommand SqlCmd = new SqlXmlCommand(ConnectionString);
              SqlCmd.CommandText = "Production.Product[@Color='Black']";
              SqlCmd.CommandType = SqlXmlCommandType.XPath;
              SqlCmd.SchemaPath = "Production.Product.xsd";
              SqlCmd.ClientSideXml = true;
              SqlCmd.RootTag = "Production.Product";
              SqlXmlAdapter SqlAdap = new SqlXmlAdapter(SqlCmd);
              SqlAdap.Fill(Ds);
              Ds.Tables[0].Rows[1]["Name"] = "XKT Crankarm";
              SqlAdap.Update(Ds);

Dans cet exemple, nous avons choisi de transférer la charge de travail sur le client en insérant la ligne de code suivante :

SqlCmd.ClientSideXml = true;

Ainsi, toutes les transformations XML seront effectuées côté client.

Le format DiffGram a fait son apparition dans le composant DataSat du Framework .NET.

Le UpdateGram utilise le format DiffGram pour modifier ou insérer des données dans une base de données SQL Server avec SQLXML.

L'utilisation des UpdateGrams s'avère très pratique dans des systèmes distribués et les applications qui ne peuvent pas utiliser ADO.NET, parce que l'on peut par exemple utiliser le protocole HTTP et un serveur Web pour faire des mises à jour d'une base de données SQL Server.

Un UpdateGram peut-être créé comme un message XML et envoyé à une application .NET, qui peut ensuite utiliser le composant SQLXML pour effectuer des mises à jour sur SQL Server.

Dans cette partie, sans toutefois entrer dans les détails sur l'UpdateGram ou le format DiffGram, nous verrons comment créer et utiliser un UpdateGram pour faire une mise à jour d'une table de la BD.

Le fichier XML du UpdateGram que nous allons utiliser est le suivant :

<ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram" >
  <updg:sync>
  <updg:before>
  <Production.Product 
                ProductID="317"
                Name="LL Crankarm"
               ProductNumber="CA-5965"/>
 </updg:before>
    <updg:after>
    <Production.Product
                  Name="ndbdhgdhg"
                  ProductNumber="CA-4652"/>
    </updg:after>
  </updg:sync>
</ROOT>

Les données qui doivent être modifiées seront spécifiées dans le nœud "updg:before". Ces données doivent être définies comme attributs (le nom de l'attribut est celui de la colonne de la table) d'une balise ayant pour nom celui de la table de la base de données.

Les nouvelles valeurs doivent être spécifiées dans le nœud "updg:after".

Le code C# de mise à jour des données avec UpdateGram est le suivant :

              SqlXmlCommand SqlCmd = new SqlXmlCommand(ConnectionString);
              SqlCmd.RootTag = "ROOT";
              SqlCmd.CommandStream = new FileStream("DiffGramProductSchema.xml", FileMode.Open, FileAccess.Read);
              SqlCmd.CommandType = SqlXmlCommandType.UpdateGram;
             SqlCmd.ExecuteNonQuery();

Les codes de mise à jour UpdateGram peuvent également être utilisés pour l'insertion des données dans des colonnes de type de XML.

Les modèles SQLXML sont des fichiers XML pouvant contenir des instructions SQL, des requêtes XPath et les déclarations des paramètres.

L'inclusion des requêtes SQL ou XPath dans des fichiers de modèles au lieu de les renseigner en dur dans le code, ou utiliser les procédures stockées, offre une meilleure flexibilité à l'application.

Cela permet également d'avoir une architecture plus souple, de réutiliser les formats de requêtes XML et d'utiliser le protocole HTTP pour des accès de façon sécurisée à la base de données.

Le fichier de modèle XML suivant permet de sélectionner des données dans la table Production.Product, en fonction de la valeur qui sera passée dans le paramètre Color.

<ROOT xmlns:sql='urn:schemas-microsoft-com:xml-sql'>
  <sql:header>
    <sql:param name="Color"/>
  </sql:header>
  <sql:query>
    Select Name, ProductNumber, Color FROM Production.Product
    WHERE Name=@Color FOR XML AUTO
  </sql:query>
</ROOT>

Notez que les paramètres doivent être contenus dans le nœud " sql:header ". La requête est quant à elle contenue dans le nœud " sql:query ".

L'exemple de code C# utilisant ce modèle est le suivant :

SqlXmlCommand SqlCmd = new SqlXmlCommand(ConnectionString);
              SqlCmd.CommandStream = new FileStream("ProductTemplates.xml", FileMode.Open, FileAccess.Read);
              SqlCmd.RootTag = "Product";
              SqlCmd.CommandType = SqlXmlCommandType.Template;
              //définition du paramètre
              SqlXmlParameter Para;
              Para = SqlCmd.CreateParameter();
              Para.Name = "@Color";
              Para.Value = "Black";
              //Lecture des données
              using(Stream st = SqlCmd.ExecuteStream()){
                  using(StreamReader sr = new StreamReader(st)){
                      Console.WriteLine(sr.ReadToEnd());
                  }
              
              }