create table jsontable
(
id int identity(1,1),
fname nvarchar(100),
Lname nvarchar(100),
age int
)
CREATE proc getjsondata
as
select * from jsontable
for json path
FrontEnd Use: Blazor Server APP
DataBase: Microsoft SQL Server
ORM: ADO.NET
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();
}
}
0 Comments
thank you for your comment