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);
}
