Visual studio запрос с параметром
Теперь, когда мы разобрались с ролью объекта подключения, можно рассмотреть, как отправлять SQL-запросы в базу данных. Тип SqlCommand (порожденный от DbCommand) представляет собой объектно-ориентированное представление SQL-запроса, имени таблицы или хранимой процедуры. Тип команды указывается свойством CommandType, которое принимает значения из перечисления CommandType:
При создании объекта команды можно сразу задать SQL-запрос, передав его с помощью параметра конструктора или непосредственно свойства CommandText. Кроме того, при создании объекта команды необходимо указать подключение, которое будет в нем применяться. Это тоже можно сделать либо через параметр конструктора, либо с помощью свойства Connection, как в следующем фрагменте кода:
Учтите, что на этом этапе SQL-запрос еще не отправляется в базу данных AutoLot, здесь лишь подготавливается состояние объекта команды для дальнейшего использования.
Выполнение команд
После того, как команда определена, ее необходимо выполнить. Существует много способов издания оператора, в зависимости от того, какой возврат ожидается (если ожидается) от команды. Классы Command предлагают следующие методы выполнения:
ExecuteNonQuery()
выполняет команду, но не возвращает вывода;
ExecuteReader()
выполняет команду и возвращает типизированный IDataReader;
ExecuteScalar()
выполняет команду и возвращает значение из первого столбца первой строки любого результирующего набора.
В дополнение к этим методам класс SqlCommand предлагает следующий метод: ExecuteXmlReader() — выполняет команду и возвращает объект XmlReader, который может быть использован для прохода по фрагменту XML, возвращенному из базы данных.
Метод ExecuteNonQuery() обычно используется для операторов UPDATE, INSERT или DELETE, где единственным возвращаемым значением является количество обработанных строк. Однако метод может вернуть результаты, если осуществляется вызов хранимой процедуры с выходными параметрами.
Метод ExecuteReader() выполняет команду и возвращает типизированный объект-читатель данных, в зависимости от используемого поставщика. Возвращенный объект может применяться для итерации по возвращенным записям.
Во многих Случаях бывает необходимо вернуть единственный результат из оператора SQL, такой как количество записей в заданной таблице или текущие дату и время на сервере. В таких ситуациях применяется метод ExecuteScalar().
Этот метод возвращает объект, который при необходимости может быть приведен к соответствующему типу. Если вызванный оператор SQL возвращает только один столбец, метод ExecuteScalar() предпочтительнее использовать перед любым другим методом, извлекающим столбец. Это также применимо к хранимым процедурам, которые возвращают единственное значение.
Параметризированный запрос возвращает данные, удовлетворяющие условиям предложения WHERE в запросе. Например, можно параметризировать список клиентов, чтобы отображать только клиентов из определенного города, добавив WHERE City = @City в конец инструкции SQL, возвращающей список клиентов.
Параметризованные запросы TableAdapter создаются в Конструктор наборов данных. их также можно создать в Windows приложении с помощью команды параметризовать данные источника в меню данные . Команда параметризовать данные источника создает элементы управления в форме, где можно ввести значения параметров и выполнить запрос.
При создании параметризованного запроса используйте нотацию параметра, относящуюся к базе данных, для которой выполняется кодирование. Например, источники данных Access и OleDb используют вопросительный знак "?" для обозначения параметров, поэтому предложение WHERE должно иметь следующий вид: WHERE City = ? .
Создание параметризованного запроса TableAdapter
Порядок создания параметризованного запроса в Конструкторе наборов данных
Создайте новый адаптер таблицы, добавив предложение WHERE с требуемыми параметрами в инструкцию SQL. Дополнительные сведения см. в разделе Создание и настройка адаптеров таблиц.
Добавьте запрос в существующий адаптер таблицы, добавив предложение WHERE с требуемыми параметрами в инструкцию SQL.
Порядок создания параметризованного запроса при разработке формы с привязкой к данным
Выберите на форме элемент управления, который уже привязан к набору данных. дополнительные сведения см. в разделе привязка Windows Forms элементов управления к данным в Visual Studio.
В меню данные выберите команду Добавить запрос.
Заполните диалоговое окно Построитель условий поиска, добавив предложение WHERE с требуемыми параметрами в инструкцию SQL.
Добавление запроса в существующую форму с привязкой к данным
Откройте форму в конструкторе Windows Forms.
В меню данные выберите команду Добавить запрос или смарт-теги данных.
Если команда Добавить запрос в меню Данные отсутствует, выберите на форме элемент управления, отображающий тот источник данных, в который вы хотите добавить параметризацию. Например, если форма отображает данные в элементе управления DataGridView, выберите его. Если форма отображает данные в отдельных элементах управления, выберите любой элемент управления с привязкой к данным.
В области Выбор таблицы источника данных выберите таблицу, в которую необходимо добавить параметризацию.
Введите имя в поле Имя нового запроса, если вы создаете запрос.
Выберите запрос в поле Имя существующего запроса.
В текстовом поле запрос введите запрос, который принимает параметры.
Щелкните ОК.
Элемент управления для ввода параметра и кнопка Загрузка добавляются на форму в элемент управления ToolStrip.
Запрос значений NULL
Параметрам TableAdapter могут быть присвоены значения NULL, если требуется запросить записи, не имеющие текущего значения. Например, рассмотрим следующий запрос, имеющий ShippedDate параметр в своем WHERE предложении:
Если бы это был запрос к TableAdapter, можно запросить все заказы, которые не были отгружены, с помощью следующего кода:
Чтобы разрешить запрос принимать значения NULL:
В Конструктор наборов данных выберите запрос TableAdapter, который должен принимать значения параметров NULL.
В окне Свойства выберите Параметры, а затем нажмите кнопку с многоточием (. ), чтобы открыть Редактор коллекции параметров.
Выберите параметр, допускающий значения NULL, и задайте для свойства AllowDbNull значение true .
Ранее для отправки запросов мы напрямую добавляли данные в выражение sql. Например, для отправки запроса на добавление данных в БД применялось следующее выражение:
В данном случае предполагается, что значения для переменных name и age вводит пользователь.
Что если переменная name получит следующее значение:
В этом случае sql-выражение в итоге будет выглядеть следующим образом:
В итоге в базу данных будет добавлено два объекта. Это относительно безобидный вид подмены sql-выражения, но реальные возможности встраивания зловредных скриптов таковы, что можно вообще потерять данные в БД, если предоставить пользователям подобным образом добавлять данные. Чтобы выйти из этой ситуации, в sql-командах используются параметры.
Для определения параметров используется объект SqlParameter . Этот объект имеет ряд конструкторов, наиболее используемые из них:
SqlParameter(String, Object) : первый параметр конструктора передает имя, а второй - значение параметра
SqlParameter(String, SqlDbType) : первый параметр конструктора передает имя параметра, а второй - его тип в виде объекта SqlDbType
SqlParameter(String, SqlDbType, Int32) : первый параметр конструктора передает имя параметра, второй - его тип в виде объекта SqlDbType , а третий - размер
SqlParameter(String, SqlDbType, Int32, String) : конструктор последовательно принимает значения для имя параметра, его типа, размера и имени стобца в таблице
Для конфигурации параметров можно использовать их свойства, среди которых следует выделить следующие:
SqlDbType : задает или устанавливает тип параметра в виде типа SqlDbType
Direction : хранит направление параметра, которое указывает, будет ли параметр входным или выходным, или он будет сочетать оба варианта, либо он будет возвращать данные из хранимой процедуры
IsNullable : указывает, допускает ли параметр значение null
ParameterName : представляет имя параметра
Size : хранит размер данных параметра
Value : хранит значение параметра
После определения параметров они добавляются в коллекцию Parameters объекта SqlCommand.
Применим параметры для добавления данных:
В данном случае в конструктор передается название параметра и его значение. Причем название параметров начинается со знака @ и должно совпадать с тем названием, которое используется в sql-выражении:
После определения параметра он добавляется в коллекцию параметров команды.
При выполнении команды на место параметров в sql-выражении подставляются их значения. При этом не важно, что параметр @name в значении определяет еще одну команду INSERT - все его значение будет добавлено в столбец name в таблице Users.
В итоге последняя добавленная строка будет содержать в столбце Name довольно длинное имя:
Подобным образом параметры можно включать и в другие типы запросов, а не только при добавлении данных.
Установка для параметра типа и размера
С помощью дополнительных конструкторов класса SqlParameter, а также его свойств можно дополнительно определить тип и размер параметра.
Так, в статье Выполнение команд и SqlCommand была создана таблица Users, в которой столбец Name имеет ограничение в 100 символов. И если мы попробует добавить в этот столбец строку, которая имеет больше 100 символов, то мы столнемся с исключением. В этом случае мы можем задать для параметра соответствующий размер.
Для создания объекта SqlCommand применяется один из его конструкторов:
SqlCommand(String) : создает объект SqlCommand, в конструктор которого передается выполняемое выражение SQL
SqlCommand(String, SqlConnection) : создает объект SqlCommand, в конструктор которого передается выполняемое выражение SQL и используемое подключение к базе данных в виде объекта SqlConnection
SqlCommand(String, SqlConnection, SqlTransaction) : третий параметр представляет применяемую транзакцию в виде объекта SqlTransaction
SqlCommand(String, SqlConnection, SqlTransaction, SqlCommandColumnEncryptionSetting) : к параметрам из предыдущего конструктора добавляет параметр типа SqlCommandColumnEncryptionSetting , который устанавливает настройки шифрования
Для управления командой применяются свойства класса SqlCommand, из которых следует отметить следующие:
CommandText : хранит выполняемую команду SQL
CommandTimeout : хранит временной интервал в секундах, после которого SqlCommand прекращает попытки выполнить команду и, если она не выполнена, генерирует ошибку. По умолчанию равен 30 секундам.
CommandType : хранит тип выполняемой команды
Connection : предоставляет используемое подключение SqlConnection
Для выполнения команды нам потребуется sql-выражение и объект подключения, которые мы можем задать как через конструктор класса SqlCommand, так и через его свойства:
С помощью свойства CommandText устанавливается SQL-выражение, которое будет выполняться. В данном случае это запрос на создание базы данных "adonetdb". А с помощью свойства Connection можно установить объект подключения SqlConnection.
В качестве альтернативы можно было бы использовать одну из версий конструктора класса:
Стоит отметить, что класс SqlCommand реализует интерфейс IDisposable и соответственно имеет метод Dispose. Однако вызывать его необязательно. Соответствующее обсуждение в репозитории SqlCommand.Dispose doesn't free managed object
Выполнение команды
Чтобы выполнить команду, необходимо применить один из методов SqlCommand:
ExecuteNonQuery()/ExecuteNonQueryAsync() : просто выполняет sql-выражение и возвращает количество измененных записей. Подходит для sql-выражений INSERT, UPDATE, DELETE, CREATE.
ExecuteReader()/ExecuteReaderAsync() : выполняет sql-выражение и возвращает строки из таблицы. Подходит для sql-выражения SELECT.
ExecuteScalar()/ExecuteScalarAsync() : выполняет sql-выражение и возвращает одно скалярное значение, например, число. Подходит для sql-выражения SELECT в паре с одной из встроенных функций SQL, как например, Min, Max, Sum, Count.
Создание базы данных
Для создания базы данных применяется SQL-команда CREATE DATABASE , после которой указывается имя создаваемой базы данных. Например, создадим базу данных с именем adonetdb :
И после выполнения команды в Visual Studio в окне SQL Server Object Explorer мы можем найти созданную базу данных:
Создание таблицы
Для создания базы данных применяется SQL-команда CREATE TABLE , после которой указывается имя создаваемой таблицы и в скобках определения столбцов.
Например, в выше созданной базе данных adonetdb создадим таблицу "Users", которая будет иметь три столбца - Id, Name, Age:
После выполнения команды в базе данных можно будет найти таблицу Users:
Добавление данных
Выполним команду по добавлению одного объекта в таблицу Users, которая ранее была создана:
Для вставки объекта используется sql-выражение INSERT, которое имеет следующий синтаксис:
В данном случае мы знаем, что в базе данных у нас есть таблица Users, в которой есть три столбца - Id и Age, хранящие целое число, и Name, хранящий строку. Поэтому соответственно мы добавляем для столбца Name значение 'Tom', а для столбца Age число 36.
Здесь метод ExecuteNonOueryAsync() возвращает число затронутых строк (в данном случае добавленных в таблицу объектов). Хотя нам необязательно возвращать результат метода, но данный результат может использоваться в качестве проверки, что операция, в частности, добавление, прошла успешно.
Чтобы убедиться, что данные добавлены, мы можем перейти к таблице Users в SQL Server Explorer в Visual Studio или в SQL Server Management Studio и увидеть добавленные данные:
Подобным образом можно добавить несколько объектов:
Обновление объектов
Обновление будет происходить аналогично, только теперь будет использоваться sql-выражение UPDATE, которое имеет следующий синтаксис:
Применим это выражение:
Здесь обновляется строка, в которой Name=Tom, то есть выше добавленный объект. Если в таблице будет несколько строк, у которых Name=Tom, то обновятся все эти строки.
Удаление
Удаление производится с помощью sql-выражения DELETE, которое имеет следующий синтаксис:
Удалим, например, всех пользователей, у которых имя Tom:
Во всех трех случаях фактически меняется только sql-выражение, а остальная логика остается неизменной. И мы также можем выполнять сразу несколько операций:
Метод ExecuteReader() извлекает объект чтения данных, который позволяет просматривать результаты SQL-оператора Select с помощью потока информации, доступного только для чтения в прямом направлении. Однако если требуется выполнить операторы SQL, модифицирующие таблицу данных, то нужен вызов метода ExecuteNonQuery() данного объекта команды. Этот единый метод предназначен для выполнения вставок, изменений и удалений, в зависимости от формата текста команды.
Понятие не запросный (nonquery) означает оператор SQL, который не возвращает результирующий набор. Следовательно, операторы Select представляют собой запросы, а операторы Insert, Update и Delete — нет. Соответственно, метод ExecuteNonQuery() возвращает значение int, содержащее количество строк, на которые повлияли эти операторы, а не новое множество записей.
Чтобы показать, как модифицировать содержимое существующей базы данных с помощью только запроса ExecuteNonQuery(), следующим шагом будет создание собственной библиотеки доступа к данным, в которой инкапсулируется процесс работы с базой данных AutoLot.
Добавление логики подключения
Первая наша задача — определить методы, позволяющие вызывающему процессу подключаться к источнику данных с помощью допустимой строки подключения и отключаться от него. Поскольку в нашей сборке AutoLotDAL.dll будет жестко закодировано использование типов класса System.Data.SqlClient, определите приватную переменную SqlConnection, которая будет выделяться при создании объекта InventoryDAL.
Кроме того, определите метод OpenConnection(), а затем еще CloseConnection(), которые будут взаимодействовать с этой переменной:
Для краткости тип InventoryDAL не будет проверять все возможные исключения, и не будет генерировать пользовательские исключения при возникновении различных ситуаций (например, когда строка подключения неверно сформирована). Однако при создании производственной библиотеки доступа к данным вам наверняка пришлось бы задействовать технику структурированной обработки исключений, чтобы учитывать все аномалии, которые могут возникнуть во время выполнения.
Добавление логики вставки
Вставка новой записи в таблицу Inventory сводится к форматированию SQL-оператора Insert (в зависимости от введенных пользователем данных) и вызову метода ExecuteNonQuery() с помощью объекта команды. Для этого добавьте в класс InventoryDAL общедоступный метод InsertAuto(), принимающий четыре параметра, которые соответствуют четырем столбцам таблицы Inventory (CarID, Color, Make и PetName). На основании этих аргументов сформируйте строку для добавления новой записи. И, наконец, выполните SQL-оператор с помощью объекта SqlConnection:
Создание оператора SQL с помощью конкатенации строк может оказаться опасным с точки зрения безопасности (вспомните атаки вставкой в SQL). Текст команды лучше создавать с помощью параметризованного запроса, который будет описан чуть позже.
Добавление логики удаления
Удаление существующей записи не сложнее вставки новой записи. В отличие от кода InsertAuto(), будет показана одна важная область try/catch, которая обрабатывает возможную ситуацию, когда выполняется попытка удаления автомобиля, уже заказанного кем-то из таблицы Customers. Добавьте в класс InventoryDAL следующий метод:
Добавление логики изменения
Когда дело доходит до обновления существующей записи в таблице Inventory, то сразу же возникает очевидный вопрос: что именно можно позволить изменять вызывающему процессу: цвет автомобиля, дружественное имя, модель или все сразу? Один из способов максимального повышения гибкости — определение метода, принимающего параметр типа string, который может содержать любой оператор SQL, но это, по меньшей мере, рискованно.
В идеале лучше иметь набор методов, которые позволяют вызывающему процессу изменять записи различными способами. Однако для нашей простой библиотеки доступа к данным мы определим единый метод, который позволяет вызывающему процессу изменить дружественное имя указанного автомобиля:
Добавление логики выборки
Теперь необходимо добавить метод для выборки записей. Как было показано ранее, объект чтения данных конкретного поставщика данных позволяет выбирать записи с помощью курсора, допускающего только чтение в прямом направлении. Посредством вызова метода Read() можно обработать каждую запись поочередно. Все это замечательно, но теперь необходимо разобраться, как возвратить эти записи вызывающему уровню приложения.
Одним из подходов может быть получение данных с помощью метода Read() с последующим заполнением и возвратом многомерного массива (или другого объекта вроде обобщенного List).
Класс DataTable содержит данные в виде коллекции строк и столбцов. Эти коллекции можно заполнять программным образом, но в типе DataTable имеется метод Load(), который может автоматически заполнять их с помощью объекта чтения данных! Вот пример, где данные из таблицы Inventory возвращаются в виде DataTable:
Работа с параметризованными объектами команд
Пока в логике вставки, изменения и удаления для типа InventoryDAL мы использовали жестко закодированные строковые литералы для каждого SQL-запроса. Вы, видимо, знаете о существовании параметризованных запросов, которые позволяют рассматривать параметры SQL как объекты, а не просто кусок текста.
Работа с SQL-запросами в более объектно-ориентированной манере не только помогает сократить количество опечаток (при наличии строго типизированных свойств), ведь параметризованные запросы обычно выполняются значительно быстрее запросов в виде строковых литералов, поскольку они анализируются только один раз (а не каждый раз, как это происходит, если свойству CommandText присваивается SQL-строка). Кроме того, параметризованные запросы защищают от атак внедрением в SQL (широко известная проблема безопасности доступа к данным).
Задание параметров с помощью типа DbParameter
Прежде чем приступить к созданию параметризованных запросов, ознакомимся с типом DbParameter (базовый класс для объектов параметров поставщиков). У этого класса есть ряд свойств, которые позволяют задать имя, размер и тип параметра, а также другие характеристики, например, направление просмотра параметра. Некоторые важные свойства типа DbParameter приведены ниже:
DbType
Выдает или устанавливает тип данных из параметра, представляемый в виде типа CLR
Direction
Выдает или устанавливает вид параметра: только для ввода, только для вывода, для ввода и для вывода или параметр для возврата значения
IsNullable
Выдает или устанавливает, может ли параметр принимать пустые значения
ParameterName
Выдает или устанавливает имя DbParameter
Size
Выдает или устанавливает максимальный размер данных для параметра (полезно только для текстовых данных)
Value
Выдает или устанавливает значение параметра
Для демонстрации заполнения коллекции объектов команд совместимыми с DBParameter объектами переделаем метод InsertAuto() так, что он будет использовать объекты параметров (аналогично можно переделать и все остальные методы, но нам будет достаточно и настоящего примера):
Обратите внимание, что здесь SQL-запрос также содержит четыре символа-заполнителя, перед каждым из которых находится символ @. С помощью свойства ParameterName в типе SqlParameter можно описать каждый из этих заполнителей и задать различную информацию (значение, тип данных, размер и т.д.), причем строго типизированным образом. После подготовки всех объектов параметров они добавляются в коллекцию объекта команды с помощью вызова Add().
Для оформления объектов параметров здесь используются различные свойства. Однако учтите, что объекты параметров поддерживают ряд перегруженных конструкторов, которые позволяют задавать значения различных свойств (что дает более компактную кодовую базу). Учтите также, что в Visual Studio 2010 имеются различные графические конструкторы, которые автоматически создадут за вас большой объем этого утомительного кода работы с параметрами.
Создание параметризованного запроса часто приводит к большему объему кода, но в результате получается более удобный способ для программной настройки SQL-операторов, а также более высокая производительность. Эту технику можно применять для любых SQL-запросов, хотя параметризованные запросы наиболее удобны, если нужно запускать хранимые процедуры.
Читайте также: