qlhb/Ropin.Inspection.Repository/Base/EntityFrameworkCoreExtensions.cs

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Infrastructure;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace Ropin.Inspection.Repository
{
    public static class EntityFrameworkCoreExtensions
    {
        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.IsMySql())
            {
                cmd.CommandText = sql;
                if(null != parameters)
                cmd.Parameters.AddRange(parameters);
            }
            return cmd;
        }

        public static DataTable SqlQuery(this DatabaseFacade facade, string sql, params object[] parameters)
        {
            DataTable dt = new DataTable();
            try
            {
                var command = CreateCommand(facade, sql, out DbConnection conn, parameters);
                var reader = command.ExecuteReader();
                
                dt.Load(reader);
                reader.Close();
                conn.Close();
                
            }
            catch(System.Data.DataException e)
            {
                System.Data.DataRow[] rowsInError;
                System.Text.StringBuilder sbError = new System.Text.StringBuilder();
                // Test if the table has errors. If not, skip it.
                if (dt.HasErrors)
                {
                    // Get an array of all rows with errors.
                    rowsInError = dt.GetErrors();
                    // Print the error of each column in each row.
                    for (int i = 0; i < rowsInError.Length; i++)
                    {
                        foreach (System.Data.DataColumn column in dt.Columns)
                        {
                            sbError.Append(column.ColumnName + " " + rowsInError[i].GetColumnError(column));
                        }
                        // Clear the row errors
                        rowsInError[i].ClearErrors();
                    }
                }
                string time = System.DateTime.Now.ToString();
                Console.WriteLine("======================");
                Console.WriteLine(time);
                Console.WriteLine("----------------------");
                Console.WriteLine(sbError.ToString());
                Console.WriteLine("----------------------");
                Console.WriteLine(e.ToString());
            }
            finally {

                
            }
            return dt;

        }
        public static DataTable SqlQuery2(this DatabaseFacade facade, string sql, params object[] parameters)
        {
            //using (SqlConnection connection = new SqlConnection("server=117.78.26.193;user id=root;password=Xhrz@2018;port=8635;persistsecurityinfo=True;database=JX_LH_Inspection;sslMode=None"))
            //{
            //    DataSet ds = new DataSet();
            //    connection.Open();
            //    SqlDataAdapter command = new SqlDataAdapter(sql, connection);
            //    command.Fill(ds);
            //    return ds.Tables[0];
            //}


            //string data = string.Empty;
            var command = CreateCommand(facade, sql, out DbConnection conn, parameters);
            DataTable OutDataTable = new DataTable();
            var daReader = command.ExecuteReader();
            DataRow dataRow;

            //设置Table名和Column名
            OutDataTable.TableName = "Ropin";
            for (int j = 0; j < daReader.FieldCount; j++)
            {
                //获取列名
                OutDataTable.Columns.Add(daReader.GetName(j), daReader.GetFieldType(j));
            }

            //循环取数据集合中的数据,存到DataTable中
            do
            {
                while (daReader.Read())
                {
                    dataRow = OutDataTable.NewRow();
                    for (int j = 0; j < daReader.FieldCount; j++)
                    {
                        var data = daReader[j];
                        dataRow[j] = data;
                    }
                    OutDataTable.Rows.Add(dataRow);
                }
            } while (daReader.NextResult());
            daReader.Close();
            conn.Close();

            return OutDataTable;
        }

        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> SqlQuery2<T>(this DatabaseFacade facade, string sql, params object[] parameters) where T : class, new()
        {
            var dt = SqlQuery2(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;
        }
        /// <summary>
        /// 执行SQL返回受影响的行数
        /// </summary>
        public static int ExecuteSqlNoQuery(this DatabaseFacade facade, string sql, DbParameter[] sqlParams = null)
        {
            return ExecuteNoQuery(facade, sql, sqlParams);
        }
        ///// <summary>
        ///// 执行存储过程返回IEnumerable数据集
        ///// </summary>
        //public static IEnumerable<T> ExecuteStoredProcedure<T>(this IDbContextCore context, string sql, DbParameter[] sqlParams = null) where T : new()
        //{
        //    return Execute<T>(context, sql, CommandType.StoredProcedure, sqlParams);
        //}
        ///// <summary>
        ///// 执行sql返回IEnumerable数据集
        ///// </summary>
        //public static IEnumerable<T> ExecuteSqlReader<T>(this IDbContextCore context, string sql, DbParameter[] sqlParams = null) where T : new()
        //{
        //    return Execute<T>(context, sql, CommandType.Text, sqlParams);
        //}
        private static int ExecuteNoQuery(this DatabaseFacade facade, string sql, DbParameter[] sqlParams)
        {
            var db = facade;
            var connection = db.GetDbConnection();
            var cmd = connection.CreateCommand();
            if (connection.State == ConnectionState.Closed)
                connection.Open();
            cmd.CommandText = sql;
            cmd.CommandType = CommandType.Text;
            if (sqlParams != null)
            {
                cmd.Parameters.AddRange(sqlParams);
            }
            var result = cmd.ExecuteNonQuery();
            connection.Close();
            return result;
        }

        /// <summary>
        /// 带分页，执行不带参数的sql语句，返回list
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="strsql"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <returns></returns>
        public static IEnumerable<T> GetList<T>(this DatabaseFacade facade, string strsql, DbParameter[] sqlParams, int pageSize, int pageIndex) where T : class, new()
        {
            var query = SqlQuery<T>(facade, strsql, sqlParams)
              .Skip((pageIndex - 1) * pageSize)
              .Take(pageSize)
              .Select(u => u).DefaultIfEmpty().ToList();
            return query;
        }

        public static IEnumerable<T> GetList<T>(this DatabaseFacade facade, string strsql, DbParameter[] sqlParams) where T : class, new()
        {
            var query = SqlQuery<T>(facade, strsql, sqlParams)
              .Select(u => u).DefaultIfEmpty().ToList();
            return query;
        }
        /// <summary>
        /// 集合为null时返回null（排除集合中第一个数据为null）
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="facade"></param>
        /// <param name="strsql"></param>
        /// <param name="sqlParams"></param>
        /// <returns></returns>
        public static IEnumerable<T> GetLists<T>(this DatabaseFacade facade, string strsql, DbParameter[] sqlParams) where T : class, new()
        {
            List<T> list = SqlQuery<T>(facade, strsql, sqlParams);
            if (list.Count > 0)
            {
                var query = list.Select(u => u).DefaultIfEmpty().ToList();
                return query;
            }
            else
            {
                return null;
            }
        }
        public static IEnumerable<T> GetList2<T>(this DatabaseFacade facade, string strsql, DbParameter[] sqlParams) where T : class, new()
        {
            var query = SqlQuery2<T>(facade, strsql, sqlParams)
              .Select(u => u).DefaultIfEmpty().ToList();
            return query;
        }
        //private static IEnumerable<T> Execute<T>(this DatabaseFacade facade, string sql, CommandType type, params DbParameter[] sqlParams) where T : new()
        //{
        //    var db = facade;
        //    var connection = db.GetDbConnection();
        //    var cmd = connection.CreateCommand();
        //    if (connection.State == ConnectionState.Closed)
        //        connection.Open();
        //    cmd.CommandText = sql;
        //    cmd.CommandType = type;
        //    if (sqlParams != null)
        //    {
        //        cmd.Parameters.AddRange(sqlParams);
        //    }

        //    var reader = cmd.ExecuteReader();
        //    var dt = new DataTable();
        //    dt.Load(reader);
        //    reader.Close();
        //    connection.Close();
        //    return dt;

        //}

        //public static void ClearDatabase(this IDbContextCore context)
        //{
        //    context.ClearDataTables();
        //}

        //public static void ClearDataTables(this IDbContextCore context, params string[] tables)
        //{
        //    if (tables == null)
        //    {
        //        var tableList = new List<string>();
        //        var types = context.GetAllEntityTypes();
        //        if (types.Any())
        //        {
        //            foreach (var type in types)
        //            {
        //                var tableName = type.ClrType.GetCustomAttribute<TableAttribute>()?.Name;
        //                if (tableName.IsNullOrWhiteSpace())
        //                    tableName = type.ClrType.Name;
        //                tableList.Add(tableName);
        //            }
        //        }
        //        else
        //        {
        //            tableList.AddRange(context.GetCurrentDatabaseTableList().Select(m => m.TableName));
        //        }

        //        tables = tableList.ToArray();
        //    }

        //    var sql = new StringBuilder();
        //    foreach (var table in tables)
        //    {
        //        sql.AppendLine($"delete from {table};");
        //    }
        //    context.ExecuteSqlWithNonQuery(sql.ToString());
        //}

        //public static object ExecuteScalar(this IDbContextCore context, string sql, params DbParameter[] sqlParams)
        //{
        //    var db = context.GetDatabase();
        //    var connection = db.GetDbConnection();
        //    var cmd = connection.CreateCommand();
        //    if (connection.State == ConnectionState.Closed)
        //        connection.Open();
        //    cmd.CommandText = sql;
        //    cmd.CommandType = CommandType.Text;
        //    if (sqlParams != null)
        //    {
        //        cmd.Parameters.AddRange(sqlParams);
        //    }

        //    var result = cmd.ExecuteScalar();
        //    connection.Close();
        //    return result;
        //}
    }
}