none
How to dynamically bind datagridview to runtime-determined table entities?

    Question

  • Hi all,

    I'm making a simple table-editor form. This has a dropdown with all the tables in the model, which the user selects. Upon selecting a table name from the dropdown, the datagridview is to be bound to the appropriate table.

    Right now I'm doing the binding with a switch like:

            private void RefreshGrid(dbcontext db, string tablename)
            {
                switch (tablename)
                {
                    case "tablename1":
                        dgvTableEditor.DataSource = db.tablename1;
                        break;
                    case "tablename2":
                        dgvTableEditor.DataSource = db.tablename2;
                        break;
                    case "tablename3":
                        dgvTableEditor.DataSource = db.tablename3;
                        break;
                }
            }

    Obviously this is horrible. What's a proper way to achieve this?

    Thanks for any tips,

    sff

    Sunday, December 01, 2013 2:43 PM

Answers

  • Hello,

    For this issue, I made a sample and please see it below, we can use the db.Database.SqlQuery.

    Form codes:

    public partial class Form1 : Form
    
        {
    
            public Form1()
    
            {
    
                InitializeComponent();
    
                List<string> tableNameList = new List<string>() { "", "Order", "OrderDetail" };
    
                this.comboBox1.DisplayMember = "TableName";
    
                this.comboBox1.DataSource = tableNameList;
    
            }
    
    
            private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
    
            {
    
                string queryTable = this.comboBox1.SelectedValue.ToString();
    
                List<object> objectList = new List<object>();
    
                if (queryTable != string.Empty)
    
                {
    
                    //Dynamic create a instance by the class name
    
                    object dynamicType = Activator.CreateInstance(Type.GetType("DynamicallyBindDatagridview." + queryTable + ""));
    
                    Type type = dynamicType.GetType();
    
                    using (WinFormDBEntities db = new WinFormDBEntities())
    
                    {
    
                        var result = db.Database.SqlQuery(type, "select * from [" + queryTable + "]");
    
                        foreach (object obj in result)
    
                        {
    
                            objectList.Add(obj);
    
                        }
    
                        this.dataGridView1.DataSource = objectList;
    
                    }
    
                }
    
            }
    
        }
    

    When I choose order, it loaded the order table data:

    When I choose orderdetail, it loaded the orderdetail table data:

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, December 02, 2013 3:23 AM
  • oh yah sorry - i only put in the part that i couldn't figured out before.

    1) string surgery to remove those qualifiers:

            private List<string> GetEFTableNames(TestEntities db)
            {
                List<string> ret = new List<string>();
    
                EntityContainer container = db.MetadataWorkspace.GetEntityContainer(db.DefaultContainerName, DataSpace.CSpace);
                ret = (
                        from meta in container.BaseEntitySets
                        where meta.BuiltInTypeKind == BuiltInTypeKind.EntitySet
                        select meta.ElementType.ToString()
                            ).ToList<string>();
    
                // get rid of the qualifier part
                List<string> temp = new List<string>();
                foreach (string tablename in ret)
                {
                    temp.Add(tablename.Remove(0, tablename.IndexOf(".") + 1));
                }
                ret = temp;
    
                return ret;
            }

    2) at binding time, run the table name list thru the EF pluralization service:

            private void RefreshGrid(TestEntities db, string tablename)
            {
                string pluralizedtable = GetPluralized(tablename);
    
                try
                {
                    dgvTableEditor.DataSource = db.GetType().GetProperty(pluralizedtable).GetValue(db, null);
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Cannnot edit table: " + tablename + Environment.NewLine + Environment.NewLine + ex.Message, "Error");
                }
            }
    
            private string GetPluralized(string input)
            {
                string ret = string.Empty;
    
                PluralizationService ps = PluralizationService.CreateService(System.Globalization.CultureInfo.GetCultureInfo("en-us"));
                ret = ps.Pluralize(input);
    
                return ret;
            }

    3) and then the initial load and event handler. nothing fancy.

            private void frmTableEditor_Load(object sender, EventArgs e)
            {
                db = new TestEntities();
    
                lbTables.DataSource = GetEFTableNames(db);
            }
    
            private void lbTables_SelectedIndexChanged(object sender, EventArgs e)
            {
                string selectedtable = lbTables.GetItemText(lbTables.SelectedItem);
                RefreshGrid(db, selectedtable);
            }

    that's the gist. plenty of ways to buff it all.

    thanks,

    sff


    Friday, December 06, 2013 10:50 AM

