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 SqlQuery(this DatabaseFacade facade, string sql, params object[] parameters) where T : class, new() { var dt = SqlQuery(facade, sql, parameters); return dt.ToList(); } public static List SqlQuery2(this DatabaseFacade facade, string sql, params object[] parameters) where T : class, new() { var dt = SqlQuery2(facade, sql, parameters); return dt.ToList(); } public static List ToList(this DataTable dt) where T : class, new() { var propertyInfos = typeof(T).GetProperties(); var list = new List(); 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; } /// /// 执行SQL返回受影响的行数 /// public static int ExecuteSqlNoQuery(this DatabaseFacade facade, string sql, DbParameter[] sqlParams = null) { return ExecuteNoQuery(facade, sql, sqlParams); } ///// ///// 执行存储过程返回IEnumerable数据集 ///// //public static IEnumerable ExecuteStoredProcedure(this IDbContextCore context, string sql, DbParameter[] sqlParams = null) where T : new() //{ // return Execute(context, sql, CommandType.StoredProcedure, sqlParams); //} ///// ///// 执行sql返回IEnumerable数据集 ///// //public static IEnumerable ExecuteSqlReader(this IDbContextCore context, string sql, DbParameter[] sqlParams = null) where T : new() //{ // return Execute(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; } /// /// 带分页,执行不带参数的sql语句,返回list /// /// /// /// /// /// public static IEnumerable GetList(this DatabaseFacade facade, string strsql, DbParameter[] sqlParams, int pageSize, int pageIndex) where T : class, new() { var query = SqlQuery(facade, strsql, sqlParams) .Skip((pageIndex - 1) * pageSize) .Take(pageSize) .Select(u => u).DefaultIfEmpty().ToList(); return query; } public static IEnumerable GetList(this DatabaseFacade facade, string strsql, DbParameter[] sqlParams) where T : class, new() { var query = SqlQuery(facade, strsql, sqlParams) .Select(u => u).DefaultIfEmpty().ToList(); return query; } /// /// 集合为null时返回null(排除集合中第一个数据为null) /// /// /// /// /// /// public static IEnumerable GetLists(this DatabaseFacade facade, string strsql, DbParameter[] sqlParams) where T : class, new() { List list = SqlQuery(facade, strsql, sqlParams); if (list.Count > 0) { var query = list.Select(u => u).DefaultIfEmpty().ToList(); return query; } else { return null; } } public static IEnumerable GetList2(this DatabaseFacade facade, string strsql, DbParameter[] sqlParams) where T : class, new() { var query = SqlQuery2(facade, strsql, sqlParams) .Select(u => u).DefaultIfEmpty().ToList(); return query; } //private static IEnumerable Execute(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(); // var types = context.GetAllEntityTypes(); // if (types.Any()) // { // foreach (var type in types) // { // var tableName = type.ClrType.GetCustomAttribute()?.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; //} } }