Extend class functions with repeatable common code

Question

I use Dapper to access my data, as per code below. There are only two functions but real code can have hundreads. I would like to log in a specific database table all executed SQL statements. This should be universal across all DataRepository class functions.

What is the best way to implement such functionality to avoid repeating it within each single function like SelectCustomers, SelectEmployees, etc.? Please share an example.

Public Class BaseRepository
Protected Shared Function OpenConnection() As IDbConnection
    Dim connection As IDbConnection
    connection = New SqlConnection(MyAppSettings.PascomDB)
    connection.Open()
    Return connection
End Function
End Class


Public Class DataRepository
Inherits BaseRepository
Public Function SelectCustomers() As IEnumerable(Of Customers)
    Using connection As IDbConnection = OpenConnection()
        Dim query As String = "SELECT * FROM Customers"
        Return connection.Query(Of Customers)(query)
        connection.Close()
    End Using
End Function

Public Function SelectEmployees() As IEnumerable(Of Employees)
    Using connection As IDbConnection = OpenConnection()
        Dim query As String = "SELECT * FROM Employees"
        Return connection.Query(Of Employees)(query)
        connection.Close()
    End Using
End Function
End Class

Answer 1

You could keep it simple and just add a method to the base class:

Public Class BaseRepository

    ' ...

    Protected Shared Function LogAndQuery(Of T)(query As String) As IEnumerable(Of T)
        Using connection As IDbConnection = OpenConnection()
            ' TODO: add logging of the query here
            Return connection.Query(Of T)(query)
            ' connection.Close() - note, this is unreachable code, but it's being disposed anyway
        End Using
    End Function

End Class

And use that in your repository methods:

Public Function SelectCustomers() As IEnumerable(Of Customers)
    Dim query As String = "SELECT * FROM Customers"
    Return LogAndQuery(Of Customers)(query)
End Function