uso de procedimientos almacenados

ÍNDICE

Descripción 1

Lección: descripción de los procedimientos almacenados 2

Lección: invocar procedimientos almacenados 9

Invocar procedimientos almacenados con Microsoft ADO.NET

Invocar procedimientos almacenados con Microsoft ADO.NET 1

Descripción

Descripción de los procedimientos almacenados

Invocar procedimientos almacenados

*****************************

Acceder y manipular datos directamente en una base de datos desde un formulario Web Form puede constituir un uso muy ineficaz de los recursos, y puede crear riesgos de seguridad. Una forma de mejorar la eficacia y seguridad del acceso a las bases de datos es crear procedimientos almacenados en el servidor de la base de datos, e invocar estos procedimientos almacenados desde nuestro formulario Web Form. Acceder a una base de datos a través de un procedimiento almacenado limita el código del formulario Web Form y el ancho de banda de la red que debemos utilizar cuando realizamos tareas complicadas. Acceder a una base de datos mediante un procedimiento almacenado también protege la base de datos al limitar el acceso directo a la base de datos a procedimientos almacenados locales y de confianza.

En este módulo, estudiaremos cómo realizar las tareas de acceso a datos desde las aplicaciones Web utilizando Microsoft® ADO.NET para acceder a procedimientos almacenados.

En este módulo, aprenderemos a:

Explicar qué es un procedimiento almacenado y los motivos para utilizarlos en el acceso a una base de datos.

Invocar procedimientos almacenados.

Introducción

Objetivos

2 Invocar procedimientos almacenados con Microsoft ADO.NET

Lección: descripción de los procedimientos almacenados

¿Qué es un procedimiento almacenado?

¿Por qué utilizar procedimientos almacenados?

Práctica: seleccionar el procedimiento almacenado correcto

*****************************

Una alternativa al acceso directo a bases de datos desde nuestra aplicación Web es invocar un procedimiento almacenado que accederá a la base de datos por nosotros. El uso de procedimientos almacenados tiene varias ventajas respecto al acceso directo a bases de datos, incluyendo la eficacia, seguridad y la protección de la base de datos.

En esta lección, aprenderemos qué es un procedimiento almacenado y los motivos para invocar procedimientos almacenados cuando se accede a una base de datos.

En esta lección, aprenderemos a:

Describir qué es un procedimiento almacenado. Explicar los motivos para utilizar procedimientos almacenados para acceder

y manipular bases de datos.

Introducción

Objetivos de la lección


¿Qué es un procedimiento almacenado?

Procedimientos comunes de datos que pueden ser invocados por múltiples aplicaciones Web

Acceso programático a una base de datos

Devolver registros

Devolver un valor

Realizar acción

ClienteCliente

SQL ServerSQL Server

FormularioWeb Form

Procedimientoalmacenado

Procedimientoalmacenado

Servidor WebServidor Web

Base datos

*****************************

Un procedimiento almacenado es un procedimiento escrito por un desarrollador de aplicaciones para bases de datos y que está asociado a una base de datos específica. A continuación, otras aplicaciones Web pueden invocar estos procedimientos almacenados para acceder y manipular datos de la base de datos.

Los procedimientos almacenados permiten el acceso a una base de datos mediante la invocación de un procedimiento existente en lugar de tener que escribir nuestras propias instrucciones SQL. Los procedimientos almacenados están formados por secuencias de instrucciones Transact-SQL, y funcionan de modo similar a los procedimientos de una aplicación Web en cuanto a que las instrucciones se invocan por su nombre, y pueden tener tanto parámetros de entrada como de salida.

Los tres tipos de procedimientos almacenados son:

