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: