Подякувати Студентському архіву довільною сумою
Admin
26.02.2023 12:38
Дякуємо, що користуєтесь нашим архівом!
SQL/CLR, Using .NET Procedural Code, UDT, TRIGGERS
Інформація про навчальний заклад
ВУЗ:
Національний університет Львівська політехніка
Інститут:
Не вказано
Факультет:
Не вказано
Кафедра:
Кафедра програмного забезпечення
Інформація про роботу
Рік:
2010
Тип роботи:
Лабораторна робота
Предмет:
Інформаційні технології
Група:
ПІ
Частина тексту файла (без зображень, графіків і формул):
МІНІСТЕРСТВО ОСВІТИ І НАУКИ УКРАЇНИ
НАЦІОНАЛЬНИЙ УНІВЕРСИТЕТ "ЛЬВІВСЬКА ПОЛІТЕХНІКА"
Інститут комп’ютерних наук та інформаційних технологій
Кафедра програмного забезпечення
ЗВІТ
До лабораторної роботи № 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, а також повертати вихідні параметри.
Ділись своїми роботами та отримуй миттєві бонуси!
0%
Оголошення від адміністратора