Generate JSON Data Using Web Service And SQL Server Stored Procedure

Satyaprakash Samantaray

9y163.1k19100Article

Let’s know about Web Service

Web Services are used for enabling an application to invoke a method of another application. These applications can be on the same computer or different computers. Web Services use protocols like HTTP, XML, and SOAP. Since these are open and well known protocols, the applications built on any platform can interoperate with Web Services. For example, a PHP application can interoperate with a Web Service built using .NET. Similarly, a web service built using different platform can be consumed by a .NET application.

Hyper Text Transfer Protocol (HTTP) is the protocol widely used by web services to send and receive messages. The messaging protocol is SOAP (Simple Object Access Protocol). SOAP messages are in XML format. Web Services have .asmx extension. For this reason, the web services are also often called as ASMX web services.

To know more about SOAP request and response, visit the below reference site.

https://www.w3.org/2003/06/soap11-soap12.html

A web service is a web-based functionality accessed using the protocols of the web to be used by the web applications. There are three aspects of web service development,

Creating the web service
Creating a proxy
Consuming the web service

Introduction to proxy in ASP.NET Web Service

A proxy is a major part for the web service codes. Before using the web service, a proxy must be created. The proxy is registered with the client application. Then, the client application makes call to the web service as if it were using a local method. The proxy takes the calls, wraps it in proper format, and sends it as a SOAP request to the Server. SOAP protocol is then used for exchanging web service data.

When the Server returns the SOAP package to the client, the proxy decodes everything and presents it to the client application.

Relationship between Visual Studio and Proxy

Visual Studio generates a proxy class using the Web Service Description Language (WSDL) document of the web service. The Web Service Description Language document formally defines a web service. It includes,

All the methods that are exposed by the web service
The parameters and their types
The return types of the methods

This information is then used by VS to create the proxy class. The client application calls the proxy class method. The proxy class will then serialize the parameters, prepare a SOAP request message, and send it to the web service. The web service executes the method and returns a SOAP response message to the proxy. The proxy class will then deserialize the SOAP response message and hands it to the client application. We don't have to serialize or deserialize .NET CLR objects to and from SOAP format. The proxy class takes care of serialization and deserialization and makes the life of a developer much easier.

Top Important Notes While Write Code For Web Services:

To use asp.net session object in a web service, the web service class must inherit from System.Web.Services.WebService class and EnableSession property of WebMethod attribute must be set to true. Hyper Text Transfer Protocol (HTTP) is the protocol widely used by web services to send and receive messages .The messaging protocol is SOAP. SOAP stands for Simple Object Access Protocol. SOAP messages are in XML format.
Notice that a web service is a class that is decorated with [WebService] attribute and inherits from System.Web.Services.WebService base class. The [WebService] attribute tells that this class contains the code for a web service. Web Service Namespace is used to uniquely identify your web service on the internet from other services that are already there on the Web. Web Service Namespace can be any string, but it is common to give it a company's internet domain name as they are usually unique.

To allow a web service to be called from Javascript , using ASP.NET AJAX, then decorate the web service class with [System.Web.Script.Services.ScriptService] attribute.
Web Method attribute used to specify description for the web service method.
BufferResponse is a boolean property. Default is true. When this property is true, the response of the XML Web service method is not returned to the client until either the response is completely serialized or the buffer is full. On the other hand, when this property is false, the response of the XML Web service method is returned to the client as it is being serialized.

In general, set BufferResponse to false, only when the XML Web service method returns large amounts of data.
CacheDuration is the property, if you want to cache the results of a web service method. This is an integer property, and specifies the number of seconds that the response should be cached. The response is cached for each unique parameter.
Web Methods in a web service can also be overloaded based on the number of parameters. Method overloading possible in web services by using MessageName property of WebMethod attribute. MessageName property is used to uniquely identify the individual XML Web service methods.
Call the web service using ASP.NET AJAX, which allows partial page post back. With partial page post back, only specific portion of the page is updated without reloading the entire page.
For smaller amounts of data, web service performance is better when BufferResponse is set to true.
In Web.Config file When allowCookies attribute is set to true, the client application accepts the cookie returned from the ASMX web service, and copies it into all future requests that are made to the web service. This ensures that the same session is maintained between the client and the web service.
To use asp.net session object in a web service, the web service class must inherit from System.Web.Services.WebService class and EnableSession property of WebMethod attribute must be set to true.

