Using a Stored Procedure in a Web Service

Introduction

In this article, I am saving a record into the database using a stored procedure in a web service application. At first create a database and a stored procedure.

Creating Database: I am creating a table "userdet" which has four columns as "userid", "username", "city" and "age".

create table userdet
(
UserId int primary key,
UserName varchar(30),
city varcahar(20),
age int
)

Creating Stored Procedure: I am creating a stored procedure "myprocedure".

ALTER PROCEDURE myprocedure
(
@puserid int,
@pusername varchar(30),
@pcity varchar(20),
@page int
)
AS
BEGIN
insert into userdet values(@puserid,@pusername,@pcity,@page)
END

Now create a web service application. Follow the given steps.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Data.SqlClient;

namespace usingparameterwebservice
{
/// <summary>
/// Summary description for mywebservice
/// </summary>
[WebService(Namespace = "mystoredprocedure.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 mywebservice : System.Web.Services.WebService
{
string constring = "Database=emp;server=.;user=sa;password=Password$2";
SqlConnection conn;
SqlCommand comm;

[WebMethod(Description="Simple Example")]
public string data(int id,string name,string city,int age)
{
conn = new SqlConnection(constring);
conn.Open();

comm = new SqlCommand();
comm.Connection=conn;
comm.CommandType = System.Data.CommandType.StoredProcedure;
comm.CommandText = "myprocedure";
comm.Parameters.AddWithValue("@puserid", id);
comm.Parameters.AddWithValue("@pusername", name);
comm.Parameters.AddWithValue("@pcity", city);
comm.Parameters.AddWithValue("@page", age);
try
{
comm.ExecuteNonQuery();
return "Record Saved";
}
catch (Exception)
{
return "Not Saved";
}
finally
{
conn.Close();
}
}
}
}