Is it possible to order data in a datatable by a related datatable in the dataset?

Question

I've been using ORM's for so long, I appear to have forgotten most of my basic data handling skills in dotnet :(

Is it possibly to do something like this?

        DataSet ds = new DataSet();
        var compiledConnection = new SqlConnection(cDbConnectionString);
        SqlDataAdapter daChart = new SqlDataAdapter("select * from Chart", compiledConnection);
        daChart.Fill(ds, "chart");

        if (ds.Tables["chart"].Rows.Count > 0)
        {
            var sourceConnection = new SqlConnection(sourceDbConnectionString);
            SqlDataAdapter daSource = new SqlDataAdapter("select * from source", sourceConnection);
            daSource.Fill(ds, "source");

            DataRelation chart_source = new DataRelation("dr", ds.Tables["chart"].Columns["intItemId"],
                   ds.Tables["source"].Columns["intRowId"], false);
            ds.Relations.Add(chart_source);
        }

And then use one of the columns in the table "chart" to order the data in the table "source" across the datarelation?

(Before anyone asks, these two tables are in separare instances of SqlServer on separate sites, so just pulling the data as one table is not a straightforward task. Hence this approach)

Cheers, Matt

Answer 1

Thanks for the suggestion, but I discovered you can do it with LINQ rather more easily:

            DataTable source = ds.Tables["source"];
            DataTable chart = ds.Tables["chart"];

            var joinedTable =
                from s in source.AsEnumerable()
                join c in chart.AsEnumerable()
                on s.Field<Int64>("intRowId") equals
                    c.Field<Int64>("intItemId")
                select new
                {
                    intRowId = s.Field<Int64>("intRowID"),
                    strTitle = s.Field<string>("strTitle"),
                    intWeight = c.Field<Int64>("intWeight")
                };

            var sortedTable = from j in joinedTable
                              orderby j.intWeight descending
                              select j;