Usando Store Procedures en aplicaciones ASP .Net

Sres. Desarrolladores, empezando este año 2006 quiero compartir con ustedes algunos ejemplo de como usar Store Procedures en Aplicaciones Web ASP .Net.

Algo de Teoría
Un Store Procedure es un  procedimiento de base de datos el cual se utiliza en una determinada base de datos. El Store Procedure o Procedimiento Almacenado puede ser accedido por cualquier aplicación con el fin de manipular los datos de esta.

El Store Procedure tiene escrito sus propias sentencias SQL llamadas sentencias Transact-SQL, son similares a las funciones o procedimientos en las aplicaciones, son llamados por su nombre y pueden tener parámetros tanto de ingreso de datos, de salida o ambos.

Tipos de Procedimientos Almacenados
Existen tres tipos de Store Procedures:

1. Store Procedure que retorna registros

Este tipo de Store Procedures son utilizados para buscar registros específicos, ordenar o filtrar estos y reportan su resultado el cual se almacena en un DataSet o en un control de lista, son basados en sentencias SQL.

Ejemplo: Un Store Procedure que presente los clientes o pasajeros que viajaron en el último mes que paso, y guardarlos en un DataSet o en un ListBox.

2. Store Procedure que retorna un Valor.

Este tipo de Store Procedures son utilizados para ejecutar un comando  en una base de datos o una función que retorna un valor único. El resultado de estos tipos de Store Procedure se asignan generalmente a una variable. 

Ejemplo: Mostrar el total de Items vendidos o atendidos en una orden.

3. Store Procedure que realiza una acción

Este tipo de Store Procedures son utilizados para realizar una acción determinada dentro de una base de datos, no retornan registros ni valores. Las sentencias utilizadas en estos tipos de Store Procedures pueden incluir actualizaciones, ediciones, modificaciones o eliminaciones de registros.

Ejemplo: Modificar el correo electrónico de un cliente o su dirección.

Porque usar Store Procedures

Existen varios motivos por los cuales un desarrollador puede decidir usar Store Procedures, una de las mas resaltantes es que resulta mas eficiente, mas seguro y no requiere de conocimiento de como está diseñada la base de datos, en la siguiente tabla se pueden resumir los principales beneficios del uso de Store Procedures:

Beneficio	Descripción
Programación Modular	Un Store Procedure es un clásico ejemplo de programación modular. Este se crea solo, se testea solo y se puede llamar muchas veces desde diversas aplicaciones.
Distribución	Los Store Procedure pueden crearse independientemente por un especialista en base de datos, mientras que pueden ser utilizados por los desarrolladores de aplicaciones Web o por otros desarrolladores.
Seguridad	Usando Store Procedures se aumentan significativamente la seguridad en una base de datos. Unicamente los testeadores y los dueños de la base de datos pueden modificarlos.
Ejecución rápida	El Store Procedure tiene mejor performance que realizar las sentencias de manejo de datos directamente desde la aplicación
Reduce el tráfico de red	Es posible que si una operación que requiera de muchas sentencias se pueda ejecutar estas directamente en el Store Procedure, en nuestra aplicación posiblemente realicemos esto con una línea, la que ejecuta o invoca al SP.
Flexibilidad	El desarrollador de la base de datos puede cambiar la estructura de esta sin afectar a las aplicaciones.

 

Crear Store Procedures
Consideraciones previas: Puede crear procedimientos almacenados mediante la instrucción CREATE PROCEDURE de Transact-SQL. Pero antes de crear uno, tenga en cuenta lo siguiente:

• Las instrucciones CREATE PROCEDURE no se pueden combinar con otras instrucciones SQL en el mismo lote.
• De forma predeterminada, el permiso para crear procedimientos almacenados corresponde al propietario de la base de datos, que puede transferirlo a otros usuarios.
• Los procedimientos almacenados son objetos de base de datos y sus nombres deben ajustarse a las reglas para los identificadores.
• Sólo puede crear un procedimiento almacenado en la base de datos actual.
• Cuando cree un procedimiento almacenado, deberá especificar lo siguiente:
• Todos los parámetros de entrada y de salida del lote o del procedimiento que realiza la llamada.
• Las instrucciones de programación que realicen operaciones en la base de datos, incluidas las llamadas a otros procedimientos.
• El valor de estado devuelto al lote o al procedimiento que realiza la llamada, a fin de indicar que la operación se ha realizado correctamente o que se ha producido un error (y el motivo del mismo).

Desde el Administrador Corporativo de SQL Server
Las dos imágenes muestran la forma:

Botón derecho en la base de datos, seleccionar Nuevo y luego Procedimiento Almacenado

Seleccionar Procedimientos Almacenados de la base de datos donde se creará, pulsar botón derecho y seleccionar Nuevo Procedimiento Almacenado, este proceso lo muestra la siguiente figura:

Luego de cualquiera de los dos procedimientos se presenta:

En esta ventana escribimos el Store Procedure.

Tips:

• El botón Comprobar la Sintaxis no indica que el Store Procedure funcione bien, unicamente que lo que hemos escrito sintácticamente este correcto.
• Use el Analizados de consultas con las instrucciones que quiera realizar, luego agregue estas en la edición del procedimiento, obviamente con los cambios necesarios, es decir, cambiar los datos utilizados como ejemplo por los respectivos parámetros.
• Usando la ventana Explorador de Servidores (Server Explorer) también se pueden crear, editar,  eliminar o ejecutar Store Procedures. el usuario que se utilice debe tener acceso requerido para esta opción.

 

EL EJEMPLO

Diseñe un WebForm con el siguiente aspecto: Dos DropDownList, dos DataGrids, dos Buttons y dos etiquetas (Label) para mostrar el total de Items para una orden y el total de órdenes de un Cliente.

