Visual studio профилирование sql запросов. Динамическое построение запросов

В окне 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). Для этого, конечно, необходимо знать названия таблиц и полей, и, в основном, создавалась с целью удобства в реализации программного обеспечения, но также этим могут воспользоваться и другие «посвященные» пользователи.

В этой статье вы узнаете что такое 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();
}

}

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

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

Аннотация: Прочитав эту лекцию, вы сможете: создавать запросы при помощи Конструктора запросов 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 :

Пример создания локальной базы данных Microsoft SQL Server в MS Visual Studio

В данной теме показано решение задачи создания базы данных типа SQL Server с помощью MS Visual Studio . Рассматриваются следующие вопросы:

• работа с окном Server Explorer в MS Visual Studio ;
• создание локальной базы данных типа SQL Server Database ;
• создание таблиц в базе данных;
• редактирование структур таблиц;
• связывание таблиц базы данных между собой;
• внесение данных в таблицы средствами MS Visual Studio .

Условие задачи

Используя средства MS Visual Studio создать базу данных типа MS SQL Server с именем Education. База данных содержит две таблицы Student и Session. Таблицы между собой связаны по некоторыму полю.

Структура первой таблицы «Student».

Структура второй таблицы “Session ”.

Выполнение

1. Загрузить MS Visual Studio .

2. Активировать окно Server Explorer .

Для работы с базами данных корпорация Microsoft предлагает облегченный сервер баз данных Microsoft SQL Server . Существуют разные версии Microsoft SQL Server , например: Microsoft SQL Server 2005 , Microsoft SQL Server 2008 , Microsoft SQL Server 2014 и прочие версии.

Загрузить эти версии можно с сайта Microsoft www.msdn.com .

Этот сервер отлично подходит для работы с базами данных. Он бесплатен и имеет графический интерфейс для создания и администрирования баз данных с помощью SQL Server Management Tool .

Прежде всего, перед созданием базы данных, нужно активировать утилиту Server Explorer . Для этого, в MS Visual Studio нужно вызвать (рис. 1)

View -> Server Explorer

Рис. 1. Вызов Server Explorer

После вызова окно Server Explorer будет иметь приблизительный вид, как показано на рисунке 2.

Рис. 2. Окно Server Explorer

3. Создание базы данных “Education”.

Чтобы создать новую базу данных, базирующуюся на поставщике данных Microsoft SQL Server , нужно кликнуть на узле Data Connections, а потом выбрать “Create New SQL Server Database … ” (рис. 3).

Рис. 3. Вызов команды создания базы данных SQL Server

В результате откроется окно «Create New SQL Server Database » (рис. 4).

В окне (в поле «Server Name») указывается имя локального сервера, установленного на вашем компьютере. В нашем случае это имя “SQLEXPRESS ”.

В поле «New database name: » указывается имя создаваемой базы данных. В нашем случае это имя Education.

Опцию Use Windows Autentification нужно оставить без изменений и нажать кнопку OK .

Рис. 4. Создание новой базы данных SQL Server 2008 Express с помощью MS Visual Studio 2010

После выполненных действий, окно Server Explorer примет вид, как показано на рисунке 5. Как видно из рисунка 5, в список имеющихся баз данных добавлена база данных Education с именем

sasha-pc\sqlexpress.Education.dbo

Рис. 5. Окно Server Explorer после добавления базы данных Education

4. Объекты базы данных Education.

Если развернуть базу данных Education (знак «+ »), то можно увидеть список из следующих основных объектов:

• Database Diagrams – диаграммы базы данных. Диаграммы показывают связи между таблицами базы данных, отношения между полями разных таблиц и т.п.;
• Tables – таблицы, в которых помещаются данные базы данных;
• Views – представления. Отличие между представлениями и таблицами состоит в том, что таблицы баз данных содержат данные, а представления данных не содержат их, а содержимое выбирается из других таблиц или представлений;
• Stored procedures – хранимые процедуры. Они представляют собою группу связанных операторов на языке SQL, что обеспечивает дополнительную гибкость при работе с базой данных.

5. Создание таблицы Student.

На данный момент база данных Education абсолютно пустая и не содержит никаких объектов (таблиц, сохраненных процедур, представлений и т.д.).

Чтобы создать таблицу, нужно вызвать контекстное меню (клик правой кнопкой мышки) и выбрать команду “Add New Table ” (рисунок 6).

Рис. 6. Команда добавления новой таблицы

Существует и другой вариант добавления таблицы базы данных с помощью команд меню Data:

Data -> Add New -> Table

Рис. 7. Альтернативный вариант добавления новой таблицы

В результате откроется окно добавления таблицы, которое содержит три столбца (рисунок 8). В первом столбце “Column Name” нужно ввести название соответствующего поля таблицы базы данных. Во втором столбце “Data Type” нужно ввести тип данных этого поля. В третьем столбце “ Allow Nulls ”указывается опция о возможности отсутствия данных в поле.

Рис. 8. Окно создания новой таблицы

С помощью редактора таблиц нужно сформировать таблицу Student как изображено на рисунке 9. Имя таблицы нужно задать при ее закрытии.

