Post
Ask Question

Combine SQL-DB And MongoDB In Same Blazor App

Introduction

Blazor is a new framework built by Microsoft for creating interactive client-side web UI with a .NET codebase. We can write both client-side and server-side code in C#.NET itself. I have already written two articles about the Blazor server on C# Corner. Please refer to the below articles for more basics about the Blazor framework.

Traditionally, in monolithic applications, we are using a single database server for the entire application. However, I am combining SQL database and MongoDB in the same application. This application will be a combination of RDBMS and No SQL databases. We will create a Blazor app from scratch and create a SQL database and a table using data migration. We will use this SQL DB to save employee data. We will use the MongoDB database to save City data. You can use the same approach for other .NET Core 3.0 applications also. We can see all the actions step by step.

Prerequisites

  • Visual Studio 2019 (version 16.3 or later)
  • .NET Core SDK 3.0
  • MongoDB Server

Create a Blazor application in Visual Studio 2019

We can create a Blazor app in Visual Studio 2019 using the Blazor server template.

Create a new blazor

We must install the below libraries to our project. We can use NuGet to install these libraries.

  • “Microsoft.EntityFrameworkCore.SqlServer”
  • “Microsoft.EntityFrameworkCore.Tools”
  • “MongoDB.Driver”

We can create an “Employee” class inside the “Data” folder with below properties.

Employee.cs

namespace BlazorSQLAndMongoDB.Data  
{  
    public class Employee  
    {  
        public string Id { get; set; }  
        public string Name { get; set; }  
        public string Department { get; set; }  
        public string Designation { get; set; }  
        public string Company { get; set; }  
        public string City { get; set; }  
    }  
}  

We can create a “SqlDbContext” class inside the Data folder for entity framework-related operations.

SqlDbContext.cs

using Microsoft.EntityFrameworkCore;  
namespace BlazorSQLAndMongoDB.Data  
{  
    public class SqlDbContext : DbContext  
    {  
        public SqlDbContext(DbContextOptions<SqlDbContext> options)  
           : base(options)  
        {  
        }  
        public DbSet<Employee> Employees { get; set; }  
    }  
}  

We can create a connection string in “appsettings.json” for the SQL database.

Connection string

We have used a local SQL server, which is available with Visual Studio to create a database and table. You can use any SQL server at your convenience.

We must register the SqlDbContext class inside the “ConfigureServices” method in the Startup class.

Configure Service

Create SQL database and table using entity framework and data migration

We can use the NuGet Package Manager Console from the Tools menu and use the below migration commands to create an SQL database and table.

add-migration Initial

The above command will create a migration script class inside the “Migrations” folder with the current timestamp suffix.

We can use the below command to create a database and table.

update-database

The database and table will be created shortly.

We can create an “IEmployeeService“ interface and declare the below methods inside the interface.

IEmployeeService.cs

using System.Collections.Generic;  
using System.Threading.Tasks;  
namespace BlazorSQLAndMongoDB.Data  
{  
    public interface IEmployeeService  
    {  
        Task<List<Employee>> GetEmployees();  
        Task<bool> CreateEmployee(Employee employee);  
        Task<bool> EditEmployee(string id, Employee employee);  
        Task<Employee> SingleEmployee(string id);  
        Task<bool> DeleteEmployee(string id);  
    }  
}  

We can implement the above methods from the interface in another class “EmployeeService”

EmployeeService.cs

