Home > DeveloperSection > Articles > How to use Stored Procedure with Web Service Application in C#.Net

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


Web Services Web Services 
Ratings:
0 Comment(s)
 16006  View(s)
Rate this:

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
)
<![if !supportLineBreakNewLine]>
<![endif]>


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
<![if !supportLineBreakNewLine]>
<![endif]>


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:

How to use Stored Procedure with Web Service Application in C#.Net
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:

How to use Stored Procedure with Web Service Application in C#.Net
If user entered right information in form, then records will be saved otherwise it will not be saved into database table.

Output:

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

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:

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

Click on ‘invoke’ button.

Output:

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


Don't want to miss updates? Please click the below button!

Follow MindStick