Connecting Blazor with SQL Server Database: A Comprehensive Guide

In today’s web development landscape, Blazor has emerged as a powerful framework for building interactive web UIs with C# and .NET. A common requirement for many Blazor applications is to interact with databases, and SQL Server remains a prevalent choice for robust data management. This guide provides a detailed walkthrough on how to effectively connect your Blazor application to a SQL Server database, leveraging Entity Framework Core for seamless data interaction and showcasing data using Syncfusion Blazor components.

This article expands upon the basics, offering a more in-depth exploration and SEO optimization for English-speaking developers seeking to master Blazor database connectivity. We will cover everything from setting up your database to performing Create, Read, Update, and Delete (CRUD) operations within your Blazor application.

Prerequisites

Before diving into the implementation, ensure you have the following software installed and configured:

• Visual Studio 2022 or later: The primary IDE for .NET development.
• .NET 6.0 SDK or later: The .NET runtime and development tools required for Blazor.
• SQL Server 2019 or later: Microsoft’s robust database management system. SQL Server Express is a free edition suitable for development purposes.

Setting Up Your SQL Server Database

First, we need to create a database and a table within SQL Server to store our application data. For this tutorial, we will create a database for managing a library and a table to store book information.

1. Open SQL Server Management Studio (SSMS): Connect to your SQL Server instance.

2. Create a New Database: Right-click on “Databases” and select “New Database…”. Name the database “Library” and click “OK”.

3. New Query: Right-click on the newly created “Library” database, navigate to “Tasks”, and select “New Query”.

4. Create the ‘Book’ Table: Execute the following SQL query to create a table named “Book” with columns for book details:

CREATE TABLE Book (
    Id BIGINT IDENTITY(1,1) PRIMARY KEY NOT NULL,
    Name VARCHAR(200) NOT NULL,
    Author VARCHAR(100) NOT NULL,
    Quantity INT,
    Price INT NOT NULL,
    Available BIT
);

This SQL script creates a table named Book with columns for Id, Name, Author, Quantity, Price, and Available. The Id column is set as the primary key and auto-increments with each new record.

After executing the query, your Book table design should resemble the following:

A visual representation of the Book table structure in SQL Server Management Studio, detailing column names, data types, and constraints.

Creating Your Blazor Web Application

Now, let’s create the Blazor application that will connect to our SQL Server database.

1. Open Visual Studio 2022.
2. Create a New Project: Select “Create a new project”.
3. Choose Blazor Web App: Search for and select “Blazor Web App” and click “Next”.

The Visual Studio “Create a new project” dialog, highlighting the “Blazor Web App” project template.

4. Configure Your Project: In the “Configure your new project” window:
   • Project name: Enter “LibraryManagement” (or your preferred project name).
   • Location: Choose a suitable location to save your project.
   • Click “Next”.

The “Configure your new project” window, with “LibraryManagement” specified as the project name.

5. Select .NET Framework and Create: In the “Additional information” window:
   • Framework: Select “.NET 6.0” or “.NET 7.0” (or later).
   • Authentication type: Choose “None” or your desired authentication method.
   • Interactivity location: Select “Server”.
   • Interactive render mode: Choose “Server”.
   • Click “Create”.

The “Additional information” window, showcasing the selection of .NET framework and Blazor Server App configurations.

This process sets up a Blazor Server application, which is ideal for database-driven applications as it executes on the server and maintains a persistent connection, optimizing data interactions.

Setting up Entity Framework Core

Entity Framework (EF) Core is an Object-Relational Mapper (ORM) that simplifies database interactions in .NET applications. We’ll use EF Core to connect our Blazor app to SQL Server.

Installing Necessary NuGet Packages

1. Open NuGet Package Manager: In Visual Studio, go to “Tools” -> “NuGet Package Manager” -> “Manage NuGet Packages for Solution…”.

2. Install EF Core Tools and SQL Server Provider: In the “Browse” tab, search for and install the following packages:

   • Microsoft.EntityFrameworkCore.Tools
   • Microsoft.EntityFrameworkCore.SqlServer

   Ensure you select the latest stable versions of these packages.

Scaffold DbContext and Model Classes