using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Threading.Tasks;
namespace BlazorSQLAndMongoDB.Data
{
    public class EmployeeService : IEmployeeService
    {
        private readonly SqlDbContext _dbContext;
        public EmployeeService(SqlDbContext dbContext)
        {
            _dbContext = dbContext;
        }
        public async Task<List<Employee>> GetEmployees()
        {
            return await _dbContext.Employees.ToListAsync();
        }
        public async Task<bool> CreateEmployee(Employee employee)
        {
            employee.Id = Guid.NewGuid().ToString();
            _dbContext.Add(employee);
            try
            {
                await _dbContext.SaveChangesAsync();
                return true;
            }
            catch (DbUpdateException)
            {
                return false;
            }
        }
        public async Task<Employee> SingleEmployee(string id)
        {
            return await _dbContext.Employees.FindAsync(id);
        }
        public async Task<bool> EditEmployee(string id, Employee employee)
        {
            if (id != employee.Id)
            {
                return false;
            }
            _dbContext.Entry(employee).State = EntityState.Modified;
            await _dbContext.SaveChangesAsync();
            return true;
        }
        public async Task<bool> DeleteEmployee(string id)
        {
            var employee = await _dbContext.Employees.FindAsync(id);
            if (employee == null)
            {
                return false;
            }
            _dbContext.Employees.Remove(employee);
            await _dbContext.SaveChangesAsync();
            return true;
        }
    }
}

We have added all logic for CRUD operations inside the service class. Please note that you can even create this service without an interface.

We can create a City class with the below properties. City data will be stored in MongoDB.

City.cs

using MongoDB.Bson;
using MongoDB.Bson.Serialization.Attributes;
namespace BlazorSQLAndMongoDB.Data
{
    public class City
    {
        [BsonId]
        [BsonRepresentation(BsonType.ObjectId)]
        public string Id { get; set; }
        public string Name { get; set; }
        public string State { get; set; }
    }
}

We can keep the MongoDB connection string, database name, and collection name inside the appsettings.json file instead of hard coding the values.

MongoDB connection string

We must create an interface and class for reading the values from the appsettings.json file.

We can create the “IMongoDbSettings” interface and declare the below properties.

IMongoDbSettings.cs

namespace BlazorSQLAndMongoDB.Data
{
    public interface IMongoDbSettings
    {
        string CollectionName { get; set; }
        string ConnectionString { get; set; }
        string DatabaseName { get; set; }
    }
}

We can create the “MongoDbSettings” class and inherit the IMongoDbSettings interface inside the class.

MongoDbSettings.cs

namespace BlazorSQLAndMongoDB.Data  
{  
    public class MongoDbSettings : IMongoDbSettings  
    {  
        public string CollectionName { get; set; }  
        public string ConnectionString { get; set; }  
        public string DatabaseName { get; set; }  
    }  
}  

We can register this interface and class inside the ConfigureServices class in the Startup class.

Interface and class inside the configuration

We can create an “ICityService“ interface and declare the below methods inside the interface.

ICityService.cs

using System.Collections.Generic;
using System.Threading.Tasks;
namespace BlazorSQLAndMongoDB.Data
{
    public interface ICityService
    {
        Task<List<City>> GetCities();
        Task<bool> CreateCity(City city);
        Task<bool> EditCity(string id, City city);
        Task<City> SingleCity(string id);
        Task<bool> DeleteCity(string id);
    }
}

We can implement the above methods from the interface in another class “CityService”

CityService.cs

using MongoDB.Driver;
using System.Collections.Generic;
using System.Threading.Tasks;
namespace BlazorSQLAndMongoDB.Data
{
    public class CityService : ICityService
    {
        private readonly IMongoCollection<City> _cities;
        public CityService(IMongoDbSettings settings)
        {
            var client = new MongoClient(settings.ConnectionString);
            var database = client.GetDatabase(settings.DatabaseName);
            _cities = database.GetCollection<City>(settings.CollectionName);
        }
        public async Task<bool> CreateCity(City city)
        {
            try
            {
                await _cities.InsertOneAsync(city);
                return true;
            }
            catch
            {
                return false;
            }
        }
        public async Task<bool> DeleteCity(string id)
        {
            try
            {
                await _cities.DeleteOneAsync(city => city.Id == id);
                return true;
            }
            catch
            {
                return false;
            }
        }
        public async Task<bool> EditCity(string id, City city)
        {
            try
            {
                await _cities.ReplaceOneAsync(book => book.Id == id, city);
                return true;
            }
            catch
            {
                return false;
            }
        }
        public async Task<List<City>> GetCities()
        {
            try
            {
                return await _cities.Find(city => true).ToListAsync();
            }
            catch
            {
                return null;
            }
        }
        public async Task<City> SingleCity(string id)
        {
            try
            {
                return await _cities.Find<City>(city => city.Id == id).FirstOrDefaultAsync();
            }
            catch
            {
                return null;
            }
        }
    }
}

We have injected the IMongoDbSettings interface in the above class and fetched MongoDB configuration values from the appsettings.json file.

We have added all logic for CRUD actions for the City entity inside the above service class.

We can register both Employee service and City service in the Startup class.

Both employee service and city service

We can enable detailed circuit errors for the Blazor server application also in the startup class.

ConfigureServices (method)

public void ConfigureServices(IServiceCollection services)
{
    services.AddRazorPages();
    services.AddServerSideBlazor();
    services.AddSingleton<WeatherForecastService>();
    services.AddDbContext<SqlDbContext>(options =>
           options.UseSqlServer(Configuration.GetConnectionString("SqlDbContext")));
    services.Configure<MongoDbSettings>(Configuration.GetSection(nameof(MongoDbSettings)));
    services.AddSingleton<IMongoDbSettings>(sp => sp.GetRequiredService<IOptions<MongoDbSettings>>().Value);
    services.AddScoped<IEmployeeService, EmployeeService>();
    services.AddScoped<ICityService, CityService>();
    services.AddServerSideBlazor().AddCircuitOptions(o => o.DetailedErrors = true);
}

We have completed the backend part of the Blazor application. We can create all razor components inside the “Pages” folder for CRUD operations.

We can create components for City first.

ListCities.razor

@using BlazorSQLAndMongoDB.Data
@page "/listcities"
@inject ICityService CityService
<h2>City Details</h2>
<p>
    <a href="/addcity">Create New City</a>
</p>
@if (cities == null)
{
    <img src="./basicloader.gif" />
}
else
{
    <table class='table'>
        <thead>
            <tr>
                <th>Name</th>
                <th>State</th>
            </tr>
        </thead>
        <tbody>
            @foreach (var city in cities)
            {
                <tr>
                    <td>@city.Name</td>
                    <td>@city.State</td>
                    <td>
                        <a href='/editcity/@city.Id'>Edit</a>
                        <a href='/deletecity/@city.Id'>Delete</a>
                    </td>
                </tr>

            }
        </tbody>
    </table>
}
@code {
    List<City> cities;

    protected override async Task OnInitializedAsync()
    {
        cities = await CityService.GetCities();
    }
}

AddCity.razor

@using BlazorSQLAndMongoDB.Data
@page "/addcity"
@inject NavigationManager NavigationManager
@inject ICityService CityService
<h2>Create City</h2>
<hr />
<form>
    <div class="row">
        <div class="col-md-8">
            <div class="form-group">
                <label for="Name" class="control-label">Name</label>
                <input for="Name" class="form-control" @bind="@city.Name" />
            </div>
            <div class="form-group">
                <label for="State" class="control-label">State</label>
                <input for="State" class="form-control" @bind="@city.State" />
            </div>
        </div>
    </div>
    <div class="row">
        <div class="col-md-4">
            <div class="form-group">
                <input type="button" class="btn btn-primary" @onclick="@CreateCity" value="Save"/>
                <input type="button" class="btn" @onclick="@Cancel" value="Cancel" />
            </div>
        </div>
    </div>
</form>
@code {
    City city = new City();
    protected async Task CreateCity()
    {
        await CityService.CreateCity(city);
        NavigationManager.NavigateTo("listcities");
    }
    void Cancel()
    {
        NavigationManager.NavigateTo("listcities");
    }
}

EditCity.razor

@using BlazorSQLAndMongoDB.Data
@page "/editcity/{id}"
@inject NavigationManager NavigationManager
@inject ICityService CityService
<h2>Edit City</h2>
<hr />
<form>
    <div class="row">
        <div class="col-md-8">
            <div class="form-group">
                <label for="Name" class="control-label">Name</label>
                <input for="Name" class="form-control" @bind="@city.Name" />
            </div>
            <div class="form-group">
                <label for="State" class="control-label">State</label>
                <input for="State" class="form-control" @bind="@city.State" />
            </div>
        </div>
    </div>
    <div class="row">
        <div class="form-group">
            <input type="button" class="btn btn-primary" @onclick="@UpdateCity" value="Update" />
            <input type="button" class="btn" @onclick="@Cancel" value="Cancel" />
        </div>
    </div>
</form>
@code {
    [Parameter]
    public string id { get; set; }
    City city = new City();
    protected override async Task OnInitializedAsync()
    {
        city = await CityService.SingleCity(id);
    }
    protected async Task UpdateCity()
    {
        await CityService.EditCity(id, city);
        NavigationManager.NavigateTo("listcities");
    }
    void Cancel()
    {
        NavigationManager.NavigateTo("listcities");
    }
}

DeleteCity.razor

@using BlazorSQLAndMongoDB.Data
@page "/deletecity/{id}"
@inject NavigationManager NavigationManager
@inject ICityService CityService
<h2>Confirm Delete</h2>
<p>Are you sure you want to delete this City with Id: <b>@id</b></p>
<br />
<div class="col-md-4">
    <table class="table">
        <tr>
            <td>Name</td>
            <td>@city.Name</td>
        </tr>
        <tr>
            <td>State</td>
            <td>@city.State</td>
        </tr>
    </table>
    <div class="form-group">
        <input type="button" value="Delete" @onclick="@Delete" class="btn btn-primary" />
        <input type="button" value="Cancel" @onclick="@Cancel" class="btn" />
    </div>
</div>
@code {
    [Parameter]
    public string id { get; set; }
    City city = new City();
    protected override async Task OnInitializedAsync()
    {
        city = await CityService.SingleCity(id);
    }
    protected async Task Delete()
    {
        await CityService.DeleteCity(id);
        NavigationManager.NavigateTo("listcities");
    }
    void Cancel()
    {
        NavigationManager.NavigateTo("listcities");
    }
}

We can create components for Employees now.

ListEmployees.razor

@using BlazorSQLAndMongoDB.Data
@page "/listemployees"
@inject IEmployeeService EmployeeService
<h2>Employee Details</h2>
<p>
    <a href="/addemployee">Create New Employee</a>
</p>
@if (employees == null)
{
    <img src="./basicloader.gif" />
}
else
{
    <table class='table'>
        <thead>
            <tr>
                <th>Name</th>
                <th>Department</th>
                <th>Designation</th>
                <th>Company</th>
                <th>City</th>
            </tr>
        </thead>
        <tbody>
            @foreach (var employee in employees)
            {
                <tr>
                    <td>@employee.Name</td>
                    <td>@employee.Department</td>
                    <td>@employee.Designation</td>
                    <td>@employee.Company</td>
                    <td>@employee.City</td>
                    <td>
                        <a href='/editemployee/@employee.Id'>Edit</a>
                        <a href='/deleteemployee/@employee.Id'>Delete</a>
                    </td>
                </tr>

            }
        </tbody>
    </table>
}
@code {
    List<Employee> employees;

    protected override async Task OnInitializedAsync()
    {
        employees = await EmployeeService.GetEmployees();
    }
}

AddEmployee.razor

