How To Execute SP

This guide shows you how to execute stored procedures using SqlRepo. It assumes you have followed one of the Getting Started guides Getting Started (IoC) or Getting Started (Static) and have a repository factory available.

SqlRepo provides two statement builders for executing stored procedures ExecuteQueryProcedure and ExecuteNonQueryProcedure. The former expects the procedure to return a result set that is mapped to entities, while the latter expects the procedure to return an integer, usually representing the number of rows affected.

You can try these examples out by forking/cloning the repository and executing the tests in $root$\SqlRepo.SqlServer.IntegrationTests\Examples\ExecutingStoredProcedures.cs

Execute A Query Procedure Without Parameters

The following example shows how to execute a stored procedure that does not require any parameters.

var result = this.repositoryFactory.Create<Contact>()
                             .ExecuteQueryProcedure()
                             .WithName("FetchAllContacts")
                             .Go()
                             .ToList();

This assumes the FetchAllContacts stored procedure is in the [dbo] schema. If your procedure is in a different schema you can override the default using the WithSchema() method. the following example shows the schema being specified explicitly.

var result = this.repositoryFactory.Create<Contact>()
                             .ExecuteQueryProcedure()
                             .WithSchema("dbo")
                             .WithName("FetchAllContacts")
                             .Go()
                             .ToList();

Both elements of the name will be encapsulated in square brackets so you do not need to include these. In both of the above cases the stored procedure [dbo].[FetchAllContacts] will be executed and 'result' will contain a Contact entity for each row in the Contact table.

Execute A Query Procedure With Parameters

The following example shows how to execute a stored procedure that requires a parameter.

 var result = this.repositoryFactory.Create<Contact>()
                             .ExecuteQueryProcedure()
                             .WithSchema("dbo")
                             .WithName("GetContactById")
                             .WithParameter("@id", 1)
                             .Go()
                             .FirstOrDefault();

Again this example explicitly specifies the schema explicitly, [dbo].[GetContactById] is executed with a value of 1 for the @id and returns a result set containing one Contact entity, so we use FirstOrDefault to capture the entity into 'result'.

Execute A Non Query Procedure Without Parameters

The following example shows how to execute a stored procedure that does not return rows and does not require any parameters.

 var result = this.repositoryFactory.Create<Contact>()
                             .ExecuteNonQueryProcedure()
                             .WithName("LogStartup")
                             .Go();

This example relies on the implicit use of the [dbo] schema and executes [dbo].[LogStartup], which adds a row to the [dbo].[Log] table and returns the number of rows affected. So 'result' should contain 1.

Execute A Non Query Procedure With Parameters

The following example shows how to execute a stored procedure that does not return rows but requires parameters.

var result = this.repositoryFactory.Create<Contact>()
                             .ExecuteNonQueryProcedure()
                             .WithName("SetLastName")
                             .WithParameter("@id", 1)
                             .WithParameter("@lastName", "Hanson")
                             .Go();

This example executes [dbo].[SetLastName] with a value of 1 for the @id parameter and 'Hanson' for the @lastName parameter. Again 'result' should contain 1 as the procedure updates a single row in the [dbo].[Contact] table.