Steps to Create a Simple Application To Understand Better

Step1

Create one table named “tblEmployees”.

Script

Create table tblEmployees
(
Id int primary key identity,
Name nvarchar(50),
Gender nvarchar(10),
Salary int
)
Go

Enter some Dummy Records into this table.

Script

Insert into tblEmployees values ('Satya1', 'Male', 55000)
Insert into tblEmployees values ('Satya2', 'Female', 68000)
Insert into tblEmployees values ('Satya3', 'Male', 57000)
Insert into tblEmployees values ('Satya4', 'Female', 53000)
Insert into tblEmployees values ('Satya5', 'Male', 60000)
Insert into tblEmployees values ('Satya6', 'Male', 60000)
Insert into tblEmployees values ('Satya7', 'Male', 60000)
Go

Step2

Create one Stored Procedure named “AngularDB”.

Script

Create Procedure AngularDB
As
Begin
select * from tblEmployees
End

Then, execute this Stored Procedure to check the records.

exec AngularDB

SQL Server

Step3

Then, create an ASP.NET application named “WebService”.

Step4

Then, add connection string in Web.Config file.

Code Ref

<?xml version="1.0" encoding="utf-8"?>
<!--
For more information on how to configure your ASP.NET application, please visit
http://go.microsoft.com/fwlink/?LinkId=169433
-->

<configuration>
<connectionStrings>
<add name="MyDB" providerName="System.Data.SqlClient" connectionString="Add Your Connection string here"/>
</connectionStrings>
<system.web>
<webServices>
<protocols>
<add name="HttpGet"/>
</protocols>
</webServices>
<compilation debug="true" targetFramework="4.0" />
</system.web>
</configuration>

Code Description

Here, I have mentioned the name of the connection string as “MyDB”.

<connectionStrings>
<add name="MyDB" providerName="System.Data.SqlClient" connectionString="Add Your Connection string here"/>
</connectionStrings>

Created ASMX web service that can perform a HTTP GET request.

<webServices>
<protocols>
<add name="HttpGet"/>
</protocols>
</webServices>

Enable an ASP.NET Web Service for HTTP POST and GET requests.

<system.web>
<webServices>
<protocols>
<add name="HttpGet"/>
<add name="HttpPost"/>
</protocols>
</webServices>
</system.web>

Step5

Then, create a class file named “Employee.cs”.

Code Ref

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
namespace WebService
{
public class Employee
{
public int id { get; set; }
public string name { get; set; }
public string gender { get; set; }
public int salary { get; set; }
}
}

Code Description

I have declared 4 entities using get and set properties inside Employee class.These 4 entities should be same as Columns inside the earlier mentioned table “tblEmployees”.

public int id { get; set; }
public string name { get; set; }
public string gender { get; set; }
public int salary { get; set; }

SQL Server

Step6

Add a new Web Service i.e. ASMX file . Name it as EmployeeService.asmx.

SQL Server

Code Ref -

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Web.Script.Serialization;
using System.Web.Services;
namespace WebService
{
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[System.ComponentModel.ToolboxItem(false)]
[System.Web.Script.Services.ScriptService]
public class EmployeeService : System.Web.Services.WebService
{
[WebMethod]
public void GetAllEmployees()
{
List<Employee> listEmployees = new List<Employee>();
string cs = ConfigurationManager.ConnectionStrings["MyDB"].ConnectionString;
using (SqlConnection con = new SqlConnection(cs))
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "AngularDB";
con.Open();
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Employee employee = new Employee();
employee.id = Convert.ToInt32(rdr["Id"]);
employee.name = rdr["Name"].ToString();
employee.gender = rdr["Gender"].ToString();
employee.salary = Convert.ToInt32(rdr["Salary"]);
listEmployees.Add(employee);
}
}
JavaScriptSerializer js = new JavaScriptSerializer();
Context.Response.Write(js.Serialize(listEmployees));
}
}
}

Code Description

This namespace is very much required for this web service related code.

using System.Web.Services;

I have created one web method named “GetAllEmployees()”.

public void GetAllEmployees()

I have used one strongly typed List of objects using Employee.cs class to manipulate , search and sort the lists.

List<Employee> listEmployees = new List<Employee>();

I have used my connection string name here.

string cs = ConfigurationManager.ConnectionStrings["MyDB"].ConnectionString;

Then, all ADO.NET objects and properties are mentioned with their vast roles like SqlConnection , SqlCommand , SqlDataReader etc.

