Exporter (0) Imprimer
Développer tout
Cet article a fait l'objet d'une traduction manuelle. Déplacez votre pointeur sur les phrases de l'article pour voir la version originale de ce texte. Informations supplémentaires.
Traduction
Source
Ce sujet n'a pas encore été évalué - Évaluez ce sujet

Inscription des types définis par l'utilisateur dans SQL Server

Avant de pouvoir utiliser un type défini par l'utilisateur dans Microsoft SQL Server, vous devez l'inscrire. L'inscription d'un UDT comprend l'inscription de l'assembly et la création du type dans la base de données dans laquelle vous souhaitez l'utiliser. La portée des UDT se limite à une seule base de données. Ils ne peuvent pas être utilisés dans plusieurs bases de données à moins d'inscrire le même assembly et UDT dans chaque base de données. Une fois l'assembly de l'UDT inscrit et le type créé, vous pouvez utiliser l'UDT dans Transact-SQL et dans le code client. Pour plus d'informations, consultez Types CLR définis par l'utilisateur.

Le moyen le plus simple de déployer votre UDT consiste à utiliser Microsoft Visual Studio. Toutefois, dans des scénarios de déploiement plus complexes et pour une souplesse maximale, utilisez Transact-SQL comme discuté plus loin dans cette rubrique.

Procédez comme suit pour créer et déployer un UDT à l'aide de Visual Studio :

  1. Créez un projet Base de données dans les nœuds de langage Visual Basic ou Visual C#.

  2. Ajoutez une référence à la base de données SQL Server qui contiendra l'UDT.

  3. Ajoutez une classe Type défini par l'utilisateur.

  4. Écrivez le code d'implémentation de l'UDT.

  5. Dans le menu Générer, sélectionnez Déployer. L'assembly est alors inscrit et le type est créé dans la base de données SQL Server.

Le syntaxe Transact-SQL CREATE ASSEMBLY est utilisée pour inscrire l'assembly dans la base de données dans laquelle vous souhaitez utiliser l'UDT. L'assembly est stocké en interne dans les tables système de la base de données, et non en externe dans le système de fichiers. Si l'UDT est dépendant d'assemblys externes, ces derniers doivent également être chargés dans la base de données. L'instruction CREATE TYPE est utilisée pour créer l'UDT dans la base de données dans laquelle il sera utilisé. Pour plus d'informations, consultez CREATE ASSEMBLY (Transact-SQL) et CREATE TYPE (Transact-SQL).

Utilisation de CREATE ASSEMBLY

Le syntaxe CREATE ASSEMBLY permet d'inscrire l'assembly dans la base de données dans laquelle vous souhaitez utiliser l'UDT. Une fois l'assembly inscrit, il n'a plus de dépendances.

La création de plusieurs versions du même assembly dans une même base de données n'est pas autorisée. Toutefois, il est possible de créer plusieurs versions du même assembly dans une même base de données en fonction de la culture. SQL Server distingue les différentes versions culturelles d'un assembly par des noms différents qui sont inscrits dans l'instance de SQL Server. Pour plus d'informations, consultez la section relative à la création et à l'utilisation d'assemblys avec nom fort dans le Kit de développement logiciel du .NET Framework.

Lorsque CREATE ASSEMBLY est exécuté avec le jeu d'autorisations SAFE ou EXTERNAL_ACCESS, l'assembly est vérifié pour s'assurer qu'il est vérifiable et sécurisé. Si vous omettez de spécifier un jeu d'autorisations, le jeu SAFE est utilisé. Le code associé au jeu d'autorisations UNSAFE n'est pas vérifié. Pour plus d'informations sur les jeux d'autorisations des assemblys, consultez Conception d'assemblys.

Exemple

L'instruction Transact-SQL suivante inscrit l'assembly Point de SQL Server dans la base de données AdventureWorks, avec le jeu d'autorisations SAFE. Si la clause WITH PERMISSION_SET est omise, l'assembly est inscrit avec le jeu d'autorisations SAFE.

USE AdventureWorks;
CREATE ASSEMBLY Point
FROM '\\ShareName\Projects\Point\bin\Point.dll' 
WITH PERMISSION_SET = SAFE;

L'instruction Transact-SQL suivante inscrit l'assembly à l'aide de l'argument <assembly_bits> dans la clause FROM. Cette valeur varbinary représente le fichier sous la forme d'un flux d'octets.

USE AdventureWorks;
CREATE ASSEMBLY Point
FROM 0xfeac4 … 21ac78

Utilisation de CREATE TYPE

Une fois l'assembly chargé dans la base de données, vous pouvez créer le type à l'aide de l'instruction Transact-SQL CREATE TYPE. Le type est alors ajouté à la liste des types disponibles pour cette base de données. La portée du type se limite à la base de données ; il ne peut être utilisé que dans la base de données dans laquelle il a été créé. Si l'UDT existe déjà dans la base de données, l'instruction CREATE TYPE échoue avec une erreur.

Remarque Remarque

