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


Posted on Jun 14, 2017 03:24 AM

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 on Jun 14, 2017 04:35 AM

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