none
Calling a Stored Procedure with a Return Value

    Question

  • I am experimenting with building a WPF application using a WCF data service. For my testing, I am using a SQL Server database I created for an existing VB6 application, which does most of its data processing and validation through stored procedures.

    It is my understanding that I can hook a stored procedure to my entity data model for select, update, and delete operations. I'm wondering if I can attach a stored procedure which takes several parameters and returns a specific value to a data model--something like this stored procedure:

    ALTER PROCEDURE Accounts_Validate
    ( @plngID INT, @pstrNumber VARCHAR(4))
    
    AS
    
    DECLARE @lngReturn INT
    DECLARE @lngCount INT
    
    SELECT @lngCount = COUNT(ID)
    FROM Accounts
    WHERE ID <> @plngID
    	AND Number = @pstrNumber
    
    IF @lngCount > 0
    	SET @lngReturn = 1
    ELSE
    	SET @lngReturn = 0
    
    RETURN @lngReturn
    
    GO

    This stored procedure needs to be called by my application for validation purposes before it will save a record. For the example above, I would call the procedure with an account number, and it checks the database to see if there is already another record in this table with the given number (since account number must be unique). The procedure returns 0 if validation passes, or non-zero if there is a problem with validation.

    What would be the best way to access a stored procedure like this in WCF?

    Thursday, August 02, 2012 7:02 PM

Answers

All replies

  • Hi, when using Entity Framework 4.0 you can import functions into the model. The functions imported will be attached directly to the DB context. With Entity Framework 3.5, this had to be done through entity SQL. This link will provide you with an example of how to perform such a task:

    http://msdn.microsoft.com/en-us/library/bb896334.aspx

    Friday, August 03, 2012 9:33 AM
  • the stored procedure is quite simple, so you can do as Dragan Radovac said, and then write something like this:
    [OperationContract]
    public int YourDataContext(int plngID, string pstrNumber)
    {
    using (YourDataContext context = new YourDataContext())
    {
      return context.Accounts_Validate(plngID, pstrNumber);
    }
    }

    Friday, August 03, 2012 10:31 AM
  • Bogdan, why would you give the same information that I have (following the link) and then propose your input as the answer? Do you have nothing better to do than copy peoples responses?
    Friday, August 03, 2012 10:55 AM
  • Thanks for the link.

    I have tried out two solutions based on the school database in your link, importing the GetDepartmentName procedure into the entity model. One is a console application with the entity model in it, and the other is an ASP.Net application with a data service and a console application with a service reference to the data service.

    On the standalone application, I can access the imported GetDepartmentName function from my entity context. On the application referencing an ASP.Net service, however, I cannot access the function. Since the application I am working on uses the entity framework in a separate service, I need to know if I can use a stored procedure import that way.

    Friday, August 03, 2012 5:04 PM
  • Hi, you may need to encapsulate the call to the stored procedure in your dataservice as described by this thread:

    http://social.msdn.microsoft.com/Forums/en-US/wcf/thread/f3293a11-f275-4130-b598-18824bc69b72/#3952d6e9-7477-4f4f-bb1b-f21d96b35981

    Friday, August 03, 2012 5:18 PM
  • Does making a [WebGet] function only work for IQueryable operations?

    This is the function that was generated in my entity model when I imported my stored procedure:

            public int GetDepartmentName(Nullable<global::System.Int32> iD, ObjectParameter name)
            {
                ObjectParameter iDParameter;
                if (iD.HasValue)
                {
                    iDParameter = new ObjectParameter("ID", iD);
                }
                else
                {
                    iDParameter = new ObjectParameter("ID", typeof(global::System.Int32));
                }
        
                return base.ExecuteFunction("GetDepartmentName", iDParameter, name);
            }
            #endregion
        }

    And this is the code for my service, in which I manually added GetDepartmentName:

    namespace SchoolService
    {
        public class School : DataService<SchoolEntities>
        {
            public static void InitializeService(DataServiceConfiguration config)
            {
                config.SetEntitySetAccessRule("*", EntitySetRights.All);
                config.SetServiceOperationAccessRule("*", ServiceOperationRights.All);
                config.DataServiceBehavior.MaxProtocolVersion = DataServiceProtocolVersion.V2;
            }
            [WebGet]
            public int GetDeparmentName(int iD, ObjectParameter name)
            {
                SchoolEntities context = new SchoolEntities();
                return context.GetDepartmentName(iD, name);
            }
        }
    }

    If I delete and try to re-add the service reference to my console application, I get an error message.

    Friday, August 03, 2012 7:56 PM
  • Hi, [WebGet] simply exposes a REST operation. Here is a good walk through on how to use stored procs with parameters in Data Services

    http://www.thereforesystems.com/execute-stored-procedure-with-adonet-data-services/

    • Marked as answer by Otomii Lu Tuesday, August 14, 2012 7:09 AM
    Friday, August 03, 2012 9:07 PM
  • Bogdan, why would you give the same information that I have (following the link) and then propose your input as the answer? Do you have nothing better to do than copy peoples responses?

    I deemed your answer as not full. Perhaps I misunderstood your answer and author's question. I see my answer as an extension of yours. Any way, hijacking your response was not my intent. First time I've marked my post as an answer by accidently clicking there. I've decided to unmark it, but it seems that I've clicked one extra time. Although it does surprise me why this incident bothers you so much.

    Saturday, August 04, 2012 6:35 PM