/// <summary> /// Comprueba si una columna es una clave foránea /// </summary> private bool CheckIsForeignKey(TableDbModel table, FieldDbModel column) { // Recorre las restricciones comprobando si es una clave foránea foreach (ConstraintDbModel constraint in table.Constraints) { if (constraint.Type == ConstraintDbModel.ConstraintType.ForeignKey && column.Name.EqualsIgnoreCase(constraint.Column)) { return(true); } } // Si ha llegado hasta aquí es porque no es una clave foránea return(false); }
/// <summary> /// Crea una sentencia de importación de una tabla con los datos de un archivo /// </summary> private SentenceImportCsv CreateSentence(SentenceImportCsvSchema sentence, TableDbModel table) { SentenceImportCsv importSentence = new SentenceImportCsv(); // Asigna las propiedades importSentence.Target = sentence.Target; importSentence.FileName = table.Name + ".csv"; importSentence.Table = table.Name; importSentence.BatchSize = sentence.BatchSize; importSentence.Timeout = sentence.Timeout; // Asigna los parámetros de archivo importSentence.Definition = sentence.Definition; // Devuelve la sentencia de importación return(importSentence); }
/// <summary> /// Crea una sentencia de exportación a CSV con los datos de la tabla /// </summary> private SentenceExportCsv CreateSentence(SentenceExportCsvSchema sentence, TableDbModel table) { SentenceExportCsv exportSentence = new SentenceExportCsv(); // Asigna las propiedades exportSentence.Source = sentence.Source; exportSentence.FileName = table.Name + ".csv"; exportSentence.Command = GetSelect(table); exportSentence.BatchSize = sentence.BatchSize; exportSentence.Timeout = sentence.Timeout; // Asigna los parámetros de archivo exportSentence.Definition = sentence.Definition; // Devuelve la sentencia de exportación return(exportSentence); }
/// <summary> /// Carga las restricciones de una tabla /// </summary> private async Task LoadConstraintsAsync(SqlServerProvider connection, TableDbModel table, TimeSpan timeout, CancellationToken cancellationToken) { ParametersDbCollection parameters = new ParametersDbCollection(); string sql = @"SELECT TableConstraints.Table_Catalog, TableConstraints.Table_Schema, TableConstraints.Table_Name, ColumnConstraint.Column_Name, ColumnConstraint.Constraint_Name, TableConstraints.Constraint_Type, Key_Column.Ordinal_Position FROM Information_Schema.Table_Constraints AS TableConstraints INNER JOIN Information_Schema.Constraint_Column_Usage AS ColumnConstraint ON TableConstraints.Constraint_Catalog = ColumnConstraint.Constraint_Catalog AND TableConstraints.Constraint_Schema = ColumnConstraint.Constraint_Schema AND TableConstraints.Constraint_Name = ColumnConstraint.Constraint_Name INNER JOIN Information_Schema.Key_Column_Usage AS Key_Column ON ColumnConstraint.Constraint_Catalog = Key_Column.Constraint_Catalog AND ColumnConstraint.Constraint_Schema = Key_Column.Constraint_Schema AND ColumnConstraint.Constraint_Name = Key_Column.Constraint_Name AND ColumnConstraint.Column_Name = Key_Column.Column_Name WHERE TableConstraints.Table_Catalog = @Table_Catalog AND TableConstraints.Table_Schema = @Table_Schema AND TableConstraints.Table_Name = @Table_Name ORDER BY TableConstraints.Table_Name, TableConstraints.Constraint_Type, Key_Column.Ordinal_Position" ; // Añade los parámetros parameters.Add("@Table_Catalog", table.Catalog); parameters.Add("@Table_Schema", table.Schema); parameters.Add("@Table_Name", table.Name); // Carga los datos using (DbDataReader reader = await connection.ExecuteReaderAsync(sql, parameters, CommandType.Text, timeout, cancellationToken)) { // Lee los datos while (!cancellationToken.IsCancellationRequested && await reader.ReadAsync(cancellationToken)) { ConstraintDbModel constraint = new ConstraintDbModel(); // Asigna los datos del registro constraint.Catalog = (string)reader.IisNull("Table_Catalog"); constraint.Schema = (string)reader.IisNull("Table_Schema"); constraint.Table = (string)reader.IisNull("Table_Name"); constraint.Column = (string)reader.IisNull("Column_Name"); constraint.Name = (string)reader.IisNull("Constraint_Name"); constraint.Type = GetConstratype((string)reader.IisNull("Constraint_Type")); constraint.Position = (int)reader.IisNull("Ordinal_Position"); // Añade la restricción a la colección table.Constraints.Add(constraint); } // Cierra el recordset reader.Close(); } }
/// <summary> /// Obtiene la sentencia SELECT de consulta de datos de una tabla /// </summary> private Sentences.Parameters.ProviderSentenceModel GetSelect(TableDbModel table) { string fields = string.Empty; // Añade los campos de la tabla foreach (FieldDbModel field in table.Fields) { // Añade el separador if (!string.IsNullOrEmpty(fields)) { fields += ", "; } // Añade el nombre de campo fields += $"[{field.Name}]"; } // Añade la cadena SQL al comando return(new Sentences.Parameters.ProviderSentenceModel($"SELECT {fields} FROM {table.FullName}", TimeSpan.FromMinutes(5))); }
/// <summary> /// Carga las tablas de un esquema /// </summary> private async Task LoadTablesAsync(SqlServerProvider connection, SchemaDbModel schema, TimeSpan timeout, CancellationToken cancellationToken) { string sql = @"SELECT Tables.TABLE_CATALOG, Tables.TABLE_SCHEMA, Tables.TABLE_NAME, Tables.TABLE_TYPE, Objects.Create_Date, Objects.Modify_Date, Properties.Value AS Description FROM INFORMATION_SCHEMA.TABLES AS Tables INNER JOIN sys.all_objects AS Objects ON Tables.Table_Name = Objects.name LEFT JOIN sys.extended_properties AS Properties ON Objects.object_id = Properties.major_id AND Properties.minor_id = 0 AND Properties.name = 'MS_Description' ORDER BY Tables.TABLE_NAME" ; // Carga las tablas using (DbDataReader reader = await connection.ExecuteReaderAsync(sql, null, CommandType.Text, timeout, cancellationToken)) { // Recorre la colección de registros while (!cancellationToken.IsCancellationRequested && await reader.ReadAsync(cancellationToken)) { TableDbModel table = new TableDbModel(); // Asigna los datos del registro al objeto table.Catalog = (string)reader.IisNull("TABLE_CATALOG"); table.Schema = (string)reader.IisNull("TABLE_SCHEMA"); table.Name = (string)reader.IisNull("TABLE_NAME"); table.CreatedAt = (DateTime)reader.IisNull("Create_Date"); table.UpdatedAt = (DateTime)reader.IisNull("Modify_Date"); table.Description = (string)reader.IisNull("Description"); // Añade el objeto a la colección schema.Tables.Add(table); } // Cierra el recordset reader.Close(); } // Carga los datos de las tablas foreach (TableDbModel table in schema.Tables) { await LoadColumnsAsync(connection, table, timeout, cancellationToken); await LoadConstraintsAsync(connection, table, timeout, cancellationToken); } }
/// <summary> /// Carga las columnas de una tabla /// </summary> private async Task LoadColumnsAsync(SqlServerProvider connection, TableDbModel table, TimeSpan timeout, CancellationToken cancellationToken) { ParametersDbCollection parameters = new ParametersDbCollection(); string sql; // Añade los parámetros parameters.Add("@Table_Catalog", table.Catalog); parameters.Add("@Table_Schema", table.Schema); parameters.Add("@Table_Name", table.Name); // Crea la cadena SQL sql = @"SELECT Columns.Column_Name, Columns.Ordinal_Position, Columns.Column_Default, Columns.Is_Nullable, Columns.Data_Type, Columns.Character_Maximum_Length, CONVERT(int, Columns.Numeric_Precision) AS Numeric_Precision, CONVERT(int, Columns.Numeric_Precision_Radix) AS Numeric_Precision_Radix, CONVERT(int, Columns.Numeric_Scale) AS Numeric_Scale, CONVERT(int, Columns.DateTime_Precision) AS DateTime_Precision, Columns.Character_Set_Name, Columns.Collation_Catalog, Columns.Collation_Schema, Columns.Collation_Name, Objects.is_identity, Properties.value AS Description FROM Information_Schema.Columns AS Columns INNER JOIN sys.all_objects AS Tables ON Columns.Table_Name = Tables.name INNER JOIN sys.columns AS Objects ON Columns.Column_Name = Objects.name AND Tables.object_id = Objects.object_id LEFT JOIN sys.extended_properties AS Properties ON Objects.object_id = Properties.major_id AND Properties.minor_id = Objects.column_id AND Properties.name = 'MS_Description' WHERE Columns.Table_Catalog = @Table_Catalog AND Columns.Table_Schema = @Table_Schema AND Columns.Table_Name = @Table_Name ORDER BY Ordinal_Position" ; // Carga los datos using (DbDataReader reader = await connection.ExecuteReaderAsync(sql, parameters, CommandType.Text, timeout, cancellationToken)) { // Lee los datos while (!cancellationToken.IsCancellationRequested && await reader.ReadAsync(cancellationToken)) { FieldDbModel column = new FieldDbModel(); // Asigna los datos del registro column.Name = (string)reader.IisNull("Column_Name") as string; column.OrdinalPosition = (int)reader.IisNull("Ordinal_Position", 0); column.Default = (string)reader.IisNull("Column_Default"); column.IsRequired = ((string)reader.IisNull("Is_Nullable")).Equals("no", StringComparison.CurrentCultureIgnoreCase); column.DbType = (string)reader.IisNull("Data_Type"); column.Length = (int)reader.IisNull("Character_Maximum_Length", 0); column.NumericPrecision = (int)reader.IisNull("Numeric_Precision", 0); column.NumericPrecisionRadix = (int)reader.IisNull("Numeric_Precision_Radix", 0); column.NumericScale = (int)reader.IisNull("Numeric_Scale", 0); column.DateTimePrecision = (int)reader.IisNull("DateTime_Precision", 0); column.CharacterSetName = (string)reader.IisNull("Character_Set_Name"); column.CollationCatalog = (string)reader.IisNull("Collation_Catalog"); column.CollationSchema = (string)reader.IisNull("Collation_Schema"); column.CollationName = (string)reader.IisNull("Collation_Name"); column.IsIdentity = (bool)reader.IisNull("is_identity"); column.Description = (string)reader.IisNull("Description") as string; // Añade la columna a la colección table.Fields.Add(column); } // Cierra el recordset reader.Close(); } }
/// <summary> /// Convierta las columnas /// </summary> private StructDocumentationModelCollection ConvertColumns(StructDocumentationModel parent, List <FieldDbModel> fields, TableDbModel table) { StructDocumentationModelCollection structsDoc = new StructDocumentationModelCollection(); // Añade la información de las columnas foreach (FieldDbModel column in fields) { StructDocumentationModel structDoc = CreateStruct(parent, column, "Column"); // Añade los parámetros de la columna structDoc.Parameters.Add("Summary", column.Description); structDoc.Parameters.Add("Default", column.Default); structDoc.Parameters.Add("IsNullable", !column.IsRequired); structDoc.Parameters.Add("DataType", column.DbType); structDoc.Parameters.Add("CharacterMaximumLength", column.Length); structDoc.Parameters.Add("NumericPrecision", column.NumericPrecision); structDoc.Parameters.Add("NumericPrecisionRadix", column.NumericPrecisionRadix); structDoc.Parameters.Add("NumericScale", column.NumericScale); structDoc.Parameters.Add("DateTimePrecision", column.DateTimePrecision); structDoc.Parameters.Add("CharacterSetName", column.CharacterSetName); structDoc.Parameters.Add("CollationCatalog", column.CollationCatalog); structDoc.Parameters.Add("CollationSchema", column.CollationSchema); structDoc.Parameters.Add("CollationName", column.CollationName); structDoc.Parameters.Add("IsIdentity", column.IsIdentity); // Obtiene el valor que indica si es una tabla foránea if (table != null) { structDoc.Parameters.Add("IsForeignKey", CheckIsForeignKey(table, column)); } // Obtiene el tipo structDoc.Parameters.Add("Type", column.DbType); // Añade la estructura a la colección structsDoc.Add(structDoc); } // Devuelve la colección return(structsDoc); }
/// <summary> /// Convierte los desencadenadores /// </summary> private StructDocumentationModelCollection ConvertTriggers(StructDocumentationModel parent, List <TriggerDbModel> triggers, TableDbModel table) { StructDocumentationModelCollection structsDoc = new StructDocumentationModelCollection(); // Añade los desencadenadores foreach (TriggerDbModel trigger in triggers) { if (trigger.Table.EqualsIgnoreCase(table.Name)) { StructDocumentationModel structDoc = CreateStruct(parent, trigger, "Trigger"); // Añade los parámetros de la tabla structDoc.Parameters.Add("Table", trigger.Table); structDoc.Parameters.Add("UserName", trigger.UserName); structDoc.Parameters.Add("Category", trigger.Category); structDoc.Parameters.Add("IsExecuted", trigger.IsExecuted); structDoc.Parameters.Add("IsExecutionAnsiNullsOn", trigger.IsExecutionAnsiNullsOn); structDoc.Parameters.Add("IsExecutionQuotedIdentOn", trigger.IsExecutionQuotedIdentOn); structDoc.Parameters.Add("IsAnsiNullsOn", trigger.IsAnsiNullsOn); structDoc.Parameters.Add("IsQuotedIdentOn", trigger.IsQuotedIdentOn); structDoc.Parameters.Add("IsExecutionAfterTrigger", trigger.IsExecutionAfterTrigger); structDoc.Parameters.Add("IsExecutionDeleteTrigger", trigger.IsExecutionDeleteTrigger); structDoc.Parameters.Add("IsExecutionFirstDeleteTrigger", trigger.IsExecutionFirstDeleteTrigger); structDoc.Parameters.Add("IsExecutionFirstInsertTrigger", trigger.IsExecutionFirstInsertTrigger); structDoc.Parameters.Add("IsExecutionFirstUpdateTrigger", trigger.IsExecutionFirstUpdateTrigger); structDoc.Parameters.Add("IsExecutionInsertTrigger", trigger.IsExecutionInsertTrigger); structDoc.Parameters.Add("IsExecutionInsteadOfTrigger", trigger.IsExecutionInsteadOfTrigger); structDoc.Parameters.Add("IsExecutionLastDeleteTrigger", trigger.IsExecutionLastDeleteTrigger); structDoc.Parameters.Add("IsExecutionLastInsertTrigger", trigger.IsExecutionLastInsertTrigger); structDoc.Parameters.Add("IsExecutionLastUpdateTrigger", trigger.IsExecutionLastUpdateTrigger); structDoc.Parameters.Add("IsExecutionTriggerDisabled", trigger.IsExecutionTriggerDisabled); structDoc.Parameters.Add("IsExecutionUpdateTrigger", trigger.IsExecutionUpdateTrigger); structDoc.Parameters.Add("Summary", trigger.Description); structDoc.Parameters.Add("DateCreate", Format(trigger.CreatedAt)); structDoc.Parameters.Add("DateUpdate", Format(trigger.UpdatedAt)); structDoc.Parameters.Add("DateReference", trigger.DateReference); structDoc.Parameters.Add("Prototype", trigger.Content); // Añade la estructura structsDoc.Add(structDoc); } } // Devuelve la colección de estructuras return(structsDoc); }