En este se notan dos partes, la primera: la extracción de los detalles de una orden y el cálculo del total de Items (suma de Quantity) para la orden y la segunda: mostrar las órdenes de un determinado cliente y la suma de las órdenes generadas.

Para la primera parte se crearon y ejecutaron los siguiente Store Procedures:

CustOrdersDetail.- para extraer el detalle de la orden

ALTER PROCEDURE CustOrdersDetail 
(
     @OrderID int
)
AS

SELECT ProductName, UnitPrice=ROUND(Od.UnitPrice, 2),
Quantity, Discount=CONVERT(int, Discount * 100),
ExtendedPrice=ROUND(CONVERT(money, Quantity * (1 - Discount) * Od.UnitPrice), 2)
FROM Products P, [Order Details] Od
WHERE Od.ProductID = P.ProductID and Od.OrderID = @OrderID

TotalItemsOrden.- para calcular el total de Items para la orden mostrada

ALTER PROCEDURE TotalItemsOrden
(
@OrderId nchar(5),
@TotalItems int OUTPUT
)
AS 
Select @TotalItems=Sum(Quantity) from [Order Details] where orderId = @OrderId
RETURN

Nota:
Los dos Store Procedures previos están presentes en la Base de datos Northwind (si no lo están deben crearse).
El SP CustOrdersDetail tiene un parámetro de entrada llamado @OrderID y es de tipo de los que retornan  registros, el SP TotalItemsOrden tiene dos parámetros llamados @OrderId y @TotalItems este de tipo OutPut y el SP es de tipo que retorna un valor.

 

El código implementado el el botón Mostrar es el siguiente

Private Sub cmdMostrar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdMostrar.Click

          'Definir un SQLCommand, El nombre del Store Procedure en CommandText
        'El CommandType = StoreProcedure y la conexion
        Dim coDetalle As New SqlCommand
        coDetalle.CommandText = "CustOrdersDetail"
        coDetalle.CommandType = CommandType.StoredProcedure
        coDetalle.Connection = cn  'Previamente definida

        'El Adaptador y su SelectCommand
        Dim daDetalle As New SqlDataAdapter
        daDetalle.SelectCommand = coDetalle

        'Parámetros si hubieran
        Dim miParam As New SqlParameter("@OrderId", SqlDbType.Int)
        miParam.Direction = ParameterDirection.Input
        coDetalle.Parameters.Add(miParam)
        coDetalle.Parameters("@OrderId").Value = DropDownList1.SelectedItem.ToString  

        'Llenar el DataSet
        'Al llenar el DataSet se ejecuta el Store Procedure
        'el SQLCommand del SQLDataAdapter se especificó
        'del tipo StoreProcedure
        daDetalle.Fill(dsDatos, "Detalle")
        'Origen del DataGrid
        DataGrid1.DataSource = dsDatos.Tables("Detalle")
        DataGrid1.DataBind()  

        'Para el total de Items
        'Se ejecuta el Store Procedure TotalItemsOrden
        Dim coTotal As New SqlCommand("TotalItemsOrden", cn)
        coTotal.CommandType = CommandType.StoredProcedure

        'Dos parámetros: El de Ingreso que es @OrderID
        'El de salida es @TotalItems
        Dim ParamOrderId As New SqlParameter("@OrderId", SqlDbType.NChar, 5)
        ParamOrderId.Direction = ParameterDirection.Input
        coTotal.Parameters.Add(ParamOrderId)
        coTotal.Parameters("@OrderId").Value = DropDownList1.SelectedItem.ToString  

        'El parámetro de Salida
        Dim ParamTotal As New SqlParameter("@TotalItems", SqlDbType.Int)
        ParamTotal.Direction = ParameterDirection.Output
        coTotal.Parameters.Add(ParamTotal)

        cn.Open()
        'Ejecutar el Store Procedure
        coTotal.ExecuteScalar()
        cn.Close()
        lblTotalItems.Text = coTotal.Parameters("@TotalItems").Value.ToString
End Sub

---

El código completo se encuentra en el archivo ZIP al final del artículo.

Recuerden votar antes de bajar el código fuente en el archivo ZIP al final del artículo, eso me motiva a seguir compartiendo mis conocimientos.

Artículos relacionados:
Uso de SQLDataReader con Store Procedures:
http://www.elguille.info/colabora/NET2005/FernandoLuque_Usando_SQLDataReader.htm

Suerte y A SEGUIR DESARROLLANDO

Ing. Fernando Luque Sánchez
De Trujillo - Perú para todo el mundo

---

Espacios de nombres usados en el código de este artículo:

System.Data
System.Data:SQLClient

---

Fichero con el código de ejemplo: FernandoLuque_SP_en_ASP.zip - (20) KB
(MD5 checksum: [6AC4C2B101B142BC77B7F0877DD334D1]

Nota sobre el checksum MD5:
La utilidad MD5.exe para comprobar la integridad de los ficheros la puedes bajar de este sitio:
MD5- Command Line Message Digest Utility (http://www.fourmilab.ch/md5/)

La versión para Windows (con el código fuente): md5.zip

Para usar la utilidad MD5, simplemente escribe desde la línea de comandos:
MD5 <nombre del fichero> y te mostrará el número de "checksum" que debe coincidir con el mostrado, si no coincide... ¡NO TE FÍES DE ESE FICHERO! ya que puede estar mal o "manipulado".
Si se te da el caso de que el checksum mostrado no coincide con el que te muestre esa utilidad, por favor avísame indicando el nombre del fichero, el checksum que has obtenido y de dónde has bajado el fichero.
Gracias.