1. Resources
    2. .NET programming
    3. WCF/Webservices

How to insert, update, delete and select operations through web service?

Posted Date: 18 Nov 2012 |Updated: 18-Nov-2012 |Category: WCF/Webservices |Author: |Member Level: Gold |Points: 100 |


In this article I am going to explain in detail about how to do insert, update, delete and select operation through web services. This code snippet is useful for other platform users also access your service to do all operation in your database.

Description :

Mostly we are use web service for other platform user use able to access our site. For that reason I have cover most of all operations through web service here.
First of all I have written SQL Helper class to access database.

using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Xml;

/// 
/// Summary description for ClsFun
/// 
public class SQLHelper
{
    SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["con"].ConnectionString);
    SqlCommand sqlcmd = new SqlCommand();
    SqlDataAdapter da = new SqlDataAdapter();
    DataTable dt = new DataTable();
    DataSet ds = new DataSet();

    public SQLHelper()
    {
        //
        // TODO: Add constructor logic here
        //
    }

    public void ExecuteCommand(string qry)
    {
        try
        {
            sqlcon.Open();
            sqlcmd = new SqlCommand(qry, sqlcon);
            sqlcmd.ExecuteNonQuery();          
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            sqlcon.Close();
        }      
    }

    public XmlElement ReturnXmlData(string qry)
    {
        XmlDataDocument xmldata;
        XmlElement xmlElement = null;

        try
        {
            sqlcon.Open();
            sqlcmd = new SqlCommand(qry, sqlcon);
            da = new SqlDataAdapter(sqlcmd);
            ds = new DataSet();
            da.Fill(ds);
            xmldata = new XmlDataDocument(ds);
            xmlElement = xmldata.DocumentElement;
        }
        catch (Exception ex)
        {
            //throw ex;
        }
        finally
        {
            sqlcon.Close();
        }
        return xmlElement;
    }
}


Web Service

Now I have write code for insert, update, delete and select records in service class like below
 
using System.Web;
using System.Web.Services;
using System.Web.Script.Services;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.Xml;

