| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
Создание таблиц средствами 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 для автоматизации различных процессов. |
|
| ||||||||||||||||
|