Cette page vous a-t-elle été utile ?
Votre avis sur ce contenu est important. N'hésitez pas à nous faire part de vos commentaires.
Vous avez d'autres commentaires ?
1500 caractères restants
Exporter (0) Imprimer
Développer tout

Enregistrement de types définis par l'utilisateur dans SQL Server

.NET Framework 2.0

Pour utiliser un type défini par l'utilisateur (UDT) dans SQL Server 2005, vous devez l'enregistrer. L'enregistrement d'un UDT est un processus en deux étapes d'enregistrement de l'assembly et de création du type dans la base de données dans laquelle vous souhaitez l'utiliser. Les UDT s'appliquent à une seule base de données et ne peuvent pas être utilisées dans plusieurs bases de données, à moins que l'assembly identique et l'UDT ne soient enregistrées avec chaque base de données. Une fois l'assembly UDT enregistré et le type créé, vous pouvez utiliser l'UDT dans Transact-SQL et dans un code client. Pour plus d'informations, voir « Types CLR définis par l'utilisateur » dans la documentation en ligne de SQL Server 2005.

Utilisation de Visual Studio pour déployer des UDT

Le moyen le plus facile de déployer votre UDT consiste à utiliser Visual Studio. Toutefois, l'utilisation de Visual Studio n'offre pas autant de flexibilité que l'utilisation de Transact-SQL lorsqu'il s'agit de déployer un UDT. Pour des scénarios de déploiement plus complexes et une flexibilité maximale, utilisez Transact-SQL en procédant de la manière décrite dans les sections suivantes.

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 2005 qui contiendra l'UDT.

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

  4. Écrivez un code pour implémenter l'UDT.

  5. Dans le menu Construire, sélectionnez Déployer. Cette action enregistre l'assembly et crée le type dans la base de données SQL Server 2005.

Utilisation de Transact-SQL pour déployer des UDT

La syntaxe Transact-SQL CREATE ASSEMBLY est utilisée pour enregistrer l'assembly dans la base de données dans laquelle vous souhaitez utiliser l'UDT. Il est stocké au niveau interne dans les tables système de la base de données, pas au niveau externe dans le système de fichiers. Si l'UDT dépend d'assemblys externes, ces derniers doivent également être chargés dans la base de données. L'instruction CREATE TYPE permet de créer l'UDT dans la base de données dans laquelle il doit être utilisé. Pour plus d'informations sur la syntaxe CREATE ASSEMBLY et CREATE TYPE, voir la documentation en ligne de SQL Server 2005.

Utilisation de CREATE ASSEMBLY

La syntaxe Transact-SQL CREATE ASSEMBLY est utilisée pour enregistrer l'assembly dans la base de données dans laquelle vous souhaitez utiliser l'UDT. Il est stocké au niveau interne dans les tables système de la base de données, pas au niveau externe dans le système de fichiers. Une fois l'assembly enregistré, il n'a pas de dépendances.

La création de plusieurs versions du même assembly dans une base de données n'est pas autorisée. En revanche, il est possible de créer plusieurs versions Culture du même assembly dans une base de données. SQL Server distingue plusieurs versions Culture d'un assembly à l'aide des différents noms enregistrés dans l'instance de SQL Server. Pour plus d'informations, voir Création et utilisation d'assemblys avec nom fort.

Lors de l'exécution de CREATE ASSEMBLY avec les ensembles d'autorisation SAFE ou EXTERNAL_ACCESS, l'assembly est contrôlé pour s'assurer qu'il est vérifiable et de type SAFE. Si vous omettez de spécifier un ensemble d'autorisations, SAFE est supposé par défaut. Un code avec l'ensemble d'autorisations UNSAFE n'est pas contrôlé. Pour plus d'informations sur les ensembles d'autorisations d'assembly, voir « Designing Assemblies » dans la documentation en ligne de SQL Server.

Exemple

L'instruction Transact-SQL suivante enregistre l'assembly Point dans SQL Server, dans la base de données AdventureWorks, avec l'ensemble d'autorisations SAFE. En cas d'omission de la clause WITH PERMISSION_SET, l'assembly est enregistré avec l'ensemble d'autorisations SAFE.

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

