Практическое использование особенностей .Net CLR в новой версии сервера MS SQL Yukon : Программирование для всех. Языки программирования, программирование баз данных, технологии. Статьи для програмиста скачать бесплатно учебники книги документация форум : Статьи

ЯЗЫК: C#
Адрес оригинальной статьи: http://www.sqljunkies.com/
Автор: Kent Tegels
Дата публикации оригинала: 26.01.2004 г.
ПЕРЕВОД: Чужа В.Ф. ака hDrummer, http://dotnetgrains.sql.ru

В тот раз, когда я впервые был представлен Цыплёнку в Ореховом Масле, у меня родилось множество вопросов. Каков он на вкус? Почему кто-то решил совместить Ореховое Масло и Цыплёнка? Как всё это готовится? Чья вообще это была идея? Попробовав первый же кусочек, я попался на крючок на всю оставшуюся жизнь.
Когда я впервые услышал о том, что следующая версия Microsoft SQL Server 'Yukon' будет хостить .NET Common Language Runtime (CLR), у меня возникли похожие вопросы: Зачем это было сделано? Как я могу воспользоваться преимуществами такого подхода? Через несколько минут я обнаружил себя просматривающим примеры использования преимуществ такой модели, которые, откровенно говоря, не убеждали меня, что это хороший повод применения той или иной особенности. В действительности, мне нужна была реальная бизнес-проблема, для решения которой использование CLR выглядело бы предпочтительным. К счастью, такая проблема у меня была.

Проблема Я работаю в ведущей архитектурной компании в Омахе, Небраска: HDR, Inc. У компании есть офисы не только в Омахе; по большому счёту, HDR обладает более чем 100 офисами и стройплощадками. Это значит, что обычным явлением для наших работников является перемещение от офиса к офису по мере работы над различными проектами. Такие путешествия часто совершаются на самолётах и часто люди не уверены в том, какой из аэропортов является ближайшим к тому или иному офису. Как же я мог им помочь?
Поскольку я всегда мыслю категориями баз данных, моей первой мыслью было создание простой БД:

Таблица, содержащая информацию о местонахождении стройплощадок - Sites.
Таблица, содержащая информацию о местонахождении аэропортов - Airports.
Таблица, ассоциирующая каждую стройплощадку с одним или несколькими аэропортами — SitesAirports.

Такой подход имеет несколько проблем. Во-первых, человек, который будет вводить данные, должен знать, какой из аэропортов самый близкий к данной площадке и при добавлении нового офиса этот вопрос будет подыматься снова и снова. Если же аэропорт будет закрыт или открыт новый, то опять же возникнет похожая проблема. Ну и потом может возникнуть такой вопрос - что мы понимаем под ‘самый близкий’?

Геометрия проще вычислений

Одним из приятных моментов работы в компании HDR является то, что меня окружают люди, которым нравится использовать технологии для решения существующих проблем. В HDR работает несколько профессионалов в области геоинформационных систем (ГИС). Основная ценность ГИС в том, что эта система позволяет вам соотнести объекты физического мира с информационно-управляемыми моделями и это именно то, что я собирался сделать. В информационно-управляемой системе мне нужно было соотнести сущности – площадки и аэропорты. Почему бы не позаимствовать фундаментальные концепции ГИС – широту и долготу?
Как вы, возможно, знаете, координаты точки на Земле измеряются в градусах широты и долготы. Широта представляет собой расстояние от полюсов, Северного и Южного, а долгота представляет собой расстояние к востоку или западу от нулевого меридиана. Например, лучшего Цыплёнка в Ореховом Масле я пробовал в маленьком ресторанчике в г. Линкольн, Небраска. Этот ресторан более-менее точно расположен на 40°45"14' северной широты, 96°38"44' западной долготы в терминах ГИС. Часто используют десятичные градусы; в этом случае 40.7539, -96.6428.
Поскольку мы знаем адреса каждого из офисов, осталось найти их координаты. Для этого мы использовали программный комплекс MapPoint, а вы можете использовать MSN Maps или какие-то другие программные пакеты. Поиск координат аэропортов выглядел немного по-другому. Я сделал несколько запросов в Google и получил большой список аэропортов и их координат. Осталось решить одну задачу – как я могу проассоциировать площадку с аэропортом?
Если вы имеете широту и долготу двух точек, можно быстро определить расстояние между ними по простой формуле (показано в псевдокоде):

dlon = lon2 - lon1
dlat = lat2 - lat1
a = (sin(dlat/2))^2 + cos(lat1) * cos(lat2) * (sin(dlon/2))^2
c = 2 * atan2(sqrt(a), sqrt(1-a))
d = 3956 * c

Значение 3,956 – меридианный радиус Земли, от полюса к полюсу в милях.
Поскольку мы можем вычислить расстояние между данной площадкой и аэропортом, то теперь нам будет просто соотнести их как более близкие или более отдалённые объекты.
И хотя наша формула выглядит достаточно простой, всё же она более сложна, чем, скажем, вычисление налога с продаж. Поэтому именно здесь будет интересно использовать функции CLR в Yukon.
Поскольку нам необходимо использование этих вычислений в запросах, один из наших DBA разработал такую пользовательскую функцию:

CREATE FUNCTION dbo.udfComputeDistance
(
@lat1 float,
@lon1 float,
@lat2 float,
@lon2 float
)
RETURNS float
AS
begin
-- dLong представляет разницу в долготе
-- а dLat в широте
declare @dLong float
declare @dLat float
-- для лучшей читаемости
-- мы упростили вычисления, разбив их на части
-- эта переменная временно хранит значение первого вычисления
declare @temp float
-- перевод десятичных градусов в радианы
set @lat2 = radians(@lat2)
set @lon1 = radians(@lon1)
set @lat1 = radians(@lat1)
set @lon2 = radians(@lon2)
-- вычисление разностей
set @dLong = @lon2 - @lon1
set @dLat = @lat1 - @lat2
-- вычисление первой части уравнения
set @temp = (square(sin(@dLat/2.0))) + cos(@lat2) * cos(@lat1) * (square(sin(@dLong/2.0)))
-- возвращаем приблизительное расстояние в милях
-- заметьте, что 3956 приблизительный меридианный радиус Земли
return (2.0 * atn2(sqrt(@temp), sqrt(1.0-@temp)))*3956.0
end

Мы используем эту функцию для нахождения всех аэропортов в радиусе 100 миль от каждой площадки с помощью простого запроса:

select s.name
,s.city
,s.state
,a.name
,a.iatacode, dbo.udfComputeDistance(s.latitude,s.longitude
,a.latitude,a.longitude) as distance
from sites s, airports a
where dbo.udfComputeDistance(s.latitude,s.longitude
,a.latitude,a.longitude) <= 100.0
order by 1,4

Query Analyzer доложил, что на выполнение этого запроса ушло около 7 секунд и упорядочивание результата заняло меньше 1% времени выполнения запроса. Поскольку эта информация используется не часто, мы поместили этот запрос в хранимую процедуру (ХП). И хотя это простое и работающее решение, эти семь секунд меня немного обеспокоили. Ведь всё-таки мы используем не первое поколение Пентиумов для работы наших SQL-серверов!

Прозрение Не является сюрпризом тот факт, что вычисления с помощью T-SQL на SQL Server Yukon не выдержали проверки временем. Движок запросов не был создан для того, чтобы выдавать процессору набор инструкций, оптимизированных для такого типа вычислений. Однако CLR разработан именно с такой оптимизацией. Размещение CLR в SQL Server Yukon – лучший подход, поскольку мы можем выдать процессору набор оптимизированных инструкций для числовых вычислений и, что не менее важно, нам не нужно повторно изобретать велосипед, который уже встроен в .NET Framework.
Первой ласточкой, если хотите, которая заставила меня использовать CLR в SQL Server Yukon было резкое уменьшение времени исполнения моего запроса с семи секунд до одной путём использования CLR-основанной пользовательской функции. Ведь разместив CLR внутри SQL Server Yukon мы взяли лучшее от двух миров: великолепный набор инструментов для работы с реляционными данными (T-SQL) и равный ему по эффективности функционально-ориентированный инструмент для работы с данными на .NET языке по вашему выбору.