Procedimientos almacenados que devuelven registros Los procedimientos almacenados que devuelven registros se utilizan para encontrar registros específicos, clasificar y filtrar esos registros, y devolver el resultado de las operaciones de búsqueda, clasificación y filtrado a un objeto DataSet o un control enlazado a lista. Estos procedimientos almacenados se basan en instrucciones Select de SQL. Un ejemplo de un procedimiento almacenado que devuelve registros es una petición de la cantidad, fecha y receptor de los tres últimos movimientos procesados en una cuenta bancaria. Estos datos podrían cargarse en un objeto DataSet para su posterior procesamiento, o mostrarse directamente al usuario en un control ListBox.

Introducción

Acceso programático a una base de datos


Procedimientos almacenados que retornan un valor, también denominados

procedimientos almacenados escalares Los procedimientos almacenados que retornan un valor se utilizan para ejecutar un comando o función de la base de datos que devuelve un único valor. Debido a que sólo se devuelve un valor, los procedimientos almacenados que retornan un valor a menudo se utilizan en el código y, a continuación, el resultado se muestra a los usuarios. Un ejemplo de procedimiento almacenado que retorna un valor es devolver el valor total de los tres últimos movimientos que se han procesado en una cuenta bancaria.

Los procedimientos almacenados de acción Los procedimientos almacenados de acción se utilizan para realizar algunas funciones en la base de datos, pero no devuelven un registro o un valor. Estas funciones de la base de datos pueden incluir actualizar, editar o modificar los datos. Un ejemplo de un procedimiento almacenado de acción es una petición para actualizar una sola dirección de correo en la base de datos de clientes de una compañía.


¿Por qué utilizar procedimientos almacenados?

Programación modular

Distribución del trabajo

Seguridad de la base de datos

Ejecución más rápida

Reduce el tráfico de red

Proporciona flexibilidad

*****************************

Es más fácil, más eficaz y más seguro utilizar un procedimiento almacenado que escribir el código requerido para conectarnos directamente a una base de datos y ejecutar nuestras propias instrucciones Transact-SQL. Invocar un procedimiento almacenado no requiere que entendamos cómo está diseñada la base de datos, y sólo se accede a la base de datos mediante un procedimiento ya probado.

Los procedimientos almacenados son ejemplos clásicos de programación modular. Creamos el procedimiento una vez, lo probamos una vez, lo almacenamos en el servidor de la base de datos, y lo invocamos varias veces desde múltiples aplicaciones. Las actualizaciones o cambios a la base de datos permanecen ocultas para todas las aplicaciones que acceden mediante el procedimiento almacenado.

Un desarrollador especialista en la programación de bases de datos puede crear los procedimientos almacenados, mientras que otros desarrolladores pueden crear paralelamente las aplicaciones Web que utilizarán el procedimiento almacenado. Esta distribución del trabajo permite que cada desarrollador se concentre en su propia especialidad, y cumpla con sus propios plazos.

El uso de procedimientos almacenados proporciona una mayor seguridad para una base de datos al limitar el acceso directo. Únicamente acceden directamente a la base de datos los procedimientos almacenados probados que han sido desarrollados por el propietario de la base de datos. Debido a que las demás aplicaciones Web y otros desarrolladores no acceden directamente a la base de datos, el riesgo de daño accidental de la estructura o el contenido de la base de datos es mínimo.

Utilizar instrucciones SQL o Transact-SQL directamente en el código Microsoft ASP.NET también supone un riesgo para la seguridad ya que las instrucciones pueden dar información a un hacker sobre la base de datos y su estructura. Además, con el acceso directo a una base de datos, también tenemos el problema de seguridad al intentar determinar qué clase de permisos deberíamos dar a la cuenta de usuario Web sobre las tablas individuales.

Introducción

Programación modular

Distribución del trabajo

Incremento de la seguridad de la base de datos


Si un procedimiento requiere una gran cantidad de código Transact-SQL o si se invoca repetidamente, el uso de procedimientos almacenados puede dar lugar a una mayor rapidez de ejecución que acceder directamente a la base de datos con código Transact-SQL. Los procedimientos almacenados son parseados y optimizados cuando se crean, y puede utilizarse una versión en memoria del procedimiento tras su primera ejecución.

