Створення типів даних в Visual Studio 2005

Збережені процедури, функції і тригери CLR і ті ж обєкти Т-SQL мають більше подібностей, ніж відмінностей У загальному випадку ці типи CLR будуть складатися з одного методу На противагу цьому для користувача типи (далі UDT) і консолідації (далі UDA) взагалі не схожі на збережені процедури, функції і тригери CLR

Обєкти UDT і UDA можуть бути структурами або класами Слід зауважити, що використання структур в загальному випадку переважніше, оскільки за визначенням структури зберігаються в стеку, а типи посилань – в купі обєкта UDT і UDA, оформлення як клас, найімовірніше, будуть мати тип посилання, що сильніше вплине на продуктивність, ніж на використання структур Як би там не було, UDT і UDA будуть містити безліч членів і повинні задовольняти великому числу критеріїв проектування У частині цієї глави ми зосередимо увагу на типах збережених процедур, функцій і тригерів, заснованих на методах

Додаткова Більш детальна інформація про користувальницьких типах наведена в главі 29

інформація

Створення проекту CLR

Перші кроки у створенні компонента інтеграції CLR в Visual Studio 2005 практично ідентичні, незалежно від того, який тип компонента створюється Наприклад, навіть незважаючи на те, що за замовчуванням проект інтеграції CLR не вимагає наявності підключення до бази даних, оскільки в майбутньому він буде існувати в самій базі, Visual Studio потребують визначити його, оскільки він буде використовуватися на етапах розробки і налагодження компонента Визначте в проекті то місце розташування бази даних, в якій компонент буде тестуватися, вже на етапі створення проекту

Після того як джерело даних буде визначено, в будь-який момент можна натиснути на ньому правою кнопкою миші у вікні Server Explorer, щоб відкрити контекстне меню, що містить команди налагодження додатка, обєктів Т-SQL і типів інтеграції CLR Користувач також повинен мати дозволу до збереженої процедурою sp_enable_sql_debug (що знаходиться в базі даних master) та дозволу зміни (ALTER) для всіх обєктів, налагодження яких буде проводитися в базі даних Слід зазначити, що тільки адміністратори бази даних мають дозвіл на налагодження типів CLR, містять пропозицію EXECUTE AS або використовують сертифікати до того ж на сервері одночасно може регламентуватиме тільки один тип Під час сесії налагодження заморожуються всі інші потоки CLR Це, природно, не можна назвати ідеальним варіантом для спільно використовуваного сервера, якщо саме на ньому виконується тестування інтеграції Налагодження компонентів CLR більше підходить для відокремленої середовища тестування, в якій сервер бази даних є екземпляром, виділеним для монопольного використання одним розробником

А Розгортання і тестування інтеграції CLR вимагає установки SQL

Server 2005 і Visual Studio 2005 Налагодження і спільна робота під час Налагодити-Зг ки не особливо добре підтримуються, якщо використовується загальна середу SQL

Server Найкращою конфігурацією для розробника є наявність на ло-Перевірено кальной робочої станції як Visual Studio 2005, так і повноцінної версії SQL Server Developer Edition Програмісти, що працюють над компонентами для розподілених або федеративних додатків баз даних, можуть розглянути як варіант локальну робочу станцію з ресурсами, адекватними для підтримки примірників віртуальних серверів, моделюючих реальну виробничу середу У такій віртуальному середовищі бажано використовувати іменовані екземпляри серверів, щоб вже на початковій стадії проектування врахувати всі угоди системи безпеки

Після того як джерело даних буде налаштований, відкриється діалогове вікно, показане на рис 271 Виберіть тип проекту бази даних для мови, який буде використаний для написання програмного коду На даному етапі особливу увагу приділіть іменуванню проекту У деяких ситуаціях в одному проекті доводиться тримати безліч типів інтеграції, в інших же кожен тип повинен бути виділений в окремий проект При прийнятті рішення слід враховувати кількість розробників, складність типів і обсяг програмного коду, спільно використовуваного різними типами

Рис 271 Створення проекту інтеграції CLR в Visual Studio 2005 Після того як виберіть тип бази даних для використовуваної мови програмування на лівій панелі, на правій виберіть використовуваний шаблон проекту

