使用EFCore执行上下文不存在的SQL语句
时间：2021-07-01 10:21:17
帮助过：24人阅读
System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Reflection;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Infrastructure;
namespace ZR.OtnWebService.Models.OrderInfos
{
public static class DbContextExtensions
{
private static void CombineParams(
ref DbCommand command,
params object[] parameters)
{
if (parameters !=
null)
{
foreach (SqlParameter parameter
in parameters)
{
if (!parameter.ParameterName.Contains(
"@"))
parameter.ParameterName = $
"@{parameter.ParameterName}";
command.Parameters.Add(parameter);
}
}
}
private static DbCommand CreateCommand(DatabaseFacade facade,
string sql,
out DbConnection dbConn,
params object[] parameters)
{
DbConnection conn =
facade.GetDbConnection();
dbConn =
conn;
conn.Open();
DbCommand cmd =
conn.CreateCommand();
if (facade.IsMySql())
{
cmd.CommandText =
sql;
CombineParams(ref cmd, parameters);
}
return cmd;
}
public static DataTable SqlQuery(
this 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>(
this DatabaseFacade facade,
string sql,
params object[] parameters)
where T :
class,
new()
{
DataTable dt =
SqlQuery(facade, sql, parameters);
return dt.ToEnumerable<T>
();
}
public static IEnumerable<T> ToEnumerable<T>(
this 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;
}
}
}
使用方法
DbContext.Database.SqlQuery<Model>($"SELECT * FROM Table").FirstOrDefault();
使用EFCore执行上下文不存在的SQL语句
标签：on() mes sof struct ++ return ref pre parameter
