Question on DataTable join

  • Thread starter Thread starter Peter
  • Start date Start date
P

Peter

Hi, I have two datatables and I want to efficiently query the
intersection of the two using the studentID. In my example code below,
I want to print out Mary and Jane and their exam grades and I don't
want to use loop because the real datatables are much larger. Can this
be done using one of the inbuilt methods in the class library and if
so how.
Thank you in advance
Peter

DataTable examgrade;
DataTable students;

private void addExamgrade(int studentID, string grade)
{
DataRow dr=examgrade.NewRow();
dr["STUDENTID"]=studentID;
dr["GRADE"]=grade;
}

private void addstudent(int studentID, string studentname,int
yearenrolled)
{
DataRow dr=students.NewRow();
dr["STUDENTID"]=studentID;
dr["STUDENTNAME"]=studentname;
dr["YEARENROLLED"]=yearenrolled;
}

private void button1_Click(object sender, System.EventArgs e)
{
examgrade=new DataTable();
examgrade.Columns.Add("STUDENTID",typeof(int));
examgrade.Columns.Add("GRADE",typeof(string));
addExamgrade(101,"A");
addExamgrade(102,"B-");
addExamgrade(500,"C");
addExamgrade(150,"FAIL");

students=new DataTable();
students.Columns.Add("STUDENTID",typeof(int));
students.Columns.Add("STUDENTNAME",typeof(string));
students.Columns.Add("YEARENROLLED",typeof(int));
addstudent(400,"Fred",1982);
addstudent(300,"John",1975);
addstudent(100,"Mark",2003);
addstudent(500,"Mary",1960);
addstudent(150,"Jane",2002);

//hardcoded answer
Console.WriteLine("The students and their grades are");
Console.WriteLine("Mary C");
Console.WriteLine("Jane FAIL");
}
 
hi
you can use the Find and Select method of the databale object.
these are two built in function of the datatable object . the select
method is rather simple .it takes as input a string "SQL query" and return
an array of dataraws
DataRow[] result = tableName.Select("name = 'whatever' ");
the find method returns only one datarow , this is because it search with
the primary key of the table . therefore, to use this method you need to
define a primary key to the table you are searching into . to define a
primary key set the PrimaryKey property of the datatable to an ARRAY of
datacoumns
mytable.PrimaryKey = new DataColumn[]{mytable.Columns["index or
name of the primary key column"].
then you can use find to get the row that has this specific value " the one
you are looking for " on its primary key feild
DataRow result = mytable.Find("some value for the primary key");

hope that was clear , if not you can read more about these two methods on
the msdn online or you can post your inquries .
Mohamed Mahfouz
MEA Developer Support Center
ITworx on behalf of Microsoft EMEA GTSC
 
Hi there - yes this is clear.

But my friends at school are talking about creating a "DataRelation"
and just doing one query. I don't understand how I can do ONE query on
a "DataSet" containing two tables.

Here is my modified code (another whole day writing this small code).
Thank you again
Peter

private void button1_Click(object sender, System.EventArgs e)
{
examgrade=new DataTable();
examgrade.Columns.Add("STUDENTID",typeof(int));
examgrade.Columns.Add("GRADE",typeof(string));
addExamgrade(101,"A");
addExamgrade(102,"B-");
addExamgrade(500,"C");
addExamgrade(150,"FAIL");

students=new DataTable();
students.Columns.Add("STUDENTID",typeof(int));
students.Columns.Add("STUDENTNAME",typeof(string));
students.Columns.Add("YEARENROLLED",typeof(int));
addstudent(400,"Fred",1982);
addstudent(300,"John",1975);
addstudent(100,"Mark",2003);
addstudent(500,"Mary",1960);
addstudent(150,"Jane",2002);

DataSet ds=new DataSet();
ds.Tables.Add(examgrade);
ds.Tables.Add(students);

DataColumn dcParentStudentID=ds.Tables[0].Columns["STUDENTID"];
DataColumn dcParentGrade=ds.Tables[0].Columns["GRADE"];

DataColumn dcChildStudentID=ds.Tables[1].Columns["STUDENTID"];
DataColumn dcChildStudentName=ds.Tables[1].Columns["STUDENTNAME"];
DataColumn dcChildYearEnrolled=ds.Tables[1].Columns["YEARENROLLED"];

DataRelation dr=new DataRelation("join",new
DataColumn[]{dcParentStudentID},new
DataColumn[]{dcChildStudentID},false);
ds.Relations.Add(dr);

//Now what?
//how to I query the "ds" to get only
//Mary & Jane and C and FAIL in a textbox or dropdownlists
}


(e-mail address removed) (Mohamoss) wrote in message
hi
you can use the Find and Select method of the databale object.
these are two built in function of the datatable object . the select
method is rather simple .it takes as input a string "SQL query" and return
an array of dataraws
DataRow[] result = tableName.Select("name = 'whatever' ");
the find method returns only one datarow , this is because it search with
the primary key of the table . therefore, to use this method you need to
define a primary key to the table you are searching into . to define a
primary key set the PrimaryKey property of the datatable to an ARRAY of
datacoumns
mytable.PrimaryKey = new DataColumn[]{mytable.Columns["index or
name of the primary key column"].
then you can use find to get the row that has this specific value " the one
you are looking for " on its primary key feild
DataRow result = mytable.Find("some value for the primary key");

hope that was clear , if not you can read more about these two methods on
the msdn online or you can post your inquries .
Mohamed Mahfouz
MEA Developer Support Center
ITworx on behalf of Microsoft EMEA GTSC
Click to expand...
 
hi
using datarelation would be useful when you have a parent and child
realtion . like You can have a data relation between two tables that has a
primary key and foreign key relation. When you have that you can create a
date relation between the tables. And use the get child rows function
(offered when you use dataRelation object ) then you pass this function .
a value of the primary key of the first table . and it return all the rows
that has this value as a foreign key in the second table . So this can be
more compact in many cases ( if you have a situation like that ) .. hope
that helps
Mohamed Mahfouz
MEA Developer Support Center
ITworx on behalf of Microsoft EMEA GTSC
 
Back
Top