Reader Level:
ARTICLE

XML Web Service to exposes the table from Database

Posted by Akshay Teotia Articles | Web Services in C# January 12, 2012
Web Service are software program that use XML to exchange information with other software via common internet protocol. In simple sense, Web Services are means for interacting with objects over the Internet.
  • 2
  • 0
  • 25578

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.

img1.gif

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.

WEBSRVICE.gif

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

img3.gif

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.

img4.gif

Copy the url of this web service for further use.

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

img5.gif

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

img6.gif
img61.gif


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.

WEBSRVICE1.gif

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

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

img8.gif

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

img9.gif

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

img10.gif

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.

img11.gif

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:

img12.gif

Step 13 : Click on Show button.

img13.gif

Resources

Article Extensions
Contents added by sandro on Jan 24, 2013
Thanks for the article, it is very clear.
However, when debugging the webservice and invoking it i get the following error message (only with VS2012, with previous versions everything works fine):

System.Security.SecurityException: Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.

This is only when a database in exposed in the webservice.
How to solve it?
Contents added by Mads Halkjaer on Aug 29, 2012
Download File: problem.zip
I have a problem creating the object for the webservice.

localhost.Service mys = new localhost.Service();   // you need to create the object of the web

In my case it looks a bit different.

localhost = ServiceReference1

I have attached a picture of the situation.

Hope you can help me.

//Mads...



COMMENT USING

Trending up