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