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

Hi nauna,

Refer below articles

1. Perform HTTP POST to REST WCF Service (SVC) in ASP.Net using C# and VB.Net
2. Call (Consume) REST WCF Service (SVC) using HttpWebRequest in ASP.Net with C# and VB.Net
3. Call (Consume) REST WCF Service (SVC) using WebClient in ASP.Net with C# and VB.Net
4. Call (Consume) WCF Service (SVC) using AJAX in ASP.Net

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

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

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