@using BlazorSQLAndMongoDB.Data
@page "/addemployee"
@inject NavigationManager NavigationManager
@inject IEmployeeService EmployeeService
@inject ICityService CityService
<h2>Create Employee</h2>
<hr />
<form>
    <div class="row">
        <div class="col-md-8">
            <div class="form-group">
                <label for="Name" class="control-label">Name</label>
                <input for="Name" class="form-control" @bind="@employee.Name" />
            </div>
            <div class="form-group">
                <label for="Department" class="control-label">Department</label>
                <input for="Department" class="form-control" @bind="@employee.Department" />
            </div>
            <div class="form-group">
                <label for="Designation" class="control-label">Designation</label>
                <input for="Designation" class="form-control" @bind="@employee.Designation" />
            </div>
            <div class="form-group">
                <label for="Company" class="control-label">Company</label>
                <input for="Company" class="form-control" @bind="@employee.Company" />
            </div>
            <div class="form-group">
                <label asp-for="City" class="control-label">City</label>
                <select asp-for="City" class="form-control" @bind="@employee.City">
                    <option value="">-- Select City --</option>
                    @foreach (var city in cities)
                    {
                        <option value="@city.Name">@city.Name</option>
                    }
                </select>
            </div>
        </div>
    </div>
    <div class="row">
        <div class="col-md-4">
            <div class="form-group">
                <input type="button" class="btn btn-primary" @onclick="@CreateEmployee" value="Save" />
                <input type="button" class="btn" @onclick="@Cancel" value="Cancel" />
            </div>
        </div>
    </div>
</form>
@code {
    Employee employee = new Employee();
    List<City> cities = new List<City>();
    protected override async Task OnInitializedAsync()
    {
        cities = await CityService.GetCities();
    }
    protected async Task CreateEmployee()
    {
        await EmployeeService.CreateEmployee(employee);
        NavigationManager.NavigateTo("listemployees");
    }
    void Cancel()
    {
        NavigationManager.NavigateTo("listemployees");
    }
}

Please note that I have injected both City service and Employee service in the above component. The city component is used to fetch city names from MongoDB and Employee service is used to save all employee data in the SQL database.

EditEmployee.razor

@using BlazorSQLAndMongoDB.Data

@page "/editemployee/{id}"
@inject NavigationManager NavigationManager
@inject IEmployeeService EmployeeService
@inject ICityService CityService
<h2>Edit Employee</h2>
<hr />
<form>
    <div class="row">
        <div class="col-md-8">
            <div class="form-group">
                <label for="Name" class="control-label">Name</label>
                <input for="Name" class="form-control" @bind="@employee.Name" />
            </div>
            <div class="form-group">
                <label for="Department" class="control-label">Department</label>
                <input for="Department" class="form-control" @bind="@employee.Department" />
            </div>
            <div class="form-group">
                <label for="Designation" class="control-label">Designation</label>
                <input for="Designation" class="form-control" @bind="@employee.Designation" />
            </div>
            <div class="form-group">
                <label for="Company" class="control-label">Company</label>
                <input for="Company" class="form-control" @bind="@employee.Company" />
            </div>
            <div class="form-group">
                <label asp-for="City" class="control-label">City</label>
                <select asp-for="City" class="form-control" @bind="@employee.City">
                    <option value="">-- Select City --</option>
                    @foreach (var city in cities)
                    {
                        <option value="@city.Name">@city.Name</option>
                    }
                </select>
            </div>
        </div>
    </div>
    <div class="row">
        <div class="form-group">
            <input type="button" class="btn btn-primary" @onclick="@UpdateEmployee" value="Update" />
            <input type="button" class="btn" @onclick="@Cancel" value="Cancel" />
        </div>
    </div>
</form>

@code {
    [Parameter]
    public string id { get; set; }

    Employee employee = new Employee();
    List<City> cities = new List<City>();
    protected override async Task OnInitializedAsync()
    {
        cities = await CityService.GetCities();
        employee = await EmployeeService.SingleEmployee(id);
    }
    protected async Task UpdateEmployee()
    {
        await EmployeeService.EditEmployee(id, employee);
        NavigationManager.NavigateTo("listemployees");
    }
    void Cancel()
    {
        NavigationManager.NavigateTo("listemployees");
    }
}