El acceso directo mediante instrucciones Transact-SQL requiere que las instrucciones sean enviadas desde el cliente cada vez que se ejecutan. Las instrucciones son compiladas y optimizadas cada vez que son ejecutadas por el servidor de la base de datos.

En ocasiones, una operación que requiere cientos de líneas de código Transact-SQL puede realizarse mediante una única instrucción que invoque un procedimiento almacenado. Enviar una llamada a través de la red, en lugar de cientos de líneas de código, reduce el tráfico de red.

Si el acceso a la base de datos se realiza a través de procedimientos almacenados, el desarrollador de la base de datos puede cambiar la estructura de la base de datos sin romper las aplicaciones Web que los utilizan. Esta protección permite la continua mejora de la base de datos sin poner en riesgo el resto del sistema.

Ejecución más rápida

Reducción del tráfico de red

Proporciona flexibilidad


Práctica: seleccionar el procedimiento almacenado correcto

Los estudiantes:

Decidirán, en determinados escenarios, qué tipo de procedimiento almacenado debe utilizarse

Tiempo: 5 Minutos

*****************************

Leer los siguientes escenarios y decidir qué tipo de procedimientos almacenados debería utilizarse:

Devolver registros Devolver valor Realizar acción

Debemos determinar los 10 mejores best sellers de la compañía. ____________________________________________________________

____________________________________________________________

Debemos determinar el beneficio neto de un determinado trimestre. ____________________________________________________________

____________________________________________________________

Debemos cambiar todos los prefijos telefónicos 425 de Estados Unidos por el prefijo 415. ____________________________________________________________

____________________________________________________________


Debemos determinar qué registros tienen el prefijo telefónico 425 de Estados Unidos. _____________________________________________________________

_____________________________________________________________

Debemos eliminar todos los elementos que se han dejado de fabricar de una determinada categoría. _____________________________________________________________

_____________________________________________________________


Lección: invocar procedimientos almacenados


Demostración: invocar un procedimiento almacenado

Práctica: mostrar datos de un procedimiento almacenado

Utilizar parámetros

Pasar parámetros de entrada

Utilizar parámetros de salida

Demostración: pasar parámetros

*****************************

Antes de poder invocar un procedimiento almacenado, debemos identificar el nombre del procedimiento y los parámetros que utiliza. Una vez identificado el procedimiento almacenado, podemos invocarlo, pasarle los parámetros de entrada que se requieran para procesar nuestra petición y gestionar los parámetros de salida incluidos en la respuesta.

En esta lección, aprenderemos cómo invocar procedimientos almacenados, pasar parámetros de entrada y gestionar parámetros de salida.

En esta lección, aprenderemos a:

Invocar un procedimiento almacenado desde un formulario Web Form. Identificar el tipo de parámetros disponibles cuando se invocan los

procedimientos almacenados. Pasar parámetros de entrada cuando se invocan procedimientos

almacenados desde un formulario Web Form. Utilizar los parámetros de salida de un procedimiento almacenado.

Introducción

Objetivos de la lección



Identificar el procedimiento almacenadoConfigurar la propiedad SelectCommand del DataAdapter

Ejecutar el procedimiento almacenado y almacenar los registros devueltos

Dim daCategory As New SqlDataAdapter()daCategory.SelectCommand = New SqlCommand()daCategory.SelectCommand.Connection = conndaCategory.SelectCommand.CommandText = "ProductCategoryList"daCategory.SelectCommand.CommandType = CommandType.StoredProcedure

Dim daCategory As New SqlDataAdapter()daCategory.SelectCommand = New SqlCommand()daCategory.SelectCommand.Connection = conndaCategory.SelectCommand.CommandText = "ProductCategoryList"daCategory.SelectCommand.CommandType = CommandType.StoredProcedure

daCategory.Fill(ds, "Categories")daCategory.Fill(ds, "Categories")

