Динамическое построение запросов

В предыдущей лекции рассказывалось о том, как повысить производительность запросов. Теперь вы знаете, как создать эффективный набор запросов, чтобы предоставить пользователям наиболее полезную информацию от вашего приложения с помощью заранее созданных запросов в хранимых процедурах или представлениях.

Однако в любых приложениях, кроме самых простых, невозможно заранее узнать все возможные варианты типов информации, которые могут понадобиться пользователям, и как они захотят отфильтровать и упорядочить ее. Вместо того, чтобы пытаться предусмотреть все такие возможности, можно предоставить пользователю управление сообщаемой приложением информацией. В этой лекции рассказывается о том, как динамически строить запросы на основе выбора, который пользователь делает в процессе выполнения рабочего цикла.

Интерфейс пользователя для построения запросов

Среда SQL Server Management Studio включает сложный интерфейс для построения запросов. Давайте изучим этот интерфейс, чтобы у вас сформировалось представление о том, как можно создавать запросы динамически. Вашему приложению не понадобятся все элементы управления, которые предоставляет среда SQL Server Management Studio. По сути, нужно тщательно продумать, как наилучшим образом ограничить пользователям возможности выбора.

Создаем запрос при помощи Конструктора запросов SQL Server Management Studio

1. В SQL Server Management Studio, если нужно, присоедините базу данных Adventure Works, а затем разверните базу данных в Object Explorer (Обозревателе объектов), как показано на рисунке.
   

   
   увеличить изображение
   
   Совет. О том, как присоединить базу данных, рассказывается в лекциях 6-7 курса "Разработка и защита баз данных в Microsoft SQL Server 2005", которая называется "Перенос базы данных на другие системы".
2. Разверните узел Tables (Таблицы) в Object Explorer (Обозревателе объектов).
3. Найдите таблицу Sales.Customer. Щелкните правой кнопкой мыши на этой таблице и выберите из контекстного меню команду Open Table (Открыть таблицу). Таблица будет выведена на экран, как показано на следующем рисунке (На этом рисунке окно Properties (Свойства) закрыто).
   

   
   увеличить изображение
   
4. В SQL Server Management Studio появилась панель инструментов Query Designer (Конструктор запросов), показанная на рисунке. Первые три кнопки этой панели инструментов отображают таблицы вашего запроса, список выбранных столбцов и действительный код SQL, который был сгенерирован.
   

   
   
5. Нажмите первую кнопку Show Diagram Pane (Показать область схемы), затем выберите столбцы CustomerID и AccountNumber.
6. Нажмите вторую кнопку - Show Criteria Pane (Показать область условий). Снимите флажок Output (Вывод) в строке *. Щелкните мышью в столбце Sort Type (Тип сортировки) в строке CustomerID и выберите из раскрывающегося списка пункт Ascending (По возрастанию).
7. При необходимости переместите видимую область окна вправо, чтобы отобразить столбец Filter (Фильтр). В той же строке CustomerID введите <10 и нажмите клавишу Enter.
8. Нажмите третью кнопку - Show SQL Pane (Показать область SQL кода). Окно программы примет следующий вид:
   

   
   увеличить изображение
   
9. Обратите внимание на строку запроса, которую программа SQL Server Management Studio сгенерировала в области SQL кода. Нажмите на панели инструментов кнопку Execute SQL (Выполнить SQL код). В панель Results (Результаты) будет возвращено 9 записей.

   Пользователям ваших приложений, как правило, не нужно ничего делать со строкой запроса. Они ничего не знают об SQL. Ваша обязанность - правильно построить строку запроса, либо на этапе проектирования, либо через код приложения в процессе выполнения рабочего цикла. Динамическое построение запроса во время рабочего цикла позволяет ввести в строку запроса пользовательский ввод.

Извлечение информации о таблицах базы данных

Чтобы предоставить пользователю список параметров, приложению, вероятно, придется извлечь информацию о таблицах базы данных. Существует несколько способов получить эту информацию. Самый важный из этих методов - использование схемы INFORMATION_SCHEMA. Эта схема является стандартной в любой базе данных.

Применение INFORMATION_SCHEMA

Схема INFORMATION_SCHEMA - это особая схема, которая есть в каждой базе данных. Она содержит определения некоторых объектов базы данных.

INFORMATION_SCHEMA соответствует стандарту ANSI, который предназначен для извлечения информации от любого ANSI-совмести-мого ядра базы данных. В SQL Server INFORMATION_SCHEMA состоит из набора представлений, которые запрашивают таблицы базы данных sys*, содержащие информацию о структуре базы данных. Запрос к этим таблицам можно выполнить напрямую, точно так же, как к любым таблицам базы данных. Однако в большинстве случаев для того, чтобы извлечь информацию из таблиц *sys, лучше использовать представления схемы INFORMATION_SCHEMA.