Используем CLR Итак, если и есть какая-то хитрость в программировании, которую я постиг за 20 лет работы в этой области, то она звучит так – начинай с того, что знаешь. В этом случае я решил сразу избавиться от возможных багов в создаваемой функции, путём отладки её в Visual Studio .NET (VS .NET) "Whidbey" IDE. Это гораздо проще, чем отлавливать их после переноса функции на сервер. Сначала я создал консольный проект на языке C# и начал кодирование в Class1.cs:

using System;
using System.Data;
using System.Data.SqlTypes;
namespace test
{
class Class1
{
private const double PI_OVER_180 = 0.0174532925;
private static double radians(double DecimalDegrees)
{
return DecimalDegrees * PI_OVER_180;
}
public static SqlDouble ComputeDistance(SqlDouble FromLat,
SqlDouble FromLong, SqlDouble ToLat, SqlDouble ToLong)
{
double lat1, lat2, lon1, lon2,
dLong = 0.0, dLat = 0.0, subCalc = 0.0;
lat1 = radians((double)(FromLat));
lon1 = radians((double)(FromLong));
lat2 = radians((double)(ToLat));
lon2 = radians((double)(ToLong));
dLong = (double)(lon2 - lon1);
dLat = (double)(lat2 - lat1);
subCalc = (Math.Pow(Math.Sin(dLat / 2.0), 2.0))
+ Math.Cos(lat2) * Math.Cos(lat1)
* (Math.Pow(Math.Sin(dLong / 2.0), 2));
return ((2.0 * Math.Atan2(Math.Sqrt(subCalc),
Math.Sqrt(1.0 - subCalc))) * 3956.0);
}
[STAThread]
public static void Main(string[] args)
{
Console.WriteLine(
ComputeDistance(40.7539,-96.6428, 41.28692,-96.07023));
Console.ReadLine();
}
}
}

Из своих предыдущих исследований с использованием MapPoint, я узнал, что ресторан, в котором готовят моего любимого Цыплёнка в Ореховом Масле, находится примерно в 50 милях от места, где я живу. Указав свои координаты широты и долготы в своей тестовой программе, я определил, что я нахожусь меньше чем в 48 милях от ресторана. Как по мне, довольно близко, чтобы получить желаемое!
Будучи уверен в правильности работы своей функции, я создал новый проект SQL Server Project с именем asmDistanceLibrary. Проект такого типа вы можете создать из диалогового окна New Project, если вы установили SQL Server Yukon, а затем VS .NET Whidbey.
Из общения с другими людьми я узнал, что намного проще использовать для таких проектов готовые шаблоны Whidbey. Причиной этому служит тот факт, что вы должны связать вместе три библиотеки (DLLs) в одну выходную библиотеу DLL для того, чтобы SQL Server Yukon мог загрузить и зарегистрировать вашу функцию:

ClrCppModule.dll
Sqlaccess.dll
Microsoft.VisualStudio.DataTools.SqlAttributes.dll

После указания имени проекта и его расположения, вам предложат указать соединение с БД. Можно выбрать соединение уже известное Server Explorer’у или создать новое.
Я выбрал имя asmDistanceLibrary по одной причине: обычно я создаю имя объекта с учётом типа объекта (за исключением таблиц). После удаления пустого файла Class1.cs, я добавил новый файл DistanceLibrary и написал там следующий код:

