Figures

Figure 1 DBStuffADONET.vb
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
Figure 2 RunSQLNonQuery
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
Show: