DataView Inner Join

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an orders table. Each record in the orders table contains a customer id.

I have a customer table. The primary key of each record in the customer
table is the customer id.

After getting a subset from the orders table, I need to take the customer
ids in the orders table subset, and list each record in the customer table
that has a matching customer id.

If I were in t-sql, this would be an easy inner join. In this case, I get
both complete tables in memory, and can only show the subset tables as
DataViews (or something like a DataView). I can't toss the original tables as
the user can request different views.

Any thoughts on creating a DataView in an inner join sort of way?

Thanks,
 
Hi,

Unfortunatelly you cannot do it, You have several options though.

You can create a DataView and using the RowFilter property , you can use
"in" : new DataView(....., "clientID in ( 1,2,3,4 ) );
 
I would suggest you to use parameterized stored procedure to retrieve the
rows to the Dataview.

chanmm
 
Thanks. Response times seem okay up to 1000 records in each table. At 10,000
records, response times take about one minute. The time get consumed by
looping through the DataRows from the second table to be put in the "in"
clause for the first table. Pointers to any other options would be
appreciated.

DataRow[] drs = dt2.Select("fk>2000");

string rowFilter = "id1 in (";

foreach (DataRow dr in drs)
{
rowFilter += dr["fk"].ToString() + ",";
}
//Note, the extra comma at the end doesn't matter
rowFilter += ")";

Console.WriteLine(rowFilter);

//dv2 = new DataView(dt1, "id1 in (10, 20,)", "id1",
DataViewRowState.CurrentRows);
dv1 = new DataView(dt1, rowFilter, "id1",
DataViewRowState.CurrentRows);
dataGridView1.DataSource = dv1;
 
Hi,

use StringBuilder instead of String for the concatenation.


--
--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation

randy1200 said:
Thanks. Response times seem okay up to 1000 records in each table. At
10,000
records, response times take about one minute. The time get consumed by
looping through the DataRows from the second table to be put in the "in"
clause for the first table. Pointers to any other options would be
appreciated.

DataRow[] drs = dt2.Select("fk>2000");

string rowFilter = "id1 in (";

foreach (DataRow dr in drs)
{
rowFilter += dr["fk"].ToString() + ",";
}
//Note, the extra comma at the end doesn't matter
rowFilter += ")";

Console.WriteLine(rowFilter);

//dv2 = new DataView(dt1, "id1 in (10, 20,)", "id1",
DataViewRowState.CurrentRows);
dv1 = new DataView(dt1, rowFilter, "id1",
DataViewRowState.CurrentRows);
dataGridView1.DataSource = dv1;
--
Randy


Ignacio Machin ( .NET/ C# MVP ) said:
Hi,

Unfortunatelly you cannot do it, You have several options though.

You can create a DataView and using the RowFilter property , you can use
"in" : new DataView(....., "clientID in ( 1,2,3,4 ) );




--
 
Unfortunately, I don't control the web service that provides my data. A
request has been placed...
 
Thanks for the StringBuilder pointer. Any other pointers?
--
Randy


Ignacio Machin ( .NET/ C# MVP ) said:
Hi,

use StringBuilder instead of String for the concatenation.


--
--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation

randy1200 said:
Thanks. Response times seem okay up to 1000 records in each table. At
10,000
records, response times take about one minute. The time get consumed by
looping through the DataRows from the second table to be put in the "in"
clause for the first table. Pointers to any other options would be
appreciated.

DataRow[] drs = dt2.Select("fk>2000");

string rowFilter = "id1 in (";

foreach (DataRow dr in drs)
{
rowFilter += dr["fk"].ToString() + ",";
}
//Note, the extra comma at the end doesn't matter
rowFilter += ")";

Console.WriteLine(rowFilter);

//dv2 = new DataView(dt1, "id1 in (10, 20,)", "id1",
DataViewRowState.CurrentRows);
dv1 = new DataView(dt1, rowFilter, "id1",
DataViewRowState.CurrentRows);
dataGridView1.DataSource = dv1;
--
Randy


Ignacio Machin ( .NET/ C# MVP ) said:
Hi,

Unfortunatelly you cannot do it, You have several options though.

You can create a DataView and using the RowFilter property , you can use
"in" : new DataView(....., "clientID in ( 1,2,3,4 ) );




--
--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation



I have an orders table. Each record in the orders table contains a
customer
id.

I have a customer table. The primary key of each record in the customer
table is the customer id.

After getting a subset from the orders table, I need to take the
customer
ids in the orders table subset, and list each record in the customer
table
that has a matching customer id.

If I were in t-sql, this would be an easy inner join. In this case, I
get
both complete tables in memory, and can only show the subset tables as
DataViews (or something like a DataView). I can't toss the original
tables
as
the user can request different views.

Any thoughts on creating a DataView in an inner join sort of way?

Thanks,
 
Hi Randy,

It seems that you need to get the Customer information from the parent
table when you have an order in the child table. In this case, I suggest
you try to add some expression columns to the child table(Orders) which map
to the parent table columns. Then you will see all the values in the child
table.

First you will need to create DataRelation between the two tables in the
DataSet schema. Then in the DataColumn.Expression property, you can specify
as "Parent.(RelationName).CustomerName", to make the column display the
Customer's name.

Please check the following link for more information about the Expression
property.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/
frlrfsystemdatadatacolumnclassexpressiontopic.asp

Kevin Yu
Microsoft Online Community Support

============================================================================
==========================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
============================================================================
==========================

(This posting is provided "AS IS", with no warranties, and confers no
rights.)
 
Back
Top