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