Skip to main content

C# Database-first Approach

Introduction​

The database-first approach is a development strategy where you begin with an existing database and generate your application's data models from that database structure. It's particularly useful when working with legacy databases or when database design is handled by specialized database administrators. In this approach, the database schema dictates the structure of your application's data model.

This tutorial will guide you through implementing a database-first approach in C# applications using Entity Framework, a popular Object-Relational Mapping (ORM) framework for .NET.

Prerequisites​

Before diving into the database-first approach, you should have:

  • Basic knowledge of C# programming
  • Visual Studio installed (2019 or later recommended)
  • SQL Server (Express edition works fine)
  • Basic understanding of databases and SQL

Understanding Database-first Approach​

In the database-first approach workflow:

  1. You start with an existing database
  2. Use tools to generate entity classes based on database tables
  3. Work with these entities in your C# application to perform CRUD operations
  4. Any schema changes are made to the database first, then reflected in the code

This contrasts with code-first approach, where you define your models in code first and generate the database from these models.

Setting Up Your Environment​

First, let's prepare our environment:

  1. Create a new C# project in Visual Studio
  2. Install the necessary NuGet packages
csharp
// Install these packages via NuGet Package Manager or Package Manager Console:
// - Microsoft.EntityFrameworkCore.SqlServer
// - Microsoft.EntityFrameworkCore.Tools
// - Microsoft.EntityFrameworkCore.Design

In Package Manager Console, you can install these packages using:

csharp
Install-Package Microsoft.EntityFrameworkCore.SqlServer
Install-Package Microsoft.EntityFrameworkCore.Tools
Install-Package Microsoft.EntityFrameworkCore.Design

Creating a Sample Database​

For this tutorial, we'll use a simple LibraryDB with two tables: Books and Authors.

Here's the SQL script to create this database:

sql
CREATE DATABASE LibraryDB;
GO

USE LibraryDB;
GO

CREATE TABLE Authors (
    AuthorId INT PRIMARY KEY IDENTITY(1,1),
    FirstName NVARCHAR(50) NOT NULL,
    LastName NVARCHAR(50) NOT NULL,
    BirthDate DATE
);
GO

CREATE TABLE Books (
    BookId INT PRIMARY KEY IDENTITY(1,1),
    Title NVARCHAR(100) NOT NULL,
    PublishedYear INT,
    ISBN NVARCHAR(20) UNIQUE,
    AuthorId INT FOREIGN KEY REFERENCES Authors(AuthorId)
);
GO

-- Insert some sample data
INSERT INTO Authors (FirstName, LastName, BirthDate) VALUES 
('Jane', 'Austen', '1775-12-16'),
('George', 'Orwell', '1903-06-25'),
('J.K.', 'Rowling', '1965-07-31');
GO

INSERT INTO Books (Title, PublishedYear, ISBN, AuthorId) VALUES 
('Pride and Prejudice', 1813, '9780141439518', 1),
('1984', 1949, '9780451524935', 2),
('Harry Potter and the Philosopher''s Stone', 1997, '9780747532699', 3);
GO

Execute this script in SQL Server Management Studio or any SQL tool you prefer.

Generating Models from Database​

Now that we have our database, let's generate C# entity classes using Entity Framework Core:

Step 1: Create a Database Connection​

Add a connection string to your appsettings.json file:

json
{
  "ConnectionStrings": {
    "LibraryConnection": "Server=(localdb)\\MSSQLLocalDB;Database=LibraryDB;Trusted_Connection=True;"
  }
}

Step 2: Scaffold the DbContext and Entity Classes​

In Package Manager Console, run:

csharp
Scaffold-DbContext "Server=(localdb)\MSSQLLocalDB;Database=LibraryDB;Trusted_Connection=True;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models

This command generates entity classes and a DbContext class in the Models directory.

Step 3: Examine the Generated Classes​

The scaffolding process creates:

  1. A LibraryDBContext class that derives from DbContext
  2. Entity classes for each table (Book and Author)
  3. Configuration for the relationships between entities

Here's what the generated classes might look like:

csharp
// LibraryDBContext.cs
using System;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata;

namespace YourProject.Models
{
    public partial class LibraryDBContext : DbContext
    {
        public LibraryDBContext()
        {
        }