Scaffolding automatically generates the DbContext and entity classes based on your existing database schema.

1. Open Package Manager Console: Go to “Tools” -> “NuGet Package Manager” -> “Package Manager Console”.

2. Run the Scaffold-DbContext Command: Execute the following command in the Package Manager Console within your LibraryManagement project:

Scaffold-DbContext "Server=localhost;Database=Library;Integrated Security=True;TrustServerCertificate=true;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models -Context LibraryContext -DataAnnotations

Note:

• Replace "Server=localhost;Database=Library;Integrated Security=True" with your actual SQL Server connection string if necessary. TrustServerCertificate=true; is added to handle potential certificate trust issues during local development.
• -OutputDir Models specifies that the generated files will be placed in a “Models” folder.
• -Context LibraryContext names the generated DbContext class as LibraryContext.
• -DataAnnotations ensures data annotations are used in model classes.

This command analyzes your “Library” database and generates:

• LibraryContext.cs: Your DbContext class, which manages the database connection and exposes your tables as DbSet properties.
• Book.cs: A model class representing the “Book” table.

A view of the Solution Explorer, showing the newly generated Book.cs model class and LibraryContext.cs DbContext within the “Models” folder.

Initially, you might find the connection string directly within the OnConfiguring method of LibraryContext.cs. However, it’s best practice to move this sensitive information to the appsettings.json file.

An excerpt from LibraryContext.cs, highlighting the connection string initially placed within the OnConfiguring method.

Moving Connection String to appsettings.json

1. Open appsettings.json: Locate and open the appsettings.json file in your Blazor Server project.

2. Add ConnectionStrings Section: Add a ConnectionStrings section to your appsettings.json file and define your connection string there:

{
  "ConnectionStrings": {
    "LibraryDatabase": "Server=localhost;Database=Library;Integrated Security=True;TrustServerCertificate=true;"
  },
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*"
}

3. Modify Program.cs to Use Connection String from Configuration: Open Program.cs and configure the LibraryContext to use the connection string from your configuration:

builder.Services.AddDbContext<LibraryContext>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("LibraryDatabase")));

This code snippet retrieves the connection string named “LibraryDatabase” from your appsettings.json and configures EF Core to use it for the LibraryContext.

Illustrates the appsettings.json file with the “ConnectionStrings” section and the updated Program.cs file referencing the connection string from configuration.

Creating a Data Access Layer (DAL)

To further organize our code and promote separation of concerns, we’ll create a Data Access Layer (DAL) to handle database interactions.

1. Create ILibraryService.cs Interface: Inside the “Models” folder (or create a new “Services” folder), create a new interface named ILibraryService.cs:

using LibraryManagement.Models;

namespace LibraryManagement.Services
{
    public interface ILibraryService
    {
        IEnumerable<Book> GetBooks();
        void InsertBook(Book book);
        void UpdateBook(long id, Book book);
        void DeleteBook(long id);
        Book? SingleBook(long id); // Return nullable Book
    }
}

Solution Explorer view showing the creation of the ILibraryService.cs interface within the “Models” folder.

2. Create LibraryService.cs: Create a class named LibraryService.cs that implements the ILibraryService interface:

using Microsoft.EntityFrameworkCore;
using LibraryManagement.Models;

namespace LibraryManagement.Services
{
    public class LibraryService : ILibraryService
    {
        private readonly LibraryContext _context;

        public LibraryService(LibraryContext context)
        {
            _context = context;
        }

        public IEnumerable<Book> GetBooks()
        {
            return _context.Books.ToList();
        }

        public void InsertBook(Book book)
        {
            _context.Books.Add(book);
            _context.SaveChanges();
        }

        public void UpdateBook(long id, Book book)
        {
            var existingBook = _context.Books.Find(id);
            if (existingBook != null)
            {
                _context.Entry(existingBook).CurrentValues.SetValues(book);
                _context.SaveChanges();
            }
        }

        public void DeleteBook(long id)
        {
            var book = _context.Books.Find(id);
            if (book != null)
            {
                _context.Books.Remove(book);
                _context.SaveChanges();
            }
        }

        public Book? SingleBook(long id)
        {
            return _context.Books.Find(id);
        }
    }
}

