How to use Stored Procedure with Web Service Application in C#.Net

In this article, I am saving a record into the database using a stored procedure with the help of web service application.  To implement this, first of all you’ve to create a database and stored procedure. Let’s have look on it.
Creating Database: 

Here, I’m creating a table with ‘userdet’ name which has four columns: ‘userid’, ‘username’, ‘city’ and ‘age’.

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

Creating Stored Procedure:

I’m creating a stored procedure with name ‘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

To create a web service application, follow the following given steps.

Step 1: Go to Visual Studio 2010 and create a New Project.

Step 2: Select an ASP.NET Web Application.

Step 3: Go to Solution explorer and right click at your project.

Step 4: Add a Web Service Application.

Step 5: Give it a name and click ok button.

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

Now run the application.
Output: 

 
Click on link "data" to go on the test page. Fill appropriate information’s in text fields which you want to insert in database table, after that click on button ‘Invoke’.
Output: 
 
If user entered right information in form, then records will be saved otherwise it will not be saved into database table.
Output:

 

The record will not be saved because id has been taken as primary key and it can be unique value, here we have been saving another record with id 1. 
Output:

Click on ‘invoke’ button.
Output: