Создание sql запросов в visual studio. Динамическое построение запросов. Интерфейс пользователя для построения запросов

В окне Server Explorer, где представлены таблицы базы данных на Microsoft SQL Server в виде дерева (см. рис. 4.1.), для каждой таблицы можно открыть окно, нажатием на кнопку Show Table Data. Именно в этом окне предоставляется возможность редактирования данных таблицы. Выглядит это окно редактирования приблизительно так, как представлено на рис. 4.2. Приблизительно, потому что полный объем не вошел в рисунок в виду большого количества колонок.

      1. Реализация отображения базы данных

Отображение базы данных (компонент DataSet) необходим для того, чтобы исключить лишние обращения к базе данных и к серверу. Это очень важная особенность, ведь если с базой работают множество пользователей, нагрузка на сервер может быть довольно большой – количество запросов во много раз больше, что приведет к замедлению работы исполнения запросов.

Поэтому, за счет DataSet на компьютере клиента находится отображение базы, с которой он работает. По завершении работы базы клиента и сервера синхронизируются (вернее, в серверную базу вносятся изменения, сделанные в базе данных клиента)

Отображение нашей базы данных на MS Visual Studio 2005 выглядит следующим образом:

Все числовые данные имеют тип Int32, строковые – String. Размеры строковых данных соответствуют тем, которые приведены в физической модели базы данных.

На скрине видно, что в каждой таблице имеются еще и TableAdapter’ы. Это своего рода мосты для соединения DataSet и источником данных. Здесь имеются такие методы, как Fill и GetData()

Этот метод предназначен для того, чтобы заполнять таблицу нужными данными из источника (файл базы данных)

    Метод GetData()

Предназначен для того, чтобы брать данные из таблицы отображения.

    1. Реализация программного обеспечения

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

      1. Интерфейс программы

Главное окно представляет собой MDI приложение, которое имеет следующее меню:

Каждая из таблиц для редактирования БД вызывается соответствующим пунктом меню «Таблицы».

Отчет о нагрузке на преподавателей, также создается по нажатию на соответствующий пункт меню «Отчеты».

Как дополнительная возможность программного обеспечения, существует пункт меню «Консоль», которая позволяет исполнять SQL запросы к базе данных, введенные пользователем и выводить результат в таблицу (компонент DataTable). Для этого, конечно, необходимо знать названия таблиц и полей, и, в основном, создавалась с целью удобства в реализации программного обеспечения, но также этим могут воспользоваться и другие «посвященные» пользователи.

Аннотация: Прочитав эту лекцию, вы сможете: создавать запросы при помощи Конструктора запросов SQL Server Management Studio, извлекать информацию о базе данных из системных таблиц базы данных, динамически создавать простые запросы на основе пользовательского ввода, форматировать пользовательский ввод и фильтровать сложные динамические запросы, выполнять синтаксический анализ и переформатировать данные для использования в фильтре, защитить базу данных от атак типа "SQL-injection", использовать процедуру sp_executeSql для передачи запроса

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

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

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

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

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

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

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

Применение INFORMATION_SCHEMA

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

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

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

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

Обратите внимание на то, что для получения схемы для таблицы нужно выбрать поле 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

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

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

Приведенный выше код на 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 :

В этой статье вы узнаете что такое LINQ и как с помощью него делать запросы к спискам.

Что такое LINQ? Language Integrated Query (LINQ) - язык интегрированных запросов, а точнее неоднозначный проект Microsoft по добавлению синтаксиса языка запросов похожий на SQL. Специфическое определение, с помощью этого инструмента можно относительно просто создавать запросы к таблицам и спискам данных, не обязательно к базе данных.

В примерах от Microsoft упускается одна маленькая, но важная деталь. Для работы с набором данных нам нужно использовать структуру DataContext, определение которой можно получить с помощью SPMetal.exe для списка или библиотеки присутствующей на сервере Sharepoint. Иными словами нам сначала необходимо определить этот тип данных, а потом уже создавать запрос Вообще рекомендую воспользоваться специальной утилитой для работы в MVS: Imtech Get SPMetal Definition Extension .

Для работы нам понадобится какой-нибудь список. Воспользуемся простым списком - "настраиваемый список"; название - "Простое обращение"; поля по умолчанию: Номер, Название.

И так, давайте создадим простую (смотрите соответствующую страничку), с помощью которой будем наблюдать результат наших манипуляций. При создании проекта выберем изолированную модель доверия, если есть необходимость работать со списками или данными других узлов, тогда необходимо создавать проект для фермы.

Далее создадим файл класс "ПростоеОбращение.cs" с помощью SPMetal.exe или плагина для студии. Добавим ссылку на библиотеку Microsoft.Sharepoint.Linq. Более подробную информацию можно получить на сайте MSDN .

Создадим функцию Render и my_mess. В последней функции будем формировать данные для отображения.

// перегружаем функцию
protected override void Render(HtmlTextWriter writer)
{
base .Render(writer);
my_mess(writer);
}

// Наша функция для работы со списком
public void my_mess(HtmlTextWriter writer)
{
// Создадим контекст
DataContext data = new DataContext("http://localhost" );
// Свяжем его с соответствующим списке на сайте в корне
EntityList<Элемент> Simple_Message = data.GetList<Элемент>("Простое обращение" );
// Выполним запрос - выберем все строки из списка "Простое обращение"
var query = from mess in Simple_Message
select mess;
// отобразим все данные из результата запроса
foreach (var elem in query)
{
writer.WriteLine("Элемент списка:" + elem.Название.ToString());
writer.WriteBreak();
}

}

Компилируем проект и добавляем на страничку. В результате отобразятся все строки находящиеся в списке.

Продолжение позже (статья не окончена)...