use lambda expressions to modularize your code!
this solutions makes use of
Lambda-Expressions[
^] and
Closures[
^] which deserve a chapter or two of its own, but you can achieve very elegant solutions. dont hesitate to ask further question, if anything is not clear:
Sub Main()
ExecuteWithinTransaction(Sub(con, trans) InsertA("Al", con, trans))
ExecuteWithinTransaction(Sub(con, trans) InsertB("Bert", con, trans))
ExecuteWithinTransaction(Sub(con, trans)
InsertA("Albert", con, trans)
InsertB("Einstein", con, trans)
End Sub)
End Sub
Sub ExecuteWithinTransaction(ByVal action As Action(Of SqlConnection, SqlTransaction))
Dim connection As SqlConnection = New SqlConnection("CONNECTIONSTRING")
connection.Open()
Try
Dim transaction = connection.BeginTransaction()
Try
action(connection, transaction)
transaction.Commit()
Catch ex As Exception
transaction.Rollback()
Throw
End Try
Finally
connection.Close()
End Try
End Sub
Sub InsertA(ByVal name As String, ByVal connection As SqlConnection, ByVal transaction As SqlTransaction)
Dim cmd As SqlCommand = New SqlCommand("INSERT INTO XXX (Name) VALUES (@NAME)", connection, transaction)
cmd.Parameters.AddWithValue("@Name", name)
cmd.ExecuteNonQuery()
End Sub
Sub InsertB(ByVal name As String, ByVal connection As SqlConnection, ByVal transaction As SqlTransaction)
Dim cmd As SqlCommand = New SqlCommand("INSERT INTO YYY (Name) VALUES (@NAME)", connection, transaction)
cmd.Parameters.AddWithValue("@Name", name)
cmd.ExecuteNonQuery()
End Sub
btw: this coding style looks nicer in C# ;)
in VisualStudio versions prior 2010 the used call to ExecuteWithinTransaction seems unsupported, as Lambdas where introduced in VS 2010 first. Nevertheless anonymous delegates are around since .NET 2.0 (which makes the usage a bit uglier).
Additional, the delegate Action<t,> was not present in .NET 2.0, so you will have to define it by yourself:
Delegate Sub Action(Of T1, T2)(ByVal Arg1 As T1, ByVal Arg2 As T2)
also, multi line anonymouse Subs seem to be not supported in VS2008 / VB.NET 9.0 either, so you would have to define the Subs as Functions:
Function InsertA(ByVal name As String, ByVal connection As String, ByVal transaction As String) As Boolean
Console.WriteLine(name, connection, transaction)
Return True
End Function
Function InsertB(ByVal name As String, ByVal connection As String, ByVal transaction As String) As Boolean
Console.WriteLine(name, connection, transaction)
Return True
End Function
and call it this way:
ExecuteWithinTransaction(Function(con As String, trans As String)
InsertA("Albert", con, trans)
InsertB("Einstein", con, trans)
Return True
End Function)
any way, this pattern should be applicable in
VS 2008 and .NET 2.0[
^] also.
Sorry for the chaos, but i´m not a VB-Developer :)