TechSapphire Sunday, 2018-12-16, 5:50 PM
Site menu
Login form
News
Play Games
  • Deep Freeze
  • Ice Slide
  • Gyroball
  • Fat Fish
  • Bush Royal Rampage
  • Insert Delete Update Select using Web Service and Stored procedure in ASP.NET

    In this lecture, you will learn to do basic CRUD operation using web service and stored procedure. To complete this lecture you need database script, web service and website to test. You can download it from here (click here to download).

    Lecture:

    • Script for database is mentioned below:

    USE [TestWebservice]
    GO
    /****** Object:  Table [dbo].[DetailsTable]    Script Date: 03/09/2015 03:22:29 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[DetailsTable](
        [PersonID] [int] IDENTITY(1,1) NOT NULL,
        [PersonName] [varchar](100) NULL,
        [PersonCity] [varchar](100) NULL,
     CONSTRAINT [PK_DetailsTable] PRIMARY KEY CLUSTERED 
    (
        [PersonID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    SET ANSI_PADDING OFF
    GO
    /****** Object:  StoredProcedure [dbo].[UpdateDetail]    Script Date: 03/09/2015 03:22:30 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    --Stored Procedure to update record into Detail table by ID
    CREATE PROC [dbo].[UpdateDetail]
    @PersonID int,
    @PersonName varchar(100),
    @PersonCity    varchar(100)
    as
    UPDATE DetailsTable set PersonName=@PersonName,PersonCity=@PersonCity where PersonID=@PersonID;
    GO
    /****** Object:  StoredProcedure [dbo].[InsertDetail]    Script Date: 03/09/2015 03:22:30 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    --Stored Procedure to Insert record into Detail table
    CREATE PROC [dbo].[InsertDetail]
    @PersonName varchar(100),
    @PersonCity    varchar(100)
    as
    Insert into DetailsTable (PersonName,PersonCity) values(@PersonName,@PersonCity) ;
    GO
    /****** Object:  StoredProcedure [dbo].[GetDetialByID]    Script Date: 03/09/2015 03:22:30 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    --Stored Procedure to get records from detail table by ID
    CREATE PROC [dbo].[GetDetialByID]
    @PersonID int
    as
    Select PersonID,PersonName,PersonCity from DetailsTable where PersonID=@PersonID;
    GO
    /****** Object:  StoredProcedure [dbo].[GetDetail]    Script Date: 03/09/2015 03:22:30 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    --Stored Procedure to get all records from detail table
    CREATE PROC [dbo].[GetDetail]
    as
    Select PersonID,PersonName,PersonCity from DetailsTable;
    GO
    /****** Object:  StoredProcedure [dbo].[DeleteDetialByID]    Script Date: 03/09/2015 03:22:30 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    --Stored Procedure to delete records from detail table by ID
    CREATE PROC [dbo].[DeleteDetialByID]
    @PersonID int
    as
    Delete from DetailsTable  where PersonID=@PersonID;
    GO

     

     •Web Service Code:

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.Web.Services;
    using System.Data.SqlClient;
    using System.Data;
    using System.Configuration;
    namespace ServiceWebPRojectr
    {
        /// <summary>
        /// Summary description for TestService
        /// </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 TestService : System.Web.Services.WebService
        {

            [WebMethod]
            public int InsertDetail(string PersonName,string    PersonCity )
            {
                int retRecord=0;
                using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["con"].ConnectionString))
                {
                    using (SqlCommand cmd = new SqlCommand("InsertDetail", con))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.Add("PersonName", SqlDbType.VarChar, 100).Value = PersonName;
                        cmd.Parameters.Add("PersonCity", SqlDbType.VarChar, 100).Value = PersonCity;
                        if (con.State != ConnectionState.Open)
                        {
                            con.Open();
                        }
                        retRecord = cmd.ExecuteNonQuery();
                    }
                    
                }
                return retRecord;
            }
            [WebMethod]
            public int UpdateDetail(int PersonID, string PersonName, string PersonCity)
            {
                int retRecord = 0;
                using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["con"].ConnectionString))
                {
                    using (SqlCommand cmd = new SqlCommand("UpdateDetail", con))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.Add("PersonID", SqlDbType.VarChar, 100).Value = PersonID;
                        cmd.Parameters.Add("PersonName", SqlDbType.VarChar, 100).Value = PersonName;
                        cmd.Parameters.Add("PersonCity", SqlDbType.VarChar, 100).Value = PersonCity;
                        if (con.State != ConnectionState.Open)
                        {
                            con.Open();
                        }
                        retRecord = cmd.ExecuteNonQuery();
                    }

                }
                return retRecord;
            }
            [WebMethod]
            public int DeleteRecord(int PersonID)
            {
                int Rowupdate = 0;
                using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["con"].ConnectionString))
                {
                    using (SqlCommand cmd = new SqlCommand("DeleteDetialByID", con))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.Add("PersonID", SqlDbType.Int).Value = PersonID;
                        if (con.State != ConnectionState.Open)
                        {
                            con.Open();
                        }
                        Rowupdate = cmd.ExecuteNonQuery();
                        if (con.State == ConnectionState.Open)
                        {
                            con.Close();
                        }
                    }

                }
                return Rowupdate;
            }
            [WebMethod]
            public DataSet GetDetialByID(int PersonID)
            {
                DataSet ds = new DataSet();
                using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["con"].ConnectionString))
                {
                    using (SqlCommand cmd = new SqlCommand("GetDetialByID", con))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.Add("PersonID", SqlDbType.Int).Value = PersonID;
                        if (con.State != ConnectionState.Open)
                        {
                            con.Open();
                        }
                        SqlDataAdapter adp = new SqlDataAdapter();
                        adp.SelectCommand = cmd;
                        adp.Fill(ds);
                        if (con.State == ConnectionState.Open)
                        {
                            con.Close();
                        }
                    }

                }
                return ds;
            }
        }
    }

    • Test Website code:

    .cs

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using System.Data;
    namespace ServiceConsume
    {
        public partial class WebForm1 : System.Web.UI.Page
        {
            protected void Page_Load(object sender, EventArgs e)
            {

            }

            protected void btnInsert_Click(object sender, EventArgs e)
            {
                ServiceRef.TestService objTest = new ServiceRef.TestService();
                int ret= objTest.InsertDetail(txtName.Text, txtCity.Text);
                if (ret > 0)
                {
                    lblMessage.Text = "Record Inserted Successfully";
                    txtID.Text = "";
                    txtName.Text = "";
                    txtCity.Text = "";
                }
                else
                {
                    lblMessage.Text = "Error while inserting record";
                }
            }

            protected void btnUpdate_Click(object sender, EventArgs e)
            {
                ServiceRef.TestService objTest = new ServiceRef.TestService();
               int ret= objTest.UpdateDetail(Convert.ToInt32(txtID.Text),txtName.Text,txtCity.Text);
               if (ret > 0)
               {
                   lblMessage.Text = "Record update Successfully";
                   txtID.Text = "";
                   txtName.Text = "";
                   txtCity.Text = "";
               }
               else
               {
                   lblMessage.Text = "Error while updating record";
               }
            }

            protected void btnDelete_Click(object sender, EventArgs e)
            {
                ServiceRef.TestService objTest = new ServiceRef.TestService();
                int ret = objTest.DeleteRecord(Convert.ToInt32(txtID.Text));
                if (ret > 0)
                {
                    lblMessage.Text = "Record delete Successfully";
                    txtID.Text = "";
                    txtName.Text = "";
                    txtCity.Text = "";
                }
                else
                {
                    lblMessage.Text = "Error while deleting record";
                }
            }

            protected void btnSelect_Click(object sender, EventArgs e)
            {
                ServiceRef.TestService objTest = new ServiceRef.TestService();
                DataSet ret = objTest.GetDetialByID(Convert.ToInt32(txtID.Text));
                if (ret.Tables.Count > 0)
                {
                    if (ret.Tables[0].Rows.Count > 0)
                    {
                        txtName.Text = ret.Tables[0].Rows[0]["PersonName"].ToString();
                        txtCity.Text = ret.Tables[0].Rows[0]["PersonCity"].ToString();
                    }
                    else
                    {
                        lblMessage.Text = "Record not found";
                    }
                }
            }
        }
    }

    .aspx:

    <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm1.aspx.cs" Inherits="ServiceConsume.WebForm1" %>

    <!DOCTYPE html>

    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
        <title></title>
    </head>
    <body>
        <form id="form1" runat="server">
        <div>
        <table>
            <tr>
                <td>ID</td>
                <td><asp:TextBox runat="server" ID="txtID"></asp:TextBox></td>
            </tr>
            <tr>
                <td>Name</td>
                <td><asp:TextBox runat="server" ID="txtName"></asp:TextBox></td>
            </tr>
            <tr>
                <td>City</td>
                <td><asp:TextBox runat="server" ID="txtCity"></asp:TextBox></td>
            </tr>

        </table>
            <asp:Label runat="server" id="lblMessage">
            </asp:Label>
            <asp:Button runat="server" ID="btnInsert" OnClick="btnInsert_Click" Text="Insert" />
            <asp:Button runat="server" ID="btnUpdate" OnClick="btnUpdate_Click" Text="Update" />
            <asp:Button runat="server" ID="btnDelete" OnClick="btnDelete_Click" Text="Delete" />
            <asp:Button runat="server" ID="btnSelect" OnClick="btnSelect_Click" Text="Select" />
        </div>
        </form>
    </body>
    </html>

    Categories
    Programming [27]
    Tips for programming
    Security [2]
    Security Tips
    Google [1]
    Use google faster then ever you use
    Project [14]
    HTML [2]
    Electronics [0]
    Data Structure [0]
    Database [16]
    SQL SERVER
    SSRS [1]
    Sql Server Reporting Services
    Copyright MyCorp © 2018