ABP框架—后台：调用存储过程Stored Procedures、视图Views（11）

在使用ABP框架构想项目时，如果想在仓储层调用存储过程，视图等对象，我们可以自定义一些方法来使用。

一、创建一个仓储层帮助类RepositoryAndSqlHelper

说明：

1.RepositoryAndSqlHelper继承ABP框架的仓储RepositoryBase，这样目前是为了将ABP默认仓储方法与扩展方法统一起来，且也方便扩展方法得到ABP默认仓储的Context等对象。

2.后续编写仓储层可直接继承RepositoryAndSqlHelper（多了调用存储过程，视图等的扩展方法）。当然不继承RepositoryAndSqlHelper,也可以继承ABP框架的仓储RepositoryBase

在EFCORE的Repositories下创建RepositoryAndSqlHelper,如下图:

代码如下：

using Abp.Data;
using Abp.Domain.Entities;
using Abp.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;

namespace xxxxx.EntityFrameworkCore.Repositories
{
    //说明：
    //1. xxxxxRepositoryBase 为ABP的默认仓储，根据情况而调整
    //2. xxxxxDbContext 为ABP的默认DbContext，根据情况而调整
    public abstract class RepositoryAndSqlHelper<TEntity, TPrimaryKey> : xxxxxRepositoryBase<TEntity, TPrimaryKey> where TEntity : class, IEntity<TPrimaryKey>
    {
        private readonly IActiveTransactionProvider _transactionProvider;

        protected RepositoryAndSqlHelper(IDbContextProvider<xxxxxDbContext> dbContextProvider, IActiveTransactionProvider transactionProvider)
            : base(dbContextProvider)
        {
            _transactionProvider = transactionProvider;
        }

        public DbCommand CreateCommand(string commandText, CommandType commandType, params SqlParameter[] parameters)
        {
            EnsureConnectionOpen();
            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(xxxxxDbContext) },
                {"MultiTenancySide", MultiTenancySide }
            });
        }
    }
}

二、定义一个仓储，继承RepositoryAndSqlHelper

下面举例调用仓储方法

public class MenuRepository : RepositoryAndSqlHelper<Sys_Menu, int>, IMenuRepository
{
    public MenuRepository(IDbContextProvider<xxxxxDbContext> dbContextProvider, IActiveTransactionProvider transactionProvider) : base(dbContextProvider, transactionProvider)
    {
    }

    public Dictionary<string, string> test()
    {
        var result = new Dictionary<string, string>();
        try
        {
            #region 利用RepositoryBase仓储，调用仓储方法

            var list = GetAllList();
            //Update(TEntity); 
            //Delete(TPrimaryKey);
            //Insert(TEntity entity);

            #endregion

            #region 利用RepositoryAndSqlHelper的扩展方法，调用CreateCommand方法

            int count = 0;
            //查表
            using (var command = CreateCommand("SELECT * FROM dbo.Sys_Menu", CommandType.Text))
            {
                using (var dataReader = command.ExecuteReader())
                {
                    count = 1;
                    while (dataReader.Read())
                    {
                        result.Add("一、" + count, dataReader["Name"].ToString());
                        count++;
                    }
                }
            }

            //查表-带SqlParameter参数
            using (var command = CreateCommand("SELECT * FROM dbo.Sys_Menu where Id = @id ", CommandType.Text, new SqlParameter("@id", 1)))
            {
                using (var dataReader = command.ExecuteReader())
                {
                    count = 1;
                    while (dataReader.Read())
                    {
                        result.Add("二、" + count, dataReader["Name"].ToString());
                        count++;
                    }
                }
            }

            //存储过程
            using (var command = CreateCommand("SP_Sys_Menu", CommandType.StoredProcedure))
            {
                using (var dataReader = command.ExecuteReader())
                {
                    count = 1;
                    while (dataReader.Read())
                    {
                        result.Add("三、" + count, dataReader["Name"].ToString());
                        count++;
                    }
                }
            }

            //存储过程-带参数
            using (var command = CreateCommand("exec SP_Para_Sys_Menu @id", CommandType.Text, new SqlParameter("@id", 1)))
            {
                using (var dataReader = command.ExecuteReader())
                {
                    count = 1;
                    while (dataReader.Read())
                    {
                        result.Add("四、" + count, dataReader["Name"].ToString());
                        count++;
                    }
                }
            }

            //视图
            using (var command = CreateCommand("SELECT * FROM dbo.View_Sys_Menu", CommandType.Text))
            {
                using (var dataReader = command.ExecuteReader())
                {
                    count = 1;
                    while (dataReader.Read())
                    {
                        result.Add("五、" + count, dataReader["Name"].ToString());
                        count++;
                    }
                }
            }

            #endregion

        }
        catch (Exception e)
        {
            //e.Message;
        }

        return result;
    }
}

对于Sys_Menu实体,IMenuRepository接口此次暂未提供代码,可参考下列文章
实体：ABP框架—后台：创建实体类Entity

https://preparedata.blog.csdn.net/article/details/97279540

仓储：ABP框架—后台：仓储Repository

https://preparedata.blog.csdn.net/article/details/97374091

ABP官方说明：

https://aspnetboilerplate.com/Pages/Documents/Articles/Using-Stored-Procedures,-User-Defined-Functions-and-Views/index.html