TechSapphire - Insert Delete Update Select using Web Service and Stored procedure in ASP.NET

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>

</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>