Click here to Skip to main content
15,867,330 members
Articles / Web Development / ASP.NET / ASP.NET Core

Using Stored Procedure, User Defined Function and Views in a Custom Repository with ASP.NET Boilerplate

Rate me:
Please Sign up or sign in to vote.
5.00/5 (10 votes)
21 Feb 2018CPOL2 min read 35.4K   13   10
How to create custom repostories in ASP.NET Boilerplate and use stored procedure, view, user defined functions inside your repository

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:

Projects

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

C#
public interface IUserRepository:  IRepository<User, long> 
{
  ...
  ...
}

Implement the repository in infrastructure layer (Acme.PhoneBook.EntityFrameworkCore).

C#
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:

C#
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).

C#
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:

C#
public interface IUserRepository:  IRepository<User, long> 
{
  ...
  Task<List<string>> GetUserNames();
  ...
}

Here is the stored procedure that is called:

C#
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:

C#
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:

C#
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:

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

C#
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:

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

C#
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:

SQL
SELECT        *
FROM            dbo.AbpUsers
WHERE        (Name = 'admin')

User Defined Function

You can call a User Defined Function like that:

C#
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:

SQL
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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Turkey Turkey
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionGet output from a stored procedure? Pin
Hao Bruce 15-Jan-19 15:48
Hao Bruce 15-Jan-19 15:48 
QuestionHow to close DbConnection after sp execution? Pin
Faisal AL Mahmud20-Sep-18 3:53
Faisal AL Mahmud20-Sep-18 3:53 
AnswerRe: How to close DbConnection after sp execution? Pin
Faisal AL Mahmud20-Sep-18 4:40
Faisal AL Mahmud20-Sep-18 4:40 
QuestionHow about using AbpDapper module? Pin
Member 1103405812-Aug-17 11:08
Member 1103405812-Aug-17 11:08 
QuestionSnippets Pin
Nelek7-Aug-17 22:42
protectorNelek7-Aug-17 22:42 
AnswerRe: Snippets Pin
Yunus Emre Kalkan10-Aug-17 0:36
Yunus Emre Kalkan10-Aug-17 0:36 
QuestionGitHub source Pin
johara567-Aug-17 6:54
johara567-Aug-17 6:54 
AnswerRe: GitHub source Pin
Yunus Emre Kalkan7-Aug-17 20:03
Yunus Emre Kalkan7-Aug-17 20:03 
QuestionSource Control For the SQL stuff Pin
rjsmith6-Aug-17 10:57
rjsmith6-Aug-17 10:57 
AnswerRe: Source Control For the SQL stuff Pin
Yunus Emre Kalkan7-Aug-17 20:00
Yunus Emre Kalkan7-Aug-17 20:00 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.