Blazor read Json from Database

Create table 

 create table jsontable
(
id int identity(1,1),
fname nvarchar(100),
Lname nvarchar(100),
age int 
)

create procedure

 CREATE proc getjsondata
as
select * from jsontable
for json path
  
 

FrontEnd Use: Blazor Server APP

DataBase: Microsoft SQL Server

ORM: ADO.NET

Video Reference click here 

SqlServer gives an easy way to create queries for output as JSON data. To process data return from the database here i am using blazor server app you can use it in another framework. for complete the process I create a table in Sqlserver and to get JSON data I create storeprocedure this procedure return JSON formatted data 

You need to add the following packages 

Microsoft.EntityFrameworkCore.SqlServer

Newtonsoft.Json

First step create modal as jsontable.cs

       

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace ReadSqlJson.Model
{
    public class Jsontable
    {
        public int id { get; set; }
        public string fname { get; set; }
        public string Lname { get; set; }
        public int age { get; set; }
    }
}
       
 

Create a service IprocessJson.cs this file contains the interface name IprocessJson which implement in class ProcessJson you can also separate these two classes 

    
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Configuration;
using Newtonsoft.Json;
using ReadSqlJson.Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Threading.Tasks;

namespace ReadSqlJson.Services
{
    public interface IprocessJson
    {
        public Task<List<Jsontable>> getJsonFromDatabase();
    }


    //I create implemetn class here you can sepertate this

    public class ProcessJson : IprocessJson
    {
        public static IConfiguration config;
        readonly SqlConnection connection;

        public ProcessJson(IConfiguration _config)
        {
            config = _config;
            connection = new SqlConnection(config.GetConnectionString("DbConnect"));
        }
        public async Task<List<Jsontable>> getJsonFromDatabase()
        {
            var list = new List<Jsontable>();
            await Task.Run(() =>
            {
                SqlCommand command = new SqlCommand("getjsondata", connection)
                {
                    CommandType = CommandType.StoredProcedure
                };
                SqlDataAdapter adapter = new SqlDataAdapter
                {
                    SelectCommand = command
                };
                DataTable dt = new DataTable();
                adapter.Fill(dt);
                string json = dt.Rows[0][0].ToString();
                list = JsonConvert.DeserializeObject<List<Jsontable>>(json);
            });
            return list;
        }

    }

}

Finally, I called it from the index. razor page for display these data 

       

           @page "/"
@inject Services.IprocessJson p
@using Model

    <style>

        #customers {
            font-family: "Trebuchet MS", Arial, Helvetica, sans-serif;
            border-collapse: collapse;
            width: 100%;
        }

            #customers td, #customers th {
                border: 1px solid #ddd;
                padding: 8px;
            }

            #customers tr:nth-child(even) {
                background-color: #f2f2f2;
            }

            #customers tr:hover {
                background-color: #ddd;
            }

            #customers th {
                padding-top: 12px;
                padding-bottom: 12px;
                text-align: left;
                background-color: #4CAF50;
                color: white;
            }
    </style>
<h1>Hello, world!</h1>

Welcome to your new app.

<SurveyPrompt Title="How is Blazor working for you?" />
<button @onclick="calldata">Load Data</button>
<table id="customers">
    <thead>
        <tr>
            <td>ID</td>
            <td>First Name</td>
            <td>Last Name</td>
            <td>Age</td>
        </tr>
    </thead>
    <tbody>
        @foreach (var data in list)
        { 
        <tr>
            <td>@data.id</td>
            <td>@data.fname</td>
            <td>@data.Lname</td>
            <td>@data.age</td>
        </tr>
        }
    </tbody>
</table>
@code
{
    List<Jsontable> list = new List<Jsontable>();
    public async Task calldata()
    {
        var data = await p.getJsonFromDatabase();
        list = data.ToList();
    }

}

       
 

Dont Forget to add 2 services in startup.cs file

 services.AddSingleton<IConfiguration>(Configuration);

  services.AddScoped<IprocessJson, ProcessJson>();