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

 

Answer 1

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

 

 

 

Answer 2

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)