protected virtual OrganizationRequestBuilderVisitor GetVisitor(CrmDbCommand command)
{
var metaDataProvider = command.CrmDbConnection.MetadataProvider;
var commandParams = command.Parameters;
var settings = command.CrmDbConnection.Settings;
return new OrganizationRequestBuilderVisitor(metaDataProvider, commandParams, _DynamicsAttributeTypeProvider, settings);
}
public void Should_Support_Deletion_Of_A_Single_Entity_By_Id()
{
// Arrange
Guid id = Guid.NewGuid();
var sql = "DELETE FROM contact WHERE contactid = '" + id + "'";
// set up fake metadata provider.
// var fakeMetadataProvider = MockRepository.GenerateMock<ICrmMetaDataProvider>();
// var fakeMetadata = GetFakeContactMetadata();
// fakeMetadataProvider.Stub(a => a.GetEntityMetadata("contact")).Return(fakeMetadata);
var fakeConn = MockRepository.GenerateMock<CrmDbConnection>();
fakeConn.Stub(a => a.MetadataProvider).Return(new FakeContactMetadataProvider());
var cmd = new CrmDbCommand(fakeConn);
cmd.CommandText = sql;
// Act
var deleteRequest = GetOrganizationRequest<DeleteRequest>(cmd);
// Assert
EntityReference targetEntityRef = deleteRequest.Target;
Assert.That(targetEntityRef, Is.Not.Null);
Assert.That(targetEntityRef.Id, Is.EqualTo(id));
}
private ICrmOperation GetOperationFromTableDirectCommand(CrmDbCommand command, CommandBehavior behavior)
{
// No need to parse the command text as SQL because table direct commands should just contain the table name.
// Therefore just construct a command that will execute a retreive multiple for the entity name specified.
var request = GetRetrieveMultipleRequest(command, behavior);
var result = new SelectMultipleEntitiesOperation(null, request);
result.CommandBehavior = behavior;
result.DbCommand = command;
// result.OperationType = Enums.CrmOperation.RetrieveMultiple;
return result;
}
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 ICrmOperation GetOperation(CrmDbCommand command, CommandBehavior behavior)
{
bool schemaOnly = (behavior & CommandBehavior.SchemaOnly) > 0;
ICrmOperation result = null;
switch (command.CommandType)
{
case CommandType.StoredProcedure:
throw new System.NotImplementedException();
case CommandType.TableDirect:
result = GetOperationFromTableDirectCommand(command, behavior);
break;
case CommandType.Text:
result = GetOperationFromTextCommand(command, behavior);
break;
default:
throw new System.NotImplementedException();
}
return result;
}
public DataTable GetForeignKeys(CrmDbConnection crmDbConnection, string[] restrictions)
{
// throw new NotImplementedException();
string catalog = GetRestrictionOrNull(0, restrictions);
string schema = GetRestrictionOrNull(1, restrictions);
string constraintTable = GetRestrictionOrNull(2, restrictions);
string constraintName = GetRestrictionOrNull(3, restrictions);
// string entityName = GetRestrictionOrNull(0, restrictions);
// string constraintName = GetRestrictionOrNull(1, restrictions);
bool hasConstraintTableFilter = !string.IsNullOrEmpty(constraintTable);
bool hasConstraintNameFilter = !string.IsNullOrEmpty(constraintName);
string commandText = "SELECT o.* FROM entitymetadata e INNER JOIN onetomanyrelationshipmetadata o ON e.MetadataId = o.MetadataId ";
if (hasConstraintTableFilter || hasConstraintNameFilter)
{
commandText += "WHERE ";
if (hasConstraintTableFilter)
{
commandText += " (e.LogicalName = '" + constraintTable + "') AND (o.referencingentity = '" + constraintTable + "')";
}
if (hasConstraintTableFilter && hasConstraintNameFilter)
{
commandText += " AND ";
}
if (hasConstraintNameFilter)
{
commandText += " (o.SchemaName = '" + constraintName + "')";
}
}
var command = new CrmDbCommand(crmDbConnection);
command.CommandText = commandText;
var adapter = new CrmDataAdapter(command);
var dataTable = new DataTable();
dataTable.Locale = CultureInfo.InvariantCulture;
adapter.Fill(dataTable);
// dataTable.AsDataView().RowFilter = "ReferencingEntity = '" + entityName +
dataTable.Columns.Add("CONSTRAINT_CATALOG", typeof(string), string.Format("'{0}'", crmDbConnection.ConnectionInfo.OrganisationName)).SetOrdinal(0);
dataTable.Columns.Add("CONSTRAINT_SCHEMA", typeof(string), string.Format("'{0}'", DefaultSchema)).SetOrdinal(1);
dataTable.Columns.Add("CONSTRAINT_NAME", typeof(string), "o.schemaname").SetOrdinal(2);
//dataTable.Columns["o.schemaname"].ColumnName = "CONSTRAINT_NAME";
//dataTable.Columns["CONSTRAINT_NAME"].SetOrdinal(2);
// dataTable.Columns.Add("CONSTRAINT_NAME", typeof(string), "''");
dataTable.Columns.Add("TABLE_CATALOG", typeof(string), string.Format("'{0}'", crmDbConnection.ConnectionInfo.OrganisationName)).SetOrdinal(3);
dataTable.Columns.Add("TABLE_SCHEMA", typeof(string), string.Format("'{0}'", DefaultSchema)).SetOrdinal(4);
dataTable.Columns["o.referencingentity"].ColumnName = "TABLE_NAME";
dataTable.Columns["TABLE_NAME"].SetOrdinal(5);
dataTable.Columns.Add("CONSTRAINT_TYPE", typeof(string), "'FOREIGN KEY'").SetOrdinal(6);
dataTable.Columns.Add("IS_DEFERRABLE", typeof(string), "'NO'").SetOrdinal(7);
dataTable.Columns.Add("INITIALLY_DEFERRED", typeof(string), "'NO'").SetOrdinal(8);
//if (hasEntityFilter)
//{
// filteredView.RowFilter = "TABLE_NAME = '" + entityName + "'";
//}
//dataTable = filteredView.ToTable(true);
if (hasConstraintNameFilter)
{
var filteredView = dataTable.AsDataView();
filteredView.RowFilter = "CONSTRAINT_NAME = '" + constraintName + "'";
dataTable = filteredView.ToTable(true);
}
//else
//{
// dataTable = filteredView.ToTable(true);
//}
return dataTable;
}
public DataTable GetForeignKeyColumns(CrmDbConnection crmDbConnection, string[] restrictions)
{
string catalog = GetRestrictionOrNull(0, restrictions);
string schema = GetRestrictionOrNull(1, restrictions);
string table = GetRestrictionOrNull(2, restrictions);
string constraintname = GetRestrictionOrNull(3, restrictions);
string columnname = GetRestrictionOrNull(4, restrictions);
bool hasEntityFilter = !string.IsNullOrEmpty(table);
bool hasColumnFilter = !string.IsNullOrEmpty(columnname);
bool hasConstraintNameFilter = !string.IsNullOrEmpty(constraintname);
string commandText = "SELECT o.* FROM entitymetadata e INNER JOIN onetomanyrelationshipmetadata o ON e.MetadataId = o.MetadataId";
if (hasEntityFilter || hasColumnFilter || hasConstraintNameFilter)
{
commandText += " WHERE ";
if (hasEntityFilter)
{
commandText += " (e.LogicalName = '" + table + "') AND (o.ReferencingEntity = '" + table + "')";
}
if (hasEntityFilter && (hasConstraintNameFilter || hasColumnFilter))
{
commandText += " AND ";
}
if (hasConstraintNameFilter)
{
commandText += " (o.SchemaName = '" + constraintname + "')";
}
if (hasConstraintNameFilter && hasColumnFilter)
{
commandText += " AND ";
}
if (hasColumnFilter)
{
commandText += " (o.ReferencingAttribute = '" + columnname + "')";
}
}
var command = new CrmDbCommand(crmDbConnection);
command.CommandText = commandText;
var adapter = new CrmDataAdapter(command);
var dataTable = new DataTable();
dataTable.Locale = CultureInfo.InvariantCulture;
adapter.Fill(dataTable);
// need to get columns seperately then join to get column ordinal
commandText = "SELECT entitymetadata.LogicalName, a.LogicalName, a.ColumnNumber FROM entitymetadata JOIN attributemetadata a on entitymetadata.MetadataId = a.MetadataId";
if (hasEntityFilter || hasColumnFilter)
{
commandText += "AND ";
if (hasEntityFilter)
{
commandText += " (entitymetadata.LogicalName = '" + table + "')";
}
if (hasEntityFilter && hasColumnFilter)
{
commandText += " AND ";
}
if (hasColumnFilter)
{
commandText += " (a.LogicalName = '" + columnname + "')";
}
}
//var columnsCommand = new CrmDbCommand(crmDbConnection);
//columnsCommand.CommandText = commandText;
//var columnsAdaptor = new CrmDataAdapter(columnsCommand);
//var columnsDatatable = new DataTable();
//columnsDatatable.Locale = CultureInfo.InvariantCulture;
//columnsAdaptor.Fill(columnsDatatable);
//DataSet fkInfoDataset = new DataSet();
//fkInfoDataset.Tables.Add(dataTable);
//fkInfoDataset.Tables.Add(columnsDatatable);
//var parentCols = new DataColumn[] { dataTable.Columns["e.LogicalName"], dataTable.Columns["o.ReferencingAttribute"] };
//var childCols = new DataColumn[] { columnsDatatable.Columns["LogicalName"], columnsDatatable.Columns["a.LogicalName"] };
//DataRelation columnInfoRelation = new DataRelation("columnJoin", parentCols, childCols);
//fkInfoDataset.Relations.Add(columnInfoRelation);
//var view = dataTable.AsDataView();
//view.j
dataTable.Columns.Add("constraint_catalog", typeof(string), string.Format("'{0}'", crmDbConnection.ConnectionInfo.OrganisationName)).SetOrdinal(0);
dataTable.Columns.Add("constraint_schema", typeof(string), string.Format("'{0}'", DefaultSchema)).SetOrdinal(1);
dataTable.Columns.Add("constraint_name", typeof(string), "o.schemaname").SetOrdinal(2);
dataTable.Columns.Add("table_catalog", typeof(string), string.Format("'{0}'", crmDbConnection.ConnectionInfo.OrganisationName)).SetOrdinal(3);
dataTable.Columns.Add("table_schema", typeof(string), string.Format("'{0}'", DefaultSchema)).SetOrdinal(4);
dataTable.Columns["o.ReferencingEntity"].ColumnName = "table_name";
dataTable.Columns["table_name"].SetOrdinal(5);
dataTable.Columns.Add("column_name", typeof(string), "o.ReferencingAttribute").SetOrdinal(6);
dataTable.Columns.Add("ordinal_position", typeof(int)).SetOrdinal(7); // TODO: FIX THIS ORDINAL!
// dataTable.Columns["a.columnnumber"].ColumnName = "ordinal_position";
// dataTable.Columns["ordinal_position"].SetOrdinal(7);
dataTable.Columns.Add("constraint_type", typeof(string), "'FOREIGN KEY'").SetOrdinal(8);
dataTable.Columns.Add("index_name", typeof(string), "constraint_name").SetOrdinal(9);
//foreach (DataRow item in dataTable.Rows)
//{
// var columnName = (string)item["column_name"];
// var columnAttInfos = item.GetChildRows(columnInfoRelation);
// if (columnAttInfos == null || !columnAttInfos.Any())
// {
// throw new InvalidOperationException("Could not find column attribute information for a one to many relationsnip column: " + columnName);
// }
// var singleColumnInfo = columnAttInfos.Single();
// item["ordinal_position"] = (int)singleColumnInfo["a.columnnumber"];
// // var columnResult = columnsDatatable.Rows.Find(myUserID);
//}
// dataTable.Columns.Add("constraint_name", typeof(string), "'FK__' + IsNull(table_name, '') + '_' + IsNull(column_name, PrimaryIdAttribute)").SetOrdinal(2);
// dataTable.Columns["column_name"].SetOrdinal(6);
// dataTable.Columns.Add("KeyType", typeof(Byte), "36").SetOrdinal(8); // 36 = uniqueidentitifer datatype - all pk indexes in crm are uniqueidentifiers.
// dataTable.Columns.Add("type_desc", typeof(string), "'CLUSTERED'");
//var filteredView = dataTable.AsDataView();
//filteredView.RowFilter = "column_name = PrimaryIdAttribute"; // necessary due to #68
//dataTable = filteredView.ToTable(true);
//if (hasConstraintNameFilter)
//{
// var filteredView = dataTable.AsDataView();
// filteredView.RowFilter = "constraint_name = '" + constraintname + "'";
// dataTable = filteredView.ToTable(true);
//}
return dataTable;
}
public DataTable GetUsers(CrmDbConnection crmDbConnection, string[] restrictions)
{
var command = new CrmDbCommand(crmDbConnection);
command.CommandText = "SELECT su.systemuserid, su.fullname, su.domainname, su.createdon, su.modifiedon FROM systemuser su";
if (restrictions != null && restrictions.Any())
{
string userName = restrictions[0];
command.CommandText = string.Format("{0} WHERE su.fullname = {1}", command.CommandText, userName);
}
var adapter = new CrmDataAdapter(command);
var dataTable = new DataTable();
dataTable.Locale = CultureInfo.InvariantCulture;
adapter.Fill(dataTable);
return dataTable;
}
public DataTable GetUniqueKeys(CrmDbConnection crmDbConnection, string[] restrictions)
{
// throw new NotImplementedException();
string entityName = GetRestrictionOrNull(0, restrictions);
bool hasEntityFilter = !string.IsNullOrEmpty(entityName);
string constraintName = GetRestrictionOrNull(1, restrictions);
bool hasConstraintNameFilter = !string.IsNullOrEmpty(constraintName);
string commandText = "SELECT * FROM entitymetadata";
if (hasEntityFilter || hasConstraintNameFilter)
{
commandText += " WHERE";
if (hasEntityFilter)
{
commandText += " (LogicalName = '" + entityName + "')";
}
//if (hasEntityFilter && hasConstraintNameFilter)
//{
// commandText += " AND ";
//}
//if (hasConstraintNameFilter)
//{
// commandText += " (SchemaName = '" + constraintName + "')";
//}
}
var command = new CrmDbCommand(crmDbConnection);
command.CommandText = commandText;
var adapter = new CrmDataAdapter(command);
var dataTable = new DataTable();
dataTable.Locale = CultureInfo.InvariantCulture;
adapter.Fill(dataTable);
// dataTable.AsDataView().RowFilter = "ReferencingEntity = '" + entityName +
dataTable.Columns.Add("CONSTRAINT_CATALOG", typeof(string), string.Format("'{0}'", crmDbConnection.ConnectionInfo.OrganisationName)).SetOrdinal(0);
dataTable.Columns.Add("CONSTRAINT_SCHEMA", typeof(string), string.Format("'{0}'", DefaultSchema)).SetOrdinal(1);
dataTable.Columns.Add("CONSTRAINT_NAME", typeof(string), "'PK__' + LogicalName + '_' + IsNull(PrimaryIdAttribute, LogicalName + 'id')").SetOrdinal(2);
// dataTable.Columns["SchemaName"].ColumnName = "CONSTRAINT_NAME";
// dataTable.Columns["CONSTRAINT_NAME"].SetOrdinal(2);
// dataTable.Columns.Add("CONSTRAINT_NAME", typeof(string), "''");
dataTable.Columns.Add("TABLE_CATALOG", typeof(string), string.Format("'{0}'", crmDbConnection.ConnectionInfo.OrganisationName)).SetOrdinal(3);
dataTable.Columns.Add("TABLE_SCHEMA", typeof(string), string.Format("'{0}'", DefaultSchema)).SetOrdinal(4);
dataTable.Columns.Add("TABLE_NAME", typeof(string), "LogicalName").SetOrdinal(5);
// dataTable.Columns["LogicalName"].ColumnName = "TABLE_NAME";
// dataTable.Columns["TABLE_NAME"].SetOrdinal(5);
dataTable.Columns.Add("CONSTRAINT_TYPE", typeof(string), "'PRIMARY KEY'").SetOrdinal(6);
dataTable.Columns.Add("IS_DEFERRABLE", typeof(string), "'NO'").SetOrdinal(7);
dataTable.Columns.Add("INITIALLY_DEFERRED", typeof(string), "'NO'").SetOrdinal(8);
if (hasConstraintNameFilter)
{
var filteredView = dataTable.AsDataView();
filteredView.RowFilter = "CONSTRAINT_NAME = '" + constraintName + "'";
dataTable = filteredView.ToTable(true);
}
return dataTable;
//else
//{
// dataTable = filteredView.ToTable(true);
//}
}
public DataTable GetTables(CrmDbConnection crmDbConnection, string[] restrictions)
{
// table_catalog
//table_schema
//table_name
//table_type
//Type of table. Can be VIEW or BASE TABLE.
var command = new CrmDbCommand(crmDbConnection);
string commandText = "SELECT * FROM EntityMetadata";
string catalog = GetRestrictionOrNull(0, restrictions);
string schema = GetRestrictionOrNull(1, restrictions);
string tableName = GetRestrictionOrNull(2, restrictions);
string tableType = GetRestrictionOrNull(3, restrictions); // doesn't matter currently what tabletype restriction is specified, we only return "base tables" not views.
if (catalog != null && catalog.ToLowerInvariant() != crmDbConnection.ConnectionInfo.OrganisationName.ToLowerInvariant())
{
// we only support the catalog currently connected to, can't query accross other catalogs.
throw new ArgumentException("invalid catalog restriction. no such catalog.");
}
if (schema != null && schema.ToLowerInvariant() != DefaultSchema.ToLowerInvariant())
{
// we only support a single schema "dbo".
throw new ArgumentException("invalid schema restriction. no such schema.");
}
if (!string.IsNullOrEmpty(tableName))
{
commandText = commandText + " WHERE LogicalName = '" + tableName + "'";
}
command.CommandText = commandText;
var adapter = new CrmDataAdapter(command);
var dataTable = new DataTable();
dataTable.Locale = CultureInfo.InvariantCulture;
adapter.Fill(dataTable);
dataTable.Columns.Add("TABLE_CATALOG", typeof(string), string.Format("'{0}'", crmDbConnection.ConnectionInfo.OrganisationName)).SetOrdinal(0);
dataTable.Columns.Add("TABLE_SCHEMA", typeof(string), string.Format("'{0}'", DefaultSchema)).SetOrdinal(1);
dataTable.Columns["logicalname"].ColumnName = "TABLE_NAME";
dataTable.Columns["TABLE_NAME"].SetOrdinal(2);
dataTable.Columns.Add("TABLE_TYPE", typeof(string), "'BASE TABLE'").SetOrdinal(3);
return dataTable;
}
private RetrieveMultipleRequest GetRetrieveMultipleRequest(CrmDbCommand command, CommandBehavior behavior)
{
var entityName = command.CommandText;
if (entityName.Contains(" "))
{
throw new ArgumentException("When CommandType is TableDirect, CommandText should be the name of an entity.");
}
var request = new RetrieveMultipleRequest()
{
Query = new QueryExpression(entityName) { ColumnSet = new ColumnSet(true), PageInfo = new PagingInfo() { ReturnTotalRecordCount = true } }
};
return request;
}
public void Should_Be_Able_To_Query_Using_Filter_Groups(String joinType, string whereClause, int expectedResults)
{
// var join = JoinOperator.Natural;
//switch (joinType)
//{
// case "INNER":
// join = JoinOperator.Inner;
// // Enum.Parse(typeof(JoinOperator), joinType)
// break;
// case "LEFT":
// join = JoinOperator.LeftOuter;
// break;
//}
var sql = string.Format("Select C.contactid, C.firstname, C.lastname, A.line1, A.customeraddressid From contact C {0} JOIN customeraddress A on C.contactid = A.parentid WHERE {1}", joinType, whereClause);
var cmd = new CrmDbCommand(null);
cmd.CommandText = sql;
var connectionString = ConfigurationManager.ConnectionStrings["CrmOrganisation"];
using (var conn = new CrmDbConnection(connectionString.ConnectionString))
{
conn.Open();
var command = conn.CreateCommand();
Console.WriteLine("Executing command " + sql);
command.CommandText = sql;
// command.CommandType = CommandType.Text;
using (var reader = command.ExecuteReader())
{
int resultCount = 0;
foreach (var result in reader)
{
resultCount++;
var contactId = (Guid)reader["C.contactid"];
var firstName = (string)reader.SafeGetString(1);
var lastName = (string)reader.SafeGetString(2);
var line1 = (string)reader.SafeGetString(3);
var alsoLine1 = (string)reader.SafeGetString("A.line1");
var customerAddressId = (Guid)reader["A.customeraddressid"];
Console.WriteLine(string.Format("{0} {1} {2} {3} {4}", contactId, firstName, lastName, line1, customerAddressId));
}
Console.WriteLine("There were " + resultCount + " results..");
Assert.That(resultCount, Is.EqualTo(expectedResults));
}
}
}
public void Should_Be_Able_To_Select_From_Table_Prefixed_With_Dbo_Schema(String schemaPrefix)
{
var sql = string.Format("Select C.contactid, C.firstname, C.lastname From {0}.contact C", schemaPrefix);
var cmd = new CrmDbCommand(null);
cmd.CommandText = sql;
var connectionString = ConfigurationManager.ConnectionStrings["CrmOrganisation"];
using (var conn = new CrmDbConnection(connectionString.ConnectionString))
{
conn.Open();
var command = conn.CreateCommand();
Console.WriteLine("Executing command " + sql);
command.CommandText = sql;
// command.CommandType = CommandType.Text;
using (var reader = command.ExecuteReader())
{
int resultCount = 0;
foreach (var result in reader)
{
resultCount++;
var contactId = (Guid)reader["C.contactid"];
var firstName = (string)reader.SafeGetString(1);
var lastName = (string)reader.SafeGetString(2);
Console.WriteLine(string.Format("{0} {1} {2}", contactId, firstName, lastName));
}
Console.WriteLine("There were " + resultCount + " results..");
}
}
}
public DataTable GetIndexes(CrmDbConnection crmDbConnection, string[] restrictions)
{
string catalog = GetRestrictionOrNull(0, restrictions);
string schema = GetRestrictionOrNull(1, restrictions);
string table = GetRestrictionOrNull(2, restrictions);
string constraintName = GetRestrictionOrNull(3, restrictions);
bool hasEntityFilter = !string.IsNullOrEmpty(table);
bool hasConstraintNameFilter = !string.IsNullOrEmpty(constraintName);
string commandText = "SELECT LogicalName, PrimaryIdAttribute FROM entitymetadata ";
if (hasEntityFilter || hasConstraintNameFilter)
{
commandText += "WHERE ";
if (hasEntityFilter)
{
commandText += " (LogicalName = '" + table + "')";
}
//if (hasEntityFilter && hasConstraintNameFilter)
//{
// commandText += " AND ";
//}
//if (hasConstraintNameFilter)
//{
// commandText += " (PrimaryIdAttribute = '" + constraintName + "')";
//}
}
var command = new CrmDbCommand(crmDbConnection);
command.CommandText = commandText;
var adapter = new CrmDataAdapter(command);
var dataTable = new DataTable();
dataTable.Locale = CultureInfo.InvariantCulture;
adapter.Fill(dataTable);
// <Indexes>
// <constraint_catalog>PortalDarrellDev</constraint_catalog>
// <constraint_schema>dbo</constraint_schema>
// <constraint_name>PK__tmp_ms_x__3214EC0737311087</constraint_name>
// <table_catalog>PortalDarrellDev</table_catalog>
// <table_schema>dbo</table_schema>
// <table_name>Table</table_name>
// <index_name>PK__tmp_ms_x__3214EC0737311087</index_name>
// <type_desc>CLUSTERED</type_desc>
//</Indexes>
dataTable.Columns.Add("constraint_catalog", typeof(string), string.Format("'{0}'", crmDbConnection.ConnectionInfo.OrganisationName)).SetOrdinal(0);
dataTable.Columns.Add("constraint_schema", typeof(string), string.Format("'{0}'", DefaultSchema)).SetOrdinal(1);
dataTable.Columns.Add("constraint_name", typeof(string), "'PK__' + [LogicalName] + '_' + [PrimaryIdAttribute]").SetOrdinal(2);
dataTable.Columns.Add("table_catalog", typeof(string), string.Format("'{0}'", crmDbConnection.ConnectionInfo.OrganisationName)).SetOrdinal(3);
dataTable.Columns.Add("table_schema", typeof(string), string.Format("'{0}'", DefaultSchema)).SetOrdinal(4);
dataTable.Columns.Add("table_name", typeof(string), "[LogicalName]").SetOrdinal(5);
//dataTable.Columns["LogicalName"].ColumnName = "table_name";
// dataTable.Columns["table_name"].SetOrdinal(5);
dataTable.Columns.Add("index_name", typeof(string), "constraint_name").SetOrdinal(6);
dataTable.Columns.Add("type_desc", typeof(string), "'CLUSTERED'").SetOrdinal(7);
if (hasConstraintNameFilter)
{
var filteredView = dataTable.AsDataView();
filteredView.RowFilter = "constraint_name = '" + constraintName + "'";
dataTable = filteredView.ToTable(true);
}
return dataTable;
}
private ICrmOperation GetOperationFromTextCommand(CrmDbCommand command, CommandBehavior behavior)
{
// We actually need to parse the SQL, and then build the appropriate organisation request.
var commandText = command.CommandText;
// Use SQLGeneration to parse the SQL command into a Visitable Builder.
var commandBuilder = new CommandBuilder();
var options = new CommandBuilderOptions();
options.PlaceholderPrefix = ParameterToken;
var sqlCommandBuilder = commandBuilder.GetCommand(commandText, options);
// Visit the builder with out custom visiter that will build the appropriate org request whilst visiting.
var visitor = GetVisitor(command);
if (visitor == null)
{
throw new InvalidOperationException("visitor was null");
}
sqlCommandBuilder.Accept(visitor);
// The visitor should now have vuild the OrgCommand that we need.
var orgCommand = visitor.CrmOperation;
if (orgCommand == null || orgCommand.Request == null)
{
throw new NotSupportedException("Could not translate the command into the appropriate Organization Service Request Message");
}
// Before returning the command, ensure some additional properties are set.
orgCommand.DbCommand = command;
orgCommand.CommandBehavior = behavior;
return orgCommand;
}
public DataTable GetColumns(CrmDbConnection crmDbConnection, string[] restrictions)
{
//TABLE_CATALOG
//TABLE_SCHEMA
//TABLE_NAME
//COLUMN_NAME
//ORDINAL_POSITION
//COLUMN_DEFAULT
//IS_NULLABLE
//DATA_TYPE
//CHARACTER_MAXIMUM_LENGTH
//CHARACTER_OCTET_LENGTH
//NUMERIC_PRECISION
//NUMERIC_PRECISION_RADIX
//NUMERIC_SCALE
//DATETIME_PRECISION
//CHARACTER_SET_CATALOG
//CHARACTER_SET_SCHEMA
//CHARACTER_SET_NAME
//COLLATION_CATALOG
//IS_SPARSE
//IS_COLUMN_SET
//IS_FILESTREAM
string catalog = GetRestrictionOrNull(0, restrictions);
string schema = GetRestrictionOrNull(1, restrictions);
string entityName = GetRestrictionOrNull(2, restrictions);
string attributeName = GetRestrictionOrNull(3, restrictions);
bool hasEntityFilter = false;
bool hasAttributeFilter = false;
if (catalog != null && catalog.ToLowerInvariant() != crmDbConnection.ConnectionInfo.OrganisationName.ToLowerInvariant())
{
// we only support the catalog currently connected to, can't query accross other catalogs.
throw new ArgumentException("invalid catalog restriction. no such catalog.");
}
if (schema != null && schema.ToLowerInvariant() != DefaultSchema.ToLowerInvariant())
{
// we only support a single schema "dbo".
throw new ArgumentException("invalid schema restriction. no such schema.");
}
hasEntityFilter = !string.IsNullOrEmpty(entityName);
hasAttributeFilter = !string.IsNullOrEmpty(attributeName);
var commandText = "SELECT entitymetadata.PrimaryIdAttribute, attributemetadata.* FROM entitymetadata INNER JOIN attributemetadata ON entitymetadata.MetadataId = attributemetadata.MetadataId ";
if (hasEntityFilter || hasAttributeFilter)
{
commandText += "WHERE ";
if (hasEntityFilter)
{
commandText += " (entitymetadata.LogicalName = '" + entityName + "')";
}
if (hasEntityFilter && hasAttributeFilter)
{
commandText += " AND ";
}
if (hasAttributeFilter)
{
commandText += " (attributemetadata.LogicalName = '" + attributeName + "')";
}
}
var command = new CrmDbCommand(crmDbConnection);
command.CommandText = commandText;
var adapter = new CrmDataAdapter(command);
var dataTable = new DataTable();
dataTable.Locale = CultureInfo.InvariantCulture;
adapter.Fill(dataTable);
dataTable.Columns.Add("TABLE_CATALOG", typeof(string), string.Format("'{0}'", crmDbConnection.ConnectionInfo.OrganisationName)).SetOrdinal(0);
dataTable.Columns.Add("TABLE_SCHEMA", typeof(string), string.Format("'{0}'", DefaultSchema)).SetOrdinal(1);
dataTable.Columns["entitylogicalname"].ColumnName = "TABLE_NAME";
dataTable.Columns["TABLE_NAME"].SetOrdinal(2);
dataTable.Columns["logicalname"].ColumnName = "COLUMN_NAME";
dataTable.Columns["COLUMN_NAME"].SetOrdinal(3);
dataTable.Columns["columnnumber"].ColumnName = "ORDINAL_POSITION";
dataTable.Columns["ORDINAL_POSITION"].SetOrdinal(4);
// dataTable.Columns["defaultvalue"].ColumnName = "COLUMN_DEFAULT";
dataTable.Columns.Add("COLUMN_DEFAULT", dataTable.Columns["defaultvalue"].DataType, "IIF([defaultvalue] = '-1', '', IIF([defaultvalue] = 'TRUE', '((' + 1 + '))', IIF([defaultvalue] = 'FALSE', '((0))', '((' + [defaultvalue] + '))')))").SetOrdinal(5);
// dataTable.Columns["COLUMN_DEFAULT"].SetOrdinal(5);
// dataTable.Columns["isnullable"].ColumnName = "IS_NULLABLE";
dataTable.Columns.Add("IS_NULLABLE", typeof(string), "IIF([isnullable] = True, 'YES', 'NO')").SetOrdinal(6);
//dataTable.Columns["IS_NULLABLE"].SetOrdinal(6);
dataTable.Columns["datatype"].ColumnName = "DATA_TYPE";
dataTable.Columns["DATA_TYPE"].SetOrdinal(7);
// dataTable.Columns["attributemetadata.length"].ColumnName = "character_maximum_length";
dataTable.Columns.Add("CHARACTER_MAXIMUM_LENGTH", typeof(int), "IIF(data_type = 'nvarchar', [maxlength], NULL)").SetOrdinal(8);
dataTable.Columns.Add("CHARACTER_OCTET_LENGTH", typeof(int), "IIF(data_type ='nvarchar',ISNULL(character_maximum_length, 0) * 2, character_maximum_length)").SetOrdinal(9);
dataTable.Columns["numericprecision"].ColumnName = "NUMERIC_PRECISION";
dataTable.Columns["NUMERIC_PRECISION"].SetOrdinal(10);
dataTable.Columns["numericprecisionradix"].ColumnName = "NUMERIC_PRECISION_RADIX";
dataTable.Columns["NUMERIC_PRECISION_RADIX"].SetOrdinal(11);
dataTable.Columns["numericscale"].ColumnName = "NUMERIC_SCALE";
dataTable.Columns["NUMERIC_SCALE"].SetOrdinal(12);
dataTable.Columns.Add("DATETIME_PRECISION", typeof(int), "IIF(data_type = 'datetime', 3, NULL)").SetOrdinal(13);
dataTable.Columns.Add("CHARACTER_SET_CATALOG", typeof(string), "NULL").SetOrdinal(14);
dataTable.Columns.Add("CHARACTER_SET_SCHEMA", typeof(string), "NULL").SetOrdinal(15);
dataTable.Columns.Add("CHARACTER_SET_NAME", typeof(string), "IIF(data_type ='nvarchar', 'UNICODE', NULL)").SetOrdinal(16);
dataTable.Columns.Add("COLLATION_CATALOG", typeof(string), "NULL").SetOrdinal(17);
dataTable.Columns.Add("IS_SPARSE", typeof(bool), "false").SetOrdinal(18);
dataTable.Columns.Add("IS_COLUMN_SET", typeof(bool), "false").SetOrdinal(19);
dataTable.Columns.Add("IS_FILESTREAM", typeof(bool), "false").SetOrdinal(20);
return dataTable;
}
protected void AssignResponseParameter(CrmDbCommand command, OrganizationResponse response)
{
if (command != null && command.Parameters.Contains(SystemCommandParameters.OrgResponse) && command.Parameters[SystemCommandParameters.OrgResponse].Direction == ParameterDirection.Output)
{
command.Parameters[SystemCommandParameters.OrgResponse].Value = response;
}
}
public DataTable GetUniqueKeyColumns(CrmDbConnection crmDbConnection, string[] restrictions)
{
string catalog = GetRestrictionOrNull(0, restrictions);
string schema = GetRestrictionOrNull(1, restrictions);
string table = GetRestrictionOrNull(2, restrictions);
string constraintname = GetRestrictionOrNull(3, restrictions);
string columnname = GetRestrictionOrNull(4, restrictions);
bool hasEntityFilter = !string.IsNullOrEmpty(table);
bool hasColumnFilter = !string.IsNullOrEmpty(columnname);
bool hasConstraintNameFilter = !string.IsNullOrEmpty(constraintname);
string commandText = "SELECT entitymetadata.PrimaryIdAttribute, entitymetadata.LogicalName, a.LogicalName, a.ColumnNumber FROM entitymetadata JOIN attributemetadata a on entitymetadata.MetadataId = a.MetadataId WHERE (a.isprimaryid = @isPrimaryId)";
if (hasEntityFilter || hasColumnFilter)
{
commandText += "AND ";
if (hasEntityFilter)
{
commandText += " (entitymetadata.LogicalName = '" + table + "')";
}
if (hasEntityFilter && hasColumnFilter)
{
commandText += " AND ";
}
if (hasColumnFilter)
{
commandText += " (a.LogicalName = '" + columnname + "')";
}
}
var command = new CrmDbCommand(crmDbConnection);
var param = command.CreateParameter();
param.DbType = DbType.Boolean;
param.Direction = ParameterDirection.Input;
param.ParameterName = "@isPrimaryId";
param.Value = true;
command.Parameters.Add(param);
command.CommandText = commandText;
var adapter = new CrmDataAdapter(command);
var dataTable = new DataTable();
dataTable.Locale = CultureInfo.InvariantCulture;
adapter.Fill(dataTable);
dataTable.Columns.Add("constraint_catalog", typeof(string), string.Format("'{0}'", crmDbConnection.ConnectionInfo.OrganisationName)).SetOrdinal(0);
dataTable.Columns.Add("constraint_schema", typeof(string), string.Format("'{0}'", DefaultSchema)).SetOrdinal(1);
dataTable.Columns["LogicalName"].ColumnName = "table_name";
dataTable.Columns.Add("column_name", typeof(string), "IsNull([a.LogicalName], [PrimaryIdAttribute])");
dataTable.Columns.Add("constraint_name", typeof(string), "'PK__' + IsNull(table_name, '') + '_' + IsNull(column_name, PrimaryIdAttribute)").SetOrdinal(2);
dataTable.Columns.Add("table_catalog", typeof(string), string.Format("'{0}'", crmDbConnection.ConnectionInfo.OrganisationName)).SetOrdinal(3);
dataTable.Columns.Add("table_schema", typeof(string), string.Format("'{0}'", DefaultSchema)).SetOrdinal(4);
dataTable.Columns["table_name"].SetOrdinal(5);
dataTable.Columns["column_name"].SetOrdinal(6);
dataTable.Columns["a.columnnumber"].ColumnName = "ordinal_position";
dataTable.Columns["ordinal_position"].SetOrdinal(7);
dataTable.Columns.Add("constraint_type", typeof(string), "'PRIMARY KEY'").SetOrdinal(8);
// dataTable.Columns.Add("KeyType", typeof(Byte), "36").SetOrdinal(8); // 36 = uniqueidentitifer datatype - all pk indexes in crm are uniqueidentifiers.
dataTable.Columns.Add("index_name", typeof(string), "constraint_name").SetOrdinal(9);
// dataTable.Columns.Add("type_desc", typeof(string), "'CLUSTERED'");
var filteredView = dataTable.AsDataView();
filteredView.RowFilter = "column_name = PrimaryIdAttribute"; // necessary due to #68
dataTable = filteredView.ToTable(true);
if (hasConstraintNameFilter)
{
filteredView = dataTable.AsDataView();
filteredView.RowFilter = "constraint_name = '" + constraintname + "'";
dataTable = filteredView.ToTable(true);
}
return dataTable;
}
private CreateEntityRequest GetCreateEntityRequest(string sql)
{
// set up fake metadata provider.
// var fakeMetadataProvider = MockRepository.GenerateMock<ICrmMetaDataProvider>();
// var fakeMetadata = GetFakeContactMetadata();
// fakeMetadataProvider.Stub(a => a.GetEntityMetadata("contact")).Return(fakeMetadata);
var fakeConn = MockRepository.GenerateMock<CrmDbConnection>();
// fakeConn.Stub(a => a.MetadataProvider).Return(fakeMetadataProvider);
var cmd = new CrmDbCommand(fakeConn);
cmd.CommandText = sql;
var createRequest = GetOrganizationRequest<CreateEntityRequest>(cmd);
return createRequest;
}
