public void Add(DataParameterCollection dpc)
{
foreach ( DataParameter dp in dpc )
{
Add(dp);
}
}
protected override SqlStatement ToSqlStatement(DbDialect dd, List<string> queryRequiredFields)
{
var dpc = new DataParameterCollection();
string sqlString = string.Format(StatementTemplate, From.ToSqlText(dpc, dd), _valuesOptions.ToSqlText(dpc, dd));
var sql = new SqlStatement(CommandType.Text, sqlString, dpc);
return sql;
}
protected override SqlStatement ToSqlStatement(DbDialect dd, List<string> queryRequiredFields)
{
var dpc = new DataParameterCollection();
string sqlString = string.Format(StatementTemplate, dd.QuoteForTableName(_tableName), Where.ToSqlText(dpc, dd, queryRequiredFields));
var sql = new SqlStatement(CommandType.Text, sqlString, dpc);
return sql;
}
public override string ToSqlText(DataParameterCollection dpc, DbDialect dd, List<string> queryRequiredFields)
{
if (_ic.SubClauseNotEmpty)
{
return string.Format("( NOT ({0}) )", _ic.ToSqlText(dpc, dd, queryRequiredFields));
}
return "";
}
public void TestTwoWay()
{
Condition c = CK.K["Id"] > 5 || CK.K["Id"] < 9;
Condition c1 = CK.K["Id"] > 5 | CK.K["Id"] < 9;
var dpc = new DataParameterCollection();
var dpc1 = new DataParameterCollection();
Assert.AreEqual(c.ToSqlText(dpc, new SqlServer2000(), null), c1.ToSqlText(dpc1, new SqlServer2000(), null));
}
public void Test3()
{
Condition c = Condition.Empty;
c = ((c & null) | null);
var cc = new WhereClause(c);
var dpc = new DataParameterCollection();
Assert.AreEqual("", cc.ToSqlText(dpc, new Access(), null));
}
public void Test5()
{
Condition c = Condition.Empty;
c = c.And(CK.K["Id"].Eq(1)).Or(CK.K["Age"].Gt(18));
var cc = new WhereClause(c);
var dpc = new DataParameterCollection();
Assert.AreEqual(" WHERE ([Id] = @Id_0) OR ([Age] > @Age_1)", cc.ToSqlText(dpc, new Access(), null));
}
protected override string GetValueString(DataParameterCollection dpc, DbDialect dd, KeyValue kv)
{
if(kv.Value == null)
{
return "NULL";
}
return dd.QuoteForColumnName((string)kv.Value);
}
public void Test4()
{
Condition c = Condition.Empty;
c &= (CK.K["Id"] == 1 | CK.K["Age"] > 18);
var cc = new WhereClause(c);
var dpc = new DataParameterCollection();
Assert.IsFalse(c is EmptyCondition);
Assert.AreEqual(" WHERE ([Id] = @Id_0) OR ([Age] > @Age_1)", cc.ToSqlText(dpc, new Access(), null));
}
public string ToSqlText(DataParameterCollection dpc, Dialect.DbDialect dd)
{
return string.Format("{0}.{1} {4} {2}.{3}",
dd.QuoteForTableName(Table1),
dd.QuoteForColumnName(Key1),
dd.QuoteForTableName(Table2),
dd.QuoteForColumnName(Key2),
StringHelper.EnumToString(Comp));
}
public void Test3()
{
DataParameter d1 = new DataParameter(1);
DataParameter d2 = new DataParameter("@p2", 2);
DataParameter d3 = new DataParameter("@p3", 3);
DataParameterCollection dpc = new DataParameterCollection(d1,d2,d3);
Assert.AreEqual(d1, dpc[0]);
Assert.AreEqual(d2, dpc[1]);
Assert.AreEqual(d3, dpc[2]);
}
public void Test6()
{
Condition c = null;
c &= (CK.K["Id"] == 1 | CK.K["Age"] > 18);
c &= null;
c |= null;
c &= CK.K["Gender"] == true;
var cc = new WhereClause(c);
var dpc = new DataParameterCollection();
Assert.AreEqual(" WHERE (([Id] = @Id_0) OR ([Age] > @Age_1)) AND ([Gender] = @Gender_2)", cc.ToSqlText(dpc, new Access(), null));
}
public string ToSqlText(DataParameterCollection dpc, DbDialect dd, List<string> queryRequiredFields)
{
CheckInput();
string sqlString = string.Format("SELECT {0} FROM {1}{2}{3}{4}",
GetColumns(dd),
From.ToSqlText(dpc, dd),
Where.ToSqlText(dpc, dd, queryRequiredFields),
IsGroupBy ? " GROUP BY " + GetFunctionArgs(dd) : "",
(Order == null || Keys.Count == 0) ? "" : Order.ToSqlText(dpc, dd)
);
return sqlString;
}
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 string ToSqlText(DataParameterCollection dpc, Dialect.DbDialect dd)
{
if (OrderItems != null && OrderItems.Count > 0)
{
var sb = new StringBuilder(" ORDER BY ");
foreach (ASC oi in OrderItems)
{
sb.Append(oi.ToString(dd));
sb.Append(",");
}
if (sb.Length > 10) { sb.Length--; }
return sb.ToString();
}
return "";
}
public string ToSqlText(DataParameterCollection dpc, DbDialect dd, List<string> queryRequiredFields)
{
if ( _ic != null )
{
string s = _ic.ToSqlText(dpc, dd, queryRequiredFields);
if (s != null)
{
if (queryRequiredFields != null && !dpc.FindQueryRequiedFieldOrId)
{
throw new DataException("The QueryRequired fields not found in query.");
}
return (s.Length > 0) ? " WHERE " + s : "";
}
}
return "";
}
public override string ToSqlText(DataParameterCollection dpc, DbDialect dd, List<string> queryRequiredFields)
{
var sb = new StringBuilder();
foreach ( Condition ic in _list )
{
if (ic.SubClauseNotEmpty)
{
sb.Append("(");
sb.Append(ic.ToSqlText(dpc, dd, queryRequiredFields));
sb.Append(") ");
sb.Append(_condition);
sb.Append(" ");
}
}
string s = sb.ToString();
return ( s.Length > 5 ) ? s.Substring(0, s.Length - _condition.Length - 2) : "";
}
//internal FromClause(params string[] linkColumnNames)
//{
// if (linkColumnNames.Length < 2 || linkColumnNames.Length % 2 != 0)
// {
// throw new ArgumentException("LinkColumnNames.Length not even or less than 2.");
// }
// joinClauseList = new JoinClause[linkColumnNames.Length / 2];
// for (int i = 0; i < linkColumnNames.Length; i+=2)
// {
// joinClauseList[i / 2] = new JoinClause(linkColumnNames[i], linkColumnNames[i + 1],
// CompareOpration.Equal, JoinMode.Inner);
// }
//}
public string ToSqlText(DataParameterCollection dpc, Dialect.DbDialect dd)
{
if (MainTableName != null)
{
return dd.QuoteForTableName(MainTableName);
}
if (_fromStrings.Contains(dd))
{
return (string)_fromStrings[dd];
}
string ret;
if (PartOf != null)
{
ret = dd.QuoteForLimitTableName(ModelContext.GetInstance(PartOf).Info.From.MainTableName);
}
else
{
var sd = new StringDictionary();
ret = dd.QuoteForTableName(JoinClauseList[0].Table1);
sd.Add(ret, "");
for (int i = 0; i < JoinClauseList.Length; i++)
{
if (i != 0 && dd.NeedBracketForJoin)
{
ret = string.Format("({0})", ret);
}
string tn = dd.QuoteForTableName(JoinClauseList[i].Table2);
if (sd.ContainsKey(tn)) { tn = dd.QuoteForTableName(JoinClauseList[i].Table1); }
sd.Add(tn, "");
ret = string.Format("{0} {3} JOIN {1} ON {2}",
ret,
tn,
JoinClauseList[i].ToSqlText(dpc, dd),
JoinClauseList[i].Mode);
}
}
lock (_fromStrings.SyncRoot)
{
_fromStrings[dd] = ret;
}
return ret;
}
public override string ToSqlText(DataParameterCollection dpc, DbDialect dd, List<string> queryRequiredFields)
{
if (queryRequiredFields != null && dpc.FindQueryRequiedFieldOrId == false)
{
if(_column == "Id" || queryRequiredFields.Contains(_column))
{
dpc.FindQueryRequiedFieldOrId = true;
}
}
var sb = new StringBuilder();
sb.Append(dd.QuoteForColumnName(_column));
if (_notIn)
{
sb.Append(" NOT");
}
sb.Append(" IN (");
if(_args.Length == 1 && _args[0].GetType() == typeof(SqlStatement))
{
sb.Append(((SqlStatement)_args[0]).SqlCommandText);
}
else if (_args.Length == 1 && _args[0].GetType() == typeof(SelectStatementBuilder))
{
sb.Append(((SelectStatementBuilder)_args[0]).ToSqlText(dpc, dd, queryRequiredFields));
}
else
{
foreach (var o in _args)
{
var v = GetValueString(dpc, dd, new KeyValue("in", o));
sb.Append(v);
sb.Append(",");
}
if (_args.Length > 0)
{
sb.Length--;
}
}
sb.Append(")");
return sb.ToString();
}
public override string ToSqlText(DataParameterCollection dpc, DbDialect dd, List<string> queryRequiredFields)
{
if (queryRequiredFields != null && dpc.FindQueryRequiedFieldOrId == false)
{
if (KV.Key == "Id" || queryRequiredFields.Contains(KV.Key))
{
dpc.FindQueryRequiedFieldOrId = true;
}
}
string dpStr = GetValueString(dpc, dd, KV);
string dkStr = dd.QuoteForColumnName(KV.Key);
switch (function)
{
case ColumnFunction.ToLower:
dkStr = string.Format("LOWER({0})", dkStr);
break;
case ColumnFunction.ToUpper:
dkStr = string.Format("UPPER({0})", dkStr);
break;
}
return string.Format("{0} {2} {1}", dkStr, dpStr, Comp);
}
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 abstract string ToSqlText(DataParameterCollection dpc, DbDialect dd, List<string> queryRequiredFields);
public void Test8()
{
Condition c = Condition.Empty;
c = c.And(CK.K["Id"] == 1).Or(CK.K["Age"] > 18);
c = c.And(null);
c = c.Or(null);
c = c.And(CK.K["Gender"].Eq(true));
var cc = new WhereClause(c);
var dpc = new DataParameterCollection();
Assert.AreEqual(" WHERE (([Id] = @Id_0) OR ([Age] > @Age_1)) AND ([Gender] = @Gender_2)", cc.ToSqlText(dpc, new Access(), null));
}
public SqlStatement GetNormalSelectSqlStatement(DbDialect dd, List<string> queryRequiredFields)
{
var dpc = new DataParameterCollection();
var sqlString = ToSqlText(dpc, dd, queryRequiredFields);
return new TimeConsumingSqlStatement(CommandType.Text, sqlString, dpc);
}
protected virtual string GetValueString(DataParameterCollection dpc, DbDialect dd, KeyValue kv)
{
if(kv.Value == null)
{
return "NULL";
}
string dpStr;
if (DataSettings.UsingParameter)
{
dpStr = dd.QuoteParameter(string.Format("{0}_{1}", DataParameter.LegalKey(kv.Key), dpc.Count));
var dp = new DataParameter(dpStr, kv.NullableValue, kv.ValueType);
dpc.Add(dp);
}
else
{
dpStr = DataTypeParser.ParseToString(kv.Value, dd);
}
return dpStr;
}
public SqlStatement(CommandType sqlCommandType, string sqlCommandText, DataParameterCollection dpc)
: this(sqlCommandType, sqlCommandText)
{
Parameters.Add(dpc);
}
public void TestOr()
{
Condition c = CK.K["Id"] > 5 || CK.K["Id"] < 9;
var dpc = new DataParameterCollection();
Assert.AreEqual("([Id] > @Id_0) OR ([Id] < @Id_1)", c.ToSqlText(dpc, new SqlServer2000(), null));
}
public SqlStatement(CommandType sqlCommandType, string sqlCommandText)
{
this.SqlCommandType = sqlCommandType;
this.SqlCommandText = sqlCommandText;
Parameters = new DataParameterCollection();
}
public SqlStatement(string sqlCommandText, DataParameterCollection dpc)
: this( GetCommandType(sqlCommandText), sqlCommandText, dpc )
{
}
public SqlStatement GetSqlStatement(string sqlStr, params object[] os)
{
CommandType ct = SqlStatement.GetCommandType(sqlStr);
if (ct == CommandType.StoredProcedure)
{
return new SqlStatement(ct, sqlStr, os);
}
var dpc = new DataParameterCollection();
int start = 0, n = 0;
var sql = new StringBuilder();
foreach (Match m in Reg.Matches(sqlStr))
{
if (m.Length == 1)
{
string pn = Dialect.QuoteParameter("p" + n);
sql.Append(sqlStr.Substring(start, m.Index - start));
sql.Append(pn);
start = m.Index + 1;
var dp = new DataParameter(pn, os[n]);
dpc.Add(dp);
n++;
}
}
if (start < sqlStr.Length)
{
sql.Append(sqlStr.Substring(start));
}
var ret = new SqlStatement(ct, sql.ToString(), dpc);
return ret;
}
public SqlStatement(CommandType sqlCommandType, string sqlCommandText)
{
this.SqlCommandType = sqlCommandType;
this.SqlCommandText = sqlCommandText;
Parameters = new DataParameterCollection();
}
public TimeConsumingSqlStatement(string sqlCommandText, DataParameterCollection dpc) : base(sqlCommandText, dpc)
{
InitSqlTimeOut();
}
public SqlStatement(string sqlCommandText, DataParameterCollection dpc)
: this(GetCommandType(sqlCommandText), sqlCommandText, dpc)
{
}
public SqlStatement(CommandType sqlCommandType, string sqlCommandText, DataParameterCollection dpc)
: this(sqlCommandType, sqlCommandText)
{
Parameters.Add(dpc);
}
