Pagina para probar Stored Procedures Con ASP.NET

Si estás escribiendo aplicaciones de bases de datos SQL Server (Oracle o DB2), vas a gastar mucho tiempo de la construcción y pruebas de los procedimientos almacenados, en especial los procedimientos almacenados que retornan información a través de parámetros en lugar de conjuntos de filas. No importa lo mucho que pruebe ese tipo de procedimientos almacenados usando herramientas de base de tatos , siempre cuando se conecta desde su programa, no hace lo que usted desea.

También en el desarrollo de estos procedimientos almacenados utilizando las herramientas de bases de datos, es posible que se encuentre escribiendo un montón de código T-SQL para declarar los parámetros antes de la llamada y mostrar los resultados después. En lugar de escribir todo ese código, sería útil disponer de una utilidad que puede recopilar información acerca de un procedimiento almacenado, lo que le permite especificar los valores para cada parámetro y ejecutar el procedimiento almacenado sin tener que escribir ningún código.


En este artículo, usted aprenderá cómo extraer una lista de procedimientos almacenados de una base de datos, además de información acerca de los parámetros asociados con el procedimiento almacenado. Por último, verás cómo aprovechar esta información y utilizarla para llamar a un procedimiento almacenado.

precaución. Desde que el programa de la muestra permitirá a un usuario para ejecutar un procedimiento almacenado en ejecucion, puede ser un problema de seguridad grande. Por esta razón, el programa solicita al usuario información de base de datos de autenticación y no se basa en la información guardada en un archivo Web.config o incluida en el código. Sin embargo, debe extremar la precaución al usar este programa. En realidad no debería ser usado contra una base de datos de producción a menos que se puede garantizar que sólo los usuarios autorizados pueden ejecutarlo.

Obteniendo los procedimientos almacenados

Con el fin de ejecutar un procedimiento almacenado en la marcha, es necesario recoger una lista de procedimientos almacenados. Este programa utiliza el sistema de almacenado sp_stored_procedures procedimiento para recoger una lista de procedimientos almacenados que pueden ser ejecutadas por el usuario, utilizando la información de conexión especificado por el usuario. Luego se une a la lista a un control DropDownList para que el usuario puede elegir qué procedimiento almacenado para ejecutar (vea la Figura 1).



FIGURA 1: Después de recoger la información de conexión de los controles TextBox, al pulsar el botón Iniciar sesión que va a extraer el conjunto de procedimientos almacenados que el usuario puede ejecutar.

Al hacer clic en el botón Iniciar sesión y se genera el evento LoginButton_Click (vea la Figura 2). Esta rutina comienza por la construcción de un nuevo objeto SqlConnection utilizando la información de conexión proporcionada por el usuario en el formulario web. Entonces se crea un nuevo objeto SqlCommand con el objeto SqlConnection y sp_stored_procedures referencia al procedimiento almacenado. A continuación, un new SqlDataAdapter se crea con el objeto SqlCommand.

Sub LoginButton_Click(sender As Object, e As EventArgs)
Dim ds As New DataSetDim conn As New SqlConnection(_
"Data source=" & DatabaseServer.Text & _
";User id=" & Userid.text & _
";Password=" & Password.Text & _
";Initial catalog=" & Database.Text)

Dim cmd As New SqlCommand("sp_stored_procedures", conn)
Dim adpt As New SqlDataAdapter(cmd)
Try
Status.Text = ""
adpt.Fill(ds, "SPs")
SPs.DataSource = ds.Tables("SPs")
SPs.DataTextField = "PROCEDURE_NAME"
SPs.DataBind()
Catch ex as SqlException
Status.Text = ex.Message
End Try
End Sub

FIGURA 2: El evento LoginButton_Click se dispara cuando el usuario se conecta al servidor de base de datos para llenar un DropDownList con los nombres de los procedimientos almacenados, el usuario puede ejecutar.

En el cuerpo principal de la rutina, un intento comando se usa para atrapar cualquier error de base de datos. Cualquier mensaje de error se mostrará en el cuadro de texto de estado. Dentro de la Prueba declaración, el cuadro de texto es el primer Estado aclaró, y luego la SqlDataAdapter se utiliza para rellenar un objeto DataSet con los resultados de la sp_stored_procedures procedimiento almacenado. Por último, el objeto DataTable devuelto por el adaptador de datos está Unido(bound) a el control DropDownList( SP ).


Desplegando los parámetros
Una vez que la lista de procedimientos almacenados se recupera de la base de datos, el usuario puede seleccionar un nombre de procedimiento almacenado de la lista desplegable y haga clic en el botón Get parámetros para construir una cuadrícula de datos que contienen información sobre sus parámetros (vea la Figura 3).




FIGURA 3: Después de seleccionar un procedimiento almacenado de la lista desplegable, haga clic en el botón Get parámetros para recuperar información de formato y acerca de cada parámetro asociado con el procedimiento almacenado.

