public void Should_Support_Nested_Joins_With_Filters_On_The_Nested_Joined_Tables(string joinType, string filterOperator, object filterValue, string filterFormatString) { var sql = GenerateTestSqlWithNestedJoinAndFilters(joinType, filterOperator, filterValue, filterFormatString); using (var sandbox = RequestProviderTestsSandbox.Create()) { var queryExpression = GetQueryExpression(sandbox.FakeCrmDbConnection, sql); // Assert // the filter should have 3 conditions, one for main entity attribue, and others for the linked entities.. // var defaultFilter = queryExpression.Criteria; //.Filters[0]; var defaultFilter = queryExpression.Criteria.Filters[0]; var defaultFilterConditions = defaultFilter.Conditions; Assert.That(defaultFilterConditions.Count, Is.EqualTo(3), "Wrong number of conditions."); var condition = defaultFilterConditions[0]; Assert.That(condition.EntityName, Is.EqualTo("contact")); AssertUtils.AssertFilterExpressionContion("firstname", filterOperator, filterValue, condition); var joinCondition = defaultFilterConditions[1]; Assert.That(joinCondition.EntityName, Is.EqualTo("A")); AssertUtils.AssertFilterExpressionContion("name", filterOperator, filterValue, joinCondition); var nestedjoinCondition = defaultFilterConditions[2]; Assert.That(nestedjoinCondition.EntityName, Is.EqualTo("AC")); AssertUtils.AssertFilterExpressionContion("firstname", filterOperator, filterValue, nestedjoinCondition); } }
public void Should_Support_Selecting_Entity_Metadata() { StringBuilder sqlBuilder = new StringBuilder(); sqlBuilder.AppendLine("SELECT e.MetadataId, e.HasChanged, e.ActivityTypeMask, e.Attributes, e.AutoRouteToOwnerQueue, e.CanBeInManyToMany, e.CanBePrimaryEntityInRelationship, "); sqlBuilder.AppendLine("e.CanBeRelatedEntityInRelationship, e.CanCreateAttributes, e.CanCreateCharts, e.CanCreateForms, e.CanCreateViews, e.CanModifyAdditionalSettings, e.CanTriggerWorkflow, "); sqlBuilder.AppendLine("e.Description, e.DisplayCollectionName, e.DisplayName, e.IconLargeName, e.IconMediumName, e.IconSmallName, e.IsActivity, e.IsActivityParty, e.IsAuditEnabled, "); sqlBuilder.AppendLine("e.IsAvailableOffline, e.IsChildEntity, e.IsConnectionsEnabled, e.IsCustomEntity, e.IsCustomizable, e.IsDocumentManagementEnabled, e.IsDuplicateDetectionEnabled, "); sqlBuilder.AppendLine("e.IsEnabledForCharts, e.IsImportable, e.IsIntersect, e.IsMailMergeEnabled, e.IsMailMergeEnabled, e.IsManaged, e.IsMappable, e.IsReadingPaneEnabled, e.IsRenameable, "); sqlBuilder.AppendLine("e.IsValidForAdvancedFind, e.IsValidForQueue, e.IsVisibleInMobile, e.LogicalName, e.IsCustomEntity, e.ManyToManyRelationships, e.ManyToOneRelationships, "); sqlBuilder.AppendLine("e.ObjectTypeCode, e.OneToManyRelationships, e.OwnershipType, e.PrimaryIdAttribute, e.PrimaryNameAttribute, e.Privileges, e.RecurrenceBaseEntityLogicalName, "); sqlBuilder.AppendLine("e.ReportViewName, e.SchemaName, e.RecurrenceBaseEntityLogicalName "); sqlBuilder.AppendLine("FROM EntityMetadata AS e"); // Arrange var sql = sqlBuilder.ToString(); Console.Write(sql); // Act using (var sandbox = RequestProviderTestsSandbox.Create()) { var queryExpression = GetOrganizationRequest <RetrieveMetadataChangesRequest>(sandbox.FakeCrmDbConnection, sql); // Assert Assert.That(queryExpression, Is.Not.Null); Assert.That(queryExpression.Query, Is.Not.Null); EntityQueryExpression query = queryExpression.Query; Assert.That(query.Properties, Is.Not.Null); MetadataPropertiesExpression props = query.Properties; Assert.That(props.PropertyNames.Count, Is.GreaterThan(1)); } }
public void Should_Support_Insert_Statement_Of_Single_Entity_With_Quoted_Table_Name_And_Quoted_ColumnNames() { // Arrange var sql = "INSERT INTO [contact] ([contactid], firstname, [lastname]) VALUES ('9bf20a16-6034-48e2-80b4-8349bb80c3e2','billy','bob')"; // set up fake metadata provider. // var fakeMetadataProvider = MockRepository.GenerateMock<ICrmMetaDataProvider>(); // var fakeMetadata = GetFakeContactMetadata(); // fakeMetadataProvider.Stub(a => a.GetEntityMetadata("contact")).Return(fakeMetadata); using (var sandbox = RequestProviderTestsSandbox.Create()) { var cmd = new CrmDbCommand(sandbox.FakeCrmDbConnection); cmd.CommandText = sql; // Act var createRequest = GetOrganizationRequest <CreateRequest>(cmd); // Assert Entity targetEntity = createRequest.Target; Assert.That(targetEntity.LogicalName == "contact"); Assert.That(targetEntity.Attributes.ContainsKey("contactid")); Assert.That(targetEntity.Attributes.ContainsKey("firstname")); Assert.That(targetEntity.Attributes.ContainsKey("lastname")); } }
public void Should_Preserve_Casing(bool caseSensitive) { // Arrange var sql = "Select contactid, firstname, lastname, MADE_UpField From contact"; using (var sandbox = RequestProviderTestsSandbox.Create()) { sandbox.FakeCrmDbConnection.Settings.CaseSensitiveColumnNames = caseSensitive; // Act var queryExpression = GetQueryExpression(sandbox.FakeCrmDbConnection, sql); // Assert Assert.That(queryExpression.ColumnSet != null); Assert.That(queryExpression.ColumnSet.Columns.Contains("firstname")); Assert.That(queryExpression.ColumnSet.Columns.Contains("lastname")); if (caseSensitive) { Assert.That(queryExpression.ColumnSet.Columns.Contains("MADE_UpField")); } else { Assert.That(queryExpression.ColumnSet.Columns.Contains("made_upfield")); } } }
public void Should_Support_Update_Statement_Of_Single_Entity_With_Output_All_Columns() { // Arrange var sql = "UPDATE contact SET firstname = 'john', lastname = 'doe' OUTPUT INSERTED.* WHERE contactid = '9bf20a16-6034-48e2-80b4-8349bb80c3e2'"; // set up fake metadata provider. // var fakeMetadataProvider = MockRepository.GenerateMock<ICrmMetaDataProvider>(); // var fakeMetadata = GetFakeContactMetadata(); // fakeMetadataProvider.Stub(a => a.GetEntityMetadata("contact")).Return(fakeMetadata); using (var sandbox = RequestProviderTestsSandbox.Create()) { var cmd = new CrmDbCommand(sandbox.FakeCrmDbConnection); cmd.CommandText = sql; // Act var orgCommand = GetOperation(cmd, System.Data.CommandBehavior.Default); var req = orgCommand.Request as ExecuteMultipleRequest; // var req = GetOrganizationRequest<ExecuteMultipleRequest>(cmd); // Assert Assert.That(req, Is.Not.Null); Assert.That(req.Requests.Count, Is.EqualTo(2)); var createRequest = (UpdateRequest)req.Requests[0]; var retrieveRequest = (RetrieveRequest)req.Requests[1]; Entity targetEntity = createRequest.Target; // Assert.That(targetEntity.Attributes.ContainsKey("contactid")); Assert.That(targetEntity.Attributes.ContainsKey("firstname")); Assert.That(targetEntity.Attributes.ContainsKey("lastname")); var targetRetrieve = retrieveRequest.Target; Assert.That(targetRetrieve.LogicalName, Is.EqualTo("contact")); var idGuid = Guid.Parse("9bf20a16-6034-48e2-80b4-8349bb80c3e2"); Assert.That(targetRetrieve.Id, Is.EqualTo(idGuid)); Assert.That(retrieveRequest.ColumnSet.AllColumns); Assert.That(orgCommand.Columns, Is.Not.Null); Assert.That(orgCommand.Columns.Count, Is.GreaterThan(0)); foreach (var outputColumn in orgCommand.Columns) { Assert.That(outputColumn.ColumnName, Is.Not.Null); Assert.That(outputColumn.ColumnName, Is.Not.EqualTo("")); Assert.That(outputColumn.AttributeMetadata, Is.Not.Null); var attMetadata = outputColumn.AttributeMetadata; Assert.That(attMetadata.AttributeType, Is.Not.Null); } // Assert.That(orgCommand.OperationType, Is.EqualTo(Enums.CrmOperation.UpdateWithRetrieve)); } }
public void Should_Support_Batch_Of_Statements_Via_ExecuteMultipleRequest() { // Arrange Guid id = Guid.Parse("9bf20a16-6034-48e2-80b4-8349bb80c3e2"); string insertSql = string.Format("INSERT INTO contact (contactid, firstname, lastname) VALUES ('{0}','billy','bob');", id); string insertSqlWithOutputClause = "INSERT INTO contact (firstname, lastname) OUTPUT INSERTED.contactid VALUES ('bat','man');"; string updateSql = string.Format("UPDATE contact SET firstname = 'john', lastname = 'doe' WHERE contactid = '{0}';", id); string updateSqlWithOutputClause = string.Format("UPDATE contact SET firstname = 'johny', lastname = 'doe' OUTPUT INSERTED.modifiedon WHERE contactid = '{0}';", id); string deleteSql = string.Format("DELETE FROM contact WHERE contactid = '{0}';", id); string selectSql = "SELECT TOP 10 firstname, lastname FROM contact ORDER BY lastname ASC;"; var sql = insertSql + insertSqlWithOutputClause + updateSql + updateSqlWithOutputClause + deleteSql + selectSql; using (var sandbox = RequestProviderTestsSandbox.Create()) { var cmd = new CrmDbCommand(sandbox.FakeCrmDbConnection); cmd.CommandText = sql; // Act var executeMultipleRequest = GetOrganizationRequest <ExecuteMultipleRequest>(cmd); // Assert Assert.That(executeMultipleRequest, Is.Not.Null); Assert.That(executeMultipleRequest.Requests, Is.Not.Null); // the first statement should translate to a CreateRequest var createRequest = executeMultipleRequest.Requests[0]; AssertRequestMessageTypeIs <CreateRequest>(createRequest); // the second statement should translate to a CreateRequest (no retreive as the output of id doesn't require a seperate retrieve) var createRequestNumber2 = executeMultipleRequest.Requests[1]; AssertRequestMessageTypeIs <CreateRequest>(createRequestNumber2); // the third statement should translate to a UpdateRequest var updateRequest = executeMultipleRequest.Requests[2]; AssertRequestMessageTypeIs <UpdateRequest>(updateRequest); // the fourth statement should translate to a UpdateRequest, anda RetrieveRequest var updateRequestNumber2 = executeMultipleRequest.Requests[3]; AssertRequestMessageTypeIs <UpdateRequest>(updateRequestNumber2); var retrieveRequestForUpdateOutputClause = executeMultipleRequest.Requests[4]; AssertRequestMessageTypeIs <RetrieveRequest>(retrieveRequestForUpdateOutputClause); // the fifth statement should translate to a DeleteRequest var deleteRequest = executeMultipleRequest.Requests[5]; AssertRequestMessageTypeIs <DeleteRequest>(deleteRequest); // the sixth statement should translate to a RetrieveMultipleRequest var retrieveMultipleRequest = executeMultipleRequest.Requests[6]; AssertRequestMessageTypeIs <RetrieveMultipleRequest>(retrieveMultipleRequest); } }
public void Should_Not_Support_Select_TOP_X_PERCENT() { // Arrange var sql = "Select TOP 10 PERCENT contactid, firstname, lastname From contact"; using (var sandbox = RequestProviderTestsSandbox.Create()) { // Act var queryExpression = GetQueryExpression(sandbox.FakeCrmDbConnection, sql); } }
public void Should_Throw_When_Insert_Statement_Has_Different_Number_Of_Columns_To_Values_Specified() { // Arrange var sql = "INSERT INTO contact (firstname) VALUES (1, 'bloggs')"; // Act using (var sandbox = RequestProviderTestsSandbox.Create()) { var queryExpression = GetOrganizationRequest <CreateRequest>(sandbox.FakeCrmDbConnection, sql); } }
public void Should_Throw_When_Select_Statement_Does_Not_Have_Any_Column_Names_Or_Star() { // Arrange var sql = "Select From contact"; // Act using (var sandbox = RequestProviderTestsSandbox.Create()) { var queryExpression = GetQueryExpression(sandbox.FakeCrmDbConnection, sql); } }
public void Should_Throw_When_Select_Statement_Does_Not_Have_A_From_Clause() { // Arrange var sql = "Select * From"; // Act using (var sandbox = RequestProviderTestsSandbox.Create()) { var queryExpression = GetQueryExpression(sandbox.FakeCrmDbConnection, sql); } }
public void Should_Support_Parameters(string filterOperator, object value, string filterFormatString) { // Arrange // Formulate DML (SQL) statement from test case data. var columnName = "firstname"; if (value == null || !value.GetType().IsArray) { filterFormatString = string.Format(filterFormatString, columnName, filterOperator); } else { throw new NotImplementedException(); } var sql = string.Format("Select contactid, firstname, lastname From contact Where {0} ", filterFormatString); // Create test subject. using (var sandbox = RequestProviderTestsSandbox.Create()) { var cmd = new CrmDbCommand(sandbox.FakeCrmDbConnection); cmd.CommandText = sql; var param = cmd.CreateParameter(); param.ParameterName = "@param1"; param.Value = value; cmd.Parameters.Add(param); // Act var subject = ResolveTestSubjectInstance(); // Act // Ask our test subject to Convert the SelectBuilder to a Query Expression. var queryExpression = base.GetQueryExpression(cmd); // Assert // Verify that the Query Expression looks as expected in order to work agaisnt the Dynamics SDK. Assert.That(queryExpression.ColumnSet.AllColumns == false); Assert.That(queryExpression.ColumnSet.Columns[0] == "contactid"); Assert.That(queryExpression.ColumnSet.Columns[1] == "firstname"); Assert.That(queryExpression.EntityName == "contact"); //var defaultConditons = queryExpression.Criteria.Conditions; var defaultConditons = queryExpression.Criteria.Filters[0].Conditions; Assert.That(defaultConditons.Count, Is.EqualTo(1)); //Assert.That(defaultFilterGroup.FilterOperator, Is.EqualTo(LogicalOperator.And)); Assert.That(defaultConditons[0].AttributeName == "firstname"); var condition = defaultConditons[0]; AssertUtils.AssertFilterExpressionContion("firstname", filterOperator, value, condition); } }
public void Should_Support_Contains_Operator_With_String_Literal_Value(string sqlOperator) { var columnName = "firstname"; var conditionValue = "Albert"; //string.IsNullOrEmpty(literalValue) ? "Albert" : literalValue; var sql = string.Format("Select contactid, firstname, lastname From contact Where {1}({0}, '{2}'))", columnName, sqlOperator, conditionValue); using (var sandbox = RequestProviderTestsSandbox.Create()) { var queryExpression = GetQueryExpression(sandbox.FakeCrmDbConnection, sql); // Assert AssertUtils.AssertQueryContainsSingleFilterCondition(queryExpression, columnName, sqlOperator, conditionValue); } }
public void Should_Support_Select_TOP_X() { // Arrange var sql = "Select TOP 15 contactid, firstname, lastname From contact"; using (var sandbox = RequestProviderTestsSandbox.Create()) { // Act var queryExpression = GetQueryExpression(sandbox.FakeCrmDbConnection, sql); // Assert Assert.That(queryExpression.TopCount == 15); } }
public void Should_Support_Various_Operators_With_Numeric_Literal_Value(string sqlOperator) { var columnName = "firstname"; var conditionValue = 1; var sql = string.Format("Select contactid, firstname, lastname From contact Where {0} {1} {2}", columnName, sqlOperator, conditionValue); using (var sandbox = RequestProviderTestsSandbox.Create()) { var queryExpression = GetQueryExpression(sandbox.FakeCrmDbConnection, sql); // Assert AssertUtils.AssertQueryContainsSingleFilterCondition(queryExpression, columnName, sqlOperator, conditionValue); } }
public void Should_Throw_When_The_Where_Equals_Clause_Does_Not_Refer_To_A_Column_Name(string sqlOperator) { // Arrange var sql = "Select contactid, firstname, lastname From contact Where 'Julius' {0} 'Julius' and lastname {0} 'Caeser'"; sql = string.Format(sql, sqlOperator); using (var sandbox = RequestProviderTestsSandbox.Create()) { // Act var queryExpression = base.GetQueryExpression(sandbox.FakeCrmDbConnection, sql); } }
public void Should_Support_Select_Statement_Containing_Star() { // Arrange var sql = "Select * From contact"; using (var sandbox = RequestProviderTestsSandbox.Create()) { // Act var queryExpression = GetQueryExpression(sandbox.FakeCrmDbConnection, sql); // Assert Assert.That(queryExpression.ColumnSet.AllColumns == true); Assert.That(queryExpression.EntityName == "contact"); } }
private void Test_That_Sql_Update_Statement_With_A_Literal_Value_Has_The_Value_Translated_To <T>(T assertValue, string fieldname, string sqlLiteralValue) { var existingContactGuid = Guid.NewGuid(); string sqlFormatString = "UPDATE contact SET " + fieldname + " = {0} WHERE contactid = '{1}'"; var sqlWithValue = string.Format(sqlFormatString, sqlLiteralValue, existingContactGuid); using (var sandbox = RequestProviderTestsSandbox.Create()) { var cmd = new CrmDbCommand(sandbox.FakeCrmDbConnection); cmd.CommandText = sqlWithValue; var request = GetOrganizationRequest <UpdateRequest>(cmd); Entity targetEntity = request.Target; AssertAttributeIsValue <T>(targetEntity, fieldname, assertValue); } }
public void Should_Support_Nested_Joins(string joinType) { var sql = string.Format("Select C.contactid, C.firstname, C.lastname, AC.name, A.addressline1 From contact C {0} JOIN customeraddress A on C.contactid = A.contactid {0} JOIN account AC on A.addressid = AC.addressid", joinType); var join = JoinOperator.Natural; switch (joinType) { case "INNER": join = JoinOperator.Inner; // Enum.Parse(typeof(JoinOperator), joinType) break; case "LEFT": join = JoinOperator.LeftOuter; break; } using (var sandbox = RequestProviderTestsSandbox.Create()) { var queryExpression = GetQueryExpression(sandbox.FakeCrmDbConnection, sql); Assert.That(queryExpression.ColumnSet.Columns.Count, Is.EqualTo(3)); Assert.That(queryExpression.LinkEntities, Is.Not.Null); Assert.That(queryExpression.LinkEntities[0], Is.Not.Null); Assert.That(queryExpression.LinkEntities[0].LinkFromEntityName, Is.EqualTo("contact")); Assert.That(queryExpression.LinkEntities[0].LinkToEntityName, Is.EqualTo("customeraddress")); Assert.That(queryExpression.LinkEntities[0].LinkFromAttributeName, Is.EqualTo("contactid")); Assert.That(queryExpression.LinkEntities[0].LinkToAttributeName, Is.EqualTo("contactid")); Assert.That(queryExpression.LinkEntities[0].EntityAlias, Is.EqualTo("A")); Assert.That(queryExpression.LinkEntities[0].JoinOperator, Is.EqualTo(join)); Assert.That(queryExpression.LinkEntities[0].Columns, Is.Not.Null); Assert.That(queryExpression.LinkEntities[0].Columns.Columns, Contains.Item("addressline1")); var addressEntity = queryExpression.LinkEntities[0]; Assert.That(addressEntity.LinkEntities, Is.Not.Null); Assert.That(addressEntity.LinkEntities[0], Is.Not.Null); Assert.That(addressEntity.LinkEntities[0].LinkFromEntityName, Is.EqualTo("customeraddress")); Assert.That(addressEntity.LinkEntities[0].LinkToEntityName, Is.EqualTo("account")); Assert.That(addressEntity.LinkEntities[0].LinkFromAttributeName, Is.EqualTo("addressid")); Assert.That(addressEntity.LinkEntities[0].LinkToAttributeName, Is.EqualTo("addressid")); Assert.That(addressEntity.LinkEntities[0].EntityAlias, Is.EqualTo("AC")); Assert.That(addressEntity.LinkEntities[0].JoinOperator, Is.EqualTo(join)); Assert.That(addressEntity.LinkEntities[0].Columns, Is.Not.Null); Assert.That(addressEntity.LinkEntities[0].Columns.Columns, Contains.Item("name")); } }
public void Should_Support_Insert_Statement_Of_Single_Entity_With_Output_Id() { // Arrange var sql = "INSERT INTO contact (firstname, lastname) OUTPUT INSERTED.contactid VALUES ('billy','bob')"; // set up fake metadata provider. // var fakeMetadataProvider = MockRepository.GenerateMock<ICrmMetaDataProvider>(); // var fakeMetadata = GetFakeContactMetadata(); // fakeMetadataProvider.Stub(a => a.GetEntityMetadata("contact")).Return(fakeMetadata); using (var sandbox = RequestProviderTestsSandbox.Create()) { var cmd = new CrmDbCommand(sandbox.FakeCrmDbConnection); cmd.CommandText = sql; // Act var orgCommand = GetOperation(cmd, System.Data.CommandBehavior.Default); var req = orgCommand.Request as CreateRequest; // var req = GetOrganizationRequest<ExecuteMultipleRequest>(cmd); // Assert Assert.That(req, Is.Not.Null); Entity targetEntity = req.Target; //Assert.That(targetEntity.Attributes.ContainsKey("contactid")); Assert.That(targetEntity.Attributes.ContainsKey("firstname")); Assert.That(targetEntity.Attributes.ContainsKey("lastname")); // Assert.That(req.Requests.Count, Is.EqualTo(2)); Assert.That(orgCommand.Columns, Is.Not.Null); Assert.That(orgCommand.Columns.Count, Is.GreaterThan(0)); var outputColumn = orgCommand.Columns[0]; Assert.That(outputColumn.ColumnName, Is.EqualTo("contactid")); Assert.That(outputColumn.AttributeMetadata, Is.Not.Null); var attMetadata = outputColumn.AttributeMetadata; Assert.That(attMetadata.AttributeType, Is.EqualTo(AttributeTypeCode.Uniqueidentifier)); Assert.IsTrue(attMetadata.IsPrimaryId.GetValueOrDefault()); // Assert.That(orgCommand.OperationType, Is.EqualTo(Enums.CrmOperation.Create)); } }
public void Should_Support_Select_Statement_From_Dbo_Prefixed_Column_Names() { // Arrange var sql = "Select dbo.contact.contactid, dbo.contact.firstname, dbo.contact.lastname From dbo.contact"; using (var sandbox = RequestProviderTestsSandbox.Create()) { // Act var queryExpression = GetQueryExpression(sandbox.FakeCrmDbConnection, sql); // Assert Assert.That(queryExpression.ColumnSet.AllColumns == false); Assert.That(queryExpression.ColumnSet.Columns[0] == "contactid"); Assert.That(queryExpression.ColumnSet.Columns[1] == "firstname"); Assert.That(queryExpression.ColumnSet.Columns[2] == "lastname"); Assert.That(queryExpression.EntityName == "contact"); } }
public void Does_Not_Support_Insert_Of_An_Entity_With_All_Default_Values() { // Arrange var sql = "INSERT INTO contact DEFAULT VALUES"; // Act using (var sandbox = RequestProviderTestsSandbox.Create()) { var createRequest = GetOrganizationRequest <CreateRequest>(sandbox.FakeCrmDbConnection, sql); Entity targetEntity = createRequest.Target; Assert.That(targetEntity.Attributes.ContainsKey("contactid")); Assert.That(targetEntity.Attributes.ContainsKey("firstname")); Assert.That(targetEntity.Attributes.ContainsKey("lastname")); } }
private void Test_That_Sql_Insert_Statement_With_A_Literal_Value_Has_The_Value_Translated_To <T>(T assertValue, string fieldname, string sqlLiteralValue) { string sqlFormatString = "INSERT INTO contact (" + fieldname + ")"; sqlFormatString = sqlFormatString + " VALUES ({0})"; var sqlWithValue = string.Format(sqlFormatString, sqlLiteralValue); using (var sandbox = RequestProviderTestsSandbox.Create()) { var cmd = new CrmDbCommand(sandbox.FakeCrmDbConnection); cmd.CommandText = sqlWithValue; var createRequest = GetOrganizationRequest <CreateRequest>(cmd); Entity targetEntity = createRequest.Target; AssertAttributeIsValue <T>(targetEntity, fieldname, assertValue); } }
public void Should_Support_Filter_Groups() { // Arrange var sql = "SELECT c.firstname, c.lastname FROM contact c WHERE (c.firstname = 'Albert' AND c.lastname = 'Einstein') OR (c.firstname = 'Max' AND c.lastname = 'Planck')"; using (var sandbox = RequestProviderTestsSandbox.Create()) { // Act var queryExpression = base.GetQueryExpression(sandbox.FakeCrmDbConnection, sql); //Assert Assert.That(queryExpression.ColumnSet.Columns.Count, Is.EqualTo(2)); Assert.That(queryExpression.Criteria.Filters, Is.Not.Null); Assert.That(queryExpression.Criteria.Filters.Count, Is.EqualTo(1)); Assert.That(queryExpression.Criteria.FilterOperator, Is.EqualTo(LogicalOperator.And)); var defaultFilterGroup = queryExpression.Criteria.Filters[0]; Assert.That(defaultFilterGroup.Filters.Count, Is.EqualTo(1)); Assert.That(defaultFilterGroup.FilterOperator, Is.EqualTo(LogicalOperator.And)); var topLevelFilterGroup = defaultFilterGroup.Filters[0]; Assert.That(topLevelFilterGroup.FilterOperator == LogicalOperator.Or); var albertEinstenFilter = topLevelFilterGroup.Filters[0]; Assert.That(albertEinstenFilter.FilterOperator == LogicalOperator.And); Assert.That(albertEinstenFilter.Conditions, Is.Not.Null); Assert.That(albertEinstenFilter.Conditions.Count, Is.EqualTo(2)); Assert.That(albertEinstenFilter.Conditions[0].Values, Contains.Item("Albert")); Assert.That(albertEinstenFilter.Conditions[0].Operator, Is.EqualTo(ConditionOperator.Equal)); Assert.That(albertEinstenFilter.Conditions[1].Values, Contains.Item("Einstein")); Assert.That(albertEinstenFilter.Conditions[1].Operator, Is.EqualTo(ConditionOperator.Equal)); var maxPlanckFilter = topLevelFilterGroup.Filters[1]; Assert.That(maxPlanckFilter.FilterOperator == LogicalOperator.And); Assert.That(maxPlanckFilter.Conditions, Is.Not.Null); Assert.That(maxPlanckFilter.Conditions.Count, Is.EqualTo(2)); Assert.That(maxPlanckFilter.Conditions[0].Values, Contains.Item("Max")); Assert.That(maxPlanckFilter.Conditions[0].Operator, Is.EqualTo(ConditionOperator.Equal)); Assert.That(maxPlanckFilter.Conditions[1].Values, Contains.Item("Planck")); Assert.That(maxPlanckFilter.Conditions[1].Operator, Is.EqualTo(ConditionOperator.Equal)); } }
public void Should_Support_Order_By_Descending_Single_Column() { // Arrange var sql = "SELECT TOP 10 firstname, lastname FROM contact ORDER BY lastname DESC "; using (var sandbox = RequestProviderTestsSandbox.Create()) { // Act var queryExpression = GetQueryExpression(sandbox.FakeCrmDbConnection, sql); // Assert Assert.That(queryExpression.Orders, Is.Not.Null); Assert.That(queryExpression.Orders.Count, Is.EqualTo(1)); var order = queryExpression.Orders.Single(); Assert.That(order.AttributeName, Is.EqualTo("lastname")); Assert.That(order.OrderType, Is.EqualTo(OrderType.Descending)); } }
public void Should_Support_In_Operator_With_String_Literal_List(string sqlOperator, string[] valuesArray) { var columnName = "firstname"; var args = new List <object>(); args.Add(columnName); args.Add(sqlOperator); args.AddRange(valuesArray); var sql = string.Format("Select contactid, firstname, lastname From contact Where {0} {1} ('{2}', '{3}')", args.ToArray()); using (var sandbox = RequestProviderTestsSandbox.Create()) { // Act var queryExpression = GetQueryExpression(sandbox.FakeCrmDbConnection, sql); // Assert AssertUtils.AssertQueryContainsSingleFilterCondition(queryExpression, columnName, sqlOperator, valuesArray); } }
public void Should_Support_Update_Statement_Of_Single_Entity_With_Output_Columns() { // Arrange var sql = "UPDATE contact SET firstname = 'john', lastname = 'doe' OUTPUT INSERTED.modifiedon WHERE contactid = '9bf20a16-6034-48e2-80b4-8349bb80c3e2'"; using (var sandbox = RequestProviderTestsSandbox.Create()) { var cmd = new CrmDbCommand(sandbox.FakeCrmDbConnection); cmd.CommandText = sql; // Act var orgCommand = GetOperation(cmd, System.Data.CommandBehavior.Default); var req = orgCommand.Request as ExecuteMultipleRequest; // Assert Assert.That(req, Is.Not.Null); Assert.That(req.Requests.Count, Is.EqualTo(2)); var createRequest = (UpdateRequest)req.Requests[0]; var retrieveRequest = (RetrieveRequest)req.Requests[1]; Entity targetEntity = createRequest.Target; Assert.That(targetEntity.Attributes.ContainsKey("firstname")); Assert.That(targetEntity.Attributes.ContainsKey("lastname")); var targetRetrieve = retrieveRequest.Target; Assert.That(targetRetrieve.LogicalName, Is.EqualTo("contact")); var idGuid = Guid.Parse("9bf20a16-6034-48e2-80b4-8349bb80c3e2"); Assert.That(targetRetrieve.Id, Is.EqualTo(idGuid)); Assert.That(retrieveRequest.ColumnSet.Columns.Contains("modifiedon")); Assert.That(orgCommand.Columns, Is.Not.Null); Assert.That(orgCommand.Columns.Count, Is.GreaterThan(0)); var outputColumn = orgCommand.Columns[0]; Assert.That(outputColumn.ColumnName, Is.EqualTo("modifiedon")); Assert.That(outputColumn.AttributeMetadata, Is.Not.Null); var attMetadata = outputColumn.AttributeMetadata; Assert.That(attMetadata.AttributeType, Is.EqualTo(AttributeTypeCode.DateTime)); // Assert.That(orgCommand.OperationType, Is.EqualTo(Enums.CrmOperation.UpdateWithRetrieve)); } }
public void Should_Support_Update_Statement_Of_Single_Entity_With_Named_Columns() { // Arrange var sql = "UPDATE contact SET firstname = 'john', lastname = 'doe' WHERE contactid = '9bf20a16-6034-48e2-80b4-8349bb80c3e2'"; using (var sandbox = RequestProviderTestsSandbox.Create()) { var cmd = new CrmDbCommand(sandbox.FakeCrmDbConnection); cmd.CommandText = sql; // Act var request = GetOrganizationRequest <UpdateRequest>(cmd); // Assert Entity targetEntity = request.Target; Assert.That(targetEntity.Attributes.ContainsKey("firstname")); Assert.That((string)targetEntity.Attributes["firstname"], Is.EqualTo("john")); Assert.That(targetEntity.Attributes.ContainsKey("lastname")); Assert.That((string)targetEntity.Attributes["lastname"], Is.EqualTo("doe")); } }
public void Should_Support_Joins_With_Filters_On_The_Joined_Table(string joinType, string filterOperator, object filterValue, string filterFormatString) { var sql = GetTestSqlWithJoinAndFilters(joinType, filterOperator, filterValue, filterFormatString); // Ask our test subject to Convert the SelectBuilder to a Query Expression. using (var sandbox = RequestProviderTestsSandbox.Create()) { var queryExpression = GetQueryExpression(sandbox.FakeCrmDbConnection, sql); // There should be filter criteria on the main entity and also on the link entity. //var defaultConditons = queryExpression.Criteria.Conditions; var defaultConditons = queryExpression.Criteria.Filters[0].Conditions; Assert.That(defaultConditons.Count, Is.EqualTo(2), "There should be two conditions."); var condition = defaultConditons[0]; AssertUtils.AssertFilterExpressionContion("firstname", filterOperator, filterValue, condition); var joinCondition = defaultConditons[1]; AssertUtils.AssertFilterExpressionContion("name", filterOperator, filterValue, joinCondition); } }
public void Should_Support_Insert_With_the_Entity_Id_Specified() { // Arrange Guid id = Guid.Parse("9bf20a16-6034-48e2-80b4-8349bb80c3e2"); var sql = string.Format("INSERT INTO contact (contactid) VALUES ('{0}')", id); using (var sandbox = RequestProviderTestsSandbox.Create()) { var cmd = new CrmDbCommand(sandbox.FakeCrmDbConnection); cmd.CommandText = sql; // Act var createRequest = GetOrganizationRequest <CreateRequest>(cmd); Entity targetEntity = createRequest.Target; Assert.That(targetEntity.Id, Is.EqualTo(id)); Assert.That(targetEntity.Attributes.ContainsKey("contactid")); Assert.That(targetEntity["contactid"], Is.EqualTo(id)); } }
public void Should_Support_Insert_Statement_Of_Single_Entity_With_Quoted_Table_Name() { // Arrange var sql = "INSERT INTO [contact] (contactid, firstname, lastname) VALUES ('9bf20a16-6034-48e2-80b4-8349bb80c3e2','billy','bob')"; using (var sandbox = RequestProviderTestsSandbox.Create()) { var cmd = new CrmDbCommand(sandbox.FakeCrmDbConnection); cmd.CommandText = sql; // Act var createRequest = GetOrganizationRequest <CreateRequest>(cmd); // Assert Entity targetEntity = createRequest.Target; Assert.That(targetEntity.LogicalName == "contact"); Assert.That(targetEntity.Attributes.ContainsKey("contactid")); Assert.That(targetEntity.Attributes.ContainsKey("firstname")); Assert.That(targetEntity.Attributes.ContainsKey("lastname")); } }