SqlDataAdapter daCategory = new SqlDataAdapter();daCategory.SelectCommand = new SqlCommand();daCategory.SelectCommand.Connection = conn;daCategory.SelectCommand.CommandText = "ProductCategoryList";daCategory.SelectCommand.CommandType = CommandType.StoredProcedure;

SqlDataAdapter daCategory = new SqlDataAdapter();daCategory.SelectCommand = new SqlCommand();daCategory.SelectCommand.Connection = conn;daCategory.SelectCommand.CommandText = "ProductCategoryList";daCategory.SelectCommand.CommandType = CommandType.StoredProcedure;

daCategory.Fill(ds, "Categories");daCategory.Fill(ds, "Categories");

*****************************

Para invocar un procedimiento almacenado, en primer lugar debemos identificarlo, crear un objeto DataAdapter, y configurar el objeto DataAdapter para que apunte a la conexión de la base de datos. Seguidamente, establecer la propiedad CommandText al nombre del procedimiento almacenado identificado y, finalmente, establecer la propiedad CommandType en CommandType.StoredProcedure.

El primer paso para utilizar un procedimiento almacenado es identificar el tipo y el nombre del mismo. Podemos utilizar un objeto DataAdapter o un objeto DataReader para invocar los tres tipos de procedimientos almacenados. El método que invoquemos para ejecutar el procedimiento almacenado variará dependiendo del tipo de procedimiento almacenado que invoquemos:

Procedimientos almacenados que devuelven registros Cuando invocamos un procedimiento que devuelve un conjunto de registros, necesitamos almacenar ese conjunto de registros en un DataSet, o directamente en un control enlazado a lista utilizando un DataReader. Si deseamos utilizar un DataSet, debemos utilizar un DataAdapter y el método Fill. Si deseamos utilizar un DataReader, debemos utilizar un objeto Command y el método ExecuteReader, y vincular el registro devuelto al control enlazado a lista.

Procedimientos almacenados que retornan un valor Cuando invocamos un procedimiento almacenado que devuelve un valor, invocamos el método ExecuteScalar del objeto Command, y guardamos el resultado en una variable del tipo de datos apropiado.

Procedimientos almacenados que realizan una acción Cuando invocamos un procedimiento almacenado que realiza alguna acción en la base de datos pero no devuelve un conjunto de registros o un valor, utilizamos el método ExecuteNonQuery del objeto Command.

Introducción

Identificar el procedimiento almacenado


Para configurar la llamada al procedimiento almacenado, debemos crear un objeto SqlCommand y guardarlo como la propiedad SelectCommand de un DataAdapter. A continuación, debemos establecer las propiedades Connection, CommandText y CommandType.

Podemos configurar visualmente la llamada a un procedimiento almacenado utilizando las herramientas para procedimientos almacenados del Cuadro de herramientas de Microsoft Visual Studio® .NET, o podemos escribir manualmente el código para invocar el procedimiento almacenado. Los siguientes ejemplos utilizan código manual para mostrar tanto el código completo como el proceso lógico de invocación de un procedimiento almacenado.

Los siguientes ejemplos invocan el procedimiento almacenado ProductCategoryList. El procedimiento almacenado ProductCategoryList devuelve una lista de IDs y nombres de categorías de la tabla Categories:

Procedure ProductCategoryList As SELECT CategoryID,CategoryName FROM Categories El siguiente código utiliza un objeto Connection y un objeto DataAdapter para invocar el procedimiento almacenado que devuelve registros ProductCategoryList:

Dim daCategory as New SqlDataAdapter() daCategory.SelectCommand = New SqlCommand() daCategory.SelectCommand.Connection = conn daCategory.SelectCommand.CommandText = _ "ProductCategoryList" daCategory.SelectCommand.CommandType = _ CommandType.StoredProcedure SqlDataAdapter daCategory = new SqlDataAdapter(); daCategory.SelectCommand = new SqlCommand(); daCategory.SelectCommand.Connection = conn; daCategory.SelectCommand.CommandText = "ProductCategoryList"; daCategory.SelectCommand.CommandType = CommandType.StoredProcedure;

