Can I display data from different tables in same dataGrid view?

  • Thread starter Thread starter mdb
  • Start date Start date
M

mdb

I have an interest in minimizing the amount of data transfer between my
SQL server and my client in a networked environement. I have a table
with a large amount of data that has foreign key values pointing into
other tables that I need data from. The data from these child tables
are mostly string values that would be repeated many many times in the
resulting dataset if I just do a join and pull the value directly.

Here's an example:

MainTable:

keyId fkA fkB fkC fkD
0 0 1 1 0
1 0 0 2 1
2 4 2 9 3
....
....
2101 3 2 1 3


And my foreign key tables might look like:

TableA:

keyA Value
0 'This is the value for key 0'
1 'This is the value for key 1'
2 'This is the value for key 2'
3 'This is the value for key 3'
4 'This is the value for key 4'

.... and so on for the other foreign key tables (B, C, and D.)

In my client, I would like to end up with a result that shows the keyId,
and the Values for each of the foreign keys. I could easily do this
with a join, but then the values get transferred over and over for each
row, and that seems like an incredible waste of bandwidth. So instead,
I want to just transfer the data once, like so:

CREATE PROCEDURE GetValues
SELECT keyId,fkA,fkB,fkC,fkD FROM MainTable
SELECT * FROM TableA WHERE keyA IN (SELECT DISTINCT fkA FROM MainTable)
SELECT * FROM TableB WHERE keyB IN (SELECT DISTINCT fkB FROM MainTable)
SELECT * FROM TableC WHERE keyC IN (SELECT DISTINCT fkC FROM MainTable)
SELECT * FROM TableD WHERE keyD IN (SELECT DISTINCT fkD FROM MainTable)

and I execute this stored procedure as the source for a DataSet. This
results in the 'Tables' collection for my dataset having 5 entries. I
then create a relation on these tables programmatically:

DataSet ds = new DataSet();
ds.DataSource = (...the result of the EXEC GetValues...)
DataColumn parent, child;
child = ds.Tables[1].Columns["keyA"];
parent = ds.Tables[0].Columns["fkA"];
ds.Relations.Add(parent, child);
child = ds.Tables[1].Columns["keyB"];
parent = ds.Tables[0].Columns["fkB"];
ds.Relations.Add(parent, child);
child = ds.Tables[1].Columns["keyC"];
parent = ds.Tables[0].Columns["fkC"];
ds.Relations.Add(parent, child);
child = ds.Tables[1].Columns["keyD"];
parent = ds.Tables[0].Columns["fkD"];
ds.Relations.Add(parent, child);

So now, in my opinion, the datagrid should know how to display the
keyId, and each of the values, assuming I can configure it correctly.
This is the main question of this post - is this possible??

I've tried creating a TableStyle with appropriate DataGridColumnStyles
setting the 'MappingName' parameters to the DataColumn.ColumnName's of
the columns I wanted to display, but that didn't work too well. For
example,

DataGridTableStyle ts = new DataGridTableStyle();
DataGridColumnStyle cs;
cs = new DataGridTextBoxColumn();
cs.MappingName = ds.Tables[0].Columns["keyId"].ColumnName;
ts.GridColumnStyles.Add(cs);
cs = new DataGridTextBoxColumn();
cs.MappingName = ds.Tables[1].Columns["Value"].ColumnName;
ts.GridColumnStyles.Add(cs);
cs.MappingName = ds.Tables[2].Columns["Value"].ColumnName;
ts.GridColumnStyles.Add(cs);

ts.MappingName = ds.Tables[0].TableName;

dataGrid1.TableStyles.Clear();
dataGrid1.TableStyles.Add(ts);

dataGrid1.DataSource = ds;


Does all this make sense??? Is there a way to do what a I want to do,
or am I going to have to build up a ListView with the items I want?
 
Hi,

Next KB could help you with this

http://support.microsoft.com/kb/308052/

--
Val Mazur
Microsoft MVP

http://xport.mvps.org



mdb said:
I have an interest in minimizing the amount of data transfer between my
SQL server and my client in a networked environement. I have a table
with a large amount of data that has foreign key values pointing into
other tables that I need data from. The data from these child tables
are mostly string values that would be repeated many many times in the
resulting dataset if I just do a join and pull the value directly.

Here's an example:

MainTable:

keyId fkA fkB fkC fkD
0 0 1 1 0
1 0 0 2 1
2 4 2 9 3
...
...
2101 3 2 1 3


And my foreign key tables might look like:

TableA:

keyA Value
0 'This is the value for key 0'
1 'This is the value for key 1'
2 'This is the value for key 2'
3 'This is the value for key 3'
4 'This is the value for key 4'

... and so on for the other foreign key tables (B, C, and D.)

In my client, I would like to end up with a result that shows the keyId,
and the Values for each of the foreign keys. I could easily do this
with a join, but then the values get transferred over and over for each
row, and that seems like an incredible waste of bandwidth. So instead,
I want to just transfer the data once, like so:

CREATE PROCEDURE GetValues
SELECT keyId,fkA,fkB,fkC,fkD FROM MainTable
SELECT * FROM TableA WHERE keyA IN (SELECT DISTINCT fkA FROM MainTable)
SELECT * FROM TableB WHERE keyB IN (SELECT DISTINCT fkB FROM MainTable)
SELECT * FROM TableC WHERE keyC IN (SELECT DISTINCT fkC FROM MainTable)
SELECT * FROM TableD WHERE keyD IN (SELECT DISTINCT fkD FROM MainTable)

