Colaboraciones en el Guille

Generador de Clases para Procedimientos Almacenados (I)

 

Fecha: 08/Mar/2006 (8 de marzo de 2006)
Autor: Jesús López

 



Fig. 1

Este es el primero de una serie de dos artículos. Este primer artículo trata del uso del Generador de Clases para Procedimientos Almacenados. El segundo artículo tratará de la forma en que ha sido creado el generador.

Audiencia

Este artículo va dirigido a desarrolladores de aplicaciones centradas en datos que usan:

Qué es el Generador de Clases para Procedimientos Almacenados

El Generador de Clases para Procedimientos Almacenados es una herramienta integrada con el entorno de desarrollo de Visual Studio 2005 que permite al desarrollador crear fácil y rápidamente una clase por cada procedimiento almacenado de una base de datos de SQL Server. Estas clases autogeneradas son envoltorios de objetos SqlCommand. Dichas clases facilitan considerablemente al desarrollador el uso de los procedimientos almacenados en sus aplicaciones.

El Generador de Clases para Procedimientos Almacenados consta de los siguientes componentes:

El Generador de Clases para Procedimientos Almacenados puede usarse en proyectos Visual Basic y Visual C# de Visual Studio 2005 Estándar y superior. Asimismo puede utilizarse, aunque con ciertas limitaciones, en Visual Basic 2005 Express y Visual C# 2005 Express

Razones para crear esta herramienta

Estas son los motivos por los que he decidido crear el Generador de Clases para Procedimientos Almacenados:

Usar la herramienta

Crear una clase para cada procedimiento almacenado de una base de datos de SQL Server es muy sencillo con esta herramienta. Simplemente hay que seguir los siguientes pasos:

  1. En el Explorador de Soluciones pulsar con el botón derecho del ratón en el proyecto y elegir Add-> New Item. Se muestra el cuadro de diálogo:


Fig. 2

  1. En el cuadro de diálogo Add New Item (Fig. 2), seleccionar Stored Procedures y pulsar el botón Add. Se habrá añadido el archivo StoredProcedures1.sps al proyecto y se mostrará el editor (Fig. 1). En las ediciones Express el editor no se muestra integrado en el entorno de desarrollo, sino en una ventana aparte como se muestra en Fig. 3


Fig. 3

  1. Escribir la cadena de conexión en la caja de texto Connection string.
  2. Pulsar el botón Refresh. Esto hará que se muestren en el editor los procedimientos almacenados contenidos en la base de datos.
  3. Seleccionar los procedimientos almacenados. De forma predeterminada están seleccionados todos los procedimientos almacenados. Los procedimientos almacenados pueden seleccionarse individualmente mediante las casillas de verificación Include. También pueden seleccionarse en grupos, por el esquema al que pertenecen, mediate el menú Select Schemas.
  4. Revisar la colección de parámetros. El editor genera automáticamente la colección de parámetros. Sin embargo hay ciertas propiedades que no puede establecer correctamente de forma automática. Por ejemplo, no puede distinguir entre parámetros que son de salida y los que son de entrada y salida, en estos casos el editor asume que son de entrada y salida. También tiene dificultades para establecer las propiedades SourceColumn, SourceVersion y NullMapping. El editor establece estas propiedades basándose en el nombre de los parámetros, teniendo en cuenta si empiezan por “Original_” o “IsNull_”. A veces es necesario modificar manualmente los valores de estas propiedades. Puede revisarse la colección de parámetros mediante el botón Params asociado a cada procedimiento almacenado. Al pulsar este botón se muestra la ventana de la Fig. 4.


Fig. 4

  1. Establecer el espacio de nombres al que pertenecerán las clases autogeneradas. La caja de texto Namespace y la casilla de verificación Append schema to namespace sirven para este propósito. Es importante poner las clases en su propio espacio de nombres para evitar colisiones de nombres con otras clases y entre los propios procedimientos almacenados, ya que pueden existir procedimientos almacenados con el mismo nombre siempre que pertenezcan a esquemas diferentes.
  2. Guardar la información pulsando el botón Save o cerrar el editor y responder que sí cuando se pregunte si se quieren guardar los cambios. Al guardar los cambios entra automáticamente en acción la custom tool generando un archivo de código fuente con una clase por cada procedimiento almacenado seleccionado. En las ediciones Express, la generación del código no ocurre de forma automática, siendo necesario iniciarla manualmente. Para ello se pulsa con el botón derecho del ratón sobre StoredProcedures1.sps y se elige Run custom tool.