También podemos establecer directamente la conexión y el texto del comando cuando creamos el objeto SqlDataAdapter. El siguiente código realiza la misma tarea que el código anterior: Dim daCategory As New SqlDataAdapter _ ("ProductCategoryList", conn) daCategory.SelectCommand.CommandType = _ CommandType.StoredProcedure SqlDataAdapter daCategory = new SqlDataAdapter ("ProductCategoryList", conn); daCategory.SelectCommand.CommandType = CommandType.StoredProcedure;

Establecer la propiedad SelectCommand

Visual Basic .NET

C#

Nota

Visual Basic .NET

C#


Para ejecutar el procedimiento almacenado y guardar los registros devueltos en un DataSet, invocar el método Fill del objeto SqlDataAdapter. Este método rellena un objeto DataTable con los registros devueltos del procedimiento almacenado.

Por ejemplo, el siguiente código rellena el objeto DataSet ds con los registros devueltos desde el procedimiento almacenado ProductCategoryList utilizando el daCategory SqlDataAdapter:

daCategory.Fill(ds, "Categories") daCategory.Fill(ds, "Categories"); Tras rellenar un DataTable con los resultados de un procedimiento almacenado Select, podemos vincular el DataTable a un control enlazado a lista para mostrar los datos.

Ejecutar el procedimiento almacenado

Visual Basic .NET

C#


Demostración: invocar un procedimiento almacenado

*****************************

En esta demostración, veremos cómo invocar un procedimiento almacenado de una base de datos Microsoft SQL Server™, y vincular los resultados a un DataGrid.

Ejecutar la demostración

1. Abrir la página SPGetRecords.aspx del proyecto Demo09VB o Demo09CS que se pueden encontrar dentro del fichero demos9.zip.

2. Generar y examinar la página. El DataGrid está vinculado al procedimiento almacenado Ten Most Expensive Products de la base de datos Northwind Traders.

3. En Visual Studio .NET, visualizar la página de código subyacente de la página SPGetRecords.aspx.

4. En el procedimiento de evento Page_Load, mostrar el código que realiza lo siguiente:

• Crea el objeto SqlConnection.

• Crea los objetos SqlDataAdapter y SqlCommand.

• Establece las propiedades del objeto SqlCommand para invocar el procedimiento almacenado.

• Crea un nuevo DataSet y lo rellena desde el DataAdapter.

Introducción


Práctica: mostrar datos de un procedimiento almacenado

Los estudiantes:

Arrastrarán un procedimiento almacenado desde el Explorador de servidores a un formulario Web Form ASP.NET

Crearán un objeto DataReader para leer los datos del procedimiento almacenado

Vincularán un DataGrid al DataReader

Tiempo: 15 minutos

*****************************

En esta práctica, crearemos un objeto DataReader para invocar un procedimiento almacenado en una base de datos SQL Server, y vincularemos los resultados a un DataGrid.

Ejecutar la práctica

1. Crear un nuevo proyecto de aplicación Web y establecer su ubicación en http://localhost/Pract09VB http://localhost/Pract09CS

2. Abrir la página WebForm1.aspx. 3. Abrir el Explorador de servidores y expandir las siguientes carpetas:

nombre_máquina, Servidores SQL Server, nombre_máquina, Northwind y Procedimientos almacenados.

4. Hacer clic en el procedimiento almacenado Ten Most Expensive Products y arrastrarlo a la página WebForm1.aspx. Visual Studio .NET crea automáticamente un objeto SqlConnection denominado sqlConnection1 y un objeto SqlCommand denominado sqlCommand1.

5. Utilizando el Cuadro de herramientas, ubicar un control DataGrid en la página WebForm1.aspx.

Introducción

Visual Basic .NET C#


6. Crear un procedimiento de evento Page_Load y agregar el siguiente código

