Should I use additional SQL Queries or use FindRows

[Guest]

Hi all, currently my application generates a DataSet object from a query issued to the database.

Then it uses some of the values from that DataSet object to send off further queries and so on.

However, I'm starting to wonder if it might be more efficient to use the "FindRows" method or RowFilter property on a DataView instead of issuing further queries.

Basically, I could retrieve all of the data into my DataSet and then create DataViews on that DataSet to do what I'm currently doing with a number of SQL queries.

However, the values I'm retrieving from the DataSet do not belong to any of the indices (primary key or otherwise) that I have built on the database tables. So it looks like I will have to use the "Sort" method before I can use the FindRows method.

Besides which, I don't know how to FindRows with the Null value (I'm guessing I just put Nothing as the object parameter). This is the code I've tried to use:
Dim myView As DataView
myView = myDataSet.Tables("some_table").DefaultView()

myView.Sort() = "column_in_table"
myView.FindRows(System.DBNull)

If I use the above code, I get the error:
Expecting 1 value(s) for the key being indexed, but received 0 value(s).

I'm guessing I need to be using "System.DBNull" somehow but I'm not sure how.

Or should I be using the RowFilter property:
myView.RowFilter = "column_in_table = NULL"

However, if I do it using the RowFilter I don't know how to generate the array of DataRowView objects. I need this since I'm not directly binding this data to a web control.

To summarize:
1. How should one determine whether it is more efficient to navigate data by issuing a SQL query for each step or whether it is more efficient to use the RowFilter property or FindRows method?
2. How can you use the FindRows method to find rows where an attribute/value in a column is set to NULL?
3. How can you get the array of DataRowObjects once you set the RowFilter property to the query string of your choosing?

Thanks,
Novice

 

[William Ryan eMVP]

Hi all, currently my application generates a DataSet object from a query issued to the database.

Then it uses some of the values from that DataSet object to send off further queries and so on.

However, I'm starting to wonder if it might be more efficient to use the

"FindRows" method or RowFilter property on a DataView instead of issuing
further queries.

Basically, I could retrieve all of the data into my DataSet and then

create DataViews on that DataSet to do what I'm currently doing with a
number of SQL queries.

However, the values I'm retrieving from the DataSet do not belong to any

of the indices (primary key or otherwise) that I have built on the database
tables. So it looks like I will have to use the "Sort" method before I can
use the FindRows method.

Besides which, I don't know how to FindRows with the Null value (I'm

guessing I just put Nothing as the object parameter). This is the code I've
tried to use:

Dim myView As DataView
myView = myDataSet.Tables("some_table").DefaultView()

myView.Sort() = "column_in_table"
myView.FindRows(System.DBNull)

If I use the above code, I get the error:
Expecting 1 value(s) for the key being indexed, but received 0 value(s).

I'm guessing I need to be using "System.DBNull" somehow but I'm not sure how.

Or should I be using the RowFilter property:
myView.RowFilter = "column_in_table = NULL"

However, if I do it using the RowFilter I don't know how to generate the

array of DataRowView objects. I need this since I'm not directly binding
this data to a web control.

To summarize:
1. How should one determine whether it is more efficient to navigate data

by issuing a SQL query for each step or whether it is more efficient to use
the RowFilter property or FindRows method?

It's almost a sure bet that using the local objects will be more effiicient
since you don't have network traffic to deal with, database loads and db
connections. I've written on this extensively at
http://www.knowdotnet.com/articles/dataviews1.html (there are three other
parts, II, III and IV.

2. How can you use the FindRows method to find rows where an

attribute/value in a column is set to NULL? You can use the IsNull
expression and specifiy a given value that you know isn't ever going to
actually occur it it's null, then check against that value
http://www.knowdotnet.com/articles/advancedrowfilter.html
ie "IsNull(Departure_Date, '01/01/1900') = '01/01/1900'"

3. How can you get the array of DataRowObjects once you set the RowFilter

property to the query string of your choosing?
Why not just bind to the DataView? It implements IList so you can bind to
it and it's very easy to use.

--

W.G. Ryan, eMVP

http://forums.devbuzz.com/
http://www.knowdotnet.com/williamryan.html
http://www.msmvps.com/WilliamRyan/

 

[Guest]

Thank you for your quick response - however either I don't understand your answer to my question 2 or you didn't understand my question.

I have a table that looks something like this:
first_name | last_name | phone_num |
John | Smith | 555-555-5555|
Jane | Doe | Null |

How do I use the FindRows() method to get all rows where the phone_num column value is set to Null?

I.E. What do I pass as a parameter into FindRows to do this?

Or can I not use the FindRows method to do this? Do I have to use the IsNull method instead?

Sorry, but this is all new to me,
Novice

 

[William Ryan eMVP]

You mentioned RowFilter so you cna just use it as the Expression like the
example I showed you with the isnull function. You can use it directly or
use the example to negate the IsNull

--
W.G. Ryan MVP Windows - Embedded

http://forums.devbuzz.com
http://www.knowdotnet.com/dataaccess.html
http://www.msmvps.com/williamryan/

Thank you for your quick response - however either I don't understand your

answer to my question 2 or you didn't understand my question.