МІНІСТЕРСТВО ОСВІТИ І НАУКИ УКРАЇНИ НАЦІОНАЛЬНИЙ УНІВЕРСИТЕТ "ЛЬВІВСЬКА ПОЛІТЕХНІКА" Інститут комп’ютерних наук та інформаційних технологій Кафедра програмного забезпечення ЗВІТ До лабораторної роботи № 5 На тему: «SQL/CLR, Using .NET Procedural Code, UDT, TRIGGERS» З дисципліни : "Бази даних ч.2" Лектор: доцент. каф. ПЗ Павич Н.Я Лабораторна робота № 5. SQL/CLR, Using .NET Procedural Code, UDT, TRIGGERS Завдання: Виконайте всі пункти і запишіть відповіді на поставлені в них питання. Створіть проект на .NET, опишіть функцію (UDF) згідно варіанту, каталогізуйте збірку та функцію у SQL Server, напишіть демонстраційний код T-SQL для використання цієї функції. Створіть користувацький тип даних (UDT) згідно варіанту, каталогізуйте збірку, і приведіть демонстраційний T-SQL код. UDT має містити щонайменше 2 поля різного типу даних (їх можна розмежувати, наприклад знаком #, $ тощо). Створіть тригер та продемонструйте його використання. Завантаження і каталогізації збірки Каталогізація збірки: CREATE ASSEMBLY METRICCONVERTER FROM 'c:\types\metricconverter.dll' GO Каталогізація функції: CREATE FUNCTION KilometersToMiles (@a float) returns float external name MetricConverter.[DM.EssentialYukon.MetricConverter].KilometersToMiles GO Спробуємо видалити оригінальний файл збірки (MetricConverter.dll), який використовувався при запуску CREATE ASSEMBLY. Збірка не створюється: Msg 6501, Level 16, State 7, Line 2 CREATE ASSEMBLY failed because it could not open the physical file "c:\types\metricconverter.dll": 2(Не удается найти указанный файл.). Спробуємо видалити збірку MetricConverter. Не вдалося – потрібно спершу видалити всі залежності: Msg 6590, Level 16, State 1, Line 5 DROP ASSEMBLY failed because 'METRICCONVERTER' is referenced by object 'KilometersToMiles'. Залежності збірки Каталогізовуємо збірку: CREATE ASSEMBLY TempFunctions FROM 'c:\types\tempfunctions.dll' GO Виконуємо наступний Transact-SQL код для вивчення SQL Server представлень метаданих: SELECT * FROM sys.assemblies GO SELECT * FROM sys.assembly_files GO

Тепер зроблю збірку «видимою»: CREATE ASSEMBLY MetricConverter from 'c:\types\MetricConverter.dll' GO ALTER ASSEMBLY MetricConverter ADD FILE FROM 'c:\types\MetricConverter.pdb' ALTER ASSEMBLY MetricConverter WITH VISIBILITY = ON GO Результат: Command(s) completed successfully. Пробую передати значення null в каталогізовану функцію: use demo1 go CREATE ASSEMBLY MetricConverter FROM 'C:\types\metricconverter.dll' go create function KilogramsToPounds (@a float) returns float external name MetricConverter.[DM.EssentialYukon.MetricConverter].KilogramsToPounds go declare @x float declare @y float set @x = null set @y = dbo.KilogramsToPounds(@x) print 'KG' if @y is null print 'NULL' print @y go Результат: Msg 6569, Level 16, State 1, Line 6 'KilogramsToPounds' failed because parameter 1 is not allowed to be null. Створюю таблицю: create table readings( ReadingDate datetime not null, City varchar(20) not null, HiTemp_Far real, HiTemp_Cen as dbo.FahrenheitToCentigrade(hitemp_far) PERSISTED ) Результат: Msg 4936, Level 16, State 1, Line 22 Computed column 'HiTemp_Cen' in table 'readings' cannot be persisted because the column is non-deterministic. Додаю перед оголошенням функції рядок [SqlFunction(DataAccess=DataAccessKind.None, IsDeterministic=true, IsPrecise=true)] Тепер запит виконується успішно. Додаю дані в таблицю: use Demo1 go INSERT readings VALUES('7/4/03', 'Boston', 75) go INSERT readings VALUES('7/5/03', 'Boston', 90)
Тепер роблю так щоб функція повертала значення null при переданні null значення: create function FahrenheitToCentigrade (@a float) returns float with RETURNS NULL ON NULL INPUT external name MetricConverter.[DM.EssentialYukon.MetricConverter].FahrenheitToCentigrade declare @x float declare @y float set @x = NULL set @y = dbo.FahrenheitToCentigrade(@x) if @y is null print 'NULL' print @y go Результат: NULL Змінюю типи вхідних і вихідних даних: [SqlFunction(DataAccess = DataAccessKind.None, IsDeterministic = true, IsPrecise = true)] public static System.Data.SqlTypes.SqlDecimal FahrenheitToCentigrade(System.Data.SqlTypes.SqlDecimal v) { return ((v-32)/9)*5; } Тепер запит повертає ціле число. Створюю нові функції: public class MyConverter { static void AddToTemp(double thetemp, double theadder) { thetemp = thetemp + theadder; } static void AddAndReturnBoth(double thetemp, double theadder, double result_f, double result_c) { result_f = thetemp + theadder; result_c = MetricConverter.FahrenheitToCentigrade(result_f); } } Тепер створюю запит зі звертанням до збережених процедур: use demo1 go drop function FahrenheitToCentigrade drop procedure AddToTemp drop procedure AddAndReturnBoth drop assembly MetricConverter go CREATE ASSEMBLY MetricConverter FROM 'C:\types\metricconverter.dll' go create function FahrenheitToCentigrade (@a float) returns float with RETURNS NULL ON NULL INPUT external name MetricConverter.[DM.EssentialYukon.MetricConverter].FahrenheitToCentigrade go CREATE PROCEDURE AddToTemp ( @a float out, @b float ) AS EXTERNAL NAME MetricConverter.[DM.EssentialYukon.MyConverter].AddToTemp go CREATE PROCEDURE AddAndReturnBoth ( @a float, @b float, @c float out, @d float out ) AS EXTERNAL NAME MetricConverter.[DM.EssentialYukon.MyConverter].AddAndReturnBoth go declare @a float declare @b float declare @c float declare @d float set @a = 10 set @b = 20 exec AddToTemp @a out, 44 print @a exec AddAndReturnBoth 22, 44, @c out, @d out print @c print @d Результат: 54 66 18.8889 UDT Реалізуємо методи Parse і ToString для підтримки конвертування в UDT від та до типу даних VARCHAR: public static ComplexNumber Parse(SqlString s) { ComplexNumber cnum = new ComplexNumber(); if (s.Value == "Null") { return ComplexNumber.Null; } string fp = @"-?([0-9]+(\.[0-9]*)?|\.[0-9]+)"; Regex ci = new Regex(@"(?<real>" + fp + @"):(?<imag>" + fp + @")i"); if (!ci.IsMatch(s.Value)) { throw new ApplicationException("bad format", null); } Match m = ci.Match(s.Value); cnum._realpart = double.Parse(m.Result("${real}")); cnum._imagpart = double.Parse(m.Result("${imag}")); return cnum; } public override string ToString() { if (IsNull) return "Null"; else return ""; } Реалізовую INullable інтерфейс: Перевизначаємо реалізацію за замовчуванням GetHashCode для повернення HashCode за значенням екземпляра: public override int GetHashCode() { return hashCode; } 1. Вказуємо, що ComplexNumber підтримує INullable інтерфейс public struct ComplexNumber: Inullable 2. Реалізовуємо метод інтерфейса INullable.IsNull public bool IsNull { get { return true; } } 3. Реалізовуємо статичну властивість з іменем Null, що повертає екземпляр NULL вашого UDT. public static ComplexNumber Null { get { return new ComplexNumber(); } } 4. Додаємо атрибут SqlUserDefinedTypeAttribute. Він використовується, щоб помітити визначення типу в збірці як призначений для користувача тип в SQL Server. Властивості цього атрибута відображають фізичні характеристики, що використовуються при реєстрації типу в SQL Server. Цей клас не може успадковуватися. [SqlUserDefinedType(Format.Native, IsByteOrdered = true, IsFixedLength = true)] [StructLayout(LayoutKind.Sequential)] [Serializable] Каталогізовуємо збірку: CREATE ASSEMBLY ComplexNumber FROM 'c:\types\ComplexNumber.dll' GO Каталогізовуємо тип даних: create type ComplexNumber external name ComplexNumber.[ComplexNumber] go Створюю таблицю та заповнюю її значеннями: create table complex_tab ( id int identity, cnumber ComplexNumber ) go -- insert rows insert INTO complex_tab values(convert(ComplexNumber, '6:8i')) insert INTO complex_tab values(convert(ComplexNumber, '2:2i')) insert INTO complex_tab values(convert(ComplexNumber, '1:3i')) Спробуємо вставити значення в не коректному форматі, наприклад, “А:5i”: insert INTO complex_tab values(convert(ComplexNumber, 'A:5i')) Результат: Msg 6522, Level 16, State 1, Line 7 A .NET Framework error occurred during execution of user-defined routine or aggregate "ComplexNumber": System.ApplicationException: bad format System.ApplicationException: в ComplexNumber.Parse(SqlString s) Запити на вибірку: select 'complex_tab content', * from complex_tab select 'select real/image', cnumber.realpart, cnumber.imagpart from complex_tab Результати запитів:

ТРИГЕРИ Наступний трігер DML відправляє клієнту повідомлення, коли хтось намагається додати або змінити дані в таблиці User. USE SportClub GO IF OBJECT_ID ('[User].MyTrigger1', 'TR') IS NOT NULL DROP TRIGGER [User].MyTrigger1; GO CREATE TRIGGER MyTrigger1 ON [User] AFTER INSERT, UPDATE AS RAISERROR ('Спроба зміни даних!!!', 16, 10); GO update [User] set name='Vasya' where userID=1 go Результат виконання запиту: Msg 50000, Level 16, State 10, Procedure MyTrigger1, Line 4 Спроба зміни даних!!! (1 row(s) affected) Наступний тригер при спробі внести дані в таблицю «Користувачі» для групи з ID 24, виводить повідомлення про неможливість додавання даних для цієї групи, і відміняє дію внесення даних. USE SportClub IF OBJECT_ID ('[User].MyTrigger3','TR') IS NOT NULL DROP TRIGGER [User].MyTrigger3; GO CREATE TRIGGER MyTrigger3 ON [User] AFTER insert AS DECLARE @groupID tinyint SELECT @groupID = inserted.groupID FROM inserted IF @groupID = 24 BEGIN RAISERROR ('Не можна вносити користувача для цієї групи', 16, 1) ROLLBACK TRANSACTION END GO insert into [User] values ('14', '24', 'shdgg', 'gggg', 'jjjj', 'kkkk', 'aaaa', 'gggg', '1111', '11-11-2011') go Результати виконання запиту: Msg 50000, Level 16, State 1, Procedure MyTrigger3, Line 10 Не можна вносити користувача для цієї групи Msg 3609, Level 16, State 1, Line 1 The transaction ended in the trigger. The batch has been aborted. Розробка власних функцій Створюємо проект на .NET – бібліотеку класів, де описуємо функції UDF. Наступні функції дозволяють надавати користувачам знижку або подвійну знижку, або відміняти знижку: using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; namespace SportLibrary { public class Sports { const double discount = 0.1; public static SqlDouble AddDiscount(SqlDouble sum) { return sum * (1 - discount); } public static SqlDouble CancelDiscount(SqlDouble sum) { return sum / (1 - discount); } public static SqlDouble AddDoubleDiscount(SqlDouble sum) { return sum * (1 - discount*2); } } } Тепер каталогізуємо цю збірку, а також функції UDF: USE SportClub GO CREATE ASSEMBLY Sports FROM 'c:\types\ClassLibrary1.dll' GO CREATE FUNCTION AddDiscount (@a float) returns float external name Sports.[SportLibrary.Sports].AddDiscount GO DECLARE @x float DECLARE @y float DECLARE @userID int SET @userID = 1 select @x = sum_month from [Payment] where userID = @userID print 'Попередня сума в місяць:' print @x print 'Поточна сума:' set @y = dbo.AddDiscount(@x) print @y update [Payment] set sum_month = @y where userID = @userID go DROP FUNCTION AddDiscount GO DROP ASSEMBLY Sports GO Результат: Попередня сума в місяць: 50 Поточна сума: 45 (1 row(s) affected) Збережені процедури Нехай маємо таблицю з даними про наявні абонементні картки. Для зручності за допомогою збережених процедур можна представити ціну картки у валюті на вибір: в доларах, євро, фунтах стерлінгів. Текст збірки в .NET: using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; namespace SportLibrary2 { public class SubscriptionAssembly { const double dollar = 7.96; const double euro = 11.38; const double pound = 12.74; public static void ToDollar(ref SqlDouble x) { x = x / dollar; } public static void ToEuro(ref SqlDouble x) { x = x / euro; } public static void ToPound(ref SqlDouble x) { x = x / pound; } } } SQL запит: USE SportClub GO CREATE ASSEMBLY SubscriptionAssembly FROM 'c:\types\ClassLibrary2.dll' GO CREATE PROCEDURE ToDollar (@a float out) AS external name SubscriptionAssembly.[SportLibrary2.SubscriptionAssembly].ToDollar GO CREATE PROCEDURE ToEuro (@a float out) AS external name SubscriptionAssembly.[SportLibrary2.SubscriptionAssembly].ToEuro GO CREATE PROCEDURE ToPound (@a float out) AS external name SubscriptionAssembly.[SportLibrary2.SubscriptionAssembly].ToPound GO DECLARE @x float DECLARE @y float DECLARE @subscriptionID int SET @subscriptionID = 1 select @x = price from Subscription where subscriptionID = @subscriptionID set @y=@x print 'Cума в гривнях:' print @x print 'Сума в доларах:' exec ToDollar @y out print @y set @y=@x print 'Сума в євро:' exec ToEuro @y out print @y set @y=@x print 'Сума у фунтах стерлінгів:' exec ToPound @y out print @y go DROP PROCEDURE ToDollar GO DROP PROCEDURE ToEuro GO DROP PROCEDURE ToPound GO DROP ASSEMBLY SubscriptionAssembly GO Результат: Cума в гривнях: 750 Сума в доларах: 94.2211 Сума в євро: 65.9051 Сума у фунтах стерлінгів: 58.8697 Висновок: .NET Framework надає середовище виконання (середовище CLR), яке виконує код і пропонує служби, що полегшують процес розробки. Середовище CLR спрощує розробку компонентів і додатків, об'єкти яких можуть працювати в різних мовах. Об'єкти, написані на різних мовах, можуть взаємодіяти один з одним, а їх поведінка може бути тісно інтегрованою. Наприклад, розробник може визначити клас, а потім на іншій мові створити похідний від нього клас або викликати метод з початкового класу. Можна також передати екземпляр класу в метод класу, написаного на іншій мові. Така інтеграція мов програмування можлива внаслідок того, що мовні компілятори і програми, які звертаються до середовища виконання, використовують систему загальних типів, визначену середовищем виконання, і дотримуються правил середовища виконання при визначенні нових типів, а також при створенні, використанні, збереженні і прив'язці до типів. Збірки є файлами динамічної бібліотеки, які використовуються в екземплярі SQL Server для розгортання функцій, збережених процедур, трігерів, визначених користувачем статистичних обчислень і визначених користувачем типів, записаних на одній з мов керованого коду, що міститься в середовищі CLR Microsoft .NET Framework, а не на мові Transact-SQL. Збірка в SQL Server є об'єктом, який посилається на модуль додатків (DLL-файл), створений в середовищі CLR .NET Framework. Збірка містить метадані класу і керований код. Передача збірки на екземпляр SQL Server — це перший крок до створення будь-якого з наступних об'єктів бази даних. В SQL Server збірки виконують наступні функції: * Містять керований код, який виконує функціональність одного або декількох об'єктів бази даних середовища CLR. * Містять метадані, які включають номер версії і культуру збірки, додатковий відкритий ключ, який унікально ідентифікує список класів збірки, методи, визначені в збірці, і архітектуру процесора збірки. * Управляють рівнем, до якого керований код може отримати доступ зовнішніх ресурсів за допомогою регулювання дозволів коду доступу. * Містять метадані про залежність від інших збірок, на які посилається збірка. Користувацькі функції полегшують роботу з обчисленнями у запитах, їх можна використовувати у скалярних виразах, а також в реалізації інших функцій та процедур. Збереженими процедурами є процедури, які не можна використовувати в скалярних виразах. На відміну від скалярних функцій, вони можуть повертати клієнту табличні результати і повідомлення, викликати інструкції мови опису даних DDL і мови обробки даних DML, а також повертати вихідні параметри.