Thursday 22 March 2018

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;
}

No comments:

Post a Comment