Insert, Delete, Update in SQL using web service in ASP.NET

Web service is a part of web application that provides facility to communicate one system to another system through http (Hyper Text Transfer Protocol). Best feature of web service is, it provide cross language and platform compatibility with the help of XML (eXtensible Markup Language).

Here I have described how to insert, delete and update data into database (SQL Server) with the help of web service. Complete steps are given below.

Step 1: Open “ASP.NET Web Service”. For selecting “ASP.NET Web Service” option, must be select NET Framework 3.5. as below image. Complete path are given below

File > New > Project > Visual C# > Web > ASP.NET Web Service

Step 2: When open “ASP.NET Web Service”. By default some code are prewritten in Service.cs page, as below image.

Step 3: Let’s go for creating web service. First set your database connection string in web.config file.

Step 4: use three namespace for connection to database. These namespaces provides all necessary libraries and classes that required for insert, delete and update records to database. These namespaces are System.Data.SqlClient, System.Data and System.Configuration.

Step 5: First create one table where we insert, delete and update records. Below I have given table structure.

CREATE DATABASE Demo

 

USE Demo

 

CREATE TABLE [dbo].[tblEmployee]

(

[ID] INT IDENTITY PRIMARY KEY,

[UserName] VARCHAR(50) NOT NULL,

[Pass] VARCHAR(50) NOT NULL,

[Email] VARCHAR(50)NOT NULL,

[Gender] VARCHAR(50) NOT NULL

)

Step 6: Now establish connection from database. Before writing code first remove default method of service.cs page. Write below code for creating connection.

private string ()

    {

        string conString = ConfigurationManager.ConnectionStrings["dbconnection"].ConnectionString;

        return conString;

    }

Note: Form above code “ConnectionState()” is a method, and “dbconnection” is the name of connection string which define into web.config in step 3.

Step 7: Now create method for inserting data into database.

[WebMethod]

    public int insertRecord(string sUserName, string sPass, string sEmail, string sGender)

    {

        var con = new SqlConnection(ConnectionState());

        con.Open();

        var cmd = new SqlCommand("Insert into tblEmployee(UserName,Pass, Email, Gender) values('" + sUserName + "','" + sPass + "','" + sEmail + "','" + sGender + "')",con);

        int row = cmd.ExecuteNonQuery();

        return row;

    }

Note: Here “[WebMethod]” is responsible for execute code on web. “insertRecord()” is a method that take four argument and received from web page. Get references from given figure.

Insert Record

Step 7: Now, execute Service.asmx page.

Copy the URL string for further use. Here you focus only “insertRecord” method.

Step 8: Click on “insertRecord” method and test your web service. That looks as below image.

After entry into textbox, click on button Invoke. If everything is fine (no error in connection string and no syntactical error) than you got a confirmation message as below image and checked your database (refresh your table).

Step 9: In this step I tell you how to add web service reference in our project. Right click on your project and select “Add Service Reference…” option as below image.

Step 10: Click on button “advanced…” as below image.

Step 11: Click on button “Add Web Reference…” as below image.

Step 12: “Add Web Reference” window are open. Paste URL String (which we copy in step 7)within URL Textbox. As shown in below image. Then click on button “go” (right hand side of URL textbox with green color). After clicking button “go”, web service method is populated!, you can see your method. Now click on button“Add Reference”. Get reference form below image.

Step 13: Added reference, you can see in solution Explorer as below image.

Step 14: Now time to create user interface. Add one .aspx (Insert.aspx) page and write below code.

