Click here to Skip to main content
13,633,601 members
Click here to Skip to main content
Add your own
alternative version

Stats

14.6K views
12 bookmarked
Posted 2 Aug 2017
Licenced CPOL

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

, 21 Feb 2018
Rate this:
Please Sign up or sign in to vote.
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).

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.

License

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

Share

About the Author

No Biography provided

You may also be interested in...

Pro
Pro

Comments and Discussions

 
QuestionHow about using AbpDapper module? Pin
Member 1103405812-Aug-17 11:08
memberMember 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
memberYunus Emre Kalkan10-Aug-17 0:36 
QuestionGitHub source Pin
johara567-Aug-17 6:54
memberjohara567-Aug-17 6:54 
AnswerRe: GitHub source Pin
Yunus Emre Kalkan7-Aug-17 20:03
memberYunus Emre Kalkan7-Aug-17 20:03 
QuestionSource Control For the SQL stuff Pin
rjsmith6-Aug-17 10:57
memberrjsmith6-Aug-17 10:57 
AnswerRe: Source Control For the SQL stuff Pin
Yunus Emre Kalkan7-Aug-17 20:00
memberYunus 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.

Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web03 | 2.8.180712.1 | Last Updated 22 Feb 2018
Article Copyright 2017 by Yunus Emre Kalkan
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid