Nous allons aborder aujourd'hui une nouvelle fonctionnalité disponible dans SQL Server 2008, au niveau DML (Data Manipulation Language) : Les "Tables Values Parameters" (TVP)
Le concept est simple, et il résulte du constat de nombreuses demandes de la part des développeurs autour de la plateforme SQL Serveur : Avoir la possibilité de passer à une procédure stockée, un paramètre de type Table.
Depuis longtemps, nous sommes confrontés à ce problème de passage d'une quantité d'informations non prédéfinis à l'avance à une procédure stockée.
Sur SQL Serveur 2000, l'utilisation d'un bloc de type Ntext contenant du XML pouvait résoudre ce problème. Le requêtage de cet ensemble d'informations dans la procédure stockée pouvait être laborieux et sujet à beaucoup de problèmes.
En effet, parlons juste du typage des données de ce champ texte et on peut imaginer le nombre de problèmes que cela peut soulever.
Sur SQL Serveur 2005, l'utilisation du type XML, s'il est associé à un schéma, peut résoudre beaucoup de problèmes, de plus il est facile de requêter l'ensemble des informations contenues dans le champ XML via le système de requétage XPath.
Si de plus notre champ XML est associé à un schéma XSD, les problème de typage des données est alors résolu.
Demain sur SQL Serveur 2008, il ne sera plus nécessaire de passer par un champ XML, les TVP rendant cette tache encore plus aisée.
Dans cet article, nous passerons brièvement les façons de faire sous SQL 2000 puis sous SQL 2005, pour nous intéresser ensuite à l'utilisation des TVP sous SQL Serveur 2008.
Le principe est donc d'utiliser un champ XML pour traiter notre flux de données, tout en sachant que ce type n'existe pas sous SQL Serveur 2000
Sous SQL 2000, peu de solutions fiables ou élégante pour résoudre ce problème.
La seule méthode possible sous SQL 2000 est donc de faire passer un "flux" non typée que nous traiterons ensuite avec un OPENXML.
Evidemment, la procédure stockée prendra en paramètre un champ de type ntext ou encore un champ varchar avec une taille raisonnable.
Plusieurs limitations dans ce cas de figure :
· OpenXML utilise un pointeur renvoyée par la méthode sp_xml_prepare_document. Ce pointeur provient d'un wrapper COM qui est très gourmand (qui lui va parser le contenu du champ etc …)
· OpenXML ne doit pas gérer de très gros document, du fait de sa sur-gourmandise.
· Le champ passé en entrée, de type varchar ou ntext, n'est pas fortement typé. Il faudra donc supposé de sa structure interne.
L'utilisation d'un type texte et sa transformation en XML pouvant être parsé, s'effectue à l'aide de la procédure stockée système sp_xml_preparedocument.
Un pointeur est alors alloué qu'il ne faudra pas oublier de dés-allouer en fin de procédure via l'instruction sp_xml_removedocument.
Voici la procédure stockée utilisée :
Alter PROCEDURE [dbo].[sSelectContact_Shiloh]
@xml ntext= null
AS
Begin
--Exemple de ce que doit contenir notre champ XML en entrée
-- Select @xml = '<Contacts>' +
--'<Contact ContactID="7" MustBeUpdate="0" UpdatedDate="10/11/2006" /> ' +
--'<Contact ContactID="99" MustBeUpdate="0" UpdatedDate="10/11/2006" /> ' +
--'<Contact ContactID="32" MustBeUpdate="0" UpdatedDate="10/11/2006" /> ' +
--'<Contact ContactID="11" MustBeUpdate="0" UpdatedDate="10/11/2006" /> ' +
--'<Contact ContactID="7" MustBeUpdate="0" UpdatedDate="10/11/2006" /> ' +
--'<Contact ContactID="8" MustBeUpdate="0" UpdatedDate="10/11/2006" /> ' +
--'<Contact ContactID="45" MustBeUpdate="0" UpdatedDate="10/11/2006" /> ' +
--'<Contact ContactID="54" MustBeUpdate="0" UpdatedDate="10/11/2006" /> ' +
--'</Contacts>'
DECLARE @Pointer INT
EXECUTE sp_xml_preparedocument @Pointer OUTPUT,@xml
Select * from Person.Contact C
Where ContactID in (
SELECT ContactID
FROM OPENXML (@Pointer,'/Contacts/Contact')
WITH (ContactID INT, MustBeUpdate bit, UpdatedDate varchar)
)
order by C.ContactID
EXEC sp_xml_removedocument @Pointer
END
La solution n'est pas très élégante, mais a le mérite de fonctionner correctement.
L'intégration sous .Net se fait assez simplement.
Pour passer les informations nous allons tout simplement passer par un DataSet que nous sérialiserons en XML :
Notez l'utilisation du mapping sous forme d'attributs de l'ensemble des colonnes de notre DataTable. (Propriété ColumnMapping)
DataSet ds = new DataSet("Contacts");
DataTable dt = new DataTable("Contact");
ds.Tables.Add(dt);
DataColumn dc = new DataColumn("ContactID", typeof(Int32));
dc.ColumnMapping = MappingType.Attribute;
dt.Columns.Add(dc);
dc = new DataColumn("MustBeUpdate", typeof(Boolean));
dc.ColumnMapping = MappingType.Attribute;
dt.Columns.Add(dc);
dc = new DataColumn("UpdatedDate", typeof(DateTime));
dc.ColumnMapping = MappingType.Attribute;
dc.AllowDBNull = true;
dt.Columns.Add(dc);
DataRow dr = dt.NewRow();
dr[0] = 1;
dr[1] = true;
dr[2] = DateTime.Now;
dt.Rows.Add(dr);
dr = dt.NewRow();
dr[0] = 123;
dr[1] = true;
dr[2] = DateTime.Now;
dt.Rows.Add(dr);
StringBuilder sb = new StringBuilder();
StringWriter sw = new StringWriter(sb);
dt.WriteXml(sw);
Une fois notre "chaine de caractères" préparée, il ne reste plus qu'à ajouter le paramètre à la liste des paramètres de notre objet SqlCommand, et appeler notre procédure stockée :
SqlConnection myConnexion = new SqlConnection(Properties.Settings.Default.AdvConnectionString);
SqlCommand cmd = new SqlCommand();
cmd.Connection = myConnexion;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "sSelectContact_Shiloh";
SqlParameter param = new SqlParameter("@xml", SqlDbType.NText);
param.Value = sb.ToString();
cmd.Parameters.Add(param);
try
{
myConnexion.Open();
SqlDataReader dataReader = cmd.ExecuteReader();
while (dataReader.Read())
{
Console.WriteLine((string)dataReader["FirstName"]);
}
dataReader.Close();
myConnexion.Close();
}
catch (Exception exc)
{
Console.WriteLine(exc.Message);
if (myConnexion.State != ConnectionState.Closed) myConnexion.Close();
}
Console.ReadLine();
Via l'analyseur de profil, nous pouvons tracer les informations qui transitent sur notre serveur SQL 2000.
On retrouve bien en entrée, notre paramètre de type nText, qui pour nous est de type XML, mais pour SQL Serveur un "vulgaire" champ nText :
Le principal souci sous SQL Serveur 2000 était la "non gestion" des types XML nativement.
Cela a été corrigé dans la version 2005 et nous pouvons passer directement un champ te type XML, et non un champ texte classique.
L'avantage ici, qui est certes non obligatoire mais fortement conseillé, c'est d'utiliser, sous SQL Serveur 2005, un type XML qui serait validé à l'aide d'un schéma XSD.
L'intérêt ici est bien de pouvoir "fortement typer" les données en entrée de notre procédure stockée. Ainsi tout autre format ou tout type XML non valide, ne pourrait passer par notre procédure stockée.
Avant de commencer, il est nécessaire de préciser que cette étape n'est pas obligatoire pour faire fonctionner notre exemple.
L'intérêt de créer un schéma XSD est de spécifier la structure et les types de valeurs autorisées dans notre champ XML.
Pour résumé, utiliser un schéma XSD permet de fortement typer les valeurs contenu dans notre champ XML.
Pour l'exemple, nous ne créerons pas de namespace associé à notre schéma.
La création d'un schéma XML s'effectue via l'utilisation du script "Create Xml Schema Collection"
Dans notre exemple, nous allons créer un schéma qui devra vérifier que les données XML correspondent bien à ceci :
<Contacts>
<Contact ContactID="7" MustBeUpdate="0" UpdatedDate="2007-10-02T09:54:29.7039593+02:00" />
<Contact ContactID="99" MustBeUpdate="0" UpdatedDate="2007-10-02T09:54:29.7039593+02:00" />
</Contacts>
Le schéma a créé est donc le suivant :
CREATE XML SCHEMA COLLECTION ContactAdditionalXmlSchema AS '
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="Contacts">
<xs:complexType>
<xs:choice minOccurs="0" maxOccurs="unbounded">
<xs:sequence>
<xs:element name="Contact">
<xs:complexType>
<xs:attribute name="ContactID" type="xs:int" />
<xs:attribute name="MustBeUpdate" type="xs:boolean" />
<xs:attribute name="UpdatedDate" type="xs:dateTime" />
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>'
A partir de maintenant tout champ XML associé à ce schéma sera forcément valide ou sera refusé par SQL Serveur 2005.
Notre procédure stockée doit prendre en paramètre un champ de type XML, qui se doit d'être conforme à notre schéma.
L'entête de notre procédure stockée devient :
Create PROCEDURE [dbo].[sSelectContact_Yukon]
@xml xml(dbo.ContactAdditionalXmlSchema) = null
AS
Begin
End
Nous savons donc dans notre procédure stockée que nous n'utiliserons que des données "valides" et fortement typées.
Il ne reste plus qu'à "utiliser" notre champ XML à l'aide d'une requête Xpath.
Notez l'utilisation dans notre exemple d'une CTE (Common Type Expression) qui s'avère utile (et tire parti d'une nouveauté de SQL Serveur 2005) mais non obligatoire ici :
Create PROCEDURE [dbo].[sSelectContact_Yukon]
@xml xml(dbo.ContactAdditionalXmlSchema) = null
AS
Begin
With myContactsTmp(ContactID) as
(Select R.Contact.value('@ContactID', 'int') from @xml.nodes('/Contacts/Contact') as R(Contact))
Select * from Person.Contact C
Inner Join myContactsTmp T on T.ContactID = C.ContactID
order by C.ContactID
END
L'intégration sous .Net se fait assez simplement.
Nous pouvons utiliser le même principe que dans l'exemple précédent.
La seule différence se fera sur le type de paramètre attendu qui ne sera plus de type ntext (SqlDbType.NText) mais tout simplement de type XML (SqlDbType.Xml)
SqlConnection myConnexion = new SqlConnection(Properties.Settings.Default.AdvConnectionString);
SqlCommand cmd = new SqlCommand();
cmd.Connection = myConnexion;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "sSelectContact_Yukon";
SqlParameter param = new SqlParameter("@xml", SqlDbType.Xml);
param.Value = sb.ToString();
cmd.Parameters.Add(param);
try
{
myConnexion.Open();
SqlDataReader dataReader = cmd.ExecuteReader();
while (dataReader.Read())
{
Console.WriteLine((string)dataReader["FirstName"]);
}
dataReader.Close();
myConnexion.Close();
}
catch (Exception exc)
{
Console.WriteLine(exc.Message);
if (myConnexion.State != ConnectionState.Closed) myConnexion.Close();
}
Console.ReadLine();
Via l'analyseur de profil, nous pouvons tracer les informations qui transitent sur notre serveur SQL 2005.
On retrouve bien en entrée, notre paramètre de type xml :
Le principe de ces "types tables" est donc de créer un nouveau type sous Sql Serveur 2008, que nous pourrons utiliser comme variables en entrée de nos procédures stockées.
Les avantages de l'utilisation des TVP :
· Ils sont fortement typés.
· Ils peuvent être indexés, et contenir une clé primaire
· Ils peuvent être triés
· Ils réduisent fortement l'utilisation du moteur SQL : Ces types sont présents dans SL Serveur 2008 et ne nécessite aucun travail d'analyse de la part du moteur.
· Ils peuvent être requêtés comme n'importe quelle table, avec l'utilisation de jointure.
· Ils simplifient grandement le modèle de développement, que ce soit coté SQL Serveur 2008 (SQL) que coté .NET
Les restrictions :
· Les statistiques ne sont pas maintenues. Cela parait logique puisque la portée de la variable ne doit pas persister dans le temps.
· Les paramètres de type table ne peuvent être passés qu'en type READONLY:
o Pas d'insert, d'update ou delete sur une TVP.
o Pas de possibilité de passer le paramètre en Output (de toute façon, les données en READONLY n'évolueraient pas !)
Voyez donc ces TVP comme un paramètre d'entrée, et non de sortie.
Par contre si vous n'utilisez les "types table" comme un paramètre d'entrée, mais comme une simple table temporaire, vous pourrez bien sûr utiliser toutes les opérations classiques d'INSERT, UPDATE, DELETE …
La première étape est la création du type sous SQL Serveur 2008.
Il se fait assez simplement, par Transact SQL, via l'instruction Create Type :
CREATE TYPE [dbo].[TableIdentificationId] AS TABLE(
[ContactId] [int] NOT NULL,
[MustBeUpdate] [bit] NOT NULL DEFAULT (0),
[UpdatedDate] [datetime] NULL
)
Nous venons de créer un nouveau "type" dans Sql Serveur 2008.
Il est donc logique de pouvoir retrouver ce type via notre SQL Server Management Studio.
Sur le montage écran suivant, nous comparons l'ensemble des types disponibles sous SQL Serveur 2005 et sous SQL Serveur 2008 :
Sous le nœud Use-Defined Table Types, nous retrouvons bien notre nouveau type Table :
Pour tester notre type, nous pouvons créer une procédure stockée prenant en paramètre ce nouveau type :
Pour l'exemple, nous reprenons la même règle métier que les exemples précédents ("Pouvoir récupérer un ensemble de contact suivant une liste d'identifiant passée en paramètre") :
Create PROCEDURE [dbo].[selectContactsFromTable]
(
@tmpContact AS TableIdentificationId readonly
)
as
BEGIN
Select * from Person.Contact C
Inner Join @tmpContact T on C.ContactID = T.ContactId
END
Note : Vous remarquerez la simplicité de la requête par rapport aux deux précédents exemples !
Il ne reste plus qu'à tester notre procédure stockée.
Pour cela rien de plus simple, un petit script construit à la main :
DECLARE @tmpContact TableIdentificationId
Insert into @tmpContact (ContactId, MustBeUpdate,UpdatedDate) Values (1, 0, null)
Insert into @tmpContact (ContactId, MustBeUpdate,UpdatedDate) Values (12, 1, null)
Insert into @tmpContact (ContactId, MustBeUpdate,UpdatedDate) Values (156, 0, null)
Insert into @tmpContact (ContactId, MustBeUpdate,UpdatedDate) Values (123, 0, null)
Insert into @tmpContact (ContactId, MustBeUpdate,UpdatedDate) Values (113, 0, null)
Insert into @tmpContact (ContactId, MustBeUpdate,UpdatedDate) Values (145, 0, null)
EXECUTE [dbo].[selectContactsFromTable] @tmpContact
Et le résultat en image :
Si vous souhaitez retrouver les informations relatives à votre type, via une vue système, utilisez la vue Sys.Table_types
SELECT * FROM sys.table_types
Notre Type Table est crée, nous pouvons maintenant nous intéresser au code .NET.
Le principe d'utilisation d'ADO.NET reste le même, si ce n'est que le type de paramètre va encore une fois changer.
Chaque SqlParameter créé devra être de type SqlDbType.Structured, type SQL pour les TVP.
Et quelle va être la valeur passée à ce nouveau paramètre ?
Trois options disponibles à l'heure actuelle :
· DataTable
· List<SqlRecord>
· DbDataReader
La DataTable créée doit se mapper sur les colonnes de notre type.
Note : Si toutefois ce n'est pas le cas, vous pouvez toujours passer par le TableMapping classique des DataTables.
L'exemple suivant montre la création d'un paramètre de type SqlDbType.Structured et la création de la DataTable qui lui sera affectée
static void CreateParametersWith_DataTable(SqlCommand cmd)
{
SqlParameter param = new SqlParameter("@tmpContact", SqlDbType.Structured);
DataTable dtContacts = new DataTable("TableIdentificationId");
dtContacts.Columns.Add(new DataColumn("ContactId", typeof(Int32)));
dtContacts.Columns.Add(new DataColumn("MustBeUpdate", typeof(Boolean));
DataColumn dc = new DataColumn("UpdatedDate", typeof(DateTime));
dc.AllowDBNull = true;
dtContacts.Columns.Add(dc);
DataRow dr = dtContacts.NewRow();
dr[0] = 12;
dr[1] = false;
dr[2] = System.DBNull.Value;
dtContacts.Rows.Add(dr);
dr = dtContacts.NewRow();
dr[0] = 123;
dr[1] = true;
dr[2] = DateTime.Now;
dtContacts.Rows.Add(dr);
param.Value = dtContacts;
cmd.Parameters.Add(param);
}
Un SqlRecord contient un tableau de SqlMetaData, chaque SqlMetaData représentant les informations de chaque colonne.
Une fois notre SqlRecord créé, nous pouvons l'insérer dans une liste générique, comme indiqué dans l'exemple suivant :
static void CreateParametersWith_SqlRecord(SqlCommand cmd)
{
SqlParameter param = new SqlParameter("@tmpContact", SqlDbType.Structured);
SqlMetaData metaContactId = new SqlMetaData("ContactId", SqlDbType.Int);
SqlMetaData metaMustBeUpdate = new SqlMetaData("MustBeUpdate", SqlDbType.Bit);
SqlMetaData metaUpdatedDate = new SqlMetaData("UpdatedDate", SqlDbType.DateTime);
List<SqlDataRecord> lst = new List<SqlDataRecord>();
SqlDataRecord record;
record = new SqlDataRecord(new SqlMetaData[] { metaContactId, metaMustBeUpdate, metaUpdatedDate });
record.SetInt32(0, 1);
record.SetBoolean(1, false);
record.SetDBNull(2);
lst.Add(record);
param.Value = lst;
cmd.Parameters.Add(param);
}
Il ne reste plus qu'à implémenter le reste du code, qui reste tout à fait classique par rapport à ce que vous avez l'habitude d'utiliser :
static void Main(string[] args)
{
SqlConnection myConnexion = new SqlConnection(Properties.Settings.Default.AdvConnectionString);
SqlCommand cmd = new SqlCommand();
cmd.Connection = myConnexion;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "selectContactsFromTable";
// CreateParametersWith_SqlRecord(cmd);
// CreateParametersWith_DataTable(cmd);
try
{
myConnexion.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
Console.WriteLine((string)dr["FirstName"]);
}
dr.Close();
myConnexion.Close();
}
catch (Exception exc)
{
Console.WriteLine(exc.Message);
if (myConnexion.State != ConnectionState.Closed) myConnexion.Close();
}
Console.ReadLine();
}
Le résultat attendu est bien le bon.
Que se passe-t-il au niveau SQL Serveur 2008 ? Une simple trace nous donne vite la réponse.
Notez que ce résultat est le même quelque soit la solution utilisée (DataSet ou SqlRecord)
Que choisir ? DataTable ? SqlRecord ?
A l'heure actuelle, je n'ai réalisé aucune analyse fine de performance de l'une ou l'autre solution.
Mais je pense pouvoir dire sans me tromper que la DataTable passée en paramètre doit être parsée et transformée en tableau de SqlRecord par le framework .NET 3.0
Pour preuve, parmi les exemples fournis avec SQL Serveur 2008, un exemple où justement, il est question de transformation d'une DataTable en tableau de SqlRecords
Vous trouverez cet exemple sous le dossier \Engine\Programmability\CLR\SendDataSet
Nous venons de voir un nouveau type de donnée présent dans SQL Serveur 2008.
Certes il existait déjà les tables temporaires, depuis longtemps, mais aujourd'hui nous pouvons utiliser ce type comme paramètre d'une procédure stockée et il sûr que ce sera là son principal intérêt !
Bon codage !