Answered by:
How do I call a stored procedure from a webmethod?

Question
-
User85056082 posted
I have been following the walkthrough: http://msdn.microsoft.com/en-us/library/cc668184(v=vs.100).aspx
I have been using my own database, not northwind, and it works perfectly. Now I wonder how to call stored procedures on the SQL Server from WCF Service, which is not part of the walkthrough.
I have understood I have to do a function Import to the .edmx file and I have managed to do that. I have a stored procedure, named Hours_Get _Pivot on SQL Server with input parameters TheDate smalldatetime, ActivityID int. In .edmx file the Stored procedure (FunctionImport) has the name HoursGetPivot and I belive the output fields have been defined correctly as a Complex Type (not visible anywhere as far as I can see).
Please give me the source code of the [webmethod] to call the procedure on SQL Server. I use C# and VS 2010.
Regards
Thursday, October 10, 2013 9:03 AM
Answers
-
User260886948 posted
Please give me the source code of the [webmethod] to call the procedure on SQL Server. I use C# and VS 2010.
Regards
Hi,
Here is a simple example about calling store procedure using webmethod, hope it can help you to get start:
create procedure first create Procedure insrt @eno int, @empname varchar(20), @sal bigint AS BEGIN insert into emp values(@eno,@empname,@sal) END Service class write method to insert record public class ReqInsert { private string eno; private string empname; private string sal; public string _eno { get { return this.eno; } set { this.eno = value; } } public string _empname { get { return this.empname; } set { this.empname = value; } } public string _sal { get { return this.sal; } set { this.sal = value; } } } //Create class to return object public class ResInsert { public Boolean _Result; } [WebMethod(BufferResponse = true, Description = "Insert new employee Details")] [ScriptMethod(ResponseFormat = ResponseFormat.Xml, XmlSerializeString = true)] //Insert Employee Details public ResInsert InsertNewEmployee(ReqInsert objReq) { ResInsert resp = new ResInsert(); resp._Result = false; try { sqlcon.Open(); sqlcmd = new SqlCommand("insrt", sqlcon); //insrt Stored Procedure Name sqlcmd.Parameters.Add("@eno",SqlDbType.Int).Value =Convert.ToInt32(objReq._eno); sqlcmd.Parameters.Add("@empname",SqlDbType.VarChar).Value =objReq._empname; sqlcmd.Parameters.Add("@sal",SqlDbType.bigint).Value =objReq._sal; sqlcmd.CommandType = CommandType.StoredProcedure; sqlcmd.ExecuteNonQuery(); sqlcon.Close(); resp._Result = true; } catch (Exception ex) { resp._Result = false; } return resp; } And call the service like this //Insert details into Database using Webservice protected void btnSubmit_Click(object sender, EventArgs e) { //Create instance for request and response ReqInsert objReq = new ReqInsert(); ResInsert objRes = new ResInsert(); try { objReq._eno= TextBox1.Text; objReq._empname = TextBox2.Text; objReq._sal = TextBox3.Text; objRes = obj.InsertNewEmployee(objReq); if (objRes._Result == true) { lblmsg.Text = "Insert record details successfully"; LoadGrid(); clear(); } else { lblmsg.Text = "Error not able to insert"; } } catch (Exception ex) { } }
For more information, please try to check the following blog:
#How to invoke a Web Service from a Stored Procedure:
http://www.codeproject.com/Articles/428200/How-to-invoke-a-Web-Service-from-a-Stored-Procedur .Best Regards,
Amy Peng- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Friday, October 11, 2013 2:38 AM -
User-488622176 posted
See this tutorial on how to call a stored procedure in C# code. : http://www.codeproject.com/Articles/15403/Calling-Stored-procedures-in-ADO-NET
You can perfectly call this from a webmethod...
Also shown from webmethod here : http://bytes.com/topic/asp-net/answers/869839-web-service-call-sql-stored-procedure (VB.NET code)
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Friday, October 11, 2013 4:00 AM
All replies
-
User260886948 posted
Please give me the source code of the [webmethod] to call the procedure on SQL Server. I use C# and VS 2010.
Regards
Hi,
Here is a simple example about calling store procedure using webmethod, hope it can help you to get start:
create procedure first create Procedure insrt @eno int, @empname varchar(20), @sal bigint AS BEGIN insert into emp values(@eno,@empname,@sal) END Service class write method to insert record public class ReqInsert { private string eno; private string empname; private string sal; public string _eno { get { return this.eno; } set { this.eno = value; } } public string _empname { get { return this.empname; } set { this.empname = value; } } public string _sal { get { return this.sal; } set { this.sal = value; } } } //Create class to return object public class ResInsert { public Boolean _Result; } [WebMethod(BufferResponse = true, Description = "Insert new employee Details")] [ScriptMethod(ResponseFormat = ResponseFormat.Xml, XmlSerializeString = true)] //Insert Employee Details public ResInsert InsertNewEmployee(ReqInsert objReq) { ResInsert resp = new ResInsert(); resp._Result = false; try { sqlcon.Open(); sqlcmd = new SqlCommand("insrt", sqlcon); //insrt Stored Procedure Name sqlcmd.Parameters.Add("@eno",SqlDbType.Int).Value =Convert.ToInt32(objReq._eno); sqlcmd.Parameters.Add("@empname",SqlDbType.VarChar).Value =objReq._empname; sqlcmd.Parameters.Add("@sal",SqlDbType.bigint).Value =objReq._sal; sqlcmd.CommandType = CommandType.StoredProcedure; sqlcmd.ExecuteNonQuery(); sqlcon.Close(); resp._Result = true; } catch (Exception ex) { resp._Result = false; } return resp; } And call the service like this //Insert details into Database using Webservice protected void btnSubmit_Click(object sender, EventArgs e) { //Create instance for request and response ReqInsert objReq = new ReqInsert(); ResInsert objRes = new ResInsert(); try { objReq._eno= TextBox1.Text; objReq._empname = TextBox2.Text; objReq._sal = TextBox3.Text; objRes = obj.InsertNewEmployee(objReq); if (objRes._Result == true) { lblmsg.Text = "Insert record details successfully"; LoadGrid(); clear(); } else { lblmsg.Text = "Error not able to insert"; } } catch (Exception ex) { } }
For more information, please try to check the following blog:
#How to invoke a Web Service from a Stored Procedure:
http://www.codeproject.com/Articles/428200/How-to-invoke-a-Web-Service-from-a-Stored-Procedur .Best Regards,
Amy Peng- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Friday, October 11, 2013 2:38 AM -
User-488622176 posted
See this tutorial on how to call a stored procedure in C# code. : http://www.codeproject.com/Articles/15403/Calling-Stored-procedures-in-ADO-NET
You can perfectly call this from a webmethod...
Also shown from webmethod here : http://bytes.com/topic/asp-net/answers/869839-web-service-call-sql-stored-procedure (VB.NET code)
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Friday, October 11, 2013 4:00 AM