Imports System.Data.SqlClient
Imports System.Collections.Generic
 
Public Class Database
    Implements IDisposable
 
    Private _Connection As SqlConnection
    Private _Transaction As SqlTransaction
 
    Public Shared Property ConnectionString As String
 
    Public Shared Function Open(ByVal withTransaction As Boolean) As Database
        Dim database As New Database(withTransaction)
        Return database
    End Function
    Public Sub New(ByVal withTransaction As Boolean)
        If (withTransaction) Then
            GetConnection()
            BeginTransaction()
        End If
    End Sub
 
    Public Function GenerateParameter(name As String, value As Object) As _
      KeyValuePair(Of String, Object)
        Return New KeyValuePair(Of String, Object)(name, value)
    End Function
    Public Sub Close()
        If (_Transaction IsNot Nothing) Then
            _Transaction.Dispose()
            _Transaction = Nothing
        End If
        If (_Connection IsNot Nothing) Then
            _Connection.Dispose()
            _Connection = Nothing
        End If
    End Sub
    Protected Function GetConnection() As SqlConnection
        If (_Connection IsNot Nothing) Then
            Return _Connection
        Else
            _Connection = New SqlConnection(ConnectionString)
            _Connection.Open()
        End If
        Return _Connection
    End Function
    Public Sub BeginTransaction()
        If (_Connection IsNot Nothing) Then
            _Transaction = _Connection.BeginTransaction()
        Else
            Throw New InvalidOperationException("No open Connection")
        End If
    End Sub
    Public Sub Rollback()
        If (_Transaction IsNot Nothing) Then
            _Transaction.Rollback()
        Else
            Throw New InvalidOperationException("No open Transaction")
        End If
    End Sub
    Public Sub Commit()
        If (_Transaction IsNot Nothing) Then
            _Transaction.Commit()
        Else
            Throw New InvalidOperationException("No open Transaction")
        End If
    End Sub
 
    Public Function ExecuteNonQuery(sql As String, ByVal ParamArray para() As _
      KeyValuePair(Of String, Object)) As Int32
        Dim con As SqlConnection = GetConnection()
        Dim cmd As SqlCommand = con.CreateCommand()
        cmd.CommandText = sql
        For Each parameter As KeyValuePair(Of String, Object) In para
            cmd.Parameters.AddWithValue(parameter.Key, parameter.Value)
        Next
        Return cmd.ExecuteNonQuery()
    End Function
    Public Function ExecuteScalar(sql As String, ByVal ParamArray para() As _
      KeyValuePair(Of String, Object)) As Object
        Dim con As SqlConnection = GetConnection()
        Dim cmd As SqlCommand = con.CreateCommand()
        cmd.CommandText = sql
        For Each parameter As KeyValuePair(Of String, Object) In para
            cmd.Parameters.AddWithValue(parameter.Key, parameter.Value)
        Next
        Return cmd.ExecuteScalar()
    End Function
#Region "IDisposable Support"
    Private disposedValue As Boolean ' So ermitteln Sie überflüssige Aufrufe
 
    ' IDisposable
    Protected Overridable Sub Dispose(disposing As Boolean)
        If Not Me.disposedValue Then
            If disposing Then
                If (_Transaction IsNot Nothing) Then
                    _Transaction.Dispose()
                End If
                If (_Connection IsNot Nothing) Then
                    _Connection.Dispose()
                End If
            End If
        End If
        Me.disposedValue = True
    End Sub
    ' Dieser Code wird von Visual Basic hinzugefügt, um das Dispose-Muster 
    ' richtig zu implementieren.
    Public Sub Dispose() Implements IDisposable.Dispose
        ' Ändern Sie diesen Code nicht. Fügen Sie oben in Dispose(ByVal 
        ' disposing As Boolean) Bereinigungscode ein.
        Dispose(True)
        GC.SuppressFinalize(Me)
    End Sub
#End Region
 
End Class

Public Class Businesslogic
    Protected Sub CreateLogin(database As Database, userId As Integer)
        database.ExecuteNonQuery("Insert Into Login (UserID, ....) Values (" & _
          "@UserID,....)", _
                                 database.GenerateParameter("@userID", userId))
    End Sub
    Protected Sub CreateUser(database As Database, firstName As String)
        database.ExecuteNonQuery("Insert Into [User] (FirstName, ....) Values (" & _
          "@FirstName,....)", _
                                 database.GenerateParameter("@FirstName", _
                                 firstName))
    End Sub
    Public Sub CreateUserWithLogin(userID As Integer, FirstName As String)
        Using database As New Database(True)
            CreateLogin(database, userID)
            CreateUser(database, FirstName)
            database.Commit()
        End Using
    End Sub
End Class