Figures

  Imports System.Data.SqlClient
Public Class DBStuffADONET
    Private sConnectionString As String = _
      "Server=localhost;uid=sa;pwd=;database=NorthWind"
    Private ConnectionCurrent As SqlConnection
    Private TransactionCurrent As SqlTransaction
    Public Sub New()
    End Sub
    Public Sub New(ByVal sNewConnectionString As String)
        If sNewConnectionString <> "" Then
            sConnectionString = sNewConnectionString
        End If
    End Sub
    Public Function RunSQLWithDataSet( _
    ByVal sSQL As String, _
    Optional ByVal sTableName As String = "Table1") _
    As DataSet
        Dim oDataSet As New DataSet()
        Dim oDataAdapter As SqlClient.SqlDataAdapter
        'Create New DataAdapter 
        oDataAdapter = New _
        SqlClient.SqlDataAdapter(sSQL, sConnectionString)
        'Fill Data Set from dataadapter
        oDataAdapter.Fill(oDataSet, sTableName)
        oDataAdapter = Nothing
        'Set return value of function
        Return oDataSet
    End Function
    Public Sub OpenConnectionTrans()
        ConnectionCurrent = New _
        SqlConnection(sConnectionString)
        ConnectionCurrent.Open()
        ' Start a local transaction.
        TransactionCurrent = ConnectionCurrent.BeginTransaction()
    End Sub
    Public Sub OpenConnection()
        ConnectionCurrent = New SqlConnection(sConnectionString)
        ConnectionCurrent.Open()
    End Sub
    Public Sub CommitTransaction()
        TransactionCurrent.Commit()
    End Sub
    Public Sub RollbackTransaction()
        TransactionCurrent.Rollback()
    End Sub
    Public Sub CloseConnection()
        If ConnectionCurrent Is Nothing Then
            Exit Sub
        End If
        If ConnectionCurrent.State = ConnectionState.Open Then
            ConnectionCurrent.Close()
            ConnectionCurrent = Nothing
        End If
    End Sub
    Public Function RunSQLNonQuery(ByVal sSQL As String) _
    As String
        ' Assign a transaction object for a 
      ' pending local transaction.
        Dim cmdLocal As New SqlCommand()
        cmdLocal.Connection = ConnectionCurrent
        cmdLocal.Transaction = TransactionCurrent
        Try
            cmdLocal.CommandText = sSQL
            cmdLocal.ExecuteNonQuery()
        Catch exc As Exception
            Return "Error occurred " & exc.Message
        Finally
        End Try
    End Function
    Public Function RunSQLScalar(ByVal sSQL As String) As String
        ' Assign a transaction object for a 
      ' pending local transaction.
        Dim cmdLocal As New SqlCommand()
        Dim bConnectionAlreadyOpen As Boolean = True
        Dim sTemp As String
        If ConnectionCurrent Is Nothing Then
            bConnectionAlreadyOpen = False
        ElseIf ConnectionCurrent.State <> _
            ConnectionState.Open Then
            bConnectionAlreadyOpen = False
        End If
        If bConnectionAlreadyOpen = False Then
            bConnectionAlreadyOpen = False
            OpenConnection()
        End If
        cmdLocal.Connection = ConnectionCurrent
        Try
            cmdLocal.CommandText = sSQL
            sTemp = CStr(cmdLocal.ExecuteScalar())
        Catch exc As Exception
            sTemp = "Error occurred " & exc.Message
        Finally
            If Not bConnectionAlreadyOpen Then
                ConnectionCurrent.Close()
                ConnectionCurrent = Nothing
            End If
        End Try
        Return sTemp
    End Function
End Class

  Public Function RunSQLNonQuery(ByVal sSQL As String) _
    As String
    ' Assign a transaction object for a 
      ' pending local transaction.
    Dim cmdLocal As New SqlCommand()
    cmdLocal.Connection = ConnectionCurrent
    cmdLocal.Transaction = TransactionCurrent
    Try
        cmdLocal.CommandText = sSQL
        cmdLocal.ExecuteNonQuery()
    Catch exc As Exception
        Return "Error occurred " & exc.Message
    Finally
    End Try
End Function