XML Web Service to exposes the table from Database

Introduction

In this article I am going to make a XML Web service that exposes the Default table from a SQL Server database. For the basics of XML Web Service read my last article, click here.

First go through SQL Server and make a table.

Database- akshay

Table Name-member

Creating XML Web Service in .Net

Here is sample code which I use to create and consume ASP.NET Web Service:

Step 1 : Create the ASP.NET Web Service Source File

Open Visual Studio 2010 and create  a new web site.->Select .Net Framework 3.5. ->Select ASP.NET Web Service page -> Then, you have to give the name of your service. In this example I am giving it the name "mywebservice". Then Click the ok Button. A screen-shot of this activity is shown below.

Step 2 : Click on the "OK" button; you will see the following window:

Here (in the above figure), you will note that there is a predefined method "HelloWorld" which returns the string "Hello World". You can use your own method and can perform other operations. Here I made the simple method "Getmember" which returns the Dataset.

Service.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Data;
using System.Data.SqlClient;
namespace mywebservice
{
    /// <summary>
    /// Summary description for Service1
    /// </summary>
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [System.ComponentModel.ToolboxItem(false)]
    // 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 Service1 : System.Web.Services.WebService
    {
        SqlConnection con;
        SqlDataAdapter adap;
        DataSet ds;
       [WebMethod]      
        public DataSet Getmember()
        {
            con = new SqlConnection(@"Data Source=.;Initial Catalog=akshay;Persist Security Info=True;User ID=sa;pwd=wintellect;");
            adap = new SqlDataAdapter("select * from member", con);
            ds = new DataSet();
            adap.Fill(ds, "member");
            return ds;
        }
    }
}

Step 3 : Build Web Service and Run the Web Service for testing by pressing F5 function key.

Copy the url of this web service for further use.

Step 5 : click on Getmember button to test the web service.

Step 6 : Click on the Invoke button; you will see the following XML file.

Now our web service is ready to use; we just need to create a new web site to consume web service.

Example of Testing Web Service in .Net.

Step 7 : Create a Test Web Site by File > New > Web Site > ASP.Net Web Site.

Named the web site, for example here I have choosen name "Test".

Step 8 : Right Click Solution Explorer and Choose "Add Web Reference...":

Step 9 : Past the url of the web service and click on "Green arrow" button and then "Add reference".

Step 10 : Now your web service is ready to use In the Solution Explorer you will see.

Step 11 : Go to the Design of the Default.aspx page and drag and drop a button; rename it as "Show" and GridView (Inside data) as.

Step 12 : Go to the Default.cs page and for the button click event put the following code.

protected void Button1_Click(object sender, EventArgs e)
    {
        localhost.Service mys = new localhost.Service();   // you need to create the object of the web service 
         mys.Getmember();
        GridView1.DataSource =  mys.Getmember();
        GridView1.DataBind();
    }

 Step 13- Pressing the F5 function key to run the website, you will see:

Step 13 : Click on Show button.

Resources

• ASP.NET Web Service Basics.
• Adding Cloud Service project to existing ASP.Net Web Application
• Understanding Web Forms and Web services in ASP.NET