En lugar de utilizar las herramientas normales de edición DataGrid, este programa utiliza un modelo de columna para insertar un cuadro de texto para el valor de entrada de cada parámetro en la cuadrícula de datos (vea la Figura 4). El resto de las columnas también se definen explícitamente como BoundColumns para simplificar el proceso de enlace de datos.


< asp:DataGrid id="ParametersDataGrid" runat="server" AutoGenerateColumns="False" >
< Columns >
< asp:BoundColumn DataField="column_name" HeaderText="Name" >
< /asp:BoundColumn >
< asp:BoundColumn DataField="type_name" HeaderText="Type" >
< /asp:BoundColumn >
< asp:BoundColumn DataField="length" HeaderText="Length" >
< /asp:BoundColumn >
< asp:BoundColumn DataField="precision" HeaderText="Precision" >
< /asp:BoundColumn >
< asp:BoundColumn DataField="scale" HeaderText="Scale" >
< /asp:BoundColumn >
< asp:BoundColumn DataField="column_type" HeaderText="Column Type" >
< /asp:BoundColumn >
< asp:TemplateColumn HeaderText="Input Value" >
< ItemTemplate >
< asp:TextBox runat="server" >
< /asp:TextBox >
< /ItemTemplate >
< /asp:TemplateColumn >
< asp:BoundColumn HeaderText="Output Value" >
< /asp:BoundColumn >
< /Columns >
< /asp:DataGrid >
< /asp:DataGrid >

FIGURA 4: El control DataGrid especifica explícitamente cada una de las columnas que se muestran en la tabla de los parámetros del procedimiento almacenado.

Utilizando el código de base que se utilizó para recuperar la lista de procedimientos almacenados de la base de datos, el evento GetParametersButton_Click recupera la lista de los parámetros asociados con el procedimiento almacenado seleccionado de la base de datos usando el procedimiento almacenado sp_sproc_columns (vea la Figura 5). La principal diferencia en esta rutina es que Parameters.Add se utiliza para definir el parámetro @ procedure_name el objeto SqlCommand que contiene el procedimiento almacenado.

Sub GetParametersButton_Click(sender As Object, e As EventArgs)
Dim ds As New DataSet
Dim conn As New SqlConnection( _
"Data source=" & DatabaseServer.Text & _
";User id=" & Userid.text & _
";Password=" & Password.Text & _
";Initial catalog=" & Database.Text)
Dim cmd As New SqlCommand("sp_sproc_columns", conn)
Dim adpt As New SqlDataAdapter(cmd)
Try
Status.Text = ""
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@procedure_name", SqlDbType.NVarchar, 390).Value = _SPs.SelectedItem.Value
adpt.Fill(ds, "Parameters")
ParametersDataGrid.DataSource = ds.Tables("Parameters")
ParametersDataGrid.DataBind()
ResultsDataGrid.Visible = False
Catch ex as SqlException
Status.Text = ex.Message
End Try
End Sub

FIGURA 5: El evento carga GetParametersButton_Click la información sobre los parámetros de un procedimiento almacenado en la ParametersDataGrid.

Una vez que el método de adaptador de datos Fill ha poblado de un objeto DataSet, el objeto resultante tabla de datos está enlazado al control ParametersDataGrid, que muestra los parámetros para el usuario. Por último, el ResultsDataGrid está desactivada en la preparación para ejecutar el procedimiento almacenado seleccionado.

El sp_sproc_columns procedimiento almacenado contiene una gran cantidad de información, además de la información que aparece en pantalla. Por ejemplo, la columna NULLABLE indica si una columna puede contener valores NULL, la columna COLUMN_DEF contiene el valor predeterminado de la columna, y el ORDINAL_POSITION contiene la posición relativa de cada parámetro en el procedimiento almacenado.

Ejecutar el procedimiento almacenado
Al hacer clic en el botón Ejecutar consulta se ejecuta el código que aparece en la Figura 6. Después de definir un objeto de conexión utilizando la información del formulario Web, un objeto SqlCommand se crea una instancia con el objeto SqlConnection y el procedimiento almacenado seleccionado de la lista desplegable en el formulario web. Un objeto SqlDataAdapter también se crea, que se utiliza para recuperar todos los datos que el procedimiento almacenado puede devolver de la base de datos.

Sub ExecuteQueryButton_Click(sender As Object, e As EventArgs)
Dim ds As New DataSet
Dim conn As New SqlConnection( _
"Data source=" & DatabaseServer.Text & _
";User id=" & Userid.text & _
";Password=" & Password.Text & _
";Initial catalog=" & Database.Text)
Dim cmd As New SqlCommand(SPs.SelectedItem.Value, conn)
Dim adpt As New SqlDataAdapter(cmd)
Try
Status.Text = ""
cmd.CommandType = CommandType.StoredProcedure
AddParameters(cmd)
adpt.Fill(ds, "Results")
UpdateParameters(cmd)
ResultsDataGrid.DataSource = ds.Tables("Results")
ResultsDataGrid.DataBind()
ResultsDataGrid.Visible = True
Catch ex as SqlException
Status.Text = ex.Message
End Try