La syntaxe CREATE TYPE est également utilisée pour créer des types de données d'alias SQL Server natifs et devrait remplacer sp_addtype comme méthode de création de types de données d'alias. Certains arguments facultatifs de la syntaxe CREATE TYPE se rapportent à la création d'UDT et ne peuvent pas être appliqués à la création de types de données d'alias.

Pour plus d'informations, consultez CREATE TYPE (Transact-SQL).

Exemple

Dans l'exemple d'instruction Transact-SQL qui suit, le type Point est créé. EXTERNAL NAME est spécifié en utilisant la syntaxe de dénomination en deux parties de AssemblyName.UDTName.

CREATE TYPE dbo.Point 
EXTERNAL NAME Point.[Point];

L'instruction DROP TYPE supprime un UDT de la base de données active. Une fois un UDT supprimé, vous pouvez utiliser l'instruction DROP ASSEMBLY pour supprimer l'assembly de la base de données.

L'instruction DROP TYPE ne s'exécute pas dans les situations suivantes :

  • Des tables de la base de données contiennent des colonnes définies à l'aide de l'UDT.

  • Des fonctions, procédures stockées ou déclencheurs créés dans la base de données avec la clause WITH SCHEMABINDING utilisent des variables ou des paramètres de l'UDT.

Exemple

L'instruction Transact-SQL suivante doit s'exécuter dans l'ordre suivant. La table qui référence l'UDT Point doit être supprimée en premier, suivie du type et enfin de l'assembly.

DROP TABLE dbo.Points;
DROP TYPE dbo.Point;
DROP ASSEMBLY Point;

Recherche des dépendances d'un UDT

En présence d'objets dépendants, comme des tables avec des définitions de colonne UDT, l'instruction DROP TYPE échoue. Elle échoue également si des fonctions, procédures stockées ou déclencheurs créés dans la base de données à l'aide de la clause WITH SCHEMABINDING utilisent des variables ou des paramètres du type défini par l'utilisateur. Vous devez commencer par supprimer tous les objets dépendants, puis exécuter l'instruction DROP TYPE.

La requête Transact-SQL suivante recherche tous les colonnes et paramètres qui utilisent un type UDT dans la base de données AdventureWorks.

USE Adventureworks;
SELECT o.name AS major_name, o.type_desc AS major_type_desc
     , c.name AS minor_name, c.type_desc AS minor_type_desc
     , at.assembly_class
  FROM (
        SELECT object_id, name, user_type_id, 'SQL_COLUMN' AS type_desc
          FROM sys.columns
     UNION ALL
        SELECT object_id, name, user_type_id, 'SQL_PROCEDURE_PARAMETER'
          FROM sys.parameters
     ) AS c
  JOIN sys.objects AS o
    ON o.object_id = c.object_id
  JOIN sys.assembly_types AS at
    ON at.user_type_id = c.user_type_id;

Vous ne pouvez pas modifier un UDT qui a été créé dans une base de données SQL Server. Vous pouvez toutefois modifier l'assembly sur lequel repose ce type. Dans la plupart des cas, vous devez supprimer l'UDT de la base de données à l'aide de l'instruction Transact-SQL DROP TYPE, apporter des modifications à l'assembly sous-jacent et le recharger à l'aide de l'instruction ALTER ASSEMBLY. Vous devez ensuite recréer l'UDT et tout objet dépendant.

Exemple

L'instruction ALTER ASSEMBLY est utilisée une fois que vous avez modifié le code source dans votre assembly UDT et l'avez recompilé. Elle copie le fichier .dll sur le serveur et le lie au nouvel assembly. Pour connaître la syntaxe complète, consultez ALTER ASSEMBLY (Transact-SQL).

L'instruction Transact-SQL ALTER ASSEMBLY suivante recharge l'assembly Point.dll à partir de l'emplacement spécifié sur le disque.

ALTER ASSEMBLY Point
FROM '\\Projects\Point\bin\Point.dll'

Utilisation de l'instruction ALTER ASSEMBLY pour ajouter le code source

La clause ADD FILE de la syntaxe ALTER ASSEMBLY n'est pas présente dans CREATE ASSEMBLY. Vous pouvez l'utiliser pour ajouter le code source ou tout autre fichier associé à un assembly. Les fichiers sont copiés depuis leur emplacement d'origine et stockés dans les tables système de la base de données. Le code source et autres fichiers est toujours à portée de main dans l'éventualité où vous deviez recréer ou documenter la version actuelle de l'UDT.

L'instruction Transact-SQL ALTER ASSEMBLY suivante ajoute le code source de la classe Point.cs pour l'UDT Point. Le texte contenu dans le fichier Point.cs est alors copié et stocké dans la base de données sous le nom PointSource.

ALTER ASSEMBLY Point
ADD FILE FROM '\\Projects\Point\Point.cs' AS PointSource;

Les informations relatives à l'assembly sont stockées dans la table sys.assembly_files dans la base de données où l'assembly a été installé. La table sys.assembly_files contient les colonnes suivantes.

assembly_id

Identificateur défini pour l'assembly. Ce numéro est affecté à tous les objets se rapportant au même assembly.

name

Nom de l'objet.

file_id

