DataRelation / DataSet Question

[corey.burnett]

Here is my situation:
I have a .NET application that queries a WebService to get the data for
the application. The WebService does not support Joins of any type
even though the data in the back end is relational. The WebService
only allows you to select data from one table at a time. So I'm trying
to come up with a way that I can get the necessary data, put it in to
DataTables that live in a DataSet and then set the necessary
DataRelations. Then I'd like to be able to bind the correct "view" of
the data to a grid. It's this very last step that has me stumped.
Here is some sample code:

Dim myDataSet as New DataSet

Dim EmployeeDataTable as DataTable = myWebService.GetData("SELECT *
FROM Employee")
myDataSet.Tables.Add(EmployeeDataTable)

Dim OfficeDataTable as DataTable = myWebService.GetData("SELECT * FROM
Office")
myDataSet.Tables.Add(OfficeDataTable)

'Now add the DataRelation
Dim myRelation as DataRelation = New DataRelation("OfficeEmployee",
OfficeDataTable.Columns("OfficeID"),
EmployeeDataTable.Columns("OfficeID"))

myDataSet.DataRelations.Add(myRelation)

myDataGrid.DataSource = ????
myDataGrid.DataBind()

I want the Grid to show all of the employees with their information
including the lookup to the Office table to get the office name. Can
this be done? Do I have to use some sort of "view" on the data set to
tell it to what fields to select from the different tables?

Thanks,
Corey

 

[Guest]

Interesting that the web service doesn't do Joins. If I have a grid that is
simply going to display data, I perform the appropriate joins in the select
statement and bind the grid to the table.

Anyway, since you can't do that, I'd try and find an implementation of the
DataGridComboBoxColumn and pull that into my project (hopefully they were
nice and only have 1 or 2 files to pull in). After that, write a routine to
set up the table style like this:

Private Sub ApplyStyle()
Dim styleEmployee As New DataGridTableStyle
Dim styleEmployeeCol As New DataGridTextBoxColumn

styleEmployeeCol = New DataGridComboBoxColumn
With DirectCast(styleEmployeeCol,
DataGridComboBoxColumn).ColumnComboBox
.DataSource = dsDataSet.Tables("Office").DefaultView
.DisplayMember = "Office"
.ValueMember = "OfficeID"
End With
styleEmployeeCol.MappingName = "OfficeID"
styleEmployeeCol.HeaderText = "Office"
styleEmployee.GridColumnStyles.Add(styleEmployeeCol)

styleEmployeeCol = New DataGridTextBoxColumn
styleEmployeeCol.MappingName = "FirstName"
styleEmployeeCol.HeaderText = "First Name"
styleEmployee.GridColumnStyles.Add(styleEmployeeCol)

grdEmployee.TableStyles.Add(styleEmployee)
End Sub

This is a simple example that assumes that you have no current style set up
in the designer or whatnot. Once you add the style, only those columns that
you set up will display on the grid - the others will remain hidden. Also,
the lookup table for the office is handled by the ComboBox column - you just
need to set the DisplayMember and ValueMember of the combobox to the ID and
Text of the Office, assuming that's how your flavor of DataGridComboBoxColumn
works. This should also display the Text instead of the ID in your grid.

Hope that works for ya.

 

[corey.burnett]

I found a way to "fake" a join in a DataSet. After the DataRelation
has been established just add a new column to the Employee table that
refers to it's Parent. Like this...

Dim myDataSet as New DataSet

Dim EmployeeDataTable as DataTable = myWebService.GetData("SELECT *
FROM Employee")
myDataSet.Tables.Add(EmployeeDataTable)

Dim OfficeDataTable as DataTable = myWebService.GetData("SELECT * FROM
Office")
myDataSet.Tables.Add(OfficeDataTable)

'Now add the DataRelation
Dim myRelation as DataRelation = New DataRelation("OfficeEmployee",
OfficeDataTable.Columns("OfficeID"),
EmployeeDataTable.Columns("OfficeID"))

myDataSet.DataRelations.Add(myRelation)

'This line of code adds a column to the Employee dataTable that
references a field in the
'Office dataTable
EmployeeDataTable.Columns.Add("OfficeName", GetType(String),
"Parent.ID")

myDataGrid.DataSource = myDataSet.Tables("Employee")
myDataGrid.DataBind()

I tested this and it works! Whew.

Corey

 

[corey.burnett]

Keith,

Thanks for that idea. I understand where you are going. Kind of a
"smoke and mirrors" approach that does get the job done. However it
seems strange to me that the DataSet allows you to set up relationships
between DataTables, but there doesn't seem to be any way to query the
related tables. There are tons of examples of showing a Parent-Child
relationship with two data grids. But that isn't what I want to do. I
want to put a number of DataTables in to a DataSet, set a bunch of
relationships and then bind the whole thing to one grid and pick and
choose which fields from which DataTables show up in the grid. Seems
reasonable.

Obviously this would all be easier if the source database supported
joins. For now that is not an option.

Thanks again for your input.

Corey

 

[Guest]

Nicely done. I haven't seen anything like that before. Well I hope my
example for the GridColumnStyles still helps you, since you seemed to talk
yourself into figuring out your other problem