public void Should_Not_Support_Generation_Of_Update_Command_Using_Conflict_Option_Compare_All_Searchable_Values()
{
// Arrange
using (var sandbox = ConnectionTestsSandbox.Create())
{
using (var dbConnection = sandbox.Container.Resolve <CrmDbConnection>())
{
var selectCommand = new CrmDbCommand(dbConnection, "SELECT contactid, firstname, lastname FROM contact");
using (var adapter = new CrmDataAdapter(selectCommand))
{
adapter.SelectCommand = selectCommand;
using (var sut = new CrmCommandBuilder(adapter))
{
sut.ConflictOption = ConflictOption.CompareAllSearchableValues;
// verify that the crmcommand builder generates appropriate insert / udapte and delete commands.
// Act
var updateCommand = sut.GetUpdateCommand();
// Assert
Assert.That(updateCommand, Is.Not.Null);
Assert.That(updateCommand.CommandText, Is.Not.Null);
Assert.That(updateCommand.CommandText, Is.Not.EqualTo(""));
Console.WriteLine(updateCommand.CommandText);
}
}
}
}
}
public void Should_Generate_Insert_Command()
{
// Arrange
using (var sandbox = ConnectionTestsSandbox.Create())
{
using (var dbConnection = sandbox.Container.Resolve <CrmDbConnection>())
{
var selectCommand = new CrmDbCommand(dbConnection, "SELECT contactid, firstname, lastname FROM contact");
using (var adapter = new CrmDataAdapter(selectCommand))
{
adapter.SelectCommand = selectCommand;
using (var sut = new CrmCommandBuilder(adapter))
{
// verify that the crmcommand builder generates appropriate insert / udapte and delete commands.
// Act
var insertCommand = sut.GetInsertCommand();
// Assert
Assert.That(insertCommand, Is.Not.Null);
Assert.That(insertCommand.CommandText, Is.Not.Null);
Assert.That(insertCommand.CommandText, Is.Not.EqualTo(""));
Console.WriteLine(insertCommand.CommandText);
}
}
}
}
}
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;
}
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;
}
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;
}
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;
}
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);
//}
}
