How to Delete Data from Database Using XML Web Service

Akshay Teotia
Jan 20, 2012
20.1k
0
0
Article

Introduction

In this article I am going to make a XML Web service that updates data into a Default table of the application. For the basics of XML Web Service read my last article. Click Here

First go through SQL Server and make a table.

The following snapshot shows the process.

Database- akshay

Table Name- student

Creating XML Web Service in .Net

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

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 screenshot 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 a simple method "GetDelete" which returns an integer value.

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;
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
// 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 Service : System.Web.Services.WebService
{
    public Service()
    {
        //Uncomment the following line if using designed components
        //InitializeComponent();
    }
    SqlConnection con;
    SqlCommand cmd;
    [WebMethod]
    public int GetDelete(int sn)
    {
        con = new SqlConnection(@"Data Source=.;Initial Catalog=akshay;Persist Security Info=True;User ID=sa;word=wintellect");
        cmd = new SqlCommand("delete from student where sn=" + sn + " ", con);
        con.Open();
        int roweffected = cmd.ExecuteNonQuery();
        return roweffected;
    }
}

Step 3 : Build the 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 4 : Click on GetDelete Button to test the web service.

Enter the value of Sn to test the web service.

By pressing the "Invoke" button a XML file is generated.

The '1' respond to our data is deleted in specific DataBase table (here "student") see here.

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

Example of Testing Web Service in .Net

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

Name the web site; for example here I have chosen the name "MyTest" and clicked on the "ok" button.

Step 6 : Right-click in the Solution Explorer and Choose "Add Web Reference":

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

Step 8 : Now your web service is ready for use; you can see it in the Solution Explorer.

Step 9 : Go to the design of the Default.aspx page; drag and drop one TextBox, one Button and a Lable.

Rename the Button as 'Delete'.

Step 10 : Go to the Default.cs page and on the button click event use the following code:

protected void Button1_Click(object sender, EventArgs e)
{
    int sn = Convert.ToInt32(TextBox1.Text);
    localhost.Service myservice = new localhost.Service();
    int temp= myservice.GetDelete(sn);
    if (temp== 1)
        {
           Label1.Text = "Record is Deleted";
        }
    else
       {
           Label1.Text = "Record is not Deleted Please Try Again";
       }
}