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