/// <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); }