Public Sub FillDs(ByVal strSQL As String, ByRef dsData As DataSet)

Public Sub FillDs(ByVal strSQL As String, ByRef dsData As DataSet)
Dim conData As New SqlClient.SqlConnection(strConnectionString)
Try
Dim SqlComm As New SqlClient.SqlCommand(strSQL, conData)
SqlComm.CommandType = CommandType.Text
Dim sqlAdp As New SqlClient.SqlDataAdapter(SqlComm)
If dsData Is Nothing Then
dsData = New DataSet
Else
dsData.Tables.Clear()
End If
sqlAdp.Fill(dsData, "Tabla")
sqlAdp.Dispose()
SqlComm.Dispose()
Catch ex As Exception
Console.Write(ex.ToString)
End Try
If IsNothing(conData) = False AndAlso (conData.State <> ConnectionState.Closed OrElse conData.State <> ConnectionState.Broken) Then
conData.Close()
End If
End Sub

Public Function ExecuteDLM(ByVal strSQL As String) As Long

Public Function ExecuteDLM(ByVal strSQL As String) As Long

Dim lngRecsAfectados As Long
'Dim conDatos As New OracleClient.OracleConnection(strConnectionString)
Dim conDatos As New SqlClient.SqlConnection(strConnectionString)
Try

conDatos.Open()
'Dim scmDatos As New OracleClient.OracleCommand
Dim scmDatos As New SqlClient.SqlCommand
scmDatos.Connection = conDatos
scmDatos.CommandText = strSQL
lngRecsAfectados += scmDatos.ExecuteNonQuery

Catch ex As Exception
Throw New Exception(ex.Message)
Finally
conDatos.Close()
End Try

Return lngRecsAfectados
End Function

Public Function GetDataRow(ByVal strSql As String) As DataRow

Public Function GetDataRow(ByVal strSql As String) As DataRow

'Dim daSelect As New OracleClient.OracleDataAdapter(strSql, strConnectionString)
Dim daSelect As New SqlClient.SqlDataAdapter(strSql, strConnectionString)
Dim dsDatos As New DataSet
daSelect.Fill(dsDatos, "Tabla")
daSelect.Dispose()

If dsDatos.Tables.Count > 0 Then
If dsDatos.Tables(0).Rows.Count > 0 Then
Return dsDatos.Tables(0).Rows(0)
Else
Return Nothing
End If
Else
Return Nothing
End If

End Function

Public Function GetDataTable(ByVal strSql As String) As DataTable

Public Function GetDataTable(ByVal strSql As String) As DataTable

'Dim daSelect As New OracleClient.OracleDataAdapter(strSql, strConnectionString)
Dim daSelect As New SqlClient.SqlDataAdapter(strSql, strConnectionString)
Dim dsDatos As New DataSet
daSelect.Fill(dsDatos, "Tabla")
daSelect.Dispose()

If dsDatos.Tables.Count > 0 Then
Return dsDatos.Tables(0)
Else
Return Nothing
End If

End Function

Public Function GetScalar(ByVal strSql As String) As String

Public Function GetScalar(ByVal strSql As String) As String

'Dim conDatos As New OracleClient.OracleConnection(strConnectionString)
Dim conDatos As New SqlClient.SqlConnection(strConnectionString)
Dim strResultado As String
Try
conDatos.Open()
'Dim scmDatos As New OracleClient.OracleCommand(strSql, conDatos)
Dim scmDatos As New SqlClient.SqlCommand(strSql, conDatos)

strResultado = "" & IIf(IsDBNull(scmDatos.ExecuteScalar()), "", scmDatos.ExecuteScalar())

Catch ex As Exception
Throw New Exception(ex.Message)
Finally
conDatos.Close()
End Try

Return strResultado

End Function

Public Function GetSelect(ByVal strSql As String, Optional ByVal strTableName As String = "Tabla") As DataSet

Public Function GetSelect(ByVal strSql As String, Optional ByVal strTableName As String = "Tabla") As DataSet

'Dim daSelect As New OracleClient.OracleDataAdapter(strSql, strConnectionString)
Dim daSelect As New SqlClient.SqlDataAdapter(strSql, strConnectionString)
Dim dsDatos As New DataSet

Try
daSelect.Fill(dsDatos, strTableName)
daSelect.Dispose()
Return dsDatos
Catch ex As Exception
Throw New Exception(strConnectionString & " " & ex.Message & " " & strSql)
End Try
End Function

Public Function ExecuteDMLVolTran(ByVal alSQLs As ArrayList) As Long

Public Function ExecuteDMLVolTran(ByVal alSQLs As ArrayList) As Long

Me.DMLVolError = ""
Dim lngRecsAfectados As Long
'Dim conDatos As New OracleClient.OracleConnection(strConnectionString)
Dim conDatos As New SqlClient.SqlConnection(strConnectionString)

'Dim trnTrans As OracleTransaction
Dim trnTrans As SqlTransaction
Dim strSQL As String
Dim intSql As Integer

trnTrans = Nothing
Try
conDatos.Open()
trnTrans = conDatos.BeginTransaction
'Dim scmDatos As New OracleClient.OracleCommand
Dim scmDatos As New SqlClient.SqlCommand
scmDatos.Connection = conDatos
scmDatos.Transaction = trnTrans

For intSql = 0 To alSQLs.Count - 1

strSQL = alSQLs(intSql)
scmDatos.CommandText = strSQL

Try
lngRecsAfectados += scmDatos.ExecuteNonQuery
Catch exsingle As Exception
'Set Error Message
Me.DMLVolError += exsingle.Message & ", "
Throw New Exception(exsingle.Message)
End Try

