private object GetAggregatedValue(string fnName, FilterExpression filter)
{
// adding filter
List <object> parameterValues = new List <object>();
string filterString = filter.GetFilterString(parameterValues);
if (string.IsNullOrEmpty(filterString) == false)
{
filterString = " Where (" + filterString + ")";
}
string hqlQuery = "Select " + fnName + " from " + this.GetTableName(GetSourceTypeEnum.View) + filterString;
hqlQuery = hqlQuery.Replace("[", "").Replace("]", "");
var query = NHibernateSession.CreateQuery(hqlQuery);
// adding parameters
for (int i = 0; i < parameterValues.Count; i++)
{
query.SetParameter(Filter.ParameterPrefix.Substring(1) + i, parameterValues[i]);
}
var currentSeq = query.List();
if (currentSeq == null)
{
return(null);
}
else
{
return(currentSeq[0]);
}
}
public void FilterExpressionConstructorTest2()
{
FilterExpression target = new FilterExpression();
string actual = target.GetFilterString(null);
Assert.AreEqual("", actual);
}
public void GetFilterStringTest4()
{
FilterExpression target = new FilterExpression();
target.AddFilter(new Filter(vTestCaseTester.ColumnNames.FieldString, "C1"));
target.AddFilter(new Filter(vTestCaseTester.ColumnNames.FieldString, "C2"));
target.LogicalOperator = FilterLogicalOperatorEnum.OR;
FilterExpression filterExp2 = new FilterExpression();
filterExp2.AddFilter(new Filter(vTestCaseTester.ColumnNames.FieldString, "C3"));
filterExp2.AddFilter(new Filter(vTestCaseTester.ColumnNames.FieldString, "C4"));
target.AddFilterExpression(filterExp2);
Assert.AreEqual(target.FiltersList.Count, 3);
List <object> filterValues = new List <object>();
string actual = target.GetFilterString(filterValues);
string expected = "(" + GetSimpleFilterString(vTestCaseTester.ColumnNames.FieldString, "=", Filter.ParameterPrefix + "0") +
" OR " + GetSimpleFilterString(vTestCaseTester.ColumnNames.FieldString, "=", Filter.ParameterPrefix + "1") + "" +
" OR (" + GetSimpleFilterString(vTestCaseTester.ColumnNames.FieldString, "=", Filter.ParameterPrefix + "2") + "" +
" AND " + GetSimpleFilterString(vTestCaseTester.ColumnNames.FieldString, "=", Filter.ParameterPrefix + "3") + ")" +
")";
Assert.AreEqual(expected, actual);
Assert.AreEqual(filterValues.Count, 4);
Assert.AreEqual(filterValues[0], "C1");
Assert.AreEqual(filterValues[1], "C2");
Assert.AreEqual(filterValues[2], "C3");
Assert.AreEqual(filterValues[3], "C4");
}
public void AndMergeTest()
{
// empty test
FilterExpression target = new FilterExpression();
FilterExpression filterExp2 = new FilterExpression();
target.AndMerge(filterExp2);
Assert.AreEqual(target.FiltersList.Count, 1);
List <object> valuesList = new List <object>();
string s = target.GetFilterString(valuesList);
Assert.AreEqual(s, "");
}
public void GetFilterStringTest()
{
FilterExpression target = new FilterExpression();
Filter filter = new Filter(vTestCaseTester.ColumnNames.FieldString, "C'ode");
List <object> filterValues = new List <object>();
target.AddFilter(filter);
string actual = target.GetFilterString(filterValues);
string expected = "" + GetSimpleFilterString(vTestCaseTester.ColumnNames.FieldString, "=", Filter.ParameterPrefix + "0");
Assert.AreEqual(expected, actual);
Assert.AreEqual(filterValues.Count, 1);
Assert.AreEqual(filterValues[0], "C'ode");
}
public void GetFilterStringTest2()
{
FilterExpression target = new FilterExpression();
target.AddFilter(new Filter(vTestCaseTester.ColumnNames.FieldString, "C0"));
target.AddFilter(new Filter(vTestCaseTester.ColumnNames.FieldString, "C1"));
List <object> filterValues = new List <object>();
string actual = target.GetFilterString(filterValues);
string expected = "(" + GetSimpleFilterString(vTestCaseTester.ColumnNames.FieldString, "=", Filter.ParameterPrefix + "0") + " " + "AND" +
" " + GetSimpleFilterString(vTestCaseTester.ColumnNames.FieldString, "=", Filter.ParameterPrefix + "1") + ")";
Assert.AreEqual(expected, actual);
Assert.AreEqual(filterValues.Count, 2);
Assert.AreEqual(filterValues[0], "C0");
Assert.AreEqual(filterValues[1], "C1");
}
public void AndMergeTest2()
{
FilterExpression target = new FilterExpression();
target.AddFilter(new Filter(vTestCaseTester.ColumnNames.FieldString, "C1"));
FilterExpression filterExp2 = new FilterExpression();
filterExp2.AddFilter(new Filter(vTestCaseTester.ColumnNames.FieldString, "C2"));
target.AndMerge(filterExp2);
Assert.AreEqual(target.FiltersList.Count, 2);
List <object> valuesList = new List <object>();
string actual = target.GetFilterString(valuesList);
string expected = "(" + GetSimpleFilterString(vTestCaseTester.ColumnNames.FieldString, "=", Filter.ParameterPrefix + "0") +
" AND " + GetSimpleFilterString(vTestCaseTester.ColumnNames.FieldString, "=", Filter.ParameterPrefix + "1") + ")";
Assert.AreEqual(actual, expected);
}
/// <summary>
/// Gets the by filter.
/// </summary>
/// <param name="parameters"></param>
/// <returns></returns>
public System.Collections.IList GetByFilter(GetByFilterParameters parameters)
{
//NHibernateSession.Clear(); // temprorary fix for Dataaccess bug
// used from http://ayende.com/blog/4023/nhibernate-queries-examples
string selectColumns = "";
SortExpression sort = parameters.Sort;
FilterExpression filter = parameters.Filter;
int pageIndex = parameters.PageIndex;
int pageSize = parameters.PageSize;
if (pageSize == -1) // invalid value
{
pageSize = this.Entity.DefaultPageSize;
}
// Column Names
ICollection <string> colNames = parameters.SelectColumns;
if (colNames != null && colNames.Count > 0)
{
// adding ID field name to any select from the database to prevent null (-1) id problems
if (string.IsNullOrEmpty(this.Entity.IDFieldName) == false)
{
if (colNames.Contains(this.Entity.IDFieldName) == false)
{
colNames.Add(this.Entity.IDFieldName);
}
}
selectColumns = "Select ";
int i = 0;
foreach (string col in colNames)
{
selectColumns += col;
if (i != colNames.Count - 1)
{
selectColumns += ",";
}
else
{
selectColumns += " ";
}
i++;
}
}
// sort
string sortString = sort.GetSortExpressionString();
if (string.IsNullOrEmpty(sortString) == false)
{
sortString = " Order By " + sortString;
}
// adding filter
List <object> parameterValues = new List <object>();
string filterString = filter.GetFilterString(parameterValues);
if (string.IsNullOrEmpty(filterString) == false)
{
filterString = " Where (" + filterString + ")";
}
string hqlQuery = selectColumns + "from " + this.GetTableName(parameters.SourceType) + filterString + sortString;
hqlQuery = hqlQuery.Replace("[", "").Replace("]", "");
var query = NHibernateSession.CreateQuery(hqlQuery);
// adding parameters
for (int i = 0; i < parameterValues.Count; i++)
{
string paramName = Filter.ParameterPrefix.Substring(1) + i;
if (parameterValues[i] is System.Collections.ICollection) // for in operator, we can have a list of items. It works in HQL, but we need to pass it as parameterList
// read more here: http://stackoverflow.com/questions/3390561/nhibernate-update-single-field-without-loading-entity
{
query.SetParameterList(paramName, (System.Collections.ICollection)parameterValues[i]);
}
else
{
query.SetParameter(paramName, parameterValues[i]);
}
}
// set paging
query.SetFirstResult(pageIndex * pageSize).SetMaxResults(pageSize);
if (colNames == null)
{
if (parameters.SourceType == GetSourceTypeEnum.Table)
{
return(query.List <T>() as List <T>);
}
else
{
return(query.List <V>() as List <V>);
}
}
else
{
var list = query.List();
return(ConvertToTypedList(parameters.SourceType, colNames, list));
}
}