jQuery Ajax JSON example in Asp.net C# [ MS SQL]

Overview: Here in this article will see asp.net jQuery Ajax JSON example, i.e. calling jQuery Ajax WebMethod and get JSON format result (JSON data) in Asp.net C# Webform. In short a simple jQuery Ajax JSON example in Asp.net C# with MS SQL Server database.

If you are looking for how to get JSON formatted result from jquery ajax call in asp.net C#, then this post will explain step by step process. You can also check 3 way to convert DataTable into JSON String in Asp.net C#.

What is jQuery AJAX?

AJAX stands for Asynchronous JavaScript and XML and with using ajax we load data from the server without reloading the whole web page refresh. i.e we can update a specific part of our web page dynamically without page refresh. By using jQuery AJAX methods, we can request HTML, XML, or JSON from the server using both HTTP Get and HTTP Post request.

Am assuming you are aware of Asp.net C# Webservice, i.e., (ASMX file) Webservice.ASMX, WebMethods and familiar with jQuery Ajax syntax.

And it’s very useful if you want to save or insert data into the database without page postback or page refresh .i.e Insert data into database using jQuery Ajax.

In this tutorial, we make a jQuery Ajax call and in response will get the JSON data, i.e., here we get a list of all cars and other detail information from our database ( MS SQL server) via WebMethod (ASMX file).

Steps to create jQuery Ajax JSON example in Asp.net C#.

Download the jQuery library and add Html Markup.
Calling jQuery Ajax method.
C#: Create Class and WebMethod, which returns JSON Object.

Fig 1:

# Download the jQuery library and add Html Markup.

First, we open Visual Studio IDE, create a new project and then we added new Asp.net web page and named as myJsonTuts.aspx. On our Asp.net web page, we add a button control and import jQuery library.

You can download jQuery files from jQuery.com, or you can use Google hosted files. So now our HTML markup looks like as shown below.

<head>
 <script src="latestJs_1.11/jquery.min.js"></script>
</head>
<body>
<form runat="server">
<div>
  <select id="ddlSelectYear">
    <option>2014</option>
    <option>2015</option>
  </select> 
  <button id="myButton">Get Car Lists</button> 
  <div id="contentHolder"></div>
</div> 
</form> 
</body>

# Client-Side: Calling jQuery Ajax method.

Now we write client-side code .i.e. on button click we make a jQuery Ajax call with returns JSON data, check this for dynamic button click event in jQuery.

Here in our case, we fetch the list of cars along with other information from the database MS SQL server via C# WebMethod, which we create later on server-side.

Let’s first check how our Client side code looks as shown below

//*
$("#myButton").on("click", function (e) {
    e.preventDefault();
    var aData= [];
    aData[0] = $("#ddlSelectYear").val(); 
    $("#contentHolder").empty();
    var jsonData = JSON.stringify({ aData:aData});
    $.ajax({
        type: "POST",
        //getListOfCars is my webmethod   
        url: "WebService.asmx/getListOfCars", 
        data: jsonData,
        contentType: "application/json; charset=utf-8",
        dataType: "json", // dataType is json format
        success: OnSuccess,
        error: OnErrorCall
    });
   
    function OnSuccess(response) {
      console.log(response.d)
    }
    function OnErrorCall(response) { console.log(error); }
    });
//*

Here in above code on our button click, we make an ajax call.

You may notice we have set dataType=”json” that is because we want our ajax response in JSON format.

# Creating a class and WebMethod, which returns list collection as JSON data.

Now we add a Webservice file (ASMX file) in our project, here we added a file and named as myfunction.asmx. In our Webservice (.asmx file) we also created a class .i.e Cars which has property as carName, carRating, carYear.

Now we write a WebMethod as we want to get the list of all cars, here getListofCars is our WebMethod, which fire the select query to the database, pull the record from the database and add it to our class object Car.

In response, we return this Car class object.

//*
    //Created a class 
    public class Cars
    {
        public string carName;
        public string carRating;
        public string carYear;
    }
//*

Now we write our WebMethod, which returns List of Cars details.

//*
[WebMethod]
public List<Cars> getListOfCars(List<string> aData)
{
    SqlDataReader dr;
    List<Cars> carList = new List<Cars>();

    using (SqlConnection con = new SqlConnection(conn))
    {
        using (SqlCommand cmd = new SqlCommand())
        {
            cmd.CommandText = "spGetCars";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = con;
            cmd.Parameters.AddWithValue("@makeYear", aData[0]);
            con.Open();
            dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            if (dr.HasRows)
            {
                while (dr.Read())
                {
                    string carname = dr["carName"].ToString();
                    string carrating = dr["carRating"].ToString();
                    string makingyear = dr["carYear"].ToString();

                    carList.Add(new Cars
                                    {
                                        carName = carname,
                                        carRating = carrating,
                                        carYear = makingyear
                                    });
                }
            }
        }
    }
    return carList;
}
//*

In response, we will get data in JSON format, check this in your browser console, JSON format list of cars is available as shown above in Fig 1.


function OnSuccess(response.d)) {
   console.log(response.d)
}

Note: Always use response.d to get the jquery ajax response in Asp.net C#.

Now we write some more code on our client-side .i.e how to display JSON data list of the car into our Web page.

In short, we have to write some jQuery code to handle the response `JSON Data`. So now our success function looks like as shown below.

//*
function OnSuccess(response) {
    var items = response.d;
    var fragment="<ul>"
    $.each(items, function (index, val) {
                                            
        var carName = val.carName;
        var carRating = val.carRating;
        var carYear = val.carYear;
        fragment += "<li> "+carName+" :: "+carRating+" - "+carYear+"</li>";
    });
    $("#contentHolder").append(fragment);
}
//*