<div>

        <fieldset style="width: 250px;">

            <legend>Insert</legend>

            <table>

                <tr>

                    <td>

                        <asp:Label ID="lblUserName" runat="server" Text="User Name"></asp:Label>

                    </td>

                    <td>

                        <asp:TextBox ID="txtUserName" runat="server"></asp:TextBox>

                        <asp:RequiredFieldValidator ID="rfvUserName" runat="server" ControlToValidate="txtUserName"

                            Display="Dynamic" ErrorMessage="*" ToolTip="User Name is required"></asp:RequiredFieldValidator>

                    </td>

                </tr>

                <tr>

                    <td>

                        <asp:Label ID="lblPass" runat="server" Text="Password"></asp:Label>

                    </td>

                    <td>

                        <asp:TextBox ID="txtPass" runat="server" TextMode="Password"></asp:TextBox>

                        <asp:RequiredFieldValidator ID="rfvPass" runat="server" ControlToValidate="txtPass"

                            Display="Dynamic" ErrorMessage="*" ToolTip="Password is required"></asp:RequiredFieldValidator>

                    </td>

                </tr>

                <tr>

                    <td>

                        <asp:Label ID="lblEmail" runat="server" Text="Email"></asp:Label>

                    </td>

                    <td>

                        <asp:TextBox ID="txtEmail" runat="server"></asp:TextBox>

                        <asp:RequiredFieldValidator ID="refEmail" runat="server" ControlToValidate="txtEmail"

                            Display="Dynamic" ErrorMessage="*" ToolTip="Email id is required"></asp:RequiredFieldValidator>

                    </td>

                </tr>

                <tr>

                    <td>

                        <asp:Label ID="lblGender" runat="server" Text="Gender"></asp:Label>

                    </td>

                    <td>

                        <asp:TextBox ID="txtGender" runat="server"></asp:TextBox>

                        <asp:RequiredFieldValidator ID="rfvGender" runat="server" ControlToValidate="txtGender"

                            Display="Dynamic" ErrorMessage="*" ToolTip="Gender is required"></asp:RequiredFieldValidator>

                    </td>

                </tr>

                <tr>

                    <td>

                    </td>

                    <td>

                        <asp:Button ID="btnInsert" runat="server" Text="Insert" OnClick="btnInsert_Click" />

                    </td>

                </tr>

            </table>

        </fieldset>

    </div>

Step 15: Copy and paste below code on insert.aspx.cs file.

using System;

 

public partial class Transuction : System.Web.UI.Page

{

    protected void Page_Load(object sender, EventArgs e)

    {

 

    }

 

    protected void btnInsert_Click(object sender, EventArgs e)

    {

        localhost.Service dbws = new localhost.Service();

        int row = dbws.insertRecord(txtUserName.Text, txtPass.Text, txtEmail.Text, txtGender.Text);

        if (row > 0)

        {

            Response.Write("<script>alert('Record insert successfuly');</script>");

            txtUserName.Text = txtPass.Text = txtEmail.Text = txtGender.Text = "";

        }

        else { Response.Write("<script>alert('Record not insert');</script>"); }

    }

}

Note: In the above code “dbws” is an object of “local.Service”. Through object we call web service method and pass data.

Step 16: Save and build application. Execute Insert.aspx page and see view of webpage. Fill textbox and click button “Insert”.

Note: Your web service is ready for insert record into database. Now we move for delete and update record into data base.

Delete Record

Step 18: For deleting record, add one .aspx (Delete.aspx) page and design web page. You can use below code.

<div>

        <fieldset>

            <legend>Delete</legend>

            <table>

                <tr>

                    <td>

                        <asp:Label ID="lblSelect" runat="server" Text="Select"></asp:Label>

                    </td>

                    <td>

                        <asp:DropDownList ID="ddlSelect" runat="server" AutoPostBack="True"

                            Width="127px" onselectedindexchanged="ddlSelect_SelectedIndexChanged">

                        </asp:DropDownList>

                    </td>

                </tr>

                <tr>

                    <td>

                        <asp:Label ID="lblUserName" runat="server" Text="User Name"></asp:Label>

                    </td>

                    <td>

                        <asp:TextBox ID="txtUserName" runat="server" ReadOnly="True"></asp:TextBox>

                    </td>

                </tr>

                <tr>

                    <td>

                        <asp:Label ID="lblPass" runat="server" Text="Password" ReadOnly="True"></asp:Label>

                    </td>

                    <td>

                        <asp:TextBox ID="txtPass" runat="server"></asp:TextBox>

                    </td>

                </tr>

                <tr>

                    <td>

                        <asp:Label ID="lblEmail" runat="server" Text="Email" ReadOnly="True"></asp:Label>

                    </td>

                    <td>

                        <asp:TextBox ID="txtEmail" runat="server"></asp:TextBox>

                    </td>

                </tr>

                <tr>

                    <td>

                        <asp:Label ID="lblGender" runat="server" Text="Gender"></asp:Label>

                    </td>

                    <td>

                        <asp:TextBox ID="txtGender" runat="server" ReadOnly="True"></asp:TextBox>

                    </td>

                </tr>

                <tr>

                    <td>

                    </td>

                    <td>

                        <asp:Button ID="btnDelete" runat="server" Text="Delete"

                            onclick="btnDelete_Click1" />

                    </td>

                </tr>

            </table>

        </fieldset>

    </div>

