Como invocar Procedimientos Almacenados en nuestra clase conexión.

Descargar ejemplo ConexionDB_02.zip

En esta ocasión vamos a continuar la clase conexión desarrollada unos artículos atrás, y ahora vamos a agregarle la opción de poder invocar procedimientos almacenados con múltiples parámetros de distintos tipos de datos, de una forma muy sencilla y basados en los estándares altamente recomendados.

El primer ejemplo de esta Clase Conexión solo permite ejecutar querys simples.

Ahora la gran diferencia es que vamos a agregar dos clases extras, clase StoredProcedure y StoredProcedureParameter.

La clase StoredProcedure tiene dos variables, mNombreProcedimiento como un String y mParametros como una colección de datos, con base a estas variables privadas tenemos sus propias Propiedades Públicas llamadas Nombre y Parámetros.

Public Class StoredProcedure

#Region " Variables "
Private mNombreProcedimiento As String
Private mParametros As Collection
#End Region

#Region " Propiedades "
Public Property Nombre() As String
Get
Return mNombreProcedimiento
End Get
Set(ByVal Value As String)
mNombreProcedimiento = Value
End Set
End Property

Public Property Parametros() As Collection
Get
Return mParametros
End Get
Set(ByVal Value As Collection)
mParametros = Value
End Set
End Property
#End Region

.
.
.

Un punto importante de esta clase es su constructor, la cual inicializa las variables simples y la colección para los datos, y sin la cual no podríamos invocar los siguientes procedimientos y funciones.

#Region " Constructor "

'Solo recibe el nombre del procedimiento e inicializa la colección.
Public Sub New(ByVal nNombre As String)
Try
Nombre = nNombre
Parametros = New Collection
Catch ex As Exception
Throw ex
End Try
End Sub

#End Region

Ahora para poder utilizar esta clase tenemos el procedimiento AgregarParametro que recibe el nombre de la Variable y el Valor de esta variable. Algo con lo que hay que tener cuidado es que la variable se debe llamar igual a como esta declarada en el procedimiento y no hay que enviarle el @ ya que se agrega automáticamente en el procedimiento.

'Agrega los parametros del procedimiento y su respectivo valor.
Public Sub AgregarParametro(ByVal pVariable As String, ByVal pValor As Object)
Try
Dim iParametro As New StoredProcedureParameter("@" & pVariable, pValor)
Me.Parametros.Add(iParametro)
Catch ex As Exception
Throw ex
End Try
End Sub

Como podemos ver, al nombre de la variable se le agrega el @ necesario para que sea interpretado por el procedimiento almacenado, y el valor el cual es de un tipo Objeto, más adelante podremos ver como lo lee el sistema. Preferiblemente es que se le envíen las variables en el mismo orden en que se reciben en el Procedimiento Almacenado.

Ahora para ejecutar el procedimiento con sus respectiva variables debemos invocar la función EjecutarProcedimiento que nos retorna un dataset.

'Ejecuta el procedimiento almacenado.
Public Function EjecutarProcedimiento() As DataSet
Try
Dim Conn As New Conexion
Dim sqlCmd As New SqlCommand(Me.Nombre, Conn.SQLConn)
sqlCmd.CommandType = CommandType.StoredProcedure

Dim mParametro As StoredProcedureParameter

'Agrega las variables al procedimiento almacenado
For Each mParametro In Me.Parametros
Dim pParam As New SqlParameter(mParametro.Variable, mParametro.GetTypeProperty)
pParam.Direction = ParameterDirection.Input
pParam.Value = mParametro.Valor
sqlCmd.Parameters.Add(pParam)
Next

'SqlAdapter utiliza el SqlCommand para llenar el Dataset
Dim sda As New SqlDataAdapter(sqlCmd)

'Se llena el dataset
Dim ds As New DataSet
sda.Fill(ds)

Conn.SQLConn.Close()
Return ds
Catch ex As Exception
Throw ex
End Try
End Function

En esta función declaramos una variable de tipo StoredProcedureParameter, la cual veremos más adelante.

En esta función EjecutarProcedimiento declaramos una variable SqlCommand y le indicamos que es de tipo StoredProcedure, ( sqlCmd.CommandType = CommandType.StoredProcedure ), con esto le indicamos al SqlDataAdapter la forma en que se ejecuta la consulta.

La última clase de este ejemplo, StoredProcedureParameter es la que administra las posibles variables del procedimiento, igualmente el procedimiento podría no recibir variables y la colección estar limpia.

Public Class StoredProcedureParameter

Private mVariable As String
Private mValor As Object

'Nombre de la variable, debe ser igual a la declarada en el procedimiento almacenado
Public Property Variable() As String
Get
Return mVariable
End Get
Set(ByVal Value As String)
mVariable = Value
End Set
End Property

'Valor de la variable, puede ser de cualquier tipo de dato. preferible que 
'coincida con las variables declaradas en GetTypeProperty
Public Property Valor()
Get
Return mValor
End Get
Set(ByVal Value)
mValor = Value
End Set
End Property

'Se definen los posibles tipos de datos que se le van a enviar al procedimiento almacenado
'Esta lista podria aumentar conforme se usen otro tipo de variable.
Public ReadOnly Property GetTypeProperty() As SqlDbType
Get
If mValor.GetType.FullName = "System.String" Then
Return SqlDbType.VarChar
ElseIf mValor.GetType.FullName = "System.Int16" Then
Return SqlDbType.Int
ElseIf mValor.GetType.FullName = "System.Int32" Then
Return SqlDbType.Int
ElseIf mValor.GetType.FullName = "System.Int64" Then
Return SqlDbType.Int
ElseIf mValor.GetType.FullName = "System.Decimal" Then
Return SqlDbType.Decimal
ElseIf mValor.GetType.FullName = "System.Double" Then
Return SqlDbType.BigInt
ElseIf mValor.GetType.FullName = "System.DateTime" Then
Return SqlDbType.DateTime
ElseIf mValor.GetType.FullName = "System.Byte" Then
Return SqlDbType.Image
End If
End Get
End Property

'Procedimiento de creacion de la variable.
Public Sub New(ByVal pVariable As String, ByVal pValor As Object)
Try
Me.Variable = pVariable
Me.Valor = pValor
Catch ex As Exception
Throw New Exception("Error en la creacion del Parametro" & vbCrLf & ex.Message)
End Try
End Sub

End Class

El constructor recibe las dos variables del parámetro, el nombre de la variable y el valor.

La propiedad GetTypeProperty es muy importante en esta clase, ya que le indica al procedimiento de que tipo de datos es la variable que se le está enviando.

Ahora para poder utilizar estas clases en un sistema de una forma muy sencilla deberíamos de invocarlas así:

Dim ds As New DataSet

Dim sp As New StoredProcedure("SP_Traer_Datos")
sp.AgregarParametro("nombre_variable", valor_variable)
sp.AgregarParametro("nombre_variable_2", valor_variable_2)

ds = sp.EjecutarProcedimiento()

Se pueden agregar tantas variable como sean necesarias no hay límite, esto en el caso de que se llame a un procedimiento almacenado, pero si lo que se ejecuta es una consulta directa (no recomendada para aplicaciones n capas), se debe hacer así:

Dim ds As New DataSet
Dim strQry As String

strQry = "SELECT * FROM TABLA"
ds = con.ConsultaBD(strQry)

Me.dgDatos.DataSource = ds.Tables(0)

De esta forma podemos invocar procedimientos almacenados en nuestras aplicaciones y no tener que lidiar con estar desarrollando código distinto cada vez que lo necesitemos.

Descargar ejemplo ConexionDB_02.zip