internal static bool ParseBooleanExtendedProperty(SchemaObjectBase schemaObjectBase, string extendedProperty) { if (schemaObjectBase == null) { return(false); } var properties = schemaObjectBase.GetLoadedExtendedProperties(); if (!properties.Contains(extendedProperty)) { return(false); } bool temp = false; bool.TryParse(properties[extendedProperty].Value.ToString(), out temp); return(temp); }
private IEnumerable <TableKeySchema> GetMyTableKeys(string connectionString, SchemaObjectBase table) { string commandText = string.Format("SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS t1 WHERE t1.TABLE_SCHEMA = '{0}' AND t1.TABLE_NAME = '{1}' AND CONSTRAINT_TYPE = 'FOREIGN KEY'", table.Database.Name, table.Name); string commandText2 = string.Format("SELECT t1.CONSTRAINT_NAME, t1.COLUMN_NAME, t1.POSITION_IN_UNIQUE_CONSTRAINT, t1.REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE t1 INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS t2 ON t2.TABLE_SCHEMA = t1.TABLE_SCHEMA AND t2.TABLE_NAME = t1.TABLE_NAME AND t2.CONSTRAINT_NAME = t1.CONSTRAINT_NAME WHERE t1.TABLE_SCHEMA = '{0}' AND t1.TABLE_NAME = '{1}' AND t2.CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY t1.CONSTRAINT_NAME, t1.POSITION_IN_UNIQUE_CONSTRAINT", table.Database.Name, table.Name); DataSet dataSet; using (DbConnection dbConnection = CreateConnection(connectionString)) { dbConnection.ConnectionString = connectionString; dbConnection.Open(); using (DbCommand dbCommand = dbConnection.CreateCommand()) { dbCommand.CommandText = commandText; dbCommand.Connection = dbConnection; dataSet = this.ConvertDataReaderToDataSet(dbCommand.ExecuteReader()); } if (dbConnection.State != ConnectionState.Closed) { dbConnection.Close(); } } using (DbConnection dbConnection2 = CreateConnection(connectionString)) { dbConnection2.ConnectionString = connectionString; dbConnection2.Open(); using (DbCommand dbCommand2 = dbConnection2.CreateCommand()) { dbCommand2.CommandText = commandText2; dbCommand2.Connection = dbConnection2; dataSet.Tables.Add(this.ConvertDataReaderToDataTable(dbCommand2.ExecuteReader())); } if (dbConnection2.State != ConnectionState.Closed) { dbConnection2.Close(); } } List <TableKeySchema> list = new List <TableKeySchema>(); if (dataSet.Tables.Count > 0 && dataSet.Tables[0].Rows.Count > 0) { dataSet.Relations.Add("Contraint_to_Keys", dataSet.Tables[0].Columns["CONSTRAINT_NAME"], dataSet.Tables[1].Columns["CONSTRAINT_NAME"]); foreach (DataRow dataRow in dataSet.Tables[0].Rows) { string text = dataRow["CONSTRAINT_NAME"].ToString(); List <DataRow> list2 = new List <DataRow>(dataRow.GetChildRows("Contraint_to_Keys")); List <string> list3 = new List <string>(list2.Count); List <string> list4 = new List <string>(list2.Count); string name = table.Name; string text2 = list2[0]["REFERENCED_TABLE_NAME"].ToString(); foreach (DataRow current in list2) { list4.Add(current["COLUMN_NAME"].ToString()); list3.Add(current["REFERENCED_COLUMN_NAME"].ToString()); } list.Add(new TableKeySchema(table.Database, text, list4.ToArray(), name, list3.ToArray(), text2)); } } if (list.Count > 0) { return(list); } return(new List <TableKeySchema>()); }
public void SetExtendedProperties(string connectionString, SchemaObjectBase schemaObject) { throw new NotImplementedException(); }
public ExtendedProperty[] GetExtendedProperties(string connectionString, SchemaObjectBase schemaObject) { List <ExtendedProperty> list = new List <ExtendedProperty>(); if (schemaObject is ColumnSchema) { ColumnSchema columnSchema = schemaObject as ColumnSchema; string commandText = string.Format( @"SELECT EXTRA, COLUMN_DEFAULT, COLUMN_TYPE,COLUMN_COMMENT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = '{0}' AND TABLE_NAME = '{1}' AND COLUMN_NAME = '{2}'", columnSchema.Table.Database.Name, columnSchema.Table.Name, columnSchema.Name); using (DbConnection dbConnection = MySQLSchemaProvider.CreateConnection(connectionString)) { dbConnection.Open(); DbCommand dbCommand = dbConnection.CreateCommand(); dbCommand.CommandText = commandText; dbCommand.Connection = dbConnection; using (IDataReader dataReader = dbCommand.ExecuteReader(CommandBehavior.CloseConnection)) { while (dataReader.Read()) { string text = dataReader.GetString(0).ToLower(); bool flag = dataReader.IsDBNull(1); string text2 = ""; if (!flag) { text2 = dataReader.GetString(1).ToUpper(); } string text3 = dataReader.GetString(2).ToUpper(); string textCOMMENT = dataReader.GetString(3).ToUpper(); bool flag2 = text.IndexOf("auto_increment") > -1; list.Add(new ExtendedProperty("CS_IsIdentity", flag2, columnSchema.DataType)); if (flag2) { list.Add(new ExtendedProperty("CS_IdentitySeed", 1, columnSchema.DataType)); list.Add(new ExtendedProperty("CS_IdentityIncrement", 1, columnSchema.DataType)); } list.Add(new ExtendedProperty("CS_ColumnDefaultIsNull", flag, DbType.Boolean)); list.Add(new ExtendedProperty("CS_Default", text2, DbType.String)); list.Add(new ExtendedProperty("CS_ColumnDefault", text2, DbType.String)); list.Add(new ExtendedProperty("CS_SystemType", text3, DbType.String)); list.Add(new ExtendedProperty("CS_ColumnType", text3, DbType.String)); list.Add(new ExtendedProperty("CS_Description", textCOMMENT, DbType.String)); list.Add(new ExtendedProperty("CS_ColumnExtra", text.ToUpper(), DbType.String)); } if (!dataReader.IsClosed) { dataReader.Close(); } } if (dbConnection.State != ConnectionState.Closed) { dbConnection.Close(); } } } if (schemaObject is TableSchema) { TableSchema tableSchema = schemaObject as TableSchema; string commandText2 = string.Format("SHOW CREATE TABLE `{0}`.`{1}`", tableSchema.Database.Name, tableSchema.Name); using (DbConnection dbConnection2 = MySQLSchemaProvider.CreateConnection(connectionString)) { dbConnection2.Open(); DbCommand dbCommand2 = dbConnection2.CreateCommand(); dbCommand2.CommandText = commandText2; dbCommand2.Connection = dbConnection2; using (IDataReader dataReader2 = dbCommand2.ExecuteReader(CommandBehavior.CloseConnection)) { while (dataReader2.Read()) { string @string = dataReader2.GetString(1); list.Add(new ExtendedProperty("CS_CreateTableScript", @string, DbType.String)); list.Add(new ExtendedProperty("CS_CreateTableScript", @string, DbType.String)); } if (!dataReader2.IsClosed) { dataReader2.Close(); } } if (dbConnection2.State != ConnectionState.Closed) { dbConnection2.Close(); } } string commandTextTABLES = string.Format( @"SELECT TABLE_COMMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '{0}' AND TABLE_NAME = '{1}'", tableSchema.Database.Name, tableSchema.Name); using (DbConnection dbConnection3 = MySQLSchemaProvider.CreateConnection(connectionString)) { dbConnection3.Open(); DbCommand dbCommand2 = dbConnection3.CreateCommand(); dbCommand2.CommandText = commandTextTABLES; dbCommand2.Connection = dbConnection3; using (IDataReader dataReader2 = dbCommand2.ExecuteReader(CommandBehavior.CloseConnection)) { while (dataReader2.Read()) { string textCOMMENT = dataReader2.GetString(0); list.Add(new ExtendedProperty("CS_Description", textCOMMENT, DbType.String)); } if (!dataReader2.IsClosed) { dataReader2.Close(); } } if (dbConnection3.State != ConnectionState.Closed) { dbConnection3.Close(); } } } return(list.ToArray()); }
public ExtendedProperty[] GetExtendedProperties(string connectionString, SchemaObjectBase schemaObject) { return(new ExtendedProperty[0]); }
/// <summary> /// DRS 2006-01-24 : GetTableKeys must return both the foreign keys contained within the /// given table AND the foreign keys that point at the given table from other tables. I've /// added this method to find those keys. I tried as best I could to stick to the same /// coding structure as was used in the original GetTableKeys (which can now be found in /// GetMyTableKeys). /// </summary> private IEnumerable <TableKeySchema> GetOthersTableKeys(string connectionString, SchemaObjectBase table) { string commandText = string.Format("SELECT DISTINCT CONSTRAINT_NAME" + " FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE t1" + " WHERE t1.TABLE_SCHEMA = '{0}' AND t1.REFERENCED_TABLE_NAME = '{1}'", table.Database.Name, table.Name); string commandText2 = string.Format("SELECT t1.CONSTRAINT_NAME, t1.TABLE_NAME, t1.COLUMN_NAME, t1.POSITION_IN_UNIQUE_CONSTRAINT," + " t1.REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME" + " FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE t1" + " INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS t2" + " ON t2.TABLE_SCHEMA = t1.TABLE_SCHEMA" + " AND t2.TABLE_NAME = t1.TABLE_NAME" + " AND t2.CONSTRAINT_NAME = t1.CONSTRAINT_NAME" + " WHERE t1.TABLE_SCHEMA = '{0}' AND t1.REFERENCED_TABLE_NAME = '{1}'" + " AND t2.CONSTRAINT_TYPE = 'FOREIGN KEY'" + " ORDER BY t1.CONSTRAINT_NAME, t1.POSITION_IN_UNIQUE_CONSTRAINT", table.Database.Name, table.Name); DataSet dataSet; DbProviderFactory factory = CreateDbProviderFactory(); using (DbConnection connection = factory.CreateConnection()) { connection.ConnectionString = connectionString; connection.Open(); using (DbCommand command = connection.CreateCommand()) { command.CommandText = commandText; command.Connection = connection; dataSet = ConvertDataReaderToDataSet(command.ExecuteReader()); } if (connection.State != ConnectionState.Closed) { connection.Close(); } } using (DbConnection connection = factory.CreateConnection()) { connection.ConnectionString = connectionString; connection.Open(); using (DbCommand command = connection.CreateCommand()) { command.CommandText = commandText2; command.Connection = connection; dataSet.Tables.Add(ConvertDataReaderToDataTable(command.ExecuteReader())); } if (connection.State != ConnectionState.Closed) { connection.Close(); } } List <TableKeySchema> tableKeySchema = new List <TableKeySchema>(); if (dataSet.Tables.Count > 0 && dataSet.Tables[0].Rows.Count > 0) { // Add constraint to keys relationship dataSet.Relations.Add("Contraint_to_Keys", dataSet.Tables[0].Columns["CONSTRAINT_NAME"], dataSet.Tables[1].Columns["CONSTRAINT_NAME"]); foreach (DataRow constraintRow in dataSet.Tables[0].Rows) { string name = constraintRow["CONSTRAINT_NAME"].ToString(); // Get the keys List <DataRow> keys = new List <DataRow>(constraintRow.GetChildRows("Contraint_to_Keys")); List <string> primaryKeys = new List <string>(keys.Count); List <string> foreignKeys = new List <string>(keys.Count); string fkTable = keys[0]["TABLE_NAME"].ToString(); string pkTable = keys[0]["REFERENCED_TABLE_NAME"].ToString(); foreach (DataRow key in keys) { foreignKeys.Add(key["COLUMN_NAME"].ToString()); primaryKeys.Add(key["REFERENCED_COLUMN_NAME"].ToString()); } tableKeySchema.Add(new TableKeySchema(table.Database, name, foreignKeys.ToArray(), fkTable, primaryKeys.ToArray(), pkTable)); } } if (tableKeySchema.Count > 0) { return(tableKeySchema); } return(new List <TableKeySchema>()); }
/// <summary> /// Sets the extended properties. /// </summary> /// <param name="connectionString">The connection string.</param> /// <param name="schemaObject">The schema object.</param> public void SetExtendedProperties(string connectionString, SchemaObjectBase schemaObject) { throw new Exception("The method or operation is not implemented."); }
public void SetExtendedProperties(string connectionString, SchemaObjectBase schemaObject) { throw new NotImplementedException("This method has not been implemented"); }
/// <summary> /// Gets the extended properties for a given schema object. /// </summary> /// <param name="connectionString">The connection string used to connect to the target database.</param> /// <param name="schemaObject"></param> /// <returns></returns> public ExtendedProperty[] GetExtendedProperties(string connectionString, SchemaObjectBase schemaObject) { List <ExtendedProperty> extendedProperties = new List <ExtendedProperty>(); if (schemaObject is ColumnSchema) { ColumnSchema columnSchema = schemaObject as ColumnSchema; string commandText = string.Format(@"SELECT EXTRA, COLUMN_DEFAULT, COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = '{0}' AND TABLE_NAME = '{1}' AND COLUMN_NAME = '{2}'", columnSchema.Table.Database.Name, columnSchema.Table.Name, columnSchema.Name); using (DbConnection connection = CreateConnection(connectionString)) { connection.Open(); DbCommand command = connection.CreateCommand(); command.CommandText = commandText; command.Connection = connection; using (IDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection)) { while (reader.Read()) { string extra = reader.GetString(0).ToLower(); bool columndefaultisnull = reader.IsDBNull(1); string columndefault = ""; if (!columndefaultisnull) { columndefault = reader.GetString(1).ToUpper(); } string columntype = reader.GetString(2).ToUpper(); bool isIdentity = (extra.IndexOf("auto_increment") > -1); extendedProperties.Add(new ExtendedProperty(ExtendedPropertyNames.IsIdentity, isIdentity, columnSchema.DataType)); if (isIdentity) { /* * MySQL auto_increment doesn't work exactly like SQL Server's IDENTITY * I believe that auto_increment is equivalent to IDENTITY(1, 1) * However, auto_increment behaves differently from IDENTITY when used * with multi-column primary keys. See the MySQL Reference Manual for details. */ extendedProperties.Add(new ExtendedProperty(ExtendedPropertyNames.IdentitySeed, 1, columnSchema.DataType)); extendedProperties.Add(new ExtendedProperty(ExtendedPropertyNames.IdentityIncrement, 1, columnSchema.DataType)); } extendedProperties.Add(new ExtendedProperty("CS_ColumnDefaultIsNull", columndefaultisnull, DbType.Boolean)); // Added for Backwards Compatibility. extendedProperties.Add(new ExtendedProperty(ExtendedPropertyNames.DefaultValue, columndefault, DbType.String)); extendedProperties.Add(new ExtendedProperty("CS_ColumnDefault", columndefault, DbType.String)); // Added for Backwards Compatibility. extendedProperties.Add(new ExtendedProperty(ExtendedPropertyNames.SystemType, columntype, DbType.String)); extendedProperties.Add(new ExtendedProperty("CS_ColumnType", columntype, DbType.String)); // Added for Backwards Compatibility. extendedProperties.Add(new ExtendedProperty("CS_ColumnExtra", extra.ToUpper(), DbType.String)); } if (!reader.IsClosed) { reader.Close(); } } if (connection.State != ConnectionState.Closed) { connection.Close(); } } } if (schemaObject is TableSchema) { TableSchema tableSchema = schemaObject as TableSchema; string commandText = string.Format(@"SHOW CREATE TABLE `{0}`.`{1}`", tableSchema.Database.Name, tableSchema.Name); using (DbConnection connection = CreateConnection(connectionString)) { connection.Open(); DbCommand command = connection.CreateCommand(); command.CommandText = commandText; command.Connection = connection; using (IDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection)) { while (reader.Read()) { string createtable = reader.GetString(1); extendedProperties.Add(new ExtendedProperty("CS_CreateTableScript", createtable, DbType.String)); } if (!reader.IsClosed) { reader.Close(); } } if (connection.State != ConnectionState.Closed) { connection.Close(); } } } return(extendedProperties.ToArray()); }
private IEnumerable <TableKeySchema> GetOthersTableKeys(string connectionString, SchemaObjectBase table) { DataSet set; string str = string.Format("SELECT DISTINCT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE t1 WHERE t1.TABLE_SCHEMA = '{0}' AND t1.REFERENCED_TABLE_NAME = '{1}'", table.Database.Name, table.Name); string str2 = string.Format("SELECT t1.CONSTRAINT_NAME, t1.TABLE_NAME, t1.COLUMN_NAME, t1.POSITION_IN_UNIQUE_CONSTRAINT, t1.REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE t1 INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS t2 ON t2.TABLE_SCHEMA = t1.TABLE_SCHEMA AND t2.TABLE_NAME = t1.TABLE_NAME AND t2.CONSTRAINT_NAME = t1.CONSTRAINT_NAME WHERE t1.TABLE_SCHEMA = '{0}' AND t1.REFERENCED_TABLE_NAME = '{1}' AND t2.CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY t1.CONSTRAINT_NAME, t1.POSITION_IN_UNIQUE_CONSTRAINT", table.Database.Name, table.Name); DbProviderFactory factory = CreateDbProviderFactory(); using (DbConnection connection = factory.CreateConnection()) { connection.ConnectionString = connectionString; connection.Open(); using (DbCommand command = connection.CreateCommand()) { command.CommandText = str; command.Connection = connection; set = this.ConvertDataReaderToDataSet(command.ExecuteReader()); } if (connection.State != ConnectionState.Closed) { connection.Close(); } } using (DbConnection connection2 = factory.CreateConnection()) { connection2.ConnectionString = connectionString; connection2.Open(); using (DbCommand command2 = connection2.CreateCommand()) { command2.CommandText = str2; command2.Connection = connection2; set.Tables.Add(this.ConvertDataReaderToDataTable(command2.ExecuteReader())); } if (connection2.State != ConnectionState.Closed) { connection2.Close(); } } List <TableKeySchema> list = new List <TableKeySchema>(); if ((set.Tables.Count > 0) && (set.Tables[0].Rows.Count > 0)) { set.Relations.Add("Contraint_to_Keys", set.Tables[0].Columns["CONSTRAINT_NAME"], set.Tables[1].Columns["CONSTRAINT_NAME"]); foreach (DataRow row in set.Tables[0].Rows) { string name = row["CONSTRAINT_NAME"].ToString(); List <DataRow> list2 = new List <DataRow>(row.GetChildRows("Contraint_to_Keys")); List <string> list3 = new List <string>(list2.Count); List <string> list4 = new List <string>(list2.Count); string foreignKeyTable = list2[0]["TABLE_NAME"].ToString(); string primaryKeyTable = list2[0]["REFERENCED_TABLE_NAME"].ToString(); foreach (DataRow row2 in list2) { list4.Add(row2["COLUMN_NAME"].ToString()); list3.Add(row2["REFERENCED_COLUMN_NAME"].ToString()); } list.Add(new TableKeySchema(table.Database, name, list4.ToArray(), foreignKeyTable, list3.ToArray(), primaryKeyTable)); } } if (list.Count > 0) { return(list); } return(new List <TableKeySchema>()); }
public ExtendedProperty[] GetExtendedProperties(string connectionString, SchemaObjectBase schemaObject) { //No extended properties in SQLite return(new ExtendedProperty[0]); }
public ExtendedProperty[] GetExtendedProperties(string connectionString, SchemaObjectBase schemaObject) { if (schemaObject is TableSchema) { TableSchema tableSchema = (TableSchema)schemaObject; string text = $"select cast(obj_description(relfilenode,'pg_class') as varchar) as comment from pg_class where relname='{tableSchema.Name}';"; List <ExtendedProperty> list = new List <ExtendedProperty>(); using (NpgsqlConnection npgsqlConnection = new NpgsqlConnection(connectionString)) { npgsqlConnection.Open(); using (NpgsqlCommand npgsqlCommand = new NpgsqlCommand(text, npgsqlConnection)) { var comment = npgsqlCommand.ExecuteScalar(); list.Add(ExtendedProperty.Readonly("CS_Description", comment?.ToString())); } if (npgsqlConnection.State != ConnectionState.Closed) { npgsqlConnection.Close(); } } return(list.ToArray()); } if (schemaObject is ColumnSchema) { List <ExtendedProperty> list = new List <ExtendedProperty>(); ColumnSchema columnSchema = schemaObject as ColumnSchema; string text = $"select pg_get_serial_sequence(a.table_name, a.column_name) as EXTRA,a.COLUMN_DEFAULT,a.data_type,c.DeText as description from information_schema.columns as a left join( select pg_attr.attname as colname,pg_constraint.conname as pk_name from pg_constraint inner join pg_class on pg_constraint.conrelid = pg_class.oid inner join pg_attribute pg_attr on pg_attr.attrelid = pg_class.oid and pg_attr.attnum = pg_constraint.conkey[1] inner join pg_type on pg_type.oid = pg_attr.atttypid where pg_class.relname = '{columnSchema.Table.Name}' and pg_constraint.contype = 'p')as b on b.colname = a.column_name left join( select attname, description as DeText from pg_class left join pg_attribute pg_attr on pg_attr.attrelid = pg_class.oid left join pg_description pg_desc on pg_desc.objoid = pg_attr.attrelid and pg_desc.objsubid = pg_attr.attnum where pg_attr.attnum > 0 and pg_attr.attrelid = pg_class.oid and pg_class.relname = '{columnSchema.Table.Name}')as c on c.attname = a.column_name where table_schema = 'public' and table_name = '{columnSchema.Table.Name}' and COLUMN_NAME = '{columnSchema.Name}' order by ordinal_position; "; using (NpgsqlConnection npgsqlConnection = new NpgsqlConnection(connectionString)) { npgsqlConnection.Open(); using (NpgsqlCommand npgsqlCommand = new NpgsqlCommand(text, npgsqlConnection)) { using (IDataReader dataReader = npgsqlCommand.ExecuteReader(CommandBehavior.CloseConnection)) { while (dataReader.Read()) { string text2 = dataReader.IsDBNull(0) ? string.Empty : dataReader.GetString(0).ToLower(); string value = dataReader.IsDBNull(1) ? null : dataReader.GetString(1).ToUpper(); string value2 = dataReader.GetString(2).ToUpper(); string description = dataReader.GetString(3); bool flag = !string.IsNullOrEmpty(text2); list.Add(new ExtendedProperty("CS_IsIdentity", flag, columnSchema.DataType)); if (flag) { list.Add(new ExtendedProperty("CS_IdentitySeed", 1, columnSchema.DataType)); list.Add(new ExtendedProperty("CS_IdentityIncrement", 1, columnSchema.DataType)); } list.Add(new ExtendedProperty("CS_Default", value, DbType.String)); list.Add(new ExtendedProperty("CS_SystemType", value2, DbType.String)); list.Add(new ExtendedProperty("CS_Sequence", text2.ToUpper(), DbType.String)); list.Add(ExtendedProperty.Readonly("CS_Description", description?.ToString())); } if (!dataReader.IsClosed) { dataReader.Close(); } } } if (npgsqlConnection.State != ConnectionState.Closed) { npgsqlConnection.Close(); } } return(list.ToArray()); } return(new ExtendedProperty[0]); }