doc/WebSite/Articles/Using-Stored-Procedures,-User-Defined-Functions-and-Views/index.html
In this article, i will explain how to create custom repositories in ASP.NET Boilerplate and use stored procedure, view, user defined functions.
To start with ASP.NET Boilerplate framework, you can download a startup template from https://aspnetboilerplate.com/Templates. I selected ASP.NET Core and Multi Page Web Application with Acme.PhoneBook project name. If you need help with setting up the template, see https://aspnetboilerplate.com/Pages/Documents/Zero/Startup-Template-Core
After opening the downloaded solution in Visual Studio 2017, we see a solution structure as like below:
We will create a custom repository to do some basic operations on User entity using stored procedure, view and user defined function. To implement a custom repository, just derive from your application specific base repository class.
Implement the interface in domain layer (Acme.PhoneBook.Core).
public interface IUserRepository: **IRepository\<User, long\>** {
...
...
}
Implement the repository in infrastructure layer (Acme.PhoneBook.EntityFrameworkCore).
public class UserRepository : **PhoneBookRepositoryBase\<User, long\>, IUserRepository** {
private readonly IActiveTransactionProvider _transactionProvider;
public UserRepository(IDbContextProvider<PhoneBookDbContext> dbContextProvider, IActiveTransactionProvider transactionProvider)
: base(dbContextProvider)
{
_transactionProvider = transactionProvider;
}
...
...
}
First of all, we are creating some helper methods those will be shared by other methods to perform some common tasks:
private DbCommand CreateCommand(string commandText, CommandType commandType, params SqlParameter[] parameters)
{
var command = Context.Database.GetDbConnection().CreateCommand();
command.CommandText = commandText;
command.CommandType = commandType;
command.Transaction = GetActiveTransaction();
foreach (var parameter in parameters)
{
command.Parameters.Add(parameter);
}
return command;
}
private async Task EnsureConnectionOpenAsync()
{
var connection = Context.Database.GetDbConnection();
if (connection.State != ConnectionState.Open)
{
await connection.OpenAsync();
}
}
private DbTransaction GetActiveTransaction()
{
return (DbTransaction)_transactionProvider.GetActiveTransaction(new ActiveTransactionProviderArgs
{
{"ContextType", typeof(PhoneBookDbContext) },
{"MultiTenancySide", MultiTenancySide }
});
}
Here is a stored procedure call that gets username of all users. Added this to the repository implementation (UserRepository).
public async Task<List<string>> GetUserNames()
{
await EnsureConnectionOpenAsync();
using (var command = CreateCommand("GetUsernames", CommandType.storedProcedure))
{
using (var dataReader = await command.ExecuteReaderAsync())
{
var result = new List<string>();
while (dataReader.Read())
{
result.Add(dataReader["UserName"].ToString());
}
return result;
}
}
}
And defined the GetUserNames method in the IUserRepository:
public interface IUserRepository: **IRepository\<User, long\>** {
...**Task\<List\<string\>\> GetUserNames();**...
}
Here is the store procedure that is called:
USE [PhoneBookDb]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetUsernames]
AS
BEGIN
SET NOCOUNT ON;
SELECT UserName FROM AbpUsers
END
GO
Now we implemented the functon that calls stored procedure from database. Let's use it in application service:
public class UserAppService : AsyncCrudAppService<User, UserDto, long, PagedResultRequestDto, CreateUserDto, UserDto>, IUserAppService
{ **private readonly IUserRepository \_userRepository;** public UserAppService(..., **IUserRepository userRepository** )
: base(repository)
{
... **\_userRepository = userRepository** ;
}
... **public async Task\<List\<string\>\> GetUserNames() { return await \_userRepository.GetUserNames(); }**}
Here is another example that sends a parameter to a stored procedure to delete a user:
public async Task DeleteUser(EntityDto input)
{
await Context.Database.ExecuteSqlCommandAsync(
"EXEC DeleteUserById @id",
default(CancellationToken),
new SqlParameter("id", input.Id)
);}
Stored procedure that is called for deletion:
USE [PhoneBookDb]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[DeleteUserById]
@id int
AS
BEGIN
SET NOCOUNT ON;
DELETE FROM AbpUsers WHERE [Id] = @id
END
GO
And another example that sends a parameter to update a user's email address:
public async Task UpdateEmail(UpdateEmailDto input)
{
await Context.Database.ExecuteSqlCommandAsync(
"EXEC UpdateEmailById @email, @id",
default(CancellationToken),
new SqlParameter("id", input.Id),
new SqlParameter("email", input.EmailAddress)
);
}
Stored procedure that is called for update method:
USE [PhoneBookDb]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[UpdateEmailById]
@email nvarchar(256),
@id int
AS
BEGIN
SET NOCOUNT ON;
UPDATE AbpUsers SET [EmailAddress] = @email WHERE [Id] = @id
END
GO
You can call a view like that:
public async Task<List<string>> GetAdminUsernames()
{
await EnsureConnectionOpenAsync();
using (var command = CreateCommand("SELECT * FROM dbo.UserAdminView", CommandType.Text))
{
using (var dataReader = await command.ExecuteReaderAsync())
{
var result = new List<string>();
while (dataReader.Read())
{
result.Add(dataReader["UserName"].ToString());
}
return result;
}
}
}
View for this method:
SELECT *
FROM dbo.AbpUsers
WHERE (Name = 'admin')
You can call a User Defined Function like that:
public async Task<GetUserByIdOutput> GetUserById(EntityDto input)
{
await EnsureConnectionOpenAsync();
using (var command = CreateCommand("SELECT dbo.GetUsernameById(@id)", CommandType.Text, new SqlParameter("@id", input.Id)))
{
var username = (await command.ExecuteScalarAsync()).ToString();
return new GetUserByIdOutput() { Username = username };
}
}
User Defined Function for this method:
USE [PhoneBookDb]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[GetUsernameById]
@id int
)
RETURNS nvarchar(32)
AS
BEGIN
DECLARE @username nvarchar(32)
SELECT @username = [UserName] FROM AbpUsers WHERE [ID] = @id
RETURN @username
END
GO
You can get the latest source code https://github.com/aspnetboilerplate/aspnetboilerplate-samples/tree/master/StoredProcedureDemo