6. Executing Stored Procedures

#Getting Started#

Most of the time for really heavy queries we'll be reading from a stored procedure.

Lets say we have the following (not very complicated) stored procedure:

CREATE PROCEDURE GetBooks_prc
AS
SELECT Count(0) FROM Books b JOIN People p ON b.AuthorId = p.Id WHERE b.Id > 10 and p.Name like '%William%'

We can then run the procedure:

var count = 0;
Execute.StoredProcedureReader("GetBooks_prc", (dr) => {
    count = dr.ReadScalar<int>();
});

Some things to break down.

1. We run a function on the Execute class called "StoredProcedureReader".
2. We give that function a first argument that is the name of our procedure.
3. We then have an Action that gives us access to a reader. This is where the majority of our code should go.
4. In the Action<> we are setting the count from the ReadScalar function on the reader. ReadScalar is a special function that is baked into IndexedSqlDataReader.

What happens if we have multiple result sets?

Given the (more complicated) stored procedure:

CREATE PROCEDURE GetBooksAndAuthors_prc
AS
SELECT * FROM Books b;

SELECT * FROM People p;

We would read the result of this procedure out with:

var books = new List<Book>();
var authors = new List<Author>();
Execute.StoredProcedureReader("GetBooksAndAuthors_prc", (dr) => {
    books = dr.ReadList<Book>();
    authors = dr.ReadList<Person>();
});

Each 'ReadList' call takes a type and outputs a list of that type, consuming a whole result set (i.e. each query in the procedure).

Next (Checking Your Model Mapping)