Step 19: Write below code on Service.cs page that used for deleting record.

    [WebMethod]

    public DataTable getRecord()

    {

        var con = new SqlConnection(ConnectionState());

        var cmd = new SqlCommand("Select ID From tblEmployee", con);

        con.Open();

        SqlDataReader dr = cmd.ExecuteReader();

        DataTable dt = new DataTable("Employee");

        dt.Load(dr);

        con.Close();

        return dt;

    }

 

    [WebMethod]

    public DataTable getDetails(int id)

    {

        var con = new SqlConnection(ConnectionState());

        var cmd = new SqlCommand("Select [UserName],[Pass],[Email],[Gender] From tblEmployee where ID = '" + id + "'", con);

        con.Open();

        SqlDataReader dr = cmd.ExecuteReader();

        DataTable dt = new DataTable("Employee");

        dt.Load(dr);

        con.Close();

        return dt;

    }

 

    [WebMethod]

    public int deleteRecord(int id)

    {

        var con = new SqlConnection(ConnectionState());

        var cmd = new SqlCommand("delete tblEmployee where ID='" + id + "'", con);

        con.Open();

        int row = cmd.ExecuteNonQuery();

        con.Close();

        return row;

    }

Step 20: Update Web Service as below image.

Step 21: write below code on Delete.aspx.cs page.

using System;

using System.Data;

 

public partial class Delete : System.Web.UI.Page

{

    protected void Page_Load(object sender, EventArgs e)

    {

        if (!IsPostBack)

        {

            getId();

        }

    }

 

    public void getId()

    {

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

        DataTable dt = dbws.getRecord();

        ddlSelect.DataTextField = "Id";

        ddlSelect.DataValueField = "Id";

        ddlSelect.DataSource = dt;

        ddlSelect.DataBind();

    }

 

    protected void btnDelete_Click1(object sender, EventArgs e)

    {

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

        int row = dbws.deleteRecord(Convert.ToInt32(ddlSelect.SelectedItem.Text));

        if (row > 0)

        {

            Response.Write("<script>alert('Record deleted successfuly');</script>");

            getId();

        }

        else { Response.Write("<script>alert('Record not deleted');</script>"); }

    }

 

    protected void ddlSelect_SelectedIndexChanged(object sender, EventArgs e)

    {

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

        DataTable dt = dbws.getDetails(Convert.ToInt32(ddlSelect.SelectedItem.Text));

        txtUserName.Text = dt.Rows[0]["UserName"].ToString();

        txtPass.Text = dt.Rows[0]["Pass"].ToString();

        txtEmail.Text = dt.Rows[0]["Email"].ToString();

        txtGender.Text = dt.Rows[0]["Gender"].ToString();

    }

}

Note:

getId() : This is a method that get value form database and populate into drop down for selecting record.

btnDelete_Click1(object sender, EventArgs e) : This is an event that execute query for deleting record from database.

ddlSelect_SelectedIndexChanged(object sender, EventArgs e) : This is an event of dropdown that populate data into textbox according dropdown value.

Step 22: Save and build application. Execute Delete.aspx page and delete record.

Update Record

Step 23: For updating record, add one .aspx (Update.aspx) page and design web page. You can use below code.

