如何创建DataRelation从表中的一列到另一个表中的两列

I have a DataTable called test_results with the following columns:

我有一个名为test_results的DataTable,其中包含以下列:

test_results_id
student_id
subject
score

and a DataTable called students with the column:

和一个名为学生的DataTable列:

student_id

I would like to create a DataRelation between the tables so that I can get all test_results rows where

我想在表之间创建一个DataRelation,这样我就可以获得所有test_results行

subject = 'math'

subject ='math'

student_id = x

student_id = x

where x is a student_id from students

其中x是学生的student_id

I want to do this so that I can quickly loop through students and find their math results:

我想这样做,以便我可以快速循环学生并找到他们的数学结果:

foreach (DataRow[] student in students){
    DataRow[] mathResults = student.GetChildRows( "relation_student_math_results" );
    foreach (DataRow[] mathResult in mathResults ){
        // do something
    }
}

1 个解决方案

#1

Intitialzing a DataRelation is quite simple; you can use the basic constructor. In your case, something like:

对DataRelation进行内部化非常简单;你可以使用基本的构造函数。在你的情况下,像:

DataRelation studentResultsRelation = new DataRelation("StudentResults", students.Columns["student_id"], test_results.Columns["student_id"]);

parentDataSet.Relations.Add(studentResultsRelation);

Where parentDataSet is the dataset containing both your datables.

其中parentDataSet是包含两个数据的数据集。

However, this is where it gets a bit tricky. You can't directly query the data relation, as it only defines the relationship between the two tables. What you can do is something like:

但是,这是一个有点棘手的地方。您不能直接查询数据关系,因为它只定义了两个表之间的关系。你能做的是:

1) Find the row that matches the student you want:

1)找到与您想要的学生匹配的行:

int studentId = 42;

DataRow[] studentRow = students.Select(String.Format("student_id = {0}"), studentId);

2) You can then take advantage of the DataRelation to retrieve all of that students results:

2)然后,您可以利用DataRelation检索所有学生的结果:

//Assumes 1 student row matched your query! Check for 0 rows and more than 1, too!
DataRow[] studentResults = studentRow[0].GetChildRows(studentResultsRelation);

You can then loop around these to find the math results:

然后你可以循环这些来找到数学结果:

List<DataRow> mathResults = new List<DataRow>();

foreach(DataRow resultRow in studentResults){
  if(Convert.ToString(resultRow["subject"]).Trim().ToUpper() == "MATH")
  {
    mathResults.Add(resultRow);
  } 
}

I can see you already had most of this, and I understand what you wanted to do with the data relation; however, I don't believe you can use it directly - instead, you first have to find the rows you want in the child (GetParentRow[s]) or parent table (GetChildRow[s]), and the relation then allows you to quickly find the matching set of rows. You can then filter these as desired.

我可以看到你已经掌握了大部分内容,而且我理解你想要对数据关系做些什么;但是,我不相信你可以直接使用它 - 相反,你首先必须在子(GetParentRow [s])或父表(GetChildRow [s])中找到你想要的行,然后关系允许你快速找到匹配的行集。然后,您可以根据需要过滤这些。

As an aside, this is a much simpler exercise with a database query.

另外,这是一个使用数据库查询的更简单的练习。

Hope this helps!

希望这可以帮助!

#1