        public LibraryDBContext(DbContextOptions<LibraryDBContext> options)
            : base(options)
        {
        }

        public virtual DbSet<Author> Authors { get; set; }
        public virtual DbSet<Book> Books { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            if (!optionsBuilder.IsConfigured)
            {
                optionsBuilder.UseSqlServer("Server=(localdb)\\MSSQLLocalDB;Database=LibraryDB;Trusted_Connection=True;");
            }
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            // Configuration code for entities
            // ...
        }
    }
}
csharp
// Author.cs
using System;
using System.Collections.Generic;

namespace YourProject.Models
{
    public partial class Author
    {
        public Author()
        {
            Books = new HashSet<Book>();
        }

        public int AuthorId { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public DateTime? BirthDate { get; set; }

        public virtual ICollection<Book> Books { get; set; }
    }
}
csharp
// Book.cs
using System;
using System.Collections.Generic;

namespace YourProject.Models
{
    public partial class Book
    {
        public int BookId { get; set; }
        public string Title { get; set; }
        public int? PublishedYear { get; set; }
        public string ISBN { get; set; }
        public int? AuthorId { get; set; }

        public virtual Author Author { get; set; }
    }
}

Using the Generated Models​

Now that we have our models, let's see how to use them in a simple console application:

csharp
using System;
using System.Linq;
using YourProject.Models;
using Microsoft.EntityFrameworkCore;

namespace DatabaseFirstDemo
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var context = new LibraryDBContext())
            {
                // Query all books with their authors
                var books = context.Books
                    .Include(b => b.Author)
                    .ToList();

                Console.WriteLine("=== Books in our library ===");
                
                foreach (var book in books)
                {
                    Console.WriteLine($"Title: {book.Title}");
                    Console.WriteLine($"Year: {book.PublishedYear}");
                    Console.WriteLine($"Author: {book.Author.FirstName} {book.Author.LastName}");
                    Console.WriteLine();
                }

                // Add a new author
                var newAuthor = new Author
                {
                    FirstName = "F. Scott",
                    LastName = "Fitzgerald",
                    BirthDate = new DateTime(1896, 9, 24)
                };
                
                context.Authors.Add(newAuthor);
                context.SaveChanges();
                
                // Add a book by this author
                var newBook = new Book
                {
                    Title = "The Great Gatsby",
                    PublishedYear = 1925,
                    ISBN = "9780743273565",
                    AuthorId = newAuthor.AuthorId
                };
                
                context.Books.Add(newBook);
                context.SaveChanges();
                
                Console.WriteLine("New book added successfully!");
            }
        }
    }
}

Output​

When you run this program, you'll see output similar to:

=== Books in our library ===
Title: Pride and Prejudice
Year: 1813
Author: Jane Austen

Title: 1984
Year: 1949
Author: George Orwell

Title: Harry Potter and the Philosopher's Stone
Year: 1997
Author: J.K. Rowling

New book added successfully!

Updating the Database Model When the Schema Changes​

If your database schema changes, you'll need to update your models:

  1. Make changes to your database (add tables, columns, etc.)
  2. Re-scaffold the database, but use the -Force flag to overwrite existing files:
csharp
Scaffold-DbContext "Server=(localdb)\MSSQLLocalDB;Database=LibraryDB;Trusted_Connection=True;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models -Force

You may want to back up any custom code you've added to these classes before running this command.

Best Practices for Database-first Approach​

  1. Keep Database Migration Scripts: Maintain SQL scripts for all database changes for version control
  2. Create Partial Classes: If you need to add custom methods to your entity classes, use partial classes to avoid losing them when re-scaffolding
  3. Use Data Annotations Sparingly: If you modify the generated model with data annotations, remember these will be lost upon re-scaffolding
  4. Consider View Models: For complex views, create separate view model classes rather than directly using entity classes
  5. Use Stored Procedures: The database-first approach works well with stored procedures for complex operations

Practical Example: Building a Library Management System​

Let's expand our example into a simple library management system that allows us to:

  • View all books
  • Search books by author
  • Check out books to borrowers

First, let's add a new table to our database:

sql
USE LibraryDB;
GO

