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:
|
1 |
Task<IEnumerable<T>> Query<T>(string sql, CommandType commandType = CommandType.Text); |
Example:
|
1 2 3 4 5 |
string sql = $""" SELECT * FROM [tag] ORDER BY [name] """; var tags = await _repo.Query<Tag>(sql); |
Select with query arguments:
|
1 |
Task<IEnumerable<T>> Query<T, U>(string sql, U parameters, CommandType commandType = CommandType.Text); |
Example:
|
1 2 3 4 5 6 7 |
int minId = 120; string sql = $""" SELECT * FROM [tag] WHERE id < @minId ORDER BY [name] """; var tags = await _repo.Query<Tag, object>(sql, new {minId = minId}); |
Select a single row
Select without query arguments:
|
1 |
Task<T> QuerySingle<T>(string sql, CommandType commandType = CommandType.Text); |
Example:
|
1 2 3 4 5 |
string sql = $""" SELECT * FROM [tag] WHERE [id] = 2021; """; var tags = await _repo.QuerySingle<Tag>(sql); |
Select with query arguments:
|
1 |
Task<T> QuerySingle<T, U>(string Sql, U parameters, CommandType commandType = CommandType.Text); |
Example:
|
1 2 3 4 5 6 7 |
int id = 2021; string sql = $""" SELECT * FROM [tag] WHERE [id] = @id; """; var tags = await _repo.QuerySingle<Tag, object>(sql, new {id = id}); |
Execute a scalar query
Perform without query arguments:
|
1 |
Task<T> ExecScalar<T>(string sql, CommandType commandType = CommandType.Text); |
Example:
|
1 2 3 4 5 |
string sql = $""" SELECT [id] FROM tag WHERE [name] = 'python' """; var id = await this.ExecScalar<int>(sql); |
Perform with query arguments:
|
1 |
Task<T> ExecScalar<T, U>(string sql, U parameters, CommandType commandType = CommandType.Text); |
Example:
|
1 2 3 4 5 6 7 |
int tagName = 'python'; string sql = $""" SELECT [id] FROM tag WHERE [name] = @tagName """; `` var id = await this.ExecScalar<int, object>(sql, new { tagName = tagName }); |
Execute non-query commands
Perform without query arguments:
|
1 |
Task Exec<T>(string sql, CommandType commandType = CommandType.Text); |
Example:
|
1 2 3 4 5 |
string sql = $""" DELETE FROM [tag] WHERE [id] = 2021; """; var tags = await _repo.Exec<Tag>(sql); |
Perform with query arguments;
|
1 |
Task Exec<T, U>(string sql, U parameters, CommandType commandType = CommandType.Text); |
Example:
|
1 2 3 4 5 6 7 |
int id = 2021; string sql = $""" DELETE FROM [tag] WHERE [id] = @id; """; var tags = await _repo.Exec<Tag, object>(sql, new {id = id}); |
App-specific helpers
Update or insert a row
|
1 |
Task<T> Upsert<T>(T model); |
If the Id column is zero then an insert is attempted, otherwise an update is attempted.
Example:
|
1 2 3 4 5 |
Snippet snippet = new {title = "My Snippet"...}; Snippet result = await _repo.Upsert<Snippet>(snippet); int newId = result.Id; |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
CREATE OR ALTER PROC [dbo].[rp_snippetUpsert] @id int = NULL, @folderid int, @title nvarchar(100), @summarymarkdown nvarchar(256), @summaryhtml nvarchar(256), @bodymarkdown nvarchar(MAX), @bodyhtml nvarchar(MAX), @dateadded datetime, @dateupdated datetime = NULL AS SET NOCOUNT ON SET XACT_ABORT ON BEGIN TRAN IF @Id > 0 BEGIN SET @dateupdated = GETDATE() UPDATE [dbo].[snippet] SET [folderid] = @folderid, [title] = @title, [summarymarkdown] = @summarymarkdown, [summaryhtml] = @summaryhtml, [bodymarkdown] = @bodymarkdown, [bodyhtml] = @bodyhtml, [dateupdated] = @dateupdated WHERE [id] = @id SELECT [id], [folderid], [title], [summarymarkdown], [summaryhtml], [bodymarkdown], [bodyhtml], [dateadded], [dateupdated] FROM [dbo].[snippet] WHERE [id] = @id END ELSE BEGIN SET @dateadded = GETDATE() INSERT INTO [dbo].snippet ( [folderid], [title], [summarymarkdown], [summaryhtml], [bodymarkdown], [bodyhtml], [dateadded] ) VALUES ( @folderid, @title, @summarymarkdown, @summaryhtml, @bodymarkdown, @bodyhtml, @dateadded ) SELECT [id], [folderid], [title], [summarymarkdown], [summaryhtml], [bodymarkdown], [bodyhtml], [dateadded], [dateupdated] FROM [dbo].[snippet] WHERE [id] = SCOPE_IDENTITY() END COMMIT GO |
Delete a row
|
1 |
Task Delete<T>(T model); |
The Delete method deletes the incoming model's corresponding row;
Example:
|
1 2 3 |
Snippet model; // where model has, at least, its id column populated. await this.Exec<T, T>($"rp_{className}Delete", model, CommandType.StoredProcedure); |
The Upsert stored procedure is generated with the sql-server-generator utility.
A generated Delete stored procedure is shown below:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
CREATE OR ALTER PROC [dbo].[rp_snippetDelete] @id int = NULL, @folderid int, @title nvarchar(100), @summarymarkdown nvarchar(256), @summaryhtml nvarchar(256), @bodymarkdown nvarchar(MAX), @bodyhtml nvarchar(MAX), @dateadded datetime, @dateupdated datetime = NULL AS SET NOCOUNT ON SET XACT_ABORT ON BEGIN TRAN DELETE FROM [dbo].[snippet] WHERE [id] = @id COMMIT GO |
