locked
How do I call a stored procedure from a webmethod? RRS feed

  • 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