FIGURA 6: Al hacer clic en el botón Ejecutar consulta se ejecuta el procedimiento almacenado seleccionado desde el formulario web.

Utilizando el objeto de comando de reciente creación, el CommandType se establece en StoredProcedure y luego la rutina de AddParameters está llamado a crear la colección de SqlParameters para el objeto SqlCommand. El SqlDataAdapter ejecuta la consulta y almacena los datos devueltos en un objeto DataSet. Las copias subrutina UpdateParameters los valores de los parámetros del procedimiento almacenado en el formulario web. Luego objeto DataTable que contiene los datos devueltos se une a la ResultsDataGrid en el formulario web. Si no se devuelve ningún registro, el control DataGrid no se mostrará.

Agregando los parámetros
El verdadero truco para hacer que este programa de trabajo es la rutina AddParameters muestra en la Figura 7. Esta rutina se recorre la colección de filas en el DataGrid. Una instrucción Select Case examina la información de tipo de datos contenidos en la segunda columna de la cuadrícula de datos y elige la declaración apropiada para agregar el parámetro a la colección SqlParameters.

Sub AddParameters(cmd As SqlCommand)

Dim di As DataGridItem
For Each di in Parameters
DataGrid.Items
If CType(di.Controls(5), TableCell).Text <> "5" Then
Select Case CType(di.Controls(1), TableCell).Text.ToLower

case "int"

cmd.Parameters.Add(CType(di.Controls(0), TableCell).Text, SqlDBType.Int).Value = _
CInt(CType(di.Controls(6).Controls(1), TextBox).Text)

case "char"

cmd.Parameters.Add(CType(di.Controls(0), TableCell).Text, SqlDBType.Char, CInt(CType(di.Controls(2), TableCell).Text)).Value = CType(di.Controls(6).Controls(1), TextBox).Text

case "datetime"

cmd.Parameters.Add(CType(di.Controls(0), TableCell).Text, SqlDBType.Datetime).Value = CDate(CType(di.Controls(6).Controls(1), TextBox).Text)


case "varchar"

cmd.Parameters.Add(CType(di.Controls(0), TableCell).Text, SqlDBType.VarChar, _
CInt(CType(di.Controls(2), TableCell).Text)).Value = _
CType(di.Controls(6).Controls(1), TextBox).Text

End Select
End If

If CType(di.Controls(5), TableCell).Text = "1" Then
cmd.Parameters(CType(di.Controls(0), TableCell).Text).Direction = _ParameterDirection.InputOutput

End If
Next di
End Sub

Agregar parámetros FIGURA 7: a la colección SqlParameters consiste en determinar el tipo de datos asociados.

Tenga en cuenta que el parámetro @ RETURN_VALUE se omite. Los valores que devuelve una COLUMN_TYPE de 5, que se almacena en la sexta columna del DataGrid.

Sólo dos tipos se muestran en la Figura 7 para mantener la lista corta, pero estos dos tipos de ilustrar el enfoque básico de agregar el parámetro. El nombre del parámetro se almacena siempre en la primera columna del DataGrid. La función CType arroja el valor devuelto por la colección de controles a un TableCell, y luego el valor real de la célula puede ser extraído de la propiedad Text. El valor del parámetro se puede convertir en el control TextBox al valor entero asociado con el parámetro.

El tipo de datos Varchar ilustra cómo la información adicional sobre el tipo de datos, tales como la longitud, se extrae de la información almacenada en el DataGrid. Otros tipos de datos se manejan de la misma manera.

Después de la instrucción Select Case agrega el parámetro a la colección, la sexta columna del DataGrid se revisa para ver si la propiedad de dirección se debe establecer en InputOutput para el parámetro. Un valor de 2 significa que el parámetro se puede devolver un valor desde el procedimiento almacenado, mientras que un valor de 1 significa que el parámetro es un parámetro de entrada y un valor de 5 indica que el parámetro contiene el valor de retorno para el procedimiento almacenado.

Actualización de Valores de los parámetros
La rutina UpdateParameters contiene un único caso de loop que explora a través de las filas de la cuadrícula de datos y actualiza el valor en la última columna de la cuadrícula de datos utilizando la información de los parámetros contemplados en la primera columna. Tenga en cuenta que desde el retorno no es un parámetro de valor real, su valor no se copia en el DataGrid.

Sub UpdateParameters(cmd As SqlCommand)

Dim di As DataGridItem

For each di in ParametersDataGrid.Items
If CType(di.Controls(5), TableCell).Text <> "5" Then
CType(di.Controls(7), TableCell).Text = cmd.Parameters(CType(di.Controls(0), TableCell).Text).Value.ToString

End If
Next di
End Sub

Después de introducir un valor CustomerID válido y haga clic en el botón Ejecutar consulta, los parámetros de actualización DataGrid se muestra en la Figura 8. Si el procedimiento almacenado devuelve un conjunto de filas, el ResultsDataGrid que contiene el conjunto de filas que aparecen debajo de los parámetros de DataGrid.