【EF Core】执行存储过程扩展类

 

原文中没有汇入  直接调用proc 返回returnvalue和多表结果的方法

经过搜索整合了以上两种方法

 

主文件：ExtendDBContext

 public static class ExtendDBContext
    {

        /// <summary>
        /// 执行SQL返回受影响的行数
        /// </summary>
        public static int ExecSqlNoQuery<T>(this MyDbContext db, string sql, SqlParameter[] sqlParams = null) where T : new()
        {
            return ExecuteNoQuery<T>(db, sql, sqlParams);
        }

        /// <summary>
        /// 执行存储过程，返回return的值
        /// </summary>
        /// <param name="db"></param>
        /// <param name="procName">存储过程名称</param>
        /// <param name="sqlParams"></param>
        /// <returns></returns>
        public static int ExecProc(this MyDbContext db, string procName, SqlParameter[] sqlParams = null) 
        {
            return ExecuteProc(db, procName, sqlParams);
        }

        /// <summary>
        /// 执行存储过程，返回结果集
        /// </summary>
        /// <param name="db"></param>
        /// <param name="procName">存储过程名称</param>
        /// <param name="sqlParams"></param>
        /// <returns></returns>
        public static DataSet ExecProc(this MyDbContext db, string procName, SqlParameter[] sqlParams = null,string tableName = "")
        {
            return ExecuteProc(db, procName, sqlParams, tableName);
        }

        /// <summary>
        /// 执行存储过程返回IEnumerable数据集
        /// </summary>
        public static IEnumerable<T> ExecProcReader<T>(this MyDbContext 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 MyDbContext db, string sql, SqlParameter[] sqlParams = null) where T : new()
        {
            return Execute<T>(db, sql, CommandType.Text, sqlParams);
        }
        private static int ExecuteNoQuery<T>(this MyDbContext db, string sql, SqlParameter[] sqlParams) where T : new()
        {
            int result = 0;
            try
            {
                DbConnection connection = db.Database.GetDbConnection();
                DbCommand cmd = connection.CreateCommand();
                db.Database.OpenConnection();
                cmd.CommandText = sql;
                cmd.CommandType = CommandType.Text;
                if (sqlParams != null)
                {
                    cmd.Parameters.AddRange(sqlParams);
                }
                result = cmd.ExecuteNonQuery();
                db.Database.CloseConnection();
                
            }
            catch (SqlException ex)
            {
                Console.WriteLine(ex.Message);
            }
            return result;
        }

        /// <summary>
        /// 执行存储过程，返回return的结果（而非受影响的行数）
        /// </summary>
        /// <param name="db"></param>
        /// <param name="procName">存储过程名称</param>
        /// <param name="sqlParams">输入参数</param>
        /// <returns></returns>
        private static int ExecuteProc(this MyDbContext db, string procName, SqlParameter[] sqlParams)
        {
            int result = 0;
            int rowsAffected = 0;
            try
            {
                DbConnection connection = db.Database.GetDbConnection();
                DbCommand cmd = connection.CreateCommand();
                db.Database.OpenConnection();
                cmd.CommandText = procName;
                cmd.CommandType = CommandType.StoredProcedure;

                if (sqlParams != null)
                {
                    cmd.Parameters.AddRange(sqlParams);
                }

                cmd.Parameters.Add(new SqlParameter("ReturnValue",SqlDbType.Int, 4, ParameterDirection.ReturnValue,
                false, 0, 0, string.Empty, DataRowVersion.Default, null));

                rowsAffected = cmd.ExecuteNonQuery();   //这里返回的是受影响的行数
                result = (int)cmd.Parameters["ReturnValue"].Value;
                db.Database.CloseConnection();

            }
            catch (SqlException ex)
            {
                Console.WriteLine(ex.Message);
            }
            return result;
        }

        /// <summary>
        /// 执行存储过程，返回return的结果（而非受影响的行数）
        /// </summary>
        /// <param name="db"></param>
        /// <param name="procName">存储过程名称</param>
        /// <param name="sqlParams">输入参数</param>
        /// <returns></returns>
        private static DataSet ExecuteProc(this MyDbContext db, string procName, SqlParameter[] sqlParams,string tableName)
        {
            DataSet ds = new DataSet();
            try
            {
                DbConnection connection = db.Database.GetDbConnection();
                DbCommand cmd = connection.CreateCommand();

                DbProviderFactory dataFactory = DbProviderFactories.GetFactory(connection);
                DbDataAdapter da = dataFactory.CreateDataAdapter();
                da.SelectCommand = cmd;
                cmd.CommandText = procName;
                cmd.CommandType = CommandType.StoredProcedure;
                if (sqlParams != null)
                {
                    cmd.Parameters.AddRange(sqlParams);
                }

                da.Fill(ds, tableName);  // Fill the DataTable and bind the DataGrid
                db.Database.CloseConnection();
            }
            catch (SqlException ex)
            {
                Console.WriteLine(ex.Message);
            }
            return ds;
        }

        private static IEnumerable<T> Execute<T>(this MyDbContext 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>();
        }
    }

 

副文件：ExtendDataTable

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;
        }
    }

 

 

参考自：

https://www.cnblogs.com/ruanraun/p/efcore.html

https://bbs.csdn.net/topics/90455116

https://cloud.tencent.com/developer/ask/145864