All replies

  • Hello,

    For this issue, I made a sample and please see it below, we can use the db.Database.SqlQuery.

    Form codes:

    public partial class Form1 : Form
    
        {
    
            public Form1()
    
            {
    
                InitializeComponent();
    
                List<string> tableNameList = new List<string>() { "", "Order", "OrderDetail" };
    
                this.comboBox1.DisplayMember = "TableName";
    
                this.comboBox1.DataSource = tableNameList;
    
            }
    
    
            private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
    
            {
    
                string queryTable = this.comboBox1.SelectedValue.ToString();
    
                List<object> objectList = new List<object>();
    
                if (queryTable != string.Empty)
    
                {
    
                    //Dynamic create a instance by the class name
    
                    object dynamicType = Activator.CreateInstance(Type.GetType("DynamicallyBindDatagridview." + queryTable + ""));
    
                    Type type = dynamicType.GetType();
    
                    using (WinFormDBEntities db = new WinFormDBEntities())
    
                    {
    
                        var result = db.Database.SqlQuery(type, "select * from [" + queryTable + "]");
    
                        foreach (object obj in result)
    
                        {
    
                            objectList.Add(obj);
    
                        }
    
                        this.dataGridView1.DataSource = objectList;
    
                    }
    
                }
    
            }
    
        }
    

    When I choose order, it loaded the order table data:

    When I choose orderdetail, it loaded the orderdetail table data:

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, December 02, 2013 3:23 AM
  • Hi Fred,

    Thanks for the reply.

    I looked around and discovered this no-sql-string approach. Is there a best-practices reason to prefer either over the other?

                List<string> ret = new List<string>();
    
                EntityContainer container = db.MetadataWorkspace.GetEntityContainer(db.DefaultContainerName, DataSpace.CSpace);
                ret = (
                        from meta in container.BaseEntitySets
                        where meta.BuiltInTypeKind == BuiltInTypeKind.EntitySet
                        select meta.ElementType.ToString()
                            ).ToList<string>();

    Thanks again,

    sff

    Friday, December 06, 2013 4:10 AM
  • Hi sherifffruitfly2,

    I run the codes provided by you.However, I only got a collection of entity class names like below:

    So I guess that there should be more codes, could you please share it with us?

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, December 06, 2013 6:28 AM
  • oh yah sorry - i only put in the part that i couldn't figured out before.

    1) string surgery to remove those qualifiers:

            private List<string> GetEFTableNames(TestEntities db)
            {
                List<string> ret = new List<string>();
    
                EntityContainer container = db.MetadataWorkspace.GetEntityContainer(db.DefaultContainerName, DataSpace.CSpace);
                ret = (
                        from meta in container.BaseEntitySets
                        where meta.BuiltInTypeKind == BuiltInTypeKind.EntitySet
                        select meta.ElementType.ToString()
                            ).ToList<string>();
    
                // get rid of the qualifier part
                List<string> temp = new List<string>();
                foreach (string tablename in ret)
                {
                    temp.Add(tablename.Remove(0, tablename.IndexOf(".") + 1));
                }
                ret = temp;
    
                return ret;
            }

    2) at binding time, run the table name list thru the EF pluralization service:

            private void RefreshGrid(TestEntities db, string tablename)
            {
                string pluralizedtable = GetPluralized(tablename);
    
                try
                {
                    dgvTableEditor.DataSource = db.GetType().GetProperty(pluralizedtable).GetValue(db, null);
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Cannnot edit table: " + tablename + Environment.NewLine + Environment.NewLine + ex.Message, "Error");
                }
            }
    
            private string GetPluralized(string input)
            {
                string ret = string.Empty;
    
                PluralizationService ps = PluralizationService.CreateService(System.Globalization.CultureInfo.GetCultureInfo("en-us"));
                ret = ps.Pluralize(input);
    
                return ret;
            }

    3) and then the initial load and event handler. nothing fancy.

            private void frmTableEditor_Load(object sender, EventArgs e)
            {
                db = new TestEntities();
    
                lbTables.DataSource = GetEFTableNames(db);
            }
    
            private void lbTables_SelectedIndexChanged(object sender, EventArgs e)
            {
                string selectedtable = lbTables.GetItemText(lbTables.SelectedItem);
                RefreshGrid(db, selectedtable);
            }

    that's the gist. plenty of ways to buff it all.

    thanks,

    sff


    Friday, December 06, 2013 10:50 AM
  • Hi sherifffruitfly2,

    Thanks for sharing the whole codes. I made a test on it.

    The differences between them are that the no-sql approach will create the sql statement by entity framework provider and then send it to database and mine will directly send the sql statement to database.

    However, yours are more versatility.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, December 09, 2013 9:25 AM