Skip to content
Tutorials Center
My learning

Topics

Lesson 30 of 36 ยท Files and Databases

Database Access Patterns

Structure database code in VB.NET - the repository pattern, interfaces, unit of work, transactions, Dapper and Entity Framework Core basics (read-only) with a runnable in-memory repository stand-in in C#.

Scattering SqlCommand calls through button handlers works for a weekend project and hurts after that: SQL text duplicated everywhere, forms that cannot run without a database, logic that cannot be tested. The fix is old and simple: put data access behind a small interface, and let the rest of the program talk only to that interface. This lesson covers that structure, then shows how Dapper and Entity Framework Core fit into it.

Honest limits. No database and no NuGet packages (EF Core, Dapper, providers) exist on this site. Package-based VB code is read-only and no query output is claimed. The architecture itself is plain object-oriented code, so it runs in the C# stand-ins with an in-memory store.

The repository pattern

A repository hides how data is stored and offers collection-like methods.

VB
Public Class Product
    Public Property Id As Integer
    Public Property Name As String
    Public Property Price As Decimal
End Class

Public Interface IProductRepository
    Function GetAll() As IReadOnlyList(Of Product)
    Function GetById(id As Integer) As Product
    Function Add(p As Product) As Product
    Sub Update(p As Product)
    Sub Delete(id As Integer)
End Interface

The forms depend on IProductRepository. One class implements it with SQL Server, another with a list in memory (for tests or demos). Switching storage changes one line where the object is created.

VB
Public Class MainForm
    Private ReadOnly _products As IProductRepository
    Public Sub New(products As IProductRepository)
        InitializeComponent()
        _products = products
    End Sub
End Class

(A WinForms designer needs a parameterless constructor; supply a second one for the designer, or create the repository in Program.Main and pass it in through a property.)

C# An in-memory repository behind an interface (C# stand-in)

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with Console.ReadLine()), it gets the next line from this box.

A SQL implementation (read-only)

VB
Public Class SqlProductRepository
    Implements IProductRepository

    Private ReadOnly _connStr As String
    Public Sub New(connStr As String)
        _connStr = connStr
    End Sub

    Public Function GetById(id As Integer) As Product Implements IProductRepository.GetById
        Using conn As New SqlConnection(_connStr)
            conn.Open()
            Using cmd As New SqlCommand("SELECT Id, Name, Price FROM Products WHERE Id = @id", conn)
                cmd.Parameters.AddWithValue("@id", id)
                Using r = cmd.ExecuteReader()
                    If Not r.Read() Then Return Nothing
                    Return New Product With {.Id = r.GetInt32(0), .Name = r.GetString(1), .Price = r.GetDecimal(2)}
                End Using
            End Using
        End Using
    End Function
    ' ... other members
End Class

Open connections late and close them early: ADO.NET pools physical connections, so Using conn ... Open() per operation is cheap and safe.

Transactions and the unit of work

When several changes must succeed or fail together (move money from A to B), wrap them in one transaction. A unit of work groups repositories that share the same connection and transaction and commits once.

VB
Using conn As New SqlConnection(_connStr)
    conn.Open()
    Using tx = conn.BeginTransaction()
        Try
            ' debit, credit ... using the SAME conn and tx
            tx.Commit()
        Catch
            tx.Rollback()
            Throw
        End Try
    End Using
End Using
C# Commit or roll back (C# stand-in)

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with Console.ReadLine()), it gets the next line from this box.

Dapper: thin and fast (read-only)

Dapper is a micro-ORM: you keep writing SQL, and it maps rows to objects.

VB
Imports Dapper

Using conn As New SqlConnection(connStr)
    Dim cheap = conn.Query(Of Product)("SELECT * FROM Products WHERE Price < @max", New With {.max = 10D})
    conn.Execute("UPDATE Products SET Price = @p WHERE Id = @id", New With {.p = 3D, .id = 1})
End Using

It removes the reader loop but not your responsibility for good SQL.

Entity Framework Core (read-only)

EF Core maps classes to tables and translates LINQ into SQL. You describe a DbContext:

VB
Imports Microsoft.EntityFrameworkCore

Public Class ShopContext
    Inherits DbContext
    Public Property Products As DbSet(Of Product)

    Protected Overrides Sub OnConfiguring(options As DbContextOptionsBuilder)
        options.UseSqlite("Data Source=shop.db")
    End Sub
End Class

Using db As New ShopContext()
    db.Database.EnsureCreated()
    db.Products.Add(New Product With {.Name = "Pen", .Price = 2.5D})
    db.SaveChanges()

    Dim cheap = db.Products.Where(Function(p) p.Price < 10D).OrderBy(Function(p) p.Name).ToList()
End Using

DbContext tracks changes and acts as a unit of work: SaveChanges() writes everything in one transaction. Use migrations (dotnet ef migrations add) to evolve the schema. Because queries are LINQ, everything from LINQ basics applies, but remember deferred execution: the SQL runs at ToList(), not at Where.

Approach You write Good for
Raw ADO.NET SQL and reader loops Maximum control, tiny dependencies
Dapper SQL only Speed with minimal magic
EF Core LINQ and classes Rapid development, migrations

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What is the main benefit of a repository interface?

    The interface decouples forms and logic from the database implementation.
  2. 2. What does a transaction guarantee?

    Commit applies all changes; rollback discards all of them.
  3. 3. In EF Core, when does the SQL for db.Products.Where(...) run?

    EF queries are LINQ and are deferred until enumerated.
  4. 4. What does Dapper do for you?

    Dapper is a micro-ORM that maps rows to objects.

Summary

  • Hide storage behind a repository interface; forms and logic talk only to the interface.
  • Use Using per operation (connections are pooled) and transactions for multi-step changes.
  • Dapper keeps SQL and maps rows; EF Core maps classes, translates LINQ and tracks changes.
  • Test logic with an in-memory implementation of the same interface.

Next: VB.NET and C# Side by Side