Returning Data in JSON Format in MVC

Here i am describing how to return data in Json format in MVC.

JSON(JavaScript Object Notation) It is a lightweight data-interchange format used to transfer data between cross platform enviroment.

Here i am showing how we can return json format in MVC.

I have the following solution explorer.

As i marked here i have to work with "MembersController" class, with "Registration Modal" and "connection" class.

I have the following table "tbl_registration".

I need to transfer this data to Json through MVC.

First of all add the code in the "member" controller.

using System.Data.SqlClient;
using MVCPROJECT.Models;
namespace MVCPROJECT.Controllers
{
public class MembersController: Controller
{
List < Dictionary < string, object >> rows = new List < Dictionary < string, object >> (); //creating a list to hold the rows of datatable
Dictionary < string, object > rowelement; //Initialise a dictionary because it will contain columnName and Column Value and the key is column Name
[HttpGet
public JsonResult Show()
{
Registration obj = new Registration(); //Model object is created
DataTable dt = new DataTable();
dt = obj.employeedetails(); //calling a method which declared in model to retrive all data from the table and store it in dt.
if (dt.Rows.Count > 0) //if data is there in dt(dataTable)
{
foreach(DataRow dr in dt.Rows)
{
rowelement = new Dictionary < string, object > ();
foreach(DataColumn col in dt.Columns)
{
rowelement.Add(col.ColumnName, dr[col]); //adding columnn
}
rows.Add(rowelement);
}
}
return Json(rows, JsonRequestBehavior.AllowGet);
}
}
}

MODEL

In model i have 2 class-

In Employee class i have the database Properties with the employeedetail() method

The code is here:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using MVCPROJECT.Models;
using Forms.Models;
namespace MVCPROJECT.Models
{
public class Registration
{
Connection objConnection = new Connection();#region "Properties"
SqlDataAdapter ds = new SqlDataAdapter();
DataTable dt = new DataTable();
public string Fname {get;set;}
public string SName {get;set;}
public string Lname {get;set;}
public string Id {get;set;}
public string EmailId {get;set;}
public string Password {get;set;}
#endregion
public DataTable employeedetails()
{
DataTable dt = new DataTable();
dt = objConnection.GetDataTable1("sp_selectemployee"); //GetDataTable1 is my method in connection.class
return dt;
}
}
}

And My Connection class look like this:

Connection.cs

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using Forms.Models;
namespace Forms.Models
{
public class Connection
{
public SqlCommand cmdData = new SqlCommand();
public SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings[1].ConnectionString);
public SqlDataAdapter dataAdapter = new SqlDataAdapter();
DataTable dt = new DataTable();
private string connStr = ConfigurationManager.ConnectionStrings["connect"].ConnectionString;
public void ExecuteCommand(string procName, CommandType cmdType, Hashtable parameters)
{
SqlConnection sqlConn = null;
try
{
sqlConn = GetSqlConnection();
if (sqlConn.State == ConnectionState.Closed)
{
sqlConn.Open();
}
SqlCommand sqlComm = GetSqlCommand(procName, cmdType, parameters, sqlConn);
sqlComm.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
sqlConn.Close();
}
}
public DataTable GetDataTable1(string strProcName)
{
cmdData = new SqlCommand(strProcName);
cmdData.CommandType = CommandType.StoredProcedure;
cmdData.Connection = conn;
dataAdapter = new SqlDataAdapter(cmdData);
dt = new DataTable();
dataAdapter.Fill(dt);
return dt;
}
}
}

And my stored procedure in database is:

USE [MVC] GO
/****** Object: StoredProcedure [dbo].[sp_selectemployee] Script Date: 05/02/2015 18:48:54 ******/
SET
ANSI_NULLS ON GO
SET
QUOTED_IDENTIFIER ON GO ALTER procedure [dbo].[sp_selectemployee] (
@mode nvarchar(10)= null,
@id nvarchar(10)= null
) as begin
select
*
from
tbl_registration end

Now when we type the url in browser.

Members------>controller

Show------------>Action Method

Now if we want to check the this Json then type-http://json.parser.online.fr/ and paste the following json array.