Примечание. Схема INFORMATION_SCHEMA иногда запрашивает таблицы, которые не нужны, что наносит ущерб производительности. В следующем примере данной лекции это не особенно важно, потому что приложение уже ожидало пользовательского ввода. Однако это следует учитывать, если скорость является важным аспектом для вашего приложения.

Вот базовый код T-SQL, который используется для получения информации о столбцах, входящих в таблицу:

SELECT TABLE_SCHEMA,
       TABLE_NAME,
       COLUMN_NAME,
       ORDINAL_POSITION,
       DATA_TYPE 
  FROM INFORMATION_SCHEMA.COLUMNS 
  WHERE TABLE_NAME = "<TABLE_NAME>")

Обратите внимание на то, что для получения схемы для таблицы нужно выбрать поле TABLE_SCHEMA. Это может иметь значение для создания аналогичных запросов в дальнейшем. Чтобы экспериментировать с методами, описанными в данной лекции, создайте новый проект в Visual Studio.

Создаем новый проект Visual Studio

1. Выберите из меню Start (Пуск) команды All Programs, Microsoft Visual Studiio 2005, Microsoft Visual Studio 2005.
2. В меню Visual Studio выберите команды File, New, Project (Файл, Создать, Проект).
3. В панели Project Types (Типы проектов) разверните узел Visual Basic (Решения Visual Basic) и выберите в панели Templates (Шаблоны) шаблон Application (Приложение). Дайте проекту имя Chapter7 и нажмите кнопку ОК,
4. Приложение для этого примера можно найти в файлах примеров в папке \Chapter7\DynQuery. Вы можете вырезать и вставлять код для следующих процедур из файла Form1.vb.

Получение списка таблиц и представлений

Как правило, вам нужно будет предоставить пользователю возможность выбрать не только столбцы, но и таблицу для динамического запроса, поэтому придется отобразить для пользователя список таблиц. Необходимую информацию можно получить при помощи следующего запроса:

SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE 
   FROM INFORMATION_SCHEMA.TABLES

В приложении этот запрос можно использовать следующим образом.

Получаем список таблиц

1. Дважды щелкните форму Form1, сгенерированную Visual Studio. На экране появится процедура Form1_Load. Объявите две глобальные переменные и добавьте вызов процедуры RetrieveTables в процедуру Form1_Load, чтобы она выглядела примерно так:

   Public Class Form1
    Dim SchemaName As String = "", 
         TableName As String = ""
    Private Sub Form1_Load(ByVal sender As System.Object, _
                           ByVal e As System.EventArgs) 
       Handles 
       MyBase.Load
       RetrieveTables() 
    End Sub
