//创建命令(同时返回连接符) 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)); }