Using StoredProcedure as a Member

You can declare your stored procedure as a member of your class. If using the Entity Framework 5 or higher, you should use a Code First DbContext:

public class MyDataBase : DbContext
{
    private readonly myStoredProc = new StoredProcedure("dbo", "MyStoredProc");
    
    public void CallMyStoredProc()
    {
        myStoredProc.Execute(this.Database.Connection);
    }

    public Task CallMyStoredProcAsync()
    {
        return myStoredProc.ExecuteAsync(this.Database.Connection);
    }
}

If your stored procedure returns one or more result sets (up to 7), you can declare them just like a code first data model:

public class MyResults
{
    [Column("ResultId")]
    public int Key { get; set; }
    public string Name { get; set; }
}

public class MyDataBase : DbContext
{
    private readonly myStoredProc<MyResults> = 
        new StoredProcedure<MyResults>("dbo", "MyStoredProc");
    private readonly myStoredProc<MyResults, MyResults> = 
        new StoredProcedure<MyResults, MyResults>("dbo", "MyStoredProc2");
        
    public IEnumerable<MyResults>CallMyStoredProc()
    {
        return myStoredProc.Execute(this.Database.Connection);
    }

    public Task<IEnumerable<MyResults>> CallMyStoredProcAsync()
    {
        return myStoredProc.ExecuteAsync(this.Database.Connection);
    }

    public Tuple<IEnumerable<MyResults>, IEnumerable<MyResults>> CallMyStoredProc2()
    {
        return myStoredProc.Execute(this.Database.Connection);
    }

    public Task<Tuple<IEnumerable<MyResults>, IEnumerable<MyResults>>> CallMyStoredProc2Async()
    {
        return myStoredProc.ExecuteAsync(this.Database.Connection);
    }
}

You can pass input parameters with either of the Passing Input Parameters methods. One bonus, however is that if you always pass the same parameter to a stored procedure, you can the result of the WithParameter method as your member variable:

private readonly myStoredProc<MyResults> = 
    new StoredProcedure<MyResults>("dbo", "MyStoredProc")
            .WithParameter("parameter", "myApp");

Last edited Dec 9, 2013 at 3:34 AM by aheidebrecht, version 1