Stored procedure in web service

Question

Hi, How will the stored procedure be used in web service application to insert record into database table. plz, explain with example. Thanks.

Pravin More · Answer

Hi Alok , here is complete code on it........... web Service code........... [OperationContract] public string InsertRow(string empname,string designation) { SqlConnection dbConn = null; dbConn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["insightconnection"].ToString()); SqlCommand cmd; SqlDataReader dr; dbcon.Open(); spcmd = new SqlCommand("SP_InsertRow", spcon); spcmd.CommandType = CommandType.StoredProcedure; spcmd.Parameters.Add(new SqlParameter("@EMPName", empname)); spcmd.Parameters.Add(new SqlParameter("@DEsignation", designation)); dr = cmd.ExecuteReader(); return "inserted......!!! "; } Stored Procedure code.............. create PROCEDURE [dbo].[SP_InsertRow] @EMPName varchar(max), @DEsignation varchar(max) AS BEGIN SET NOCOUNT ON; insert into EMPDetails values(@EMPName,@DEsignation) End Thanks, Pravin.

asif iqbal · Answer

Hi Pravin, As per your given code suppose I have webmethods for Delete and Update too, And I need to consume them(WebMethods) in my web application where I also have to handle Transaction, Can you provide with example of codes while consuming transaction should also gets handle. Since I cannot use connection.BeginTransaction in my Consuming application becoz connection part is run inside webmethod e.g. /Application Consuming Webmethod try { //Here I need to handle Transaction if any error occurred in //middle of sth the transaction should get rolled back service1.InsertRow(param1, param2); service1.DeleteRow(param3, param4); service1.UpdateRow(param5, param6); } Catch { } web Service code........... [OperationContract] public string InsertRow(string empname,string designation) { SqlConnection dbConn = null; dbConn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["insightconnection"].ToString()); SqlCommand cmd; SqlDataReader dr; dbcon.Open(); spcmd = new SqlCommand("SP_InsertRow", spcon); spcmd.CommandType = CommandType.StoredProcedure; spcmd.Parameters.Add(new SqlParameter("@EMPName", empname)); spcmd.Parameters.Add(new SqlParameter("@DEsignation", designation)); dr = cmd.ExecuteReader(); return "inserted......!!! "; } Stored Procedure code.............. create PROCEDURE [dbo].[SP_InsertRow] @EMPName varchar(max), @DEsignation varchar(max) AS BEGIN SET NOCOUNT ON; insert into EMPDetails values(@EMPName,@DEsignation) End

Pravin More · Answer

welcome Alok, kindly mark accepted answer if it helped for you.

Alok Pandey · Answer

Thanks, Pravin.

Stored procedure in web service

Answers (4)