Після створення проекту шаблон для даного типу може бути доданий в проект або з допомогою клацання правої кнопки миші на його імені в контекстному меню Solution Explorer і вибору з нього пункту Add, або з допомогою відповідної команди меню Project програми Visual Studio У контекстному меню вікна Solution Explorer (рис 272) можна вибрати пункт Properties, щоб відкрити діалогове вікно Project Desigher У цьому вікні можна визначити суворе імя, задати рівень дозволів CAS для складання, а також налаштувати параметри компіляції та розгортання проекту

Якщо збірка буде використовуватися декількома додатками, компанія Microsoft рекомендує привласнювати їй суворе імя Суворе імя створює глобальний унікальний ідентифікатор для збірки і маніфесту, що складається з текстового імені, номера версії, необовязковою регіональної інформації, загальної частини ключової пари public / private і цифрового підпису Так як навряд чи існують практичні підстави заборони використання бази даних різними додатками, всі компоненти інтеграції CLR краще строго іменувати

Ключова пара public / private створюється за допомогою утиліти командного рядка snexe Вона поміщається в текстовий файл з розширенням snk, як якщо б це був контейнер постачальника служби криптографії (CSP) Файл ключової пари або контейнер CSP може бути згенерований, а підпис збірки – завершена під час компіляції на основі інформації, введеної у вкладках Project Designers Signing і Publish У корпоративних умовах слід приділити особливу увагу стратегії підписування практично у всіх випадках буде переважніше мати єдиний ключ, спільно використовуваний усіма додатками У той же час у певних ситуаціях можуть бути виділені персональні ключі і використано відкладене підписування збірок У цьому сценарії збірка підписується одним або декількома довіреними особами, щоб гарантувати, що тільки потрібні складання будуть завантажені у виробничий сервер

У контекстному меню Solution Explorer доступні команди Build (Побудувати), Clean (Очистити), Rebuild (Перебудувати) і Deploy (Розгорнути) Ті ж команди доступні і в меню Build програми Visual Studio

Запустіть Profiler на джерелі даних SQL Server, який буде використаний під час розгортання, щоб побачити інструкції CREATE ASSEMBLY і CREATE <об'єкт>, згенеровані Visual Studio Можливість розгортання є потужним інструментом розробки, який генерує інструкції створення та видалення збирання, а також окремих типів на сервері розробки Навряд чи цей метод виявиться придатним для розгортання обєктів CLR у виробничому середовищі В останньому випадку потрібно за допомогою команди Create створити бібліотеки Dll з кодом MSIL і помістити ці файли в мережевій каталог Після цього дані збірки можуть бути завантажені з допомогою інструкцій CREATE ASSEMBLY сценаріїв

Повертаючись до рис 272, відзначимо, що в проект можна додати два типи посилань: посилання на існуючий компонент, який буде використаний в даному проекті, і посилання на Web-службу

Додавання посилання на компонент типу інтеграції CLR запропонує вам тільки частина можливостей, доступних іншим додаткам NET На рис 273 показаний цей скорочений список доступних загальних компонентів NET, придатних до використання в CLR SQL Server Посилання на компоненти, створені в локальних проектах, також можуть бути додані у вкладці Projects цього діалогового вікна Щоб локальні проекти стали доступними в діалоговому вікні References, шлях до них повинен бути попередньо прописаний у вікні Project Designer

Puc 273 Додавання посилання в проект SQL Server

У цьому вікні відображаються всі інші компоненти збірки в просторі проекту і доступні збірки системних компонентів

Посилання на Web-службу XML створюють у проекті проксі-сервер для файлів wsdl і asmx Цей сервер дозволить виконувати інструкції на стадії розробки проекту, а також стати компоненту інтеграції CLR клієнтом для зазначеної Web-служби Файл визначення Web-служби може існувати на локальному сервері, в локальній мережі або в Інтернеті Як тільки Web-служба буде додана в проект, члени, згенеровані в проксі-сервері, можна переглянути у вікні Object Explorer Потенціал використання Web-служб збереженими процедурами CLR несказанно великий

Будьте обережні при використанні Web-служб на стадії розробки Сгене-Віаманіе рировать проксі-сервер може виконувати програмний код на локальному компютері в контексті безпеки користувача, зареєстрованого у момент відкриття IDE програми Visual Studio Залежно від того, що робить Web-служба, локальна робоча станція може бути під час її роботи надмірно завантажена

Допсдшіеяічая Додаткові деталі використання Web-служб в SQL Server описуються ^ формація в главах 31 і 32

Програмування збереженої процедури CLR

Збережені процедури є найбільш гнучкою формою типів інтеграції CLR Не треба й говорити, що вони – також найпростіший з усіх типів Збережені процедури є найбільш придатними обєктами для використання рівнів захисту CAS EXTERNAL_ ACCESS і UNSAFE Незважаючи на те що в деяких сценаріях переважніше задіяти функції або, що менш ймовірно, тригери, збережені процедури CLR реалізують більш природний канал інтеграції в базу даних гетерогенних сховищ Ризик використання функцій і тригерів полягає в тому, що при залученні їх у потенційно довгограючі сценарії в середовищі виконання з привілеями це стане обмежуючим фактором в їх використанні в якості інтерфейсу між ядром бази даних і зовнішнім світом На противагу цьому збережені процедури NET можуть легко і безпечно взаємодіяти з файловою системою

Весь програмний код, що описується в цій главі і що стосується про-В ектов Visual Studio 2005, можна завантажити з Web-сайту книги за адресою www ^ Ч ^ / ^ Мережі SQLServerBiblecom

Розглянемо збережену процедуру CLR, яка служить утилітою експорту будь-якого набору даних або їх колекцій у файл з роздільниками CSV У цьому прикладі буде продемонстровано безліч щойно описаних концепцій Сама процедура виконує наступне

І Читає дані, що вимагають доступу в контексті підключення

■ Виконує запис у файлову систему, що тягне за собою використання рівня захисту З AS EXTERNAL ACCESS

■ Виводить запитувачу інформаційні повідомлення, реалізуючи можливість використання класу каналу

Процедура приймає два аргументи

■ Допустимий запит Т-SQL, функція або збережена процедура, яка повертає один або більше результуючих наборів даних

■ Припустиме імя файлу, включаючи шлях до нього

У наведеному нижче прикладі продемонстровані наступні частини цієї збереженої процедури Спочатку створюється шаблон збереженої процедури Після додавання шаблону збереженої процедури в новий проект бази даних і додавання простору імен System 10 в збірку методу збереженої процедури може бути присвоєно імя, також можуть бути визначені аргументи Потім для доступу до локальних даних буде необхідно використовувати контекст внутрішнього підключення процесу Так як зовнішня процедура повинна мати можливість отримати доступ до даних за допомогою виклику збереженої процедури та / або інструкції SQL, слід реалізувати обєкт, здатний виконати обидві ці операції Далі може виявитися корисним інформувати про виконуваному дії зухвалу процедуру Це легко реалізувати за допомогою обєкта SQLPipe і форматування повідомлення Наприклад, значення вхідних аргументів можуть бути передані як інформаційне повідомлення

Зверніть увагу на те, що аргументи, оголошені з рідним типом SqlString, повинні бути перетворені перед використанням в операціях NET, в той час як рядок NET (тип string) може використовуватися і без перетворення В останньому випадку перетворення виконується неявно при виклику процедури Таким чином, витрат на виконання перетворення не уникнути, хоча трудомісткість програмування можна трохи знизити

Перед використанням обєкта Sql Connect ion він повинен бути відкритий До того ж перед записом результатів у файл повинен бути відкритий потік до цього файлу Тут ми вперше використовуємо простір імен System 10, раніше доданий в збірку Більше того, так як процедура використовує ресурси поза контекстом SQL Server, в якому запущена, рівень захисту повинен бути встановлений в EXTERNAL ACCESS, при цьому повинні бути задані необхідні права та дозволи для доступу до файлової системи

Як тільки зєднання буде відкрито і файл готовий до отримання даних, інструкція може виконуватися Існує маса методів виконання інструкцій Можна наповнювати набір даних DataSet, що знаходиться в памяті, щоб направити потоком результати у форматі XML а можна просто отримати скалярний значення Кожна з цих операцій вимагає наявності спеціального методу У прикладі експорту в файл CSV результат повинен бути спрямований у потік як односторонній результуючий набір даних або обєкт DataReader з використанням методу ExecuteReader Метод ExecuteReader приймає аргумент CommandBehavior, який підлаштовує результат до пропонованим вимогам У даному прикладі визначено режим роботи команди за замовчуванням, так що цей аргумент може бути опущений Наприклад, якщо вимоги визначають, що тільки один результуючий набір даних може бути спрямований у файл, а не кілька, слід вказати аргумент CommandBehavior SingleResult

Коли виконується DataReader, схема результуючого набору даних може бути легко ідентифікована на льоту за допомогою створення в памяті обєкта DataTable і наповнення його за допомогою виклику методу GetSchemaTable існуючого обєкта DataReader Цей метод допоможе включити в файл CSV імена стовпців

На закінчення результат направляється в файл, після чого буфер потоку, а також обєкт DataTable і саме підключення повинні бути закриті Тільки після цього виконання методу можна завершити

Imports System10

&ltMicrosoftSqlServerServerSqlProcedure()&gt _

Public Shared Sub clrusp_ResultToCSVFile(ByVal sTSQL As SqlString, _ ByVal sFile As String)

Dim cnCLR As SqlConnection = New _

SqlConnection(&quotcontext connection=true&quot)

Dim qryGetResult As SqlCommand = cnCLRCreateCommand

qryGetResultCommandText = CStr(sTSQL)

qryGetResultCommandType = CommandTypeText

Dim plnfo As SqlPipe = SqlContextPipe

plnfoSend(&quotT-SQL batch to execute: &quot &amp CStr(sTSQL))

plnfoSend(&quotDestination file: &quot &amp sFile)

cnCLROpen()

Dim strmResult As StreamWriter = New StreamWriter(sFile)

Dim sqldrResult As SqlDataReader = _

qryGetResultExecuteReader(CommandBehaviorDefault)

Dim sqldrResultSchema As DataTable sqldrResultSchema = sqldrResultGetSchemaTable For Each drRow As DataRow In sqldrResultSchemaRows strmResultWrite(drRow(&quotColumnName&quot)ToString)

If CInt(drRow(&quotColumnOrdinal&quot)) &lt FieldCount – 1 Then strmResultWrite, 0, 1)

Else

strmResultWrite(vbCrLf, 0, 2)

End If Next

If sqldrResultHasRows Then While sqldrResultRead()

For i As Int32 = 0 To sqldrResultFieldCount – 1 strmResultWrite(sqldrResultGetSqlValue(i)ToString, 0, _

Len(sqldrResultGetSqlValue(i)ToString))

If i &lt sqldrResultFieldCount – 1 Then strmResultWrite(&quot, &quot, 0, 1)

Else

strmResultWrite(vbCrLf, 0, 2)

End If Next End While End If

strmResultFlush() strmResultClose() sqldrResultClose() cnCLRClose()

Безсумнівно, потрібно було б додати ще дещо, наприклад обробку помилок і безлічі результуючих наборів даних, якщо такі існують При бажанні читач може ознайомитися з повним варіантом проекту на Web-сайті книги

Функції CLR

Функція CLR в більшості випадків не вимагає такого обсягу програмного коду, як збережені процедури З тієї ж причини, з якої рекомендується бути у функціях T-SQL гранично коротким, бажано кілька обмежити області застосування функцій CLR Це дозволить зберегти транзакції максимально короткими і одночасно уникнути витрат продуктивності, які можуть бути приховані в програмному коді функцій Збережені процедури CLR здатні елегантно модифікувати структуру даних, в той час як функції ніколи не зможуть змінити стан бази даних

Компанія Microsoft вирішила не перешкоджати створенню клієнтських подключе-Вкімакке ний ADONET 20 в тілі функцій CLR Це означає, що існує можливість зміни стану бази даних і не можна запобігти такі дії, виконані з функції Така можливість може звести нанівець все хороше, що є у функціях CLR Остерігайтеся цих коштів

Тепер подивимося, як можна використовувати скалярную функцію для перевірки 1Р-адреси Таку операцію легко виконати за допомогою функції CLR, що використовує звичайні вираження NET Ця функція є досить потужною, оскільки може бути використана в інструкціях DML, збережених процедурах Т-SQL, обмеженнях перевірки і обчислюваних стовпцях

Для створення функції додайте імпорт простору імен System Text RegularExpression в шаблон Visual Studio 2005, встановіть атрибути функції, визначаючи, що вона детермінована і точна, введіть кілька рядків програмного коду, після чого розгорніть функцію Ця функція може виявитися корисною в обмеженнях перевірки та індексованих обчислюваних стовпцях, так як вона виконує необхідні маніпуляції рядками набагато швидше, ніж еквівалент мовою T-SQL

Imports SystemTextRegularExpression

&ltMicrosoftSqlServerServerSqlFunction _

(DataAccess:=DataAccessKindNone/ IsDeterministic:=True, _

IsPrecise:=True)&gt _

Public Shared Function clrfn_IsIP(ByVal Value As String) As Boolean Dim rx As New Regex( _

11 ( (2 [0-4] \d|25 [0-5] | [01] \d\d) \ ) {3} (2 [0-4] \d|25 [0-5] | [01] \d\d) &quot , RegexOptionsIgnoreCase _

Or RegexOptionsIgnorePatternWhitespace)

Return rxMatch(CType(Value, String))Success End Function

Якщо функція як результат повертає набір даних, а не скалярний значення, результатом стане потоковая таблична функція Ключовим словом тут є потоковая. Табличні функції Т-SQL повинні повернути всю таблицю до того, як вона зможе бути використана Це задіює великий обсяг памяті, так само як і простору в базі tempdb, якщо результуючий набір даних великий, або якщо запит для отримання результату обробляє великий обсяг даних Та ж функція, описана як потокова функція CLR, може бути використана, як тільки створена, таким чином, знижуючи вимоги до памяті і базі tempdb На щастя, табличні функції дозволяють набору даних бути ініціалізованих, використовуючи контекст підключення, а не створюючи нове клієнтське підключення ADO Використання клієнтського підключення ADONET зводить нанівець роль функції – забезпечення ефективного виконання не базованих на множинах операцій Отже, таких підключень слід уникати

Для завершення огляду типів CLR, що відносяться до баз даних, розглянемо тригер У SQL Server 2005 тригери можуть запропонувати багато нового Тригери CLR реалізують всю функціональність, доступну в тригерах Т-SQL Тригер може бути асоційований з дією DDL або DML У тригерах DML доступні таблиці Inserted і Deleted Тригерами DDL можуть бути події або групи подій

Тригер, запропонований як приклад в Books Online, демонструє використання стандартних виразів для перевірки правильності адреси електронної пошти та подальшого занесення запису в журнал аудиту Цей приклад може здатися цікавим, однак логічніше було б реалізувати дану задачу за допомогою обмеження перевірки та тригера аудиту Т-SQL Насправді в більшості завдань, для яких пропонується рішення за допомогою тригера CLR, тригер Т-SQL, що робить запис у черзі брокера служб, був би зручніше як для розробника, так і для адміністратора Час покаже, але тригер CLR претендує на роль найменш корисного з типів CLR Давайте розглянемо наступний тригер CLR DDL, який реалізує властивості контексту тригера для операцій DDL рівня бази даних Це дозволить продемонструвати спосіб формування тригера CLR

Тригери інтеграції CLR можуть здатися цікавими тим, хто зацікавлений Назаштку ван в сумісності з законом Сарбанеса-Окслі, оскільки дії аудиту компілюються і тому не так чутливі до підстановці і ненавмисної модифікації, як тригери Т-SQL Контекст XML події тригера також містить стандартизований і повний набір інформації аудиту

&ltMicrosoftSqlServerServerSqlTrigger _

(Name:=&quotclrtr_ddlchanges&quot, _

Targe t: = &quotDATABAS E &quot , _

Event:=&quotDDL_DATABAS E_LEVEL_EVENTS&quot) &gt _

Public Shared Sub clrtr_ddlchanges()

Dim plnfo As SqlPipe = SqlContextPipe

plnfоSend (SqlContextTriggerContextTriggerActionToString) plnfoSend (SqlContextTriggerContextEventDataValueToString) plnfoSend (SqlContextWindowsldentityNameToString)

End Sub

У даному прикладі були продемонстровані інформативні можливості тригера CLR Однак всю інформацію, доступну в даному тригері, можна з легкістю знайти і в тригері Т-SQL, при цьому ймовірність того, що даному триггеру знадобиться записати дані в іншу таблицю, досить висока Виглядає так, ніби витрати на переміщення інформації між таблицею бази даних і CLR з урахуванням компіляції тригера буде складніше виправдати, ніж використання збереженої процедури або функції CLR При цьому, природно, передбачається, що використані правильні підстави для визначення того, коли збережену процедуру, функцію або тригер краще писати на CLR і коли на T-SQL

Інтеграцію CLR можна також використовувати для створення користувацьких в функцій консолідації (типу min (), max () або sum ()) Детальний опис ти процесу розробки користувальницьких функцій консолідації даних виходить ^ за рамки цієї книги, проте ви можете знайти приклади таких функцій на

сайті книги (www SQLServerBible com)

Джерело: Нільсен, Пол Microsoft SQL Server 2005 Біблія користувача : Пер з англ – М: ООО ІД Вільямс , 2008 – 1232 с : Ил – Парал тит англ

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*