В редакторе таблиц можно задавать свойства полей в окне Column Properties. Для того, чтобы задать длину строки (nvchar) в символах, в окне Column Properties есть свойство Length. По умолчанию значения этого свойства равно 10.

Рис. 9. Таблица Student

Следующим шагом нужно задать ключевое поле. Это осуществляется вызовом команды “Set Primary Key ” из контекстного меню поля Num_book. С помощью ключевого поля будут установлены связи между таблицами. В нашем случае ключевым полем есть номер зачетной книжки.

Рис. 10. Задание ключевого поля

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

Рис. 11. Таблица Student после окончательного формирования

Теперь можно закрыть таблицу. В окне сохранения таблицы нужно задать ее имя – Student (рис. 12).

Рис. 12. Ввод имени таблицы Student

6. Создание таблицы Session.

По образцу создания таблицы Student создается таблица Session.

На рисунке 13 изображен вид таблицы Session после окончательного формирования. Первичный ключ (Primary Key ) устанавливается в поле Num_book. Имя таблицы задается Session.

Рис. 13. Таблица Session

После выполненных действий, в окне Server Explorer будут отображаться две таблицы Student и Session.

Таким образом, в базу данных можно добавлять любое количество таблиц.

7. Редактирование структуры таблиц.

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

Для того, чтобы вносить изменения в таблицы базы данных в MS Visual Studio, сначала нужно снять опцию “Prevent Saving changes that require table re-creation ” как показано на рисунке 14. Иначе, MS Visual Studio будет блокировать внесения изменений в ранее созданную таблицу. Окно Options, показанное на рисунке 14 вызывается из меню Tools в такой последовательности:

Tools -> Options -> Database Tools -> Table and Database Designers

Рис. 14. Опция “Prevent Saving changes that require table re-creation ”

После настройки можно изменять структуру таблицы. Для этого используется команда “Open Table Definition ” (рисунок 15) из контекстного меню, которая вызывается для выбранной таблицы (правый клик мышкой).

Рис. 15. Вызов команды “Open Table Definition ”

Также эта команда размещается в меню Data:

Data -> Open Table Definition

Предварительно таблицу нужно выделить.

8. Установление связей между таблицами.

В соответствии с условием задачи, таблицы связаны между собою по полю Num_book.

Чтобы создать связь между таблицами, сначала нужно (рисунок 16):

• выделить объект Database Diagram;
• выбрать команду Add New Diagram из контекстного меню (или из меню Data).

Рис. 16. Вызов команды добавления новой диаграммы

В результате откроется окно добавления новой диаграммы Add Table (рисунок 17). В этом окне нужно выбрать последовательно две таблицы Session и Student и нажать кнопку Add.

Рис. 17. Окно добавления таблиц к диаграмме

Рис. 18. Таблицы Student и Session после добавления их к диаграмме

Чтобы начать устанавливать отношение между таблицами, надо сделать клик на поле Num_book таблицы Student, а потом (не отпуская кнопку мышки) перетянуть его на поле Num_book таблицы Session.

В результате последовательно откроются два окна: Tables and Columns (рис. 19) и Foreign Key Relationship (рис. 20), в которых нужно оставить все как есть и подтвердить свой выбор на OK.

В окне Tables and Columns задается название отношения (FK_Session_Student ) и названия родительской (Student) и дочерней таблиц.

Рис. 19. Окно Tables and Columns

Рис. 20. Окно настройки свойств отношения

После выполненных действий будет установлено отношение между таблицами (рисунок 21).

Рис. 21. Отношение между таблицами Student и Session

Сохранение диаграммы осуществляется точно также как и сохранение таблицы. Имя диаграммы нужно выбрать на свое усмотрение (например Diagram1).

После задания имени диаграммы откроется окно Save, в котором нужно подтвердить свой выбор (рисунок 22).

Рис. 22. Подтверждение сохранения изменений в таблицах

9. Ввод данных в таблицы.

Система Microsoft Visual Studio разрешает непосредственно вносить данные в таблицы базы данных.

В нашем случае, при установлении связи (рис. 19) первичной (Primary Key Table ) избрана таблица Student. Поэтому, сначала нужно вносить данные в ячейки именно этой таблицы. Если попробовать сначала внести данные в таблицу Session, то система заблокирует такой ввод с выводом соответствующего сообщения.

Чтобы вызвать режим ввода данных в таблицу Student, нужно вызвать команду Show Table Data из контекстного меню (клик правой кнопкой мышки) или с меню Data (рис. 23).

Рис. 23. Команда Show Table Data

Откроется окно, в котором нужно ввести входные данные (рис. 24).

Рис. 24. Ввод данных в таблице Student

После внесения данных в таблицу Student нужно внести данные в таблицу Session.

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

Например, если в поле Num_book таблицы Student введены значения “101”, “102”, “103” (см. рис. 24), то следует вводить именно эти значения в поле Num_book таблицы Session. Если попробовать ввести другое значение, система выдаст приблизительно следующее окно (рис. 25).

Рис. 25. Сообщение об ошибке ввода данных связанных таблиц Student и Session

Таблица Session с введенными данными изображена на рисунке 26.