para crear un DataReader desde el objeto SqlCommand y vincularlo al DataGrid: Dim dr As SqlClient.SqlDataReader SqlConnection1.Open() dr = SqlCommand1.ExecuteReader() DataGrid1.DataSource = dr DataGrid1.DataBind() dr.Close() SqlConnection1.Close() SqlDataReader dr; sqlConnection1.Open(); dr = sqlCommand1.ExecuteReader(); DataGrid1.DataSource = dr; DataGrid1.DataBind(); dr.Close(); sqlConnection1.Close();

7. Si se está utilizando C# para generar este proyecto, debemos introducir el siguiente código al inicio de la página de código subyacente. using System.Data.SqlClient;

8. Generar y examinar la página WebForm1.aspx.

Visual Basic .NET

C#

C#


Utilizar parámetros

Identificar los parámetros disponibles

Input

Output

InputOutput

ReturnValue

Incluir parámetros en la colección de parámetros

o

Incluir valores de parámetros en la cadena de comando

*****************************

Cuando utilizamos procedimientos almacenados en una base de datos SQL Server o en cualquier otra base de datos basada en procedimientos, se pueden utilizar parámetros para pasar información y recuperar datos del procedimiento almacenado.

Cuando utilizamos parámetros con una base de datos SQL Server, los nombres de los parámetros que se agregan a la colección Parameters del objeto Command deben coincidir con los nombres de los parámetros del procedimiento almacenado; no obstante, el orden de los parámetros es flexible.

Cuando utilizamos parámetros en una base de datos OLE DB, el orden de los parámetros en la colección Parámetros debe coincidir con el orden de los parámetros definidos en el procedimiento almacenado.

La siguiente tabla describe los tipos de parámetros disponibles en los procedimientos almacenados.

Parámetro Uso Input Utilizado por la aplicación Web para enviar valores de datos

específicos a un procedimiento almacenado.

Output Utilizado por un procedimiento almacenado para enviar valores específicos de retorno a la aplicación Web que lo invoca.

InputOutput Utilizado por un procedimiento almacenado tanto para recuperar información enviada por nuestra aplicación Web como para enviar valores de retorno específicos a la aplicación Web.

ReturnValue Utilizado por un procedimiento almacenado para enviar un valor de retorno a la aplicación Web que lo invoca.

Introducción

Nota


Pasar parámetros de entrada

Crear el parámetro, establecer la dirección y el valor, agregar a la colección de parámetros

Ejecutar el procedimiento almacenado y almacenar los registros devueltos

SqlParameter param = new SqlParameter("@Beginning_Date", SqlDbType.DateTime);

param.Direction = ParameterDirection.Input;param.Value = Convert.ToDateTime

(txtStartDate.Text);da.SelectCommand.parámetros.Add(param);

SqlParameter param = new SqlParameter("@Beginning_Date", SqlDbType.DateTime);

param.Direction = ParameterDirection.Input;param.Value = Convert.ToDateTime

(txtStartDate.Text);da.SelectCommand.parámetros.Add(param);

ds = New DataSet();da.Fill(ds, "Products");

ds = New DataSet();da.Fill(ds, "Products");

Códigos de ejemplo

ds = New DataSet()da.Fill(ds, "Products")

ds = New DataSet()da.Fill(ds, "Products")

param = New SqlParameter _("@Beginning_Date", SQLDbType.DateTime)

param.Direction = ParameterDirection.Inputparam.Value = CDate(txtStartDate.Text)da.SelectCommand.parámetros.Add(param)

param = New SqlParameter _("@Beginning_Date", SQLDbType.DateTime)

param.Direction = ParameterDirection.Inputparam.Value = CDate(txtStartDate.Text)da.SelectCommand.parámetros.Add(param)

*****************************

Una vez identificados los parámetros soportados por un procedimiento almacenado, debemos agregar los parámetros que utilizaremos a la colección Parameters del objeto Command.

