public void Delete()
{
DeleteQuery query = new DeleteQuery("products");
query.WhereClause.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field("productId"), SqlExpression.Number(999), CompareOperator.Equal));
RenderDelete(query);
}
public virtual void Union()
{
SqlUnion union = new SqlUnion();
SelectQuery query = new SelectQuery();
query.Columns.Add(new SelectColumn(SqlExpression.Raw("price * 10"), "priceX10"));
query.FromClause.BaseTable = FromTerm.Table("products");
union.Add(query);
query = new SelectQuery();
query.Columns.Add(new SelectColumn(SqlExpression.Field("price"), "priceX10"));
query.FromClause.BaseTable = FromTerm.Table("products");
union.Add(query, DistinctModifier.All);
query = new SelectQuery();
query.Columns.Add(new SelectColumn(SqlExpression.Field("price"), "priceX10"));
query.FromClause.BaseTable = FromTerm.Table("products");
union.Add(query, DistinctModifier.Distinct);
string sql = Renderer.RenderUnion(union);
Console.WriteLine(sql);
RunSql(sql);
}
/// <summary>
/// Create a sub-select statement for a query condition where the operator specifies that the specified multivalued attribute value must not exist
/// </summary>
/// <param name="builder">The builder for this query</param>
/// <param name="parameterNames">The parameters to evaluate against</param>
/// <returns>An SQL SELECT statement</returns>
private string CreateSubSelectStatementForMVNotEquals(DBQueryBuilder builder, IList <string> parameterNames)
{
string paramNameAttribute = builder.AddParameter(this.SearchAttribute.Name);
WhereClause subClause = new WhereClause(WhereClauseRelationship.Or);
WhereClause existWhere = new WhereClause(WhereClauseRelationship.And);
AcmaSchemaAttribute objectIdAttribute = ActiveConfig.DB.GetAttribute("objectId");
existWhere.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field("objectId", this.SearchAttribute.DBTable), SqlExpression.Field(objectIdAttribute.Name, objectIdAttribute.DBTable), Reeb.SqlOM.CompareOperator.Equal));
existWhere.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field("attributeName", this.SearchAttribute.DBTable), SqlExpression.Parameter(paramNameAttribute), Reeb.SqlOM.CompareOperator.Equal));
foreach (string paramNameValue in parameterNames)
{
subClause.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field(this.SearchAttribute.ColumnName, this.SearchAttribute.DBTable), SqlExpression.Parameter(paramNameValue), Reeb.SqlOM.CompareOperator.Equal));
}
existWhere.SubClauses.Add(subClause);
SelectQuery subQuery = new SelectQuery();
subQuery.Columns.Add(new SelectColumn("objectId"));
subQuery.Top = 1;
subQuery.FromClause.BaseTable = this.SearchAttribute.DBTable;
subQuery.WherePhrase.SubClauses.Add(existWhere);
Reeb.SqlOM.Render.SqlServerRenderer render = new Reeb.SqlOM.Render.SqlServerRenderer();
string sql = render.RenderSelect(subQuery);
return(sql);
}
WhereTerm CreateRangeTerm(PivotColumn pivotCol, PivotColumnValue pivotColValue)
{
Range step = pivotColValue.Range;
SqlExpression fieldExpr = SqlExpression.Field(pivotCol.ColumnField);
if (step.HighBound == null && step.LowBound == null)
{
throw new PivotTableException("At least one bound of a Range must be set.");
}
SqlExpression lowBoundExpr = (step.LowBound != null) ? SqlExpression.Constant(pivotCol.DataType, pivotColValue.Range.LowBound) : null;
SqlExpression highBoundExpr = (step.HighBound != null) ? SqlExpression.Constant(pivotCol.DataType, pivotColValue.Range.HighBound) : null;
WhereTerm term;
if (step.HighBound == null)
{
term = WhereTerm.CreateCompare(fieldExpr, lowBoundExpr, CompareOperator.GreaterOrEqual);
}
else if (step.LowBound == null)
{
term = WhereTerm.CreateCompare(fieldExpr, highBoundExpr, CompareOperator.Less);
}
else
{
term = WhereTerm.CreateBetween(fieldExpr, lowBoundExpr, highBoundExpr);
}
return(term);
}
public void Update()
{
UpdateQuery query = new UpdateQuery("products");
query.Terms.Add(new UpdateTerm("price", SqlExpression.Number(12.1)));
query.Terms.Add(new UpdateTerm("quantaty", SqlExpression.Field("quantaty")));
query.WhereClause.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field("productId"), SqlExpression.Number(1), CompareOperator.Equal));
RenderUpdate(query);
}
WhereTerm CreateColumnValueCondition(PivotColumn col, PivotColumnValue val)
{
if (val.ValueType == PivotColumnValueType.Scalar)
{
return(WhereTerm.CreateCompare(SqlExpression.Field(col.ColumnField), SqlExpression.Constant(new SqlConstant(col.DataType, val.Value)), CompareOperator.Equal));
}
else
{
return(CreateRangeTerm(col, val));
}
}
SqlExpression PivotCaseExpression(PivotColumn col, PivotColumnValue val)
{
CaseClause caseClause = new CaseClause();
caseClause.ElseValue = SqlExpression.Null();
CaseTerm term = new CaseTerm(PivotCaseCondition(col, val), SqlExpression.Field(valueField));
caseClause.Terms.Add(term);
return(SqlExpression.Case(caseClause));
}
/// <summary>
/// Creates a SelectColumn with a column name, table, column alias and optional aggregation function
/// </summary>
/// <param name="columnName">Name of a column</param>
/// <param name="table">The table this field belongs to</param>
/// <param name="columnAlias">Alias of the column</param>
/// <param name="function">Aggregation function to be applied to the column. Use SqlAggregationFunction.None to specify that no function should be applied.</param>
public SelectColumn(string columnName, FromTerm table, string columnAlias, SqlAggregationFunction function)
{
if (function == SqlAggregationFunction.None)
{
expr = SqlExpression.Field(columnName, table);
}
else
{
expr = SqlExpression.Function(function, SqlExpression.Field(columnName, table));
}
this.alias = columnAlias;
}
public override void HandleQuickFilters(poSelectQuery query, List <powFilterField> filterFields)
{
base.HandleQuickFilters(query, filterFields);
foreach (var field in filterFields)
{
if (field.FieldName == "heName")
{
var value = $"%{field.FieldValue}%";
query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field("heName", query.BaseTable), query.ParameterValue(value), CompareOperator.Like));
}
}
}
public virtual void ComplicatedQuery()
{
FromTerm tCustomers = FromTerm.Table("customers", "c");
FromTerm tProducts = FromTerm.Table("products", "p");
FromTerm tOrders = FromTerm.Table("orders", "o");
SelectQuery query = new SelectQuery();
query.Columns.Add(new SelectColumn("name", tCustomers));
query.Columns.Add(new SelectColumn(SqlExpression.IfNull(SqlExpression.Field("name", tCustomers), SqlExpression.Constant(SqlConstant.String("name"))), "notNull"));
query.Columns.Add(new SelectColumn(SqlExpression.Null(), "nullValue"));
query.Columns.Add(new SelectColumn("name", tProducts, "productName", SqlAggregationFunction.None));
query.Columns.Add(new SelectColumn("price", tProducts));
query.FromClause.BaseTable = tCustomers;
query.FromClause.Join(JoinType.Left, tCustomers, tOrders, "customerId", "customerId");
query.FromClause.Join(JoinType.Inner, tOrders, tProducts, "productId", "productId");
query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field("name", tCustomers), SqlExpression.String("John"), CompareOperator.Equal));
query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.String("Dohe"), SqlExpression.Field("name", tCustomers), CompareOperator.NotEqual));
query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field("name", tCustomers), SqlExpression.String("J%"), CompareOperator.Like));
query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Date(DateTime.Now), SqlExpression.Field("date", tOrders), CompareOperator.Greater));
query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Number(10), SqlExpression.Number(9), CompareOperator.Greater));
query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Number(10), SqlExpression.Number(9), CompareOperator.GreaterOrEqual));
query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Number(10), SqlExpression.Number(11.5), CompareOperator.LessOrEqual));
query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Number(1), SqlExpression.Number(1), CompareOperator.BitwiseAnd));
WhereClause group = new WhereClause(WhereClauseRelationship.Or);
group.Terms.Add(WhereTerm.CreateBetween(SqlExpression.Field("price", tProducts), SqlExpression.Number(1), SqlExpression.Number(10)));
group.Terms.Add(WhereTerm.CreateIn(SqlExpression.Field("name", tProducts), SqlConstantCollection.FromList(new string[] { "Nail", "Hamer", "Skrewdriver" })));
group.Terms.Add(WhereTerm.CreateIn(SqlExpression.Field("name", tProducts), "select name from products"));
group.Terms.Add(WhereTerm.CreateNotIn(SqlExpression.Field("name", tProducts), SqlConstantCollection.FromList(new string[] { "Unkown" })));
group.Terms.Add(WhereTerm.CreateNotIn(SqlExpression.Field("name", tProducts), "select name from products"));
group.Terms.Add(WhereTerm.CreateIsNull(SqlExpression.Field("name", tProducts)));
group.Terms.Add(WhereTerm.CreateIsNotNull(SqlExpression.Field("name", tProducts)));
group.Terms.Add(WhereTerm.CreateExists("select productId from products"));
group.Terms.Add(WhereTerm.CreateNotExists("select productId from products"));
query.WherePhrase.SubClauses.Add(group);
query.OrderByTerms.Add(new OrderByTerm("name", tCustomers, OrderByDirection.Descending));
query.OrderByTerms.Add(new OrderByTerm("price", OrderByDirection.Ascending));
query.Distinct = true;
query.Top = 10;
RenderSelect(query);
}
/// <summary>
/// Builds the query
/// </summary>
/// <param name="whereClause">The WHERE conditions</param>
/// <param name="maxResults">The number of results to return</param>
/// <returns>The constructed SQL query</returns>
private string BuildQuery(WhereClause whereClause, int maxResults)
{
SelectQuery query = new SelectQuery();
AcmaSchemaAttribute objectIdAttribute = ActiveConfig.DB.GetAttribute("objectId");
FromTerm objectBaseTable = objectIdAttribute.DBTable;
query.Columns.Add(new SelectColumn("*", objectBaseTable));
query.Distinct = true;
query.FromClause.BaseTable = objectBaseTable;
foreach (FromTerm term in this.attributeTables)
{
query.FromClause.Join(JoinType.Left, objectBaseTable, term, new JoinCondition("objectId", "objectId"));
}
if (maxResults > 0)
{
query.Top = maxResults;
}
//if (this.parameters.Count == 0)
//{
// return null;
//}
// Prevents returning the querying object as a search result
if (Guid.Empty != this.SourceObjectId)
{
string param = this.AddParameter(this.SourceObjectId);
query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field(objectIdAttribute.Name, objectIdAttribute.DBTable), SqlExpression.Parameter(param), CompareOperator.NotEqual));
}
if (this.OrderByTerms != null)
{
query.OrderByTerms.AddRange(this.OrderByTerms);
}
query.WherePhrase.SubClauses.Add(whereClause);
SqlServerRenderer renderer = new SqlServerRenderer();
return(renderer.RenderSelect(query));
}
private void AppendLikeExpression(string[] words, SelectQuery selectQuery, WhereClause group, string columnName)
{
foreach (string word in words)
{
if (word.Length == 0)
{
continue;
}
var parameter = new Parameter("%" + word + "%");
group.Terms.Add(
WhereTerm.CreateCompare(SqlExpression.Field(columnName, selectQuery.FromClause.BaseTable),
SqlExpression.Parameter(), CompareOperator.Like));
selectQuery.Parameters.Add(parameter);
}
}
public virtual void Join5()
{
FromTerm tCustomers = FromTerm.Table("customers");
FromTerm tProducts = FromTerm.Table("products", "p");
FromTerm tOrders = FromTerm.Table("orders", "o");
SelectQuery query = new SelectQuery();
query.Columns.Add(new SelectColumn("name", tCustomers));
query.Columns.Add(new SelectColumn("name", tProducts));
query.FromClause.BaseTable = tCustomers;
query.FromClause.Join(JoinType.Left, tCustomers, tOrders, new JoinCondition("customerId"), new JoinCondition("customerId"));
WhereClause condition = new WhereClause(WhereClauseRelationship.Or);
condition.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field("productId", tOrders), SqlExpression.Field("productId", tProducts), CompareOperator.Equal));
condition.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field("orderId", tOrders), SqlExpression.Field("productId", tProducts), CompareOperator.Equal));
query.FromClause.Join(JoinType.Left, tOrders, tProducts, condition);
RenderSelect(query);
}
public virtual void SubQuery()
{
FromTerm tCustomers = FromTerm.Table("customers");
SelectQuery subQuery = new SelectQuery();
subQuery.Top = 1;
subQuery.Columns.Add(new SelectColumn("name", tCustomers));
subQuery.FromClause.BaseTable = FromTerm.Table("customers");
SelectQuery query = new SelectQuery();
query.Columns.Add(new SelectColumn("name", tCustomers));
query.Columns.Add(new SelectColumn(SqlExpression.SubQuery("select count(*) from customers"), "cnt"));
query.Columns.Add(new SelectColumn(SqlExpression.SubQuery(subQuery), "subq"));
query.FromClause.BaseTable = tCustomers;
query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field("customerId", tCustomers), SqlExpression.SubQuery("select customerId from customers where name='John'"), CompareOperator.Equal));
RenderSelect(query);
}
public virtual void Having()
{
FromTerm tCustomers = FromTerm.Table("customers");
FromTerm tProducts = FromTerm.Table("products", "p");
FromTerm tOrders = FromTerm.Table("orders", "o");
SelectQuery query = new SelectQuery();
query.Columns.Add(new SelectColumn("name", tCustomers));
query.Columns.Add(new SelectColumn("price", tProducts, "sum", SqlAggregationFunction.Sum));
query.FromClause.BaseTable = tCustomers;
query.FromClause.Join(JoinType.Inner, query.FromClause.BaseTable, tOrders, "customerId", "customerId");
query.FromClause.Join(JoinType.Inner, tOrders, tProducts, "productId", "productId");
query.GroupByTerms.Add(new GroupByTerm("name", tCustomers));
query.HavingPhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field("name", tCustomers), SqlExpression.String("John"), CompareOperator.Equal));
RenderSelect(query);
}
/// <summary>
/// Creates a <see cref="SelectQuery"/> which produces drill-down results
/// </summary>
/// <param name="crossTabRowKey">Value identifying cross-tab's row</param>
/// <param name="crossTabColumnName">Name of a cross-tab column</param>
/// <returns>A <see cref="SelectQuery"/> which produces drill-down results of the specified cross-tab cell</returns>
public SelectQuery BuildDrillDownSql(SqlConstant crossTabRowKey, string crossTabColumnName)
{
Validate();
SelectQuery query = new SelectQuery();
query.Columns.Add(new SelectColumn("*"));
query.FromClause.BaseTable = GetBaseFromTerm();
PivotColumn pivotCol;
PivotColumnValue pivotVal;
if (!FindPivotColumnValue(crossTabColumnName, out pivotCol, out pivotVal))
{
throw new PivotTableException(string.Format("Cross-Tab column '{0}' could not be found in pivot transformation definition.", crossTabColumnName));
}
query.WherePhrase.Terms.Add(CreateColumnValueCondition(pivotCol, pivotVal));
if (crossTabRowKey != null)
{
query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field(rowField), SqlExpression.Constant(crossTabRowKey), CompareOperator.Equal));
}
return(query);
}
protected override poSelectQuery BuildQuery()
{
var selQuery = new poSelectQuery("heItems");
selQuery.AddColumn("heID", selQuery.BaseTable, "ID");
selQuery.AddColumn("heCode", selQuery.BaseTable, "heCode");
selQuery.AddColumn("heName", selQuery.BaseTable, "heName");
selQuery.AddColumns("heFactoryCode", "heSeasID", "heAMsntID", "heAuxiliaryCode", "heDetailedDescr", "heClassification", "heKind",
"heCompID", "heActive", "heNameSoundex", "heRefNumber", "heIaccID", "heProductionCatID", "heBlockSales", "heBlockPurchases", "heBlockWarehouses", "hePartInStockControl"
, "heSalInvAttrSpPrice", "hePurInvAttrSpPrice", "heCat01ID", "heCat02ID", "HECALCFROMVALUE",
"heRetailPrice", "heWholeSalesPrice");
selQuery.AddColumns("heSplrID", "heType");
selQuery.WherePhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field("heCompID", selQuery.FromClause.BaseTable), selQuery.ParameterValue(AppContext.GetService <heSystemParams>().CurrentCompanyID), CompareOperator.Equal));
selQuery.WherePhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field("heType", selQuery.FromClause.BaseTable), selQuery.ParameterValue(Convert.ToInt16(hewItemsDataObjectProxy.ItemTypeEnum.Service)), CompareOperator.Equal));
selQuery.AddEnumColumn(AppContext, selQuery.FromClause.BaseTable, "Hercules;heItems;heKind");
selQuery.AddEnumColumn(AppContext, selQuery.FromClause.BaseTable, "Hercules;heItems;heClassification");
var AMesUnits = FromTerm.Table("heMeasurementUnits", "MU1");
selQuery.AddColumn("heName", AMesUnits, "AMUNAME");
selQuery.FromClause.Join(JoinType.Inner, selQuery.BaseTable, AMesUnits, "heAMsntID", "heID");
var vats = FromTerm.Table("heVATClasses", "Vats");
selQuery.AddColumn("heName", vats, "VTCLNAME");
selQuery.FromClause.Join(JoinType.Inner, selQuery.FromClause.BaseTable, vats, "heVtclID", "heID");
var iaccs = FromTerm.Table("heItemAccCategories", "Iacc");
selQuery.AddColumn("heName", iaccs, "IaccName");
selQuery.FromClause.Join(JoinType.Left, selQuery.FromClause.BaseTable, iaccs, "heIaccID", "heID");
return(selQuery);
}
/// <summary>
/// Renders a SELECT statement which a result-set page
/// </summary>
/// <param name="pageIndex">The zero based index of the page to be returned</param>
/// <param name="pageSize">The size of a page</param>
/// <param name="totalRowCount">Total number of rows the query would yeild if not paged</param>
/// <param name="query">Query definition to apply paging on</param>
/// <returns>Generated SQL statement</returns>
/// <remarks>
/// To generate pagination SQL you must supply <paramref name="totalRowCount"/>.
/// To aquire the total number of rows use the <see cref="RenderRowCount"/> method.
/// </remarks>
public virtual string RenderPage(int pageIndex, int pageSize, int totalRowCount, SelectQuery query)
{
if (query.OrderByTerms.Count == 0)
{
throw new InvalidQueryException("OrderBy must be specified for paging to work.");
}
int currentPageSize = pageSize;
if (pageSize * (pageIndex + 1) > totalRowCount)
{
currentPageSize = totalRowCount - pageSize * pageIndex;
}
if (currentPageSize < 0)
{
currentPageSize = 0;
}
SelectQuery baseQuery = query.Clone();
baseQuery.Top = (pageIndex + 1) * pageSize;
//baseQuery.Columns.Add(new SelectColumn("*"));
foreach (OrderByTerm term in baseQuery.OrderByTerms)
{
baseQuery.Columns.Add(
new SelectColumn(term.Field, term.Table, FormatSortFieldName(term.Field),
SqlAggregationFunction.None));
}
string baseSql = RenderSelect(baseQuery);
SelectQuery reverseQuery = new SelectQuery();
reverseQuery.Columns.Add(new SelectColumn("*"));
reverseQuery.Top = currentPageSize;
reverseQuery.FromClause.BaseTable = FromTerm.SubQuery(baseSql, "r");
ApplyOrderBy(baseQuery.OrderByTerms, reverseQuery, false, reverseQuery.FromClause.BaseTable);
string reverseSql = RenderSelect(reverseQuery);
SelectQuery forwardQuery = new SelectQuery();
foreach (SelectColumn originalCol in query.Columns)
{
FromTerm forwardTable = FromTerm.TermRef("f");
SqlExpression expr = null;
if (originalCol.ColumnAlias != null)
{
expr = SqlExpression.Field(originalCol.ColumnAlias, forwardTable);
}
else if (originalCol.Expression.Type == SqlExpressionType.Field ||
originalCol.Expression.Type == SqlExpressionType.Constant)
{
expr = SqlExpression.Field((string)originalCol.Expression.Value, forwardTable);
}
if (expr != null)
{
forwardQuery.Columns.Add(new SelectColumn(expr, originalCol.ColumnAlias));
}
}
forwardQuery.FromClause.BaseTable = FromTerm.SubQuery(reverseSql, "f");
ApplyOrderBy(baseQuery.OrderByTerms, forwardQuery, true, forwardQuery.FromClause.BaseTable);
return(RenderSelect(forwardQuery));
}
/// <summary>
/// Creates a WHERE clause for a multivalued attribute
/// </summary>
/// <param name="builder">The builder for this query</param>
/// <param name="parameterNames">The parameters to evaluate against</param>
/// <returns>The WhereClause object for this query</returns>
private WhereClause CreateWhereClauseforAVPTarget(DBQueryBuilder builder, IList <string> parameterNames)
{
FromTerm fromTable;
fromTable = builder.GetNextAVPTableReference(this.SearchAttribute.TableName);
WhereClause clause = new WhereClause(WhereClauseRelationship.And);
if (this.Operator != ValueOperator.NotPresent)
{
string paramName = builder.AddParameter(this.SearchAttribute.Name);
WhereTerm attributeNameTerm = WhereTerm.CreateCompare(SqlExpression.Field("attributeName", fromTable), SqlExpression.Parameter(paramName), CompareOperator.Equal);
clause.Terms.Add(attributeNameTerm);
}
if (this.Operator == ValueOperator.IsPresent)
{
clause.Terms.Add(WhereTerm.CreateIsNotNull(SqlExpression.Field(this.SearchAttribute.ColumnName, fromTable)));
}
else if (this.Operator == ValueOperator.NotPresent)
{
string sql = this.CreateSubSelectStatementForMVNotExists(builder);
clause.Terms.Add(WhereTerm.CreateNotExists(sql));
}
else
{
WhereClause subClause = new WhereClause(WhereClauseRelationship.Or);
switch (this.Operator)
{
case ValueOperator.And:
case ValueOperator.Contains:
case ValueOperator.GreaterThan:
case ValueOperator.GreaterThanOrEq:
case ValueOperator.LessThan:
case ValueOperator.StartsWith:
case ValueOperator.EndsWith:
case ValueOperator.LessThanOrEq:
foreach (string parameterName in parameterNames)
{
subClause.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field(this.SearchAttribute.ColumnName, fromTable), SqlExpression.Parameter(parameterName), this.CompareOperator));
}
break;
case ValueOperator.Equals:
subClause.Terms.Add(WhereTerm.CreateIn(SqlExpression.Field(this.SearchAttribute.ColumnName, fromTable), parameterNames.ToCommaSeparatedString()));
break;
case ValueOperator.NotEquals:
string sql = this.CreateSubSelectStatementForMVNotEquals(builder, parameterNames);
clause.Terms.Add(WhereTerm.CreateNotExists(sql));
break;
default:
throw new InvalidOperationException();
}
clause.SubClauses.Add(subClause);
}
return(clause);
}
/// <summary>
/// Creates a WHERE clause for a single-valued attribute
/// </summary>
/// <param name="parameterNames">The parameters to evaluate against</param>
/// <returns>The WhereClause object for this query</returns>
private WhereClause CreateWhereClauseforSVTarget(IList <string> parameterNames)
{
FromTerm fromTable;
fromTable = this.SearchAttribute.DBTable;
WhereClause clause = new WhereClause(WhereClauseRelationship.And);
if (this.Operator == ValueOperator.IsPresent)
{
clause.Terms.Add(WhereTerm.CreateIsNotNull(SqlExpression.Field(this.SearchAttribute.ColumnName, fromTable)));
}
else if (this.Operator == ValueOperator.NotPresent)
{
clause.Terms.Add(WhereTerm.CreateIsNull(SqlExpression.Field(this.SearchAttribute.ColumnName, fromTable)));
}
else if (parameterNames.Count == 0)
{
throw new ArgumentNullException("parameterNames");
}
else if (parameterNames.Count > 1)
{
WhereClause subClause = new WhereClause(WhereClauseRelationship.Or);
switch (this.Operator)
{
case ValueOperator.And:
case ValueOperator.Contains:
case ValueOperator.StartsWith:
case ValueOperator.EndsWith:
case ValueOperator.GreaterThan:
case ValueOperator.GreaterThanOrEq:
case ValueOperator.LessThan:
case ValueOperator.LessThanOrEq:
foreach (string parameterName in parameterNames)
{
subClause.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field(this.SearchAttribute.ColumnName, fromTable), SqlExpression.Parameter(parameterName), this.CompareOperator));
}
break;
case ValueOperator.Equals:
subClause.Terms.Add(WhereTerm.CreateIn(SqlExpression.Field(this.SearchAttribute.ColumnName, fromTable), parameterNames.ToCommaSeparatedString()));
break;
case ValueOperator.NotEquals:
subClause.Terms.Add(WhereTerm.CreateNotIn(SqlExpression.Field(this.SearchAttribute.ColumnName, fromTable), parameterNames.ToCommaSeparatedString()));
break;
default:
throw new InvalidOperationException();
}
clause.SubClauses.Add(subClause);
}
else
{
clause.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field(this.SearchAttribute.ColumnName, fromTable), SqlExpression.Parameter(parameterNames[0]), this.CompareOperator));
}
return(clause);
}
public virtual void Parameter()
{
FromTerm tCustomers = FromTerm.Table("customers");
SelectQuery query = new SelectQuery();
query.Columns.Add(new SelectColumn("name", tCustomers));
query.FromClause.BaseTable = tCustomers;
query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Parameter(GetParameterName("pName")), SqlExpression.Field("name", tCustomers), CompareOperator.Equal));
string sql = Renderer.RenderSelect(query);
Console.WriteLine(sql);
if (connection != null)
{
IDbCommand command = connection.CreateCommand();
command.CommandText = sql;
command.Parameters.Add(CreateParameter("@pName", "John"));
command.ExecuteNonQuery();
}
}
