Get Data from server Using ajax

Target Audience

Beginners who are learning C# MVC and want to get record from SQL server to browser using jquery  ajax.

Store Procedure

USE [Girfa_StudentDB]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

alter proc [dbo].[GetStudentList]

AS

BEGIN

     SET NOCOUNT ON;

           select roll,name,city from dbo.Student

     SET NOCOUNT OFF;

END

View

<input type="button" value="Get Data" onclick="GetData()"/>

<table>

    <thead>

        <tr><th>Roll</th><th>Name</th><th>City</th></tr>

    </thead>

    <tbody id="tbl">

    </tbody>

</table>

JS

function GetData()

{

        $.ajax({

            url: '/Student/GetStudentList/',

            dataType: "json",

            type: "GET",

            contentType: 'application/json; charset=utf-8',

            data: {  },

            success: function (data) {

                if (data != '') {

                    $("#tbl").empty();

                    var html = "";

                    $.each(data, function (i, item) {

                        rec++;

                        html += "<tr><td>" + item.Roll + "</td><td>" + item.Name + "</td><td>" + item.City + "</td></tr>";

                    })

                    $("#tbl").append(html);                   

                }               

            }

        });

   

}

Model

public class StudentModel

{

    public string Roll { get; set; }

    public string Name { get; set; }

    public string City { get; set; }

}

Controller

public JsonResult GetStudentList(string  id) 

{

   List<StudentModel> data = new List<StudentModel>();

   WebsiteRepository ob = new WebsiteRepository();

   data = ob. GetStudentList ();

   return Json(data, JsonRequestBehavior.AllowGet);           

}

For view

public ActionResult Dashboard()

{

   List<StudentModel> data = new List<StudentModel>();

   WebsiteRepository ob = new WebsiteRepository();

   data = ob. GetStudentList();

   return View(data);

 }

Repository

public List<StudentModel> GetStudentList(int roll)

{

    List<StudentModel> Record=new List<StudentModel>();

    try

    {

        using (SqlConnection dbcon = new SqlConnection(ConfigurationManager.ConnectionStrings["dbConnection"].ToString()))

        {

            using (SqlCommand cmd = new SqlCommand("[dbo].[GetStudentList]", dbcon))

            {

                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.AddWithValue("@roll", roll);

    

                if (dbcon.State == ConnectionState.Closed)

                    dbcon.Open();

                using (SqlDataReader drOb= cmd.ExecuteReader())

                {

                    if (drOb.HasRows)

                    {

                               

                        while (drOb.Read())

                        {

                            StudentModel data = new StudentModel();

                            data.Roll = drOb.IsDBNull(0) ? " " : Convert.ToString(drOb[0]);

                            data.Name = drOb.IsDBNull(1) ? " " : Convert.ToString(drOb[1]);

                            data.City = drOb.IsDBNull(2) ? " " : Convert.ToString(drOb[2]);

                                  

                            Record.Add(data);

                        }

                    }

                }

            }

        }

    }                      

    catch (Exception ex)

    {

    }

    return Record;

}

Next Topic