Para crear un parámetro, crear un nuevo objeto SqlParameter con el nombre y tipo de datos del parámetro, según lo especificado por el procedimiento almacenado. A continuación, establecer la propiedad Direction del nuevo parámetro para indicar la forma en que el procedimiento almacenado utilizará el parámetro. Si el procedimiento almacenado devuelve un valor de retorno, crear un parámetro denominado returnValue. Si el parámetro es de entrada, establecer la propiedad Value para especificar los datos que deberían enviarse al SQL Server.

Por ejemplo, el procedimiento almacenado ProductsByCategory toma un parámetro de entrada, denominado @CategoryID de tipo int, como muestra el siguiente código:

Procedure ProductsByCategory ( @CategoryID int ) As SELECT ProductID, ModelName, UnitCost, ProductImage, Chairman FROM Products WHERE CategoryID=@CategoryID

Introducción

Crear un parámetro


Para invocar el procedimiento almacenado ProductsByCategory, crear un parámetro de entrada denominado @CategoryID y establecer su valor con el valor de un cuadro de texto:

Dim workParam1 As New SqlParameter _ ("@CategoryID", SqlDbType.Int) workParam1.Direction = ParameterDirection.Input workParam1.Value = Cint(txtStartDate.Text) SqlParameter workParam1 = new SqlParameter ("@CategoryID", SqlDbType.Int); workParam1.Direction = ParameterDirection.Input; workParam1.Value = Convert.ToInt16(txtStartDate.Text);

Deberíamos validar siempre el contenido de un cuadro de texto antes de enviar la entrada del usuario al procedimiento almacenado. Para una mayor simplicidad, el código anterior no lo hace.

Una vez creado el objeto Parameter, utilizar el método Add de la colección Parameters del objeto SelectCommand. Si un procedimiento almacenado tiene más de un parámetro, no importa en qué orden los agreguemos puesto que los creamos por nombre:

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) SqlDataAdapter daSales = new SqlDataAdapter(); daSales.SelectCommand = new SqlCommand(); daSales.SelectCommand.Connection = conn; daSales.SelectCommand.CommandText = "ProductsByCategory"; daSales.SelectCommand.CommandType = CommandType.StoredProcedure; daSales.SelectCommand.Parameters.Add(workParam1); Una vez creado el objeto Command, utilizamos el método Fill para ejecutar el procedimiento almacenado y recuperar los registros:

ds = New DataSet() daSales.Fill(ds, "Products") ds = new DataSet(); daSales.Fill(ds, "Products");

Visual Basic .NET

C#

Nota

Visual Basic .NET

C#

Ejecutar un procedimiento almacenado

Visual Basic .NET

C#


Utilizar parámetros de salida

Crear el parámetro, establecer dirección y añadir a la colección Parameters

Ejecutar el procedimiento almacenado y almacenar losregistros devueltos

Leer los parámetros de salida

param = New SqlParameter("@ItemCount", SQLDbType.Int)param.Direction = ParameterDirection.Outputda.SelectCommand.parameters.Add(param)

param = New SqlParameter("@ItemCount", SQLDbType.Int)param.Direction = ParameterDirection.Outputda.SelectCommand.parameters.Add(param)

ds = new DataSet()da.Fill(ds)

ds = new DataSet()da.Fill(ds)

iTotal = da.parameters("@ItemCount").ValueiTotal = da.parameters("@ItemCount").Value

param = new SqlParameter("@ItemCount", SqlDbType.Int);param.Direction = ParameterDirection.Output;da.SelectCommand.parameters.Add(param);

param = new SqlParameter("@ItemCount", SqlDbType.Int);param.Direction = ParameterDirection.Output;da.SelectCommand.parameters.Add(param);

ds = new DataSet();da.Fill(ds);

ds = new DataSet();da.Fill(ds);

iTotal = da.parameters("@ItemCount").Value;iTotal = da.parameters("@ItemCount").Value;

*****************************

Para leer el valor de un parámetro de salida o un valor de retorno de un procedimiento almacenado que devuelve registros, debemos acceder al valor del parámetro de salida en la colección Parámetros después de que el procedimiento almacenado se haya ejecutado.

