FindRows in DataView - Not searching some columns

[David Wender]

I want to create a dataview with a sort on multiple columns. However,
when I use FindRows, I only want to search some of the columns, not
all. Is this possible? I have not been able to make it happen.

Dim objKeys(2) as Object

objKeys(0) = "CL"
objKeys(2) = 4000

Dim posView As DataView = New DataView(posDS.Tables("Positions"), _
"Trader = 'Dave'", "Commodity, Contract, Strike", _
DataViewRowState.CurrentRows)

Dim foundRows() As DataRowView = posView.FindRows(objKeys)

What I am trying to do is keep a filtered dataset (filter will not
change often) and subfilter on it.

I don't want to go through every row in the dataview and then compare
the columns with the subfilter criteria as I assume that this would be
much slower.
Thanks.

Dave.

[William Ryan eMVP]

http://www.knowdotnet.com/articles/adopartiii.html

--
W.G. Ryan MVP Windows - Embedded

http://forums.devbuzz.com
http://www.knowdotnet.com/dataaccess.html
http://www.msmvps.com/williamryan/
"David Wender" <da**********@yahoo.com> wrote in message
news:cb**************************@posting.google.c om...

I want to create a dataview with a sort on multiple columns. However,
when I use FindRows, I only want to search some of the columns, not
all. Is this possible? I have not been able to make it happen.

Dim objKeys(2) as Object

objKeys(0) = "CL"
objKeys(2) = 4000

Dim posView As DataView = New DataView(posDS.Tables("Positions"), _
"Trader = 'Dave'", "Commodity, Contract, Strike", _
DataViewRowState.CurrentRows)

Dim foundRows() As DataRowView = posView.FindRows(objKeys)

What I am trying to do is keep a filtered dataset (filter will not
change often) and subfilter on it.

I don't want to go through every row in the dataview and then compare
the columns with the subfilter criteria as I assume that this would be
much slower.
Thanks.

Dave.

[Jay B. Harlow [MVP - Outlook]]

David,
Have you considered using two DataViews?

Remember that a single DataTable can have any number of DataView objects
associated with it.

Dim posView As DataView = New DataView(posDS.Tables("Positions"), _
"Trader = 'Dave'", "Commodity, Contract, Strike", _
DataViewRowState.CurrentRows) Dim posView2 As DataView = New DataView(posDS.Tables("Positions"), _
"Trader = 'Dave'", "Commodity, Contract", _
DataViewRowState.CurrentRows) Dim foundRows() As DataRowView = posView2.FindRows(objKeys)
Of course multiple DataViews on a DataTable can cause performance problems,
however if the sort keys are the same, I understand that the underlying
index is shared among the Views, I'm not sure what would happen in this
case...

Hope this helps
Jay

"David Wender" <da**********@yahoo.com> wrote in message
news:cb**************************@posting.google.c om... I want to create a dataview with a sort on multiple columns. However,
when I use FindRows, I only want to search some of the columns, not
all. Is this possible? I have not been able to make it happen.

Dim objKeys(2) as Object

objKeys(0) = "CL"
objKeys(2) = 4000

Dim posView As DataView = New DataView(posDS.Tables("Positions"), _
"Trader = 'Dave'", "Commodity, Contract, Strike", _
DataViewRowState.CurrentRows)

Dim foundRows() As DataRowView = posView.FindRows(objKeys)

What I am trying to do is keep a filtered dataset (filter will not
change often) and subfilter on it.

I don't want to go through every row in the dataview and then compare
the columns with the subfilter criteria as I assume that this would be
much slower.
Thanks.

Dave.

[David Wender]

Hi Jay,
Thanks for the reply. I hadn't thought of using a second dataview on
the same table. However in this case I'm not sure it would be
appropriate. Here's some background of what I'm trying to get done.

1) I start with a large DataSet - it includes all data for a given
Group of Traders.

2) Within this large group of data, a user will typically only want to
see a particular subset most of the time. (i.e. they may want to see
all Crude Oil, Heating Oil and Gasoline trades done by traders Dave
and Mike) Therefore I rebuild this special dataview only when the user
wants to switch to a different subgroup. Hopefully this saves
resources since I rarely need to change the sort order or filter of
the dataview. Also the custom filters can get a little complex so I
don't really want to build them often.

