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 empnamestring 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 empnamestring 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.

A K · Answer

Thanks Pravin.