Returning Data in JSON Format in MVC

In this blog I am describing how to return 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-

1. Employee class
2. Connection 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.