Next

trnTrans.Commit()

Catch ex As Exception
If Not trnTrans Is Nothing Then
trnTrans.Rollback()
End If
lngRecsAfectados = 0
Finally
conDatos.Close()
End Try

Return lngRecsAfectados

End Function

Public Function ExecuteDLMVol(ByVal alSQLs As ArrayList) As Long

Public Function ExecuteDLMVol(ByVal alSQLs As ArrayList) As Long

Me.DMLVolError = ""
Dim lngRecsAfectados As Long
'Dim conDatos As New OracleClient.OracleConnection(strConnectionString)
Dim conDatos As New SqlClient.SqlConnection(strConnectionString)
Dim strSQL As String
Dim intSql As Integer
Try
conDatos.Open()
'Dim scmDatos As New OracleClient.OracleCommand
Dim scmDatos As New SqlClient.SqlCommand
scmDatos.Connection = conDatos

For intSql = 0 To alSQLs.Count - 1

strSQL = alSQLs(intSql)
scmDatos.CommandText = strSQL

Try
lngRecsAfectados += scmDatos.ExecuteNonQuery
Me.DMLVolError += "OK, "
Catch exsingle As Exception
'Continue with next
Me.DMLVolError += exsingle.Message & ", "
End Try

Next

Catch ex As Exception
'Throw New Exception(ex.Message)
Finally
conDatos.Close()
End Try

Return lngRecsAfectados

End Function

Public Function Delete(ByVal strSQL As String) As Long

Public Function Delete(ByVal strSQL As String) As Long

Dim lngRecsAfectados As Long
'Dim conDatos As New OracleClient.OracleConnection(strConnectionString)
Dim conDatos As New SqlClient.SqlConnection(strConnectionString)
Try
conDatos.Open()

'Dim scmDatos As New OracleClient.OracleCommand(strSQL, conDatos)
Dim scmDatos As New SqlClient.SqlCommand(strSQL, conDatos)
lngRecsAfectados = scmDatos.ExecuteNonQuery

Catch ex As Exception
lngRecsAfectados = 0
Me.DMLVolError = ex.Message
'Throw New Exception(ex.Message)
Finally
conDatos.Close()
End Try

Return lngRecsAfectados

End Function

Public Function UpdateDataSet(ByVal strSQL As String, ByVal dtDatos As DataTable) As Boolean

Public Function UpdateDataSet(ByVal strSQL As String, ByVal dtDatos As DataTable) As Boolean

'Dim daUpdate As New OracleClient.OracleDataAdapter
Dim daUpdate As New SqlClient.SqlDataAdapter

'Dim cbUpdate As OracleClient.OracleCommandBuilder
Dim cbUpdate As SqlClient.SqlCommandBuilder
'Dim cnDataBase As New OracleClient.OracleConnection(strConnectionString)
Dim cnDataBase As New SqlClient.SqlConnection(strConnectionString)

cnDataBase.Open()
'cbUpdate = New OracleClient.OracleCommandBuilder(daUpdate)
cbUpdate = New SqlClient.SqlCommandBuilder(daUpdate)
'daUpdate.SelectCommand = New OracleClient.OracleCommand(strSQL, cnDataBase)
daUpdate.SelectCommand = New SqlClient.SqlCommand(strSQL, cnDataBase)
Try
daUpdate.Update(dtDatos)
Catch ex As Exception
Throw New Exception(ex.Message)
Finally
daUpdate.Dispose()
cnDataBase.Close()
End Try

Return True

End Function

Public Function Update(ByVal strSQL As String) As Long

Public Function Update(ByVal strSQL As String) As Long

Dim lngRecsAfectados As Long
'Dim conDatos As New OracleClient.OracleConnection(strConnectionString)
Dim conDatos As New SqlClient.SqlConnection(strConnectionString)
Try
conDatos.Open()

'Dim scmDatos As New OracleClient.OracleCommand(strSQL, conDatos)
Dim scmDatos As New SqlClient.SqlCommand(strSQL, conDatos)

lngRecsAfectados = scmDatos.ExecuteNonQuery

Catch ex As Exception
lngRecsAfectados = 0
Me.DMLVolError = ex.Message
'Throw New Exception(ex.Message)
Finally
conDatos.Close()
End Try

Return lngRecsAfectados

End Function

Public Function Insert(ByVal strSql As String) As Long

Public Function Insert(ByVal strSql As String) As Long

Dim lngRecsAfectados As Long
'Dim conDatos As New OracleClient.OracleConnection(strConnectionString)
Dim conDatos As New SqlClient.SqlConnection(strConnectionString)
Try
conDatos.Open()

'Dim scmDatos As New OracleClient.OracleCommand(strSql, conDatos)
Dim scmDatos As New SqlClient.SqlCommand(strSql, conDatos)

lngRecsAfectados = scmDatos.ExecuteNonQuery

Catch ex As DataException
lngRecsAfectados = 0
Me.DMLVolError = ex.Message
Catch ex As Exception
lngRecsAfectados = 0
If ex.Message.Contains("Violation of PRIMARY KEY") Then
Me.DMLVolError = "*UNIQUE*"
Else
Me.DMLVolError = ex.Message
End If
Finally
conDatos.Close()
End Try

Return lngRecsAfectados

End Function

Property DMLVolError() As String

Property DMLVolError() As String
Get
Return strVolError
End Get
Set(ByVal Value As String)
strVolError = Value
End Set
End Property