[WebService(Namespace = "http://localhost/")]
[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 EmployeeDetails : System.Web.Services.WebService
{
    //Create instance for sqlheleper class
    SQLHelper obj = new SQLHelper();
    string query = string.Empty;

  
   
    public EmployeeDetails()
    {

        //Uncomment the following line if using designed components 
        //InitializeComponent(); 
    }

    /*** Retrieve employee record start ***/

    public class ReqReturnRecord
    {
        private string eno;
        private string val;

        public string _eno
        {
            get
            {
                return this.eno;
            }

            set
            {
                this.eno = value;
            }
        }
    }


    //Create class to return more than one values    
    public class ResReturnRecord
    {
        public XmlElement _Result;

    }

    [WebMethod(BufferResponse = true, Description = "Get Employee Details")]
    [ScriptMethod(ResponseFormat = ResponseFormat.Xml, XmlSerializeString = true)]

    //Get employee details using employee number or get all employee details
    public ResReturnRecord GetEmployeeDetails(ReqReturnRecord objReq)
    {
        ResReturnRecord resp = new ResReturnRecord();
        if (objReq._eno == "")
        {
            query = "Select * from emp";
        }
        else
        {
            query = "Select * from emp where eno='" + objReq._eno + "'";
        }
        resp._Result = obj.ReturnXmlData(query);
        return resp;
    }

    /*** Retrieve employee record end ***/




    /*** Insert new employee record start ***/
    public class ReqInsert
    {
        private string eno;
        private string empname;
        private string sal;

        public string _eno
        {
            get
            {
                return this.eno;
            }

            set
            {
                this.eno = value;
            }
        }
        public string _empname
        {
            get
            {
                return this.empname;
            }

            set
            {
                this.empname = value;
            }
        }
        public string _sal
        {
            get
            {
                return this.sal;
            }

            set
            {
                this.sal = value;
            }
        }
    }


    //Create class to return object
    public class ResInsert
    {
        public Boolean _Result;
    }

    [WebMethod(BufferResponse = true, Description = "Insert new employee Details")]
    [ScriptMethod(ResponseFormat = ResponseFormat.Xml, XmlSerializeString = true)]

    //Insert Employee Details
    public ResInsert InsertNewEmployee(ReqInsert objReq)
    {
        ResInsert resp = new ResInsert();
        resp._Result = false;
        query = "insert into emp(eno,empname,sal) Values('" + objReq._eno + "','" + objReq._empname + "','" + objReq._sal + "')";
        try
        {
            obj.ExecuteCommand(query);
            resp._Result = true;
        }
        catch (Exception ex)
        {
            resp._Result = false;
        }
        return resp;
    }
    /*** Insert new employee end ***/



    /*** Update existing employee details start ***/
    public class ReqUpdate
    {
        private string eno;
        private string empname;
        private string sal;

        public string _eno
        {
            get
            {
                return this.eno;
            }

            set
            {
                this.eno = value;
            }
        }
        public string _empname
        {
            get
            {
                return this.empname;
            }

            set
            {
                this.empname = value;
            }
        }
        public string _sal
        {
            get
            {
                return this.sal;
            }

            set
            {
                this.sal = value;
            }
        }
    }


    //Create class to return object
    public class ResUpdate
    {
        public Boolean _Result;  
    }

    [WebMethod(BufferResponse = true, Description = "Update existing employee details")]
    [ScriptMethod(ResponseFormat = ResponseFormat.Xml, XmlSerializeString = true)]
    [System.Diagnostics.DebuggerStepThroughAttribute()]
    //Update Employee Details
    public ResUpdate UpdateEmployeeDetails(ReqUpdate objReq)
    {
        ResUpdate resp = new ResUpdate();
        resp._Result = false;
        query = "update emp set empname='" + objReq._empname + "', sal='" + objReq._sal + "' where eno='" + objReq._eno + "'";
       
        try
        {
            obj.ExecuteCommand(query);
            resp._Result = true;
        }
        catch (Exception ex)
        {
            resp._Result = false;           
        }
        return resp;
    }
    /*** Update existing employee details end ***/

    /*** Delete existing employee details start ***/
    public class ReqDelete
    {
        private string eno;
        private string empname;
        private string sal;

        public string _eno
        {
            get
            {
                return this.eno;
            }

            set
            {
                this.eno = value;
            }
        }        
    }


    //Create class to return object   
    public class ResDelete
    {
        public Boolean _Result;
    }

    [WebMethod(BufferResponse = true, Description = "Delete existing employee details")]
    [ScriptMethod(ResponseFormat = ResponseFormat.Xml, XmlSerializeString = true)]

    //Delete Employee Details
    public ResDelete DeleteEmployeeDetails(ReqDelete objReq)
    {
        ResDelete resp = new ResDelete();
        resp._Result = false;
        query = "delete from emp where eno='" + objReq._eno + "'";

        try
        {
            obj.ExecuteCommand(query);
            resp._Result = true;
        }
        catch (Exception ex)
        {
            resp._Result = false;
        }
        return resp;
    }
    /*** Delete existing employee details end ***/

}


After I run this service class check In browser that web methods
images

Copy that above service URL in the browser and then createFile --> New -- > webstie . In the solution explorer right click on the project name and choose add web reference
images

The new popup window appear in that window paste that URL box paste that URL and click go button to see preview of that web service and then click ok button to web service as reference of your new website.
images

After add reference we are able to access web service class and its methods through our website code.

Design side

In design side I have placed three textboxes, five buttons and grid view control to perform all operations through web services.
images 



<table width="600" cellpadding="0" cellspacing="0" align="center">
    <tr>
        <td colspan="2">
            <asp:Label ID="lblmsg" runat="server"></asp:Label>
        </td>
    </tr>
    <tr>
        <td colspan="2" height="40">
            <b>Data Manipulation (Insert / update / delete) thorugh web sercvices</b>
        </td>
    </tr>
    <tr>
        <td width="50%" height="40">
            Enter eno
        </td>
        <td>
            <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
        </td>
    </tr>
    <tr>
        <td width="50%" height="40">
            Enter empname
        </td>
        <td>
            <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>
        </td>
    </tr>
    <tr>
        <td width="50%" height="40">
            Enter sal
        </td>
        <td>
            <asp:TextBox ID="TextBox3" runat="server"></asp:TextBox>
        </td>
    </tr>
    <tr>
        <td colspan="2" align="center" height="80">
            <asp:Button ID="btnSubmit" runat="server" Text="Insert" OnClick="btnSubmit_Click"
                Width="160" /> 
            <asp:Button ID="btnupdate" runat="server" Text="Update" Width="160" 
                onclick="btnupdate_Click" /> 
            <asp:Button ID="btnDelete" runat="server" Text="Delete" Width="160" 
                onclick="btnDelete_Click" /> <br /><br />
            <asp:Button ID="btnRetrieveSelected" runat="server" Text="Retrieve entered employee"
                Width="190" onclick="btnRetrieveSelected_Click" /> 
            <asp:Button ID="btnRetrieve" runat="server" Text="Retrieve All" OnClick="btnRetrieve_Click"
                Width="160" /><br />
        </td>
    </tr>
    <tr>
        <td colspan="2" align="center" height="80"><br />
            <asp:GridView ID="GridView1" runat="server" BackColor="White" 
                BorderColor="#CCCCCC" BorderStyle="None" BorderWidth="1px" CellPadding="3">
                <RowStyle ForeColor="#000066" />
                <FooterStyle BackColor="White" ForeColor="#000066" />
                <PagerStyle BackColor="White" ForeColor="#000066" HorizontalAlign="Left" />
                <SelectedRowStyle BackColor="#669999" Font-Bold="True" ForeColor="White" />
                <HeaderStyle BackColor="#006699" Font-Bold="True" ForeColor="White" />
            </asp:GridView>
        </td>
    </tr>
</table>

Server side


In server side I have do all operation in each button click event I have call separate web service method to perform operation.
First Import that reference and create instance for service class .

using localhost;

public partial class _Default : System.Web.UI.Page 
{

    EmployeeDetails obj = new EmployeeDetails();
    DataSet ds = new DataSet();

Now here I am going to explain each opertaion in detail

Insert employee details

In this section I am insert new record to database through web service. First I create two instance for that insert class. One for request insert class and other one for response output class. I have assign each request class variable through that instance and call the web method InsertNewEmployee to insert new record into the database.

//Insert details into Database using Webservice
protected void btnSubmit_Click(object sender, EventArgs e)
{
    //Create instance for request and response
    ReqInsert objReq = new ReqInsert();
    ResInsert objRes = new ResInsert();

    try
    {
        objReq._eno= TextBox1.Text;
        objReq._empname = TextBox2.Text;
        objReq._sal = TextBox3.Text;
        objRes = obj.InsertNewEmployee(objReq);

        if (objRes._Result == true)
        {
            lblmsg.Text = "Insert record details successfully";
            LoadGrid();
            clear();
        }
        else
        {
            lblmsg.Text = "Error not able to insert";
        }           
    }
    catch (Exception ex)
    { 
    
    }   
}


images

Update employee details

In this section I am update existing record in database through web service. First I create two instance for that update class. One for request insert class and other one for response output class. I have assign value for each request class variable through that instance and call the web method UpdateEmployeeDetails to update record details in the database.

//Update Existing details using Webservice
protected void btnupdate_Click(object sender, EventArgs e)
{
    //Create instance for request and response
    ReqUpdate objReq = new ReqUpdate();
    ResUpdate objRes = new ResUpdate();

    try
    {
        objReq._eno = TextBox1.Text;
        objReq._empname = TextBox2.Text;
        objReq._sal = TextBox3.Text;
        objRes = obj.UpdateEmployeeDetails(objReq);

        if (objRes._Result == true)
        {
            lblmsg.Text = "Update Details successfully";
            LoadGrid();
            clear();
        }
        else
        {               
           lblmsg.Text = "Error not able to Update";
        }
    }
    catch (Exception ex)
    {
        lblmsg.Text = ex.ToString();
    }
}

images


Delete employee details

In this section I am delete existing record in database through web service. Same like above I have assign value for employee number mean which employee need to delete through that instance and call the web method DeleteEmployeeDetails to delete record from the database.

//Delete Existing details using Webservice
protected void btnDelete_Click(object sender, EventArgs e)
{
    //Create instance for request and response
    ReqDelete objReq = new ReqDelete();
    ResDelete objRes = new ResDelete();

    try
    {
        objReq._eno = TextBox1.Text;        
        objRes = obj.DeleteEmployeeDetails(objReq);

        if (objRes._Result == true)
        {
            lblmsg.Text = "Delete record details successfully";
            LoadGrid();
            clear();
        }
        else
        {
            lblmsg.Text = "Error not able to Update";
        }
    }
    catch (Exception ex)
    {
        lblmsg.Text = ex.ToString();
    }
}
images


Search record

In this section I am search single record from database using employee number and display output in the gridview. Here I have call that GetEmployeeDetails to search particular employee details from the database.

//Select entered employee details from database using Webservice
protected void btnRetrieveSelected_Click(object sender, EventArgs e)
{
    ReqReturnRecord objReq = new ReqReturnRecord();
    ResReturnRecord objRes = new ResReturnRecord();
    objReq._eno = TextBox1.Text;
    objRes = obj.GetEmployeeDetails(objReq);
    XmlElement xlt = objRes._Result;
    if (xlt != null)
    {
        ds = new DataSet();
        XmlNodeReader nodereader = new XmlNodeReader(xlt);
        ds.ReadXml(nodereader, XmlReadMode.Auto);
        GridView1.DataSource = ds;
        GridView1.DataBind();
    }
}


images

Display all records code

In this section I am display all employee details in the gridview using below code I used same GetEmployeeDetails methods but I pass employee number as empty to display all records.

void LoadGrid()
{

    ReqReturnRecord objReq = new ReqReturnRecord();
    ResReturnRecord objRes = new ResReturnRecord();
    objReq._eno = "";
    objRes = obj.GetEmployeeDetails(objReq);
    XmlElement xlt = objRes._Result;
    if (xlt != null)
    {
        ds = new DataSet();
        XmlNodeReader nodereader = new XmlNodeReader(xlt);
        ds.ReadXml(nodereader, XmlReadMode.Auto);
        GridView1.DataSource = ds;
        GridView1.DataBind();
    }
}


images


Full Server side code


using localhost;

public partial class _Default : System.Web.UI.Page 
{

    EmployeeDetails obj = new EmployeeDetails();
    DataSet ds = new DataSet();    
    
    protected void Page_Load(object sender, EventArgs e)
    {
        lblmsg.Text = "";
        if (!Page.IsPostBack)
        {
            //Load existing data from database to gridview
            LoadGrid();
        }
    }

    //Load existing data from database to gridview
    void LoadGrid()
    {

        ReqReturnRecord objReq = new ReqReturnRecord();
        ResReturnRecord objRes = new ResReturnRecord();
        objReq._eno = "";
        objRes = obj.GetEmployeeDetails(objReq);
        XmlElement xlt = objRes._Result;
        if (xlt != null)
        {
            ds = new DataSet();
            XmlNodeReader nodereader = new XmlNodeReader(xlt);
            ds.ReadXml(nodereader, XmlReadMode.Auto);
            GridView1.DataSource = ds;
            GridView1.DataBind();
        }
    }


    //Insert details into Database using Webservice
    protected void btnSubmit_Click(object sender, EventArgs e)
    {
        //Create instance for request and response
        ReqInsert objReq = new ReqInsert();
        ResInsert objRes = new ResInsert();

        try
        {
            objReq._eno= TextBox1.Text;
            objReq._empname = TextBox2.Text;
            objReq._sal = TextBox3.Text;
            objRes = obj.InsertNewEmployee(objReq);

            if (objRes._Result == true)
            {
                lblmsg.Text = "Insert record details successfully";
                LoadGrid();
                clear();
            }
            else
            {
                lblmsg.Text = "Error not able to insert";
            }           
        }
        catch (Exception ex)
        { 
        
        }   
    }

    //Update Existing details using Webservice
    protected void btnupdate_Click(object sender, EventArgs e)
    {
        //Create instance for request and response
        ReqUpdate objReq = new ReqUpdate();
        ResUpdate objRes = new ResUpdate();

        try
        {
            objReq._eno = TextBox1.Text;
            objReq._empname = TextBox2.Text;
            objReq._sal = TextBox3.Text;
            objRes = obj.UpdateEmployeeDetails(objReq);

            if (objRes._Result == true)
            {
                lblmsg.Text = "Update Details successfully";
                LoadGrid();
                clear();
            }
            else
            {               
               lblmsg.Text = "Error not able to Update";
            }
        }
        catch (Exception ex)
        {
            lblmsg.Text = ex.ToString();
        }
    }

    //Delete Existing details using Webservice
    protected void btnDelete_Click(object sender, EventArgs e)
    {
        //Create instance for request and response
        ReqDelete objReq = new ReqDelete();
        ResDelete objRes = new ResDelete();

        try
        {
            objReq._eno = TextBox1.Text;        
            objRes = obj.DeleteEmployeeDetails(objReq);

            if (objRes._Result == true)
            {
                lblmsg.Text = "Delete record details successfully";
                LoadGrid();
                clear();
            }
            else
            {
                lblmsg.Text = "Error not able to Update";
            }
        }
        catch (Exception ex)
        {
            lblmsg.Text = ex.ToString();
        }
    }

    //Select enetered employee details from database using Webservice
    protected void btnRetrieveSelected_Click(object sender, EventArgs e)
    {
        ReqReturnRecord objReq = new ReqReturnRecord();
        ResReturnRecord objRes = new ResReturnRecord();
        objReq._eno = TextBox1.Text;
        objRes = obj.GetEmployeeDetails(objReq);
        XmlElement xlt = objRes._Result;
        if (xlt != null)
        {
            ds = new DataSet();
            XmlNodeReader nodereader = new XmlNodeReader(xlt);
            ds.ReadXml(nodereader, XmlReadMode.Auto);
            GridView1.DataSource = ds;
            GridView1.DataBind();
        }
    }

    //Select all record details from database using Webservice
    protected void btnRetrieve_Click(object sender, EventArgs e)
    {
        LoadGrid();
    }

    void clear()
    {
        //This loop takes all controls from the form1 - make sure your form name as form1 otherwise change it as per your form name
        foreach (Control c in form1.Controls)
        {
            //Clear all textbox values
            if (c is TextBox)
                ((TextBox)c).Text = "";

            //clear all check boxes
            if (c is CheckBox)
                ((CheckBox)c).Checked = false;

            //Clear all radio buttons
            if (c is RadioButton)
                ((RadioButton)c).Checked = false;

            //Clear all radio buttons
            if (c is DropDownList)
                ((DropDownList)c).SelectedIndex = 0;
        }
    }
}

Source code:

Client Side: ASP.NET
Code Behind: C#

Conclusion

I hope this code snippet is helping you to know how to do all operation through web services.


Attachments

  • WebService_InsertUpdateDelete (44357-74615-WebService-InsertUpdateDelete.rar)

    • Comments

    Author: KUNDAN KUMAR SRIVASTAVA28 Jul 2014 Member Level: Gold   Points : 0

    Follow below link which explains web service creation step by step.

    http://sharepointcafe.net/tutorial/web-services-in-asp-net



  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
    • Name:
    Email:
    Submit Article
    Return to Article Index

    Top Contributors

    Today
      Last 7 Days

        more...

        About dotnetspider.com

        We believe in providing quality content to our readers. If you have any questions or concerns regarding any content published here, feel free to contact us using the Contact link below.

        Quick Links
        General
        Services

        Digital Marketing by SpiderWorks Technologies, Kochi - India. ©