Dynamically Creating GridView

Question

I want to create a GridView that displays records for PDF Files. These records can have meta data attached that is customizeable by the user, so they can create their own columns and enter their own information in there. I then want it to be displayed in a GridView, so they can order each column and the column order if the column order is -1 it will not display in the GridView.

For example there is a static table

DocumentsTable:
ID int
PDF_Folder varchar
UserID int

Then there is another table that users can create their own columns for

MetaDataColumns:
ID int
userid int foreign key
KeyName varchar
Order int

and the table to hold the values

MetaDataValues:
ID int
UserID int foreign key
DocumentID int foreign key
MetaDataID int foreign key
value varchar(100)

Now the problem is I need to get the columns from the MetaDataColumn to create the GridView and then populate it with the values in MetaDataValue table. My original plan is to have a function that dynamically creates the GridView and adds columns to it, however I am stuck on how to use the values in the MetaDataValue as the columns. Alternatively I could just have the GridView AutoGenerate columns but I need to customize the SQL to display the custom data. I'm a bit stuck on how to even approach this.

One approach I have come up with is this pseudo code:

private DataTable CreateColumns()
{
   var columns =  select * from MetaDataColumns 
                  where userid = UserId;

   DataTable dt = new DataTable();

   foreach (column in columns)
   {
       dt.Columns.Add(new DataColumn(column[keyName], typeof(string));  //assumes all string
   }

 return dt
}

private void PopulateDG(DataGrid dg)
{
    var documents = select * from DocumentsTable
                     where userid=UserId;

    foreach (document in documents)
    {            
        var columnValues = select * from MetaDatavalues 
                           documentID == document.id;

        DataRow dr = dg.NewRow();
        dr[columnValues.KeyName] = columnValues.value;

    }

 }

 private void LoadGV()
 {  
   DataGrid dg = CreateColumns();
   PopulateDG(dg);
   GridView.datasource = dg;
   GridView.DataBind();
  }

One of the things I dont like about this design is for every row in the documents table it creates another query. Im not sure if this is a problem with SQL?

Community · Accepted Answer · 2017-05-23 11:56:21Z

2

+50

Your problem is mainly due to the design of the database. You have to dynamically add columns because you've translated what would be a column (in 3NF) to a row in your tables. Obviously, this is because you allow users to add their own columns - my mind shudders, but that's the way the app works :-).

Due to the structure of MetaDataColumns I'm going to assume that a user has the ability to define a set of column names that they can then choose to apply to an individual document as they wish.

I think the problem is that in trying to normalise everything properly, in a completely de-normalised database, you've managed to cause yourself a lot of hassle. My solution would be to denormalise your table MetaDataValues. You don't mention what RDBMS you're using but MySQL has a hard limit of 4096 columns or 65k bytes. The limit in Oracle is 1000 and 1024 in SQL Server.

If you change the structure of MetaDataValues to the following you should be able to fit at least 332 sets of information in there. This would be separately unique on UserID, DocumentID so you could, theoretically, remove the surrogate key ID.

MetaDataValues:
ID int
UserID int foreign key
DocumentID int foreign key
KeyName1 varchar
Order1 int
Value1 varchar(100) 
...
KeyNameN varchar
OrderN int
ValueN varchar(100)

Of course, this does set an upper-limit on the number of columns that you'll be able to allow an individual user to create to 332; but, it's normal to limit the abilities of users to go insane and anyone who can think of 332 separate bits of metadata to store on a single PDF deserves to be limited somehow.

If you do have particularly information obsessed users you can always declare a second table with the same structure and keep on filling it in.

Doing this would mean that MetaDataColumns would not be used for anything but displaying the users options to them. You would have to update MetaDataValues each time a change was made and ensuring that you didn't over-write already extant information may be a little bit of a pain. I would suspect you'd have to do something like selecting the record before you update it, iterating through KeyName1 .. KeyNameN and filling in the first one that doesn't have any data in it. Alternatively you could just write an absolutely horrible SQL query. Either way, this would become the "choke point".

