9.- uso de procedimientos almacenados

Invocar procedimientos almacenados con Microsoft ADO.NET

ÍNDICE Descripción

1

Lección: descripción de los procedimientos almacenados

2

Lección: invocar procedimientos almacenados

9

Invocar procedim ientos almacenados con Microsoft ADO.NET

1

Descripción 

Descripción de los pr ocedimientos almacenados



Invocar proc edimientos almacenados

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

Introducción

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.

Objetivos

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.

2


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

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

Introducción

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.

Objetivos de la lección

En esta lección, aprenderemos a:  

Describir qué es un procedimiento almacenado. Explicar los motivos para utilizar procedimientos almacenados para ac ceder y manipular bases de datos.


3

¿Qué es un procedimiento almacenado? 



Procedimientos co munes de datos que pu eden ser invocados por múltiples aplicaciones Web Acceso programático a una base de datos 

Devolver registros



Devolver un valor



Realizar acción Formulario Web Form

Cliente Cliente

Procedimiento Procedimiento almacenado almacenado Base datos

Servidor ServidorWeb Web

SQL SQLServer Server

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

Introducción

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.

Acceso programático a una 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.

4






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.


5

¿Por qué utilizar procedimi entos almacenados? 

Programación modular



Distribución del t rabajo



Seguri dad de la base de datos



Ejecución más rápida



Reduce el tráfico de red



Proporciona flexibilidad

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

Introducción

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.

Programación modular

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.

Distribución del trabajo

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.

Incremento de l a seguridad de la base de datos

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 e n 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.

6


Ejecución más rápida

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.

Reducción del tráfico de red

En ocasiones, una operación que requiere cientos de líneas de código TransactSQL 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.

Proporciona flexibilidad

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.


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. ____________________________________________________________ ____________________________________________________________

7

8


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 

Invocar proc edimientos almacenados



Demostración: invocar un procedimiento almacenado



Práctica: most rar datos de un procedimiento almacenado



Utilizar parámetros



Pasar parámetros de entrada



Utilizar parámetros de salida



Demostración: pasar parámetros

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

Introducción

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.

Objetivos de la lección

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.

9

10


Invocar procedimientos almacenados 

Identificar el procedimiento almacenado



Configurar la propiedad SelectCommand del DataAdapter

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

Ejecutar el procedimiento almacenado y almacenar los registros devueltos daCategory.Fill(ds, daCategory.Fill(ds, "Categories") "Categories") daCategory.Fill(ds, daCategory.Fill(ds, "Categories"); "Categories");

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

Introducción

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 .

Identificar el procedimiento almacenado

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.

Invocar procedimi entos almacenados con Microsoft ADO.NET

Establecer la propiedad SelectCommand

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 almac enado 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 : Visual Basi c .NET

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

C#

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

Nota 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: Visual Basi c .NET

Dim daCategory As New SqlDataAdapter _ ("ProductCategoryList", conn) daCategory.SelectCommand.CommandType = _ CommandType.StoredProcedure

C#

SqlDataAdapter daCategory = new SqlDataAdapter ("ProductCategoryList", conn); daCategory.SelectCommand.CommandType = CommandType.StoredProcedure;

11

12


Ejecutar el procedimiento almacenado

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 :

Visual Basi c .NET

daCategory.Fill(ds, "Categories")

C#

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.


13

Demostración: invocar un procedimiento almacenado

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

Introducción

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 .

14


Práctica: mostrar datos de un pr ocedimiento 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

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

Introducción

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

Visual Basi c .NET C#

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.


15

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 : Visual Basi c .NET

Dim dr As SqlClient.SqlDataReader SqlConnection1.Open() dr = SqlCommand1.ExecuteReader() DataGrid1.DataSource = dr DataGrid1.DataBind() dr.Close() SqlConnection1.Close()

C#

SqlDataReader dr; sqlConnection1.Open(); dr = sqlCommand1.ExecuteReader(); DataGrid1.DataSource = dr; DataGrid1.DataBind(); dr.Close(); sqlConnection1.Close();

C#

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.

16


Utilizar parámetros 



Identificar los parámetros d isponibles 

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

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

Introducción

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. Nota 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. Utilizado por un procedimiento almacenado para enviar valores específicos de retorno a la aplicación Web que lo invoca.

Output 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.


17

Pasar parámetros de entrada 

Crear el parámetro, establecer la dirección y el valor, agregar a la colección de parámetros SqlParameter param SqlParameter SqlParameter param == new new _ SqlParameter param == New SqlParameter param New SqlParameter _ ("@Beginning_Date", SqlDbType.DateTime); ("@Beginning_Date", SqlDbType.DateTime); ("@Beginning_Date", SQLDbType.DateTime) ("@Beginning_Date", SQLDbType.DateTime) param.Direction == ParameterDirection.Input; param.Direction ParameterDirection.Input; param.Direction = ParameterDirection.Input param.Direction = ParameterDirection.Input param.Value param.Value == Convert.ToDateTime Convert.ToDateTime param.Value param.Value == CDate(txtStartDate.Text) CDate(txtStartDate.Text) (txtStartDate.Text); (txtStartDate.Text); da.SelectCommand.parámetros.Add(param) da.SelectCommand.parámetros.Add(param) da.SelectCommand.parámetros.Add(param); da.SelectCommand.parámetros.Add(param);



Ejecutar el procedimiento almacenado y almacenar los registros devueltos ds DataSet() ds == New New DataSet(); DataSet(); DataSet() da.Fill(ds, "Products") da.Fill(ds, "Products"); "Products"); "Products")

Códigosde ejemplo *****************************

Introducción

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 .

Crear un parámetro

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

18


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: Visual Basi c .NET

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

C#

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

Nota 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: Visual Basi c .NET

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)

C#

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);

Ejecutar un procedimiento almacenado

Una vez creado el objeto Command, utilizamos el método Fill para ejecutar el procedimiento almacenado y recuperar los registros:

Visual Basi c .NET

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

C#

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


19

Utilizar parámetros de salida 

Crear el parámetro, establecer dirección y añadir a la colección Parameters param param == New New SqlParameter("@ItemCount", SqlParameter("@ItemCount", SQLDbType.Int) SQLDbType.Int) param.Direction param.Direction == ParameterDirection.Output ParameterDirection.Output da.SelectCommand.parameters.Add(param) da.SelectCommand.parameters.Add(param) param param == new new SqlParameter("@ItemCount", SqlParameter("@ItemCount", SqlDbType.Int); SqlDbType.Int); param.Direction param.Direction == ParameterDirection.Output; ParameterDirection.Output; da.SelectCommand.parameters.Add(param); da.SelectCommand.parameters.Add(param);



Ejecutar el procedimiento almacenado y almacenar los registros devueltos ds ds == new new DataSet() DataSet() da.Fill(ds) da.Fill(ds)

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

Leer los parámetros de salida

 iTotal

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

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

Introducción

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.

Ejemplo de uso de parámetros d e salida

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. Invocar un procedimiento almacenado q ue devuelve un valor

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:

20


Visual Basi c .NET

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)

C#

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);

Ejecutar el procedimiento almacenado

El siguiente código ejecuta el procedimiento almacenado MyCmd:

Visual Basi c .NET

conn.Open() myCmd.ExecuteNonQuery() conn.Close()

C#

conn.Open(); myCmd.ExecuteNonQuery(); conn.Close();

Leer parámetros de salida

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:

Visual Basi c .NET

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

C#

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


21

Demostración: pasar parámetros

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

Introducción

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 .

9.- uso de procedimientos almacenados

Recommend Documents