Joe Ricklefs Joe Ricklefs - 3 years ago 86
C# Question

How to run stored procedures using IDataContext interface?

Issue: I'm trying to run a stored procedure in a controller that implements the

IDataContext
.

I know how to call
databaseContext.Database.SqlQuery <string>
... but in the
IDataContext
the
Database
class isn't exposed. I'm looking for an example of the proper way to expose the
Database
class in an
IDataContext
interface, so that I can mock the
IDataContext
properly and be able to call
IDataContext.Database.SqlQuery<string>
.......

public class MyController : ODataController
{
private IDataContext _db = new DataContext();

[HttpPost]
[ODataRoute("RunFile()")]
public IHttpActionResult RunFile()
{
//..code omitted for brevity
var result = _db.Database.SqlQuery<int>("exec MyStoredProc").ToList();
return Ok(result);
}
}


I've seen a similar question to the one I'm asking but I'm looking for an example of the answer that was posted in this question. link to Original post

Basically looking for and example of how to expose the
Database
class in the
IDataContext
.

Answer Source

I would advise not leaking that implementation concern. Instead expose only the desired functionality you want by updating the interface

public interface IDataContext {
    //...other members

    IEnumerable<TElement> SqlQuery<TElement>(string sql, params object[] parameters);
    int ExecuteSqlCommand(string sql, params object[] parameters);
}

and implementation.

public class DataContext : DbContext, IDataContext {
    //...other code removed for brevity

    public IEnumerable<TElement> SqlQuery<TElement>(string sql, params object[] parameters) {
        return Database.SqlQuery<TElement>(sql,parameters);
    }
    public int ExecuteSqlCommand(string sql, params object[] parameters) {
        return Database.ExecuteSqlCommand(sql,parameters);
    }
}

So now the dependent class can use only the functionality it needs

public class MyController : ODataController {
    private readonly IDataContext db;

    public MyController(IDataContext db) {
        this.db = db;
    }

    [HttpPost]
    [ODataRoute("RunFile()")]
    public IHttpActionResult RunFile() {
        //..code omitted for brevity

        var result = db.SqlQuery<int>("exec MyStoredProc").ToList();
        return Ok(result);
    }
}
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download