/// <summary>
/// Load the Business Objects from the specific DataStore type that applies to this loader.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="classDef"></param>
/// <param name="selectQuery"></param>
/// <returns></returns>
protected override LoaderResult GetObjectsFromDataStore <T>(IClassDef classDef, ISelectQuery selectQuery)
{
int totalCountAvailableForPaging;
var selectQueryDB = new SelectQueryDB(selectQuery, _databaseConnection);
var totalNoOfRecords = GetTotalNoOfRecordsIfNeeded(classDef, selectQueryDB);
List <LoadedBoInfo> loadedBoInfos;
string loadMechanismDescription;
if (IsLoadNecessary(selectQueryDB, totalNoOfRecords))
{
var statement = CreateStatementAdjustedForLimits(selectQueryDB, totalNoOfRecords);
loadedBoInfos = GetLoadedBusinessObjectsFromDB <T>(classDef, statement, selectQueryDB);
totalCountAvailableForPaging = totalNoOfRecords == -1 ? loadedBoInfos.Count : totalNoOfRecords;
loadMechanismDescription = statement.ToString();
}
else
{
// The load you ARE concerned about limits, and it is past the end of the total, or the limit is 0
loadedBoInfos = new List <LoadedBoInfo>();
totalCountAvailableForPaging = totalNoOfRecords;
loadMechanismDescription = "[No SQL query due to request of a load past the end of the dataset or of a zero sized limit]";
}
return(new LoaderResult
{
LoadedBoInfos = loadedBoInfos,
TotalCountAvailableForPaging = totalCountAvailableForPaging,
LoadMechanismDescription = loadMechanismDescription,
});
}
/// <summary>
/// Populates a <see cref="QueryResult"/> using the given <see cref="ISelectQuery"/>.
/// With this method you can execute a custom select query and get a result set back. If you are loading against a
/// database, the <see cref="IClassDef"/> associated to the <see cref="ISelectQuery"/> will be used to map property names
/// to database fields and will also be used to convert values that are returned from the database to the expected
/// type. This can be used to get result sets that span tables.
/// </summary>
/// <param name="selectQuery">The select query to execute</param>
/// <returns>A <see cref="QueryResult"/> that contains the results of the query</returns>
public IQueryResult GetResultSet(ISelectQuery selectQuery)
{
var classDef = selectQuery.ClassDef;
var criteria = selectQuery.Criteria;
QueryBuilder.PrepareCriteria(classDef, criteria);
//Ensure that all the criteria field sources are merged correctly
selectQuery.Criteria = criteria;
selectQuery.Fields.ForEach(pair =>
{
var field = pair.Value;
var fieldSource = field.Source;
QueryBuilder.PrepareField(fieldSource, classDef, field);
selectQuery.Source.MergeWith(field.Source);
field.Source = field.Source.ChildSourceLeaf;
});
var queryDb = new SelectQueryDB(selectQuery, _databaseConnection);
var statement = queryDb.CreateSqlStatement();
var resultSet = new QueryResult();
var propNames = selectQuery.Fields.Keys;
propNames.ForEach(resultSet.AddField);
using (IDataReader dr = _databaseConnection.LoadDataReader(statement))
{
while (dr.Read())
{
var rawValues = new object[dr.FieldCount];
dr.GetValues(rawValues);
resultSet.AddResult(rawValues);
}
}
return resultSet;
}
/// <summary>
/// Populates a <see cref="QueryResult"/> using the given <see cref="ISelectQuery"/>.
/// With this method you can execute a custom select query and get a result set back. If you are loading against a
/// database, the <see cref="IClassDef"/> associated to the <see cref="ISelectQuery"/> will be used to map property names
/// to database fields and will also be used to convert values that are returned from the database to the expected
/// type. This can be used to get result sets that span tables.
/// </summary>
/// <param name="selectQuery">The select query to execute</param>
/// <returns>A <see cref="QueryResult"/> that contains the results of the query</returns>
public IQueryResult GetResultSet(ISelectQuery selectQuery)
{
var classDef = selectQuery.ClassDef;
var criteria = selectQuery.Criteria;
QueryBuilder.PrepareCriteria(classDef, criteria);
//Ensure that all the criteria field sources are merged correctly
selectQuery.Criteria = criteria;
selectQuery.Fields.ForEach(pair =>
{
var field = pair.Value;
var fieldSource = field.Source;
QueryBuilder.PrepareField(fieldSource, classDef, field);
selectQuery.Source.MergeWith(field.Source);
field.Source = field.Source.ChildSourceLeaf;
});
var queryDb = new SelectQueryDB(selectQuery, _databaseConnection);
var statement = queryDb.CreateSqlStatement();
var resultSet = new QueryResult();
var propNames = selectQuery.Fields.Keys;
propNames.ForEach(resultSet.AddField);
using (IDataReader dr = _databaseConnection.LoadDataReader(statement))
{
while (dr.Read())
{
var rawValues = new object[dr.FieldCount];
dr.GetValues(rawValues);
resultSet.AddResult(rawValues);
}
}
return(resultSet);
}
public void Test_SelectQueryDB_Set_FirstRecordToLoad()
{
//---------------Set up test pack-------------------
ISelectQuery selectQuery = new SelectQueryDB(new SelectQuery(), DatabaseConnection.CurrentConnection);
//---------------Assert Precondition----------------
Assert.AreEqual(0, selectQuery.FirstRecordToLoad);
//---------------Execute Test ----------------------
selectQuery.FirstRecordToLoad = 10;
//---------------Test Result -----------------------
Assert.AreEqual(10, selectQuery.FirstRecordToLoad);
}
/// <summary>
/// Returns the business object of the type provided that meets the search criteria.
/// An exception is thrown if more than one business object is found that matches the criteria.
/// If that situation could arise, rather use GetBusinessObjectCol.
/// </summary>
/// <param name="selectQuery">The select query</param>
/// <returns>Returns the business object found</returns>
/// <exception cref="UserException">Thrown if more than one object matches the criteria</exception>
public T GetBusinessObject <T>(ISelectQuery selectQuery) where T : class, IBusinessObject, new()
{
var classDef = ClassDef.Get <T>();
var source = selectQuery.Source;
QueryBuilder.PrepareSource(classDef, ref source);
selectQuery.Source = source;
QueryBuilder.PrepareCriteria(classDef, selectQuery.Criteria);
var selectQueryDB = new SelectQueryDB(selectQuery, _databaseConnection);
var statement = selectQueryDB.CreateSqlStatement();
IClassDef correctSubClassDef = null;
T loadedBo = null;
var objectUpdatedInLoading = false;
using (var dr = _databaseConnection.LoadDataReader(statement))
{
if (dr.Read())
{
loadedBo = LoadBOFromReader <T>(dr, selectQueryDB, out objectUpdatedInLoading);
//Checks to see if the loaded object is the base of a single table inheritance structure
// and has a sub type if so then returns the correct sub type.
correctSubClassDef = GetCorrectSubClassDef(loadedBo, dr);
//Checks to see if there is a duplicate object meeting this criteria
if (dr.Read())
{
ThrowRetrieveDuplicateObjectException(statement, loadedBo);
}
}
}
if (correctSubClassDef != null)
{
loadedBo = GetLoadedBoOfSpecifiedType(loadedBo, correctSubClassDef);
}
if (loadedBo == null)
{
return(null);
}
var isFreshlyLoaded = loadedBo.Status.IsNew;
SetStatusAfterLoad(loadedBo);
if (objectUpdatedInLoading)
{
CallAfterLoad(loadedBo);
if (!isFreshlyLoaded)
{
FireUpdatedEvent(loadedBo);
}
}
return(loadedBo);
}
/// <summary>
/// Loads a business object of the type identified by a <see cref="ClassDef"/>,
/// using the SelectQuery given. It's important to make sure that the ClassDef parameter given
/// has the properties defined in the fields of the select query.
/// This method allows you to define a custom query to load a business object
/// </summary>
/// <param name="classDef">The ClassDef of the object to load.</param>
/// <param name="selectQuery">The select query to use to load from the data source</param>
/// <returns>The business object that was found. If none was found, null is returned. If more than one is found an <see cref="HabaneroDeveloperException"/> error is throw</returns>
public IBusinessObject GetBusinessObject(IClassDef classDef, ISelectQuery selectQuery)
{
var source = selectQuery.Source;
QueryBuilder.PrepareSource(classDef, ref source);
selectQuery.Source = source;
QueryBuilder.PrepareCriteria(classDef, selectQuery.Criteria);
var selectQueryDB = new SelectQueryDB(selectQuery, _databaseConnection);
var statement = selectQueryDB.CreateSqlStatement();
IClassDef correctSubClassDef = null;
IBusinessObject loadedBo = null;
var objectUpdatedInLoading = false;
using (var dr = _databaseConnection.LoadDataReader(statement))
{
if (dr.Read())
{
loadedBo = LoadBOFromReader(classDef, dr, selectQueryDB, out objectUpdatedInLoading);
correctSubClassDef = GetCorrectSubClassDef(loadedBo, dr);
if (dr.Read())
{
ThrowRetrieveDuplicateObjectException(statement, loadedBo);
}
}
}
if (correctSubClassDef != null)
{
BORegistry.BusinessObjectManager.Remove(loadedBo);
var subClassBusinessObject = GetBusinessObject(correctSubClassDef, loadedBo.ID);
loadedBo = subClassBusinessObject;
}
if (loadedBo == null)
{
return(null);
}
var isFreshlyLoaded = loadedBo.Status.IsNew;
SetStatusAfterLoad(loadedBo);
if (objectUpdatedInLoading)
{
CallAfterLoad(loadedBo);
if (!isFreshlyLoaded)
{
FireUpdatedEvent(loadedBo);
}
}
return(loadedBo);
}
/// <summary>
/// Reloads a BusinessObjectCollection using the criteria it was originally loaded with. You can also change the criteria or order
/// it loads with by editing its SelectQuery object. The collection will be cleared as such and reloaded (although Added events will
/// only fire for the new objects added to the collection, not for the ones that already existed).
/// </summary>
public int GetCount(IClassDef classDef, Criteria criteria)
{
var selectQuery = QueryBuilder.CreateSelectCountQuery(classDef, criteria);
var selectQueryDB = new SelectQueryDB(selectQuery, _databaseConnection);
// FIXME: this should come from the db
var sqlFormatter = new SqlFormatter("", "", "", "");
var statement = selectQueryDB.CreateSqlStatement(_databaseConnection.SqlFormatter);
var totalNoOfRecords = 0;
using (var dr = _databaseConnection.LoadDataReader(statement))
{
while (dr.Read())
{
totalNoOfRecords = Convert.ToInt32(dr[0].ToString());
}
}
return(totalNoOfRecords);
}
private static ISqlStatement CreateStatementAdjustedForLimits(SelectQueryDB selectQuery, int totalNoOfRecords)
{
int originalLimit = selectQuery.Limit;
if (selectQuery.FirstRecordToLoad > 0)
{
int remainingNumberOfItems = totalNoOfRecords - selectQuery.FirstRecordToLoad;
if (originalLimit >= 0)
{
selectQuery.Limit = (originalLimit < remainingNumberOfItems)
? originalLimit
: remainingNumberOfItems;
}
else
{
selectQuery.Limit = remainingNumberOfItems;
}
}
ISqlStatement statement = selectQuery.CreateSqlStatement();
selectQuery.Limit = originalLimit;
return(statement);
}
public void TestCreateSqlStatement_WithNotInCriteria()
{
//---------------Set up test pack-------------------
IClassDef classDef = MyBO.LoadDefaultClassDef();
IEnumerable values = new object[] { "100", "200", "300" };
Criteria.CriteriaValues criteriaValues = new Criteria.CriteriaValues(values);
Criteria criteria = new Criteria("TestProp", Criteria.ComparisonOp.NotIn, criteriaValues);
ISelectQuery selectQuery = QueryBuilder.CreateSelectQuery(classDef, criteria);
SelectQueryDB query = new SelectQueryDB(selectQuery, DatabaseConnection.CurrentConnection);
//---------------Assert PreConditions---------------
//---------------Execute Test ----------------------
ISqlStatement statement = query.CreateSqlStatement(_sqlFormatter);
//---------------Test Result -----------------------
string statementString = statement.Statement.ToString();
StringAssert.EndsWith("WHERE a1.[TestProp] NOT IN (?Param0, ?Param1, ?Param2)", statementString);
Assert.AreEqual("?Param0", statement.Parameters[0].ParameterName);
Assert.AreEqual("100", statement.Parameters[0].Value);
Assert.AreEqual("?Param1", statement.Parameters[1].ParameterName);
Assert.AreEqual("200", statement.Parameters[1].Value);
Assert.AreEqual("?Param2", statement.Parameters[2].ParameterName);
Assert.AreEqual("300", statement.Parameters[2].Value);
//---------------Tear Down -------------------------
}
public void Test_CreateSQL_ShouldUseAliasesInCriteria_WhenNotCriteria()
{
//---------------Set up test pack-------------------
SelectQuery selectQuery = new SelectQuery();
const string sourceName = "mysource";
var source1 = new Source(sourceName);
selectQuery.Source = source1;
Source field1Source = source1;
QueryField field1 = new QueryField("testfield", "testfield", field1Source);
selectQuery.Fields.Add(field1.FieldName, field1);
selectQuery.Criteria = new Criteria(
null,
Criteria.LogicalOp.Not,
new Criteria(field1, Criteria.ComparisonOp.Is, null));
SelectQueryDB query = new SelectQueryDB(selectQuery, DatabaseConnection.CurrentConnection);
SqlFormatter sqlFormatter = new SqlFormatter("[", "]", "", "LIMIT");
//---------------Execute Test ----------------------
ISqlStatement statement = query.CreateSqlStatement(sqlFormatter);
//---------------Test Result -----------------------
StringAssert.AreEqualIgnoringCase("SELECT a1.[testfield] FROM [mysource] a1 WHERE NOT (a1.[testfield] IS NULL)", statement.Statement.ToString());
}
public void Test_SetupAliases_SetsUpAliasesForAllSources()
{
//---------------Set up test pack-------------------
IClassDef classDef = MyBO.LoadClassDefWithRelationship();
MyRelatedBo.LoadClassDef();
Criteria criteria = CriteriaParser.CreateCriteria("MyRelationship.MyRelatedTestProp = 'test'");
SelectQuery selectQuery = (SelectQuery)QueryBuilder.CreateSelectQuery(classDef, criteria);
//---------------Execute Test ----------------------
SelectQueryDB selectQueryDb = new SelectQueryDB(selectQuery, DatabaseConnection.CurrentConnection);
//---------------Test Result -----------------------
Assert.AreEqual(3, selectQueryDb.Aliases.Count);
Assert.AreEqual("a1", selectQueryDb.Aliases["MyBO"]);
Assert.AreEqual("a1", selectQueryDb.Aliases["MyBO.MyRelationship"]);
Assert.AreEqual("a2", selectQueryDb.Aliases["MyRelationship"]);
Assert.IsTrue(selectQueryDb.Aliases.Values.Contains("a1"));
Assert.IsTrue(selectQueryDb.Aliases.Values.Contains("a2"));
}
public void Test_CreateSQL_SelectCountQuery_WithCriteria()
{
//---------------Set up test pack-------------------
DatabaseConnection.CurrentConnection = new DatabaseConnectionStub();
IClassDef classDef = MyBO.LoadDefaultClassDef();
Criteria criteria = new Criteria("TestProp", Criteria.ComparisonOp.Equals, "test");
SqlFormatter sqlFormatter = new SqlFormatter("[", "]", "", "LIMIT");
//-------------Assert Preconditions -------------
//---------------Execute Test ----------------------
ISelectQuery selectQuery = QueryBuilder.CreateSelectCountQuery(classDef, criteria);
SelectQueryDB query = new SelectQueryDB(selectQuery, DatabaseConnection.CurrentConnection);
ISqlStatement statement = query.CreateSqlStatement(sqlFormatter);
//---------------Test Result -----------------------
string statementString = statement.Statement.ToString();
StringAssert.Contains("SELECT [Count(*)] FROM [MyBO] a1 WHERE ", statement.Statement.ToString());
StringAssert.EndsWith("WHERE a1.[TestProp] = ?Param0", statementString);
Assert.AreEqual("?Param0", statement.Parameters[0].ParameterName);
Assert.AreEqual("test", statement.Parameters[0].Value);
}
public void Test_CreateSQL_LoadWithLimit_AtEnd_PaginatedFind()
{
//---------------Set up test pack-------------------
DatabaseConnection.CurrentConnection = new DatabaseConnectionStub();
IClassDef classDef = MyBO.LoadClassDefs_OneProp();
ISelectQuery selectQuery = QueryBuilder.CreateSelectQuery(classDef);
selectQuery.FirstRecordToLoad = 2;
selectQuery.Limit = 4;
selectQuery.OrderCriteria = OrderCriteria.FromString("MyBOID");
SelectQueryDB query = new SelectQueryDB(selectQuery, DatabaseConnection.CurrentConnection);
SqlFormatter sqlFormatter = new SqlFormatter("", "", "", "LIMIT");
//---------------Assert Precondition----------------
Assert.AreEqual(2, selectQuery.FirstRecordToLoad);
Assert.AreEqual(4, selectQuery.Limit);
//---------------Execute Test ----------------------
ISqlStatement statement = query.CreateSqlStatement(sqlFormatter);
string actualStatement = statement.Statement.ToString();
//---------------Test Result -----------------------
const string expectedFirstSelect = "(SELECT a1.MyBoID FROM MyBO a1 ORDER BY a1.MyBOID ASC LIMIT 6) As FirstSelect";
StringAssert.Contains(expectedFirstSelect, actualStatement);
string expectedSecondSelect = string.Format("(SELECT FirstSelect.MyBoID FROM {0} ORDER BY FirstSelect.MyBOID DESC LIMIT 4) As SecondSelect", expectedFirstSelect);
StringAssert.Contains(expectedSecondSelect, actualStatement);
string expectedMainSelect = string.Format("SELECT SecondSelect.MyBoID FROM {0} ORDER BY SecondSelect.MyBOID ASC", expectedSecondSelect);
Assert.AreEqual(expectedMainSelect, actualStatement);
}
public void TestCreateSqlStatement_NonPersistableProperty()
{
//---------------Set up test pack-------------------
IClassDef classDef = MyBO.LoadDefaultClassDef();
PropDef newPropDef =
new PropDef("NonPeristableProp", typeof (string), PropReadWriteRule.ReadOnly, null);
newPropDef.Persistable = false;
classDef.PropDefcol.Add(newPropDef);
ISelectQuery selectQuery = QueryBuilder.CreateSelectQuery(classDef);
//---------------Execute Test ----------------------
SelectQueryDB query = new SelectQueryDB(selectQuery, DatabaseConnection.CurrentConnection);
ISqlStatement statement = query.CreateSqlStatement();
//---------------Test Result -----------------------
string statementString = statement.Statement.ToString();
StringAssert.AreEqualIgnoringCase("SELECT a1.MyBoID, a1.TestProp, a1.TestProp2 FROM MyBO a1", statementString);
//---------------Tear Down -------------------------
}
public void TestClassTable_LoadSubtypeWithCriteriaFromBaseType()
{
//---------------Set up test pack-------------------
Structure.Entity.LoadDefaultClassDef();
IClassDef classDef = Structure.Part.LoadClassDef_WithClassTableInheritance();
string entityType = TestUtil.GetRandomString();
ISelectQuery selectQuery = QueryBuilder.CreateSelectQuery(classDef);
Criteria criteria = new Criteria("EntityType", Criteria.ComparisonOp.Equals, entityType);
QueryBuilder.PrepareCriteria(classDef, criteria);
selectQuery.Criteria = criteria;
SelectQueryDB query = new SelectQueryDB(selectQuery, DatabaseConnection.CurrentConnection);
//---------------Execute Test ----------------------
ISqlStatement statement = query.CreateSqlStatement(_sqlFormatter);
//---------------Test Result -----------------------
StringAssert.Contains("a2.[field_Entity_Type] = ?Param0", statement.Statement.ToString());
//---------------Tear Down -------------------------
}
public void TestClassTableInheritance_Join()
{
//---------------Set up test pack-------------------
IClassDef circleClassDef = Circle.GetClassDef();
ISelectQuery selectQuery = QueryBuilder.CreateSelectQuery(circleClassDef);
SelectQueryDB query = new SelectQueryDB(selectQuery, DatabaseConnection.CurrentConnection);
//---------------Assert Precondition----------------
Assert.AreEqual(1, selectQuery.Source.InheritanceJoins.Count);
//---------------Execute Test ----------------------
ISqlStatement statement = query.CreateSqlStatement(_sqlFormatter);
//---------------Test Result -----------------------
StringAssert.Contains("([circle_table] a1 JOIN [Shape_table] a2 ON a1.[CircleID_field] = a2.[ShapeID_field])", statement.Statement.ToString());
}
public void TestCreateSqlStatement_WithNoLimit_AtBeginning()
{
//---------------Set up test pack-------------------
DatabaseConnection.CurrentConnection = new DatabaseConnectionStub_LimitClauseAtBeginning();
SelectQuery selectQuery = new SelectQuery {Limit = (-1)};
const string fieldName = "Field1";
selectQuery.Fields.Add(fieldName, new QueryField(fieldName, fieldName, null));
selectQuery.Source = new Source("Table1");
SelectQueryDB query = new SelectQueryDB(selectQuery, DatabaseConnection.CurrentConnection);
//---------------Execute Test ----------------------
ISqlStatement statement = query.CreateSqlStatement(_sqlFormatter);
//---------------Test Result -----------------------
string statementString = statement.Statement.ToString();
StringAssert.StartsWith("SELECT [Field1]", statementString);
//---------------Tear Down -------------------------
}
public void TestCreateSqlStatement_NoSourceNameInQueryFields()
{
//---------------Set up test pack-------------------
ISelectQuery selectQuery = new SelectQuery();
selectQuery.Fields.Add("Surname", new QueryField("Surname", "Surname", null));
selectQuery.Fields.Add("ContactPersonID", new QueryField("ContactPersonID", "ContactPersonID", null));
selectQuery.Source = new Source("bob");
//---------------Execute Test ----------------------
SelectQueryDB query = new SelectQueryDB(selectQuery, DatabaseConnection.CurrentConnection);
ISqlStatement statement = query.CreateSqlStatement(_sqlFormatter);
//---------------Test Result -----------------------
string statementString = statement.Statement.ToString();
StringAssert.AreEqualIgnoringCase("SELECT [Surname], [ContactPersonID] FROM [bob] a1", statementString);
//---------------Tear Down -------------------------
}
/// <summary>
/// Actually loads the objects using the statement given.
/// </summary>
/// <typeparam name="T">The type of class you are loading</typeparam>
/// <param name="classDef">The classdef to use when loading</param>
/// <param name="statement">The sql statement to run</param>
/// <param name="selectQuery">The original select query</param>
/// <returns></returns>
protected virtual List <LoadedBoInfo> GetLoadedBusinessObjectsFromDB <T>(IClassDef classDef, ISqlStatement statement, SelectQueryDB selectQuery) where T : IBusinessObject
{
var loadedBoInfos = new List <LoadedBoInfo>();
using (var dr = _databaseConnection.LoadDataReader(statement))
{
while (dr.Read())
{
var loadedBoInfo = LoadCorrectlyTypedBOFromReader <T>(dr, classDef, selectQuery);
loadedBoInfos.Add(loadedBoInfo);
}
}
return(loadedBoInfos);
}
private LoadedBoInfo LoadCorrectlyTypedBOFromReader <T>(IDataReader dr, IClassDef classDef, SelectQueryDB selectQuery) where T : IBusinessObject
{
bool objectUpdatedInLoading;
var loadedBo = (T)LoadBOFromReader(classDef, dr, selectQuery, out objectUpdatedInLoading);
//Checks to see if the loaded object is the base of a single table inheritance structure
// and has a sub type
var correctSubClassDef = GetCorrectSubClassDef(loadedBo, dr);
// loads an object of the correct sub type (for single table inheritance)
loadedBo = GetLoadedBoOfSpecifiedType(loadedBo, correctSubClassDef);
// these collections are used to determine which objects should the AfterLoad and FireUpdatedEvent methods be called on
var loadedBoInfo = new LoadedBoInfo();
loadedBoInfo.IsFreshlyLoaded = loadedBo.Status.IsNew;
SetStatusAfterLoad(loadedBo);
loadedBoInfo.LoadedBo = loadedBo;
loadedBoInfo.IsUpdatedInLoading = objectUpdatedInLoading;
return(loadedBoInfo);
}
public void TestCreateSqlStatement_WithNoLimit_AtEnd()
{
//---------------Set up test pack-------------------
DatabaseConnection.CurrentConnection = new DatabaseConnectionStub(new SqlFormatter("", "", "", "LIMIT"));
SelectQuery selectQuery = new SelectQuery();
selectQuery.Limit = -1;
const string fieldName = "Field1";
selectQuery.Fields.Add(fieldName, new QueryField(fieldName, fieldName, null));
selectQuery.Source = new Source("Table1");
SelectQueryDB query = new SelectQueryDB(selectQuery, DatabaseConnection.CurrentConnection);
//---------------Execute Test ----------------------
ISqlStatement statement = query.CreateSqlStatement(_sqlFormatter);
//---------------Test Result -----------------------
string statementString = statement.Statement.ToString();
StringAssert.EndsWith("FROM [Table1] a1", statementString);
//---------------Tear Down -------------------------
}
public void TestCreateSqlStatement_WithCriteria_WithClassID()
{
//---------------Set up test pack-------------------
IClassDef classDef = MyBO.LoadDefaultClassDef();
classDef.ClassID = Guid.NewGuid();
Criteria criteria = new Criteria("TestProp", Criteria.ComparisonOp.Equals, "test");
ISelectQuery selectQuery = QueryBuilder.CreateSelectQuery(classDef, criteria);
SelectQueryDB query = new SelectQueryDB(selectQuery, DatabaseConnection.CurrentConnection);
//---------------Assert PreConditions---------------
//---------------Execute Test ----------------------
ISqlStatement statement = query.CreateSqlStatement(_sqlFormatter);
//---------------Test Result -----------------------
string statementString = statement.Statement.ToString();
StringAssert.Contains("WHERE (a1.[TestProp] = ?Param0)", statementString);
Assert.AreEqual("?Param0", statement.Parameters[0].ParameterName);
Assert.AreEqual("test", statement.Parameters[0].Value);
StringAssert.EndsWith("(a1.[DMClassID] = ?Param1)", statementString);
Assert.AreEqual("?Param1", statement.Parameters[1].ParameterName);
Assert.AreEqual(classDef.ClassID.Value.ToString("B").ToUpper(), statement.Parameters[1].Value);
//---------------Tear Down -------------------------
}
public void TestClassTableInheritanceHierarchy_Join()
{
//---------------Set up test pack-------------------
IClassDef filledCircleClassDef = FilledCircle.GetClassDefWithClassInheritanceHierarchy();
ISelectQuery selectQuery = QueryBuilder.CreateSelectQuery(filledCircleClassDef);
SelectQueryDB query = new SelectQueryDB(selectQuery, DatabaseConnection.CurrentConnection);
//---------------Assert Precondition----------------
//---------------Execute Test ----------------------
ISqlStatement statement = query.CreateSqlStatement(_sqlFormatter);
//---------------Test Result -----------------------
StringAssert.Contains("(([FilledCircle_table] a1 JOIN [circle_table] a2 ON a1.[FilledCircleID_field] = a2.[CircleID_field])" +
" JOIN [Shape_table] a3 ON a2.[CircleID_field] = a3.[ShapeID_field])", statement.Statement.ToString());
}
public void TestCreateSqlStatement_WithCriteria_DateTimeNow()
{
//---------------Set up test pack-------------------
IClassDef classDef = MyBO.LoadClassDefWithDateTime();
Criteria criteria = new Criteria("TestDateTime", Criteria.ComparisonOp.Equals, new DateTimeNow());
ISelectQuery selectQuery = QueryBuilder.CreateSelectQuery(classDef, criteria);
SelectQueryDB query = new SelectQueryDB(selectQuery, DatabaseConnection.CurrentConnection);
//---------------Assert PreConditions---------------
//---------------Execute Test ----------------------
DateTime dateTimeBefore = DateTime.Today;
ISqlStatement statement = query.CreateSqlStatement(_sqlFormatter);
DateTime dateTimeAfter = DateTime.Today.AddDays(1);
//---------------Test Result -----------------------
string statementString = statement.Statement.ToString();
StringAssert.EndsWith("WHERE a1.[TestDateTime] = ?Param0", statementString);
Assert.AreEqual("?Param0", statement.Parameters[0].ParameterName);
object value = statement.Parameters[0].Value;
Assert.IsInstanceOf(typeof(DateTime), value);
DateTime dateTimeValue = (DateTime)value;
Assert.GreaterOrEqual(dateTimeValue, dateTimeBefore);
Assert.LessOrEqual(dateTimeValue, dateTimeAfter);
}
public void TestSingleTableInheritance_DiscriminatorInWhere()
{
//---------------Set up test pack-------------------
IClassDef circleClassDef = CircleNoPrimaryKey.GetClassDefWithSingleInheritance();
ISelectQuery selectQuery = QueryBuilder.CreateSelectQuery(circleClassDef);
SelectQueryDB query = new SelectQueryDB(selectQuery, DatabaseConnection.CurrentConnection);
//---------------Execute Test ----------------------
ISqlStatement statement = query.CreateSqlStatement(_sqlFormatter);
//---------------Test Result -----------------------
StringAssert.Contains("a1.[ShapeType_field] = ?Param0", statement.Statement.ToString());
Assert.AreEqual(1, statement.Parameters.Count);
Assert.AreEqual("CircleNoPrimaryKey", statement.Parameters[0].Value);
//---------------Tear Down -------------------------
}
public void TestCreateSqlStatement_WithCriteria_TwoLevels()
{
//---------------Set up test pack-------------------
IClassDef classDef = MyBO.LoadClassDefWithRelationship();
MyRelatedBo.LoadClassDef();
Criteria criteria = CriteriaParser.CreateCriteria("MyRelationship.MyRelatedTestProp = 'test'");
ISelectQuery selectQuery = QueryBuilder.CreateSelectQuery(classDef, criteria);
SelectQueryDB query = new SelectQueryDB(selectQuery, DatabaseConnection.CurrentConnection);
//---------------Execute Test ----------------------
ISqlStatement statement = query.CreateSqlStatement(_sqlFormatter);
//---------------Test Result -----------------------
StringAssert.Contains(
"[MyBO] a1 LEFT JOIN [MyRelatedBo] a2 ON a1.[RelatedID] = a2.[MyRelatedBoID]",
statement.Statement.ToString());
//---------------Tear Down -------------------------
}
public void Test_CreateSQL_LoadWithLimit_PaginatedFind_HasCorrectFieldNames()
{
//---------------Set up test pack-------------------
DatabaseConnection.CurrentConnection = new DatabaseConnectionStub();
IClassDef classDef = ContactPersonTestBO.LoadDefaultClassDef();
ISelectQuery selectQuery = QueryBuilder.CreateSelectQuery(classDef);
selectQuery.FirstRecordToLoad = 3;
selectQuery.Limit = 5;
selectQuery.OrderCriteria = OrderCriteria.FromString("Surname");
SelectQueryDB query = new SelectQueryDB(selectQuery, DatabaseConnection.CurrentConnection);
SqlFormatter sqlFormatter = new SqlFormatter("", "", "TOP", "");
//---------------Assert Precondition----------------
Assert.AreEqual(3, selectQuery.FirstRecordToLoad);
Assert.AreEqual(5, selectQuery.Limit);
//---------------Execute Test ----------------------
ISqlStatement statement = query.CreateSqlStatement(sqlFormatter);
string actualStatement = statement.Statement.ToString();
//---------------Test Result -----------------------
const string expectedFirstSelect = "(SELECT TOP 8 a1.ContactPersonID, a1.Surname_field, a1.FirstName_field, a1.DateOfBirth FROM contact_person a1 ORDER BY a1.Surname_field ASC) As FirstSelect";
StringAssert.Contains(expectedFirstSelect, actualStatement);
string expectedSecondSelect = string.Format("(SELECT TOP 5 FirstSelect.ContactPersonID, FirstSelect.Surname_field, FirstSelect.FirstName_field, FirstSelect.DateOfBirth FROM {0} ORDER BY FirstSelect.Surname_field DESC ) As SecondSelect", expectedFirstSelect);
StringAssert.Contains(expectedSecondSelect, actualStatement);
string expectedMainSelect = string.Format("SELECT SecondSelect.ContactPersonID, SecondSelect.Surname_field, SecondSelect.FirstName_field, SecondSelect.DateOfBirth FROM {0} ORDER BY SecondSelect.Surname_field ASC", expectedSecondSelect);
Assert.AreEqual(expectedMainSelect, actualStatement);
}
public void TestCreateSqlStatement_WithOrderFields_WithoutBuilder_DifferentFieldNames()
{
//---------------Set up test pack-------------------
IClassDef classDef = MyBO.LoadDefaultClassDefWithDifferentTableAndFieldNames();
ISelectQuery selectQuery = QueryBuilder.CreateSelectQuery(classDef);
selectQuery.OrderCriteria = OrderCriteria.FromString("MyBoID, TestProp");
SelectQueryDB query = new SelectQueryDB(selectQuery, DatabaseConnection.CurrentConnection);
//---------------Assert PreConditions---------------
//---------------Execute Test ----------------------
ISqlStatement statement = query.CreateSqlStatement(_sqlFormatter);
//---------------Test Result -----------------------
string statementString = statement.Statement.ToString();
StringAssert.EndsWith("ORDER BY a1.[my_bo_id] ASC, a1.[test_prop] ASC", statementString);
//---------------Tear Down -------------------------
}
public void Test_CreateSQL_LoadWithLimit_AtEnd_PaginatedFind_HasCorrectFieldNames_WithDelimiters()
{
//---------------Set up test pack-------------------
DatabaseConnection.CurrentConnection = new DatabaseConnectionStub();
IClassDef classDef = ContactPersonTestBO.LoadDefaultClassDef();
ISelectQuery selectQuery = QueryBuilder.CreateSelectQuery(classDef);
selectQuery.FirstRecordToLoad = 3;
selectQuery.Limit = 5;
selectQuery.OrderCriteria = OrderCriteria.FromString("Surname");
SelectQueryDB query = new SelectQueryDB(selectQuery, DatabaseConnection.CurrentConnection);
SqlFormatter sqlFormatter = new SqlFormatter("[", "]", "", "LIMIT");
//---------------Assert Precondition----------------
Assert.AreEqual(3, selectQuery.FirstRecordToLoad);
Assert.AreEqual(5, selectQuery.Limit);
//---------------Execute Test ----------------------
ISqlStatement statement = query.CreateSqlStatement(sqlFormatter);
string actualStatement = statement.Statement.ToString();
//---------------Test Result -----------------------
const string expectedFirstSelect = "(SELECT a1.[ContactPersonID], a1.[Surname_field], a1.[FirstName_field], a1.[DateOfBirth] FROM [contact_person] a1 ORDER BY a1.[Surname_field] ASC LIMIT 8) As [FirstSelect]";
StringAssert.Contains(expectedFirstSelect, actualStatement);
string expectedSecondSelect = string.Format("(SELECT [FirstSelect].[ContactPersonID], [FirstSelect].[Surname_field], [FirstSelect].[FirstName_field], [FirstSelect].[DateOfBirth] FROM {0} ORDER BY [FirstSelect].[Surname_field] DESC LIMIT 5) As [SecondSelect]", expectedFirstSelect);
StringAssert.Contains(expectedSecondSelect, actualStatement);
string expectedMainSelect = string.Format("SELECT [SecondSelect].[ContactPersonID], [SecondSelect].[Surname_field], [SecondSelect].[FirstName_field], [SecondSelect].[DateOfBirth] FROM {0} ORDER BY [SecondSelect].[Surname_field] ASC", expectedSecondSelect);
Assert.AreEqual(expectedMainSelect, actualStatement);
}
public void TestCreateSqlStatement_WithEmptyOrderCriteria()
{
//---------------Set up test pack-------------------
IClassDef classDef = MyBO.LoadDefaultClassDef();
ISelectQuery selectQuery = QueryBuilder.CreateSelectQuery(classDef);
selectQuery.OrderCriteria = new OrderCriteria();
SelectQueryDB query = new SelectQueryDB(selectQuery, DatabaseConnection.CurrentConnection);
//---------------Assert PreConditions---------------
//---------------Execute Test ----------------------
ISqlStatement statement = query.CreateSqlStatement(_sqlFormatter);
//---------------Test Result -----------------------
string statementString = statement.Statement.ToString();
StringAssert.EndsWith("FROM [MyBO] a1", statementString, "An empty OrderCriteria should be ignored");
//---------------Tear Down -------------------------
}
public void Test_CreateSQL_SelectCountQuery()
{
//---------------Set up test pack-------------------
DatabaseConnection.CurrentConnection = new DatabaseConnectionStub();
IClassDef classDef = MyBO.LoadClassDefs_OneProp();
//-------------Assert Preconditions -------------
//---------------Execute Test ----------------------
ISelectQuery selectQuery = QueryBuilder.CreateSelectCountQuery(classDef);
SelectQueryDB query = new SelectQueryDB(selectQuery, DatabaseConnection.CurrentConnection);
ISqlStatement statement = query.CreateSqlStatement();
//---------------Test Result -----------------------
Assert.AreEqual("SELECT Count(*) FROM MyBO a1", statement.Statement.ToString());
}
public void TestCreateSqlStatement_WithOrderFields_MixedOrder()
{
//---------------Set up test pack-------------------
IClassDef classDef = MyBO.LoadDefaultClassDef();
ISelectQuery selectQuery = QueryBuilder.CreateSelectQuery(classDef);
selectQuery.OrderCriteria = QueryBuilder.CreateOrderCriteria(classDef, "MyBoID DESC, TestProp ASC");
SelectQueryDB query = new SelectQueryDB(selectQuery, DatabaseConnection.CurrentConnection);
//---------------Assert PreConditions---------------
//---------------Execute Test ----------------------
ISqlStatement statement = query.CreateSqlStatement(_sqlFormatter);
//---------------Test Result -----------------------
string statementString = statement.Statement.ToString();
StringAssert.EndsWith("ORDER BY a1.[MyBoID] DESC, a1.[TestProp] ASC", statementString);
//---------------Tear Down -------------------------
}
public void TestSetupAliases_SetsAliasOnSource()
{
//---------------Set up test pack-------------------
SelectQuery selectQuery = new SelectQuery();
const string sourceName = "mysource";
var source1 = new Source(sourceName);
selectQuery.Source = source1;
selectQuery.Criteria = null;
//---------------Execute Test ----------------------
SelectQueryDB selectQueryDb = new SelectQueryDB(selectQuery, DatabaseConnection.CurrentConnection);
//---------------Test Result -----------------------
Assert.AreEqual("a1", selectQueryDb.Aliases[source1.ToString()]);
}
public void TestCreateSqlStatement_WithOrder_ThroughRelationship()
{
//---------------Set up test pack-------------------
new Engine();//TO Load ClassDefs
new Car();//TO Load ClassDefs
new ContactPerson();//TO Load ClassDefs
ClassDef addressClassDef = new Address().ClassDef;
ISelectQuery selectQuery = QueryBuilder.CreateSelectQuery(addressClassDef);
selectQuery.OrderCriteria = QueryBuilder.CreateOrderCriteria(addressClassDef, "ContactPerson.Surname");
SelectQueryDB query = new SelectQueryDB(selectQuery, DatabaseConnection.CurrentConnection);
//---------------Execute Test ----------------------
ISqlStatement statement = query.CreateSqlStatement(_sqlFormatter);
//---------------Test Result -----------------------
string statementString = statement.Statement.ToString();
StringAssert.Contains("JOIN [contact_person] a2 ON a1.[ContactPersonID] = a2.[ContactPersonID]", statementString);
StringAssert.EndsWith("ORDER BY a2.[Surname_field] ASC", statementString);
}
public void TestCreateSqlStatement_NoCriteria()
{
//---------------Set up test pack-------------------
IClassDef classDef = MyBO.LoadDefaultClassDef();
ISelectQuery selectQuery = QueryBuilder.CreateSelectQuery(classDef);
//---------------Assert PreConditions---------------
//---------------Execute Test ----------------------
SelectQueryDB query = new SelectQueryDB(selectQuery, DatabaseConnection.CurrentConnection);
ISqlStatement statement = query.CreateSqlStatement();
//---------------Test Result -----------------------
string statementString = statement.Statement.ToString();
StringAssert.AreEqualIgnoringCase("SELECT a1.MyBoID, a1.TestProp, a1.TestProp2 FROM MyBO a1", statementString);
//---------------Tear Down -------------------------
}
public void TestCreateSqlStatement_WithOrder_ThroughRelationship_TwoLevels()
{
//---------------Set up test pack-------------------
new Engine();//TO Load ClassDefs
new Car();//TO Load ClassDefs
new ContactPerson();//TO Load ClassDefs
ClassDef engineClassDef = new Engine().ClassDef;
ISelectQuery selectQuery = QueryBuilder.CreateSelectQuery(engineClassDef);
selectQuery.OrderCriteria = QueryBuilder.CreateOrderCriteria(engineClassDef, "Car.Owner.Surname");
SelectQueryDB query = new SelectQueryDB(selectQuery, DatabaseConnection.CurrentConnection);
//---------------Assert PreConditions---------------
//---------------Execute Test ----------------------
ISqlStatement statement = query.CreateSqlStatement(_sqlFormatter);
//---------------Test Result -----------------------
string statementString = statement.Statement.ToString();
string expectedJoinSql = "LEFT JOIN [car_table] a2 ON a1.[CAR_ID] = a2.[CAR_ID])";
expectedJoinSql += " LEFT JOIN [contact_person] a3 ON a2.[OWNER_ID] = a3.[ContactPersonID])";
StringAssert.Contains(expectedJoinSql, statementString);
StringAssert.EndsWith("ORDER BY a3.[Surname_field] ASC", statementString);
}
public void Test_CreateSQL_ShouldUseAliasesInJoins()
{
//---------------Set up test pack-------------------
SelectQuery selectQuery = new SelectQuery();
var mysource = new Source("mysource");
selectQuery.Source = mysource;
QueryField fieldOnMySource = new QueryField("testfield", "testfield", mysource);
Source joinedTableSource = new Source("mysource");
joinedTableSource.JoinToSource(new Source("myjoinedtosource"));
QueryField fieldOnJoinedTableSource = new QueryField("testfield", "testfield", joinedTableSource);
joinedTableSource.Joins[0].JoinFields.Add(new Source.Join.JoinField(fieldOnMySource, fieldOnJoinedTableSource));
selectQuery.Fields.Add(fieldOnMySource.FieldName, fieldOnMySource);
selectQuery.Criteria = new Criteria(fieldOnJoinedTableSource, Criteria.ComparisonOp.Equals, "myvalue");
SelectQueryDB query = new SelectQueryDB(selectQuery, DatabaseConnection.CurrentConnection);
SqlFormatter sqlFormatter = new SqlFormatter("[", "]", "", "LIMIT");
//---------------Execute Test ----------------------
ISqlStatement statement = query.CreateSqlStatement(sqlFormatter);
//---------------Test Result -----------------------
StringAssert.AreEqualIgnoringCase(
"SELECT a1.[testfield] FROM ([mysource] a1 " +
"JOIN [myjoinedtosource] a2 on a1.[testfield] = a2.[testfield]) " +
"WHERE a2.[testfield] = ?Param0", statement.Statement.ToString());
}
public void TestCreateSqlStatement_WithOrder_ThroughRelationship_CompositeKey()
{
//---------------Set up test pack-------------------
Car car = new Car();
new ContactPersonCompositeKey();
ISelectQuery selectQuery = QueryBuilder.CreateSelectQuery(car.ClassDef);
selectQuery.OrderCriteria = QueryBuilder.CreateOrderCriteria(car.ClassDef, "Driver.Surname");
SelectQueryDB query = new SelectQueryDB(selectQuery, DatabaseConnection.CurrentConnection);
//---------------Assert PreConditions---------------
//---------------Execute Test ----------------------
ISqlStatement statement = query.CreateSqlStatement(_sqlFormatter);
//---------------Test Result -----------------------
string statementString = statement.Statement.ToString();
StringAssert.Contains("JOIN [ContactPersonCompositeKey] a2 ON a1.[Driver_FK1] = a2.[PK1_Prop1] AND a1.[Driver_FK2] = a2.[PK1_Prop2]) ", statementString);
StringAssert.EndsWith("ORDER BY a2.[Surname] ASC", statementString);
}
