none
How to get label text from table reference on database based on TableName and FieldName RRS feed

  • Question

  • Problem

    How to get label text from table reference on database  based on Table Name and Field Name dynamically

    from database and Display them on windows form create Employee.


    Meaning i need to get label text dynamically from database not static from model

    So that Every Time I  need to change text of label i will change it from database depend on reference table 

    AND No need to change from code .

    IF You give me function or any thing general because i have more models and view i need to make like that)


    Tools used sql server 2012 and visual studio 2017 windows forms


    Query Get Data from reference Table

    SELECT TableName, FieldName,EnglishtextforLabel FROM ReferenceFile WHERE (FieldName = 'EmployeeId' ) AND TableName = 'Employee'

    Database Have two tables Employee and Reference File(may be increase models and views so that if any thing general is prefer) 

    Models classes Include HRContext
    
     public class ReferenceFile(have 3 key as composit keys(Code,TableName,FieldName))
        {
            public int Code { get; set; }
            public string TableName { get; set; }
            public string FieldName { get; set; }
            public string EnglishtextforLabel{ get; set; }
    
        }
     public class Employee
        {
            public int EmployeeId { get; set; }
            public string EmployeeName { get; set; }
            public int EmployeeAge { get; set; }
        }
     public class HRContext : DbContext
        {
            public HRContext(DbContextOptions<HRContext> options)
    : base(options)
            { }
    
            public DbSet<Employee> Employees { get; set; }
            public DbSet<ReferenceFile> ReferenceFiles { get; set; }
            protected override void OnModelCreating(ModelBuilder modelBuilder)
            {
                base.OnModelCreating(modelBuilder);
                modelBuilder.Entity<Employee>()
                   .HasKey(t => new { t.EmployeeId });
    
                modelBuilder.Entity<ReferenceFile>()
                    .HasKey(t => new { t.Code,t.TableName,t.FieldName });
            }
        }
    Here i need to get label on view create from table reference not static field name

    Sample data for ReferenceTable

    Code  TableName  FieldName       EnglishtextforLabel 
    1      Employee     EmployeeId              Code
    2      Employee     EmployeeName         Name        
    3      Employee     EmployeeAge            Age
    As reference table above sample labels on windows form  create employee must be Give me the Result below :

    Code

    Name

    Age


    • Edited by engahmedbarbary Thursday, December 27, 2018 10:33 AM clear more data
    Thursday, December 27, 2018 10:30 AM

Answers

  • Hi engahmedbarbary,

    If you want to get all the table in entity framework dbcontext. please try the following method. 

    using (var context = new Model1())
                {
                    var metadata = ((IObjectContextAdapter)context).ObjectContext.MetadataWorkspace;
    
                    var tables = metadata.GetItemCollection(DataSpace.SSpace)
                        .GetItems<EntityContainer>()
                        .Single()
                        .BaseEntitySets
                        .OfType<EntitySet>()
                        .Where(s => !s.MetadataProperties.Contains("Type")
                        || s.MetadataProperties["Type"].ToString() == "Tables");
    
                    foreach (var table in tables)
                    {
                        var tableName = table.MetadataProperties.Contains("Table")
                            && table.MetadataProperties["Table"].Value != null
                            ? table.MetadataProperties["Table"].Value.ToString()
                            : table.Name;
    
                        var tableSchema = table.MetadataProperties["Schema"].Value.ToString();
    
                        Console.WriteLine(tableSchema + "." + tableName);
                    }
                }

    You could also get all of the fields via the following method.

     var names = typeof(Employee).GetProperties()
                 .Select(property => property.Name)
                 .ToArray();

    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, December 31, 2018 6:20 AM
    Moderator

All replies

  • How is your ReferenceFile related to Employee. You might have composite keys on the first class, but i see no attribute (FK) back to the EMployee class.

    Normally, when you have all attributes as keys in a table, EF treats them as navigation property. 

    But first, can you clarify more on your relationship between both tables??

    Thursday, December 27, 2018 7:31 PM
  • thank you for reply

    actually no relation between two tables 

    but only join between two table employee and table reference

    by select from reference where tablename =employe and fieldname=all fields inside employee table 

    Saturday, December 29, 2018 8:46 AM
  • Hi engahmedbarbary,

    If you want to get all the table in entity framework dbcontext. please try the following method. 

    using (var context = new Model1())
                {
                    var metadata = ((IObjectContextAdapter)context).ObjectContext.MetadataWorkspace;
    
                    var tables = metadata.GetItemCollection(DataSpace.SSpace)
                        .GetItems<EntityContainer>()
                        .Single()
                        .BaseEntitySets
                        .OfType<EntitySet>()
                        .Where(s => !s.MetadataProperties.Contains("Type")
                        || s.MetadataProperties["Type"].ToString() == "Tables");
    
                    foreach (var table in tables)
                    {
                        var tableName = table.MetadataProperties.Contains("Table")
                            && table.MetadataProperties["Table"].Value != null
                            ? table.MetadataProperties["Table"].Value.ToString()
                            : table.Name;
    
                        var tableSchema = table.MetadataProperties["Schema"].Value.ToString();
    
                        Console.WriteLine(tableSchema + "." + tableName);
                    }
                }

    You could also get all of the fields via the following method.

     var names = typeof(Employee).GetProperties()
                 .Select(property => property.Name)
                 .ToArray();

    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, December 31, 2018 6:20 AM
    Moderator