Here we are going to learn how to create a web services in ASP.NET which will return data as XML and JSON.
Step 1: Create new project and select ASP.NET web Application.


Step 2: Select Empty template and click OK.


Step 3: Now add Web Service in project and give it name webservice1.asmx,


Step 4: Make sure you add the connection string in your web.config file as in the following screenshot,


Step 5: Now add the references System.data, System.Data.SqlClient and System.configuration,


Step 6: Create Employee table and run the following script in your SQL query editor,
  1. /****** Object: Table [dbo].[Employee] Script Date: 09-12-2015 23:40:32 ******/
  2. SET
  3. ANSI_NULLS ON GO
  4. SET
  5. QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Employee](
  6. [EmpId] [int] IDENTITY(1, 1) NOT NULL,
  7. [EmpFirstName] [nvarchar](50) NULL,
  8. [EmpLastName] [nvarchar](50) NULL,
  9. [DeptId] [int] NULL,
  10. CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED ([EmpId] ASC) WITH (
  11. PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
  12. IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
  13. ALLOW_PAGE_LOCKS = ON
  14. ) ON [PRIMARY]
  15. ) ON [PRIMARY] GO
Now add the following method in your webservice1.asmx.cs file,
  1. [WebMethod]
  2. public DataSet getEmp()
  3. {
  4. string sql = "SELECT [EmpId],[EmpFirstName],[EmpLastName],[DeptId] FROM Employee";
  5. SqlDataAdapter da = new SqlDataAdapter(sql, ConfigurationManager.ConnectionStrings["Connection"].ToString());
  6. DataSet ds = new DataSet();
  7. da.Fill(ds);
  8. return ds;
  9. }
Write a SQL query, create a SqlDataAdapter object and pass the SQL query and connection string.

Step 7: Save and run the services by clicking F5 and invoking getEmp method as in the following,


Step 8 : Click on Invoke.
Step 9: It will return all the employees details in XML format.

XML
Step 10 : Now for JSON return please add newtonsoft.json from the NuGet Manager. Go to Tools, then NuGet Package Manager and click on Package Manager console.
Step 11: Write PM> Install-Package Newtonsoft.Json. It will add the reference Newtonsoft.Json in our project.
Step 12: It will display Success message as per the following screen, once you install the package.
Step 13: Now add the reference Newtonsoft.Json in your code.
Step 14: Create the method 'getEmployee' same as above method 'getEmp'. The only difference here is we use jsonConvert.SerializateIbject to serialize the data.
  1. [WebMethod]
  2. public string getEmployee()
  3. {
  4. string sql = "SELECT [EmpId],[EmpFirstName],[EmpLastName],[DeptId] FROM Employee";
  5. SqlDataAdapter da = new SqlDataAdapter(sql, ConfigurationManager.ConnectionStrings["Connection"].ToString());
  6. DataSet ds = new DataSet();
  7. da.Fill(ds);
  8. return JsonConvert.SerializeObject(ds, Newtonsoft.Json.Formatting.Indented);
  9. }


Step 15: Now run the service and invoke getEmployee method.
Step 16: It will return the same data but in JSON format.
Thanks for reading the article. Hope you like it.