• 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 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:

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 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 }
    });
}

Stored Procedure

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

View

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')

User Defined Function

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

Source Code

You can get the latest source code https://github.com/aspnetboilerplate/aspnetboilerplate-samples/tree/master/StoredProcedureDemo