3) Though this subgroup filter does not change often, I need to
calculate values of subsets of this data every few seconds. For
example, if a new Heating Oil price comes in, I want to recalculate
all of the Heating Oil prices in the dataview - hence the need to
filter the dataview.

4) Though I usuallly will only need to search on one column, I need
the view sorted on another column because of certain calculations that
are made only when that column changes.

Thanks again and appreciate your help.

Dave.

"Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> wrote in message news:<eR**************@TK2MSFTNGP12.phx.gbl>...

David,
Have you considered using two DataViews?

Remember that a single DataTable can have any number of DataView objects
associated with it.

Dim posView As DataView = New DataView(posDS.Tables("Positions"), _
"Trader = 'Dave'", "Commodity, Contract, Strike", _
DataViewRowState.CurrentRows)

Dim posView2 As DataView = New DataView(posDS.Tables("Positions"), _
"Trader = 'Dave'", "Commodity, Contract", _
DataViewRowState.CurrentRows)

Dim foundRows() As DataRowView = posView2.FindRows(objKeys)

Of course multiple DataViews on a DataTable can cause performance problems,
however if the sort keys are the same, I understand that the underlying
index is shared among the Views, I'm not sure what would happen in this
case...

Hope this helps
Jay

"David Wender" <da**********@yahoo.com> wrote in message
news:cb**************************@posting.google.c om...

I want to create a dataview with a sort on multiple columns. However,
when I use FindRows, I only want to search some of the columns, not
all. Is this possible? I have not been able to make it happen.

Dim objKeys(2) as Object

objKeys(0) = "CL"
objKeys(2) = 4000

Dim posView As DataView = New DataView(posDS.Tables("Positions"), _
"Trader = 'Dave'", "Commodity, Contract, Strike", _
DataViewRowState.CurrentRows)

Dim foundRows() As DataRowView = posView.FindRows(objKeys)

What I am trying to do is keep a filtered dataset (filter will not
change often) and subfilter on it.

I don't want to go through every row in the dataview and then compare
the columns with the subfilter criteria as I assume that this would be
much slower.
Thanks.

Dave.

[Jay B. Harlow [MVP - Outlook]]

David,

1) I start with a large DataSet - it includes all data for a given
Group of Traders. Sounds good.
2) Within this large group of data, a user will typically only want to
the dataview. Also the custom filters can get a little complex so I
don't really want to build them often. You can pass the same filter to both DataViews.
3) Though this subgroup filter does not change often, I need to
calculate values of subsets of this data every few seconds. Changing rows in the DataSet will change rows in all of the DataViews.
hence the need to filter the dataview. Are you processing the Dataview itself? Have you considered using
DataTable.Select instead?
4) Though I usuallly will only need to search on one column, I need
the view sorted on another column because of certain calculations that
are made only when that column changes. If you are using the View for processing you may want to consider using
DataTable.Select instead. If you are using it for Display then the DataView
is the way to go. Depending on what you are doing I would use both!

Const format As String = "Trader='{0}' And Commodity='{1}' And
Contact={2}"
Dim table As DataTable = posDS.Tables("Positions")
Dim filter As String = String.Format(format, "Dave", "CL", 4000)
Dim sort As String = "Commodity, Contract, Strike"
Dim foundRows() As DataRow = table.Select(filter, sort,
DataViewRowState.CurrentRows)

Note you get back an array of DataRow objects, not an array of DataRowView
objects.

Hope this helps
Jay

"David Wender" <da**********@yahoo.com> wrote in message
news:cb**************************@posting.google.c om... Hi Jay,
Thanks for the reply. I hadn't thought of using a second dataview on
the same table. However in this case I'm not sure it would be
appropriate. Here's some background of what I'm trying to get done.

1) I start with a large DataSet - it includes all data for a given
Group of Traders.

2) Within this large group of data, a user will typically only want to
see a particular subset most of the time. (i.e. they may want to see
all Crude Oil, Heating Oil and Gasoline trades done by traders Dave
and Mike) Therefore I rebuild this special dataview only when the user
wants to switch to a different subgroup. Hopefully this saves
resources since I rarely need to change the sort order or filter of
the dataview. Also the custom filters can get a little complex so I
don't really want to build them often.

