public ISelectResult <TResult> Select <TResult>(Expression <Func <T, TResult> > sel) { var dynamicParameters = new DynamicParameters(); var selExp = new JoinSelectExpression(sel, new Dictionary <string, string> { { "", $"{this.DBModel.GetDBModel_SqlProvider().GetProviderOption().OpenQuote}jtmp{this.DBModel.GetDBModel_SqlProvider().GetProviderOption().CloseQuote}" } }, dynamicParameters, DBModel.GetDBModel_SqlProvider()); var selSql = string.Join(",", selExp.QueryColumns).Replace($"{this.DBModel.GetDBModel_SqlProvider().GetProviderOption().OpenQuote}jtmp{this.DBModel.GetDBModel_SqlProvider().GetProviderOption().CloseQuote}.", ""); var sql = new StringBuilder($"SELECT {selSql} FROM {this.DBModel.GetDBModel_SqlProvider().GetProviderOption().OpenQuote}{DBTool.GetTableName(this.DBModel)}{this.DBModel.GetDBModel_SqlProvider().GetProviderOption().CloseQuote} {DBTool.GetTableWith(this)} WHERE 1=1"); var countSql = new StringBuilder($"SELECT COUNT(1) FROM {DBTool.GetTableName(this.DBModel)} {DBTool.GetTableWith(this)} WHERE 1=1"); var whereExp = new WhereExpression(WhereExpression, "", dynamicParameters, DBModel.GetDBModel_SqlProvider()); sql.Append(whereExp.SqlCmd); countSql.Append(whereExp.SqlCmd); if (OrderbyList.Count > 0) { sql.Append($" ORDER BY "); var olst = OrderbyList.Select(m => { var order = new JoinOrderExpression(m.Field, new Dictionary <string, string> { { "", "" } }, dynamicParameters, DBModel.GetDBModel_SqlProvider()); return(order.SqlCmd + " " + m.OrderBy); }); sql.Append(string.Join(",", olst) + " "); } sql.Append(DBModel.GetDBModel_SqlProvider().GetLimit(StarSize, Rows)); var sr = new SelectResult <TResult>(sql.ToString(), countSql.ToString(), dynamicParameters); sr.DBModel = DBModel; return(sr); }
public ISelectResult <TResult> Select <TResult>(Expression <Func <T, TResult> > select, bool firstTableSelectAll = false) where TResult : new() { var p = new DynamicParameters(); p.AddDynamicParams(Param); var selectResult = new JoinSelectExpression(select, Map, p); var selSql = string.Join(",", selectResult.QueryColumns); if (firstTableSelectAll) { if (!string.IsNullOrEmpty(selSql)) { selSql = "t1.*," + selSql; } else { selSql = "t1.*"; } } var isDBModel = typeof(BaseDBModel).IsAssignableFrom(typeof(TResult)); if (isDBModel) { selSql = "0 as DBModel_IsBeginChange," + selSql; } var whereExpression = new JoinWhereExpression(WhereExpression, Map, p); var sql = new StringBuilder($"SELECT {selSql} FROM {JoinStr} WHERE 1=1 {Where} {whereExpression.SqlCmd} "); var countSql = new StringBuilder($"SELECT count(1) FROM {JoinStr} WHERE 1=1 {Where} {whereExpression.SqlCmd} "); if (OrderbyList.Count > 0) { sql.Append($" ORDER BY "); var olst = OrderbyList.Select(m => { var order = new JoinOrderExpression(m.Field, Map, p); return(order.SqlCmd + " " + m.OrderBy); }); sql.Append(string.Join(",", olst) + " "); } if (StarSize != 0 || Rows != 0) { sql.Append($" OFFSET {StarSize } ROWS "); if (Rows != 0) { sql.Append($" FETCH NEXT {Rows} ROWS ONLY "); } } return(new SelectResult <TResult>(sql.ToString(), countSql.ToString(), p)); }
private static string GetOrderBy(List <OrderCriteria> orderByLst, DynamicParameters dynamicParameters, ISqlProvider sqlProvider) { var orderByList = orderByLst.Select(a => { var o = new JoinOrderExpression(a.Field, new Dictionary <string, string> { { "", "" } }, dynamicParameters, sqlProvider); return(o.SqlCmd + (a.OrderBy == EOrderBy.Asc ? " ASC " : " DESC ")); }).ToList(); if (!orderByList.Any()) { return(""); } return("ORDER BY " + string.Join(",", orderByList)); }
public ISelectResult <TResult> Select <TResult>(Expression <Func <T, TResult> > sel) where TResult : new() { var dynamicParameters = new DynamicParameters(); var selExp = new JoinSelectExpression(sel, new Dictionary <string, string> { { "", "[jtmp]" } }, dynamicParameters); var selSql = string.Join(",", selExp.QueryColumns).Replace("[jtmp].", ""); var sql = new StringBuilder($"SELECT {selSql} FROM {DBTool.GetTableName(this.DBModel)} WHERE 1=1"); var countSql = new StringBuilder($"SELECT COUNT(1) FROM {DBTool.GetTableName(this.DBModel)} WHERE 1=1"); var whereExp = new WhereExpression(WhereExpression, "", dynamicParameters); sql.Append(whereExp.SqlCmd); countSql.Append(whereExp.SqlCmd); if (OrderbyList.Count > 0) { sql.Append($" ORDER BY "); var olst = OrderbyList.Select(m => { var order = new JoinOrderExpression(m.Field, new Dictionary <string, string> { { "", "" } }, dynamicParameters); return(order.SqlCmd + " " + m.OrderBy); }); sql.Append(string.Join(",", olst) + " "); } if (StarSize != 0 || Rows != 0) { sql.Append($" OFFSET {StarSize } ROWS "); if (Rows != 0) { sql.Append($" FETCH NEXT {Rows} ROWS ONLY "); } } return(new SelectResult <TResult>(sql.ToString(), countSql.ToString(), dynamicParameters)); }