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

Last Reply one year ago By AnandM

Posted one year ago

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


Posted one year ago

hi,

 

I reviewed all the links it does not have what i asked for,

 

my webservice is ok i just need to know that how can i fetch data from sql server and return the values please see my question again thanks


Posted one year ago

http://www.c-sharpcorner.com/article/generate-json-data-using-web-service-and-sql-server-stored-procedure/

 

i want some thing like this but this tutorial is very complex and using store procedure i want sql statement in web service instead of store procedure


Posted one year ago Modified on one year ago

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