3. Register Services in Program.cs: Register both ILibraryService and LibraryService in your Program.cs file as scoped services:

builder.Services.AddScoped<ILibraryService, LibraryService>();
builder.Services.AddDbContext<LibraryContext>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("LibraryDatabase")));

This registers LibraryService as the implementation for ILibraryService, making it available for dependency injection throughout your Blazor application.

Integrating Syncfusion Blazor Grid

Syncfusion Blazor Grid is a feature-rich component for displaying and manipulating tabular data. We’ll use it to showcase data from our SQL Server database and enable CRUD operations.

Add Syncfusion NuGet Packages

1. Open NuGet Package Manager: (Tools -> NuGet Package Manager -> Manage NuGet Packages for Solution…).

2. Install Syncfusion Packages: Browse and install the following NuGet packages:

   • Syncfusion.Blazor.Grid
   • Syncfusion.Blazor.Themes

Register Syncfusion Blazor Service

1. Open Program.cs: Add the Syncfusion Blazor service registration:

using Syncfusion.Blazor;

// ... other code

builder.Services.AddSyncfusionBlazor();

// ... rest of Program.cs

Add Syncfusion Namespace to _Imports.razor

1. Open _Imports.razor: Add the Syncfusion Blazor namespace:

@using Syncfusion.Blazor
@using Syncfusion.Blazor.Grids

Add Theme Stylesheet

1. Open _Layout.cshtml (for .NET 6) or _Host.cshtml (for .NET 7+): Add the Syncfusion Bootstrap 5 theme stylesheet within the <head> section:

<link href="_content/Syncfusion.Blazor.Themes/bootstrap5.css" rel="stylesheet" />

Add Script Reference

1. Open _Layout.cshtml (for .NET 6) or _Host.cshtml (for .NET 7+): Add the Syncfusion Blazor script reference at the end of the <body> section:

    </body>
</html>
<script src="_content/Syncfusion.Blazor.Core/scripts/syncfusion-blazor.min.js" type="text/javascript"></script>

Displaying Data in Syncfusion Blazor Grid

1. Open or Create a Razor Page: Navigate to the Pages folder and open an existing Razor page (e.g., Index.razor) or create a new one (e.g., BookGrid.razor).

2. Inject ILibraryService: Inject the ILibraryService into your Razor page:

@page "/bookgrid"
@using LibraryManagement.Models
@using LibraryManagement.Services
@inject ILibraryService LibraryService

<PageTitle>Book Grid</PageTitle>

<h1>Book Inventory</h1>

<SfGrid DataSource="@LibraryBooks" TValue="Book">
    <GridColumns>
        <GridColumn Field="@nameof(Book.Id)" IsPrimaryKey="true" IsIdentity="true" Visible="false"></GridColumn>
        <GridColumn Field="@nameof(Book.Name)" Width="150"></GridColumn>
        <GridColumn Field="@nameof(Book.Author)" Width="150"></GridColumn>
        <GridColumn Field="@nameof(Book.Quantity)" Width="90" TextAlign="TextAlign.Right"></GridColumn>
        <GridColumn Field="@nameof(Book.Price)" Width="90" Format="C2" TextAlign="TextAlign.Right"></GridColumn>
        <GridColumn Field="@nameof(Book.Available)" DisplayAsCheckBox="true" Width="70"></GridColumn>
    </GridColumns>
</SfGrid>

@code {
    public IEnumerable<Book> LibraryBooks { get; set; } = Enumerable.Empty<Book>(); // Initialize to empty

    protected override void OnInitialized()
    {
        LibraryBooks = LibraryService.GetBooks();
    }
}

This code snippet:

• Injects ILibraryService.
• Declares a LibraryBooks property to hold book data.
• Retrieves book data from the service in OnInitialized and assigns it to LibraryBooks.
• Configures SfGrid with DataSource bound to LibraryBooks and defines columns to display book properties.

Screenshot of the Blazor application running in a browser, showcasing the Syncfusion Blazor Grid populated with data fetched from the SQL Server database.

Implementing CRUD Operations

To enable CRUD operations within the Syncfusion Blazor Grid, we’ll configure editing and handle grid events.

1. Enable Editing and Toolbar: Modify your SfGrid component in the Razor page to include <GridEditSettings> and <GridToolbar>:

<SfGrid DataSource="@LibraryBooks" Toolbar="@(new List<string>() { "Add", "Edit", "Delete", "Cancel", "Update" })" TValue="Book">
    <GridEditSettings AllowAdding="true" AllowDeleting="true" AllowEditing="true" Mode="EditMode.Normal"></GridEditSettings>
    <GridEvents OnActionBegin="ActionBeginHandler" OnActionComplete="ActionCompleteHandler" TValue="Book"></GridEvents>
    <GridColumns>
        <GridColumn Field="@nameof(Book.Id)" IsPrimaryKey="true" IsIdentity="true" Visible="false"></GridColumn>
        <GridColumn Field="@nameof(Book.Name)" Width="150"></GridColumn>
        <GridColumn Field="@nameof(Book.Author)" Width="150"></GridColumn>
        <GridColumn Field="@nameof(Book.Quantity)" Width="90" TextAlign="TextAlign.Right"></GridColumn>
        <GridColumn Field="@nameof(Book.Price)" Width="90" Format="C2" TextAlign="TextAlign.Right"></GridColumn>
        <GridColumn Field="@nameof(Book.Available)" DisplayAsCheckBox="true" Width="70"></GridColumn>
    </GridColumns>
</SfGrid>

2. Implement Event Handlers: Add the ActionBeginHandler and ActionCompleteHandler methods in the @code block to handle CRUD operations:

@code {
    public IEnumerable<Book> LibraryBooks { get; set; } = Enumerable.Empty<Book>();

    protected override void OnInitialized()
    {
        LibraryBooks = LibraryService.GetBooks();
    }

    public void ActionBeginHandler(ActionEventArgs<Book> Args)
    {
        if (Args.RequestType.Equals(Syncfusion.Blazor.Grids.Action.Save))
        {
            if (Args.Action == "Add")
            {
                LibraryService.InsertBook(Args.Data);
            }
            else if (Args.Action == "Edit")
            {
                LibraryService.UpdateBook(Args.Data.Id, Args.Data);
            }
        }
        else if (Args.RequestType.Equals(Syncfusion.Blazor.Grids.Action.Delete))
        {
            LibraryService.DeleteBook(Args.Data.Id);
        }
    }

    public void ActionCompleteHandler(ActionEventArgs<Book> Args)
    {
        if (Args.RequestType.Equals(Syncfusion.Blazor.Grids.Action.Save) || Args.RequestType.Equals(Syncfusion.Blazor.Grids.Action.Delete))
        {
            LibraryBooks = LibraryService.GetBooks(); // Refresh data after CRUD operation
            StateHasChanged(); // Notify Blazor to re-render the component
        }
    }
}

Explanation of Event Handlers:

• ActionBeginHandler:
  • Triggered when a grid action (Add, Edit, Delete, Save) begins.
  • Checks Args.RequestType to identify the action type.
  • For “Save” requests (Add or Edit), it further checks Args.Action to differentiate between adding and editing.
  • Calls the corresponding methods in LibraryService (InsertBook, UpdateBook, DeleteBook) to perform database operations.
• ActionCompleteHandler:
  • Triggered when a grid action completes.
  • Refreshes the LibraryBooks data by calling LibraryService.GetBooks() to reflect the changes in the grid.
  • StateHasChanged() is called to notify Blazor that the component’s state has changed, forcing a re-render of the grid with updated data.

Now, you have a fully functional Blazor Grid connected to your SQL Server database, with complete CRUD capabilities. You can add, edit, and delete book records directly within the grid, and these changes will be persisted in your SQL Server database.

Animated GIF demonstrating adding, editing, and deleting records within the Syncfusion Blazor Grid, showcasing the CRUD operations interacting with the SQL Server database.

Conclusion

This comprehensive guide has demonstrated how to seamlessly connect your Blazor Server application to a SQL Server database using Entity Framework Core and enhance data presentation and manipulation with Syncfusion Blazor Grid. By following these steps, you can build robust, data-driven Blazor applications with efficient database interactions and a rich user interface. This approach not only streamlines development but also ensures your application is well-structured, maintainable, and optimized for performance.