11. Работа из ASP.NET с хранимыми процедурами на источнике данных

Хранимые процедуры в приложениях ASP.NET 2.0, объект Command и коллекция Parameters в ADO.NET

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

Поэтому в реальном приложении без хранимых процедур не обойтись.

Тому, как их использовать в ADO.NET/ASP.NET, и посвящен этот модуль.

Хранимые процедуры можно разбить на три типа:

- возвращающие данные в табличном представлении (похожи на параметризованные запросы)

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

- выполняющие какие-либо действия (каскадное обновление)

Все три типа хр. проц. можно использовать в ASP.NET.

Чем хороши хранимые процедуры:

- обеспечивают модульность программирования

- в них легко можно реализовать дополнительные возможности обеспечения безопасности

- повышение производительности за счет более удобной работы с кэшем, где хранится план выполнения

- сокращение сетевого трафика - достаточно передать только имя хранимой процедуры и параметры;

- защита от изменений в структуре БД

Теперь - о том, как вызывать хранимую процедуру из приложения ASP.NET.

На графическом экране достаточно из списка SQL серверов выбрать нужный сервер и перетащить из-под его контейнера хранимую процедуру на форму. На ней автоматически будут созданы объекты Connection и Command.

Дальше, например, можно воспользоваться методом ExecuteReader объекта Command, чтобы получить стандартный объект DataReader на основе возвращаемых данных, или создавать DataSet через DataAdapter.

Dim dr As SqlClient.SqlDataReader

SqlConnection1.Open()

dr = SqlCommand1.ExecuteReader()

DataGrid1.DataSource = dr

DataGrid1.DataBind()

dr.Close()

SqlConnection1.Close()

Чаще всего нам нужно передавать хранимой процедуре значения параметров и принимать возвращаемые значения. Для этого используется коллекция Parameters объекта Command. Для каждого объекта Parameter предусмотрено значение свойства (.Value) и направления (.Direction).

При этом при работе с SQL Server важны только имена объектов SqlParameter (порядок не важен), а при работе с источниками данных OLE DB важен порядок.

Предположим, у нас есть хранимая процедура следующего вида:

Procedure ProductsByCategory (

   @CategoryID int )

As

  SELECT ProductID, ModelName, UnitCost, ProductImage,

   Chairman

 FROM Products

 WHERE CategoryID=@CategoryID

Передать ей значение его единственного параметра можно так, как показано ниже. Вначале создаем объект SqlParameter и настраиваем его свойства:

Dim workParam1 As New SqlParameter _

 ("@CategoryID", SqlDbType.Int)

workParam1.Direction = ParameterDirection.Input

workParam1.Value = Cint(txtStartDate.Text)

Затем добавляем его в коллекцию Parameters объекта SelectCommand:

Dim daSales as New SqlDataAdapter()

daSales.SelectCommand = New SqlCommand()

daSales.SelectCommand.Connection = conn

daSales.SelectCommand.CommandText = "ProductsByCategory"

daSales.SelectCommand.CommandType = _

 CommandType.StoredProcedure

daSales.SelectCommand.Parameters.Add(workParam1)

Затем запускаем хранимую процедуру на выполнение и заполняем тем, что она вернула, объект DataSet:

ds = New DataSet()

daSales.Fill(ds, "Products")

В примере как у нас важно обязательно проверить значение в текстовом  поле, прежде чем передавать его объекту Command.

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

Если же возвращаются нетабличные значения, то можно просто получить то, что вернет метод ExecuteNonQuery объекта кода. Предположим, что у нас есть процедура, которая возвращает скалярное значение:

Procedure OrdersCount (

   @CustomerID int,

    @ItemCount int OUTPUT )

As

 SELECT @ItemCount=COUNT(OrderID)

 FROM Orders

 WHERE CustomerID=@CustomerID

Получить нужное значение можно так:

Dim myCmd As SqlCommand = New SqlCommand("OrdersCount", conn)

myCmd.CommandType = CommandType.StoredProcedure

'add an input parameter

Dim workParam as SqlParameter

workParam = New SqlParameter("@CustomerID", SqlDbType.Int)

workParam.Direction = ParameterDirection.Input

workParam.Value = CInt(txtCustID.Text)

myCmd.Parameters.Add (workParam)

'add an output parameter

workParam = New SqlParameter("@ItemCount", SqlDbType.Int)

workParam.Direction = ParameterDirection.Output

myCmd.Parameters.Add (workParam)

conn.Open()

myCmd.ExecuteNonQuery()

conn.Close()

curSales = myCmd.Parameters("@ItemCount").Value

Получить учебные материалы по этому курсу