using System;
using System.Data.Sql;
using System.Data.SqlTypes;
using Math = System.Math;
public class CDistanceLibrary
{
private const double PI_OVER_180 = 0.0174532925;
private static double radians(double DecimalDegrees)
{
return DecimalDegrees * PI_OVER_180;
}
public static SqlDouble ComputeDistance(SqlDouble FromLat,
SqlDouble FromLong, SqlDouble ToLat, SqlDouble ToLong)
{
double lat1, lat2, lon1, lon2,
dLong = 0.0, dLat = 0.0, subCalc = 0.0;
lat1 = radians((double)(FromLat));
lon1 = radians((double)(FromLong));
lat2 = radians((double)(ToLat));
lon2 = radians((double)(ToLong));
dLong = (double)(lon2 - lon1);
dLat = (double)(lat2 - lat1);
subCalc = (Math.Pow(Math.Sin(dLat / 2.0), 2.0))
+ Math.Cos(lat2) * Math.Cos(lat1)
* (Math.Pow(Math.Sin(dLong / 2.0), 2));
return ((2.0 * Math.Atan2(Math.Sqrt(subCalc),
Math.Sqrt(1.0 - subCalc))) * 3956.0);
}
}

Этот код выглядит знакомым для вас, не так ли? Я уже тестировал его в консольном приложении и уверен, что он будет работать. Единственные заметные различия – отсутствие функции main() и другое имя класса - CDistanceLibrary. Просто компилируем её, инсталлируем и всё?
Вобщем-то… нет. По-крайней мере, пока нет. Для понимания почему, мы должны немного углубиться в то, что в действительности происходит между SQL Server Yukon и CLR.
В случае привычного хода вещей, SQL Server обрабатывает T-SQL запросы так же, как интерпретатор скриптов. Каждая строка запроса вынуждает движок запросов выполнить какое-то действие, например получение данных с диска, упорядочивание строк и т.д. Обычно, вся задействованная программная логика — в меньшей или большей степени — содержится в библиотеках SQL Server’а. Есть два известных исключения: расширенные хранимые процедуры и сборки CLR. Когда движок запросов получает запрос на использование одного из этих сервисов, он пытается найти точку входа для доступа к ним. Если точка входа найдена, в соответствующие функции передаются данные и управление передаётся вызываемой логике. Когда же процедура или сборка возвращает контроль выполнения обратно в SQL Server, движок запросов получает результаты или ошибки выполнения и продолжает нормальную работу.
Здесь и лежит ответ, почему развертывание проекта VS .NET Whidbey не оказывает никакого эффекта на SQL Server. Согласно материалам последней конференции PDC, похоже, что развертывание проекта на самом деле только регистрирует сборку в SQL Server Yukon, но у него нет данных, где находится точка входа для этой сборки. Мы укажем её руками.

Проект SQL Server Workbench Процесс регистрации сборки в SQL Server Yukon на самом деле прост. Запустим SQL Server Workbench и создадим новый запрос. Выглядит он так:

USE [Your Database Name Goes Here]
DROP FUNCTION clrComputeDistance
GO
DROP ASSEMBLY asmDistanceLibrary
GO
CREATE ASSEMBLY asmDistanceLibrary
FROM '[The path to your DLL]asmDistanceLibrary.dll'
WITH PERMISSION_SET = SAFE
GO

Заметьте, что вам нужно будет изменить этот запрос в соответствии с той БД, для которой вы будете регистрировать сборку. Также не забудьте изменить путь, по которому в действительности находится ваша библиотека.
Теперь, когда SQL Server Yukon знает местонахождение вашей сборки, мы должны превратить её в один из объектов, которые можно использовать в Query Analyzer. Мне нравится использовать для этого пользовательские функции, но вы можете также воспользоваться хранимыми процедурами. А вот и T-SQL код:

CREATE FUNCTION dbo.clrComputeDistance
(
@lat1 as float,
@lon1 as float,
@lat2 as float,
@lon2 as float
)
RETURNS float
AS EXTERNAL NAME asmDistanceLibrary:CDistanceLibrary::ComputeDistance
GO

Если вы не работали с расширенными ХП, единственной новой вещью для вас будет строка AS EXTERNAL NAME. Всё, что делает этот код - просит SQL Server Yukon зарегистрировать точку входа для метода ComputeDistance сборки asmDistanceLibrary класса CDistanceLibrary.
Теперь можно протестировать нашу функцию с помощью запроса:

SELECT dbo.clrComputeDistance(40.7539,-96.6428, 41.28692,-96.07023)