Here we stored the response in variable item and then using the jQuery $.each() method we make a loop over data, get and set the value to ‘<li>’ tag and append to parent `<UL>`

# OutPut: Asp.net ajax JSON example:

Finally, our Output which displays the list of car and other information.

Output of jQuery Ajax JSON Example in Asp.net C# with MS SQL server database

Output of jQuery Ajax in Asp.net C#

Conclusion: In this article, we learn how to make a jquery ajax call in Asp.net C# .ie using WebMethod ( .asmx file) and returns JSON format data. Also, we display this JSON data into our HTML .i.e onSuccess method we create a List of Cars with other information ( Li tag) using JSON data.

You must also check these articles:

Other Reference:

jQuery.com (Ajax)

Hope you enjoyed this tutorial. If you have any recommendations, please let us know what you think in the comment section below! See you again next time!

20 comments on “Asp.net: jQuery Ajax JSON Example, C# WebMethod with SQL Database”

zabeel khan June 27, 2017 at 3:07 pm

thank u sir

Black_Giser February 23, 2017 at 11:31 am

it happened that System.IndexOutOfRangeException: index overranging the limitation.when using the webservice.Please help me.Thanks.

- Satinder singh April 6, 2017 at 3:34 pm
  
  Refer this StackOverflow Link
  
Othmane February 3, 2017 at 5:13 pm

Hello,
Keep smiling 🙂
thank you

EvaTomlison August 13, 2016 at 7:55 am

I need some help, please!
I do not understand something..
HTML markup and jQuery Ajax method should be in the same file: Default.aspx
Defining a class Cars and [WebMethod] sholud be in the same file: myfunction.asmx (or maybe myfunction.asmx.cs?!)
And then, the “function OnSuccess(response)” should be where?

I’m sorry, I know it is a stupid question, but I’m very new to this and I really need some help

- Satinder singh September 16, 2016 at 7:10 am
  Hi EvaTomlison,
  Here we use two files .i.e (Default.aspx and myFunction.asmx)
  
  Default.aspx : Here we put out HTML markup and jquery ajax code with success and error method.
  
  myFunction.asmx.cs : Here we write the WebMethod and the Class.
  
  Hope now you got a clear idea about using jquery ajax with JSON data in asp.net
Ben August 10, 2016 at 1:31 am

Excellent, this helped me a lot.

- Satinder singh August 10, 2016 at 6:01 am
  
  Thanks Ben,
  Am glad you find it useful.
  
kamlesh July 12, 2016 at 10:58 am

need help ,how write a jquery function to display the name list from a customer database table as a help list as soon as the textbox get focus in the web form (asp.net with c#) , and second thing is the selected data from the help list should be displayed on the textbox also

- Satinder singh July 12, 2016 at 11:14 am
  
  Hi Kamlesh,
  You need to say you want to use jquery ajax based Autocomplete in your web from, then pls refer http://www.dotnetcurry.com/ShowArticle.aspx?ID=515
  
Gerardo March 30, 2016 at 2:05 pm

help please!!

error 500 (Internal Server Error)

Gerardo March 30, 2016 at 2:03 pm

help please!

error 500 (Internal Server Error)

- Satinder singh March 30, 2016 at 4:45 pm
  
  Hi Gerardo,
  
  Error 500 (Internal Server Error) mostly occurs if your Webmethod URL is wrong, Pls enter jQuery ajax method URL property with valid path.
  
Jhon January 20, 2016 at 8:47 am

I have this problem: http://stackoverflow.com/questions/34851481/how-to-change-file-to-retrieve-data-from-json . Could you please give me a hint how I may solve it? Many thanks in advance!

Marcel Girgis January 5, 2016 at 4:07 pm

What does the mark-up for “contentHolder” look like?

- Satinder singh February 11, 2016 at 11:51 am
  
  Hi Marcel,
  Nice observation, have updated the post.
  Here “contentHolder” is nothing but the ID of a DIV tag, which later use to bind HTML fragment inside it.
  
SJ December 15, 2015 at 4:06 pm

Hi,

I tried creating a new database with database fields, carname, carrating, and caryear. But I cant seem to query the fields in the codes above. May in know more about your connection string to query from the database and also your stored procedure, ‘spGetCars’.

Thanks

- Satinder singh December 17, 2015 at 5:31 am
  Though it’s basic, but sure will update this in the post.
  Ok first you need to add your connection string into your Web.config
  
  <add name="constr" providerName="System.Data.SqlClient" connectionString= "server=sys009;database=myDatabaseName;uid=sa;pwd=myPassword" />
  
  Secondly create a connection into your webpage so you can use in your Webmethod/functions
  
  SqlConnection conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["constr"].ToString());
  
  And about query it’s simple SELECT * FROM CAR
  so Storedprocedure would be like
  
  CREATE PROC spGetCars( @makeYear int ) BEGIN AS SELECT * FROM CAR WHERE [email protected] END
nacho July 2, 2015 at 8:44 pm

Hi, i’m having this error:
Synchronous XMLHttpRequest on the main thread is deprecated because of its detrimental effects to the end user’s experience. For more help, check http://xhr.spec.whatwg.org/.
browserLink:37 Setting ‘XMLHttpRequest.withCredentials’ for synchronous requests is deprecated.

what am i doing wrong?.

Thanks

- Satinder singh July 3, 2015 at 5:26 am
  
  To avoid this warning, do not use `async: true` in any of your `$.ajax()` calls. This is the only feature of XMLHttpRequest that’s deprecated.
  You can resolve this by adding `async: true,` in your ‘$.ajax()` call

Asp.net: jQuery Ajax JSON Example, C# WebMethod with SQL Database