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.

• Go to Visual Studio 2010  and create a New Project.
• Select an ASP.NET Web Application.
• Go to Solution explorer and right click at your project.
• Add a Web Service Application.
• Give it a name and click ok button.

Replace the given code with following code.

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();
            }
        }
    }
}

Run the application

Output:



Click "data" to go on the test page. Fill the form with value which is to be insert into database table. Click the invoke button.

Output:



Click the invoke button. If user do right form entry, then record will be saved otherwise it will not be saved.

Output:




The record will not be saved because id is taken as primary key and it can be unique value and here we are saving another record with id 1.

Output:



Click the "invoke" button.

Output: