Overflowing a DataView's RowFilter

M

Marty McFly

Greetings,

I'm trying to let my users dynamically filter records from a table that
relate to other tables.

RELATIONSHIPS:
[CustomersTable].[CustomerID] = [OrdersTable].[CustomerID]
[SalesRepsTable].[SalesRepID] = [OrdersTable].[SalesRepID]

There is a Many-to-Many relationship between CustomersTable and
SalesRepsTable.

On my Windows form, I basically dump all the SQL Server column names as text
boxes and check boxes to give users the ability to filter and sort on
anything they wish.

However, when I establish the relationship between Customers and SalesReps,
the RowFilter property of the dvCustomers DataView can get quite lengthy. I
tested the actual RowFilter statement, and it checks out. It only errors
off when the RowFilter is applied. An example:

dvCustomers.RowFilter = "CustomerID=32 OR CustomerID=434 OR
CustomerID=4955..."

The largest RowFilter I've successfully set so far contained 231 CustomerID
values. The next largest I tried (384 CustomerIDs) failed with the
following error: "An unhandled exception of type
'System.StackOverflowException' occurred in system.windows.forms.dll"

Because this is a M:M relationship, there is no way to get around the length
of the RowFilter string. For each value, I have to query the OrdersTable
and SalesRepsTable just to get the CustomerID. Does anyone have any ideas
on how to get this to work? Also, is the RowFilter limited by length,
number of parameters, or something completely different?

Many thanks,

Marty McFly
 
M

Marty McFly

Hi Cor,

Thank you for the reply.
You can have a many to many relation, however when you want to let the user
select than there is in my opinion a one to many relation.
Click to expand...

I agree. That is how I set up the RowFilter. When the user specifies
column values for a foreign, I query the database to get the primary keys.
Example:

1. The user wants to view all customers that purchased items from a sales
representative named Johnson.
The user then enters "Johnson" into the textbox that references
[SalesRepsTable].[LastName] and clicks the "Filter Customers" button.

2. In the btnFilterCustomers click event, I create a T-SQL SELECT statement:

\\\
SELECT ct.CustomerID
FROM CustomersTable ct
INNER JOIN OrdersTable ot
ON ct.CustomerID = ot.CustomerID
INNER JOIN SalesRepsTable st
ON st.SalesRepID = ot.SalesRepID
WHERE st.LastName = 'Johnson'
///

3. I query the database to generate a RowFilter string, using code like:

\\\
Dim filter As String = ""
Dim dr As SqlDataReader = cmd.ExecuteReader
While dr.Read()
filter &= "CustomerID=" & dr("CustomerID") & " OR "
End While
If filter.EndsWith("OR ") Then _
filter = filter.Substring(0, filter.Length - 3)
///
The resulting filter:
filter = "CustomerID=123 OR CustomerID=321 OR CustomerID=1234 "

(As I mentioned in my original post, this works fine, with the exception
of overflowing the RowFilter for larger resultant sets.)

4. I then set the RowFilter:

dvCustomers.RowFilter = filter

Is there a better way to do this?

Why are you not using the normal datarelation?
Click to expand...

Do you mean that there is a way to use relation objects, so that other
tables can be referenced more succinctly in a RowFilter? I guess I was
under the impression that relations among data tables are usually just used
for data integrity (cascading updates/deletes, etc.).

Thanks again, Cor. Much appreciated.

Marty
 
G

Greg Burns

The resulting filter:
filter = "CustomerID=123 OR CustomerID=321 OR CustomerID=1234 "
Click to expand...

If all the same column, try using IN keyword. (I think this works in a
filter.)
filter = "CustomerID IN(123,321,1234)"
 
M

Marty McFly

Hi Greg,

You're right. IN works perfectly. Do you know of a resource that lists all
the acceptable RowFilter syntax? I couldn't find anything on MSDN.

Thank you very much, Greg. Brilliantly simple.

Marty
 
G

Greg Burns

I am not sure of any specific resources either. I think anything valid in a
WHERE clause in SQL works.

Noticed in your code your are doing...
While dr.Read()
filter &= "CustomerID=" & dr("CustomerID") & " OR "
End While

This is horribly inefficient when dealing with strings. Check out
stringbuilder class.

Try something akin to:

Dim sb as new system.text.stringbuilder

sb.append("Customer ID IN (")
While dr.Read()
sb.append(dr("CustomerID").ToString)
sb.append(",")
End While
sb.remove(sb.length-1,1)
sb.append(")")
filter = sb.ToString


Are you using SQL server or MSDE? I would think about redesigning this
using a stored procedure and a temporary table.

Greg
 
M

Marty McFly

This is horribly inefficient when dealing with strings. Check out
stringbuilder class.
Click to expand...
I've never used the StringBuilder class...I'll give it a try.
Are you using SQL server or MSDE? I would think about redesigning this
using a stored procedure and a temporary table.
Click to expand...
I'm using SQL Server. I decided to build the string in VB just to save
resources on the server. (We have quite a few databases on the server. The
less processing, the better. And the workstations are fairly high-end, so
it's not a problem to offload processing to the clients.) Thanks for the
suggestion, though. Under normal circumstances, I would go your route.

Thanks again, Greg. You know your stuff. :)

Marty
 
J

Jay B. Harlow [MVP - Outlook]

Marty,
The syntax that ADO.NET supports for Expressions (such as those passed to
Select & Compute, and DataView.RowFilter) is documented under
DataColumn.Expression.

http://msdn.microsoft.com/library/d...fSystemDataDataColumnClassExpressionTopic.asp

If you define a DataRelation between two tables you can use
DataRow.GetChildRows to get the child rows for a parent DataRow or use
GetParentRow & GetParentRows to get the parents for the children. When you
data bind you can bind to the DataRelation object so you can have the parent
rows in one datagrid, plus the child rows for the selected records in a
second datagrid.

Rather then building the RowFilter directly, I would consider defining extra
"work" DataTables that demoralizes enough of the data to simplify the look
ups. Of course with an eye on the size these DataTables become.
Alternatively I would leave the data on the SQL Server and call stored
procedures and let it return the "selected" data.

Hope this helps
Jay
 
Top