Clase SQLconexion

Imports System.Data.SqlClient

Public Class ConexionSQL


#Region "Declaraciones"

    Private Shared StrCon As String = ""
    Private Shared mCon As SqlConnection
    Private Shared mDs As New DataSet
    Private Shared mDa As New SqlDataAdapter
    Private Shared mDt As New DataTable
    Private Shared mCom As New SqlCommand
    Private Shared ObjAccesoArchivo As New AccesoArchivo
    Private Shared ObjAccesoBD As AccesoXML
    Private Shared Servidor, Base As String
    Private Shared mDtResultado As New DataTable
    Private Shared i As Integer

#End Region


#Region "Funciones"

    Public Shared Function ExecuteDataset(ByVal unCommandText As String) As DataSet
        Try
            mDs = New DataSet
            mCon = New SqlConnection(StrCon)
            mDa = New SqlDataAdapter(unCommandText, mCon)
            mCon.Open()
            mDa.Fill(mDs)
            Return mDs
        Catch ex As Exception
            MsgBox("Ocurrio un error en funcion DATASET. " & ex.Message.ToString)
            Return Nothing
        Finally
            mCon.Close()
            mCon.Dispose()
        End Try
    End Function

    Public Shared Function ExecuteDataTable(ByVal unCommandText As String) As DataTable
        Try
            mDt = New DataTable
            mCon = New SqlConnection(StrCon)
            mDa = New SqlDataAdapter(unCommandText, mCon)
            mCon.Open()
            mDa.Fill(mDt)
            Return mDt
        Catch ex As Exception
            MsgBox("Ocurrio un error en funcion DATATABLE. " & ex.Message.ToString)
            Return Nothing
        Finally
            mCon.Close()
            mCon.Dispose()
        End Try
    End Function

    Public Shared Function ObtenerBase() As String
        Return Base
    End Function

    Public Shared Function ObtenerServidor() As String
        Return Servidor
    End Function


#Region "RN"

    Public Shared Function EjecutarSP(ByVal unStoredProcedure As String, ParamArray param() As String) As DataTable
        mDtResultado = New DataTable
        EjecutarStoredProcedure(unStoredProcedure, param)
        mDtResultado = mDt
        Return mDtResultado
    End Function

    Public Shared Function EjecutarSP(ByVal unStoredProcedure As String) As DataTable
        mDtResultado = New DataTable
        EjecutarStoredProcedure(unStoredProcedure)
        mDtResultado = mDt
        Return mDtResultado
    End Function

#End Region


#Region "BD"

    Private Shared Sub EjecutarStoredProcedure(ByVal unStoredProcedure As String, ParamArray parametro() As String)
        InicializarDatos()
        Ejecutar(unStoredProcedure, parametro)
    End Sub

    Private Shared Sub EjecutarStoredProcedure(ByVal unStoredProcedure As String)
        InicializarDatos()
        Ejecutar(unStoredProcedure)
    End Sub

    Private Shared Sub InicializarDatos()
        mCon = New SqlConnection
        ObjAccesoArchivo = New AccesoArchivo
        mCon.ConnectionString = ObjAccesoArchivo.Conn.ConnectionString
        ObjAccesoBD = New AccesoXML
        Base = ObjAccesoBD.BaseDatos
        Servidor = ObjAccesoBD.Servidor
    End Sub

    Public Shared Function Ejecutar(Procedimiento As String, ParamArray Parametro() As Object) As DataTable
        Try
            i = 0
            mCon.Open()
            mCom = New SqlCommand
            mCom.CommandText = Procedimiento
            mCom.CommandType = CommandType.StoredProcedure
            mCom.Connection = mCon
            mDt = New DataTable
            SqlCommandBuilder.DeriveParameters(mCom)
            For i = 0 To Parametro.Length
                If i > 0 Then
                    CType(mCom.Parameters(i), SqlParameter).Value = Parametro(i - 1)
                End If
            Next
            mDt = CargarDataTable(mCom.ExecuteReader)
            Return mDt
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Mensaje de Sistema")
            mDt = Nothing
            Return mDt
        Finally
            mCon.Close()
        End Try
    End Function

    Private Shared Function CargarDataTable(ByVal Rst As SqlDataReader) As DataTable
        mDt = New DataTable
        Try
            If Rst.HasRows Then
                mDt.Load(Rst)
            Else
                mDt = Nothing
            End If
            Return mDt
        Catch ex As Exception
            Return mDt
        Finally
            Rst.Close()
        End Try
    End Function

#End Region


#End Region


End Class