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

.
.
.

#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

'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

'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

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

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

Dim ds As New DataSet
Dim strQry As String

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

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