and I execute this stored procedure as the source for a DataSet. This
results in the 'Tables' collection for my dataset having 5 entries. I
then create a relation on these tables programmatically:

DataSet ds = new DataSet();
ds.DataSource = (...the result of the EXEC GetValues...)
DataColumn parent, child;
child = ds.Tables[1].Columns["keyA"];
parent = ds.Tables[0].Columns["fkA"];
ds.Relations.Add(parent, child);
child = ds.Tables[1].Columns["keyB"];
parent = ds.Tables[0].Columns["fkB"];
ds.Relations.Add(parent, child);
child = ds.Tables[1].Columns["keyC"];
parent = ds.Tables[0].Columns["fkC"];
ds.Relations.Add(parent, child);
child = ds.Tables[1].Columns["keyD"];
parent = ds.Tables[0].Columns["fkD"];
ds.Relations.Add(parent, child);

So now, in my opinion, the datagrid should know how to display the
keyId, and each of the values, assuming I can configure it correctly.
This is the main question of this post - is this possible??

I've tried creating a TableStyle with appropriate DataGridColumnStyles
setting the 'MappingName' parameters to the DataColumn.ColumnName's of
the columns I wanted to display, but that didn't work too well. For
example,

DataGridTableStyle ts = new DataGridTableStyle();
DataGridColumnStyle cs;
cs = new DataGridTextBoxColumn();
cs.MappingName = ds.Tables[0].Columns["keyId"].ColumnName;
ts.GridColumnStyles.Add(cs);
cs = new DataGridTextBoxColumn();
cs.MappingName = ds.Tables[1].Columns["Value"].ColumnName;
ts.GridColumnStyles.Add(cs);
cs.MappingName = ds.Tables[2].Columns["Value"].ColumnName;
ts.GridColumnStyles.Add(cs);

ts.MappingName = ds.Tables[0].TableName;

dataGrid1.TableStyles.Clear();
dataGrid1.TableStyles.Add(ts);

dataGrid1.DataSource = ds;


Does all this make sense??? Is there a way to do what a I want to do,
or am I going to have to build up a ListView with the items I want?
Click to expand...
 
Hi,

Next KB could help you with this

http://support.microsoft.com/kb/308052/
Click to expand...

Doesn't help at all..

First of all, that article seems to mostly talk about how to provide your
own controls (First, Next, Previous, Last) for controlling databound
controls (hence use of CurrencyManager, which I didn't know about, so the
article completely useless to me) but this doesn't help my problem at all.

Seconds, all that article does is show fields from ONE table... why are
they even loading the Orders table? Even worse, why would they create a
relation to data that they never use? I need to display fields from two
different tables.

-mdb
 
Hi,

Next KB could help you with this

http://support.microsoft.com/kb/308052/
Click to expand...

Let me rephrase my previous response.... I jumped a bit too quickly when I
said they didn't use Orders table (I hadn't actually tried the example and
I missed the datagrid - it was so innocuous).

But they still aren't doing quite what I want... they are databinding
text fields from one database, and what I want to end up with is a SINGLE
datagrid that shows fields from both tables... in other words, something
that might look like this IN A DATAGRID:

OrderID Customer Contact Name
-------- -------------- ------------------------
10643 ALFKI Maria Anders
10692 ALFKI Maria Anders
10308 ANATR Ana Trujillo

Where OrderID comes from 'Orders' table and 'Customer' and 'Contact Name'
come from 'Customers' table. In their example, the datagrid ONLY shows
data from the 'Orders' table.

-mdb
 
Mdb.

Of course you can do that, (assuming that they have the same key).

Just use datarelations, that is very easy to do. (A dataset contains
datatables and datarelations).

\\\
customerAdapter.Fill(customerDataSet, "Customers")
orderAdapter.Fill(customerDataSet, "Orders")
orderDetailsAdapter.Fill(customerDataSet, "OrderDetails")

' Create relationships.
customerDataSet.Relations.Add("CustomerOrders", _
customerDataSet.Tables("Customers").Columns("CustomerID"), _
customerDataSet.Tables("Orders").Columns("CustomerID"))

customerDataSet.Relations.Add("OrderDetails", _
customerDataSet.Tables("Orders").Columns("OrderID"), _
customerDataSet.Tables("OrderDetails").Columns("OrderID"))
///


I hope this helps,

Cor
 
Mdb.

Of course you can do that, (assuming that they have the same key).

Just use datarelations, that is very easy to do. (A dataset contains
datatables and datarelations).
Click to expand...


Yeah... I've got that part. Now the question is, can I display values from
the different tables in ONE row in a datagrid? That's what I'm having
trouble doing.

So using the Northwind example, I would want to see something like the
following in a datagrid:

OrderID Customer Contact Name
-------- -------------- ------------------------
10643 ALFKI Maria Anders
10692 ALFKI Maria Anders
10308 ANATR Ana Trujillo

-mdb
 
Mdb,

When you want them in one row you have to use the join, or to create an
extra table (or add data using the relations to extra created columns using
the expression with the relation in the child and display that).

Keep in mind that you cannot update that created (or joined) table in a
straight way.
You would have to pull the data out that again.

I hope this helps,

Cor
 
When you want them in one row you have to use the join, or to create
an extra table (or add data using the relations to extra created
columns using the expression with the relation in the child and
display that).
Click to expand...

Ok so I think I understand that the answer is simply 'no' unless I want to
do extra work. That answers my question - thanks much!

-mdb
 
Back
Top