动态创建的GridView [英] Dynamically Creating GridView
问题描述
我要创建显示记录的PDF文件的GridView。这些记录可以有元数据连接是由用户可定制的，这样他们就可以创建自己的栏目，并在那里输入自己的信息。然后，我希望它显示在一个GridView，因此，如果列的顺序是，他们可以订购的每一列和列的顺序-1它不会在GridView显示。
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.
例如有一个静态表
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
和持有值的表
MetaDataValues:
ID int
UserID int foreign key
DocumentID int foreign key
MetaDataID int foreign key
value varchar(100)
现在的问题是，我需要从MetaDataColumn拿到列创建GridView控件，然后在MetaDataValue表中的值来填充它。我原来的计划是有一个动态创建GridView和添加列到它的功能，但是我坚持就如何在MetaDataValue使用的值的列。我也可以只是在GridView自动生成列，但我需要自定义SQL显示自定义数据。我停留在如何平衡的办法这一点。
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.
一种方法我想出了是这样的伪code：
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();
}
一个我不喜欢这个设计是在文件表中每一行它创建另一个查询的事情。林不知道这是否是使用SQL的一个问题？
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?
推荐答案
您的问题，主要是由于的设计的数据库。你有，因为你已经翻译这将是一列动态添加列（在3NF ）至排在你的表。显然，这是因为你允许用户添加自己的栏目 - 我的心灵颤抖，但是这是应用程序的工作方式： - ）
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 :-).
由于结构 MetaDataColumns 我要假设一个用户定义一组列名的，他们可以再选择应用到的能力因为他们希望单个文档。
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.
我认为问题是，在试图正确地正常化的一切，在一个完全反规范化的数据库，你已经成功地使自己很多的麻烦。我的解决办法是denormalise你的表 MetaDataValues 。你不提您所使用的RDBMS，但MySQL有的 4096列或65K字节。 Oracle中的限制是 1000 和<一个href=\"http://stackoverflow.com/questions/1533857/what-is-the-max-number-of-columns-allowed-in-sql-server-2008-view\">1024在SQL Server中。
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.
如果您 MetaDataValues 的结构更改为以下，你应该能够适合的至少的332套的信息在里面。这将是对用户名分别独特， DocumentID ，所以你可以，理论上，去除代理键 ID 。
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)
当然，这并设置上，你就可以允许个人用户创建332列数的上限;但是，这是正常的限制去疯狂，谁能够想到的元数据的332位独立存储在一个单一的PDF值得以某种方式限制用户的能力。
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.
这样做将意味着 MetaDataColumns 将不会被用于任何东西，但显示用户选项给他们。你会的有无的更新 MetaDataValues 每次更改，并确保你没有过写现存已有的信息可能会有点时间的痛苦。我怀疑你必须做的是这样选择的记录更新前，通过 KeyName1 .. KeyNameN 和填充在不具有在它的任何数据的第一个。或者你可以只写一个绝对可怕的SQL查询。无论哪种方式，这将成为瓶颈。
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".
另一种选择将是一个除了列添加到 MetaDataColumns ，这表明了N列有关，但这种将用户限制为332列绝对，而不是332每文档。
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 = ?
有没有必要试图通过表动态生成1000列的select语句进行迭代。所有的信息是正确的，并有方便你。
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.
这篇关于动态创建的GridView的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
