列数未知的扁平子/父数据 [英] Flattern child/parent data with unknown number of columns
问题描述
我正在努力寻找存储和表示SQL(MySQL DB)和C#Windows窗体中的数据的最佳方法.
I'm struggling to find the best way to store and represent the data I have in SQL (MySQL DB) and C# windows form.
我的数据在映射到如下所示的类时；
My data when mapped to classes which looks like this;
public class Parent
{
public string UniqueID { get; set; } //Key
public DateTime LoadTime { get; set; }
public string Reference { get; set; }
private List<Child> Elements { get; set; }
}
public class Child
{
public int MemberCode { get; set; } //Composite key
public int ElementCode { get; set; } //Composite key
public Object Data { get; set; }
}
我的数据非常动态.因此，父记录可以具有任意数量的子记录.
My data is very dynamic. So a parent record can have any number of child records.
在子记录中，那么MemberCode和ElementCode实际上是其他表/类的外键，当执行查找时，它们会为我提供有关实际数据的详细信息.例如
In the child record then the MemberCode and ElementCode are actually foreign keys to other tables/classes, which when a look-up is performed gives me details of what the data actually is. For example
MemberCode = 1 & ElementCode = 1 means data is a Date
MemberCode = 1 & ElementCode = 3 means data is a telephone number
MemberCode = 2 & ElementCode = 11 means data is a Product Code
MemberCode = 2 & ElementCode = 12 means data is a Service Code
etc
这些有效地组合起来以指示列名是什么，并且它们总是相同的(因此，MemberCode = 1& ElementCode = 1将始终是Date，无论与之关联的是哪个子对象). 目前，这些是引用/查找，但是我也可以将数据放在类中的变量中，因为这样可能会更容易.那么它将更像是键值对".
These effectively combine to indicate what the column name is, and these are always the same (so MemberCode = 1 & ElementCode = 1 will always be a Date no matter which child object it is associated with). At the moment these are references/lookups but I could also put the data in a variable in the class as that might make it easier. Then it would be more like a Key Value Pair.
目前，在我的数据库中，我将这些存储为两个表，子记录还包含来自父记录的UniqueID.但是我不确定，这将是我所解释的最好方法.
At the moment in my DB I have these stored as two tables, with the child record also containing the UniqueID from the parent. But I'm, not sure that this is the best way as I will explain.
我的表就是这样创建的
CREATE TABLE `PARENT` (
`ID` INT(11) NOT NULL AUTO_INCREMENT,
`LOADTIME` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`REFERENCE` VARCHAR(100) NOT NULL,
PRIMARY KEY (`ID`)
)
CREATE TABLE `CHILD` (
`ID` INT(11) NOT NULL,
`MEMBER_CODE` INT(11) NOT NULL,
`ELEMENT_CODE` INT(11) NOT NULL,
`DATA` VARCHAR(4000) NULL DEFAULT NULL,
PRIMARY KEY (`ID`, `MEMBER_CODE`, `ELEMENT_CODE`),
CONSTRAINT `fk_ID` FOREIGN KEY (`ID`) REFERENCES `Parent` (`ID`)
)
现在我要做的是将这些数据展平，这样我就可以将单个子记录与所有子记录显示为一行.理想情况下，我想在ObjectListView中显示它( http://objectlistview.sourceforge.net/cs/index.html )，但可以考虑使用datagrid来简化工作.
Now what I want to do is to flatten out this data so that I can display a single parent record with all child records as a single row. I ideally want to display it in an ObjectListView (http://objectlistview.sourceforge.net/cs/index.html) but can consider datagrid if it makes life easier.
因为我的数据是动态的，所以我努力将其弄平，如果我选择10个父记录，则每个记录可以具有不同数量的子元素，并且每个记录可以具有不同的MemberCodes和ElementCode，这意味着它们是有效的不同的列.
Because my data is dynamic, then I'm struggling to flatten this out and if I select 10 parent records then each can have different number of child elements, and each can have different MemberCodes and ElementCode, which means that they are effectively different columns.
所以我的数据可能如下所示(但规模较大)；
So my data could look like the following (but on a larger scale);
但是由于数据的动态性质，所以我很难做到这一点.无论是在SQL中还是在我的代码中的对象中.也许还有另一种存储我的数据的方法会更适合它.
But because of the dynamic nature of the data, then I struggling to do this. Either in SQL or in Objects in my code. Maybe there is even another way to store my data which would suit it better.
推荐答案
经过很多天的努力，我自己设法解决了这个问题.我所做的是以下内容；
After many many days working on this then I have managed to resolve this issue myself. What I done was the following;
然后在我的原始子类中，MemberCode和ElementCode构成一个唯一键，该键基本上说明了列名是什么.因此，我将这一步骤更进一步，并添加了"Column_Name"，这样我就拥有了
In my original child class then the MemberCode and ElementCode make a unique key that basically stated what the column name was. So I took this a step further and added a "Column_Name" so that I had
public class Child
{
public int MemberCode { get; set; } //Composite key
public int ElementCode { get; set; } //Composite key
public string Column_Name { get; set; } //Unique. Alternative Key
public Object Data { get; set; }
}
这显然也反映在我的数据库表中.
This was obviously reflected in my database table as well.
我提取数据的SQL看起来像这样；
My SQL to extract the data then looked like this;
select p.UniqueID, p.LoadTime, p.reference, c.MemberCode, c.ElementCode , c.column_name, c.Data
from parent as p, child as c
where p.UniqueID = c.UniqueID
//aditional filter criteria
ORDER BY p.UniqueID, MemberCode, ElementCode
首先按UniqueID进行排序对于确保记录按正确的顺序进行后续处理至关重要.
ordering by the UniqueID first is critical to ensure the records are in the right order for later processing.
我将使用dynamic和ExpandoObject()来存储数据.
The I would use a dynamic and a ExpandoObject() to store the data.
因此，我遍历结果以将sql结果转换为以下结构；
So I iterate over the result to the convert the sql result into this structure as follows;
List<dynamic> allRecords = new List<dynamic>(); //A list of all my records
List<dynamic> singleRecord = null; //A list representing just a single record
bool first = true; //Needed for execution of the first iteration only
int lastId = 0; //id of the last unique record
foreach (DataRow row in args.GetDataSet.Tables[0].Rows)
{
int newID = Convert.ToInt32(row["UniqueID"]); //get the current record unique id
if (newID != lastId) //If new record then get header/parent information
{
if (!first)
allRecords.Add(singleRecord); //store the last record
else
first = false;
//new object
singleRecord = new List<dynamic>();
//get parent information and store it
dynamic head = new ExpandoObject();
head.Column_name = "UniqueID";
head.UDS_Data = row["UniqueID"].ToString();
singleRecord.Add(head);
head = new ExpandoObject();
head.Column_name = "LoadTime";
head.UDS_Data = row["LoadTime"].ToString();
singleRecord.Add(head);
head = new ExpandoObject();
head.Column_name = "reference";
head.UDS_Data = row["reference"].ToString();
singleRecord.Add(head);
}
//get child information and store it. One row at a time
dynamic record = new ExpandoObject();
record.Column_name = row["column_name"].ToString();
record.UDS_Data = row["data"].ToString();
singleRecord.Add(record);
lastId = newID; //store the last id
}
allRecords.Add(singleRecord); //stores the last complete record
然后，我以所需的固定方式动态存储我的信息.
Then I have my information stored dynamically in the flat manner that I required.
现在，下一个问题是我想使用的ObjectListView.这不能接受这种动态类型.
Now the next problem was the ObjectListView I wanted to use. This could not accept such dynamic types.
因此，我可以根据需要将信息存储在代码中，但是仍然无法按要求显示它.
So I had the information stored within my code as I wanted, but I could still not display it as was required.
解决方案是使用称为DataListView的ObjectListView变体.这实际上是相同的控件，但可以绑定数据.
另一种选择是使用DatagridView，但出于其他原因，我想坚持使用ObjectListView.
The solution was that was to use a variant of the ObjectListView known as the DataListView. This is effectively the same control but can be data bound.
Another alternative would also be to use a DatagridView, but I wanted to stick to the ObjectListView for other reasons.
所以现在我不得不将我的动态数据转换为数据源.我这样做如下；
So now I had to convert my dynamic data into a Datasource. This I done as follows;
DataTable dt = new DataTable();
foreach (dynamic record in allRecords)
{
DataRow dr = dt.NewRow();
foreach (dynamic item in record)
{
var prop = (IDictionary<String, Object>)item;
if (!dt.Columns.Contains(prop["Column_name"].ToString()))
{
dt.Columns.Add(new DataColumn(prop["Column_name"].ToString()));
}
dr[prop["Column_name"].ToString()] = prop["UDS_Data"];
}
dt.Rows.Add(dr);
}
然后，我只需将数据源分配给DataListView，生成列，然后嘿，现在我提取，展平并显示了我的动态数据.
Then I simply assign my datasource to the DataListView, generate the columns, and hey presto I now have my dynamic data extracted, flattened and displayed how I require.
这篇关于列数未知的扁平子/父数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
