Хранимые процедуры

56

Хранимая процедура (stored procedure) — это именованный блок SQL-кода, хранимый в базе данных. Хранимые процедуры можно создавать для возврата набора строк или скалярных типов данных, а также выполнения других нужных действий (например, вставки, обновления или удаления); они могут принимать любое количество необязательных параметров. В результате получается рабочая единица, которая ведет себя как типичная функция, только она находится в хранилище данных, а не в двоичном бизнес-объекте.

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

На данном этапе в базе данных AutoLot определена одна хранимая процедура с именем GetPetName, имеющая следующий формат:

ALTER PROCEDURE GetPetName
@carID int,
@petName char(10) output
AS
	SELECT @petName = PetName from Inventory 
	where CarID = @carID

Давайте добавим вызов хранимой процедуры в сборку AutoLotDAL.dll, которая рассматривалась в предыдущей статье:

public string LookUpPetName(int carId)
        {
            string carPetName = string.Empty;

            // Задание имени хранимой процедуры
            using (SqlCommand cmd = new SqlCommand("GetPetName", this.connect))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                // Входной параметр.
                SqlParameter param = new SqlParameter();
                param.ParameterName = "@carID";
                param.SqlDbType = SqlDbType.Int;
                param.Value = carId;

                //По умолчанию параметры считаются входными, но все же для ясности:
                param.Direction = ParameterDirection.Input;
                cmd.Parameters.Add(param);

                // Выходной параметр.
                param = new SqlParameter();
                param.ParameterName = "@petName";
                param.SqlDbType = SqlDbType.Char;
                param.Size = 10;
                param.Direction = ParameterDirection.Output;
                cmd.Parameters.Add(param);

                // Выполнение хранимой процедуры.
                cmd.ExecuteNonQuery();
                // Возврат выходного параметра.
                carPetName = ((string)cmd.Parameters["@petName"].Value).Trim();
            }
            return carPetName;
        }

Один важный аспект, касающийся вызова хранимых процедур: вспомните, что объект команды может представлять оператор SQL (по умолчанию) или имя хранимой процедуры. Если необходимо сообщить объекту команды, что он должен вызывать хранимую процедуру, то нужно передать имя этой процедуры (через аргумент конструктора или с помощью свойства CommandText) и установить в свойстве CommandType значение CommandType.StoredProcedure (иначе вы получите исключение времени выполнения, т.к. по умолчанию объект команды ожидает оператор SQL).

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

После завершения работы хранимой процедуры с помощью вызова ExecuteNonQuery() значение выходного параметра можно получить с помощью просмотра коллекции параметров для данного объекта команды и соответствующего приведения типа:

carPetName = ((string)cmd.Parameters["@petName"].Value).Trim();

И вот первый вариант библиотеки доступа к данным AutoLotDAL.dll готов! С помощью этой сборки можно создавать произвольные интерфейсы для вывода и редактирования данных (консольные приложения, Windows-приложения, приложения на основе Windows Presentation Foundation или веб-приложения на базе HTML).

Пройди тесты
Лучший чат для C# программистов