DataView Inner Join

[randy1200]

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,
--
Randy

[Ignacio Machin \( .NET/ C# MVP \)]

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

"randy1200" <ra*******@news groups.nospam> wrote in message
news:78******** *************** ***********@mic rosoft.com...

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,
--
Randy

[chanmm]

I would suggest you to use parameterized stored procedure to retrieve the
rows to the Dataview.

chanmm

"randy1200" <ra*******@news groups.nospam> wrote in message
news:78******** *************** ***********@mic rosoft.com...

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,
--
Randy

[randy1200]

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.WriteLi ne(rowFilter);

//dv2 = new DataView(dt1, "id1 in (10, 20,)", "id1",
DataViewRowStat e.CurrentRows);
dv1 = new DataView(dt1, rowFilter, "id1",
DataViewRowStat e.CurrentRows);
dataGridView1.D ataSource = dv1;
--
Randy
"Ignacio Machin ( .NET/ C# MVP )" wrote:

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

"randy1200" <ra*******@news groups.nospam> wrote in message
news:78******** *************** ***********@mic rosoft.com...

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,
--
Randy

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,

use StringBuilder instead of String for the concatenation.
--
--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation

"randy1200" <ra*******@news groups.nospam> wrote in message
news:A5******** *************** ***********@mic rosoft.com...

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.WriteLi ne(rowFilter);

//dv2 = new DataView(dt1, "id1 in (10, 20,)", "id1",
DataViewRowStat e.CurrentRows);
dv1 = new DataView(dt1, rowFilter, "id1",
DataViewRowStat e.CurrentRows);
dataGridView1.D ataSource = dv1;
--
Randy
"Ignacio Machin ( .NET/ C# MVP )" wrote:

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

"randy1200" <ra*******@news groups.nospam> wrote in message
news:78******** *************** ***********@mic rosoft.com...

>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,
> --
> Randy

[randy1200]

Unfortunately, I don't control the web service that provides my data. A
request has been placed...
--
Randy
"chanmm" wrote:

I would suggest you to use parameterized stored procedure to retrieve the
rows to the Dataview.

chanmm

"randy1200" <ra*******@news groups.nospam> wrote in message
news:78******** *************** ***********@mic rosoft.com...

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,
--
Randy

[randy1200]

Thanks for the StringBuilder pointer. Any other pointers?
--
Randy
"Ignacio Machin ( .NET/ C# MVP )" wrote:

Hi,

use StringBuilder instead of String for the concatenation.
--
--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation

"randy1200" <ra*******@news groups.nospam> wrote in message
news:A5******** *************** ***********@mic rosoft.com...

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.WriteLi ne(rowFilter);

//dv2 = new DataView(dt1, "id1 in (10, 20,)", "id1",
DataViewRowStat e.CurrentRows);
dv1 = new DataView(dt1, rowFilter, "id1",
DataViewRowStat e.CurrentRows);
dataGridView1.D ataSource = dv1;
--
Randy
"Ignacio Machin ( .NET/ C# MVP )" wrote:

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

"randy1200" <ra*******@news groups.nospam> wrote in message
news:78******** *************** ***********@mic rosoft.com...
>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,
> --
> Randy

[Kevin Yu [MSFT]]

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.Expr ession property, you can specify
as "Parent.(Relati onName).Custome rName", 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/de...us/cpref/html/
frlrfsystemdata datacolumnclass expressiontopic .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.)