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