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()
{
        $j.ajax({
            url: '/Student/GetStudentList/',
            dataType: "json",
            type: "GET",
            contentType: 'application/json; charset=utf-8',
            data: {  },
            success: function (data) {
                if (data != '') {
                    $("#tbl").empty();
                    var html = "";
                    $j.each(data, function (i, item) {
                        rec++;


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

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

Model


public class StudentModel
{
    public string Roll { get; set; }
    public string Name { get; set; }
    public string City { get; set; }
}

Controller


public ActionResult GetStudentList()

{
    Repository ob = new ReportRepository();
    List<StudentModel> list = new List<StudentModel>();
    list=ob.GetInvoiceList();
    return View(list);
}

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 drGetRequestedPin = cmd.ExecuteReader())
                {
                    if (drGetRequestedPin.HasRows)
                    {
                               
                        while (drGetRequestedPin.Read())
                        {
                            StudentModel data = new StudentModel();
                            data.Roll = drGetRequestedPin.IsDBNull(0) ? "0" : Convert.ToString(drGetRequestedPin[0]);
                            data.Name = drGetRequestedPin.IsDBNull(1) ? "-" : Convert.ToString(drGetRequestedPin[1]);
                            data.City = drGetRequestedPin.IsDBNull(2) ? "0" : Convert.ToString(drGetRequestedPin[2]);
                                  
                            Record.Add(data);
                        }
                    }
                }
            }
        }
    }                      
    catch (Exception ex)
    {

    }
    return Record;
}