Imports System.Data
Imports System.Data.SqlClient
Public Class Database
Dim connStr As String = "Data Source=TUPC\SQLEXPRESS;Initial Catalog=bdactivos;Integrated Security=True"
Dim conn As New SqlConnection
Public Sub New()
conn.ConnectionString = connStr
End Sub
Public Sub OpenConnection()
Try
If conn.State = ConnectionState.Closed Then
conn.Open()
End If
Catch ex As Exception
Throw ex
End Try
End Sub
Public Sub CloseConnection()
Try
If conn.State <> ConnectionState.Closed Then
conn.Close()
End If
Catch ex As Exception
Throw ex
End Try
End Sub
Public Function ExecuteSP(ByVal spName As String, ByVal params As ArrayList) As DataTable
Try
'crear comando
Dim command As New SqlCommand
command.CommandType = CommandType.StoredProcedure
command.CommandText = spName
command.CommandTimeout = 5
command.Connection = conn
For Each param As SqlParameter In params
command.Parameters.Add(param)
Next
'metodo de ejecución
Dim ds As New DataSet 'objeto creado vacio
Dim da As New SqlDataAdapter(command)
da.Fill(ds)
'asegurarnos de que se obtuvo una respuesta de la BD
If ds.Tables.Count > 0 Then
Return ds.Tables(0)
Else
Return Nothing
End If
Catch ex As Exception
Throw ex
End Try
End Function
Public Function ExecuteQuery(ByVal sql As String) As DataTable
Try
Dim command As New SqlCommand
command.CommandType = CommandType.Text
command.CommandText = sql
command.CommandTimeout = 5
command.Connection = conn
Dim ds As New DataSet
Dim da As New SqlDataAdapter(command)
da.Fill(ds)
If ds.Tables.Count > 0 Then
Return ds.Tables(0)
Else
Return Nothing
End If
Catch ex As Exception
Throw ex
End Try
End Function
Public Sub ExecuteNonQuery(ByVal sql As String)
Try
Dim command As New SqlCommand
command.CommandType = CommandType.Text
command.CommandText = sql
command.CommandTimeout = 5
command.Connection = conn
command.ExecuteNonQuery()
Catch ex As Exception
Throw ex
End Try
End Sub
Public Function ExecuteSPWithTransaction(ByVal spName As String, ByVal params As ArrayList) As DataTable
Dim transaction As SqlTransaction = conn.BeginTransaction(IsolationLevel.Snapshot)
Try
Dim command As New SqlCommand
command.CommandType = CommandType.StoredProcedure
command.CommandText = spName
command.CommandTimeout = 5
command.Connection = conn
For Each param As SqlParameter In params
command.Parameters.Add(param)
Next
command.Transaction = transaction
Dim ds As New DataSet
Dim da As New SqlDataAdapter
da.Fill(ds)
transaction.Commit()
If ds.Tables.Count > 0 Then
Return ds.Tables(0)
Else
Return Nothing
End If
Catch ex As Exception
transaction.Rollback()
Throw ex
End Try
End Function
Public Function ExecuteSPWithOutParam(ByVal spName As String, ByVal params As ArrayList, ByVal indexParamSalida As Int16) As Object
Try
Dim command As New SqlCommand
command.CommandType = CommandType.StoredProcedure
command.CommandText = spName
command.CommandTimeout = 5
command.Connection = conn
For Each param As SqlParameter In params
command.Parameters.Add(param)
Next
Dim ds As New DataSet
Dim da As New SqlDataAdapter(command)
da.Fill(ds)
Dim obj As Object = command.Parameters.Item(indexParamSalida).Value
Return obj
Catch ex As Exception
Throw ex
End Try
End Function
End Class
