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.
  1. using System.Data.SqlClient;
  2. using MVCPROJECT.Models;
  3. namespace MVCPROJECT.Controllers
  4. {
  5. public class MembersController: Controller
  6. {
  7. List < Dictionary < string, object >> rows = new List < Dictionary < string, object >> (); //creating a list to hold the rows of datatable
  8. Dictionary < string, object > rowelement; //Initialise a dictionary because it will contain columnName and Column Value and the key is column Name
  9. [HttpGet
  10. public JsonResult Show()
  11. {
  12. Registration obj = new Registration(); //Model object is created
  13. DataTable dt = new DataTable();
  14. dt = obj.employeedetails(); //calling a method which declared in model to retrive all data from the table and store it in dt.
  15. if (dt.Rows.Count > 0) //if data is there in dt(dataTable)
  16. {
  17. foreach(DataRow dr in dt.Rows)
  18. {
  19. rowelement = new Dictionary < string, object > ();
  20. foreach(DataColumn col in dt.Columns)
  21. {
  22. rowelement.Add(col.ColumnName, dr[col]); //adding columnn
  23. }
  24. rows.Add(rowelement);
  25. }
  26. }
  27. return Json(rows, JsonRequestBehavior.AllowGet);
  28. }
  29. }
  30. }
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:
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Linq;
  4. using System.Web;
  5. using System.Data;
  6. using System.Data.SqlClient;
  7. using MVCPROJECT.Models;
  8. using Forms.Models;
  9. namespace MVCPROJECT.Models
  10. {
  11. public class Registration
  12. {
  13. Connection objConnection = new Connection();#region "Properties"
  14. SqlDataAdapter ds = new SqlDataAdapter();
  15. DataTable dt = new DataTable();
  16. public string Fname {get;set;}
  17. public string SName {get;set;}
  18. public string Lname {get;set;}
  19. public string Id {get;set;}
  20. public string EmailId {get;set;}
  21. public string Password {get;set;}
  22. #endregion
  23. public DataTable employeedetails()
  24. {
  25. DataTable dt = new DataTable();
  26. dt = objConnection.GetDataTable1("sp_selectemployee"); //GetDataTable1 is my method in connection.class
  27. return dt;
  28. }
  29. }
  30. }
And My Connection class look like this:
Connection.cs
  1. using System;
  2. using System.Collections;
  3. using System.Configuration;
  4. using System.Data;
  5. using System.Data.SqlClient;
  6. using Forms.Models;
  7. namespace Forms.Models
  8. {
  9. public class Connection
  10. {
  11. public SqlCommand cmdData = new SqlCommand();
  12. public SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings[1].ConnectionString);
  13. public SqlDataAdapter dataAdapter = new SqlDataAdapter();
  14. DataTable dt = new DataTable();
  15. private string connStr = ConfigurationManager.ConnectionStrings["connect"].ConnectionString;
  16. public void ExecuteCommand(string procName, CommandType cmdType, Hashtable parameters)
  17. {
  18. SqlConnection sqlConn = null;
  19. try
  20. {
  21. sqlConn = GetSqlConnection();
  22. if (sqlConn.State == ConnectionState.Closed)
  23. {
  24. sqlConn.Open();
  25. }
  26. SqlCommand sqlComm = GetSqlCommand(procName, cmdType, parameters, sqlConn);
  27. sqlComm.ExecuteNonQuery();
  28. }
  29. catch (Exception ex)
  30. {
  31. throw ex;
  32. }
  33. finally
  34. {
  35. sqlConn.Close();
  36. }
  37. }
  38. public DataTable GetDataTable1(string strProcName)
  39. {
  40. cmdData = new SqlCommand(strProcName);
  41. cmdData.CommandType = CommandType.StoredProcedure;
  42. cmdData.Connection = conn;
  43. dataAdapter = new SqlDataAdapter(cmdData);
  44. dt = new DataTable();
  45. dataAdapter.Fill(dt);
  46. return dt;
  47. }
  48. }
  49. }
And my stored procedure in database is:
  1. USE [MVC] GO
  2. /****** Object: StoredProcedure [dbo].[sp_selectemployee] Script Date: 05/02/2015 18:48:54 ******/
  3. SET
  4. ANSI_NULLS ON GO
  5. SET
  6. QUOTED_IDENTIFIER ON GO ALTER procedure [dbo].[sp_selectemployee] (
  7. @mode nvarchar(10)= null,
  8. @id nvarchar(10)= null
  9. ) as begin
  10. select
  11. *
  12. from
  13. 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.