Execute a stored procedure in Entity Framework from within an ADO.Net DataService

Question

I am using ADO.Net DataServices to expose an Entity Data Model to Silverlight. The model has a stored procedure that returns void. I want to call this procedure from the Silverlight client.

My understanding is that I should add a [WebInvoke] method to the DataService class and use DbCommand to call the stored procedure.

Here is my code so far:

using System.Data.Common;
using System.Data.Services;
using System.ServiceModel.Web;

namespace Foo.Web
{
    public class PayrollDataService : DataService<Foo.Web.PayrollEntities>
    {
        public static void InitializeService(IDataServiceConfiguration config)
        {
            config.SetEntitySetAccessRule("*", EntitySetRights.AllRead);
            config.UseVerboseErrors = true;
        }
        [WebInvoke]
        public void RunMyProcedure()
        {
            DbConnection conn = this.CurrentDataSource.Connection;
            DbCommand cmd = conn.CreateCommand();

            // TODO: Call the stored procedure in the EF Data Model.
        }
    }
}

Can someone confirm that this is the right approach and show an example using DbCommand in this situation?

Spann

Answer 1

The question I have is why you are running this in the Entity Framework, as you are returning nothing, which suggests to me you are not actually filling objects. If this is merely some clean up and does not affect the data, I would not have it in EF.

There is nothing wrong with using DBCommand to fire queries, if that is your question. And if you are actually affecting EF objects, I would not be as concerned about storing the procedure there. If the procedure does not affect EF objects, I would have it separate from EF rather than see everything as a nail and EF as the hammer.

Daugavpils

That make sense. I started with the EF model and DataServices because I thought that stored procedures were fully supported. Turns out they are only supported if returning an Entity-type. See: #579036 – Spann

I am returning entity types in most cases but this is one exception. Should I separate this one method from all the others? – Spann

I see nothing wrong with separating this method out, as it does not actually use the EF. You are simply adding the sproc there so you have one data access method. There are arguments both ways. Pro = same pattern throughout; CON = saddling unlike methods for same pattern. – Daugavpils

I don't agree. If there's only one method that doesn't return an entity, but it logicially belongs to the rest of the entity collection, I'd keep it there. – Calamondin

I agree Inferis, but we have not established this belongs to the same grouping. We also have issues that the EF is an OR Mapper, and aimed at filling entities. So while the grouping might be "appropriate" in some instances, you have a hurdle. – Daugavpils

Answer 2

You must import your store procedure in your edmx model, import function, which will return None, and call it from your code. Just like this:

[WebInvoke][WebGet]
public void RunMyProcedure(int Param1, string Param2)
{
    this.CurrentDataSource.ImportedFunction(Param1,Param2);
}

Then you can call it from browser: http: //urltoservice/serviceName.svc/RunMyProcedure?Param1=666&Param2='stringvalue'

Pirandello