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

Форматирование пользовательского ввода для динамического запроса

1. В окне Visual Studio перейдите на вкладку Form1.vb [Design].
2. Перетащите элемент управления Button (Кнопка) под элемент управления ListView (Список). В окне Properties (Свойства) измените текст на "Выполнить запрос".
3. Дважды щелкните на элементе Button (Кнопка). Visual Studio создаст процедуру с именем Button1_Click. Добавьте в эту процедуру следующий код:

   Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) 
      Handles Button1.Click 
      Dim baseSQL As String = "SELECT {0} FROM {1}" 
      Dim sbFields As New System.Text.StringBuilder 
      Dim numChecked As Integer = 0 
      With sbFields
        For Each el As ListViewItem In ListView1.Items 
          If el.Checked Then
            numChecked = numChecked + 1 
            If .Length <> 0 Then
               .Append(",") 
            End If
            Console.WriteLine(el.Text) 
            AppendFormat("[{0}]", el.Text) 
          End If 
        Next 
      End With
      Console.WriteLine(sbFields) 
   End Sub

   Примечание. Если вам нужно выполнить со строкой более двух манипуляций, лучше использовать объект StringBuilder, чтобы получить результат, а затем использовать соединение строк.
4. Постройте и запустите проект. Выберите несколько столбцов в элементе управления ListView, нажмите кнопку "Выполнить запрос" и изучите список столбцов с разделителем-запятой, который отображается в панели Output (Вывод).
5. Закройте форму, чтобы завершить работу приложения.
6. В завершение можно завершить создание динамической инструкции, добавив список столбцов и имя таблицы. Поскольку таблица может принадлежать схеме, в запросе лучше использовать полное уточненное имя таблицы.

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

Строим и выполняем динамический запрос

1. Замените предложение Console.WriteLine в конце процедуры Button1_Click следующим кодом, который создает строку динамического запроса:

   Dim tblsql As String 
     Dim txtsql As String
     tblsql = String.Format("{0}.{1}", SchemaName, TableName)
     txtsql = String.Format(baseSQL, _ 
              sbFields.ToString, tblsql)
2. После этого добавьте в конце процедуры Button1_Click следующий код для выполнения динамического запроса. Для этого примера первые 100 строк результата отображаются в панели Output (Вывод). В реальном приложении следует использовать в качестве DataSource для элемента управления DataGridView значение DataTable. Элементы управления DataGridView идеально подходят для отображения результатов запроса для пользователя.

   Dim MyConnection As New SqlClient.SqlConnection( _
              "Data Source=.\SQLExpress;" & _
              "Initial Catalog=AdventureWorks;Trusted_Connection=Yes;") 
     Dim com As New SqlClient.SqlCommand(txtsql, MyConnection) 
     Dim tableResults As New DataTable tableResults.Clear()
     Dim da As New SqlClient.SqlDataAdapter(com) 
     
     da.Fill(tableResults) 
     For i As Integer = 0 To tableResults.Rows.Count - 1
       With tableResults.Rows.Item(i)
         Dim rowstr As New System.Text.StringBuilder 
         For j As Integer = 0 To numChecked - 1 
           rowstr.Append(.Item(j)) 
           rowstr.Append(" ") 
         Next
         Console.WriteLine(rowstr) 
       End With
       If i > 100 Then Exit For 
     Next
3. Постройте и запустите проект. Выберите несколько столбцов в элементе управления ListView и нажмите кнопку Выполнить запрос. Результаты динамического запроса отображаются в панели Output (Вывод). Вы можете выбрать другие столбцы и нажать кнопку еще раз, чтобы построить и выполнить другой динамический запрос.
4. Закройте форму, чтобы завершить работу приложения.

Выполнение динамического запроса от имени другой учетной записи

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

В SQL Server 2005 для того, чтобы действовать от имени другого пользователя в рамках определенной команды, можно выполнить команду EXECUTE. Синтаксис команды EXECUTE:

EXECUTE ("<dynamic query>") as 
        USER='<User Name>'

Динамическая сортировка и фильтрация

Когда запрос возвращает большой набор записей, результаты будут полезнее, если пользователь сможет задать порядок возвращения строк, а также то, какие строки следует отфильтровать.

Добавление порядка сортировки в динамический запрос

Чтобы выполнить сортировку динамического запроса, просто добавьте предложение ORDER BY, указав после него список столбцов в порядке, выбранном пользователем. Если пользователь хочет получить информацию в порядке убывания, добавьте также ключевое слово DESC.

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

Public Class SortInfo
Public SchemaName As String
Public TableName As String
Public ColumnName As String
Public SortOrder As SortOrder
Public SortPosition As Integer End Class

Чтобы создать упорядоченный список, код проходит по этому списку, добавляя определение в StringBuilder, и в случае, если задано какое-либо упорядочение, добавляет предложение ORDER TO в начале содержимого StringBuilder.

For Each o As System.Collections.Generic.KeyValuePair( _ 
           Of Integer, SortInfo) In OrderList 
  With sbOrderBy
    If .Length <> 0 Then
       .Append(",") 
    End If
    .AppendFormat("[{0}].[{1}]", o.Value.TableName, _
                  o.Value.ColumnName) 
    If o.Value.SortOrder = SortOrder.Descending Then
       .Append(" DESC") 
    End If 
  End With 
Next 
If sbOrderBy.Length > 0 Then
  sbOrderBy.Insert(0, " ORDER BY ") 
End If

Фильтрация динамического запроса

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

SELECT <Field List> 
   FROM <Table Name> 
   WHERE <Condition List> 
   ORDER BY <Order List>

Список условий следует проектировать с учетом следующих рекомендаций:

• Используйте шаблон <ColumnName> <compare operator> <value>.
• Сопоставьте значение datatype столбцу datatype.
• Добавьте дополнительные фильтры в Condition_List с помощью ключевых слов AND или OR.

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

Законченный пример - приложение Dynamic Query

Следующие примеры основаны на примере приложения Dynamic Queries, которое находится в папке Filters в файлах примеров к 3 лекции. Пример приложения Dynamic Queries - это расширенная версия приложения, которое мы создали в этой лекции. Это приложение включает элемент управления ListView, который показывает все настройки, сделанные пользователем для создания динамического запроса, как показано на рис. 3.1. Чтобы попробовать сделать то же самое, выполните действия, описанные ниже.

Рис. 3.1. Элемент управления ListView с информацией о запросе, который нужно создать пользователю