How to Delete Data from Database Using XML Web Service


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.

img1.gif

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.

img2.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 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.

img4.gif

Copy the url of this web service for further use.

Step 4 : Click on GetDelete Button to test the web service.

img5.gif

Enter the value of Sn to test the web service.

img6.gif

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

img7.gif

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

img8.gif

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.

img9.gif

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":

img10.gif

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

img11.gif

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

img12.gif

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

img13.gif

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";
       }
}

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

img14.gif

Enter the value of TextBox.

img15.gif

Press the Delete Button.

img16.gif

The record is deleted; you can check it from your database.

img17.gif

Resources


Similar Articles