Numéro identifiant chaque objet (le premier objet associé à un assembly_id ayant la valeur 1). Si plusieurs objets sont associés au même assembly_id, chaque valeur file_id suivante est incrémentée de 1.

content

Représentation hexadécimale de l'assembly ou du fichier.

Vous pouvez utiliser la fonction CAST ou CONVERT pour convertir le contenu de la colonne content en texte lisible. La requête suivante convertit le contenu du fichier Point.cs en texte lisible, en utilisant le nom indiqué dans la clause WHERE pour limiter le jeu de résultats à une ligne unique.

SELECT CAST(content AS varchar(8000)) 
  FROM sys.assembly_files 
  WHERE name='PointSource';

Si vous copiez et collez les résultats dans un éditeur de texte, vous pouvez constater que les sauts de ligne et les espaces qui existaient dans l'original ont été conservés.

Lors de la planification de votre implémentation d'UDT, identifiez les méthodes qui sont nécessaires dans l'assembly lui-même de l'UDT, ainsi que celles qui doivent être créées dans des assemblys distincts et implémentées en tant que fonctions définies par l'utilisateur ou procédures stockées. Le fait de séparer les méthodes dans des assemblys distincts vous permet de mettre à jour le code sans affecter les données qui peuvent être stockées dans une colonne UDT d'une table. Vous pouvez modifier les assemblys d'UDT sans supprimer de colonnes UDT et autres objets dépendants uniquement lorsque la nouvelle définition peut lire les valeurs précédentes et que la signature du type ne change pas.

Le fait de séparer le code de procédure susceptible de changer du code requis pour implémenter l'UDT simplifie considérablement la maintenance. Par ailleurs, le fait de n'inclure que le code qui est nécessaire au fonctionnement de l'UDT et de créer des définitions d'UDT aussi simples que possible réduit le risque que l'UDT lui-même ait besoin d'être supprimé de la base de données pour les révisions de code ou la résolution de bogues.

UDT Currency et fonction de conversion de devise

L'UDT Currency de l'exemple de base de données AdventureWorks illustre un exemple de méthode recommandée pour structurer un UDT et ses fonctions associées. L'UDT Currency est utilisé pour traiter les données monétaires reposant sur le système monétaire d'une culture particulière et permet de stocker différents types de devises, comme les dollars ou les euros. La classe de l'UDT expose un nom de culture sous forme de chaîne, et une somme d'argent sous forme de type de données decimal. Toutes les méthodes de sérialisation nécessaires sont contenues dans l'assembly qui définit la classe. La fonction qui implémente la conversion de devise entre une culture et une autre est implémentée en tant que fonction externe nommée ConvertCurrency. Cette fonction se trouve dans un assembly distinct. La fonction ConvertCurrency extrait le taux de conversion d'une table de la base de données AdventureWorks. Si la source des taux de conversion est amenée à être modifiée ou si le code est susceptible d'être modifié de quelque autre manière, l'assembly peut facilement être modifié sans affecter l'UDT Currency.

Le code de l'UDT Currency et des fonctions ConvertCurrency peut être consulté en installant les exemples de CLR (Common Language Runtime).

Utilisation d'UDT dans plusieurs bases de données

Par définition, la portée des UDT se limite à une seule base de données. Autrement dit, un UDT défini dans une base de données ne peut pas être utilisé dans une définition de colonne d'une autre base de données. Pour utiliser des UDT dans plusieurs bases de données, vous devez exécuter les instructions CREATE ASSEMBLY et CREATE TYPE dans chaque base de données sur des assemblys identiques. Les assemblys sont considérés comme identiques s'ils partagent les mêmes nom, nom fort, culture, version, jeu d'autorisations et contenu binaire.

Une fois l'UDT inscrit et accessible dans les deux bases de données, vous pouvez convertir une valeur UDT d'une base de données en vue de l'utiliser dans une autre. Des UDT identiques peuvent être utilisés dans plusieurs bases de données dans les scénarios suivants :

  • appel de procédures stockées définies dans des base de données différentes ;

  • interrogation de tables définies dans des bases de données différentes ;

  • sélection de données UDT dans une colonne UDT de table de base de données et insertion dans une seconde base de données avec une colonne UDT identique.

Dans ces situations, toute conversion requise par le serveur se produit automatiquement. Vous ne pouvez pas effectuer explicitement ces conversions à l'aide des fonctions Transact-SQL CAST ou CONVERT.

Notez qu'aucune mesure n'est nécessaire pour utiliser des UDT lorsque le moteur de base de données SQL Server crée des tables de travail dans la base de données système tempdb. Cela inclut la gestion des curseurs, des variables de table et des fonctions table définies par l'utilisateur qui incluent des UDT et qui utilisent tempdb de manière transparente. Toutefois, si vous créez explicitement une table temporaire dans tempdb qui définit une colonne UDT, l'UDT doit être inscrit dans tempdb de la même manière que pour une base de données utilisateur.

Cela vous a-t-il été utile ?
(1500 caractères restants)
Merci pour vos suggestions.

Ajouts de la communauté

AJOUTER
Afficher:
© 2014 Microsoft. Tous droits réservés.