Опубликован: 29.07.2008 | Доступ: свободный | Студентов: 1172 / 115 | Оценка: 4.49 / 4.15 | Длительность: 17:53:00
Лекция 3:

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

< Лекция 2 || Лекция 3: 1234 || Лекция 4 >
Форматирование пользовательского ввода для динамического запроса
  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. Чтобы попробовать сделать то же самое, выполните действия, описанные ниже.

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

Рис. 3.1. Элемент управления ListView с информацией о запросе, который нужно создать пользователю
< Лекция 2 || Лекция 3: 1234 || Лекция 4 >
Гаральд Егоркин
Гаральд Егоркин
Россия
Павел Шелякин
Павел Шелякин
Россия