Saturday, January 21, 2012

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.


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

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

2 comments:

Dorababu said...

Hi Can you attach the code in a zip file. I did as per you said but I am unable to get the records form DB

DotNetGuts said...

@Dorababu,
First run the very basic JQuery Ajax example without any parameter (Hello World Example), just to make sure that you are doing things correctly.


Few things you important to make things running on your machine
1) Please UnComment line
[System.Web.Script.Services.ScriptService]
In order to allow this Web Service to be called from script, using ASP.NET AJAX

2) Name of function in .aspx file and name of function in webservice file is exactly the same.
Considering above example: It should be "ReadRegion"

3) Make sure that parameter name in .aspx file and parameter name in webservice is exactly matching.
Considering above example: It should be "CountryName". Remember if you declare CountryName in .aspx file and strCountryName in webservice it won't work.

Hope that helps.

Most Recent Post

Subscribe Blog via Email

Enter your email address:



Disclaimers:We have tried hard to provide accurate information, as a user, you agree that you bear sole responsibility for your own decisions to use any programs, documents, source code, tips, articles or any other information provided on this Blog.
Page copy protected against web site content infringement by Copyscape