string cs = ConfigurationManager.ConnectionStrings["MyDB"].ConnectionString;
using (SqlConnection con = new SqlConnection(cs))
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "AngularDB";
con.Open();
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Employee employee = new Employee();
employee.id = Convert.ToInt32(rdr["Id"]);
employee.name = rdr["Name"].ToString();
employee.gender = rdr["Gender"].ToString();
employee.salary = Convert.ToInt32(rdr["Salary"]);
listEmployees.Add(employee);
}
}

I have put my Stored Procedure “AngularDB” here.

cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "AngularDB";

Mention all the fields using object of Employee class.

Employee employee = new Employee();
employee.id = Convert.ToInt32(rdr["Id"]);
employee.name = rdr["Name"].ToString();
employee.gender = rdr["Gender"].ToString();
employee.salary = Convert.ToInt32(rdr["Salary"]);

Then, add all field values of Employee object “employee” to List class object “listEmployees”.

listEmployees.Add(employee);

Provide the serialization and deserialization functionality of AJAX-enabled application.

JavaScriptSerializer js = new JavaScriptSerializer();

Then, convert the listEmployees object values into JSON string.

Context.Response.Write(js.Serialize(listEmployees));

WebService Namespace can be any string, but it is common to give it a company's internet domain name as they are usually unique. Something like

[WebService(Namespace = "http://tempuri.org/")]

If you want the method exposed as part of the Web service, then the method must be public and should be decorated with [WebMethod] attribute. This attribute has got several properties which can be used to configure the behavior of the XML Web service method.

SQL Server

Step7

Check for Asp.Net web service Using Stored Procedure Works ?

Right Click On “EmployeeService.asmx” , Then View In Browser.

SQL Server

The below link is the Web Service URL :

http://localhost:52057/EmployeeService.asmx

Then Click On GetAllEmployees Link.

SQL Server

Then, to test the data whatever inserted in the above mentioned table, click on "Invoke" button.

Also, to test the operation using the HTTP POST protocol, click the 'Invoke' button.

SQL Server

Then your URL path is changed.

http://localhost:52057/EmployeeService.asmx/GetAllEmployees

TABLE’S DATAS IN JSON FORMAT

[{"id":1,"name":"Satya1","gender":"Male","salary":55000},{"id":2,"name":"Satya2","gender":"Female","salary":68000},{"id":3,"name":"Satya3","gender":"Male","salary":57000},{"id":4,"name":"Satya4","gender":"Female","salary":53000},{"id":5,"name":"Satya5","gender":"Male","salary":60000},{"id":6,"name":"Satya6","gender":"Male","salary":60000},{"id":7,"name":"Satya7","gender":"Male","salary":60000},{"id":8,"name":"Satya8","gender":"Male","salary":60000},{"id":9,"name":"Satya8","gender":"Male","salary":60000},{"id":10,"name":"Satya9","gender":"Female","salary":60000},{"id":11,"name":"Satya10","gender":"Male","salary":60000}]

OUTPUT

You can see all data in JSON format.

SQL Server

Important Notes

This web service retrieves the data from SQL Server database table, and returns it in JSON format.

19 Comments

Join the conversation! Your thoughts help the community grow.

Manoj vaish6y ago
I want to call web services within stored procedure and that web service will return json data and store into the table. PLease help
chetan rajput7y ago
Thank you so much , and if I want to give one web service result or output is give to the another sql satement as input how to do
Yasir Raza8y ago
I got this error
- Satyaprakash Samantaray8y ago
  Create a sp named AngularDB . Then try other steps....
