public CSFilter(CSFilter filter1, string andOr, CSFilter filter2)
{
if (filter1.IsBlank && filter2.IsBlank)
{
_expression = "";
_parameters = new CSParameterCollection();
}
else if (filter1.IsBlank)
{
_expression = "(" + filter2.Expression + ")";
_parameters = new CSParameterCollection(filter2.Parameters);
return;
}
else if (filter2.IsBlank)
{
_expression = "(" + filter1.Expression + ")";
_parameters = new CSParameterCollection(filter1.Parameters);
}
else
{
_expression = "(" + filter1._expression + ") " + andOr + " (" + filter2.Expression + ")";
_parameters = new CSParameterCollection(filter1.Parameters);
_parameters.Add(filter2.Parameters);
}
}
public static CSList <T> OrderedList(string orderBy, CSFilter filter)
{
CSList <T> list = List(filter);
list.OrderBy = orderBy;
return(list);
}
public CSList <TObjectType> FilteredBy(CSFilter filter)
{
CSList <TObjectType> newCollection = Clone();
newCollection.Filter = Filter.And(filter);
return(newCollection);
}
public object GetScalar(string fieldName, CSAggregate aggregate, string filterExpression, CSParameterCollection filterParameters)
{
string tableAlias = CSNameGenerator.NextTableAlias;
CSFilter queryFilter = Filter.And(BuildRelationFilter(tableAlias));
if (!string.IsNullOrEmpty(filterExpression))
{
queryFilter = queryFilter.And(filterExpression, filterParameters);
}
return(CSObject <TObjectType> .GetScalar(fieldName, tableAlias, aggregate, queryFilter));
}
public static T ReadFirst(CSFilter filter)
{
CSList <T> objects = new CSList <T>(filter);
objects.MaxRecords = 1;
if (objects.Count < 1)
{
return(null);
}
else
{
return(objects[0]);
}
}
public void AddFilterOr(CSFilter filter)
{
Filter |= filter;
Refresh();
}
/// <summary>
/// TODO Completar documentación
/// </summary>
/// <param name="paramName"></param>
/// <param name="paramValueOrig"></param>
/// <param name="paramValueDest"></param>
/// <param name="strict"></param>
/// <returns></returns>
public CSFilter OrBetWeen(string paramName, object paramValueOrig, object paramValueDest, bool strict)
{
string expOrig = String.Format("{0}>{1}@{0}", paramName, (strict) ? "" : "=");
string expDest = String.Format("{0}<{1}@{0}", paramName, (strict) ? "" : "=");
CSFilter filter = new CSFilter(expOrig, String.Format("@{0}", paramName), paramValueOrig);
filter = filter.And(expDest, String.Format("@{0}", paramName), paramValueDest);
return new CSFilter(this, "OR", filter);
}
public CSFilter(CSFilter sourceFilter)
{
_expression = sourceFilter._expression;
_parameters = new CSParameterCollection(sourceFilter._parameters);
}
/// <summary>
/// Or's the specified filter.
/// </summary>
/// <param name="filterOr">The filter to be or'ed with this filter.</param>
/// <returns>A new filter.</returns>
public CSFilter Or(CSFilter filterOr)
{
return new CSFilter(this, "OR", filterOr);
}
/// <summary>
/// Ands the specified filter with this CSFilter
/// </summary>
/// <param name="filterOr">The CSfilter to be And'ed</param>
/// <returns>A new CSFilter</returns>
public CSFilter And(CSFilter filterOr)
{
return new CSFilter(this, "AND", filterOr);
}
public static int Count(CSFilter filter)
{
return(List(filter).CountFast);
}
public CSFilter(CSFilter filter1, AndOr andOr, CSFilter filter2)
{
if (filter1.IsBlank && filter2.IsBlank)
{
_expression = "";
_parameters = new CSParameterCollection();
}
else if (filter1.IsBlank)
{
_expression = "(" + filter2.Expression + ")";
_parameters = new CSParameterCollection(filter2.Parameters);
return;
}
else if (filter2.IsBlank)
{
_expression = "(" + filter1.Expression + ")";
_parameters = new CSParameterCollection(filter1.Parameters);
}
else
{
_expression = "(" + filter1._expression + ") " + andOr.ToString() + " (" + filter2.Expression + ")";
_parameters = new CSParameterCollection(filter1.Parameters);
_parameters.Add(filter2.Parameters);
}
}
public CSFilter(CSFilter sourceFilter)
{
_expression = sourceFilter._expression;
_parameters = new CSParameterCollection(sourceFilter._parameters);
}
public CSFilter And(CSFilter filterOr)
{
return(new CSFilter(this, "AND", filterOr));
}
private static object GetScalar(string fieldName, string tableAlias, CSAggregate aggregate, CSFilter queryFilter, string orderBy)
{
CSSchema schema = CSSchema.Get(typeof(T));
if (tableAlias == null)
{
tableAlias = CSNameGenerator.NextTableAlias;
}
if (orderBy == null)
{
orderBy = "";
}
string aggregateExpr = null;
int maxRows = 0;
switch (aggregate)
{
case CSAggregate.None: aggregateExpr = "{0}"; maxRows = 1; break;
case CSAggregate.Sum: aggregateExpr = "sum({0})"; break;
case CSAggregate.SumDistinct: aggregateExpr = "sum(distinct {0})"; break;
case CSAggregate.Count: aggregateExpr = "count(*)"; break;
case CSAggregate.CountDistinct: aggregateExpr = "count(distinct {0})"; break;
case CSAggregate.Avg: aggregateExpr = "avg({0})"; break;
case CSAggregate.AvgDistinct: aggregateExpr = "avg(distinct {0})"; break;
case CSAggregate.Max: aggregateExpr = "max({0})"; break;
case CSAggregate.Min: aggregateExpr = "min({0})"; break;
}
CSJoinList joins = new CSJoinList();
if (fieldName != "*")
{
fieldName = CSExpressionParser.ParseFilter(fieldName, schema, tableAlias, joins);
}
string whereFilter = CSExpressionParser.ParseFilter(queryFilter.Expression, schema, tableAlias, joins);
orderBy = CSExpressionParser.ParseOrderBy(orderBy, schema, tableAlias, joins);
string sqlQuery = schema.DB.BuildSelectSQL(schema.TableName, tableAlias, new[] { String.Format(aggregateExpr, fieldName) }, null, joins.BuildJoinExpressions(), whereFilter, orderBy, 1, maxRows, false, false);
return(schema.DB.GetScalar(sqlQuery, queryFilter.Parameters));
}
public CSList <TObjectType> Where(CSFilter filter)
{
return(FilteredBy(filter));
}
private static TReturn GetScalar <TReturn>(string fieldName, string tableAlias, CSAggregate aggregate, CSFilter queryFilter, string orderBy)
{
return(GetScalar(fieldName, tableAlias, aggregate, queryFilter, orderBy).Convert <TReturn>());
}
internal static TScalar GetScalar <TScalar>(string fieldName, string tableAlias, CSAggregate aggregate, CSFilter queryFilter)
{
return(GetScalar <TScalar>(fieldName, tableAlias, aggregate, queryFilter, null));
}
internal static TScalar GetScalar <TScalar>(string fieldName, string tableAlias, string orderBy, CSFilter queryFilter)
{
return(GetScalar <TScalar>(fieldName, tableAlias, CSAggregate.None, queryFilter, orderBy));
}
protected CSFilter BuildRelationFilter(string tableAlias)
{
if (tableAlias == null)
{
tableAlias = "";
}
else
tableAlias += ".";
if (Relation != null)
{
CSParameterCollection parameters = new CSParameterCollection();
switch (Relation.RelationType)
{
case CSSchemaRelationType.OneToMany:
{
//DAE 2010-10-08 Soporte para claves compuestas
//CSParameter csParameter = parameters.Add();
//csParameter.Value = RelationObject.Data[String.Format("#{0}", Relation.LocalKey)].Value;
//return new CSFilter(String.Format("{{{0}{1}}}={2}", tableAlias, Relation.ForeignKey, csParameter.Name), parameters);
//CSFilter filter = new CSFilter();
string sFilter = "";
for (int i = 0; i < Relation.LocalKeys.Count; i++)
{
CSParameter csParameter = parameters.Add();
csParameter.Value = RelationObject.Data[String.Format("#{0}", Relation.LocalKeys[i])].Value;
//filter.And(String.Format("{{{0}{1}}}={2}", tableAlias, Relation.ForeignKeys[i], csParameter.Name));
sFilter += String.Format("{{{0}{1}}}={2}", tableAlias, Relation.ForeignKeys[i], csParameter.Name);
if (i < Relation.LocalKeys.Count - 1)
{
sFilter += " AND ";
}
}
//filter.Parameters.Add(parameters);
CSFilter filter = new CSFilter(sFilter, parameters);
return filter;
}
case CSSchemaRelationType.ManyToMany:
{
if (Relation.ForeignKey == null)
Relation.ForeignKey = Schema.KeyColumns[0].Name;
if (Relation.ForeignLinkKey == null)
Relation.ForeignLinkKey = Relation.ForeignKey;
CSParameter csParameter = parameters.Add();
csParameter.Value = RelationObject.Data["#" + Relation.LocalKey].Value;
return new CSFilter("{" + tableAlias + Relation.ForeignKey + "} $in ($select {" + Relation.ForeignLinkKey + "} $from [" + Relation.LinkTable + "] where {" + Relation.LocalLinkKey + "}=" + csParameter.Name + ")", parameters);
}
}
}
return CSFilter.None;
}
private bool Write()
{
if (!_fieldData.IsDirty)
{
return(true);
}
if (_dataState == CSObjectDataState.Deleted)
{
return(false);
}
List <string> fieldNames = new List <string>();
List <string> fieldValues = new List <string>();
CSFilter whereClause = new CSFilter();
CSParameterCollection parameters = new CSParameterCollection();
foreach (CSSchemaColumn schemaColumn in _schema.Columns)
{
CSFieldValue fieldValue = _fieldData["#" + schemaColumn.Name];
if (!schemaColumn.IsKey && (fieldValue == null || !fieldValue.IsDirty || fieldValue.SchemaField.ReadOnly))
{
continue;
}
CSParameter parameter = parameters.Add();
parameter.Value = fieldValue.ValueDirect;
if (schemaColumn.IsKey)
{
whereClause = whereClause.And(_schema.DB.QuoteField(schemaColumn.Name) + "=@" + parameter.Name.Substring(1));
}
if (fieldValue.IsDirty && !fieldValue.SchemaField.ReadOnly && !schemaColumn.Identity && !schemaColumn.ReadOnly)
{
fieldNames.Add(schemaColumn.Name);
fieldValues.Add("@" + parameter.Name.Substring(1));
}
fieldValue.ValueState = CSFieldValueState.Read;
}
if (whereClause.Expression.Length == 0)
{
throw new CSException("No key fields");
}
if (fieldValues.Count > 0)
{
string sqlQuery = _schema.DB.BuildUpdateSQL(_schema.TableName, fieldNames.ToArray(), fieldValues.ToArray(), whereClause.Expression);
if (_schema.DB.ExecuteNonQuery(sqlQuery, parameters) != 1)
{
return(false);
}
}
return(true);
}
public CSList(CSFilter filter)
: this()
{
Filter = filter;
}
public static CSList <T> List(CSFilter filter)
{
return(new CSList <T>(filter));
}
public void AddFilterOr(CSFilter filter)
{
Filter |= filter;
Refresh();
}
internal bool ReadFields(CSStringCollection columnList, CSStringCollection keyList, List <object> valueList)
{
List <string> fieldList = new List <string>();
List <string> aliasList = new List <string>();
Dictionary <string, string> fieldAliasMap = new Dictionary <string, string>(StringComparer.OrdinalIgnoreCase);
CSFilter whereClause = new CSFilter();
CSTable table = new CSTable(_schema);
CSParameterCollection parameters = new CSParameterCollection();
foreach (CSSchemaColumn schemaColumn in _schema.Columns)
{
if (keyList.Contains(schemaColumn.Name))
{
CSParameter parameter = parameters.Add();
parameter.Value = valueList[keyList.IndexOf(schemaColumn.Name)];
whereClause = whereClause.And(table.TableAlias + "." + _schema.DB.QuoteField(schemaColumn.Name) + "=" + parameter.Name);
_fieldData["#" + schemaColumn.Name].ValueDirect = parameter.Value;
_fieldData["#" + schemaColumn.Name].ValueState = CSFieldValueState.Read;
}
else if (columnList.Contains(schemaColumn.Name))
{
string alias = CSNameGenerator.NextFieldAlias;
fieldList.Add(table.TableAlias + "." + schemaColumn.Name);
aliasList.Add(alias);
fieldAliasMap[alias] = schemaColumn.Name;
}
}
/** Build query for prefetch of relations **/
CSJoinList joinList = new CSJoinList();
List <PrefetchField> prefetchFields = GetPrefetchFieldsOne(table, fieldList, aliasList, joinList, null);
if (whereClause.Expression.Length == 0)
{
return(false);
}
if (fieldList.Count == 0)
{
return(true);
}
string sqlQuery = _schema.DB.BuildSelectSQL(table.TableName, table.TableAlias, fieldList.ToArray(), aliasList.ToArray(), joinList.BuildJoinExpressions(), whereClause.Expression, null, 1, 1, true, false);
using (CSTransaction csTransaction = new CSTransaction(_schema))
{
using (ICSDbReader dataReader = _schema.DB.CreateReader(sqlQuery, parameters))
{
if (!dataReader.Read())
{
return(false);
}
FromDataReader(dataReader, fieldAliasMap);
foreach (PrefetchField prefetchField in prefetchFields)
{
ReadRelationToOne(prefetchField.SchemaField, dataReader, prefetchField.AliasMap);
}
}
csTransaction.Commit();
}
return(true);
}
private void Populate()
{
if (Populated)
{
return;
}
if (Relation != null && RelationObject != null && Relation.RelationType == CSSchemaRelationType.OneToMany && RelationObject.IsNew)
{
_objectArray = new List <TObjectType>();
Populated = true;
return;
}
CSTable table = new CSTable(Schema);
//string mainAlias = CSHelper.NextTableAlias;
List <string> columnList = new List <string>(Schema.ColumnsToRead.Count);
List <string> aliasList = new List <string>(Schema.ColumnsToRead.Count);
Dictionary <string, string> aliasMap = new Dictionary <string, string>(StringComparer.OrdinalIgnoreCase);
foreach (string columnName in Schema.ColumnsToRead)
{
string alias = CSNameGenerator.NextFieldAlias;
columnList.Add(table.TableAlias + "." + columnName);
aliasList.Add(alias);
aliasMap.Add(alias, columnName);
}
CSJoinList filterJoins = new CSJoinList();
List <PrefetchField> prefetchFields = CSObject.GetPrefetchFieldsOne(table, columnList, aliasList, filterJoins, PrefetchPaths);
CSFilter whereFilter;
if (PrefetchFilter != null)
{
whereFilter = new CSFilter(DB.QuoteField(table.TableAlias + "." + PrefetchFilter.ForeignKey) + " in (" + PrefetchFilter.InStatement + ")", PrefetchFilter.Parameters);
}
else
{
string parsedFilterExpression = CSExpressionParser.ParseFilter(Filter.Expression, Schema, table.TableAlias, filterJoins);
whereFilter = new CSFilter(parsedFilterExpression, Filter.Parameters);
CSFilter relationFilter = BuildRelationFilter(table.TableAlias);
whereFilter = whereFilter.And(CSExpressionParser.ParseFilter(relationFilter.Expression, Schema, table.TableAlias, filterJoins), relationFilter.Parameters);
}
string parsedOrderBy = CSExpressionParser.ParseOrderBy(OrderBy, Schema, table.TableAlias, filterJoins);
string sqlQuery = DB.BuildSelectSQL(table.TableName, table.TableAlias, columnList.ToArray(), aliasList.ToArray(), filterJoins.BuildJoinExpressions(), whereFilter.Expression, parsedOrderBy, StartRecord, MaxRecords, true, false);
_objectArray = GetObjects(sqlQuery, whereFilter.Parameters, aliasMap, prefetchFields);
if (Schema.KeyColumns.Count == 1)
{
string columnName = Schema.KeyColumns[0].Name;
_objectMap = new Dictionary <object, TObjectType>();
foreach (TObjectType csObject in _objectArray)
{
_objectMap.Add(csObject.Data["#" + columnName].Value, csObject);
}
}
foreach (CSSchemaField prefetchField in GetPrefetchFieldsMany())
{
CSRelation relation = prefetchField.Relation;
Dictionary <object, TObjectType> prefetchMap = new Dictionary <object, TObjectType>();
// Creates empty lists in each object of this list
foreach (TObjectType csObject in _objectArray)
{
prefetchMap[csObject.Data["#" + relation.LocalKey].Value] = csObject;
CSList relationCollection = (CSList)Activator.CreateInstance(prefetchField.FieldType);
relationCollection.Relation = relation;
relationCollection.RelationObject = csObject;
relationCollection.InitializePrefetch();
csObject.Data[prefetchField.Name].ValueDirect = relationCollection;
csObject.Data[prefetchField.Name].ValueState = CSFieldValueState.Read;
}
Type objectType = relation.ForeignSchema.ClassType;
CSList csList = (CSList)Activator.CreateInstance(typeof(CSList <>).MakeGenericType(objectType));
//string prefetchTableAlias = CSNameGenerator.NextTableAlias;
string prefetchFilter = DB.BuildSelectSQL(table.TableName, table.TableAlias, new[] { table.TableAlias + "." + relation.LocalKey }, new[] { CSNameGenerator.NextFieldAlias }, filterJoins.BuildJoinExpressions(), whereFilter.Expression, parsedOrderBy, StartRecord, MaxRecords, true, true);
csList.PrefetchFilter = new PrefetchFilter(relation.ForeignKey, prefetchFilter, whereFilter.Parameters);
if (PrefetchPaths != null && PrefetchPaths.Length > 0)
{
List <string> newPrefetchPaths = new List <string>();
foreach (string path in PrefetchPaths)
{
if (path.StartsWith(prefetchField.Name + "."))
{
newPrefetchPaths.Add(path.Substring(prefetchField.Name.Length + 1));
}
}
if (newPrefetchPaths.Count > 0)
{
csList.PrefetchPaths = newPrefetchPaths.ToArray();
}
}
foreach (CSObject csObject in csList)
{
object localKey = csObject.Data["#" + relation.ForeignKey].ValueDirect;
CSList relationCollection = (CSList)prefetchMap[localKey].Data[prefetchField.Name].ValueDirect;
relationCollection.AddFromPrefetch(csObject);
}
}
Populated = true;
}
public CSFilter Or(CSFilter filterOr)
{
return(new CSFilter(this, "OR", filterOr));
}
