SQL Server 存储过程使用 视图 Dapper EF DbHelperSQL

存储过程 
Entity Framework Core 原始 SQL 查询
EF Core 视图
Dapper 存储过程
EF 存储过程

● 存储过程 Procedure 是一组为了完成特定功能的 SQL 语句集合，经编译后存储在数据库中，一次编译后永久有效，用户通过指定存储过程的名称并给出参数来执行。
● 存储过程中可以包含逻辑控制语句和数据操纵语句，它可以接受参数、输出参数、返回单个或多个结果集以及返回值。

【存储过程-proc_user_select】动态添加 where 条件，分页，output【存储过程】

CREATE PROCEDURE [dbo].[proc_user_select]
  --@department_code int,
  @equipment_code int,
  @start_time datetime,
  @end_time datetime,
  --@page int=1,
  --@per_page int,
  --@order nvarchar(200),
  @total int output,
  @page_count int output,
  @msg nvarchar(60) output
AS
BEGIN
    declare @sqlString1 nvarchar(666)
	declare @sqlString2 nvarchar(666)
	declare @sqlString3 nvarchar(666)
	declare @sqlWhere nvarchar(666) = N''
	declare @sql_total_string nvarchar(666) = N''
	declare @user_name nvarchar(500) = 'a'
	declare @page int = 2
    declare @per_page int =10
	declare @order nvarchar(666) = 'user_code'
	declare @skip int

	set @skip =  (case when @page-1 <= 0 then 0 else @page-1 end) * @per_page

	if (@order = '')
        begin
			set @order = 'user_code'       
        end
    if (@user_name ! = '')
        begin
			set @sqlWhere = @sqlWhere + 'and user_name like ''%'+ @user_name +'%'' '       
        end
	if (@start_time is not null and @end_time is not null)
		begin
            set @sqlWhere += 'and created_time between ''' + convert(nvarchar(10),@start_time,23) +''' and ''' +  convert(nvarchar(10),dateadd(day,1,@end_time),120) + ''' '
		end

	set @sqlString1 = 'select * from sys_user where 1=1 '+ @sqlWhere +' ';
	set @sqlString2 = N'select row_number() over (order by '+ @order +') as RowNumber,* from sys_user where 1=1 '+ @sqlWhere +' ';
	set @sqlString3 = N'select top '+ convert(nvarchar(10),@per_page) +' * from (select row_number() over(order by '+ @order +' desc) AS RowNumber,* from sys_user where 1=1 '+ @sqlWhere +') record where RowNumber > '+ convert(nvarchar(10),@skip) +''

	set @sql_total_string = N'select @total = count(*) from sys_user where 1=1 '+ @sqlWhere

	print @sqlString1
	print @sqlString2
	print @sqlString3

	exec sp_executesql @sql_total_string,N'@total int out',@total output
	set @page_count = ceiling(@total*1.0/@per_page)

	exec(@sqlString3)
END
GO

【存储过程-proc_department_select】动态添加 where 条件，分页，排序，统计总条数，统计总页数，output【存储过程】

CREATE PROCEDURE [dbo].[proc_department_select]
  @department_code int,
  @department_name nvarchar(200),
  @page int,
  @per_page int,
  @order nvarchar(200),
  @total int output,
  @page_count int output
AS
BEGIN
	declare @sql_string nvarchar(2000)
	declare @sql_total_string nvarchar(2000) 
	declare @sql_where nvarchar(2000) = N''	
	declare @skip int
	set @skip = (case when @page-1 <= 0 then 0 else @page-1 end) * @per_page

    if (@order = '')
        begin
			set @order = 'department_code'       
        end   
	if(@department_code > 0)
		begin
			set @sql_where += 'and department_code =' + @department_code
		end

	set @sql_total_string = N'select @total = count(*) from sys_department where 1=1 '+ @sql_where
	set @sql_string = N'select top '+ convert(nvarchar(10),@per_page) +' * from (select row_number() over(order by '+ @order +' desc) AS RowNumber,* from sys_department where 1=1 '+ @sql_where +') record where RowNumber > '+ convert(nvarchar(10),@skip) +''

	exec sp_executesql @sql_total_string,N'@total int out',@total output
    set @page_count = ceiling(@total * 1.0 / @per_page)
	exec(@sql_string)
END

【存储过程-proc_statistics_workload】参数允许为空，动态添加 where 条件，分页，排序，统计总条数，统计总页数，offset/fetch next，output【存储过程】

CREATE PROCEDURE [dbo].[proc_statistics_workload]
  @department_code nvarchar(20) = null,
  @department_name nvarchar(200) = null,
  @user_code nvarchar(20) = null,
  @user_name nvarchar(20) = null,
  @start_time datetime = null,
  @end_time datetime = null,
  @page int = 1,
  @per_page int,
  @total int output,
  @page_count int output
AS
BEGIN
	declare @sql_string nvarchar(2000)
	declare @sql_total_string nvarchar(2000) 
	declare @sql_where nvarchar(500) = N''
	declare @sql_where_user nvarchar(500) = N''

	declare @skip int
	set @skip = (case when @page-1 <= 0 then 0 else @page-1 end) * @per_page

	if(@department_code<>'0')
		begin
			set @sql_where_user += 'and u.DEPARTMENTID ''%' + @department_code + '%'' '
		end
    if(@department_name <> '')
		begin
			set @sql_where_user += 'and u.DEPARTMENT_NAME like ''%' + @department_name + '%'' '
		end
	if (@user_code <> '')
        begin
			set @sql_where_user += 'and u.ACCOUNT like ''%' + @user_code + '%'' '       
        end
	if (@user_name <> '')
        begin
			set @sql_where_user += 'and u.REALNAME like ''%' + @user_name + '%'' '       
        end
	if(@start_time is not null and @end_time is not null)
		begin
			set @sql_where += 'and CREATE_DATE between ''' + convert(nvarchar(10),@start_time,23) +''' and ''' +  convert(nvarchar(10),dateadd(day,1,@end_time),23) + ''' '
		end

	set @sql_string = N'
	select t.total,t.DOCTOR_ID as doctor_id,u.REALNAME as real_name,u.DEPARTMENT_NAME as department_name 
    from (select count(*) total,DOCTOR_ID from ORG_PATIENT where 1=1 '+ @sql_where +' group by DOCTOR_ID) t
    left join SYS_USER u on u.ACCOUNT = t.DOCTOR_ID
	where 1=1 '+ @sql_where_user +'
    order by CREATE_TIME desc
    offset '+ convert(nvarchar(10),@skip) +' rows 
    fetch next '+ convert(nvarchar(10),@per_page) +' rows only'

	set @sql_total_string = N'select @total = count(*) from ('+ @sql_string +') t1'

	print @sql_total_string
	print @sql_string

    exec sp_executesql @sql_total_string,N'@total int out',@total output
	set @page_count = ceiling(@total * 1.0 / @per_page)
	exec(@sql_string)
END
GO

【Dapper】动态添加 where 条件，分页，排序，统计总条数，统计总页数，output【Dapper】

public PageResult<List<DepartmentDto>> GetProcPageList(string name, PageDto page)
{
    int pageCount = 0;
    int total = 0;

    PageResult<List<DepartmentDto>> result = null;
    List<DepartmentDto> list = new List<DepartmentDto>();
    #region 存储过程
    DynamicParameters paraDept = new DynamicParameters();
    paraDept.Add("@department_code", 0);
    paraDept.Add("@department_name", "");
    paraDept.Add("@page", page.page);
    paraDept.Add("@per_page", page.per_page);
    paraDept.Add("@order", "");
    paraDept.Add("@total", 0, DbType.Int32, ParameterDirection.Output);
    paraDept.Add("@page_count", 0, DbType.Int32, ParameterDirection.Output);
    list = _conn.Query<DepartmentDto>(ConfigManager.Configuration["DbSql:Department:ProcSelectPage"], paraDept, commandType: CommandType.StoredProcedure).ToList();

    pageCount = paraDept.Get<int>("@page_count");
    total = paraDept.Get<int>("@total");

    #endregion
    result = new PageResult<List<DepartmentDto>>(list, total, pageCount);
    return result;
}

动态添加 where 条件，分页，排序，统计总条数，统计总页数，output【appsettings.json】

"DbSql": {
  "DbType": "SqlServer",
  "SqlServerConnection": "Server=.;Database=dbTest;User ID=sa;Password=000000;",
  "Department": {
    "ProcSelectPage": "proc_department_select"
  }
}

无参数，output【存储过程】

CREATE PROCEDURE proc_User_select
  @page_count int output,
  @total int output
AS
BEGIN
    declare @SqlString nvarchar(2000) 
	declare @sqlWhere nvarchar(500)

    SELECT * from sys_user
END
GO
--执行存储过程SQL语句
execute proc_User_select 
exec proc_User_select 
GO

有参数，output【存储过程】

CREATE PROCEDURE proc_NurseOnDuty_Select
    @start datetime,
    @end datetime,
    @departmentCode int,
    @page_count int output,
    @total int output
AS
BEGIN
    declare @SqlString nvarchar(2000) 
	declare @sqlWhere nvarchar(500)

    SELECT * from sys_user
END
GO

--执行存储过程SQL语句
execute proc_NurseOnDuty_Select @start = N'2020-02-10', @end = N'2020-02-20', @departmentCode = 1
exec proc_NurseOnDuty_Select @start = N'2020-02-10', @end = N'2020-02-20', @departmentCode = 1

有返回值，@@IDENTITY，SCOPE_IDENTITY()【insert - 存储过程】

CREATE PROCEDURE proc_VerifyCode_insert
    @verify_code int output
AS
BEGIN
    INSERT INTO sys_verify_code
    (verify_type,code,phone,user_code,modify_code,modify_name,modify_time,[status])
    VALUES
    (1,'123456','123',1,1,'123','2020-02-02',1)

    select @verify_code = @@IDENTITY;
    --set @verify_code = @@IDENTITY;
    --return SCOPE_IDENTITY(); --作用域不同
    --return @@IDENTITY;       --作用域不同
END
GO

execute proc_VerifyCode_insert 
exec proc_VerifyCode_insert 
GO

删除 - 存储过程（输出参数，output）

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[proc_menu_delete]
    @menu_code int,
    @code int output,
    @msg nvarchar(20) output
AS
BEGIN
	declare @count int
	set @count = (select count(*) from sys_menu where parent_code = @menu_code)

	if(@count <= 0)
		begin
		    delete from sys_menu where menu_code=@menu_code
		    set @code = 1
		    set @msg = '成功'
		end
	else
		begin
		    set @code = 2
		    set @msg = '失败'
		end
	END

删除 - 执行存储过程

DECLARE	@return_value int, 
        @code int, 
        @msg nvarchar(20)

EXEC @return_value = proc_menu_delete @menu_code = 1, @code = @code OUTPUT, @msg = @msg OUTPUT

SELECT	@code as N'@code', @msg as N'@msg'

SELECT	'Return Value' = @return_value

删除 - dapper

public int DeleteMenu(int id)
{
    var parameters = new DynamicParameters();
    parameters.Add("@menu_code", id);
    parameters.Add("@code", 0, DbType.Int32, ParameterDirection.Output);
    parameters.Add("@msg", "", DbType.String, ParameterDirection.Output);
    int result = _conn.Execute("proc_menu_delete", parameters, commandType: CommandType.StoredProcedure);
    int code = parameters.Get<int>("@code");
    string name = parameters.Get<string>("@msg");
    return result;
}

Dapper调用存储过程
Dapper 基本使用

private void Exec()
{
    ///执行 insert 存储过程
    var insert = _conn.Execute("proc_NurseOnDuty_Select",
        new
        {
            start = "2020-02-10",
            end = "2020-02-11",
            departmentCode = 1
        }, commandType: CommandType.StoredProcedure);


    ///执行 insert 存储过程 ReturnValue
    DynamicParameters para = new DynamicParameters();
    para.Add("@start", "2020-02-10");
    para.Add("@end", "2020-02-11");
    para.Add("@id", 0, DbType.Int32, ParameterDirection.ReturnValue);
    para.Add("@id", "", DbType.String, ParameterDirection.ReturnValue);
    para.Add("@id", null, DbType.Int32, ParameterDirection.ReturnValue);
    para.Add("@id", null, DbType.String, ParameterDirection.ReturnValue);

    var insert = _conn.Execute("proc_NurseOnDuty_Select", para, commandType: CommandType.StoredProcedure);


    ///执行 查询 存储过程
    var userList = _conn.Query<sys_role>("proc_role_select",
        new
        {
            start = "2020-02-10",
            end = "2020-02-11",
            departmentCode = 1
        }, commandType: CommandType.StoredProcedure).ToList();

    var result = _conn.Query<object>("proc_NurseOnDuty_Select",
        new
        {
            start = "2020-02-10",
            end = "2020-02-11",
            departmentCode = 1
        }, commandType: CommandType.StoredProcedure).ToList();
}

EF 存储过程
1、FromSql：结果不能有关联关系数据。相当于不能 join ，也返回不了 join 的关联表的数据。
2、FromSqlInterpolated
3、ExecuteSqlRaw
4、ExecuteSqlInterpolated
5、ExecuteSqlCommand：执行插入、更新跟删除的存储过程不能直接映射到实体【弃用的】。
6、出参【ParameterDirection.Output】
7、SqlQuery

传参方式【Output出参】

 public async Task<ArrayList> GetSevenDays()
 {
   #region 参数
   List<DbParameter> param = new List<DbParameter>();
   param.Add(new SqlParameter("@HosId", ""));
   #endregion

   #region 参数
   SqlParameter[] parameters = new SqlParameter[1];
   SqlParameter paramIn = new SqlParameter("currentDate", "");
   #endregion

   #region FromSqlRaw Sql语句
   var model = Context.User.FromSqlRaw("select * from sys_user where @Id = 1").SingleOrDefault();
   var data = Context.User.FromSqlRaw("select * from sys_user where code = 'ql'").AsNoTracking().FirstOrDefault();
   #endregion

   #region FromSqlRaw 存储过程 参数
   var list = Context.User.FromSqlRaw("exec proc_age_select @Id = 1").ToList();
   var a = Context.SequenceResult.FromSqlRaw("exec [dbo].[proc_user_select] @count=1,@code=QL").AsNoTracking().ToList();        
   var b = Context.SequenceResult.FromSqlRaw("exec [dbo].[proc_user_select] @count=1,@code=QL").AsEnumerable().FirstOrDefault();
   var c = Context.User.FromSqlRaw("exec [dbo].[proc_select]").ToList();
   
   var code = new SqlParameter("code", "QL");
   var count1 = new SqlParameter("count", 1);
   var e = Context.SequenceResult.FromSqlRaw("exec [dbo].[proc_sequence_select] @count,@code", count1, code).AsEnumerable().FirstOrDefault();
        
   SqlParameter[] para = new SqlParameter[] {
     new SqlParameter("count",1),
     new SqlParameter("code","QL")
   };
   var f = Context.SequenceResult.FromSqlRaw("exec [dbo].[proc_sequence_select] @count,@code", para).AsEnumerable().FirstOrDefault();
        
   #endregion

   #region FromSqlInterpolated
   var d = Context.User.FromSqlInterpolated("SELECT * FROM sys_user").ToList();
   #endregion

   #region ExecuteSqlInterpolated
   Context.Database.ExecuteSqlInterpolated("select * from sys_user");
   #endregion

   #region ExecuteSqlRaw
   Context.Database.ExecuteSqlRaw("select * from sys_user");
   Context.Database.ExecuteSqlRaw("select * from sys_user where name=@name and id=@id", new[]
   {
     new SqlParameter("name", "tom1"),
     new SqlParameter("id", 1),
   });
   Context.Database.ExecuteSqlRawAsync("exec SP_RRE_POST_HOSPITAL_RECORES");
   #endregion

   #region ExecuteSqlCommand
   SqlParameter[] para = new SqlParameter[] {
     new SqlParameter("@ID","")
   };
   Context.Database.ExecuteSqlCommand("delete sys_user where id=@ID", para);

   Context.Database.ExecuteSqlCommand("sp_user_deleteByID @ID", para);
   #endregion

   #region ExecuteSqlCommand
   SqlParameter pp_name = new SqlParameter("@Name", "");
   SqlParameter pp_phone = new SqlParameter("@Phone", "");
   Context.Database.ExecuteSqlCommand("exec [proc_AddSysUser01] @Name,@Phone", pp_name, pp_phone);

   Context.Database.ExecuteSqlCommand($"update sys_user set name=@name where id=@id", new[]
   {
     new SqlParameter("name", "tom1"),
     new SqlParameter("id", 1),
   });
   #endregion

   #region ExecuteSqlCommand
   var name = "C# 高级进阶";
   var author = "-";
   var cateid = 1;
   Context.Database.ExecuteSqlCommand("proc_createbook @p0,@p1,@p2", name, author, cateid);
   #endregion

   #region 自定义方法执行存储过程
   string userId = "123";
   SqlParameter[] sqlParameters =
   {
     new SqlParameter("@UserId", System.Data.SqlDbType.VarChar),
     new SqlParameter("@total", SqlDbType.Int,4),
     new SqlParameter("@per_page", SqlDbType.Int)
   };

   if (string.IsNullOrEmpty(userId))
     sqlParameters[0].Value = DBNull.Value; 
   else
     sqlParameters[0].Value = userId; 

   sqlParameters[1].Direction = ParameterDirection.Output;
   sqlParameters[2].Direction = ParameterDirection.Output;

   int total = Convert.ToInt32(sqlParameters[1].Value);
   int pageCount = Convert.ToInt32(sqlParameters[2].Value);

   await DbHelperSQL.ExecSpAsync(Context, "proc_seven_days_select", sqlParameters);
   #endregion

   #region 自定义方法执行存储过程【出参】
   List<DbParameter> param = new List<DbParameter>();
   param.Add(new SqlParameter("@department_code", request.department_code));
   param.Add(new SqlParameter("@department_name", request.department_name));
   param.Add(new SqlParameter("@user_code", request.user_code));
   param.Add(new SqlParameter("@user_name", request.user_name));
   param.Add(new SqlParameter("@start_time", request.start_time));
   param.Add(new SqlParameter("@end_time", request.end_time));
   param.Add(new SqlParameter("@page", request.page));
   param.Add(new SqlParameter("@per_page", request.limit));
   
   SqlParameter paramTotal = new SqlParameter("@total", SqlDbType.Int);
   paramTotal.Direction = ParameterDirection.Output;
   SqlParameter paramPageCount = new SqlParameter("@page_count", SqlDbType.Int);
   paramPageCount.Direction = ParameterDirection.Output;

   param.Add(paramTotal);
   param.Add(paramPageCount);
   var resultExpression = DbHelperSQL.ExecSpAsync(Context,"proc_statistics_workload", param.ToArray());

   //根据List<DbParameter> param集合数量，从0开始计数
   int total = Convert.ToInt32(param[8].Value);
   int pageCount = Convert.ToInt32(param[9].Value);
   #endregion

   #region
   dbContext.Database.SqlQuery<CarExpendSettlementByIncomeOutPut>($"{sql}");
   #endregion

   return await DbHelperSQL.ExecSpAsync(Context, "proc_seven_days_select", null);
 }

ExtendDbContext.cs

using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.Common;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;

namespace Nuclein.Repository
{
  public static class ExtendDbContext
  {
    /// <summary>
    /// 执行SQL返回受影响的行数
    /// </summary>
    public static int ExecSqlNoQuery<T>(this NucleinDbContext db, string sql, SqlParameter[] sqlParams = null) where T : new()
    {
      return ExecuteNoQuery<T>(db, sql, sqlParams);
    }
    /// <summary>
    /// 执行存储过程返回IEnumerable数据集
    /// </summary>
    public static IEnumerable<T> ExecProcReader<T>(this NucleinDbContext db, string sql, SqlParameter[] sqlParams = null) where T : new()
    {
      return Execute<T>(db, sql, CommandType.StoredProcedure, sqlParams);
    }
    /// <summary>
    /// 执行sql返回IEnumerable数据集
    /// </summary>
    public static IEnumerable<T> ExecSqlReader<T>(this NucleinDbContext db, string sql, SqlParameter[] sqlParams = null) where T : new()
    {
      return Execute<T>(db, sql, CommandType.Text, sqlParams);
    }
    private static int ExecuteNoQuery<T>(this NucleinDbContext db, string sql, SqlParameter[] sqlParams) where T : new()
    {
      DbConnection connection = db.Database.GetDbConnection();
      DbCommand cmd = connection.CreateCommand();
      int result = 0;
      db.Database.OpenConnection();
      cmd.CommandText = sql;
      cmd.CommandType = CommandType.Text;
      if (sqlParams != null)
      {
        cmd.Parameters.AddRange(sqlParams);
      }
      result = cmd.ExecuteNonQuery();
      db.Database.CloseConnection();
      return result;
    }
    private static IEnumerable<T> Execute<T>(this NucleinDbContext db, string sql, CommandType type, SqlParameter[] sqlParams) where T : new()
    {
      DbConnection connection = db.Database.GetDbConnection();
      DbCommand cmd = connection.CreateCommand();
      db.Database.OpenConnection();
      cmd.CommandText = sql;
      cmd.CommandType = type;
      if (sqlParams != null)
      {
        cmd.Parameters.AddRange(sqlParams);
      }
      DataTable dt = new DataTable();
      using (DbDataReader reader = cmd.ExecuteReader())
      {
        dt.Load(reader);
      }
      db.Database.CloseConnection();
      return dt.ToCollection<T>();
    }

    public static async IAsyncEnumerable<string> ReadAllLines(string file)
    {
      using (var fs = File.OpenRead(file))
      {
        using (var sr = new StreamReader(fs))
        {
          while (true)
          {
            string line = await sr.ReadLineAsync();
            if (line == null)
            {
              break;
            }
            yield return line;
          }
        }
      }
    }
  }
}

public static class ExtendDataTable
{
  public static DataTable ToDataTable<T>(this IEnumerable<T> data)
  {
    PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(T));
    var table = new DataTable();
    foreach (PropertyDescriptor prop in properties)
      table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
    foreach (T item in data)
    {
      DataRow row = table.NewRow();
      foreach (PropertyDescriptor prop in properties)
        row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;
      table.Rows.Add(row);
    }
    return table;
  }

  public static T ToEntity<T>(this DataTable dt) where T : new()
  {
    IEnumerable<T> entities = dt.ToCollection<T>();
    return entities.FirstOrDefault();
  }

  public static IEnumerable<T> ToCollection<T>(this DataTable dt) where T : new()
  {
    if (dt == null || dt.Rows.Count == 0)
    {
      return Enumerable.Empty<T>();
    }
    IList<T> ts = new List<T>();
    // 获得此模型的类型 
    Type type = typeof(T);
    string tempName = string.Empty;
    foreach (DataRow dr in dt.Rows)
    {
      T t = new T();
      PropertyInfo[] propertys = t.GetType().GetProperties();
      foreach (PropertyInfo pi in propertys)
      {
        tempName = pi.Name;
        //检查DataTable是否包含此列（列名==对象的属性名）     
        if (dt.Columns.Contains(tempName))
        {
          // 判断此属性是否有Setter   
          if (!pi.CanWrite) continue;//该属性不可写，直接跳出   
          object value = dr[tempName];
          if (value != DBNull.Value)
            pi.SetValue(t, value, null);
        }
      }
      ts.Add(t);
    }
    return ts;
  }
}

DbHelperSQL.cs

using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Text;
using System.Threading.Tasks;

namespace Nuclein.Repository
{
  public static class DbHelperSQL
  {
    /// <summary>
    /// 查询
    /// </summary>
    /// <returns></returns>
    public async static Task<ArrayList> ExecSpAsync(this NucleinDbContext db, string sql, SqlParameter[] sqlParams)
    {
      var connection = db.Database.GetDbConnection();
      using (var cmd = connection.CreateCommand())
      {
        await db.Database.OpenConnectionAsync();
        cmd.CommandText = sql;
        cmd.CommandType = System.Data.CommandType.StoredProcedure;
        if(sqlParams!=null)
          cmd.Parameters.AddRange(sqlParams);
        var dr = await cmd.ExecuteReaderAsync();
        var columnSchema = dr.GetColumnSchema();
        var data = new ArrayList();
        while (await dr.ReadAsync())
        {
          var item = new Dictionary<string, object>();
          foreach (var kv in columnSchema)
          {
            if (kv.ColumnOrdinal.HasValue)
            {
              var itemVal = dr.GetValue(kv.ColumnOrdinal.Value);
              item.Add(kv.ColumnName, itemVal.GetType() != typeof(DBNull) ? itemVal : "");
            }
          }
          data.Add(item);
        }
        dr.Dispose();
        return data;
      }
    }

    /// <summary>
    /// 增删改
    /// </summary>
    /// <returns></returns>
    public async static Task<int> ExecuteNonQueryAsync(this NucleinDbContext db, string sql, SqlParameter[] sqlParams)
    {
      int numint;
      var connection = db.Database.GetDbConnection();
      using (var cmd = connection.CreateCommand())
      {
        await db.Database.OpenConnectionAsync();
        cmd.CommandText = sql;
        cmd.CommandType = System.Data.CommandType.StoredProcedure;
        cmd.Parameters.AddRange(sqlParams);
        numint = await cmd.ExecuteNonQueryAsync();
      }
      return numint;
    }
  }
}

*
*
*
*
*