This article gives an explanation about how to create web service in asp.net web forms and return the response in JSON and XML format. Here I also explain how to retrieve data from the SQL server database using a web service as well as also show you how you can retrieve data from the SQL server database.

Requirement

Implementation

Step 1: Open Your Visual Studio 2013 Or Higher Version.

Step 2: Go To File Menu and Create New Project and then Select "ASP.NET Empty Web Site", and Set project path location and Click on Ok. Same as shown in the screen below.

Step 3: Now, You have to add Web Service to the project, and for that, you have to press right-click on your project name from Solution Explorer and Click on ADD >> ADD NEW ITEM.

Step 4: Again one popup window will appear on your screen where you have to select "Web Service (ASMX)" give the Name of Your WebService and finally Click on Add button shown below.

NOTE: Make sure The file extension of your web service is (.asmx)

Step 5: Now, You have to do a database connection with your application and write your connection string in your web. config file as shown below.

  <connectionStrings>
    <add name="constr" connectionString="Data Source=DESKTOP-P1PHIU6\SQLEXPRESS;Initial Catalog=DB_Codingvila;Integrated Security=True" providerName="System.Data.SqlClient"/>
  </connectionStrings>

Step 6: Then after, You have to open the cs file of your web service (Codingvila.asmx.cs) and you need to add the following required Namespaces.

using System.Configuration;
using System.Data;
using Newtonsoft.Json;
using System.Data.SqlClient;

Step 7: Now, you should create a simple table for Students with some dummy data in the SQL server.

CREATE TABLE [dbo].[Students]
(
	[StudentId] INT NOT NULL PRIMARY KEY IDENTITY, 
    [RollNo] INT NULL, 
    [FirstName] NVARCHAR(20) NULL, 
    [LastName] NVARCHAR(20) NULL, 
    [Branch] NVARCHAR(20) NULL, 
    [College] NVARCHAR(50) NULL
)

INSERT INTO Students (RollNo,FirstName,LastName,Branch,College) VALUES 
(1001,'Nikunj','Satasiya','CSE','RK University'),
(1002,'Hiren','Dobariya','CSE','RK University'),
(1003,'Vishal','Sabhaya','CSE','RK University'),
(1004,'Sruti','Patel','IT','RK University'),
(1005,'Priya','Patel','EC','RK University'),
(1006,'Vivek','Ghadiya','CSE','RK University')

Step 8: Then after, you need to add the following method in your created web service (Codingvila.asmx.cs) file to retrieve all records from the student's table.

C#

Return XML

[WebMethod]
    public DataTable GetAllStudentsXML()
    {
        string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        using (SqlConnection con = new SqlConnection(constr))
        {
            using (SqlCommand cmd = new SqlCommand("SELECT * FROM Students with (NOLOCK)"))
            {
                cmd.Connection = con;
                DataSet ds = new DataSet();
                using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
                {
                    sda.Fill(ds, "Students");
                }
                return ds.Tables[0];
            }
        }
    }

VB.NET

Return XML

<WebMethod()> _
    Public Function GetAllStudentsXML() As DataTable
 
        Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
        Using con As New SqlConnection(constr)
            Using cmd As New SqlCommand("SELECT * FROM Students with (NOLOCK)")
                cmd.Connection = con
                Dim ds As New DataSet()
                Using sda As New SqlDataAdapter(cmd)
 
                    sda.Fill(ds, "Students")
                End Using
                Return ds.Tables(0)
            End Using
        End Using
    End Function

Step 9: Now, Save created Web service and run the service by clicking F5 and invoke GetAllStudentsXML Method as shown below

Step 10: As a Response created web service will return all the following details of Students in XML format.

Step 11: To return the Result as JSON, if you didn't have a reference of newtonsoft.json then you need to add newtonsoft.json from the NuGet Manager. and for that Go to Tools >> NuGet Package Manager and click on Package Manager console as shown in the screen below.

Step 12: Now, you have to write the following command in the Package Manager console.

PM> Install-Package Newtonsoft.Json

This command will add the reference of Newtonsoft.Json to your project.

Step 13: After successful installation of the package you have to add the reference Newtonsoft.Json in your created web service (Codingvila.asmx.cs) file.

Step 14: Now, we will create another method to retrieve the details of students in JSON format.

C#

Return JSON

[WebMethod]
    public string GetAllStudentsJSON()
    {
        string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        using (SqlConnection con = new SqlConnection(constr))
        {
            using (SqlCommand cmd = new SqlCommand("SELECT * FROM Students with (NOLOCK)"))
            {
                cmd.Connection = con;
                DataSet ds = new DataSet();
                using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
                {
                    sda.Fill(ds, "Students");
                }
                return JsonConvert.SerializeObject(ds, Newtonsoft.Json.Formatting.Indented);
            }
        }
    }

VB.NET

Return JSON

<WebMethod()> _
 
    Public Function GetAllStudentsJSON() As String
        Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
        Using con As New SqlConnection(constr)
            Using cmd As New SqlCommand("SELECT * FROM Students with (NOLOCK)")
                cmd.Connection = con
                Dim ds As New DataSet()
                Using sda As New SqlDataAdapter(cmd)
                    sda.Fill(ds, "Students")
                End Using
                Return JsonConvert.SerializeObject(ds, Newtonsoft.Json.Formatting.Indented)
            End Using
        End Using
    End Function

Step 15: Now, run the service and invoke the GetAllStudentsJSON method.

Step 16: Your service will return the following result and now it will be in JSON format.

Step 17: done.