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