3) Though this subgroup filter does not change often, I need to
calculate values of subsets of this data every few seconds. For
example, if a new Heating Oil price comes in, I want to recalculate
all of the Heating Oil prices in the dataview - hence the need to
filter the dataview.

4) Though I usuallly will only need to search on one column, I need
the view sorted on another column because of certain calculations that
are made only when that column changes.

Thanks again and appreciate your help.

Dave.

"Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> wrote in message

news:<eR**************@TK2MSFTNGP12.phx.gbl>...

David,
Have you considered using two DataViews?

Remember that a single DataTable can have any number of DataView objects
associated with it.

Dim posView As DataView = New DataView(posDS.Tables("Positions"), _
"Trader = 'Dave'", "Commodity, Contract, Strike", _
DataViewRowState.CurrentRows)

Dim posView2 As DataView = New DataView(posDS.Tables("Positions"), _
"Trader = 'Dave'", "Commodity, Contract", _
DataViewRowState.CurrentRows)

Dim foundRows() As DataRowView = posView2.FindRows(objKeys)

Of course multiple DataViews on a DataTable can cause performance problems, however if the sort keys are the same, I understand that the underlying
index is shared among the Views, I'm not sure what would happen in this
case...

Hope this helps
Jay

"David Wender" <da**********@yahoo.com> wrote in message
news:cb**************************@posting.google.c om...

I want to create a dataview with a sort on multiple columns. However,
when I use FindRows, I only want to search some of the columns, not
all. Is this possible? I have not been able to make it happen.

Dim objKeys(2) as Object

objKeys(0) = "CL"
objKeys(2) = 4000

Dim posView As DataView = New DataView(posDS.Tables("Positions"), _
"Trader = 'Dave'", "Commodity, Contract, Strike", _
DataViewRowState.CurrentRows)

Dim foundRows() As DataRowView = posView.FindRows(objKeys)

What I am trying to do is keep a filtered dataset (filter will not
change often) and subfilter on it.

I don't want to go through every row in the dataview and then compare
the columns with the subfilter criteria as I assume that this would be
much slower.
Thanks.

Dave.

[David Wender]

Hi Jay,

Thanks again for your help, I really appreciate it.
I never realized that you could apply a sort on a datatable.select,
that's a nice trick!
Anyway, I think I am going to stick with processing the dataview
simply because it doesn't change often and therefore doing a select
every few seconds would probably add unnecessary overhead. Basically
what happens is that if a futures price changes, I need to recalculate
theoretical position values that derive from the positions in the
dataview. The positions in the dataview don't change much, it's just
that I need to do calculations based on new values that are not in the
table/view.

It would be interesting to see if anyone has done any performance
tests regarding the datatable.select command on tables with and
without primary keys, and on dataviews.

Dave.

"Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> wrote in message news:<#E**************@TK2MSFTNGP10.phx.gbl>...

David,

1) I start with a large DataSet - it includes all data for a given
Group of Traders.

Sounds good.

2) Within this large group of data, a user will typically only want to
the dataview. Also the custom filters can get a little complex so I
don't really want to build them often.

You can pass the same filter to both DataViews.

3) Though this subgroup filter does not change often, I need to
calculate values of subsets of this data every few seconds.

Changing rows in the DataSet will change rows in all of the DataViews.

hence the need to filter the dataview.

Are you processing the Dataview itself? Have you considered using
DataTable.Select instead?

4) Though I usuallly will only need to search on one column, I need
the view sorted on another column because of certain calculations that
are made only when that column changes.

If you are using the View for processing you may want to consider using
DataTable.Select instead. If you are using it for Display then the DataView
is the way to go. Depending on what you are doing I would use both!

Const format As String = "Trader='{0}' And Commodity='{1}' And
Contact={2}"
Dim table As DataTable = posDS.Tables("Positions")
Dim filter As String = String.Format(format, "Dave", "CL", 4000)
Dim sort As String = "Commodity, Contract, Strike"
Dim foundRows() As DataRow = table.Select(filter, sort,
DataViewRowState.CurrentRows)

Note you get back an array of DataRow objects, not an array of DataRowView
objects.

Hope this helps
Jay