EFCore 中执行存储过程返回DataSet DataTable

在项目中由于需求，需要返回复杂的数据，需要执行存储过程，但是在DONETCORE2.0中，看官网文档执行的sql的有点操蛋，满足不了需求，就想到了ADO.NET 

于是找资料，也没有合适的，就动手自己封装了一个。代码如下；

public static class ExtendDbRepository
    {
        public static DataSet SqlQuery(this Db db, string spName, params SqlParameter[] paramsters)
        {
            SqlConnection connection = db.Database.GetDbConnection() as SqlConnection;
            SqlDataAdapter adapter = null;
            DataSet set = null;
            using (SqlCommand command = new SqlCommand(spName, connection))
            {
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = spName;
                command.Parameters.AddRange(paramsters);
                adapter = new SqlDataAdapter(command);

                set = new DataSet();
                adapter.Fill(set);
                adapter.SelectCommand.Parameters.Clear();
                adapter.Dispose();
                command.Parameters.Clear();
                command.Dispose();
                connection.Close();
                connection.Dispose();
                return set;
            }
        }

        public static IEnumerable<TElement> SqlQuery<TElement>(this Db db, string sql, params object[] parameters) where TElement : new()
        {
            var connection = db.Database.GetDbConnection();
            using (var cmd = connection.CreateCommand())
            {
                db.Database.OpenConnection();
                cmd.CommandText = sql;
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.Parameters.AddRange(parameters);
                var dr = cmd.ExecuteReader();
                var columnSchema = dr.GetColumnSchema();
                var data = new List<TElement>();
                while (dr.Read())
                {
                    TElement item = new TElement();
                    Type type = item.GetType();
                    foreach (var kv in columnSchema)
                    {
                        var propertyInfo = type.GetProperty(kv.ColumnName);
                        if (kv.ColumnOrdinal.HasValue && propertyInfo != null)
                        {
                            //注意需要转换数据库中的DBNull类型
                            var value = dr.IsDBNull(kv.ColumnOrdinal.Value) ? null : dr.GetValue(kv.ColumnOrdinal.Value);
                            propertyInfo.SetValue(item, value);
                        }
                    }
                    data.Add(item);
                }
                dr.Dispose();
                return data;
            }
        }
    }