This component is also injected into City service and Employee service.

DeleteEmployee.razor

@using BlazorSQLAndMongoDB.Data
@page "/deleteemployee/{id}"
@inject NavigationManager NavigationManager
@inject IEmployeeService EmployeeService
<h2>Confirm Delete</h2>
<p>Are you sure you want to delete this Employee with Id: <b>@id</b></p>
<br />
<div class="col-md-4">
    <table class="table">
        <tr>
            <td>Name</td>
            <td>@employee.Name</td>
        </tr>
        <tr>
            <td>Department</td>
            <td>@employee.Department</td>
        </tr>
        <tr>
            <td>Designation</td>
            <td>@employee.Designation</td>
        </tr>
        <tr>
            <td>Company</td>
            <td>@employee.Company</td>
        </tr>
        <tr>
            <td>City</td>
            <td>@employee.City</td>
        </tr>
    </table>
    <div class="form-group">
        <input type="button" value="Delete" @onclick="@Delete" class="btn btn-primary" />
        <input type="button" value="Cancel" @onclick="@Cancel" class="btn" />
    </div>
</div>
@code {
    [Parameter]
    public string id { get; set; }
    Employee employee = new Employee();
    protected override async Task OnInitializedAsync()
    {
        employee = await EmployeeService.SingleEmployee(id);
    }
    protected async Task Delete()
    {
        await EmployeeService.DeleteEmployee(id);
        NavigationManager.NavigateTo("listemployees");
    }
    void Cancel()
    {
        NavigationManager.NavigateTo("listemployees");
    }
}

We can modify the NavMenu shared component to add routing for City and Employee data.

NavMenu.razor

<div class="top-row pl-4 navbar navbar-dark">
    <a class="navbar-brand" href="">Blazor with SQL and Mongo</a>
    <button class="navbar-toggler" @onclick="ToggleNavMenu">
        <span class="navbar-toggler-icon"></span>
    </button>
</div>
<div class="@NavMenuCssClass" @onclick="ToggleNavMenu">
    <ul class="nav flex-column">
        <li class="nav-item px-3">
            <NavLink class="nav-link" href="" Match="NavLinkMatch.All">
                <span class="oi oi-home" aria-hidden="true"></span> Home
            </NavLink>
        </li>
        <li class="nav-item px-3">
            <NavLink class="nav-link" href="listcities">
                <span class="oi oi-plus" aria-hidden="true"></span> City details
            </NavLink>
        </li>
        <li class="nav-item px-3">
            <NavLink class="nav-link" href="listemployees">
                <span class="oi oi-list-rich" aria-hidden="true"></span> Employee details
            </NavLink>
        </li>
    </ul>
</div>
@code {
    bool collapseNavMenu = true;
    string NavMenuCssClass => collapseNavMenu ? "collapse" : null;
    void ToggleNavMenu()
    {
        collapseNavMenu = !collapseNavMenu;
    }
}

We have removed the routing for Counter and Weather data which are created by default.

We have completed the entire coding part. We can run the application. Please make sure your MongoDB Windows service is running.

Home

We can create a new City data.

Create city

After saving the data, you can check the MongoDB data using MongoDB compass software.

Cities

We can see the newly added City data available there.

We can create a new Employee data now.

Create employee

We can add one more Employee detail and list two Employee details in the grid.

Employee details

We have successfully added two Cities and two Employees with applications.

Conclusion

In this post, we have seen how to combine SQL DB and MongoDB in the same Blazor application. We have successfully integrated RDBMS and No SQL features in a single application. We can use this approach for other .NET Core 3.0 applications as well. I hope you have enjoyed the post. If you have any queries, please feel free to send feedback. I welcome all your comments.


Similar Articles