En un proyecto podemos tener tantos items Stored Procedures como queramos, sin embargo, generalmente será mejor idea tener sólo uno con todos los procedimientos almacenados de la base de datos. También podría ser buena idea tener un ítem Stored Procedures por cada esquema de la base de datos.

Las clases autogeneradas

La herramienta personalizada (custom tool) genera una clase envoltorio de un objeto SqlCommand para cada procedimiento almacenado seleccionado. Todas las clases autogeneradas heredan de la clase base abstracta StoredProcedure la cual incluye funcionalidad común reduciendo así la cantidad de código generado. La clase StoredProcedure implementa el interfaz IDisposable y está definida en un ensamblado aparte, la custom tool añade automáticamente una referencia a este ensamblado. Todas las clases autogeneradas disponen de los siguientes miembros públicos:

 Por ejemplo, supongamos que tenemos el siguiente procedimiento almacenado:

CREATE PROCEDURE dbo.GetContactsPage
    @PageSize int,
    @PageNumber int
AS
    SELECT ContactID, FirstName, MiddleName,
           LastName, EmailAddress, Phone
    FROM (
	SELECT	ContactID, FirstName, MiddleName, 
	        LastName, EmailAddress, Phone, 
		ROW_NUMBER() OVER (ORDER BY LastName, ContactID) AS RowNumber
	FROM Person.Contact
    ) AS Contact
    WHERE RowNumber BETWEEN @PageSize * @PageNumber + 1
          AND @PageSize * (@PageNumber + 1)
						

El Generador de Clases para Procedimientos almacenados generaría la siguiente clase:


Namespace StoredProcedures.dbo
    
    Public NotInheritable Class GetContactsPage
        Inherits StoredProcedure
        
        Public Sub New()
            MyBase.New("[dbo].[GetContactsPage]")
            Me.CreateParameters
        End Sub
        
        Public Sub New(ByVal Connection As SqlConnection)
            MyBase.New("[dbo].[GetContactsPage]", Connection)
            Me.CreateParameters
        End Sub
        
        Public Sub New(ByVal Transaction As SqlTransaction)
            MyBase.New("[dbo].[GetContactsPage]", Transaction)
            Me.CreateParameters
        End Sub
        
        Public Property PageSize() As System.Nullable(Of Integer)
            Get
                Return CType(Me(1),System.Nullable(Of Integer))
            End Get
            Set
                Me(1) = value
            End Set
        End Property
        
        Public Property PageNumber() As System.Nullable(Of Integer)
            Get
                Return CType(Me(2),System.Nullable(Of Integer))
            End Get
            Set
                Me(2) = value
            End Set
        End Property
        
        Private Sub CreateParameters()
            Dim param As SqlParameter
            param = New SqlParameter("@PageSize", SqlDbType.Int)
            param.SourceColumn = "PageSize"
            Me.Parameters.Add(param)
            param = New SqlParameter("@PageNumber", SqlDbType.Int)
            param.SourceColumn = "PageNumber"
            Me.Parameters.Add(param)
        End Sub
        
        Public Overloads Function ExecuteNonQuery(
		ByVal PageSize As System.Nullable(Of Integer), 
		ByVal PageNumber As System.Nullable(Of Integer)
	) As Integer
            Return MyBase.ExecuteNonQuery(PageSize, PageNumber)
        End Function
        
        Public Overloads Function ExecuteScalar(
		ByVal PageSize As System.Nullable(Of Integer), 
		ByVal PageNumber As System.Nullable(Of Integer)
	) As Object
            Return MyBase.ExecuteScalar(PageSize, PageNumber)
        End Function
        
        Public Overloads Function ExecuteReader(
		ByVal PageSize As System.Nullable(Of Integer), 
		ByVal PageNumber As System.Nullable(Of Integer)
	) As SqlDataReader
            Return MyBase.ExecuteReader(PageSize, PageNumber)
        End Function
        
        Public Overloads Function ExecuteXmlReader(
		ByVal PageSize As System.Nullable(Of Integer), 
		ByVal PageNumber As System.Nullable(Of Integer)
	) As XmlReader
            Return MyBase. ExecuteXmlReader(PageSize, PageNumber)
        End Function
        
        Public Overloads Sub SetParameters(
		ByVal PageSize As System.Nullable(Of Integer), 
		ByVal PageNumber As System.Nullable(Of Integer)
	)            
	    MyBase.SetParameters(PageSize, PageNumber)
        End Sub
        
        Public Overloads Sub LoadTable(
		ByVal Table As DataTable, 
		ByVal PageSize As System.Nullable(Of Integer), 
		ByVal PageNumber As System.Nullable(Of Integer)
	)
            MyBase.LoadTable(Table, PageSize, PageNumber)
        End Sub
    End Class
