EF Core中取消了EF中十分好用的SqlQuery方法
但是如果想用的话我们可以自己封装
面向CV编程吧
public static class DbContextExtensions
{
public static dynamic pagin<T>(DatabaseFacade facade, string SortField,string SqlBody, string SqlWhere, params object [] ParameterArr) where T : class, new()
{
string sql = "";
if (SqlWhere == null)
{
sql = $"select * from (select * from (select row_number() OVER (ORDER BY {SortField}) rowindex, {SqlBody})info limit @rowindex , @pagecount)t ";
}
else
{
sql = $"select * from (select * from (select row_number() OVER (ORDER BY {SortField}) rowindex, {SqlBody} where {SqlWhere} )info limit @rowindex , @pagecount)t ";
}
return SqlQuery<T>(facade, sql, ParameterArr);
}
//分页
public static dynamic pagin(DatabaseFacade facade, string SortField, string SqlBody,string SqlWhere, params object[] ParameterArr)
{
string sql ="";
if (SqlWhere == null)
{
sql = $"select * from (select * from (select row_number() OVER (ORDER BY {SortField}) rowindex, {SqlBody})info limit @rowindex , @pagecount)t ";
}
else
{
sql = $"select * from (select * from (select row_number() OVER (ORDER BY {SortField}) rowindex, {SqlBody} where {SqlWhere} )info limit @rowindex , @pagecount)t ";
}
return SqlQuery(facade, sql, ParameterArr);
}
//处理参数
private static void CombineParams(ref DbCommand command, params object[] parameters)
{
if (parameters != null)
{
foreach (MySqlParameter parameter in parameters)
{
if (!parameter.ParameterName.Contains("@"))
parameter.ParameterName = $"@{parameter.ParameterName}";
command.Parameters.Add(parameter);
}
}
}
//创建SQL命令
private static DbCommand CreateCommand(DatabaseFacade facade, string sql, out DbConnection dbConn, params object[] parameters)
{
DbConnection conn = facade.GetDbConnection();
dbConn = conn;
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
conn.Open();
DbCommand cmd = conn.CreateCommand();
if (facade.IsMySql())
{
cmd.CommandText = sql;
CombineParams(ref cmd, parameters);
}
return cmd;
}
//查询
public static DataTable SqlQuery( DatabaseFacade facade, string sql, params object[] parameters)
{
DbCommand cmd = CreateCommand(facade, sql, out DbConnection conn, parameters);
DbDataReader reader = cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(reader);
reader.Close();
conn.Close();
return dt;
}
//泛型查询
public static IEnumerable<T> SqlQuery<T>( DatabaseFacade facade, string sql, params object[] parameters) where T : class, new()
{
DataTable dt = SqlQuery(facade, sql, parameters);
return ToEnumerable<T>(dt);
}
//类型转换
public static IEnumerable<T> ToEnumerable<T>( DataTable dt) where T : class, new()
{
PropertyInfo[] propertyInfos = typeof(T).GetProperties();
T[] ts = new T[dt.Rows.Count];
int i = 0;
foreach (DataRow row in dt.Rows)
{
T 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);
}
ts[i] = t;
i++;
}
return ts;
}
}