A general-purpose Dapper Repository

In all the following methods if the commandType argument is CommandType.Text then the sql argument is SQL. If the commandType argument is CommandType.StoredProcedure then the sql argument is a stored procedure name. The default in all methods is CommandType.Text.

Repository primitives

Select one or more rows

Select without query arguments:

Example:

Select with query arguments:

Example:

Select a single row

Select without query arguments:

Example:

Select with query arguments:

Example:

Execute a scalar query

Perform without query arguments:

Example:

Perform with query arguments:

Example:

Execute non-query commands

Perform without query arguments:

Example:

Perform with query arguments;

Example:

App-specific helpers

Update or insert a row

If the Id column is zero then an insert is attempted, otherwise an update is attempted.

Example:

The Upsert method derives a stored procedure name driven by the incoming class name. If the incoming class name is Snippet then the stored procedure called is named rp_snippet_upsert.

The inserted or updated row is returned. For insert operations you can interrogate the row returned for its new id. Upsert methods use the QuerySingle primitive method described above.

The Upsert stored procedure is generated with the sql-server-generator utility. The stored procedures it generates are aware of what the table's unique id column is (that is, the column name 'id' isn't assumed to be the table's id column).

A generated upsert stored procedure is shown below:

Delete a row

The Delete method deletes the incoming model's corresponding row;

Example:

The Upsert stored procedure is generated with the sql-server-generator utility.

A generated Delete stored procedure is shown below:

Leave a Comment

Your email address will not be published. Required fields are marked *