Calling a Stored Procedure with a Return Value


  • 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))
    DECLARE @lngReturn INT
    DECLARE @lngCount INT
    SELECT @lngCount = COUNT(ID)
    FROM Accounts
    WHERE ID <> @plngID
    	AND Number = @pstrNumber
    IF @lngCount > 0
    	SET @lngReturn = 1
    	SET @lngReturn = 0
    RETURN @lngReturn

    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


All replies

  • the stored procedure is quite simple, so you can do as Dragan Radovac said, and then write something like this:
    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
  • 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
  • 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);
                    iDParameter = new ObjectParameter("ID", typeof(global::System.Int32));
                return base.ExecuteFunction("GetDepartmentName", iDParameter, name);

    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;
            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
  • 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