JQuery Ajax DB Call to Retrieve data based on user Criteria

In this article, i will be retrieving data from database with asp.net web service using jQuery Ajax.

If you are new to jQuery and don't know anything about it, I will recommend you to first read following articles before reading any further.

• JQuery Basics
• Play with JQuery Live Example and make yourself comfortable
• JQuery Ajax Simple Example

In this article we will take input country name and display region information. Check out Live Demo

Step 1: Create Asp.net Web Application

Step 2: Open Site.Master and include jQuery Reference by adding below line just before </head> tag.
<!--Include JQuery File-->
<script type="text/javascript" language="Javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.6.2/jquery.min.js"></script>




Step 3: Open "Default.aspx" and add following code
I have added here h2 title tag, asp.net button, textbox and label.  Please note, that i have declared ShowRegionInfo function, I will be calling javascript this function, which will internally make jQuery Ajax call to asp.net web service.

<h2>Example 4: JQuery DB Call to Retrieve data based on user Criteria </h2>
<br />
<b>Retrieve region based on country name</b><br /><br />
Enter Country Name: <asp:TextBox ID="txtCountryName" runat="server" Text=""></asp:TextBox><br />
<asp:Button ID="btnGetMsg" runat="server" Text="Click Me" OnClientClick="ShowRegionsInfo();return false;" /><br />
<asp:Label ID="lblOutput" runat="server" Text=""></asp:Label>

Step 4: Add new folder to solution named "WebService"

Step 5: Right click on "WebService" folder and click add new item, to add new web service.

Step 6: Add code to make database call to retrieve region data using jQuery Ajax.  Please add following code inside "wsJQueryDBCall.asmx.cs" file
Add following namespaces

using System.Data.SqlClient;
using System.Configuration;
using System.Data;
using System.Text;


Add following code



[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[System.ComponentModel.ToolboxItem(false)]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
[System.Web.Script.Services.ScriptService]
public class wsJQueryDBCall : System.Web.Services.WebService
{


    [WebMethod]
    public string ReadRegion(string CountryName)
    {
        String strConnString = ConfigurationManager.AppSettings["connStr"].ToString();


        String strQuery = "Select Region " +
                            "from Regions " +
                            "Inner Join CountriesNew on CountriesNew.CountryId = Regions.CountryId " +
                            "where UPPER(CountriesNew.Country) = @CountryName";


        using (SqlConnection con = new SqlConnection(strConnString))
        {
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.AddWithValue("@CountryName", CountryName.ToUpper());
                cmd.CommandText = strQuery;
                cmd.Connection = con;
                con.Open();
                SqlDataReader sdr = cmd.ExecuteReader();
                StringBuilder sb = new StringBuilder();


                if (sdr.HasRows)
                {
                    sb.Append("" + CountryName + " has following regions:
");


                    while (sdr.Read())
                    {
                        sb.Append(sdr["Region"].ToString() + "
");
                    }
                }
                else
                {
                    sb.Append("No Records found");
                }


                con.Close();


                return sb.ToString();
            }
        }
    }
}

As you have noticed ReadRegion Method is regular method which takes input country name and makes database call to retrieve region data from sql server database and return result as appended string of region.


Important Note:  Please UnComment line
[System.Web.Script.Services.ScriptService]
In order to allow this Web Service to be called from script, using ASP.NET AJAX



Step 7:  Open "Default.aspx" and Write jQuery function to make webservice call to retrieve data from database.



<script type = "text/javascript">
    function ShowRegionsInfo() {


        var pageUrl = '<%=ResolveUrl("~/WebService/wsJQueryDBCall.asmx")%>'
            
        $.ajax({
            type: "POST",
            url: pageUrl + "/ReadRegion",
            data: "{'CountryName':'" + $('#<%=txtCountryName.ClientID%>').val() + "'}",
            contentType: "application/json; charset=utf-8",
            dataType: "json",
            success: OnSuccessCall,
            error: OnErrorCall
        });


    }


    function OnSuccessCall(response) {
        $('#<%=lblOutput.ClientID%>').html(response.d);
    }


    function OnErrorCall(response) {
        alert(response.status + " " + response.statusText);
    }
</script>

Understanding jQuery Ajax call parameter

• http://dotnetguts.blogspot.com/2012/01/jquery-ajax-examples-by-calling-aspnet.html

Now, run the web application and input your country name and hit button to see region which is retrieved from sql server database.

Check out Live Demo of Calling Webservice using JQuery Ajax Example

More JQuery Tutorials