Header Ads Widget

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
select * from jsontable
for json path

FrontEnd Use: Blazor Server APP

DataBase: Microsoft SQL Server


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 



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();
                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


        #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;
<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">
            <td>First Name</td>
            <td>Last Name</td>
        @foreach (var data in list)
    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.AddScoped<IprocessJson, ProcessJson>();

Post a Comment