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