Yasir Raza8y ago
System.Data.SqlClient.SqlException: Could not find stored procedure 'AngularDB'. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader() at WebS.EmployeeService.GetAllEmployees() in f:\WPF\WebS\WebS\EmployeeService.asmx.cs:line 29
Gowtham Allaka8y ago
{ country : "india", state : [ "ap", "telangana","tamilnadu"]}, { country : "srilanka",state:["aaa","bbb","ccc"]
Gowtham Allaka8y ago
good morning sir, i want json format like nested json ....country and nested states ...
Juan Samano8y ago
Excellent! Thank you.
- Satyaprakash Samantaray8y ago
  Thanks for feedback
Ilham Niya8y ago
Mailon munansangu ,i tried with both web service and jdbc methods and i found the web service that works well
Ilham Niya8y ago
I have already found the solution thank you very much Satyaprakash Samantaray
- Satyaprakash Samantaray8y ago
  Thanks for reading my article and if you have any doubt regarding technical issue you can ask me
mailon munansangu8y ago
If I want to access the web service data in android app, will I need special permission on the sever? Plus which is better web service or jdbc to connect an android app
Ilham Niya8y ago
Thank you very much .it's very usefull .please i need the same thing but with geometry data .and i can't convert the geometry in my .asmx .Can you help me.
- Satyaprakash Samantaray8y ago
  Post in csharp corner forum with details and I will check..
Zeeshan Azim9y ago
Thanks bro. It works
Thiruppathi R9y ago
Nice Article.Thanks to sharing..
Saravanakumar Sekaran9y ago
Wow very nice article
- Satyaprakash Samantaray9y ago
  Thanks for valuable feedback

Generate JSON Data Using Web Service And SQL Server Stored Procedure

Satyaprakash Samantaray

9y163.1k19100Article

Let’s know about Web Service

To know more about SOAP request and response, visit the below reference site.

https://www.w3.org/2003/06/soap11-soap12.html

A web service is a web-based functionality accessed using the protocols of the web to be used by the web applications. There are three aspects of web service development,

Creating the web service
Creating a proxy
Consuming the web service

Introduction to proxy in ASP.NET Web Service

When the Server returns the SOAP package to the client, the proxy decodes everything and presents it to the client application.

Relationship between Visual Studio and Proxy

All the methods that are exposed by the web service
The parameters and their types
The return types of the methods

Top Important Notes While Write Code For Web Services:

To use asp.net session object in a web service, the web service class must inherit from System.Web.Services.WebService class and EnableSession property of WebMethod attribute must be set to true. Hyper Text Transfer Protocol (HTTP) is the protocol widely used by web services to send and receive messages .The messaging protocol is SOAP. SOAP stands for Simple Object Access Protocol. SOAP messages are in XML format.
Notice that a web service is a class that is decorated with [WebService] attribute and inherits from System.Web.Services.WebService base class. The [WebService] attribute tells that this class contains the code for a web service. Web Service Namespace is used to uniquely identify your web service on the internet from other services that are already there on the Web. Web Service Namespace can be any string, but it is common to give it a company's internet domain name as they are usually unique.

To allow a web service to be called from Javascript , using ASP.NET AJAX, then decorate the web service class with [System.Web.Script.Services.ScriptService] attribute.
Web Method attribute used to specify description for the web service method.
BufferResponse is a boolean property. Default is true. When this property is true, the response of the XML Web service method is not returned to the client until either the response is completely serialized or the buffer is full. On the other hand, when this property is false, the response of the XML Web service method is returned to the client as it is being serialized.

In general, set BufferResponse to false, only when the XML Web service method returns large amounts of data.
CacheDuration is the property, if you want to cache the results of a web service method. This is an integer property, and specifies the number of seconds that the response should be cached. The response is cached for each unique parameter.
Web Methods in a web service can also be overloaded based on the number of parameters. Method overloading possible in web services by using MessageName property of WebMethod attribute. MessageName property is used to uniquely identify the individual XML Web service methods.
Call the web service using ASP.NET AJAX, which allows partial page post back. With partial page post back, only specific portion of the page is updated without reloading the entire page.
For smaller amounts of data, web service performance is better when BufferResponse is set to true.
In Web.Config file When allowCookies attribute is set to true, the client application accepts the cookie returned from the ASMX web service, and copies it into all future requests that are made to the web service. This ensures that the same session is maintained between the client and the web service.
To use asp.net session object in a web service, the web service class must inherit from System.Web.Services.WebService class and EnableSession property of WebMethod attribute must be set to true.

Steps to Create a Simple Application To Understand Better

Step1

Create one table named “tblEmployees”.

Script

Create table tblEmployees
(
Id int primary key identity,
Name nvarchar(50),
Gender nvarchar(10),
Salary int
)
Go

Enter some Dummy Records into this table.

Script

Insert into tblEmployees values ('Satya1', 'Male', 55000)
Insert into tblEmployees values ('Satya2', 'Female', 68000)
Insert into tblEmployees values ('Satya3', 'Male', 57000)
Insert into tblEmployees values ('Satya4', 'Female', 53000)
Insert into tblEmployees values ('Satya5', 'Male', 60000)
Insert into tblEmployees values ('Satya6', 'Male', 60000)
Insert into tblEmployees values ('Satya7', 'Male', 60000)
Go

Step2

Create one Stored Procedure named “AngularDB”.

Script

Create Procedure AngularDB
As
Begin
select * from tblEmployees
End

Then, execute this Stored Procedure to check the records.

exec AngularDB

SQL Server

Step3

Then, create an ASP.NET application named “WebService”.

Step4

Then, add connection string in Web.Config file.

Code Ref

<?xml version="1.0" encoding="utf-8"?>
<!--
For more information on how to configure your ASP.NET application, please visit
http://go.microsoft.com/fwlink/?LinkId=169433
-->

<configuration>
<connectionStrings>
<add name="MyDB" providerName="System.Data.SqlClient" connectionString="Add Your Connection string here"/>
</connectionStrings>
<system.web>
<webServices>
<protocols>
<add name="HttpGet"/>
</protocols>
</webServices>
<compilation debug="true" targetFramework="4.0" />
</system.web>
</configuration>

Code Description

Here, I have mentioned the name of the connection string as “MyDB”.

<connectionStrings>
<add name="MyDB" providerName="System.Data.SqlClient" connectionString="Add Your Connection string here"/>
</connectionStrings>

Created ASMX web service that can perform a HTTP GET request.

<webServices>
<protocols>
<add name="HttpGet"/>
</protocols>
</webServices>

Enable an ASP.NET Web Service for HTTP POST and GET requests.

<system.web>
<webServices>
<protocols>
<add name="HttpGet"/>
<add name="HttpPost"/>
</protocols>
</webServices>
</system.web>

Step5

Then, create a class file named “Employee.cs”.

Code Ref

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
namespace WebService
{
public class Employee
{
public int id { get; set; }
public string name { get; set; }
public string gender { get; set; }
public int salary { get; set; }
}
}

Code Description

I have declared 4 entities using get and set properties inside Employee class.These 4 entities should be same as Columns inside the earlier mentioned table “tblEmployees”.

public int id { get; set; }
public string name { get; set; }
public string gender { get; set; }
public int salary { get; set; }

SQL Server

Step6

Add a new Web Service i.e. ASMX file . Name it as EmployeeService.asmx.

SQL Server

Code Ref -

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Web.Script.Serialization;
using System.Web.Services;
namespace WebService
{
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[System.ComponentModel.ToolboxItem(false)]
[System.Web.Script.Services.ScriptService]
public class EmployeeService : System.Web.Services.WebService
{
[WebMethod]
public void GetAllEmployees()
{
List<Employee> listEmployees = new List<Employee>();
string cs = ConfigurationManager.ConnectionStrings["MyDB"].ConnectionString;
using (SqlConnection con = new SqlConnection(cs))
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "AngularDB";
con.Open();
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Employee employee = new Employee();
employee.id = Convert.ToInt32(rdr["Id"]);
employee.name = rdr["Name"].ToString();
employee.gender = rdr["Gender"].ToString();
employee.salary = Convert.ToInt32(rdr["Salary"]);
listEmployees.Add(employee);
}
}
JavaScriptSerializer js = new JavaScriptSerializer();
Context.Response.Write(js.Serialize(listEmployees));
}
}
}

