gptAI
Home

Connect Blazor To SQL Server Database: Easy Steps Explained

11 min read 11-15- 2024
Connect Blazor To SQL Server Database: Easy Steps Explained

Table of Contents :

Blazor is a powerful framework for building interactive web applications using C# and .NET. One of the common requirements when developing web applications is to connect to a SQL Server database to retrieve and manipulate data. In this article, we will explore the easy steps to connect Blazor to a SQL Server database, allowing you to build robust applications with data storage and retrieval capabilities. 🛠️

What is Blazor?

Blazor is a web framework that allows developers to build interactive web applications using C#. It provides two hosting models: Blazor Server and Blazor WebAssembly (WASM). While Blazor WebAssembly runs on the client-side, Blazor Server executes on the server, which enables easier access to server resources, including databases.

Why Connect Blazor to SQL Server?

Connecting Blazor to a SQL Server database offers several advantages:

  • Robust Data Management: SQL Server provides powerful data management capabilities, including transactions, concurrency control, and data integrity.
  • Scalability: SQL Server is designed to handle large volumes of data and can scale effectively as your application grows.
  • Secure: SQL Server includes various security features to help protect sensitive data.

Prerequisites

Before you start, ensure you have the following:

  1. Visual Studio: A version that supports Blazor development.
  2. SQL Server: Local instance or an accessible SQL Server database.
  3. Entity Framework Core: A popular Object-Relational Mapping (ORM) tool for .NET.

Step 1: Setting Up the SQL Server Database

To connect Blazor to a SQL Server database, you first need to set up a database. Here are the steps:

  1. Open SQL Server Management Studio (SSMS).
  2. Connect to your SQL Server instance.
  3. Create a new database by right-clicking on the "Databases" node and selecting "New Database". Name it, for example, BlazorDemo.
CREATE DATABASE BlazorDemo;
  1. Create a table to store data. For instance, create a Products table with the following SQL script:
CREATE TABLE Products (
    Id INT PRIMARY KEY IDENTITY(1,1),
    Name NVARCHAR(100) NOT NULL,
    Price DECIMAL(18,2) NOT NULL
);

Step 2: Creating a Blazor Project

Now, let’s create a Blazor project.

  1. Open Visual Studio and select "Create a new project".
  2. Choose Blazor App from the templates and click Next.
  3. Name your project (e.g., BlazorProductApp) and select a location.
  4. Choose either Blazor Server App or Blazor WebAssembly App based on your preference.
  5. Click Create.

Step 3: Adding Entity Framework Core

To interact with SQL Server, we will use Entity Framework Core.

  1. Open the NuGet Package Manager by right-clicking on your project in Solution Explorer and selecting "Manage NuGet Packages".
  2. Search for the following packages and install them:
    • Microsoft.EntityFrameworkCore.SqlServer
    • Microsoft.EntityFrameworkCore.Tools

Step 4: Creating the Data Model

Next, we need to create a data model that represents the Products table in our database.

  1. Right-click on the project, select "Add" -> "Class" and name it Product.cs.
public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
}
  1. Create a new class called AppDbContext.cs to define the database context.
using Microsoft.EntityFrameworkCore;

public class AppDbContext : DbContext
{
    public AppDbContext(DbContextOptions options) : base(options) { }

    public DbSet Products { get; set; }
}

Step 5: Configuring the Database Connection

Now it's time to set up the database connection.

  1. Open the appsettings.json file and add your SQL Server connection string:
{
  "ConnectionStrings": {
    "DefaultConnection": "Server=(localdb)\\mssqllocaldb;Database=BlazorDemo;Trusted_Connection=True;"
  }
}
  1. Open the Startup.cs file and configure the services to use Entity Framework and the SQL Server connection:
public void ConfigureServices(IServiceCollection services)
{
    services.AddDbContext(options =>
        options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));

    services.AddRazorPages();
    services.AddServerSideBlazor();
}

Step 6: Creating the Repository Pattern

To make data access easier, you can implement a repository pattern.

  1. Create an interface called IProductRepository.cs.
public interface IProductRepository
{
    Task> GetProductsAsync();
    Task GetProductByIdAsync(int id);
    Task AddProductAsync(Product product);
    Task UpdateProductAsync(Product product);
    Task DeleteProductAsync(int id);
}
  1. Implement the interface in a class called ProductRepository.cs.
public class ProductRepository : IProductRepository
{
    private readonly AppDbContext _context;

    public ProductRepository(AppDbContext context)
    {
        _context = context;
    }

    public async Task> GetProductsAsync()
    {
        return await _context.Products.ToListAsync();
    }

    public async Task GetProductByIdAsync(int id)
    {
        return await _context.Products.FindAsync(id);
    }

    public async Task AddProductAsync(Product product)
    {
        _context.Products.Add(product);
        await _context.SaveChangesAsync();
    }

    public async Task UpdateProductAsync(Product product)
    {
        _context.Products.Update(product);
        await _context.SaveChangesAsync();
    }

    public async Task DeleteProductAsync(int id)
    {
        var product = await _context.Products.FindAsync(id);
        if (product != null)
        {
            _context.Products.Remove(product);
            await _context.SaveChangesAsync();
        }
    }
}
  1. Register the repository in the Startup.cs file.
services.AddScoped();

Step 7: Building the Blazor Component

Now that we have our repository set up, let’s create a Blazor component to display and manage the products.

  1. Create a new folder called Pages if it doesn't exist, and add a new component called Product.razor.
@page "/products"

@inject IProductRepository ProductRepository


Products



        @foreach (var product in products)
        {
            
        }
    

    
        

            Name
            Price
            Actions
        

    
    

                @product.Name
                @product.Price.ToString("C")
                
                    
                
            






@code {
    private List products;

    protected override async Task OnInitializedAsync()
    {
        products = await ProductRepository.GetProductsAsync();
    }

    private async Task AddProduct()
    {
        var product = new Product { Name = "New Product", Price = 9.99M };
        await ProductRepository.AddProductAsync(product);
        products = await ProductRepository.GetProductsAsync();
    }

    private async Task DeleteProduct(int id)
    {
        await ProductRepository.DeleteProductAsync(id);
        products = await ProductRepository.GetProductsAsync();
    }
}

Step 8: Running the Application

Now it’s time to run your application and see everything in action!

  1. Press F5 to start debugging the application.
  2. Navigate to /products to see your list of products.
  3. Test adding and deleting products to verify that everything is functioning as expected.

Important Notes

Remember to create a migration for your database schema using the command Add-Migration InitialCreate in the Package Manager Console, and then update the database with Update-Database. This will ensure that your database matches the model definitions you've created.

Conclusion

Connecting Blazor to a SQL Server database involves several straightforward steps, from setting up the database to creating a Blazor component for data interaction. With this guide, you can build a robust web application that leverages the power of SQL Server for effective data management. 🌟

Incorporating these principles into your development workflow can significantly enhance your applications, offering users a seamless and data-rich experience. Happy coding! 💻

Featured Posts