2

I've got three DataTables that I need to join together, and use the joined data as the DataSource for a GridView. The first (localSQLTable) is populated via a query against an MS-SQL database. The second two (serviceResponse.Tables(0) and serviceResponse.Tables(1) ) are built using DataSet.ReadXML from the results of a web service.

I've gotten this far:

Dim joinedData = From f In localSQLTable _
                 Join s1 As DataRow In serviceResponse.Tables(0) _
                 On f.Item("KNum") Equals s1.Item("Number") _
                 Join s2 As DataRow In serviceResponse.Tables(1) _
                 On s1.Item("KNumber_Id") Equals s2.Item("KNumber_Id") _
                 Select Guid = f.Item("Guid"), Num = f.Item("Num"), Desc = f.Item("Desc"), KNum = f.Item("KNum"), KDesc = s2.Item("KDescription_Text"), Type = s2.Item("Type") _
                 Where (Type.ToString.ToUpper = "LONG_HTML")

myGridView.DataSource = joinedData
myGridView.DataBind()

However, it seemed joinedData is just an IEnumerable (of anonymous type). I've tried a few things, including the following:

  • Attempting to build joinedData as an IEnumerable(Of DataRow) using a lambda function (of which I am not familiar at all) to build the new DataRow
  • Calling .ToList() or .AsEnumerable() (after toying with types) on the result set

The main issue is that no matter what I seem to try, there's something wrong with using the results as the DataSource for my GridView - I got one of the two exceptions:

  • The data source for GridView with id did not have any properties or attributes from which to generate columns. Ensure that your data source has content.
  • The data source does not support server-side data paging.

I also know I probably shouldn't be using .Item ("Field") instead of the strongly-typed .Field (Of T)("Field") in my Linq query - I was waiting on that change until I've got the data actually usable.

I'm not married to Linq; if DataSet.Merge is more appropriate (or some other methodology), I'll entertain it. There's also a distinct possibility that I'll actually have to join what I have to another two DataTables later. If that's the case, I'll likely merge the serviceResponse tables into one, so I'll still only be joining three tables.

So what can I do to join this data together and use the result as my GridView's DataSource? And is anything I'm doing going to be any faster than just tacking on two extra columns in my original DataTable (localSQLTable) and filling them row-by-row using the XML response data?

abatishchev
  • 98,240
  • 88
  • 296
  • 433
Ian Pugsley
  • 1,062
  • 8
  • 20

2 Answers2

2

In your SELECT use f.Field<Guid>("Guid") instead

Example (C#)

gv.DataSource = serviceResponse.Tables[0].AsEnumerable().Select(r => new { Name = r.Field<Guid>("Guid") });
gv.DataBind();

Example (vb)

gv.DataSource = dt.AsEnumerable().Select(Function(r) New With { .Name = r.Field(Of Guid)("Guid") })
gv.DataBind()

Or disable AutoGenerateColumns on the GridView

Edit: The following query works fine

void Main()
{
    var dt1 = new DataTable();
    dt1.Columns.Add("Col1", typeof(string));

    var dt2 = new DataTable();
    dt2.Columns.Add("Col2", typeof(string));

    var row = dt1.NewRow();
    row[0] = "test";
    dt1.Rows.Add(row);

    row = dt2.NewRow();
    row[0] = "test";
    dt2.Rows.Add(row);

    var gv = new GridView();
    gv.DataSource = from t1 in dt1.AsEnumerable()
                    join t2 in dt2.AsEnumerable()
                        on t1[0] equals t2[0]
                    select new
                    {
                        Name1 = t1.Field<string>(0),
                        Name2 = t2.Field<string>(0)
                    };
    gv.DataBind();
}
Magnus
  • 45,362
  • 8
  • 80
  • 118
  • Sorry for the wait. I'd love to keep AutoGenerateColumns true - I'll try this soon and get back to you. – Ian Pugsley Apr 08 '11 at 20:26
  • Didn't work - looks like a casting issue, like I need to explicitly be selecting datarows, maybe. I'm getting the following: `Unable to cast object of type ' – Ian Pugsley Apr 11 '11 at 15:23
  • You'r adding AsEnumerable() on the tables? – Magnus Apr 11 '11 at 15:43
  • I added `.AsEnumerable()` to each of the three tables in my join above. – Ian Pugsley Apr 11 '11 at 16:57
  • It looks like the issue is that since I'm doing a Join on two DataTables, my anonymous type contains both of the datarows. The code given will work for a single source of data, but with my join I'm going to have to come up with a way to return a DataRow with the exact schema I need, that holds the combination of my data. – Ian Pugsley Apr 11 '11 at 21:13
  • Perhaps the AsDataView() extension method might help you. Add it at the end of the query. – Magnus Apr 11 '11 at 22:01
  • Looks like I need a DataTable to start off with to use `AsDataView()` - if I could get the DataTable in the first place, I'd be fine. – Ian Pugsley Apr 12 '11 at 14:37
  • The issue with your revised code is that it isn't using GridView paging. I've come up with a way to make it work, though it isn't pretty - I'll post it as an answer, but I upvoted you for all your help. – Ian Pugsley Apr 12 '11 at 15:22
0

The end result of all of this is the following:

Dim joinedData As Generic.IEnumerable(Of DataRow) = (From f In localSQLTable.AsEnumerable() _
                                                     Join h In serviceResponse.Tables(0).AsEnumerable() _
                                                     On h.Item("serviceknum") Equals f.Item("knum") _
                                                     Select GetFinalDataRow(finalTable, f, h))

gvGridOne.DataSource = joinedData.CopyToDataTable()
gvGridOne.DataBind()

I explicitly defined the DataTable and schema (by adding DataColumns) of the ending DataTable I need, then passed that DataTable and the two DataRows in my join to GetFinalDataRow(), defined as:

Public Function GetFinalDataRow(ByRef FinalTable As DataTable, ByVal Row1 As DataRow, ByVal Row2 As DataRow) As DataRow
    Dim newRow As DataRow = FinalTable.NewRow()
    For Each col As DataColumn In FinalTable.Columns
        If Row1.Table.Columns.Contains(col.ColumnName) Then
            newRow(col.ColumnName) = Row1.Item(col.ColumnName)
        ElseIf Row2.Table.Columns.Contains(col.ColumnName) Then
            newRow(col.ColumnName) = Row2.Item(col.ColumnName)
        Else
            newRow(col.ColumnName) = ""
        End If
    Next

    Return newRow
End Function

My joinedData object is now an IEnumerable of DataRows, and I can copy that to a DataTable for my GridView's datasource, and it will auto generate columns and allow paging.

Ian Pugsley
  • 1,062
  • 8
  • 20