Code Description

This namespace is very much required for this web service related code.

using System.Web.Services;

I have created one web method named “GetAllEmployees()”.

public void GetAllEmployees()

I have used one strongly typed List of objects using Employee.cs class to manipulate , search and sort the lists.

List<Employee> listEmployees = new List<Employee>();

I have used my connection string name here.

string cs = ConfigurationManager.ConnectionStrings["MyDB"].ConnectionString;

Then, all ADO.NET objects and properties are mentioned with their vast roles like SqlConnection , SqlCommand , SqlDataReader etc.

string cs = ConfigurationManager.ConnectionStrings["MyDB"].ConnectionString;
using (SqlConnection con = new SqlConnection(cs))
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "AngularDB";
con.Open();
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Employee employee = new Employee();
employee.id = Convert.ToInt32(rdr["Id"]);
employee.name = rdr["Name"].ToString();
employee.gender = rdr["Gender"].ToString();
employee.salary = Convert.ToInt32(rdr["Salary"]);
listEmployees.Add(employee);
}
}

I have put my Stored Procedure “AngularDB” here.

cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "AngularDB";

Mention all the fields using object of Employee class.

Employee employee = new Employee();
employee.id = Convert.ToInt32(rdr["Id"]);
employee.name = rdr["Name"].ToString();
employee.gender = rdr["Gender"].ToString();
employee.salary = Convert.ToInt32(rdr["Salary"]);

