Contents
Get the source code from the Github repository.
Introduction
In this article, I will explain how to create custom repositories in ASP.NET Boilerplate and use stored procedure, view, user defined functions. To learn more about ASP.NET Boilerplate framework, take a look at its documentation.
To start with ASP.NET Boilerplate framework, you can download a startup template from here. 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 this link.
After opening the downloaded solution in Visual Studio 2017, we see a solution structure as given below:
Creating A Custom Repository
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;
}
...
...
}
Helper Methods
First of all, we are creating some helper methods that 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 void EnsureConnectionOpen()
{
var connection = Context.Database.GetDbConnection();
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
}
private DbTransaction GetActiveTransaction()
{
return (DbTransaction)_transactionProvider.GetActiveTransaction(new ActiveTransactionProviderArgs
{
{"ContextType", typeof(PhoneBookDbContext) },
{"MultiTenancySide", MultiTenancySide }
});
}
Stored Procedure
Here is a stored procedure call that gets username of all users. Add this to the repository implementation (UserRepository).
public async Task<List<string>> GetUserNames()
{
EnsureConnectionOpen();
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 stored 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 function 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
View
You can call a view like that:
public async Task<List<string>> GetAdminUsernames()
{
EnsureConnectionOpen();
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')
User Defined Function
You can call a User Defined Function like that:
public async Task<GetUserByIdOutput> GetUserById(EntityDto input)
{
EnsureConnectionOpen();
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
Source on Github
The source code is published on github here.
