RowFilter in presense of DataRelations

We know the role of DataRelation class in .NET − It helps create parent-child relationship between tables in a dataset. We also know that it is an an association which can be formed by declaring primary and foreign key columns while creating the relationship. I have seen many examples of using this class, specially the one in this article on MSDN:

DataRelation custOrderRel = custDS.Relations.Add("CustOrders",
           custDS.Tables["Customers"].Columns["CustomerID"],
           custDS.Tables["Orders"].Columns["CustomerID"]);
foreach (DataRow custRow in custDS.Tables["Customers"].Rows)
{
Console.WriteLine(custRow["CustomerID"]);
foreach (DataRow orderRow in custRow.GetChildRows(custOrderRel))
  Console.WriteLine(orderRow["OrderID"]);
}

The above code is not doing anything but creating a relationship "CustOrders" and looping through parent and child rows to dump their contents to console. The GetChildRows() method works pretty well as long as no explicit filter is applied on any of the related tables in the dataset. Let's assume that the Customers table contains 3 customers and Orders table stores some orders of these customers. In the above mentioned situation, when no filter is applied, the output may look like:

    

Customer ID	Order ID
1	101
1	102
2	201
2	202
2	203
3	301
3	302

However, consider the situation where we might only be interested in list of orders that belong to CustomersID = 1, CreateChildView() is the method to go with. This method helps get rows from the child table that are associated with the filtered rows from the parent table.

DataRelation custOrderRel = custDS.Relations.Add("CustOrders",
           custDS.Tables["Customers"].Columns["CustomerID"],
           custDS.Tables["Orders"].Columns["CustomerID"]);

DataView dv = custDS.Tables["Customers"].DefaultView;
dv.RowFilter = "CustomerID = 1";

foreach (DataRowView drv in dv)
{
Console.WriteLine(drv["CustomerID"]);
for(DataRowView orderRow in drv.CreateChildView(custOrderRel))
  Console.WriteLine(orderRow["OrderID"]);
}

Customer ID	Order ID
1	101
1	102

 

Sort By Column Value

We do sorting by column names almost everyday while writing database queries. Below is one such simple query

SELECT customerid, employeeid, orderdate  FROM dbo.orders  ORDER BY customerid

But, today I faced a situation where I had to write a query that returns all the rows however some of the rows appeared on the top in the list if they match a specified criteria. Lets take the example of above query where we want to get a list of all the customers but customers having customerid 'VINET' should appear on top. I came up with the below query:

DECLARE @customerid AS VARCHAR(100)  SET @customerid='VINET'  SELECT customerid, employeeid, orderdate,  (CASE @customerid WHEN '' THEN  null WHEN customerid THEN customerid END) AS 'sortColumn'  FROM orders  ORDER BY sortColumn DESC, employeeid

In the above query, 'sortColumn'  is a temporary column that is being used just to sort the whole result set by its value. 

This might not be a perfect solution as I am not a SQL expert but, I am content with it for the time being as long as it servers the purpose. If you think there is a much better way of doing the same thing, then you are more than welcome to share it here.