| ||||||||||||||||
 | ||||||||||||||||
| ||||||||||||||||
 | ||||||||||||||||
| ||||||||||||||||
|
|
Создание таблиц средствами SQL-DMO Совершенствуйте навыки программирования с применением SQL-DMO в проектах Access
В статье "Постигаем основы SQL-DMO", опубликованной в 5 номере журнала SQL Server Magazine Online за 2001 год, я показал, как использовать распределенные объекты управления, SQL Distributed Management Objects (SQL-DMO), для перечисления объектов базы данных. Модель SQL-DMO показывает объекты, методы, свойства и события через интерфейс COM в процессе управления выполнением административных задач в SQL Server. В настоящей статье я расскажу о том, как создавать в проектах Microsoft Access таблицы, пользуясь объектами SQL-DMO и Visual Basic for Applications (VBA). Поскольку объекты SQL-DMO демонстрируют свои функциональные возможности через интерфейс COM, то предложенные в данной статье подходы применимы в любой программной среде, где существует возможность использовать интерфейс COM. В статье "Постигаем основы SQL-DMO" было показано, каким образом базы данных располагаются внутри объектов SQL Server. Аналогичным образом коллекции таблиц размещаются на сервере. На рисунке 1 приведено графическое представление коллекции таблиц Tables внутри базы данных. Объекты Table, в свою очередь, обладают иерархически организованными коллекциями объектов и отдельными объектами. Каждая таблица обязательно имеет коллекцию столбцов Columns, но у любой таблицы может существовать единственный объект PrimaryKey, соответствующий первичному ключу. Коллекция Keys ключей таблицы содержит ссылки на все ограничения первичного ключа таблицы и внешних ее ключей.  Рассмотрим три примера кода SQL-DMO, которые демонстрируют приемы программирования, обеспечивающие успешное решение более сложных задач, связанных с проектированием таблиц. К числу таких задач относятся: создание экземпляра таблицы и добавление в нее столбцов; создание таблицы с первичным ключом; построение таблицы с внешними ключами. Создание таблицы и добавление столбцовКод VBA, приведенный в листинге 1, создает таблицу товаров Products, состоящую из четырех столбцов. Этот код содержит две процедуры, формирующие общую рабочую среду, в которой функционируют другие примеры, рассматриваемые в данной статье. Данный код также демонстрирует достаточно специфические правила синтаксиса для создания столбцов таблицы, относящихся к следующим типам данных: целочисленному int, символьному переменной длины varchar, денежному money и десятичному decimal.
ЛИСТИНГ 1: Создание таблицы товаров Products.
BEGIN CALLOUT A
Sub CallAddProductsTable()
Dim srvname As String
Dim loginname As String
Dim pwd As String
Dim dbname As String
Dim tblname As String
`Сформировать указатели для объекта SQLServer, входного имени,
`пароля и имени базы данных.
srvname = "cablat"
loginname = "sa"
pwd = ""
dbname = "SQLMagTablesSQL"
END CALLOUT A
BEGIN CALLOUT B
`Указать имя таблицы и программу, которая будет ее формировать.
`Передать программе спецификации таблицы.
tblname = "Products"
AddProductsTable srvname, _
loginname, pwd, dbname, tblname, _
"name", "ProdID", _
"datatype", "int", _
"name", "ProdName", _
"datatype", "varchar", _
"length", 25, _
"name", "Price", _
"datatype", "money", _
"name", "ProdWeight", _
"datatype", "decimal", _
"numericprecision", 9, _
"numericscale", 5
End Sub
END CALLOUT B
BEGIN CALLOUT C
Sub AddProductsTable(srvname As String, _
loginname As String, pwd As String, _
dbname, tblname, _
ParamArray ColSpecs() As Variant)
Dim srv1 As SQLDMO.SQLServer
Dim tbl1 As SQLDMO.Table
Dim col1 As SQLDMO.Column
Dim col2 As SQLDMO.Column
Dim col3 As SQLDMO.Column
Dim col4 As SQLDMO.Column
`Подсоединиться к указанному объекту SQLServer.
Set srv1 = New SQLDMO.SQLServer
srv1.Connect srvname, loginname, pwd
`Создать экземпляр таблицы и назвать ее.
Set tbl1 = New SQLDMO.Table
tbl1.Name = tblname
END CALLOUT C
BEGIN CALLOUT D
`Добавить целочисленный тип данных.
Set col1 = New SQLDMO.Column
col1.Name = ColSpecs(1)
col1.DataType = ColSpecs(3)
tbl1.Columns.Add col1
`Добавить тип данных символьной строки.
Set col2 = New SQLDMO.Column
col2.Name = ColSpecs(5)
col2.DataType = ColSpecs(7)
col2.Length = ColSpecs(9)
tbl1.Columns.Add col2
`Добавить еще один целочисленный тип данных.
Set col3 = New SQLDMO.Column
col3.Name = ColSpecs(11)
col3.DataType = ColSpecs(13)
tbl1.Columns.Add col3
`Добавить десятичный тип данных.
Set col4 = New SQLDMO.Column
col4.Name = ColSpecs(15)
col4.DataType = ColSpecs(17)
col4.NumericPrecision = ColSpecs(19)
col4.NumericScale = ColSpecs(21)
tbl1.Columns.Add col4
END CALLOUT D
BEGIN CALLOUT E
`Перед добавлением новой таблицы в назначенную
` базу данных удалить предыдущую версию таблицы,
` если она существует.
On Error Resume Next
srv1.Databases(dbname).Tables(tblname).Remove
srv1.Databases(dbname).Tables.Add tbl1
`Очистить объекты.
Set col1 = Nothing
Set col2 = Nothing
Set col3 = Nothing
Set col4 = Nothing
Set tbl1 = Nothing
srv1.Disconnect
Set srv1 = Nothing
End Sub
END CALLOUT E
Первая процедура, приведенная в листинге 1, определяет аргументы, в которых указывается, где будет создана таблица, и какие у нее будут столбцы. Вторая процедура создает таблицу и добавляет столбцы в полном соответствии с теми аргументами, которые ей передала первая процедура. Аргументы, которые задают индивидуальные свойства столбцов, передаются между первой и второй процедурами в виде элементов массива параметров. Важной особенностью массива параметров является то, что он не требует точно указывать количество аргументов. Поэтому массив параметров удобно применять для передачи аргументов, предназначенных переменному количеству столбцов с различными типами данных. Без использования массива параметров это потребовало бы проведения переменного числа настроек параметров установки. Давайте более подробно разберем, как построен код в листинге 1. Часть кода, обозначенная меткой А, выполняет двоякую функцию. Во-первых, код объявляет множество типов строковых переменных. Большинство этих типов относится к информации общего характера, такой как название сервера SQL Server и базы данных, в которой должна быть построена таблица. Кроме того, задается входной идентификатор и пароль, который будут применять пользователи при установлении соединения с сервером. Для создания таблиц, которые должны быть видны всем пользователям, принято применять входной идентификатор, который принадлежит фиксированной серверной роли системного администратора sysadmin. Код, помеченный меткой A, задает следующие названия: база данных называется SQLMagTablesSQL, а сервер - cablat. В качестве входного идентификатора пользователя задействован идентификатор системного администратора sa, а в роли пароля выступает строка пробелов. Код, обозначенный в листинге 1 меткой В, присваивает специфические табличные значения переменным, а затем передает эти переменные вместе с теми аргументами, которые были определены в метке А, второй хранимой процедуре в качестве ее аргументов. Первая процедура передает второй процедуре от двух до четырех аргументов для определения типа данных каждого столбца. Столбец ProdID относится к типу целочисленных данных int; столбец ProdName относится к символьному типу данных переменной длины varchar. При указании типа данных varchar или nvarchar необходимо задать максимальное количество символов, которое разрешено записывать в рассматриваемый столбец. Метка В в листинге 1 соответствует определению максимального размера помещаемой в столбец ProdName символьной строки, равного 25 символам. Часть кода, обозначенная в листинге 1 меткой С, демонстрирует синтаксис, который следует применять для скалярных аргументов и элементов массива параметров. Эта часть кода также устанавливает соединение с сервером, на котором будет размещена база данных. Кроме того, код дает ссылку на конкретную базу данных, в которой на этом сервере будет храниться таблица. Наконец, код создает экземпляр табличного объекта Table, tbl1, и присваивает значение его свойству, содержащему название таблицы Name. Код, обозначенный в листинге 1 меткой D, демонстрирует общий подход к определению столбцов таблицы и показывает возможные отклонения, которые могут иметь место для взаимоисключающих типов данных. Общий подход реализуется в три этапа. На первом шаге необходимо создать экземпляр столбца. На втором шаге код должен настроить этот столбец, присвоив значения таким его свойствам, как название Name и тип данных DataType. Эта часть процедуры использует элементы массива параметров для задания значений свойствам столбца. Определяемые свойства могут меняться в зависимости от типа данных столбца. К примеру, для столбца, принадлежащего целочисленному типу данных int, требуется настроить всего два свойства - имя и тип данных. В то же время столбец, относящийся к типу данных varchar, требует определения значений трех свойств - названия Name, типа данных DataType, и длины Length. Третий шаг создания столбца предусматривает инициирование метода добавления Add, который и добавит созданный столбец в коллекцию столбцов Columns рассматриваемой таблицы. Меткой E в листинге 1 отмечена та часть кода, которая сначала добавляет таблицу в ту базу данных, которая была указана в последней строке части кода, обозначенной меткой А. До начала добавления таблицы код удаляет из базы данных все ранние версии этой таблицы. После этого код вычищает все дочерние объекты. Создание таблицы с первичным ключомТеперь разберемся с примером кода, который создает таблицу заказов Orders. У этой таблицы имеется первичный ключ, построенный по столбцу с активизированным свойством идентичности Identity. Столбцы таблиц SQL Server, у которых активизировано свойство идентичности Identity, ведут себя точно так же, как и столбцы традиционных таблиц Access, относящиеся к типу данных AutoNumber. Приведенный пример кода во многом напоминает код из листинга 1 за исключением перевода свойства AllowNulls третьего столбца в состояние True. Код в листинге 2 содержит две процедуры, которые создают таблицу заказов с именем Orders. Таблица Orders содержит столбец идентификатора заказа OrderI; столбец OrderDate, в который заносится дата ввода заказа, а также столбец ShippedDate, в который записывается дата отправки заказа. Столбец OrderID служит первичным ключом таблицы, а столбец ShippedDate может содержать неопределенные значения NULL. Часть кода, обозначенная в листинге 2 меткой А, показывает, какой синтаксис следует применять для создания первичного ключа на основе первого столбца таблицы. Чтобы заставить SQL Server автоматически формировать значения первичного ключа для новых строк, процедура активизирует свойство Identity, присваивая ему значение True. Исходное значение равно 1000, а приращение составляет 10. После добавления в проект таблицы столбца со свойством Identity часть кода, обозначенная в листинге 2 меткой А, создает экземпляр key1 ключевого объекта Key, а затем присваивает значения свойствам Name и Type объекта key1, которые содержат соответственно название и тип данных. Свойству Clustered объекта key1 присваивается значение True. В результате уникальный индекс первичного ключа строится в виде кластеризованного индекса для всей таблицы Orders. Прежде чем добавить ключ key1 в коллекцию ключей Keys таблицы Orders, необходимо указать хотя бы один объект из класса столбцов, который будет поставлен в соответствие первичному ключу. В приведенном примере в этой роли выступает столбец col1, у которого активизировано свойство Identity.
ЛИСТИНГ 2: Создание таблицы заказов Orders.
Sub CallAddOrdersTable()
Dim srvname As String
Dim loginname As String
Dim pwd As String
Dim dbname As String
Dim tblname As String
`Сформировать указатели для объекта SQLServer, входного имени,
`пароля и имени базы данных.
srvname = "cablat"
loginname = "sa"
pwd = ""
dbname = "SQLMagTablesSQL"
tblname = "Orders"
AddOrdersTable srvname, _
loginname, pwd, dbname, tblname, _
"name", "OrderID", _
"datatype", "int", _
"name", "OrderDate", _
"datatype", "datetime", _
"name", "ShippedDate", _
"datatype", "datetime"
End Sub
Sub AddOrdersTable(srvname As String, _
loginname As String, pwd As String, _
dbname, tblname, _
ParamArray ColSpecs() As Variant)
Dim srv1 As SQLDMO.SQLServer
Dim tbl1 As SQLDMO.Table
Dim col1 As SQLDMO.Column
Dim key1 As SQLDMO.Key
Dim col2 As SQLDMO.Column
Dim col3 As SQLDMO.Column
`Подсоединиться к указанному объекту SQLServer.
Set srv1 = New SQLDMO.SQLServer
srv1.Connect srvname, loginname, pwd
`Создать экземпляр таблицы и назвать его.
Set tbl1 = New SQLDMO.Table
tbl1.Name = tblname
BEGIN CALLOUT A
`Добавить целочисленный тип данных
`со свойством Identity.
Set col1 = New SQLDMO.Column
col1.Name = ColSpecs(1)
col1.DataType = ColSpecs(3)
col1.AllowNulls = False
col1.Identity = True
col1.IdentitySeed = 1000
col1.IdentityIncrement = 10
tbl1.Columns.Add col1
`Добавить столбец со свойством Identity,
`который будет служить первичным ключом таблицы.
Set key1 = New SQLDMO.Key
key1.Name = "OrdersPK"
key1.Type = SQLDMOKey_Primary
key1.Clustered = True
key1.KeyColumns.Add col1.Name
tbl1.Keys.Add key1
END CALLOUT A
`Добавить временной тип данных datetime.
Set col2 = New SQLDMO.Column
col2.Name = ColSpecs(5)
col2.DataType = ColSpecs(7)
tbl1.Columns.Add col2
BEGIN CALLOUT B
`Добавить тип данных datetime, допускающий неопределенные значения.
Set col3 = New SQLDMO.Column
col3.Name = ColSpecs(9)
col3.DataType = ColSpecs(11)
col3.AllowNulls = True
tbl1.Columns.Add col3
END CALLOUT B
`Перед добавлением новой таблицы в назначенную
` базу данных удалить предыдущую версию таблицы,
` если она существует.
On Error Resume Next
srv1.Databases(dbname).Tables(tblname).Remove
srv1.Databases(dbname).Tables.Add tbl1
`Очистить объекты.
Set col1 = Nothing
Set key1 = Nothing
Set col2 = Nothing
Set col3 = Nothing
Set tbl1 = Nothing
srv1.Disconnect
Set srv1 = Nothing
End Sub
Во все столбцы в листинге 1 и в два первых столбца в листинге 2 необходимо вводить определенные значения. Иное дело третий столбец ShippedDate в листинге 2. В этом столбце могут находиться неопределенные значения NULL, поскольку дата фактической отправки заказа неизвестна в момент ввода сведений о заказе, она вводится в столбец ShippedDate позднее, когда заказ уже отправлен. Поэтому часть кода, обозначенная в листинге 2 меткой В, присваивает значение True свойству AllowNulls третьего столбца, после чего можно помещать в него неопределенные значения. По умолчанию значение этого свойства равно False. Создание таблицы с внешним ключомТаблица товаров Products из листинга 1 и таблица заказов Orders из листинга 2 связаны между собой отношением многие-ко-многим. Такое отношение имеет место, потому что один и тот же товар может войти в один или несколько заказов, а каждый заказ может включать многие товары. Чтобы отразить такое отношение в разрабатываемом в данной статье проекте базы данных, необходимо внести в этот проект два изменения. Во-первых, нужно переделать проект таблицы товаров Products таким образом, чтобы у нее тоже появился первичный ключ. Во-вторых, необходимо добавить в базу данных новую таблицу, которая свяжет таблицы Products и Orders. В этой таблице будут храниться общие данные доменов обеих таблиц. К примеру, в ней можно хранить количество определенного товара, которое указывается в отдельной строке заказа. Новую таблицу, которая связывает таблицы Products и Orders, назовем OrderDetails. В примере проекта Access, который разбирается в данной статье, включены процедуры, автоматизирующие процесс добавления первичного ключа в таблицу товаров Products. Файл, содержащий эти процедуры, размещен на сайте американской версии журнала SQL Server Magazine. Идентификатор исходной статьи равен 20350. Чтобы вызвать эти процедуры, из окна редактора Visual Basic Editor запустите процедуру CallRemoveOriginalProductIDColumnAndAddNewProductIDColumn для данного проекта. Эта процедура вызывает две другие процедуры. Первая вызываемая процедура удаляет исходный столбец идентификатора товара ProdID из таблицы товаров Products. Вторая вызванная процедура добавляет на место удаленного столбца столбец ProdID. У замещающего столбца активизировано свойство Identity, и этому столбцу поставлен в соответствие ключевой объект Key. У объекта Key значение свойства Type отвечает первичному ключу. В листинге 3 показаны две процедуры, предназначенные для создания таблицы связей OrderDetails. У этой таблицы имеются внешние ключи, которые ссылаются на таблицы Orders и Products. В листинге 3 показан также синтаксис, применяемый при построении первичного ключа на основе нескольких столбцов. Часть кода, обозначенная в листинге 3 меткой А, сначала добавляет в таблицу два столбца. Третий блок кода, относящегося к этой метке, создает внешний ключ. Код, создающий внешний ключ, начинается с генерации экземпляра объекта ключа Key. После присвоения имени этому объекту код присваивает его свойству Type признак внешнего ключа foreign key. Вслед за этим код добавляет свойство Name объекта col1, содержащее название этого столбца, в принадлежащую ключу коллекцию названий ключевых столбцов KeyColumns. В соответствии со спецификацией столбец OrderID таблицы OrderDetails назначается локальным столбцом внешнего ключа. Затем код назначает таблицу Orders и ее столбец OrderID соответственно таблицей и столбцом, на которые будут формироваться ссылки. После настройки всех этих свойств код, формирующий первый внешний ключ, завершает свою работу добавлением созданного ключа в коллекцию ключей Keys таблицы OrderDetails. Остальная часть кода метки А создает внешний ключ, который будет указывать на столбец идентификатора товара ProdID в таблице товаров Products из столбца ProdID таблицы OrderDetails.
ЛИСТИНГ 3: Создание таблицы с подробными сведениями о заказах OrderDetails.
Sub CallAddOrderDetailsTable()
Dim srvname As String
Dim loginname As String
Dim pwd As String
Dim dbname As String
Dim tblname As String
`Сформировать указатели для объекта SQLServer, входного имени,
`пароля и имени базы данных.
srvname = "cablat"
loginname = "sa"
pwd = ""
dbname = "SQLMagTablesSQL"
tblname = "OrderDetails"
AddOrderDetailsTable srvname, _
loginname, pwd, dbname, tblname, _
"name", "OrderID", _
"datatype", "int", _
"name", "ProdID", _
"datatype", "int", _
"name", "Quantity", _
"datatype", "int"
End Sub
Sub AddOrderDetailsTable(srvname As String, _
loginname As String, pwd As String, _
dbname, tblname, _
ParamArray ColSpecs() As Variant)
Dim srv1 As SQLDMO.SQLServer
Dim tbl1 As SQLDMO.Table
Dim col1 As SQLDMO.Column
Dim col2 As SQLDMO.Column
Dim key1 As SQLDMO.Key
Dim key2 As SQLDMO.Key
Dim key3 As SQLDMO.Key
Dim col3 As SQLDMO.Column
`Подсоединиться к указанному объекту SQLServer.
Set srv1 = New SQLDMO.SQLServer
srv1.Connect srvname, loginname, pwd
`Создать экземпляр таблицы и назвать ее.
Set tbl1 = New SQLDMO.Table
tbl1.Name = tblname
BEGIN CALLOUT A
`Добавить целочисленный тип данных.
Set col1 = New SQLDMO.Column
col1.Name = ColSpecs(1)
col1.DataType = ColSpecs(3)
tbl1.Columns.Add col1
`Добавить целочисленный тип данных.
Set col2 = New SQLDMO.Column
col2.Name = ColSpecs(5)
col2.DataType = ColSpecs(7)
tbl1.Columns.Add col2
`Добавить внешний ключ,
`указывающий на таблицу Orders.
Set key1 = New SQLDMO.Key
key1.Name = "OrderIDFK"
key1.Type = SQLDMOKey_Foreign
key1.KeyColumns.Add col1.Name
key1.ReferencedTable = "Orders"
key1.ReferencedColumns.Add "OrderID"
tbl1.Keys.Add key1
`Добавить внешний ключ,
`указывающий на таблицу Products.
Set key2 = New SQLDMO.Key
key2.Name = "ProdIDFK"
key2.Type = SQLDMOKey_Foreign
key2.KeyColumns.Add col2.Name
key2.ReferencedTable = "Products"
key2.ReferencedColumns.Add "ProdID"
tbl1.Keys.Add key2
END CALLOUT A
BEGIN CALLOUT B
`Добавить первичный ключ, состоящий из двух столбцов.
Set key3 = New SQLDMO.Key
key3.Name = "OrderIDAndProdIDPK"
key3.Type = SQLDMOKey_Primary
key3.Clustered = False
str1 = col1.Name
key3.KeyColumns.Add str1
str1 = col2.Name
key3.KeyColumns.Add str1
tbl1.Keys.Add key3
END CALLOUT B
`Добавить тип данных datetime, допускающий неопределенные значения.
Set col3 = New SQLDMO.Column
col3.Name = ColSpecs(9)
col3.DataType = ColSpecs(11)
tbl1.Columns.Add col3
`Перед добавлением новой таблицы в назначенную
` базу данных удалить предыдущую версию таблицы,
` если она существует.
On Error Resume Next
srv1.Databases(dbname).Tables(tblname).Remove
srv1.Databases(dbname).Tables.Add tbl1
`Очистить объекты.
Set col1 = Nothing
Set col2 = Nothing
Set key1 = Nothing
Set key2 = Nothing
Set key3 = Nothing
Set col3 = Nothing
Set tbl1 = Nothing
srv1.Disconnect
Set srv1 = Nothing
End Sub
Часть кода, обозначенная в листинге 3 меткой В, демонстрирует синтаксис, применяемый для формирования первичного ключа из нескольких столбцов таблицы. Этот синтаксис аналогичен тому, который используется в коде, отмеченном символом А. Отличие заключается в том, что на этот раз метод добавления Add применяется к элементам коллекции ключа KeyColumns. Метод применяется по одному разу для каждого столбца, входящего в определение первичного ключа. К примеру, код возле метки В вызывает метод один раз для значения свойства Name объекта col1, а второй раз - для значения свойства Name объекта col2. Другое отличие этого примера кода установления первичного ключа от кода, приведенного в листинге 2, заключается в том, что данный код определяет некластеризованный индекс. Объекты SQL-DMO представляют собой мощное средство автоматизации решения административных задач, которые обычно выполняются в SQL Server вручную. Приведенные в данной статье три примера кода читатели могут использовать для разработки собственных систем автоматизированного построения таблиц. Практическое применение предложенного подхода подскажет новые идеи относительно использования объектов SQL-DMO для автоматизации различных процессов. |
|
|
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
|