Consume Json Web Service in ASP.Net using C# and VB.Net

Last Reply on Jun 15, 2017 04:45 AM By AnandM

Posted on Jun 14, 2017 03:07 AM

hi,

 

I want to make restful webservice which fetch data from sql server database and give out in json format.

 

Please share some snippet on internet it is very complex i believe you make it very easy to understand and useful thanks

You are viewing reply posted by: AnandM on Jun 15, 2017 04:45 AM.
Posted on Jun 15, 2017 04:45 AM Modified on on Jun 15, 2017 04:46 AM

Hi nauna,

I have created a sample which full fill your requirement you need to modify the code according to your need.

I have used Northwind Database Customers Table you can get Database from below link.

Install the Northwind and Pubs Sample Databases in SQL Server Express

Refer below Sample Code.

WebService.asmx

<%@ WebService Language="C#" CodeBehind="~/App_Code/WebService.cs" Class="WebService" %>

WebService.cs

using System.Collections.Generic;
using System.Web.Services;
using System.Data.SqlClient;
using System.Configuration;
using System.Web.Script.Serialization;

/// <summary>
/// Summary description for WebService
/// </summary>
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
// 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 WebService : System.Web.Services.WebService
{
    [WebMethod]
    public string GetCustomers()
    {
        List<object> customers = new List<object>();
        string sql = "SELECT TOP 10 CustomerId,ContactName,Country FROM Customers";
        using (SqlConnection conn = new SqlConnection())
        {
            conn.ConnectionString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
            using (SqlCommand cmd = new SqlCommand(sql))
            {
                cmd.Connection = conn;
                conn.Open();
                using (SqlDataReader sdr = cmd.ExecuteReader())
                {
                    while (sdr.Read())
                    {
                        customers.Add(new
                        {
                            CustomerId = sdr["CustomerId"],
                            ContactName = sdr["ContactName"],
                            Country = sdr["Country"]
                        });
                    }
                }
                conn.Close();
            }
            return (new JavaScriptSerializer().Serialize(customers));
        }
    }
}

HTML

<div>
    <table id="tblCustomers">
    </table>
</div>
<div>
    <script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
    <script type="text/javascript">
        $(function () {
            $.ajax({
                type: "POST",
                url: "WebService.asmx/GetCustomers",
                data: "",
                contentType: "application/json; charset=utf-8",
                dataType: "json",
                success: function (r) {                        
                    $('[id*=tblCustomers]').append("<tr><th>CustomerId</th><th>ContactName</th><th>Country</th></tr>");
                    $.each($.parseJSON(r.d), function (key, value) {
                        var customerId = value.CustomerId;
                        var contactName = value.ContactName;
                        var country = value.Country;
                        $('[id*=tblCustomers]').append("<tr><td>" + customerId + "</td><td>" + contactName + "</td><td>" + country + "</td></tr>");
                    });
                },
                error: function (r) {
                    alert(r.responseText);
                },
                failure: function (r) {
                    alert(r.responseText);
                }
            });

        });
    </script>
</div>

Output

CUSTOMERID ContactName Country
AAFKM Mudassar Khan Belgium
ALFKI Maria Austria
ANATR Ana Trujillo France
ANTON Antonio Moreno Brazil
AROUT Thomas Hardy Ireland
BERGS Christina Berglund Italy
BLAUS Hanna Moos Finland
BLONP Frédérique Citeaux Finland
BOLID Martín Sommer Argentina
BONAP Laurence Lebihan USA