Funciones VB.NET para manejo de base de datos

Resumen de Rutinas:
1. GetDataSetBD(String SQL)
2. EjecutaComando(String SQL)

---

Function GetDataSetBD(ByVal StrSQL As String) As DataSet
Dim dbConnection As New SqlConnection(ConfigurationManager.AppSettings("MyDataBaseKey"))
Dim objAdapter As New SqlDataAdapter(StrSQL, dbConnection)
Dim ds As New DataSet()
Try
objAdapter.Fill(ds)
Catch ex As Exception
Throw New Exception(ex.Message, ex.InnerException)
Finally
objAdapter.Dispose()
If dbConnection.State = ConnectionState.Open Then dbConnection.Close()
dbConnection.Dispose()
End Try
Return ds
End Function


Overloads Function EjecutaComando(ByVal strsql As String, ByVal sqlparametros As SqlParameter()) As Boolean
Dim bolaccion As Boolean
Dim objcn As New SqlConnection(ConfigurationManager.AppSettings("MyDataBaseKey"))
objcn.Open()

Dim objcmd As SqlCommand = objcn.CreateCommand()
Dim objTran As SqlTransaction
objTran = objcn.BeginTransaction()
objcmd.Connection = objcn
objcmd.Transaction = objTran
Try
With objcmd
.CommandText = strsql
For Each objpar As SqlParameter In sqlparametros
.Parameters.Add(objpar)
Next
.ExecuteNonQuery()
End With
objTran.Commit()
bolaccion = True
Catch ex As SqlException
objTran.Rollback()
If objcn.State = ConnectionState.Open Then objcn.Close()
objTran.Dispose()
objcmd.Dispose()
objcn.Dispose()

Throw New Exception(ex.Message, ex.InnerException)
Finally
If objcn.State = ConnectionState.Open Then objcn.Close()
objTran.Dispose()
objcmd.Dispose()
objcn.Dispose()
End Try
Return bolaccion
End Function

Overloads Function EjecutaComando(ByVal strsql As String, ByVal sqlparametros As SqlParameter(), ByVal tipo As Integer) As Integer
Dim intID As Integer
Dim objcn As New SqlConnection(ConfigurationManager.AppSettings("MyDataBaseKey"))
objcn.Open()

Dim objcmd As SqlCommand = objcn.CreateCommand()
Dim objTran As SqlTransaction
objTran = objcn.BeginTransaction()
objcmd.Connection = objcn
objcmd.Transaction = objTran
Try
With objcmd
.CommandText = strsql
For Each objpar As SqlParameter In sqlparametros
.Parameters.Add(objpar)
Next
intID = .ExecuteScalar
End With
objTran.Commit()

Catch ex As SqlException
objTran.Rollback()
If objcn.State = ConnectionState.Open Then objcn.Close()
objTran.Dispose()
objcmd.Dispose()
objcn.Dispose()

Throw New Exception(ex.Message, ex.InnerException)
Finally
If objcn.State = ConnectionState.Open Then objcn.Close()
objTran.Dispose()
objcmd.Dispose()
objcn.Dispose()
End Try
Return intID
End Function

Sub RunSQLBD(ByVal StrSQL As String)
Dim dbConnection As New SqlConnection(ConfigurationManager.AppSettings("MyDataBaseKey"))
Dim dbCommand As New SqlCommand(StrSQL, dbConnection)
Try
dbConnection.Open()
dbCommand.ExecuteNonQuery()
Finally
dbConnection.Close()
End Try
End Sub
Function RunSQLBD_ID(ByVal StrSQL As String) As String
Dim dbConnection As New SqlConnection(ConfigurationManager.AppSettings("MyDataBaseKey"))
Dim dbCommand As New SqlCommand(StrSQL, dbConnection)
Dim StrResult As String
Try
dbConnection.Open()
StrResult = dbCommand.ExecuteScalar()
Finally
dbConnection.Close()
End Try
Return StrResult
End Function