How To Execute Sql

This guide shows you how to execute ad hoc SQL statements 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.

We do not recommend using this feature too often. The reason SqlRepo exists is to build and execute SQL statements using strongly typed expressions. However we recognise that sometimes you may need to do something that SqlRepo does not yet support and provide this feature as a temporary measure. We hope you will let us know what it is you are doing and if it is possible and makes sense to support the requirement in a strongly typed way we will add it.

SqlRepo provides two statement builders for executing adhoc SQL ExecuteQuerySql and ExecuteNonQuerySql. The former expects the SQL statement to return a result set that is mapped to entities, while the latter expects the script 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\ExecutingAdHocSql.cs

Execute An SQL Query

The following example shows how to execute an SQL Query.

var result = this.repositoryFactory.Create<Contact>()
                             .ExecuteQuerySql()
                             .WithSql("SELECT * FROM [dbo].[Contact]")
                             .Go()
                             .ToList();

This executes a simple select statement to return all rows from [dbo].[Contact], which are mapped into Contact entities and captured into 'result'.

Execute Non Query SQL

The following example show how to execute an SQL statement that does not return any rows.

 var result = this.RepositoryFactory.Create<Contact>()
                             .ExecuteNonQuerySql()
                             .WithSql("UPDATE [dbo].[Contact] SET [LastName] = 'Hanson' WHERE [Id] = 1")
                             .Go();

This executes and update statement to set the last name of a contact with the id of 1. By default SQL Server will return the number of rows affected by the last statement so 'result' should contain 1.