Then, add all field values of Employee object “employee” to List class object “listEmployees”.

listEmployees.Add(employee);

Provide the serialization and deserialization functionality of AJAX-enabled application.

JavaScriptSerializer js = new JavaScriptSerializer();

Then, convert the listEmployees object values into JSON string.

Context.Response.Write(js.Serialize(listEmployees));

WebService Namespace can be any string, but it is common to give it a company's internet domain name as they are usually unique. Something like

[WebService(Namespace = "http://tempuri.org/")]

Step7

Check for Asp.Net web service Using Stored Procedure Works ?

Right Click On “EmployeeService.asmx” , Then View In Browser.

SQL Server

The below link is the Web Service URL :

http://localhost:52057/EmployeeService.asmx

Then Click On GetAllEmployees Link.

SQL Server

Then, to test the data whatever inserted in the above mentioned table, click on "Invoke" button.

Also, to test the operation using the HTTP POST protocol, click the 'Invoke' button.

SQL Server

Then your URL path is changed.

http://localhost:52057/EmployeeService.asmx/GetAllEmployees

TABLE’S DATAS IN JSON FORMAT

[{"id":1,"name":"Satya1","gender":"Male","salary":55000},{"id":2,"name":"Satya2","gender":"Female","salary":68000},{"id":3,"name":"Satya3","gender":"Male","salary":57000},{"id":4,"name":"Satya4","gender":"Female","salary":53000},{"id":5,"name":"Satya5","gender":"Male","salary":60000},{"id":6,"name":"Satya6","gender":"Male","salary":60000},{"id":7,"name":"Satya7","gender":"Male","salary":60000},{"id":8,"name":"Satya8","gender":"Male","salary":60000},{"id":9,"name":"Satya8","gender":"Male","salary":60000},{"id":10,"name":"Satya9","gender":"Female","salary":60000},{"id":11,"name":"Satya10","gender":"Male","salary":60000}]

OUTPUT

You can see all data in JSON format.

SQL Server

Important Notes

This web service retrieves the data from SQL Server database table, and returns it in JSON format.

SQL Server Web Services Stored Procedure JSON Data

19 Comments

Join the conversation! Your thoughts help the community grow.

Manoj vaish6y ago
I want to call web services within stored procedure and that web service will return json data and store into the table. PLease help
chetan rajput7y ago
Thank you so much , and if I want to give one web service result or output is give to the another sql satement as input how to do
Yasir Raza8y ago
I got this error
- Satyaprakash Samantaray8y ago
  Create a sp named AngularDB . Then try other steps....
Yasir Raza8y ago
System.Data.SqlClient.SqlException: Could not find stored procedure 'AngularDB'. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader() at WebS.EmployeeService.GetAllEmployees() in f:\WPF\WebS\WebS\EmployeeService.asmx.cs:line 29
Gowtham Allaka8y ago
{ country : "india", state : [ "ap", "telangana","tamilnadu"]}, { country : "srilanka",state:["aaa","bbb","ccc"]
Gowtham Allaka8y ago
good morning sir, i want json format like nested json ....country and nested states ...
Juan Samano8y ago
Excellent! Thank you.
- Satyaprakash Samantaray8y ago
  Thanks for feedback
Ilham Niya8y ago
Mailon munansangu ,i tried with both web service and jdbc methods and i found the web service that works well
Ilham Niya8y ago
I have already found the solution thank you very much Satyaprakash Samantaray
- Satyaprakash Samantaray8y ago
  Thanks for reading my article and if you have any doubt regarding technical issue you can ask me
mailon munansangu8y ago
If I want to access the web service data in android app, will I need special permission on the sever? Plus which is better web service or jdbc to connect an android app
Ilham Niya8y ago
Thank you very much .it's very usefull .please i need the same thing but with geometry data .and i can't convert the geometry in my .asmx .Can you help me.
- Satyaprakash Samantaray8y ago
  Post in csharp corner forum with details and I will check..
Zeeshan Azim9y ago
Thanks bro. It works
Thiruppathi R9y ago
Nice Article.Thanks to sharing..
Saravanakumar Sekaran9y ago
Wow very nice article
- Satyaprakash Samantaray9y ago
  Thanks for valuable feedback