8

I have two simple stored procedures in SqlServer:

  • SetData(@id int, @data varchar(10))
  • GetData(@id int).

GetData currently returns a single-row, single-column result set, but I could change it to be a proper function if needed.

What would be the best way to execute these from a DbContext instance?

If possible, I'd like to avoid having to do my own connection state management and/or exposing EF-specific types. I started by retrieving the ObjectContext and looking at the Execute* functions, but the documentation is pretty bad and lacking examples involving stored procedures.

Ideally, I'd like to be able to do this:

myContext.ExecuteNonQuery("SetData", id, data);
var data = myContext.ExecuteScalar<string>("GetData", id);
Diego Mijelshon
  • 52,548
  • 16
  • 116
  • 154

1 Answers1

22

DbContext offers these functions. Use:

IEumerable<...> result = myContext.Database.SqlQuery<...>(...)

to execute retrieval stored procedure and 

int result = myContext.Database.ExecuteSqlCommand(...)

to execute data modification stored procedure.

Ladislav Mrnka
  • 360,892
  • 59
  • 660
  • 670
  • 1
    What about parameters? Do I have to do it as specified here or is there a shortcut? http://stackoverflow.com/questions/4873607/how-to-use-dbcontext-database-sqlquerytelementsql-params-with-stored-procedu/4874600#4874600 – Diego Mijelshon Jun 08 '11 at 18:32
  • That is one way. It should also work without using `SqlParameter` - just pass values. I'm not sure if you don't have to refer to them by @p0, @p1, etc. when using values directly. – Ladislav Mrnka Jun 08 '11 at 18:55
  • I ended up passing an anonymous object and converting it to SqlParameters internally. – Diego Mijelshon Jun 08 '11 at 19:29