CREATE TABLE Borrowers (
    BorrowerId INT PRIMARY KEY IDENTITY(1,1),
    Name NVARCHAR(100) NOT NULL,
    Email NVARCHAR(100) UNIQUE
);
GO

CREATE TABLE BookLoans (
    LoanId INT PRIMARY KEY IDENTITY(1,1),
    BookId INT FOREIGN KEY REFERENCES Books(BookId),
    BorrowerId INT FOREIGN KEY REFERENCES Borrowers(BorrowerId),
    CheckoutDate DATETIME NOT NULL DEFAULT GETDATE(),
    DueDate DATETIME NOT NULL,
    ReturnedDate DATETIME NULL
);
GO

-- Insert sample data
INSERT INTO Borrowers (Name, Email) VALUES 
('John Smith', '[email protected]'),
('Sarah Johnson', '[email protected]');
GO

INSERT INTO BookLoans (BookId, BorrowerId, CheckoutDate, DueDate) VALUES 
(1, 1, GETDATE(), DATEADD(day, 14, GETDATE())),
(3, 2, GETDATE(), DATEADD(day, 14, GETDATE()));
GO

After updating our database, re-scaffold the models:

csharp
Scaffold-DbContext "Server=(localdb)\MSSQLLocalDB;Database=LibraryDB;Trusted_Connection=True;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models -Force

Now let's create a simple library management application:

csharp
using System;
using System.Linq;
using YourProject.Models;
using Microsoft.EntityFrameworkCore;

namespace LibraryManagementSystem
{
    class Program
    {
        static void Main(string[] args)
        {
            bool exit = false;
            
            while (!exit)
            {
                Console.WriteLine("===== Library Management System =====");
                Console.WriteLine("1. View all books");
                Console.WriteLine("2. Search books by author");
                Console.WriteLine("3. Check out a book");
                Console.WriteLine("4. Return a book");
                Console.WriteLine("5. Exit");
                Console.Write("Select an option: ");
                
                var option = Console.ReadLine();
                
                switch (option)
                {
                    case "1":
                        ViewAllBooks();
                        break;
                    case "2":
                        SearchBooksByAuthor();
                        break;
                    case "3":
                        CheckoutBook();
                        break;
                    case "4":
                        ReturnBook();
                        break;
                    case "5":
                        exit = true;
                        break;
                    default:
                        Console.WriteLine("Invalid option. Please try again.");
                        break;
                }
                
                Console.WriteLine("\nPress any key to continue...");
                Console.ReadKey();
                Console.Clear();
            }
        }

        static void ViewAllBooks()
        {
            using (var context = new LibraryDBContext())
            {
                var books = context.Books
                    .Include(b => b.Author)
                    .ToList();
                
                Console.WriteLine("\n=== Available Books ===");
                foreach (var book in books)
                {
                    // Check if book is currently loaned
                    var loan = context.BookLoans
                        .FirstOrDefault(l => l.BookId == book.BookId && l.ReturnedDate == null);
                    
                    string status = loan == null ? "Available" : "Checked Out";
                    
                    Console.WriteLine($"ID: {book.BookId}, Title: {book.Title}, " +
                        $"Author: {book.Author.FirstName} {book.Author.LastName}, Status: {status}");
                }
            }
        }

        static void SearchBooksByAuthor()
        {
            Console.Write("\nEnter author's name (or part of it): ");
            string searchTerm = Console.ReadLine();
            
            using (var context = new LibraryDBContext())
            {
                var books = context.Books
                    .Include(b => b.Author)
                    .Where(b => b.Author.FirstName.Contains(searchTerm) || 
                                b.Author.LastName.Contains(searchTerm))
                    .ToList();
                
                if (books.Count == 0)
                {
                    Console.WriteLine("No books found for this author.");
                    return;
                }
                
                Console.WriteLine($"\n=== Books by author matching '{searchTerm}' ===");
                foreach (var book in books)
                {
                    Console.WriteLine($"ID: {book.BookId}, Title: {book.Title}, " +
                        $"Author: {book.Author.FirstName} {book.Author.LastName}, Year: {book.PublishedYear}");
                }
            }
        }