El procedimiento almacenado OrdersCount toma un ID de cliente y devuelve el número de pedidos pendientes que éste tiene, pero no los pedidos actuales. El procedimiento almacenado utiliza los parámetros de entrada @CustomerID, y el parámetro de salida @ItemCount, ambos del tipo int, como muestra el siguiente procedimiento almacenado:

Procedure OrdersCount ( @CustomerID int, @ItemCount int OUTPUT ) As SELECT @ItemCount=COUNT(OrderID) FROM Orders WHERE CustomerID=@CustomerID Como el procedimiento almacenado anterior devuelve el número de filas y no los datos de dichas filas, no necesitamos utilizar un objeto DataAdapter. En lugar de ello, podemos utilizar un objeto Command directamente, e invocar el método ExecuteNonQuery para ejecutar el procedimiento almacenado.

Para invocar el procedimiento almacenado OrdersCount, necesitamos crear un parámetro de entrada denominado @CustomerID y un parámetro de salida denominado @ItemCount y añadirlos a la colección Parameters de un objeto Command, y a continuación invocar a ExecuteNonQuery para ejecutar el procedimiento almacenado:

Introducción

Ejemplo de uso de parámetros de salida

Invocar un procedimiento almacenado que devuelve un valor


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) SqlCommand myCmd = new SqlCommand("OrdersCount", conn); myCmd.CommandType = CommandType.StoredProcedure; // add an input parameter SqlParameter workParam; workParam = new SqlParameter("@CustomerID", SqlDbType.Int); workParam.Direction = ParameterDirection.Input; workParam.Value = Convert.ToInt16(txtCustID.Text); myCmd.Parameters.Add(workParam); // add an output parameter workParam = new SqlParameter("@ItemCount", SqlDbType.Int); workParam.Direction = ParameterDirection.Output; myCmd.Parameters.Add(workParam); El siguiente código ejecuta el procedimiento almacenado MyCmd:

conn.Open() myCmd.ExecuteNonQuery() conn.Close() conn.Open(); myCmd.ExecuteNonQuery(); conn.Close(); Si estamos recuperando un valor de un procedimiento almacenado que devuelve valores o establece un parámetro de salida, necesitamos utilizar el método Value del parámetro devuelto en la colección Parameters. Podemos referenciar el valor del parámetro de salida por el nombre o por el índice. El siguiente ejemplo de código recupera el valor del parámetro de salida @ItemCount por el nombre:

curSales = myCmd.Parameters("@ItemCount").Value curSales = myCmd.Parameters["@ItemCount"].Value;

Visual Basic .NET

C#

Ejecutar el procedimiento almacenado

Visual Basic .NET

C#

Leer parámetros de salida

Visual Basic .NET

C#


Demostración: pasar parámetros

*****************************

En esta demostración, veremos cómo invocar un procedimiento almacenado con dos parámetros de entrada y a continuación enlazar los resultados a un DataGrid.

Para ejecutar la demostración

1. Abrir la página SPUseParameters.aspx del proyecto Demo09VB o Demo09CS que se pueden encontrar dentro del fichero demos9.zip.

2. Generar y examinar. Introducir las fechas de inicio y final en Beginning Date y Ending Date y a continuación hacer clic en Get Sales by Year. Se enlaza un DataGrid al procedimiento almacenado Sales by Year en la base de datos Northwind Traders.

3. En Visual Studio .NET, visualizar el código subyacente de la página SPUseParameters.aspx.

4. En el procedimiento de evento cmdSale_Click, mostrar el código que realiza lo siguiente:

• Crea la SqlConnection.

• Crea el SqlDataAdapter y establece las propiedades del SelectCommand para invocar el procedimiento almacenado Sales by Year.

• Crear dos parámetros de entrada.

• Crear un nuevo DataSet y rellenar el DataSet desde el DataAdapter.

Introducción

uso de procedimientos almacenados

Documents