- •Visual Studio и sql Server 2005 - начало работы. Проекты.
- •Интеграция в sql Server
- •Хранимые процедуры
- •Пользовательские функции Скалярные функции
- •Табличные функции
- •Триггеры
- •Агрегирующие функции
- •Пользовательские типы данных
- •Программирование на стороне клиента
- •Настройка odbc
- •Программный доступ посредством odbc
- •Классификация api-функций odbc
- •Пример программирования на основе odbc
- •Технология ado.Net Общие сведения
- •Соединение с sql Server
- •Представление базы данных на стороне клиента
- •Средства отображения таблиц
- •Взаимодействие с sql Server и получение результатов Запуск команд на стороне сервера
- •Использование адаптера
- •Контрольные вопросы:
- •Самостоятельная работа:
- •Список литературы:
Агрегирующие функции
Диалект языка SQL, реализуемый на SQL Server 2005, содержит не так уж и много агрегирующих функций — всего тринадцать. Опираясь на технологию .NET, мы можем создавать собственные агрегирующие функции. Остановимся на этом подробнее. В листинге 4.18 представлен текст модуля на С#, позволяющий создать агрегирующую функцию вычисления среднего значения, функция эквивалентна встроенной в SQL функции avg(). В листинге 4.19 представлена последовательность команд, позволяющая создать новую агрегирующую функцию.
Листинг 4.18
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.Native)]
public struct avgnew
{
// функция инициализации
public void Init()
{
sum = 0;
count = 0;
}
// данная функция выполняет основные действия агрегировния
public void Accumulate(SqlDouble s)
{
sum += s;
count++;
}
// функция предназанчена для объединения текущего экземпляра агрегата
// с передаваемым в качестве параметра
public void Merge(avgnew Group)
{
sum += Group.sum;
count++;
}
// функция возвращает результирующее значение
public SqlDouble Terminate()
{
return sum / count;
}
// внутренние переменные
private SqlDouble sum;
private Int32 count;
}
Изучая текст модуля в листинге 4.18, обратите внимание на следующее.
Агрегирующая функция описывается открытой структурой. Имя открытой структуры затем используется в команде create aggregate (листинг 4.19). Структура описывается двумя обязательными атрибутами: Serializable и SqlUserDefinedAggregate.
Структура, описывающая агрегирующую функцию, должна содержать четыре обязательных метода (функции):
Init — данный метод вызывается перед началом вычисления агрегирую щей функции и поэтому используется для инициализации значений переменных с целью вычисления;
Accumulate— данная функция предназначена для накапливания агрегирующих значений (сумм, счетчиков и т. п.);
Merge — метод предназначен для объединения текущего экземпляра агрегата с передаваемым в качестве параметра;
Terminate — функция вызывается в конце вычисления и используется для формирования окончательного результата и возвращения этого результирующего значения.
Листинг 4.19
drop aggregate dbo.avgnew
drop assembly sql5
go
create assembly sql5
from 'F:\sql\sql5\sql5\bin\Release\sql5.dll'
with permission_set = safe
go
create aggregate avgnew(@value float)
returns float
external name sql5.avgnew
Пользовательские типы данных
Создание новых типов данных (User Defined Type, UDT), которые затем можно применять как при определении столбцов таблиц, так и при декларировании переменных в языке Transact-SQL, является одним из самых красивых и мощных инструментов интеграции CLR и SQL Server 2005. Новый тип может иметь свои свойства и методы, что значительно расширяет функциональность программирования на стороне сервера.
Создадим новый проект и добавим в него модуль, используя пункт меню Project | Add User-Defined Type.... Получим следующий текст (листинг 4.20).
Листинг 4.20
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedType(Format.Native)]
public struct Type1 : INullable
{
public override string ToString()
{
// Replace the following code with your code
return "";
}
public bool IsNull
{
get
{
// Put your code here
return m_Null;
}
}
public static Type1 Null
{
get
{
Type1 h = new Type1();
h.m_Null = true;
return h;
}
}
public static Type1 Parse(SqlString s)
{
if (s.IsNull)
return Null;
Type1 u = new Type1();
// Put your code here
return u;
}
// This is a place-holder method
public string Method1()
{
// Insert method code here
return "Hello";
}
// This is a place-holder static method
public static SqlString Method2()
{
// Insert method code here
return new SqlString("Hello");
}
// This is a place-holder field member
public int var1;
// Private member
private bool m_Null;
}
Обратите внимание, что структура, на основе которой создается пользовательский тип данных, является наследником интерфейса INullabie. Все типы данных пространства имен System.Data.SqlTypes также строятся на основе этого интерфейса.
Изучая листинг 4.20, обратим внимание на обязательный метод Null (точнее, согласно правилам языка С#, это называется свойством, посредством которого можно получить доступ к некоторому полю). Без этого метода, который просто возвращает значение определяемого типа, невозможно зарегистрировать новый тип данных. Еще один метод (свойство) — это IsNull. Данный метод наследуется из интерфейса INullabie, и здесь мы его переопределяем.
Посредством этого свойства можно получить значение поля m_Null, которое определяет, присвоено данному экземпляру значение null или нет (равно true, если присвоено).
Обычно в целях безопасности поля объявляют закрытыми (private), т. е. запрещают прямой доступ к ним. Доступ же к ним осуществляется посредством свойства, которое может иметь два метода: get и set. Метод get предназначен для доступа к полю и обычно содержит лишь один оператор return (см. листинг 4.20). Метод set может содержать множество операторов. Его назначением является не только изменить поле, но и, возможно, сделать изменения в других полях, дабы соблюсти непротиворечивость.
Кроме перечисленных компонентов, в классе также должны присутствовать метод, преобразующий данный тип к строковому представлению (ToString), и метод обратного преобразования (из строкового представления к данному типу) — Parse.
И так, приступим к созданию нового типа данных. В качестве примера представим точку в трехмерном пространстве, которая может быть задана тремя координатами. Этот тип данных реализован в листинге 4.21. Кроме очевидных членов класса, о которых мы ранее говорили, в листинге имеется также метод, с помощью которого можно определить расстояние между двумя точками в трехмерном пространстве.
листинге 4.21
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedType(Format.Native)]
public struct point3d : INullable
{
// свойство - проверка на NULL
public bool IsNull
{
get
{
return m_Null;
}
set
{
m_Null = value;
}
}
// обязательный метод, возвращающий значение типа point3d
public static point3d Null
{
get
{
point3d p = new point3d();
p.m_Null = true;
return p;
}
}
// метод, преобразующий тип в строку
public override string ToString()
{
if (this.IsNull)
return "NULL";
else
return this.xm.ToString() + ":" +
this.ym.ToString() + ":" +
this.zm.ToString();
}
// метод, разбирающий представленный строкой тип
public static point3d Parse(SqlString s)
{
if (s.IsNull)
return Null;
else
{
point3d p = new point3d();
String ss = s.ToString();
String[] xyz = null;
xyz = ss.Split(new Char[] { ':' }, 3);
p.xm = System.Convert.ToInt32(xyz[0]);
p.ym = System.Convert.ToInt32(xyz[1]);
p.zm = System.Convert.ToInt32(xyz[2]);
p.IsNull = false;
return p;
}
}
// свойство для доступа к закрытому полю xm
public Int32 x
{
get
{
return this.xm;
}
set
{
this.xm = value;
}
}
// свойство для доступа к закрытому полю ym
public Int32 y
{
get
{
return this.ym;
}
set
{
this.ym = value;
}
}
// свойство для доступа к закрытому полю zm
public Int32 z
{
get
{
return this.zm;
}
set
{
this.zm = value;
}
}
// метод для определения расстояния между точками
public decimal distance(point3d o)
{
point3d p1 = (point3d)o;
point3d p2 = this;
// в начале обработка значений NULL
if (p1.IsNull)
{
p1.x = 0;
p1.y = 0;
p1.z = 0;
}
if (p2.IsNull)
{
p2.x = 0;
p2.y = 0;
p2.z = 0;
}
double xd = Convert.ToDouble(p2.x - p1.x);
double yd = Convert.ToDouble(p2.y - p1.y);
double zd = Convert.ToDouble(p2.z - p1.z);
return System.Convert.ToDecimal(
System.Math.Sqrt((xd * xd) + (yd * yd) + (zd * zd)));
}
// закрытые поля
private Int32 xm;
private Int32 ym;
private Int32 zm;
private bool m_Null;
}
Опишем члены класса point3d:
IsNull — это свойство определяет, присвоено или нет переменной данного типа значение null;
обязательный метод (свойство) Null;
метод ToString, предназначенный для преобразования нашего типа к строке, в которой координаты отделяются друг от друга знаком :;
метод Parse, в котором осуществляется преобразование строкового представления типа к обычному;
свойства х, у, z предоставляют доступ к закрытым членам класса — полям xm, ym, zm;
метод distance позволяет определить расстояние между двумя точками в пространстве.
В листинге 4.22 представлена последовательность команд, в результате выполнения которой будет создан новый тип.
листинге 4.22
--удалить тип и сборку, если они есть
drop type point3d
drop assembly sql6
go
--зарегистрировать сборку
create assembly sql6
from 'F:\sql\sql6\sql6\bin\Release\sql6.dll'
with permission_set = safe
go
--зарегистировать новый тип данных
create type point3d
external name sql6.point3d
Созданный тип может быть использован и как тип переменной в программных модулях на стороне сервера, и как тип столбца таблицы. В листинге 4.23 представлена хранимая процедура new_type, с помощью которой легко проверить в действии новый тип данных. Т.о. открываются безграничные возможности наращивания функциональности SQL Server 2005!
Листинг 4.23
create procedure new_type
as
begin
—задаем переменные нового типа
declare @p point3d
declare @pl point3d —переменная, в которой будет храниться расстояние между точками
declare @s float .—инициализируем переменные строками
set @р=Ч2:23:45';
set @pl='0:0:0'; —вывести координату точки
select @p.z
set @p.x=123 —вывести строковое представление точки
select cast(@p.ToString() as char(15))
--задать координаты точки
set @p1.x=10; set @p1.y=12; set @p1.z=15;
--определить расстояние между точками
set @s=@p.distance(@p1);
--вывести расстояние
select @s
end