public void TestSelectSentenceBuilder1A() { var ssb = new SelectStatementBuilder("UserTable", null, new Range(1, 10)); ssb.Keys.Add(new KeyValuePair<string, string>("a", null)); const string s = "SELECT TOP 10 [a] FROM [UserTable];\n<Text><60>()"; Assert.AreEqual(s, ssb.ToSqlStatement(dd, null).ToString()); }
public override SqlStatement GetPagedSelectSqlStatement(SelectStatementBuilder ssb, List<string> queryRequiredFields) { SqlStatement sql = ssb.GetNormalSelectSqlStatement(this, queryRequiredFields); sql.SqlCommandText = string.Format("{0} LIMIT {1}, {2}", sql.SqlCommandText, ssb.Range.Offset, ssb.Range.Rows); return sql; }
public void TestSelectSentenceBuilder2() { var ssb = new SelectStatementBuilder("UserTable", null, new Range(1, 10)); ssb.Keys.Add(new KeyValuePair<string, string>("a", null)); ssb.Where.Conditions = new OrClause("ID", 5, 3, 2); const string s = "SELECT TOP 10 [a] FROM [UserTable] WHERE ([ID] = @ID_0) OR ([ID] = @ID_1) OR ([ID] = @ID_2);\n<Text><60>(@ID_0=5:Int32,@ID_1=3:Int32,@ID_2=2:Int32)"; TesterHelper.AssertSqlSentenceEqual(s, ssb.ToSqlStatement(dd, null).ToString()); }
public void TestSelectSentenceBuilder3() { var ssb = new SelectStatementBuilder("UserTable", null, new Range(1, 10)); ssb.Keys.Add(new KeyValuePair<string, string>("a", null)); ssb.Where.Conditions = new OrClause("ID", 5, 3, 2); ssb.Where.Conditions = new AndClause(ssb.Where.Conditions, new KeyValueClause("UserName", "l'lf", CompareOpration.Equal, ColumnFunction.None)); const string s = "SELECT TOP 10 [a] FROM [UserTable] WHERE (([ID] = @ID_0) OR ([ID] = @ID_1) OR ([ID] = @ID_2)) AND ([UserName] = @UserName_3);\n<Text><60>(@ID_0=5:Int32,@ID_1=3:Int32,@ID_2=2:Int32,@UserName_3=l'lf:String)"; TesterHelper.AssertSqlSentenceEqual(s, ssb.ToSqlStatement(dd, null).ToString()); }
public override SqlStatement GetPagedSelectSqlStatement(SelectStatementBuilder ssb, List<string> queryRequiredFields) { var dpc = new DataParameterCollection(); string sqlString = string.Format("SELECT TOP {4} {0} FROM {1}{2}{3}", ssb.GetColumns(this), ssb.From.ToSqlText(dpc, this), ssb.Where.ToSqlText(dpc, this, queryRequiredFields), (ssb.Order == null || ssb.Keys.Count == 0) ? "" : ssb.Order.ToSqlText(dpc, this), ssb.Range.EndIndex ); return new TimeConsumingSqlStatement(CommandType.Text, sqlString, dpc); }
public virtual SqlStatement GetGroupBySumStatement(Condition iwc, OrderBy order, string groupbyColumnName, string sumColumnName) { var builder = new SelectStatementBuilder(Context.Info.From, order, null); builder.Where.Conditions = iwc; var list = groupbyColumnName.Split(','); foreach (string s in list) { builder.Keys.Add(new KeyValuePair<string, string>(s, null)); builder.SetAsGroupBySum(groupbyColumnName, sumColumnName); } return builder.ToSqlStatement(Context); }
public void TestSelectSentenceBuilder0() { var ssb = new SelectStatementBuilder( "UserTable" ); ssb.SetCountColumn("*"); string s = "SELECT COUNT(*) AS it__count__ FROM [UserTable];\n"; Assert.AreEqual(s, ssb.ToSqlStatement(dd, null).SqlCommandText); ssb = new SelectStatementBuilder("UserTable"); ssb.SetCountColumn("abc"); s = "SELECT COUNT([abc]) AS it__count__ FROM [UserTable];\n"; Assert.AreEqual(s, ssb.ToSqlStatement(dd, null).SqlCommandText); ssb = new SelectStatementBuilder("UserTable"); ssb.Keys.Add(new KeyValuePair<string, string>("zzz", null)); ssb.SetCountColumn("abc"); s = "SELECT [zzz],COUNT([abc]) AS it__count__ FROM [UserTable];\n"; Assert.AreEqual(s, ssb.ToSqlStatement(dd, null).SqlCommandText); }
public override SqlStatement GetPagedSelectSqlStatement(SelectStatementBuilder ssb, List<string> queryRequiredFields) { if (ssb.Order == null || ssb.Keys.Count == 0) { throw PagedMustHaveOrder; } const string posName = "__rownumber__"; var dpc = new DataParameterCollection(); string sqlString = string.Format( "SELECT {7} FROM (SELECT {0}, ROW_NUMBER() OVER ({3}) AS {6} FROM {1} {2}) AS T WHERE T.{6} >= {4} AND T.{6} <= {5} ORDER BY T.{6}", ssb.GetColumns(this), ssb.From.ToSqlText(dpc, this), ssb.Where.ToSqlText(dpc, this, queryRequiredFields), ssb.Order.ToSqlText(dpc, this), ssb.Range.StartIndex, ssb.Range.EndIndex, posName, ssb.GetColumns(this, false, true) ); return new TimeConsumingSqlStatement(CommandType.Text, sqlString, dpc); }
public override SqlStatement GetPagedSelectSqlStatement(SelectStatementBuilder ssb, List<string> queryRequiredFields) { SqlStatement sql = ssb.GetNormalSelectSqlStatement(this, queryRequiredFields); sql.SqlCommandText = string.Format("SELECT * FROM ( SELECT ROW_.*, ROWNUM ROWNUM_ FROM ( {0} ) ROW_ WHERE ROWNUM <= {1} ) WHERE ROWNUM_ >= {2}", sql.SqlCommandText, ssb.Range.EndIndex, ssb.Range.StartIndex); return sql; }
public virtual SqlStatement GetPagedSelectSqlStatement(SelectStatementBuilder ssb, List<string> queryRequiredFields) { throw DoesNotSupportPagedSelect; }
public override SqlStatement GetPagedSelectSqlStatement(SelectStatementBuilder ssb, List<string> queryRequiredFields) { return ssb.GetNormalSelectSqlStatement(this, queryRequiredFields); }
private void SetSelectColumnsForDynamicLinqObject(SelectStatementBuilder sb, Type returnType) { var handler = DynamicLinqObjectHandler.Factory.GetInstance(returnType); handler.Init(Context.Info); foreach (MemberHandler fi in handler.GetMembers()) { string value = null; if (fi.Name != fi.MemberInfo.Name) { value = fi.MemberInfo.Name; } sb.Keys.Add(new KeyValuePair<string, string>(fi.Name, value)); } }
public virtual SqlStatement GetSumStatement(Condition iwc, string columnName) { var builder = new SelectStatementBuilder(this.Context.Info.From, null, null); builder.Where.Conditions = iwc; builder.SetSumColumn(columnName); return builder.ToSqlStatement(this.Context); }
public virtual SelectStatementBuilder GetSelectStatementBuilder(FromClause from, Condition iwc, OrderBy oc, Range lc, bool isDistinct, bool noLazy, Type returnType, string colName) { var sb = new SelectStatementBuilder(from ?? this.Context.Info.From, oc, lc) { IsDistinct = isDistinct, NoLazy = noLazy }; sb.Where.Conditions = iwc; if (!colName.IsNullOrEmpty()) { sb.Keys.Add(new KeyValuePair<string, string>(colName, null)); return sb; } if (returnType.Name.StartsWith("<")) { this.SetSelectColumnsForDynamicLinqObject(sb, returnType); return sb; } this.Context.Handler.SetValuesForSelect(sb, noLazy); return sb; }
public virtual SqlStatement GetResultCountStatement(Condition iwc, bool isDistinct) { var builder2 = new SelectStatementBuilder(this.Context.Info.From, null, null) {IsDistinct = isDistinct}; SelectStatementBuilder isv = builder2; isv.Where.Conditions = iwc; if (isDistinct) { this.Context.Handler.SetValuesForSelect(isv, false); string columnName = isv.GetColumns(this.Context.Provider.Dialect, true, false); isv.SetCountColumn(columnName); isv.IsDistinct = false; isv.Keys.Clear(); } else { isv.SetCountColumn("*"); } return isv.ToSqlStatement(this.Context); }
public void TestSelectSentenceBuilder1() { var ssb = new SelectStatementBuilder( "UserTable", null, new Range(1, 10) ); ssb.ToSqlStatement(dd, null); }