C# ASP.NET Relations vs SQL Inner Join

[katupilar]

I am writing a tool to interface with a couple of tables in SQL Server 2000.

I usually write my queries with an Inner Join to bring in fields from seperate tables and load that to a DataSet. I'm wondering if this way is more efficient then using a DataSet.Relations.Add approach or a combination of the two.

When should you use an Inner Join over a Relation within a dataset and vice versa?

For example is this possible:

 
SqlConnection cn = new SqlConnection("Data Source=" + myServerAddress + ";Initial Catalog=" + myDataBase + ";Integrated Security=SSPI");
 
SqlCommand cmdNewRules = new SqlCommand("Select rg.GroupId, rg.RuleGroup, r.RuleID, r.RuleName From RuleGroup rg INNER JOIN Rules r ON rg.GroupID = r.GroupID", cn);
 
//Create the dataset to be populated with both tables in the hiearchy.
DataSet dsRule = new DataSet();
 
SqlDataAdapter adpRules = new SqlDataAdapter(cmdNewRules);
//Here I don't really want to Fill the dataset twice nor do I need to. 
//But I don't know how to create a DataTable object from a DataSet 
//when the DataSet has been populated by a query using an Inner Join
adpRules.Fill(dsRule,"Rules");
adpRules.Fill(dsRule,"RuleGroup");
 
 

I always see only this example and if I am querying two tables and have the ability to join them at the database level why wouldn't I just do it there?

//Create connection and sql commands.
SqlConnection cn = new SqlConnection("Data Source=" + myServerAddress + ";Initial Catalog=" + myDataBase + ";Integrated Security=SSPI");
SqlCommand cmdRules = new SqlCommand("Select RuleID, RuleName, GroupID From Rules", cn);
SqlCommand cmdRuleGroup = new SqlCommand("Select GroupId, RuleGroup From RuleGroup", cn);
 
//Create the dataset to be populated with both tables in the hiearchy.
DataSet dsRule = new DataSet();
SqlDataAdapter adpRules = new SqlDataAdapter(cmdRules);
SqlDataAdapter adpRuleGroup = new SqlDataAdapter(cmdRuleGroup);