public static List <T> Join2Tables <T>(this IDbConnection thisObj, SQLJoinType join_type, List <string> colsA, List <string> colsB, string tableA, string tableB, string keyA, string keyB, string where = null, string orderby = null, int limit = -1, int offset = -1) { try { string sql = @"SELECT "; if (colsA != null) { for (int i = 0; i < colsA.Count; i++) { sql += " Tba." + colsA[i] + ","; } } if (colsB != null) { for (int i = 0; i < colsB.Count; i++) { sql += " Tbb." + colsB[i] + ","; } } // Remove last , sql = sql.Substring(0, sql.Length - 1); string jt = join_type.ToString(); sql += " FROM " + tableA + " Tba " + jt + " JOIN " + tableB + " Tbb ON Tba.\"" + keyA + "\" = Tbb.\"" + keyB + "\" "; if (where != null) { sql += " WHERE " + where + " "; } if (orderby != null) { sql += "ORDER BY " + orderby + " "; } if (limit > 0) { sql += " LIMIT " + limit.ToString(); } if (offset > 0) { sql += " OFFSET " + offset.ToString(); } sql += ";"; return(thisObj.SqlList <T>(sql)); } catch { return(null); } }
public static String PrepareFilterExpression(String exp1, SQLJoinType option, String exp2) { String expression = ""; switch (option) { case SQLJoinType.AND: expression = String.Format("({0} AND {1})", exp1, exp2); break; case SQLJoinType.OR: expression = String.Format("({0} OR {1})", exp1, exp2); break; default: break; } return(expression); }