<div>

        <fieldset>

            <legend>Update</legend>

            <table>

                <tr>

                    <td>

                        <asp:Label ID="lblSelect" runat="server" Text="Select"></asp:Label>

                    </td>

                    <td>

                        <asp:DropDownList ID="ddlSelect" runat="server" AutoPostBack="True" OnSelectedIndexChanged="ddlSelect_SelectedIndexChanged"

                            Width="127px">

                        </asp:DropDownList>

                    </td>

                </tr>

                <tr>

                    <td>

                        <asp:Label ID="lblUserName" runat="server" Text="User Name"></asp:Label>

                    </td>

                    <td>

                        <asp:TextBox ID="txtUserName" runat="server"></asp:TextBox>

                        <asp:RequiredFieldValidator ID="rfvUserName" runat="server" ControlToValidate="txtUserName"

                            Display="Dynamic" ErrorMessage="*" ToolTip="User Name is required"></asp:RequiredFieldValidator>

                    </td>

                </tr>

                <tr>

                    <td>

                        <asp:Label ID="lblPass" runat="server" Text="Password"></asp:Label>

                    </td>

                    <td>

                        <asp:TextBox ID="txtPass" runat="server" TextMode="Password"></asp:TextBox>

                        <asp:RequiredFieldValidator ID="rfvPass" runat="server" ControlToValidate="txtPass"

                            Display="Dynamic" ErrorMessage="*" ToolTip="Password is required"></asp:RequiredFieldValidator>

                    </td>

                </tr>

                <tr>

                    <td>

                        <asp:Label ID="lblEmail" runat="server" Text="Email"></asp:Label>

                    </td>

                    <td>

                        <asp:TextBox ID="txtEmail" runat="server"></asp:TextBox>

                        <asp:RequiredFieldValidator ID="refEmail" runat="server" ControlToValidate="txtEmail"

                            Display="Dynamic" ErrorMessage="*" ToolTip="Email id is required"></asp:RequiredFieldValidator>

                    </td>

                </tr>

                <tr>

                    <td>

                        <asp:Label ID="lblGender" runat="server" Text="Gender"></asp:Label>

                    </td>

                    <td>

                        <asp:TextBox ID="txtGender" runat="server"></asp:TextBox>

                        <asp:RequiredFieldValidator ID="rfvGender" runat="server" ControlToValidate="txtGender"

                            Display="Dynamic" ErrorMessage="*" ToolTip="Gender is required"></asp:RequiredFieldValidator>

                    </td>

                </tr>

                <tr>

                    <td>

                    </td>

                    <td>

                        <asp:Button ID="btnUpdate" runat="server" Text="Update" OnClick="btnUpdate_Click" />

                    </td>

                </tr>

            </table>

        </fieldset>

    </div>

Step 24: Write below code on Service.cs page that used for updating record. ConnectionState() (Step 6 ), getRecord() (step 19), getDetails() (step 19) method are necessary for updating record.

[WebMethod]

    public int updateRecord(int id, string sUserName, string sPass, string sEmail, string sGender)

    {

        var con = new SqlConnection(ConnectionState());

        var cmd = new SqlCommand("update tblEmployee set UserName='" + sUserName + "', Pass ='" + sPass + "', Email ='" + sEmail + "' , Gender='" + sGender + "' where ID='" + id + "'", con);

        con.Open();

        int row = cmd.ExecuteNonQuery();

        con.Close();

        return row;

    }

Step 25: Update Web Service as step 20.

Step 26: Write below code on Update.aspx.cs page.


using System;

using System.Data;

 

public partial class Update : System.Web.UI.Page

{

    protected void Page_Load(object sender, EventArgs e)

    {

        if (!IsPostBack)

        {

            getId();

        }

    }

 

    public void getId()

    {

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

        DataTable dt = dbws.getRecord();

        ddlSelect.DataTextField = "Id";

        ddlSelect.DataValueField = "Id";

        ddlSelect.DataSource = dt;

        ddlSelect.DataBind();

    }

 

 

    protected void btnUpdate_Click(object sender, EventArgs e)

    {

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

        int row = dbws.updateRecord(Convert.ToInt32(ddlSelect.SelectedItem.Text), txtUserName.Text, txtPass.Text, txtEmail.Text, txtGender.Text);

        if (row > 0)

        {

            Response.Write("<script>alert('Record update successfuly');</script>");

        }

        else { Response.Write("<script>alert('Record not update');</script>"); }

    }

    protected void ddlSelect_SelectedIndexChanged(object sender, EventArgs e)

    {

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

        DataTable dt = dbws.getDetails(Convert.ToInt32(ddlSelect.SelectedItem.Text));

        txtUserName.Text = dt.Rows[0]["UserName"].ToString();

        txtPass.Text = dt.Rows[0]["Pass"].ToString();

        txtEmail.Text = dt.Rows[0]["Email"].ToString();

        txtGender.Text = dt.Rows[0]["Gender"].ToString();

    }

}