Answer Guruji(8920128728): Combine SQL-DB And MongoDB In Same Blazor App

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

Create A Simple Blazor Server Application With .NET Core 3.0 (https://answerguruji.blogspot.com/2019/11/create-simple-blazor-server-application.html)
Create a Single Page App with Blazor Server and Entity Framework Core 3.0 (https://answerguruji.blogspot.com/2019/11/create-single-page-app-with-blazor.html)

Traditionally, in monolithic applications, we are using single database server for entire application. But I am combining SQL database and MongoDB in 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 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 Blazor server template.

We must install 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 SQL database.

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

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

Create SQL database and table using entity framework and data migration

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

add-migration Initial

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

We can use below command to create database and table.

update-database

Database and table will be created shortly.

We can create an “IEmployeeService“ interface and declare 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 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 patient = await _dbContext.Employees.FindAsync(id);  
            if (patient == null)  
            {  
                return false;  
            }  
  
            _dbContext.Employees.Remove(patient);  
            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 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 MongoDB connection string, database name, and collection name inside the appsettings.json file instead of hard coding the values.

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

We can create “IMongoDbSettings” interface and declare 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 “MongoDbSettings” class and inherit 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 Startup class.

We can create an “ICityService“ interface and declare 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 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(book => book.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 above class and fetched MongoDB configuration values from appsettings.json file.

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

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

We can enable detailed circuit errors for Blazor server application also in 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 Blazor application. We can create all razor components inside “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 Employee 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 above component. City component is used to fetch city name from MongoDB and Employee service is used to save all employee data in 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 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;  
    }  
}  