L'instruction Transact-SQL suivante enregistre l'assembly avec l'argument <assembly_bits> dans la clause FROM. Cette valeur varbinary représente le fichier comme un flux d'octets.

USE AdventureWorks;
CREATE ASSEMBLY Point
FROM 0xfe…

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. Cette action ajoute le type à la liste des types disponibles pour cette base de données. Le type s'applique à une base de données et 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 et génère un message d'erreur.

NoteRemarque

La syntaxe CREATE TYPE est également utilisée pour créer des UDT SQL Server natifs et est destinée à remplacer sp_addtype comme moyen de créer des UDT. Certains arguments facultatifs de la syntaxe CREATE TYPE font référence à la création d'UDT et ne sont pas applicables à la création d'UDT (tel qu'un type de base).

Pour une présentation complète de la syntaxe CREATE TYPE, voir « CREATE TYPE (Transact-SQL) » dans la documentation en ligne de SQL Server.

Exemple

L'instruction Transact-SQL suivante crée le type Point. EXTERNAL NAME est spécifié à l'aide de la syntaxe de dénomination en deux parties AssemblyName.UDTName.

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

Suppression d'un UDT de la base de données

L'instruction DROP TYPE supprime un UDT de la base de données actuelle. Une fois l'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 :

  • Tables dans la base de données, qui contiennent des colonnes définies à l'aide de l'UDT.

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

Exemple

L'instruction Transact-SQL suivante doit s'exécuter dans l'ordre suivant. Premièrement, la table faisant référence à l'UDT Point doit être supprimée, puis le type, puis enfin l'assembly.

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

Recherche de dépendances d'UDT

S'il y a des objets dépendants, tels que des tables contenant des définitions de colonne UDT, l'instruction DROP TYPE échoue. Elle échoue également s'il y a des fonctions, procédures stockées ou déclencheurs créés dans la base de données avec la clause WITH SCHEMABINDING et si ces routines utilisent des variables ou des paramètres du type défini par l'utilisateur. Vous devez d'abord supprimer tous les objets dépendants, puis exécuter l'instruction DROP TYPE.

L'instruction Transact-SQL suivante localise la totalité des colonnes et des paramètres utilisant un 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;

Maintenance des UDT

Vous ne pouvez pas modifier un UDT après qu'il a été créé dans une base de données SQL Server, même si vous pouvez modifier l'assembly sur lequel le type est basé. Dans la plupart des cas, vous devez supprimer l'UDT de la base de données avec 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 tous les objets dépendants.

Exemple

L'instruction ALTER ASSEMBLY est utilisée après que vous avez apporté des modifications au code source dans votre assembly UDT et l'avez recompilé. Elle copie le .dll sur le serveur, puis rétablit la liaison avec le nouvel assembly. Pour la syntaxe complète, voir « ALTER ASSEMBLY » dans la documentation en ligne de SQL Server 2005.

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 d'ALTER ASSEMBLY pour ajouter un code source

La clause ADD FILE dans la syntaxe ALTER ASSEMBLY ne figure pas dans CREATE ASSEMBLY. Vous pouvez l'utiliser pour ajouter du code source ou tout autre fichier associé à un assembly. Les fichiers sont copiés à partir de leur emplacement original et stockés dans des tables système dans la base de données. Cela vous assure de toujours disposer d'un code source ou d'autres fichiers si jamais vous devez recréer ou documenter la version actuelle de l'UDT.

L'instruction Transact-SQL ALTER ASSEMBLY suivante ajoute le code source de classe Point.cs pour l'UDT Point. Cette opération copie le texte contenu dans le fichier Point.cs et le stocke dans la base de données sous le nom « PointSource ».

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

Les informations de l'assembly sont stockées dans la table sys.assembly_files de la base de données dans laquelle 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 attribué à tous les objets relatifs au même assembly.

name

Le nom de l'objet.

file_id

Nombre identifiant chaque objet. Le premier objet associé à un assembly_id donné reçoit 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 name de la clause WHERE pour limiter l'ensemble de résultats à une simple ligne.

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 voyez que les sauts de ligne et les espaces présents dans l'original ont été conservés.

Gestion d'UDT et d'assemblys

Lors de la planification de votre implémentation des UDT, vous devez envisager les méthodes nécessaires dans l'assembly UDT proprement dit, ainsi que les méthodes à créer dans des assemblys séparés et implémentées comme fonctions définies par l'utilisateur ou procédures stockées. La séparation de méthodes en assemblys séparés vous permet de mettre à jour le code sans que cela n'affecte les données pouvant être stockées dans une colonne UDT d'une table. Vous pouvez modifier des assemblys UDT sans supprimer des colonnes UDT et d'autres objets dépendants uniquement si la nouvelle définition peut lire les valeurs précédentes et si la signature du type ne change pas.

La séparation du code de procédure susceptible de changer du code requis pour implémenter l'UDT simplifie considérablement la maintenance. L'inclusion uniquement du code nécessaire pour que l'UDT fonctionne et le maintien de définitions d'UDT aussi simples que possible réduit le risque de devoir supprimer l'UDT proprement dit de la base de données pour des révisions de code ou des corrections de bogue.

UDT Currency et fonction de conversion de devises

L'UDT Currency dans l'exemple de base de données AdventureWorks fournit un exemple utile de la méthode recommandée pour structurer un UDT et ses fonctions associées. L'UDT Currency est utilisé pour gérer les liquidités sur la base du système monétaire d'une culture particulière et permet de stocker différents types de devise, tels que des euros, des dollars américains, etc. La classe UDT expose un nom de culture comme chaîne et un montant comme type de données decimal. Toutes les méthodes de sérialisation nécessaires sont contenues dans l'assembly définissant la classe. La fonction qui implémente la conversion de devise d'une culture à l'autre est implémentée comme une fonction externe nommée ConvertCurrency, et cette fonction se trouve dans un assembly séparé. La fonction ConvertCurrency opère en extrayant le taux de conversion d'une table dans la base de données AdventureWorks. Si la source des taux de conversion ou le code existant change, l'assembly peut être aisément modifié sans que cela n'affecte l'UDT Currency.

La liste de codes pour l'UDT Currency et les fonctions ConvertCurrency peut être trouvée en installant les exemples de CLR. Pour plus d'informations, voir « Installing Samples » dans la documentation en ligne de SQL Server.

Utilisation d'UDT dans des bases de données

Les UDT s'appliquent par définition à une seule base de données. C'est pourquoi un UDT défini dans une base de données ne peut pas être utilisé dans une définition de colonne dans une autre base de données. Pour utiliser des UDT dans plusieurs bases de données, vous devez utiliser les instructions CREATE ASSEMBLY et CREATE TYPE de chaque base de données sur des assemblys identiques. Les assemblys sont considérés comme identiques s'ils ont les mêmes nom, nom fort, culture, version, ensemble d'autorisations et contenu binaire.

Une fois l'UDT enregistré et accessible dans les deux bases de données, vous pouvez en convertir la valeur à partir d'une UDT pour l'utiliser dans une autre. Vous pouvez utiliser des UDT identiques dans plusieurs bases de données dans les scénarios suivants :

  • Appel d'une procédure stockée définie dans plusieurs bases de données.

  • Interrogation de tables définies dans plusieurs bases de données.

  • Sélection de données UDT d'une colonne UDT de table de base de données et son 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 pourrez pas effectuer les conversions explicitement à l'aide des fonctions Transact-SQL CAST ou CONVERT.

Notez que vous n'avez rien à faire pour utiliser des UDT dans des scénarios où le moteur 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 de valeur tabulaire définies par l'utilisateur qui incluent des UDT et utilisent de façon transparente tempdb. Toutefois, si vous créez explicitement une table temporaire dans tempdb définissant une colonne UDT, l'UDT devra être enregistré dans tempdb de la même manière qu'une base de données utilisateur.

Voir aussi

Ajouts de la communauté

AJOUTER
Microsoft réalise une enquête en ligne pour recueillir votre opinion sur le site Web de MSDN. Si vous choisissez d’y participer, cette enquête en ligne vous sera présentée lorsque vous quitterez le site Web de MSDN.

Si vous souhaitez y participer,
Afficher:
© 2015 Microsoft