How To Create JSON From Database In C#

In this article, you will get the following questions answered.

• What is JSON?
• What is serialization and deserialization of JSON data?
• How many preferred types of JSON data are there?
• Step by step JSON creation.

What is JSON?

JSON's full form is JavaScript Object Notation.

JavaScript Object Notation (JSON) is a lightweight data-interchange format. JSON creation and read of JSON string is very easy. In Web technology, nowadays people transfer and receive the data in this format. Even MONGO DB stores the data in JSON format.

What is Serialization and Deserialization of JSON data?

The creation of JSON data from an object is serialization, and deserialization is its inverse operation.

How many preferred types of JSON data are there?

Preferred types of JSON Data are as follows,

Object

In this, the data is stored in KEY/VALUE pattern. Object starts with { and end with }. It is very easy to read and understand the object.

As you see in the example section, Key “friendid” and value “1”, which is separated by : colon sign.

{"friendid":"1","friendname":"Ashish Kalla","friendplace":"Malad","friendmobile":"777777777"}   

Array

Array starts with “[“ and end with “]”and the rest of the operation is similar to the object pattern. JSON array is mostly the same as a general list kind of array, which we use in .NET. In addition to it in Javascript, you can plugin function to process something.

Var friendlist=[{"friendlist":{"friendid":"2","friendname":"Ashish Kalla","friendplace":"Malad","friendmobile":"777777777"}},  
  
{"friendlist":{"friendid":"3","friendname":"Suhana Kalla","friendplace":"Phalodi","friendmobile":"666666666"}}]   

Step by step JSON creation 

JSON is used primarily to transmit the data between a Server and Web Application. Basically in .NET, we can send and receive JSON data. In my major projects, I transfer/give JSON. We give/ send/ transfer JSON data through Web Service, AJAX jQuery calls and other ways.

Here, we generate JSON data in C# code at the back-end and return the string. You can return JSON in object mode (single record) or an array mode (list of records).

In this walk through, we create JSON data and write on the Browser screen, using Response.Write().

Step 1

Create ASP.NET Empty Web Site project named “JsonData”.

 

Step 2 

Create a table and code the sample data.

Table structure

USE [MBKTest]  
GO  
  
SET ANSI_NULLS ON  
GO  
  
SET QUOTED_IDENTIFIER ON  
GO  
  
CREATE TABLE [dbo].[tblMyFriends](  
    [FriendID] [int] IDENTITY(1,1) NOT NULL,  
    [FriendName] [nvarchar](50) NULL,  
    [FriendPlace] [nvarchar](50) NULL,  
    [FriendMobile] [nvarchar](10) NULL  
) ON [PRIMARY]  
  
GO 

Sample data

Step 3

Right click on the project and select Add-->Add New Item and select WebForm

Add a new Web Form named “Default.aspx”, in this page accepting user Id and password as well as creating cookies.

Step 4

Right click on the project and select Add-->Add New Item and select [Linq To SQL Class].

Select LINQ to SQL Class named “FriendListDataClass.dbml”

As you click on ADD button in the screen given above, you will see the dialog box.

Simply press YES.

Now, switch to Server Explorer and select Data Connections.

Step 5 - Connection establishment

Right click on Data Connections and select ADD CONNECTION option.

Double click your DBML file, which is located inside APP_CODE folder.

Drag and drop the table named “ tblMyFriends” on DBML canvas.

Understanding code for JSON creation

To achieve the task, we require the namespace given below.

System.IO;  
System.Runtime.Serialization.Json;  
  
//this line very important which make ready program to make JSON  
//GetType giving idea about you are going to create json for "System.Collections.Generic.List`1[tblMyFriend]"  
DataContractJsonSerializer ser = new DataContractJsonSerializer(FriendList.GetType());  
          
//Writing the JSON  
ser.WriteObject(str, FriendList);   

CODE Default.aspx.cs 

using System;  
using System.Collections.Generic;  
using System.IO;  
using System.Linq;  
using System.Runtime.Serialization.Json;  
using System.Web;  
using System.Web.UI;  
using System.Web.UI.WebControls;  
  
public partial class _Default : System.Web.UI.Page  
{  
    protected void Page_Load(object sender, EventArgs e)  
    {  
        string JsonStr = null;  
       
        FriendListDataClassDataContext db = new FriendListDataClassDataContext();  
        var FriendList = db.tblMyFriends.ToList();  
  
        MemoryStream str = new MemoryStream();  
  
        //this line very important which make ready program to make JSON  
        //GetType giving idea about you are going to create json for "System.Collections.Generic.List`1[tblMyFriend]"  
        DataContractJsonSerializer ser = new DataContractJsonSerializer(FriendList.GetType());  
          
        //Writing the JSON  
        ser.WriteObject(str, FriendList);  
  
        str.Position = 0;  
        StreamReader sr = new StreamReader(str);  
        JsonStr = sr.ReadToEnd();  
  
        //Writing the JSON on browswer   
        Response.Write(JsonStr);  
          
    }  
}

OUTPUT - JSON DATA

[{"FriendID":1,"FriendMobile":"999999786","FriendName":"Shree Sai","FriendPlace":"Shirdi"},
{"FriendID":2,"FriendMobile":"777777777","FriendName":"Ashish Kalla","FriendPlace":"Malad"},
{"FriendID":3,"FriendMobile":"666666666","FriendName":"Suhana Kalla","FriendPlace":"Phalodi"},
{"FriendID":4,"FriendMobile":"555555555","FriendName":"Aakash Kalla","FriendPlace":"Malar"},
{"FriendID":5,"FriendMobile":"111111111","FriendName":"Mahesh Sir","FriendPlace":"US"},
{"FriendID":6,"FriendMobile":"222222222","FriendName":"Praveen Sir","FriendPlace":"Singapore"},
{"FriendID":7,"FriendMobile":"333333333","FriendName":"Dinesh Sir","FriendPlace":"Dubai"}]