/// <summary>
/// Obtiene las propiedades de una tabla a partir de una entidad
/// </summary>
/// <param name="entity">Entidad para buscar su tabla</param>
/// <param name="database">enumerado para saber a que base de datos se va a conectar</param>
/// <returns>Lista de tipo ColumnDefinition con todos los datos</returns>
/// <history>
/// [emoguel] created 05/08/2016
/// </history>
public static List <ColumnDefinition> GetFieldsByTable <T>(T entity, EnumDatabase database) where T : class
{
DbContext dbContext = null;
#region dbContext
switch (database)
{
case EnumDatabase.IntelligentMarketing:
{
dbContext = new IMEntities(ConnectionHelper.ConnectionString());
break;
}
case EnumDatabase.Asistencia:
{
dbContext = new AsistenciaEntities(ConnectionHelper.ConnectionString(database));
break;
}
case EnumDatabase.IntelligenceContracts:
{
dbContext = new ICEntities(ConnectionHelper.ConnectionString(database));
break;
}
}
#endregion
using (dbContext)
{
string strTableName = GetTableName(entity, dbContext);
string strDatabaseName = dbContext.Database.Connection.Database;
#region Query
string strQuery = "Use " + strDatabaseName + @" SELECT
C.name as [Column],
T.name as [Type],
C.Precision,
C.Scale,
CASE WHEN CE.NUMERIC_PRECISION IS NOT NULL THEN CE.NUMERIC_PRECISION + (CASE WHEN CE.NUMERIC_SCALE > 0 THEN 1 ELSE 0 END)
ELSE IsNull(CE.CHARACTER_MAXIMUM_LENGTH, C.precision) END as MaxLength,
CE.COLUMN_DEFAULT as [Default Value],
CE.IS_NULLABLE as Nullable,
IsNull(P.value, '') as Description
FROM sys.tables as TA
LEFT JOIN sysusers S on TA.schema_id = S.uid
INNER JOIN sys.columns as C on C.object_id = TA.object_id
INNER JOIN sys.types as T on T.system_type_id = C.system_type_id and T.name <> 'sysname'
LEFT JOIN INFORMATION_SCHEMA.COLUMNS CE ON CE.TABLE_SCHEMA = S.name AND CE.TABLE_NAME = TA.name AND CE.COLUMN_NAME = C.name
LEFT JOIN sys.extended_properties as P on P.major_id = C.object_id and P.minor_id = C.column_id and P.class = 1
WHERE TA.name = '" + strTableName + @"'
ORDER BY S.name, TA.name, C.column_id
";
#endregion
var dQuery = dbContext.Database.SqlQuery <ColumnDefinition>(strQuery);
return(dQuery.ToList());
}
}
/// <summary>
/// Devuelve la lista de collaborators
/// </summary>
/// <param name="collaborator">objeto con los parametros para el SP</param>
/// <returns>Lista de tipo Collaborator</returns>
/// <history>
/// [emoguel] created 21/06/2016
/// </history>
public static async Task <List <Collaborator> > GetCollaborators(Collaborator collaborator)
{
List <Collaborator> lstCollaborator = await Task.Run(() =>
{
using (var dbContext = new AsistenciaEntities(ConnectionHelper.ConnectionString(EnumDatabase.Asistencia)))
{
return(dbContext.USP_ObtenerColaboradoresPorParametro(
(!string.IsNullOrWhiteSpace(collaborator.EmpID))? collaborator.EmpID :"ALL",
(!string.IsNullOrWhiteSpace(collaborator.NombreCompleto))? collaborator.NombreCompleto:"ALL",
(!string.IsNullOrWhiteSpace(collaborator.Puesto))? collaborator.Puesto:"ALL",
(!string.IsNullOrWhiteSpace(collaborator.Locacion))? collaborator.Locacion:"ALL",
(!string.IsNullOrWhiteSpace(collaborator.Hotel))? collaborator.Locacion:"ALL").ToList());
}
});
return(lstCollaborator);
}