End Namespace  
    

Podríamos utilizar el siguiente fragmento de código para cargar una determinada página de contactos en un DataTable:

Imports System.Data
Imports System.Data.SqlClient
Imports VBStoredProceduresTest.StoredProcedures.dbo

......

Dim Connection As New SqlConnection( _
    Data Source=(local);Integrated Security=SSPI;Initial Catalog=AdventureWorks")
Dim GetContactsPage As New GetContactsPage(Connection)
Dim ContactsTable As New DataTable("Contacts")
GetContactsPage.LoadTable(ContactsTable, 100, 10)    
    

NOTA: VBStoredProceduresTest es el espacio de nombres raíz del proyecto, de forma predeterminada coincide con el nombre del proyecto.

Mantenimiento de las clases autogeneradas

El conjunto de procedimientos almacenados de una base de datos, puede variar durante la fase de desarrollo y durante la vida de la aplicación. El Generador de Clases para Procedimientos Almacenados dispone de un mecanismo muy sencillo para mantener sincronizados los procedimientos de la base de datos con las clases autogeneradas. Para sincronizarlos, se abre el item StoredProcedures1.sps y se pulsa el botón Refresh. El editor ayuda al desarrollador a localizar rápidamente los cambios que se han producido de la siguiente manera:

Uso de las clases autogeneradas

Las clases autogeneradas deberían utilizarse para crear la capa de acceso a datos. No es recomendable usar directamente estas clases dentro de la capa de presentación.

Dentro de la capa de acceso a datos se puede usar las clases autogeneradas para:

Por ejemplo, suponiendo que tenemos los procedimientos almacenados GetShippers, AddShipper, UpdateShipper y DeleteShipper para la tabla Shippers de la base de datos Northwind. Podríamos usar el siguiente fragmento de código para configurar un SqlDataAdapter que use estos procedimientos almacenados:

Imports System.Data
Imports System.Data.SqlClient
Imports VBStoredProceduresTest.StoredProcedures.dbo

......

Dim Connection As New SqlConnection( _
    Data Source=(local);Integrated Security=SSPI;Initial Catalog=Northwind")
Dim GetShippers As New GetShippers(Connection)
Dim AddShipper As New AddShipper(Connection)
Dim DeleteShipper As New DeleteShipper(Connection)
Dim UpdateShipper As New UpdateShipper(Connection)
Dim Adapter As New SqlDataAdapter(GetShippers.Command)
Adapter.UpdateCommand = UpdateShipper.Command
Adapter.DeleteCommand = DeleteShipper.Command
Adapter.InsertCommand = AddShipper.Command

 

Notas sobre el archivo de descarga

El archivo de descarga contiene el programa de instalación. Al descomprimir el archivo (según como se extraiga el contenido del zip) se crea la carpeta sqlranger_StoredProceduresClassGenerator.setup que contiene a su vez la subcarpeta Instalar en la que está el programa de instalación. Para instalar esta herramienta hay que ejecutar el programa Setup.exe que se encarga de comprobar si ya hay una versión anterior instalada, y de ser así, se encarga de desinstalarla.


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

System.Data
System.Data.SqlClient


Fichero con el programa de instalación: sqlranger_StoredProceduresClassGenerator.setup.zip - 157 KB

(MD5 checksum: E822FD26B770126F115E7AD46B5FC0B3)


ir al índice principal del Guille