public static void AddWhereCondition(this SQlQuery query, string whereCondition) { if (!string.IsNullOrEmpty(whereCondition)) { query.WhereCondition.Add(whereCondition); } }
/// <summary> /// "Any" keyword is used without expression /// Way to use: /// Condition[Table].Any() /// </summary> public static bool Any <T>(IDbConnection db) { SQlQuery sql = new SQlQuery(TableHelper.GetTableName <T>()); string newSql = @"SELECT CASE WHEN ( EXISTS ( " + sql.Query + " )) THEN cast(1 as bit) ELSE cast(0 as bit) END"; var result = db.Query <bool>(newSql).FirstOrDefault(); return(result); }
//public static int Count<TSource>(this IEnumerable<TSource> source, Func<TSource, bool> predicate); //public static int Count<TSource>(this IEnumerable<TSource> source); //public static decimal? Min(this IEnumerable<decimal?> source); //public static decimal? Sum<TSource>(this IEnumerable<TSource> source, Func<TSource, int> selector); //public static decimal? Max<TSource>(this IEnumerable<TSource> source, Func<TSource, decimal?> selector); #endregion #region Count /// <summary> /// result[0] = result[1] /// </summary> //private static string[] GetForeignKeyColumn(Type table1, Type table2) //{ // string[] result = new string[2]; // PropertyInfo[] table1PInfos = table1.GetProperties(); // string table1NameInDb = TableHelper.GetTableName(table1); // string table2NameInDb = TableHelper.GetTableName(table2); // PropertyInfo pForeignKeyInfosTable1 = table1PInfos.FirstOrDefault(p => p.GetCustomAttributes(true) // .Any(a => (a as ForeignKeyAttribute) != null && (a as ForeignKeyAttribute).Name == table2NameInDb)); // if (pForeignKeyInfosTable1 != null) // { // result[0] = table1NameInDb + '.' + pForeignKeyInfosTable1.Name; // result[1] = table2NameInDb + '.' + pForeignKeyInfosTable1.Name; // return result; // } // else // { // PropertyInfo[] table2PInfos = table2.GetProperties(); // PropertyInfo pForeignKeyInfosTable2 = table2PInfos.FirstOrDefault(p => p.GetCustomAttributes(true) // .Any(a => (a as ForeignKeyAttribute) != null && (a as ForeignKeyAttribute).Name == table1NameInDb)); // if (pForeignKeyInfosTable2 != null) // { // result[0] = table1NameInDb + '.' + pForeignKeyInfosTable1.Name; // result[1] = table2NameInDb + '.' + pForeignKeyInfosTable1.Name; // return result; // } // //External fields of table 1 // //search id of table 2 in table 1 // PropertyInfo primaryKeyTable2 = table2PInfos.FirstOrDefault(p => string.Compare(p.Name, "id", true) == 0); // if (primaryKeyTable2 == null) // primaryKeyTable2 = table2PInfos.FirstOrDefault(p => p.GetCustomAttributes(true).Any(a => (a as PrimaryKey) != null)); // if (primaryKeyTable2 == null) // throw new Exception("Could not found the primary key in entity: " + table1.FullName); // PropertyInfo allPExternalTable2InTable1 = table1PInfos.FirstOrDefault(p => string.Compare(p.Name, primaryKeyTable2.Name, true) == 0); // if (allPExternalTable2InTable1 != null) // primary key of table 2 is existed in table 1 // { // result[0] = table1NameInDb + '.' + primaryKeyTable2.Name; // result[1] = table2NameInDb + '.' + primaryKeyTable2.Name; // return result; // } // else // { // //search id of table 1 in table 2 // PropertyInfo primaryKeyTable1 = table1PInfos.FirstOrDefault(p => string.Compare(p.Name, "id", true) == 0); // if (primaryKeyTable1 == null) // primaryKeyTable1 = table1PInfos.FirstOrDefault(p => p.GetCustomAttributes(true).Any(a => (a as PrimaryKey) != null)); // if (primaryKeyTable1 == null) // throw new Exception("Could not found the primary key in entity: " + table1.FullName); // PropertyInfo pExternalTable1InTable2 = table2PInfos.FirstOrDefault(p => string.Compare(p.Name, primaryKeyTable1.Name, true) == 0); // if (pExternalTable1InTable2 != null) // { // result[0] = table1NameInDb + '.' + primaryKeyTable1.Name; // result[1] = table2NameInDb + '.' + primaryKeyTable1.Name; // return result; // } // else // throw new Exception("Could not found the foreign key between two entities: " + table1.FullName + " and " + table2.FullName); // } // } //} //private static string GetPrimaryIdColumn(Type table) //{ // PropertyInfo[] table1PInfos = table.GetProperties(); // PropertyInfo primaryKeyTable1 = // table1PInfos.FirstOrDefault(p => string.Compare(p.Name, "id", true) == 0); // if (primaryKeyTable1 == null) // primaryKeyTable1 = table1PInfos.FirstOrDefault(p => p.GetCustomAttributes(true).Any(a => (a as PrimaryKey) != null)); // if (primaryKeyTable1 == null) // throw new Exception("Count not find the primaryKey of table: " + table.FullName); // return primaryKeyTable1.Name; //} /// <summary> /// "Count" keyword is used without expression -- /// Way to use: [Table].Count() /// </summary> public static int Count <T>(IDbConnection db) { SQlQuery query = new SQlQuery(TableHelper.GetTableName <T>()); string newSql = @"SELECT COUNT(1) FROM ( " + query.Query + " ) as Extend"; var result = db.Query <int>(newSql).FirstOrDefault(); return(result); }
public static QueryResult <T> Get <T>(Expression <Func <T, bool> > expression) { var body = expression.Body as BinaryExpression; SQlQuery query = new SQlQuery(TableHelper.GetTableName <T>()); WalkTree <T>(expression, ref query); return(new QueryResult <T>(query)); }
public static void AddJoinOperator(this SQlQuery query, List <string> joinOperator) { if (joinOperator != null && joinOperator.Any()) { query.JoinContidion.AddRange(joinOperator); } query.JoinContidion = query.JoinContidion.Distinct().ToList(); }
/// <summary> /// "Any" keyword is used with expression /// Way to use: /// Any[Table](x=> x.id = 10); /// Find[Table](x=> x.id = 10).Any(x=>x.id = 10); /// </summary> public static bool Any <T>(IDbConnection db, Expression <Func <T, bool> > expression) { var body = expression.Body as BinaryExpression; SQlQuery query = new SQlQuery(TableHelper.GetTableName <T>()); WalkTree <T>(expression, ref query); string newSql = @"SELECT CASE WHEN ( EXISTS ( " + query.Query + " )) THEN cast(1 as bit) ELSE cast(0 as bit) END"; var result = db.Query <bool>(newSql, (object)query.Param).FirstOrDefault(); return(result); }
/// <summary> /// "Count" keyword is used without expression -- /// Way to use: [Table].Count(expression) /// </summary> public static int Count <T>(Expression <Func <T, bool> > expression, IDbConnection db) { var body = expression.Body as BinaryExpression; SQlQuery query = new SQlQuery(TableHelper.GetTableName <T>()); WalkTree <T>(expression, ref query); string newSql = @"SELECT COUNT(1) FROM ( " + query.Query + " ) as Extend"; var result = db.Query <int>(newSql, (object)query.Param).FirstOrDefault(); return(result); }
public static QueryResult <T> Get <T>() { SQlQuery query = new SQlQuery(TableHelper.GetTableName <T>()); return(new QueryResult <T>(query)); }
public QuerySource(SQlQuery sql) { Sql = sql; }
///// <summary> ///// Gets the param. ///// </summary> ///// <value> ///// The param. ///// </value> //public dynamic Param //{ // get // { // return _result.Param; // } //} /// <summary> /// Initializes a new instance of the <see cref="QueryResult" /> class. /// </summary> /// <param name="sql">The SQL.</param> /// <param name="param">The param.</param> public QueryResult(SQlQuery sql) { _result = new QuerySource <SQlQuery>(sql); }
public static void AddQueryParameter(this SQlQuery query, QueryParameter parameters) { query.QueryParameters.Add(parameters); }