Dynamic GridView Creation

Question

I want to create a GridView that displays entries for PDF files. These records may contain user-defined metadata, so they can create their own columns and enter their own information there. Then I want it to be displayed in the GridView, so they can order each column and column order, if the column order is -1, it will not be displayed in the GridView.

For example, there is a static table

DocumentsTable: ID int PDF_Folder varchar UserID int 

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

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

and a table for storing values

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

Now the problem is that I need to get the columns from MetaDataColumn to create a GridView, and then fill it with the values ​​in the MetaDataValue table. My initial plan is to have a function that dynamically creates a GridView and adds columns to it, however, I am fixated on how to use values ​​in MetaDataValue as columns. Alternatively, I could just use AutoGenerate's GridView columns, but I need to configure SQL to display user data. I am a little fixated on how to even approach this.

One approach that I came up with is the 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 don't like about this design is that a different query is created for each row of the document table. I'm not sure if this is a problem with SQL?

Answer 1

Your problem is mainly related to database design. You must add columns dynamically because you have translated what would be a column ( in 3NF ) into a row in your tables. Obviously, this is due to the fact that you allow users to add their own columns - my brain is trembling, but the way the application works :-).

Due to the structure of the MetaDataColumns I’m going to assume that the user has the ability to define a set of column names that they can then select to apply to a single document as they see fit.

I think the problem is that when you try to normalize everything correctly, in a completely de-normalized database you managed to cause a lot of trouble. My solution would be to denormalize the MetaDataValues table. You do not mention which DBMS you use, 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 can place at least 332 sets of information. This would be uniquely unique on the UserID , DocumentID , so you could theoretically remove the surrogate ID key.

 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 sets an upper limit on the number of columns that you can allow an individual user to create up to 332; but, it’s okay to limit the ability of users to go crazy, and anyone who can think of 332 separate bits of metadata to store in a single PDF document somehow deserves a limitation.

If you have users who are especially obsessed with information, you can always declare a second table with the same structure and continue to fill it.

Doing this means that MetaDataColumns will not be used for anything other than displaying user settings for them. You will need to update MetaDataValues every time a change is made, and making sure that you do not overwrite existing information can be a little painful. I would suspect that you would have to do something like selecting an entry before updating it, iterating through KeyName1 .. KeyNameN and populating the first one with no data. Alternatively, you can simply write an absolutely terrible SQL query. In any case, this will become a “fading point”.

Another option is to add an add column to MetaDataColumns , which indicates to which N the column belongs, but this restricts the user to 332 columns, not 332 to the document.

However, your choice from the database is now insanely simple:

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

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

At the end of the day, the “right” answer to your question depends on where you want to spend your time. You want it to take half a second longer to create or update a document or half a second (maybe more) to select the information in this document.

Personally, I believe that users understand that creating something takes time, but there is nothing more annoying than waiting for time to see something.

There is another, social, and not a database solution. Do not let your users create their own columns. Select the most common pieces of metadata that your users want and create them correctly in a normalized form in the database. You can create columns with the correct data type (which ultimately saves you a lot of trouble), and it will be much easier for them. I doubt that you are lucky that this will happen; but it is worth keeping in mind.

Answer 2

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 similar to

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

Answer 3

I definitely see a problem with the pseudo-code approach, where each row of data represents a separate request. When you have 1000 rows of data, you will have 1000+ queries that hit the database, your page will be very slow.

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

 var values = SELECT * FROM MetaDataValues WHERE documentid IN (SELECT id FROM DocumentsTable WHERE userid = UserId) foreach (val in values) { DataRow dr = dg.NewRow(); ... } 

Usually I do not prefer the "SELECT *" approach, it forces the database to make an additional query to fill all the columns. In your case, given that the user may be limited to the columns that they could see, SQL will in many ways bring more data than necessary. Thus, your code can be further consolidated and optimized, for example:

 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);