//创建命令（同时返回连接符）
private static DbCommand createCommand(DbContext context, DbContextSqlQueryCommands commands, out DbConnection connection)
{
var conn = context.Database.GetDbConnection();
connection = conn;
conn.Open();
var cmd = conn.CreateCommand();
if (commands.Sqlite != null && context.Database.IsSqlite())
{
cmd.CommandText = commands.Sqlite.Query;
combineParams(DbContextType.Sqlite, ref cmd, commands.Sqlite.Parameters);
}
else if (commands.MySql != null && context.Database.IsMySql())
{
cmd.CommandText = commands.MySql.Query;
combineParams(DbContextType.MySql, ref cmd, commands.MySql.Parameters);
}
else if (commands.Sql != null && context.Database.IsSqlServer())
{
cmd.CommandText = commands.Sql.Query;
combineParams(DbContextType.SqlServer, ref cmd, commands.Sql.Parameters);
}
return(cmd);
}
/// <summary>
/// 查询数据库，返回唯一数据
/// </summary>
/// <param name="context">EF上下文</param>
/// <param name="commands">数据库查询语句集合</param>
/// <returns>查询到的唯一数据</returns>
public static object QueryObject(this DbContext context, DbContextSqlQueryCommands commands)
{
var command = createCommand(context, commands, out var conn);
var rsl = command.ExecuteScalar();
conn.Close();
return(rsl);
}
/// <summary>
/// 查询数据库，返回多个查询结果集
/// </summary>
/// <param name="context">EF上下文</param>
/// <param name="commands">数据库查询语句集合</param>
/// <returns>数据DataSet</returns>
public static DataSet QuerySet(this DbContext context, DbContextSqlQueryCommands commands)
{
var dt = Query(context, commands);
var ds = new DataSet();
ds.Tables.Add(dt);
return(ds);
}
///// <summary>
///// 执行sql语句，返回受影响行数
///// </summary>
///// <param name="context">EF上下文</param>
///// <param name="commands">数据库查询语句集合</param>
///// <returns>受影响行数</returns>
//public static int Exec(this DbContext context, DbContextSqlQueryCommands commands)
//{
// var command = createCommand(context, commands, out var conn);
// var rsl = command.ExecuteNonQuery();
// conn.Close();
// return rsl;
//}
/// <summary>
/// 查询数据库
/// </summary>
/// <param name="context">EF上下文</param>
/// <param name="commands">数据库查询语句集合</param>
/// <returns>数据DataTable</returns>
public static DataTable Query(this DbContext context, DbContextSqlQueryCommands commands)
{
var command = createCommand(context, commands, out var conn);
var reader = command.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(reader);
reader.Close();
conn.Close();
return(dt);
}
/// <summary>
/// 查询数据库
/// </summary>
/// <param name="context">EF上下文</param>
/// <param name="commands">数据库查询语句集合</param>
/// <returns>数据DataTable</returns>
public static string jsonQuery(this DbContext context, DbContextSqlQueryCommands commands)
{
string jsonReturn = string.Empty;
var command = createCommand(context, commands, out var conn);
var reader = command.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(reader);
reader.Close();
conn.Close();
for (var i = 0; i < dt.Rows.Count; i++)
{
jsonReturn += (dt.Rows[i][0]).ToString();
}
return(jsonReturn);
}
/// <summary>
/// 查询数据库，返回唯一强类型数据
/// </summary>
/// <typeparam name="T">查询结果类型</typeparam>
/// <param name="context">EF上下文</param>
/// <param name="commands">数据库查询语句集合</param>
/// <returns>查询到的唯一强类型数据</returns>
public static T QueryObject <T>(this DbContext context, DbContextSqlQueryCommands commands)
{
return((T)QueryObject(context, commands));
}
/// <summary>
/// 查询数据库，返回第一条数据
/// </summary>
/// <param name="context">EF上下文</param>
/// <param name="commands">数据库查询语句集合</param>
/// <returns>查询到的第一条数据或null</returns>
public static DataRow QueryOne(this DbContext context, DbContextSqlQueryCommands commands)
{
var dt = Query(context, commands);
return(dt.Rows.Count > 0 ? dt.Rows[0] : null);
}
/// <summary>
/// 查询数据库，返回IEnumerable的强类型数据
/// </summary>
/// <typeparam name="T">查询结果类型</typeparam>
/// <param name="context">EF上下文</param>
/// <param name="commands">数据库查询语句集合</param>
/// <returns>IEnumerable的强类型数据</returns>
public static IEnumerable <T> Query <T>(this DbContext context, DbContextSqlQueryCommands commands)
{
var tb = Query(context, commands);
return(DataTableToList <T>(tb));
}
