none
Ability for End User to Define New Tables from Relational DataSet RRS feed

  • Question

  • I'm relatively new to using ADO.NET, so please excuse my ignorance if this is trivial or impossible!

    I have a relational DataSet that is generated programmatically, containing tables that are then exposed to the end user via a DataGrid control.  What I would like to do is allow the end user to build their own custom table from the tables/fields that are in the DataSet, providing them the flexibility to generate table content as they like (obviously within the constraints of the data relations).

    As a hypothetical example, I might have the following:

    • Cars table with columns CarID, CarName, RadioID, EngineID
    • Radios table with columns RadioID, RadioBrand, FM?, AM?, CDPlayer?

    The end user can see each of these tables individually, but may want a custom table that has columns CarID, CarName, RadioBrand, CDPlayer?

    Does anybody know of an example that could handle this that I could use as a starting point?  An open-source project would be great or even a few pointers on how to get started with both the guts to making this work and any ideas for a good UI for doing this.

    Thanks in advance for any ideas or suggestions!

    Wednesday, November 16, 2011 9:22 PM

All replies

  • Hi rogersbr,

    You can add some CheckBoxs in your page, the text of every CheckBox is a column's name. Let user to choose which column they want to display in the DataGrid, and add all of them into a list. Then call the method below:

    public static DataTable CreateCustomTable(List<string> columnList)
            {
                using (SqlConnection con = new SqlConnection("your connection string"))
                using (SqlCommand cmd = new SqlCommand())
                {
                    int count = columnList.Count();
                    StringBuilder sb = new StringBuilder();
    
                    sb.Append("select ");
    
                    //add column which need to query 
                    for (int i = 0; i < count; i++)
                    {
                        sb.Append(columnList[i] + ",");
                    }
    
                    //Remove the last ','
                    sb = sb.Remove(sb.Length - 1, 1);
                    sb.Append(" from Cars,Radios where Cars.RadioID=Radios.RadioID");
    
                    cmd.Connection = con;
                    cmd.CommandText = sb.ToString();
    
                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                    DataTable dt = new DataTable();
                    adapter.Fill(dt);
                    return dt;
                }
            }
    

     The UI can be like this:

    At the button's click event, call the method above.

    If you have any problem, please feel free to let me know.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us
    Friday, November 18, 2011 3:14 AM
    Moderator
  • Allen,

    Thanks for the reply, but perhaps I didn't explain my scenario well enough.  The main step that I'm trying to figure out is how to somehow automate going from the hard-coded DataSet/DataTables/DataRelations to what you suggest above.  How can I somehow automatically determine what fields should be available for selection (your checkboxes) to a user for building up their custom table?  The constraints should come from the predefined DataRelations, such that if I initially present the entire list of fields in the database (or presumably a refined list of applicable fields) and a user checks one, then the list refreshes to only allow the user to pick additional fields that can go along with the first field they picked.

    Expanding on my previous example, if I also have an Animals table with AnimalID, AnimalName, NumberLegs fields and I expose all fields from my three tables as checkboxes, then if the user chooses the CarName field, the fields related to the animals table will no longer be available, while the remaining fields from the cars and radios table will be.  Similarly, if they first picked the AnimalName field, only the other animal table fields would remain.

    Thanks,

    Brady

    Friday, November 18, 2011 6:37 PM
  • Hi rogersbr,

    If the user choose a column which has no relation with other tables, you can't get data from other tables related to this column. If you want to do that, you need query all the datas to a dataset and base on user's choice to determin which column of the datagrid should be hidden, set that column's visible to false.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us
    Monday, November 21, 2011 3:33 AM
    Moderator
  • Allen,

    I realize that if there are no relations between tables, that I wouldn't be able to easily get the data; I don't want to do this.  Getting the data between related tables is also not a problem.

    What I'm trying to figure out is how to expose a list of field names (i.e. columns) that the user can pick from to build up their own custom table, such that they are restricted to picking fields that can go together (i.e. are either in the same table or can be reached in another table via relations).

    I suspect this could be done with some custom logic to first obtain all the possible fields from all tables in the database, then as the user picks fields, run some more logic that regenerates the list of available fields, but this time only retrieving those that are related to the already chosen field(s).

    My original question posted here was to try to find out if there is an easy way to do this using some functionality in ADO.NET or if somebody knows of a 3rd party product or open source project that does something like this.

    Thanks,

    Brady

    Tuesday, November 22, 2011 6:58 PM
  • Hi rogersbr,

    I'll continue to research on it, if I have any idea, I'll tell you at once.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us
    Wednesday, November 23, 2011 3:54 AM
    Moderator