Example 4: JQuery DB Call to Retrieve data based on user Criteria


Retrieve region based on country name

Enter Country Name:





Sourcode

    Default.aspx
 
<html>
<head>


<link href="~/Styles/Site.css" rel="stylesheet" type="text/css" />


<script type="text/javascript" language="Javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.7.2/jquery.min.js"></script>

</head>
<body>

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


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

</body>
</html>


Sourcode

    HelloWorld.asmx.cs
 
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;

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

namespace HelloWorldJQueryAjax.WebService
{
    /// <summary>
    /// Summary description for HelloWorld
    /// </summary>
    [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 HelloWorld : 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(); } } } } }
Share with your friends!!