The other option would be to add an addition column to MetaDataColumns, which indicated which N the column related to but this restricts a user to 332 columns absolutely rather than 332 per document.

However, your selection from the database is now insanely easy:

select d.*, m.*
  from DocumentsTable d
  join MetaDataValues m
    on d.ID = m.DocumentID
   and d.UserID = m.UserID
 where d.UserId = ?

There's no need to try to iterate through tables dynamically generating 1,000 column select statements. All the information is right there and easily accessible for you.

At the end of the day the "correct" answer to your question depends on where you want to spend the time. Do you want it to take half a second longer to create or update a document or half a second (probably more) to select the information on that document.

Personally, I think users understand that creating something takes time but there's nothing more annoying than having to wait ages to see something appear.

There is another, social, rather than database solution. Don't allow your users to create their own columns. Pick the most common pieces of metadata that your users want and create them properly in a normalised form in the database. You'd be able to create columns with the correct data-type (which will save you a lot of hassle in the long run) and have a much easier time of it. I doubt you'll be lucky enough to have that happen; but it's worth bearing in mind.

edited May 23, 2017 at 11:56

CommunityBot

11 silver badge

answered Jul 14, 2012 at 23:09

Ben

53.1k36 gold badges133 silver badges156 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Michael Over a year ago

My original design was just to have 10 columns that the users could rename.. Obviously I could extend this to as many columns as I need, and just have a maximum limit...

Michael Over a year ago

before i go down this route.. is there not a way I could use PIVOT to only use 1 (or 2 to get the column names) query.

Michael Over a year ago

also with this idea, what is the point of the metadatatable. Wouldnt I just put keynameN, orderN, valueN in the document table? the only reason for the other 2 tables were so i could dynamically add columns..

Michael Over a year ago

actually 2 tables. the values in the document table and the keyname and order.

Ben Over a year ago

On pivot I know of no DB where you can dynamically name a column. Yes you can reduce it to two. I kept then separate because I didn't know how big an undertaking it would be. It will be a pain to update the keywords, but if you've only got 10 columns it should be easier.

|

Nathan · Accepted Answer · 2012-07-13 07:49:32Z

1

Do you mean

<DataGrid ItemsSource="{Binding}" AutoGenerateColumns="false">
    <DataGrid.Columns>
        <DataGridTextColumn Binding="{Binding Path=Id}" Header="ID"/>
        <DataGridTextColumn Binding="{Binding Path=Name}" Header="Name"/>
    </DataGrid.Columns>
</DataGrid>

and create a class in code behind like

public class MetadataSource
{
    public MetadataSource()
    { // use reflection to create properties/values }
}

answered Jul 13, 2012 at 7:49

Nathan

4017 silver badges13 bronze badges

Comments

Tianzhen Lin · Accepted Answer · 2012-07-14 16:08:01Z

I definitely see an issue with your pseudo-code approach where each row of a data represents a separate query. When you have 1,000 rows of data, you would end up having 1,000+ queries hitting the database, your page would be extremely slow.

You could at least combine two of the SQL queries as an immediate step to improve the situation, such as:

var values = SELECT * FROM MetaDataValues
WHERE documentid IN (SELECT id FROM DocumentsTable WHERE userid = UserId)

foreach (val in values)
{            
    DataRow dr = dg.NewRow();
    ...
}

I generally don't prefer "SELECT *" approach, it forces the database to make additional query to fill in all the columns. In your case, given the user could be limited to the columns they could see, the SQL in many ways would bring in more data than necessary. So your code could be further consolidated and optimized like:

private void PopulateDG(DataGrid dg)
{
    var columns = SELECT columnKey FROM MetaDataColumns
            WHERE userid = UserId;

    // pseudo code, join column keys into a comma delimited string
    string columnFields = string.Join(",", columns);

    string getValueSql = string.Format("SELECT {0} FROM MetaDataValues
            WHERE documentid IN (SELECT id FROM DocumentsTable WHERE userid = UserId)", columnFields);

    var values = ExecuteSql(getValueSql);

Collectives™ on Stack Overflow

Dynamically Creating GridView

3 Answers 3

6 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related