И наконец, мы можем вернуться к нашей проблеме, определив ближайшие аэропорты к нашим площадкам. Вот такой простой запрос:

SELECT site.name,site.city,site.state
,airport.name,airport.iataCode
,ROUND(DBO.UDFCOMPUTEDISTANCE(site.LATITUDE,site.LONGITUDE,airport.LATITUDE,airport.LONGITUDE),2) AS distance
FROM sites site,airports airport
WHERE DBO.CLRCOMPUTEDISTANCE(site.LATITUDE,site.LONGITUDE,airport.LATITUDE,airport.LONGITUDE) <= 100
ORDER BY site.name,distance

Выводы Мы рассмортели почему и как мы можем использовать SQL Server Yukon совместно со сборками .NET, созданными в VS .NET Whidbey. Как по мне, есть четыре области применения данной технологии. Первая – наличие сложной математики, задействованной в вычислениях, на подобие тех, что были продемонстрированы выше. Выгодой стало существенное увеличение производительности. Ещё одной полезной областью применения будет та, для которой T-SQL не предназначен, например разбор строк, разделённых запятыми, в список значений. Третий момент – если вы хотите сделать нечто, для чего T-SQL сам по себе вообще не годится. Предположим, например, что вы разработали ХП, возвращающую результат запроса в виде фрагмента XML документа, но вы хотели бы получить результат, уже переведенный в HTML, после наложения таблицы стилей XSL к этому фрагменту XML. Это практически не возможно сделать с T-SQL, но довольно просто выполнить с помощью .NET CLR. И последняя область применения – уйти от создания и поддержки расширенных ХП на C++. Не потому, что C++ плох, а потому, что создание сборок намного проще.

Да, если бы кто-то смог сделать приготовление Цыплёнка в Ореховом Масле таким же простым…

HTML 5: пять вещей вызывающих особый интер....

HTML 5 — это грядущее обновление гипертекстового языка разметки, основного способа создания контента для размещения его во всемирной паутине. Разработка HTML остановилась в 1999 году, на версии HTML 4.01 и с тех пор web-содержимое изменилось так, что текущие спецификации HTML перестали соответствовать сегодняшним требованиям. HTML 5 нацелен на то, чтобы увеличить функциональную совместимость HTML и соответствовать растущим требованиям разнообразного и смешанного web-контента. HTML 5 так же нацелен на устранение недостатков четвертой версии. В этой статье мы взглянем на 5 новых интересных вещей в HTML 5.

Элемент управления ListView был представлен в .Net Framework 3.5 как замена устаревшему GridView. Новый элемент имеет более расширенный функционал, чем его предшественник, но в тоже время лишен некоторых внутренних механизмов, что впрочем целиком следствие из расширенной универсальности ListView. Среди отличий ListView и GridView можно назвать и гибкую настройку разметки, что позволяет выводить данные не только в табличном виде, но и вообще в любом каком пожелает программист. Благодаря шаблонам ItemTemplate, EditItemTemplate, InsertItemTeplate можно настроить внешний вид при любом из состояний ListView: редактировании или выборе элемента.

Компания Стимулсофт предоставляет для разработчиков мощный набор инструментов для создания отчетов для Microsoft Visual Studio .Net 2005 и 2008; эти инструменты доступны как для Windows Forms, так и для Web Forms. Это генератор отчетов Stimulsoft Reports.Net. Генератор отчетов Stimulsoft Reports.Net имеет ряд особенностей: простая работа с дизайнером отчетов, полная поддержка экспорта в PDF, Word, Excel и многие другие форматы. Crystal Report и Microsoft Reporting Service – очень хорошие программные продукты для повседневной работы, но, если Вам необходимо создать отчеты с поддержкой кросс-табов, drill down, Ajax, штрих-кодов и возможностью подключения одновременно более одного источника данных, то Stimulsoft Reports.Net поможет Вам сэкономить массу времени. Также, данный генератор отчетов позволяет пользователям создавать свои собственные отчеты любой сложности. И все эти особенности делают Stimulsoft Reports.Net хорошим выбором в сфере программных продуктов для Business Intelligence.