2. После этого добавьте показанную здесь процедуру RetrieveTables после процедуры Form1_Load. Чтобы эта программа могла выполняться, необходимо, чтобы к экземпляру сервера SQLExpress была присоединена база данных Adventure Work. Узнать о том, как присоединить базу данных, можно в лекции 6-7 курса "Разработка и защита баз данных в Microsoft SQL Server 2005".

   Sub RetrieveTables()
     
     Dim FieldName As String
     Dim MyConnection As New SqlClient.SqlConnection( _ 
                             "Data Source=.\SQLExpress;" & _
                             "Initial Catalog=AdventureWorks;Trusted_
                             Connection=Yes;") 
     Dim com As New SqlClient.SqlCommand( _
                             "SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE " & _ 
                             "FROM INFORMATION_SCHEMA.TABLES", _ 
                             MyConnection) MyConnection.Open()
     Dim dr As SqlClient.SqlDataReader = com.ExecuteReader With dr
     Do While .Read
        "Следует сохранить эту информацию для использования в 
        "форме или на странице
        SchemaName = .GetString(0)
        TableName = .GetString(1)
        FieldName = .GetString(2)
        Console.WriteLine("{0} {1} {2}", _
        SchemaName, TableName, FieldName) Loop .Close() End With
        "Предположим, что пользователь выбрал следующие схему и таблицу: 
        SchemaName = "Sales" 
        TableName = "Customer" 
   End Sub

   Примечание. В реальном приложении строка соединения и строка SQL могут обслуживаться ресурсами приложения или конфигурационным файлом приложения.
3. Выберите из меню Debug (Отладка) команду Start Debugging (Начать отладку), чтобы создать и выполнить проект. Появится пустое окно формы, а в панели Output (Вывод) в окне Visual Studio будет записана информация схемы.
4. Закройте форму, чтобы завершить работу приложения.

Приведенный выше код на Visual Basic инициализирует объект SqlCommand с именем com со строкой SQL, которую нужно выполнить, а затем выполняет объект SqlCommand. Это самый простой способ выполнить предложение T-SQL из приложения.

В качестве упражнения можно разместить схемы и таблицы, полученные в процессе выполнения процедуры Load нашей формы, в пользовательском интерфейсе приложения в виде формы, чтобы пользователь мог выбрать схему и таблицу для работы. Для примера в этой лекции мы предполагаем, что пользователь выбрал схему Sales и таблицу Customer.

После того, как пользователь выбрал таблицу, можно извлечь список столбцов для этой таблицы при помощи того же метода, используя пользовательский ввод в качестве имени таблицы в запросе. Для этого в строку запроса следует ввести заместитель, а затем заменить этот заместитель вызовом String.Format. В приведенном ниже коде заместитель в строке запроса - (0).

Получаем список столбцов

1. Добавьте следующую процедуру RetrieveColumns в код ниже процедуры RetrieveTables:

   Sub RetrieveColumns(ByVal TableName As String)
     MyConnection As New SqlClient.SqlConnection( _ 
                 "Data Source=.\SQLExpress;" & _
                 "Initial Catalog=AdventureWorks;Trusted_Connection=Yes;") 
     Dim sqlStr As String
     sqlStr = "SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, " + _
              "ORDINAL_POSITION, DATA_TYPE " + _ 
              "FROM INFORMATION_SCHEMA.COLUMNS " + _ 
              "WHERE (TABLE_NAME = "{0}")" 
     Dim tableColumns As New DataTable Dim da As New SqlClient.SqlDataAdapter( _
                String.Format(sqlStr, TableName), MyConnection) da.Fill(tableColumns)
     For i As Integer = 0 To tableColumns.Rows.Count - 1 
       With tableColumns.Rows.Item(i) 
          Console.WriteLine("{0} {1} {2}", _ 
                            .Item(1), .Item(2), .Item(3)) 
       End With 
     Next 
   End Sub
2. В процедуру Form1_Load добавьте следующий вызов процедуры RetrieveColumns после процедуры RetrieveTables:

   RetrieveColumns(TableName)
3. Выберите из меню Debug (Отладка) команду Start Debugging (Начать отладку), чтобы создать и выполнить проект. Появится пустое окно формы, а в панели Output (Вывод) в окне Visual Studio будет записана информация о столбцах и таблице.
4. Закройте форму, чтобы завершить работу приложения.

Объект типа DataTable в процедуре RetrieveColumns может быть использован для заполнения элементов управления CheckListBox или ListView с включенными элементами управления CheckBoxes, чтобы пользователь мог выбрать нужные поля.

Добавляем в форму элемент управления ListView

1. В окне Visual Studio перейдите на вкладку Form1.vb [Design].
2. Выберите из меню View (Вид) команду Toolbox (Панель элементов).
3. Перетащите мышью элемент управления Label из панели элементов в форму. Щелкните правой кнопкой мыши на label и выберите из контекстного меню команду Properties (Свойства). В окне Properties (Свойства) измените текст в строке Label на Столбцы.
4. Теперь перетащите мышью элемент управления ListView (Список) из панели элементов в форму. После этого окно должно выглядеть так:
   

   
   увеличить изображение
   
5. Перетащите правую границу ListView (Список), чтобы увеличить ширину списка. Щелкните правой кнопкой мыши на ListView (Список) и выберите из контекстного меню команду Properties (Свойства). В окне Properties (Свойства) задайте для свойства CheckBoxes значение True, а свойство View на List.
6. Щелкните правой кнопкой мыши на форме и выберите из контекстного меню команду View Code (Перейти к коду), чтобы вернуться к нашему коду. В процедуре RetrieveColumns замените предложение Console.WriteLine следующим кодом:

   ListView1.Items.Add(.Item(2))
7. Выполните построение и запустите проект. Вы увидите в форме список столбцов с полями для установки флажков, как показано на рисунке:
   

   
   
8. Закройте форму, чтобы завершить работу приложения.

После того, как пользователь выберет таблицу и столбцы для просмотра, приложение может создать запрос. Базовая структура запроса на выборку включает список столбцов через запятую:

SELECT <Column1>, <Column2>, <Column3> 
   FROM <Table_Name>

Вы легко можете создать список столбцов из элемента управления ListView.