ABP学习实践(十四)--执行自定义SQL语句，创建动态表

在日常的开发工作中，有时我们需要在应用程序中直接执行SQL语句，比如动态创建数据库表、自定义查询等，利用ABP框架的仓储特性和EntityFramework Core的特性就能实现这个功能。

1.执行自定义SQL语句

网上可以查到不少通过EFCore执行SQL语句的文章，但有些是比较早的，相关方法可能已经废弃了，也有结合ABP仓储实现的，但有些地方感觉也没写明白。

1.1 创建自定义仓储

在领域层（一般是Core项目）中添加自定义仓储接口。

    public interface ISqlRepository: IRepository
    {
        /// <summary>
        /// 执行给定的命令
        /// </summary>
        /// <param name="sql">命令字符串</param>
        /// <param name="parameters">要应用于命令字符串的参数</param>
        /// <returns>执行命令后由数据库返回的结果</returns>
        Task<int> Execute(string sql, params object[] parameters);


        /// <summary>
        /// 执行语句返回dataset,注意参数用@p0、@p1、@p3...以此类推，要按照顺序
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="parameters">sql参数</param>
        /// <returns>dataset</returns>
        Task<DataTable> ExecuteDataTable(string sql, params object[] parameters);
   }

其中Execute方法一般用于新增、修改、删除操作，返回结果是受影响的结果条数；ExecuteDataTable方法一般用于查询操作，返回结果为查询结果集，格式为Datatable。

1.2 实现自定义仓储

在基础设施层（一般是EntityFrameworkCore项目）中实现接口。

    public class SqlRepository : ISqlRepository, ITransientDependency
    {
        private readonly IDbContextProvider<DataStandardMakerDbContext> _dbContextProvider;
        public SqlRepository(IDbContextProvider<DataStandardMakerDbContext> dbContextProvider)
        {
            _dbContextProvider = dbContextProvider;
        }

        /// <summary>
        /// 执行给定的命令
        /// </summary>
        /// <param name="sql">命令字符串</param>
        /// <param name="parameters">要应用于命令字符串的参数</param>
        /// <returns>执行命令后由数据库返回的结果</returns>
        public async Task<int> Execute(string sql, params object[] parameters)
        {
            return await _dbContextProvider.GetDbContext().Database.ExecuteSqlCommandAsync(sql, parameters);
        }

        /// <summary>
        /// 执行命令返回集合
        /// </summary>
        /// <param name="sql">命令字符串</param>
        /// <param name="parameters">参数</param>
        /// <returns>结果集</returns>
        public async Task<DataTable> ExecuteDataTable(string sql, params object[] parameters)
        {
            DataTable dt=_dbContextProvider.GetDbContext().Database.SqlQuery(sql, parameters);
            return await Task.FromResult(dt);
        }
    }

其中_dbContextProvider.GetDbContext().Database.SqlQuery(sql, parameters)中的SqlQuery为扩展方法。EFCore的扩展方法类为：

	public static class EntityFrameworkCoreExtension
	{
		private static DbCommand CreateCommand(DatabaseFacade facade, string sql, out DbConnection connection, params object[] parameters)
		{
			var conn = facade.GetDbConnection();
			connection = conn;
			conn.Open();
			var cmd = conn.CreateCommand();
			if (facade.IsSqlite()) //数据库类型的判断，可以根据需要改成SQL Server或MySQL
			{
				cmd.CommandText = sql;
				cmd.Parameters.AddRange(parameters);
			}
			return cmd;
		}

		public static DataTable SqlQuery(this DatabaseFacade facade, string sql, params object[] parameters)
		{
			var command = CreateCommand(facade, sql, out DbConnection conn, parameters);
			var reader = command.ExecuteReader();
			var dt = new DataTable();
			dt.Load(reader);
			reader.Close();
			conn.Close();
			return dt;
		}

		public static List<T> SqlQuery<T>(this DatabaseFacade facade, string sql, params object[] parameters) where T : class, new()
		{
			var dt = SqlQuery(facade, sql, parameters);
			return dt.ToList<T>();
		}

		public static List<T> ToList<T>(this DataTable dt) where T : class, new()
		{
			var propertyInfos = typeof(T).GetProperties();
			var list = new List<T>();
			foreach (DataRow row in dt.Rows)
			{
				var t = new T();
				foreach (PropertyInfo p in propertyInfos)
				{
					if (dt.Columns.IndexOf(p.Name) != -1 && row[p.Name] != DBNull.Value)
						p.SetValue(t, row[p.Name], null);
				}
				list.Add(t);
			}
			return list;
		}
	}

1.3 使用示例

在应用层（一般是Application项目）可以直接使用自定义仓储实现SQL语句操作。

    public class DemoService : DemoAppServiceBase, IApplicationService
    {
        private readonly ISqlRepository _repository;
        public DataService(ISqlRepository repository)
        {
            _repository = repository;
        }

        public  async Task<int> UpdateTable()
        {
        	string strSql="update tablename set colname1=value1 where id=xx";
            var result = _repository.Execute(strSql).Result;
            return await Task.FromResult(result);
        }

        public async Task<DataTable> QueryTable()
        {
        	string strSql="select colname1,colname2 from tablename";
            var result = _repository.ExecuteDataTable(strSql).Result;
            return await Task.FromResult(result);
        }
    }