System.data.dataview rowfilter on a contains string

Question

5.00/5 (1 vote)

See more:

When attempting to work with a dataview, its easy to filter on something = something. But what I want to do is filter on something contains this_string.

I have a data table with about 5000 items in it. Where in PowerShell I can filter using something like the following:

$filteredOnContainsString = $datatable.rows | ? {$_.Contains(String)}

This works, but is is super slow.

Would someone please give me an example where I can filter with contains or something similar to get the same results as above?

What I have tried:

Tied this:

$dataviewFilter = New-Object System.Data.dataview($datatable)
$dataviewFilter.RowFilter = "Name contains Del"

Result = "Exception setting "RowFilter": "Syntax error: missing operand after the 'Contains' operator."

Posted 10-Jun-19 5:01am

Chilly YahYah

Updated 10-Jun-19 6:10am

Add a Solution

2 solutions

Solution 2

I actually do not have permissions to an SQL server, so I am unsure if that is even possible for me to run "Invoke-SQLCMD". What I am doing is querying a SharePoint site using invoke-restmethod in PowerShell, as I only have Full control permissions on a site that I manage. I was successful in filling a Datatable.
Now my goal is to filter the data.
Taking your response is purly SQL based, that got me to think that the datable object can only be filtered based on SQL syntax.

So where this works for powershell: $filteredOnContainsString = $datatable.rows | ? {$_.Contains(String)}

The equivalent SQL query would be something like this:
Select [Table] from [Database]
where [column] like '%string%'

Taking this to my Datatable filter this actually works!!

$dataviewFilter = New-Object System.Data.dataview($datatable)
$dataviewFilter.RowFilter = "Name like '%Del%'"

SO the moral of the story is SYNTAX. IN this case the Syntax is in SQL format. My bad. Thank you for sending me in the correct direction!!!

Posted 10-Jun-19 6:10am

Chilly YahYah

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Gerry Schmitz · Accepted Answer · 2019-06-10T05:22:00

"Probably" the whole table is getting downloaded due to the ".Contains" extension.

You need a stored proc or pass-thru SQL so the query "runs on the server"

Quote:
Now, I mentioned earlier about the Invoke-SQLCMD cmdlet. This is by far the easiest way to get data in and out of SQL Server in PowerShell. Set a string variable to the query you want, and use the cmdlet. If you’re getting results back, assign the invocation of the cmdlet to a variable, and those results are returned as a PowerShell object.

In PowerShell ADO.NET is Still Your Friend – Allen White SQL Server Performance and Automation Blog[^]