Generador de Clases para Procedimientos Almacenados (I)

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:

• Visual Basic o C# como lenguaje de desarrollo.
  
• SQL Server 2000 ó 2005 como sistema de gestión de bases de datos.
  
• Procedimientos almacenados como medio para acceder y manipular los datos de una base de datos.

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:

• Un Item Template que permite añadir al proyecto elementos del tipo Stored Procedures. Estos elementos son archivos con extensión sps que contienen la información necesaria para generar una clase envoltorio por cada procedimiento almacenado. En realidad los archivos sps contienen la serialización binaria de un DataSet bien definido (strong typed).
  
• Un editor estándar basado en archivos e integrado con el entorno de desarrollo para editar elementos del tipo Stored Procedures.
  
• Un editor externo de archivos sps para las ediciones Express.
  
• Una herramienta personalizada (custom tool) que genera las clases a partir de la información contenida en los archivos sps.

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:

• En Visual Studio 2003 podíamos arrastrar un procedimiento almacenado desde el Explorador de Servidores y soltarlo en la superficie de diseño de un componente. Esto facilitaba la creación de componentes de la capa de acceso a datos. Sin embargo, en Visual Studio 2005 ha desaparecido esta funcionalidad, con lo que para usar directamente procedimientos almacenados ahora hay que configurar manualmente los objetos SqlCommand, lo que resulta una tarea tediosa y propensa a errores.
  
• Los TableAdapters de Visual Studio 2005 son una herramienta muy útil para crear objetos de acceso a datos con una funcionalidad básica de forma rápida y fácil. Sin embargo son difíciles y a veces imposible de personalizar de manera que funcionen como se necesita. Además, los TableAdapters a veces realizan acciones de forma automática y sin pedir confirmación al desarrollador que pueden hacerle perder parte del trabajo realizado, lo que resulta odioso y muy frustrante. Por último los TableAdapters ocultan ciertas características de los comandos y DataAdapters que a veces son esenciales. Después de perder mucho tiempo intentando hacer funcionar los TableAdapters como yo necesitaba, y después de muchas frustraciones he llegado a la conclusión de que no me sirven.
  
• En definitiva, necesitaba una herramienta que me facilitara la creación de la capa de acceso a datos y que me librara de la propensa a errores y tediosa tarea de la configuración y uso de los objetos SqlCommand para llamar a 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

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

3. Escribir la cadena de conexión en la caja de texto Connection string.
   
4. Pulsar el botón Refresh. Esto hará que se muestren en el editor los procedimientos almacenados contenidos en la base de datos.
   
5. 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.
   
6. 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

7. 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.
   
8. 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:

• Tres constructores que inicializan las propiedades CommandText y CommandType y generan la colección de parámetros. Estos constructores son:
  • El constructor predeterminado.
  • Un constructor con una conexión como parámetro.
  • Un constructor con una transacción como parámetro.
    
• Propiedades heredadas de StoredProcedure:
  • Command. (Sólo lectura) El objeto SqlCommand subyacente.
  • Connection. La conexión asociada.
  • Transaction. La transacción asociada.
    
• Una propiedad por cada parámetro del procedimiento almacenado. Estas propiedades tienen el mismo nombre que el parámetro y son del tipo correspondiente al tipo de parámetro. Todas estas propiedades son de lectura y escritura excepto las correspondientes a los parámetros de salida y al parámetro de retorno que son de sólo lectura.
  
• Varios métodos para ejecutar el procedimiento almacenado. Cada uno de estos métodos tiene un argumento por cada parámetro del procedimiento almacenado. Estos métodos abren la conexión si no está abierta y la dejan en el mismo estado en que se encontraba, excepto los métodos ExecuteReader y ExecuteXmlReader que no cierran la conexión después de ejecutarse. Estos métodos son:
  • ExecuteNonQuery
  • ExecuteScalar
  • ExecuteReader
  • ExecuteXmlReader
  • LoadTable

 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:

• Resalta los procedimientos que han cambiado, poniendo los nuevos, los eliminados y que han cambiado su colección de parámetros en un color diferente.
  
• Al pasar el ratón por encima de estos procedimientos, se muestra un mensaje que describe la situación del procedimiento.
  
• Pone los procedimientos nuevos, eliminados y modificados al principio de la lista.
  
• Para los procedimientos cuya colección de parámetros ha cambiado, puede verse qué parámetros y en qué forma han cambiado pulsando el botón params.
  
• Los procedimientos permanecerán resaltados hasta que se guarde el item StoredProcedures1.sps.

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:

• Ejecutar directamente procedimientos almacenados.
  
• Configurar SqlDataAdapters.

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)