Importante clases para llenar ComboBox e insertar con un stored procedured 1. conexion
Código:
2. clase storedProcedureImports System.Data.SqlClient
Public Class Conexion
#Region " Variables "
'Variable que utilizaremos para la conexion
Private mUsuario As String
Private mPassword As String
Private mConSSPI As Boolean = False
Private mServidor As String
Private mBaseDatos As String
#End Region
#Region " Propiedades "
Public Property Usuario() As String
Get
Return mUsuario
End Get
Set(ByVal Value As String)
mUsuario = Value
End Set
End Property
Public Property Password() As String
Get
Return mPassword
End Get
Set(ByVal Value As String)
mPassword = Value
End Set
End Property
Public Property ConSSPI() As Boolean
Get
Return mConSSPI
End Get
Set(ByVal Value As Boolean)
mConSSPI = Value
End Set
End Property
Public Property Servidor() As String
Get
Return mServidor
End Get
Set(ByVal Value As String)
mServidor = Value
End Set
End Property
Public Property BaseDatos() As String
Get
Return mBaseDatos
End Get
Set(ByVal Value As String)
mBaseDatos = Value
End Set
End Property
#End Region
'cadena conexion
Private Function StrConexion() As String
Try
Dim strConn As String
strConn = "Server=" & Servidor & "; " & _
"DataBase=" & BaseDatos & "; "
If Not ConSSPI Then
strConn &= "user id=" & Usuario & ";password=" & Password
Else
strConn &= "Integrated Security=SSPI"
End If
Return strConn
Catch ex As Exception
Throw ex
End Try
End Function
'Funcion a la cual se le envia el query y nos retorna un DataSet
Public Function ConsultaBD(ByVal pQuery As String) As DataSet
Try
Return CreateDataSet(pQuery)
Catch ex As Exception
Throw ex
End Try
End Function
'Procesa el query y crea el dataset de la consulta
Private Function CreateDataSet(ByVal strSQL As String) As DataSet
Try
Dim sqlConn As New SqlConnection(Me.StrConexion)
'SqlCommand es utilizado para ejecutar los comandos SQL
Dim sqlCmd As New SqlCommand(strSQL, sqlConn)
sqlCmd.CommandTimeout = 3600
'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)
Return ds
Catch ex As Exception
Throw ex
End Try
End Function
End Class
Código:
3. clase storedProcedureParameterImports System.Data
Imports System.Data.SqlClient
Imports System.Data.sql
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"
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
Public Function EjecutarProcedimiento() As DataSet
Try
Dim Conn As SqlClient.SqlConnection = New SqlClient.SqlConnection("data source=(local);" & "initial catalog = TpmActionProject; integrated security = SSPI")
Dim sqlCmd As New SqlCommand(Me.Nombre, Conn)
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.Close()
Return ds
Catch ex As Exception
Throw ex
End Try
End Function
Public Function EjecutarProcedimientoListaAll() As DataTable
Try
Dim Conn As SqlClient.SqlConnection = New SqlClient.SqlConnection("data source=(local);" & "initial catalog = TpmActionProject; integrated security = SSPI")
Dim sqlCmd As New SqlCommand(Me.Nombre, Conn)
Dim dTable As DataTable = New DataTable
Dim adapter As New SqlDataAdapter
sqlCmd.CommandType = CommandType.StoredProcedure
adapter.SelectCommand = sqlCmd
adapter.Fill(dTable)
Return dTable
Catch ex As Exception
Throw ex
End Try
End Function
End Class
Código:
Public Class StoredProcedureParameter
Private mVariable As String
Private mValor As Object
Public Property Variable() As String
Get
Return mVariable
End Get
Set(ByVal Value As String)
mVariable = Value
End Set
End Property
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
luego en el formulario:
' llenar el ComboBox
Código:
' insertar un dato en una tabla Public Sub cargaList()
Try
Dim x As New StoredProcedure("usp_getTypeContact")
Dim dr As DataTable = x.EjecutarProcedimientoListaAll()
Cbid.DataSource = dr
Cbid.DisplayMember = "cDescription"
Cbid.ValueMember = "nIdTypeContact"
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
Código:
Nota: se le realizaron modificaciones y anexos al código publicado en la siguiente pg: Public Sub insert()
Try
Dim ds As New DataSet
Dim sp As New StoredProcedure("usp_insertContact")
sp.AgregarParametro("nIdTypeContact", CInt(Cbid.SelectedValue))
sp.AgregarParametro("cName", CStr(Txtname.Text))
sp.AgregarParametro("cCargo", CStr(Txtcargo.Text))
sp.AgregarParametro("cAddress", CStr(Txtaddress.Text))
sp.AgregarParametro("cMail", CStr(Txtmail.Text))
sp.AgregarParametro("cTelephone", CStr(Txttelephone.Text))
ds = sp.EjecutarProcedimiento()
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
http://www.dotnetcr.com/Libreria.asp...clase-conexion
Aut: royrojas
