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

た入场券 2023-05-29 14:25 22阅读 0赞

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

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

说明：

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

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

在EFCORE的Repositories下创建RepositoryAndSqlHelper,如下图:  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9wcmVwYXJlZGF0YS5ibG9nLmNzZG4ubmV0_size_16_color_FFFFFF_t_70]

代码如下：

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][https_preparedata.blog.csdn.net_article_details_97279540]

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

> [https://preparedata.blog.csdn.net/article/details/97374091][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][https_aspnetboilerplate.com_Pages_Documents_Articles_Using-Stored-Procedures_-User-Defined-Functions-and-Views_index.html]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9wcmVwYXJlZGF0YS5ibG9nLmNzZG4ubmV0_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20191106182051150.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9wcmVwYXJlZGF0YS5ibG9nLmNzZG4ubmV0,size_16,color_FFFFFF,t_70
[https_preparedata.blog.csdn.net_article_details_97279540]: https://preparedata.blog.csdn.net/article/details/97279540
[https_preparedata.blog.csdn.net_article_details_97374091]: https://preparedata.blog.csdn.net/article/details/97374091
[https_aspnetboilerplate.com_Pages_Documents_Articles_Using-Stored-Procedures_-User-Defined-Functions-and-Views_index.html]: https://aspnetboilerplate.com/Pages/Documents/Articles/Using-Stored-Procedures,-User-Defined-Functions-and-Views/index.html