        static void CheckoutBook()
        {
            using (var context = new LibraryDBContext())
            {
                // Display available books
                var availableBooks = context.Books
                    .Include(b => b.Author)
                    .Where(b => !context.BookLoans
                        .Any(l => l.BookId == b.BookId && l.ReturnedDate == null))
                    .ToList();
                
                Console.WriteLine("\n=== Available Books ===");
                foreach (var book in availableBooks)
                {
                    Console.WriteLine($"ID: {book.BookId}, Title: {book.Title}, " +
                        $"Author: {book.Author.FirstName} {book.Author.LastName}");
                }
                
                // Get book ID
                Console.Write("\nEnter the ID of the book to check out: ");
                if (!int.TryParse(Console.ReadLine(), out int bookId))
                {
                    Console.WriteLine("Invalid book ID.");
                    return;
                }
                
                // Display borrowers
                var borrowers = context.Borrowers.ToList();
                Console.WriteLine("\n=== Borrowers ===");
                foreach (var borrower in borrowers)
                {
                    Console.WriteLine($"ID: {borrower.BorrowerId}, Name: {borrower.Name}");
                }
                
                // Get borrower ID
                Console.Write("\nEnter the ID of the borrower: ");
                if (!int.TryParse(Console.ReadLine(), out int borrowerId))
                {
                    Console.WriteLine("Invalid borrower ID.");
                    return;
                }
                
                // Create a new loan
                var newLoan = new BookLoan
                {
                    BookId = bookId,
                    BorrowerId = borrowerId,
                    CheckoutDate = DateTime.Now,
                    DueDate = DateTime.Now.AddDays(14) // 2 weeks loan period
                };
                
                context.BookLoans.Add(newLoan);
                context.SaveChanges();
                
                Console.WriteLine("Book checked out successfully!");
            }
        }

        static void ReturnBook()
        {
            using (var context = new LibraryDBContext())
            {
                // Display checked out books
                var checkedOutBooks = context.BookLoans
                    .Include(l => l.Book)
                    .Include(l => l.Borrower)
                    .Where(l => l.ReturnedDate == null)
                    .ToList();
                
                if (checkedOutBooks.Count == 0)
                {
                    Console.WriteLine("No books are currently checked out.");
                    return;
                }
                
                Console.WriteLine("\n=== Currently Checked Out Books ===");
                foreach (var loan in checkedOutBooks)
                {
                    Console.WriteLine($"Loan ID: {loan.LoanId}, Book: {loan.Book.Title}, " +
                        $"Borrower: {loan.Borrower.Name}, Due: {loan.DueDate.ToShortDateString()}");
                }
                
                // Get loan ID
                Console.Write("\nEnter the Loan ID for the book being returned: ");
                if (!int.TryParse(Console.ReadLine(), out int loanId))
                {
                    Console.WriteLine("Invalid loan ID.");
                    return;
                }
                
                // Find and update the loan
                var loan = context.BookLoans.Find(loanId);
                if (loan == null)
                {
                    Console.WriteLine("Loan not found.");
                    return;
                }
                
                loan.ReturnedDate = DateTime.Now;
                context.SaveChanges();
                
                Console.WriteLine("Book returned successfully!");
            }
        }
    }
}

Summary​

The database-first approach in C# is a powerful way to work with existing databases or when database design is handled separately from application development. It allows you to:

  1. Generate C# entity classes from an existing database schema
  2. Use Entity Framework to interact with the database through these entity classes
  3. Perform CRUD operations with strongly-typed classes
  4. Update your model when the database schema changes

This approach is particularly valuable when working with legacy systems or complex database designs created by specialized database teams. It provides a convenient bridge between your C# application and a pre-existing database infrastructure.

Additional Resources​

  1. Entity Framework Core Documentation
  2. Database-first approach with EF Core
  3. Working with DbContext in EF Core

Exercises​

  1. Enhance the Library Management System to track late returns and calculate fines
  2. Add a feature to extend the due date for book loans
  3. Create a report showing the most popular books and authors based on checkout frequency
  4. Implement a reservation system for books that are currently checked out
  5. Add user authentication and different access levels (librarian vs. regular borrower)

If you spot any mistakes on this website, please let me know at [email protected]. I’d greatly appreciate your feedback! :)