/// <summary>
/// Carga las columnas de la vista
/// </summary>
private async Task LoadColumnsAsync(SqlServerProvider connection, ViewDbModel view, TimeSpan timeout, CancellationToken cancellationToken)
{
ParametersDbCollection parameters = new ParametersDbCollection();
string sql = @"SELECT Table_Catalog, Table_Schema, Table_Name, Column_Name
FROM Information_Schema.View_Column_Usage
WHERE View_Catalog = @View_Catalog
AND View_Schema = @View_Schema
AND View_Name = @View_Name" ;
// Asigna lo parámetros
parameters.Add("@View_Catalog", view.Catalog);
parameters.Add("@View_Schema", view.Schema);
parameters.Add("@View_Name", view.Name);
// Carga las columnas
using (DbDataReader reader = await connection.ExecuteReaderAsync(sql, parameters, CommandType.Text, timeout, cancellationToken))
{
// Lee los registros
while (!cancellationToken.IsCancellationRequested && await reader.ReadAsync(cancellationToken))
{
FieldDbModel column = new FieldDbModel();
// Carga los datos de la columna
column.Catalog = (string)reader.IisNull("Table_Catalog");
column.Schema = (string)reader.IisNull("Table_Schema");
column.Table = (string)reader.IisNull("Table_Name");
column.Name = (string)reader.IisNull("Column_Name");
// Añade la columna a la colección
view.Fields.Add(column);
}
// Cierra el recordset
reader.Close();
}
}
/// <summary>
/// Obtiene el tipo de datos de una columna
/// </summary>
private string GetDataType(FieldDbModel column)
{
string type = column.DbType;
// Añade los datos
if (column.Length > 0)
{
type += $"({column.Length})";
}
// Devuelve el tipo
return(type);
}
/// <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>
/// 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();
}
}
