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 AccesoArchivos
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 AccesoArchivos
mCon.ConnectionString = ObjAccesoArchivo.Conn.ConnectionString
ObjAccesoBD = New AccesoXML
Base = ObjAccesoBD.BaseDatos
Servidor = ObjAccesoBD.Servidor
End Sub
Private Shared Function Ejecutar(Procedimiento As String, ParamArray Parametro() As String) As DataTable
Try
i = 0
mCon.Open()
mCom = New SqlCommand
mCom.CommandText = Procedimiento
mCom.CommandType = CommandType.StoredProcedure
mCom.CommandTimeout = 3000000
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
