Filter Datagridview to same datagridview

Ask your PowerShell-related questions, including questions on cmdlet development!
Forum rules
Do not post any licensing information in this forum.

Any code longer than three lines should be added as code using the 'Select Code' dropdown menu or attached as a file.
This topic is 7 years and 3 months old and has exceeded the time allowed for comments. Please begin a new topic or use the search feature to find a similar but newer topic.
Locked
User avatar
supportMIB
Posts: 62
Last visit: Thu Feb 29, 2024 11:17 am

Filter Datagridview to same datagridview

Post by supportMIB »

I tried using codebox, but it kept saying my data could not be presented...I must be doing something wrong..

I have a dataviewgrid that populates with a lot of data, anywhere from a few rows to a 50k or more.

This data is a lot of file paths to show share permissions, such as...

\\share\folder\subfolder\sub
\\share\folder2\subfolder\sub2
\\share\folder3\subfolder\sub3

It pulls this information from a database and takes a while, so I want to save the initial load as a variable and then query/filter further results from that variable instead of querying the database to save some time.

The querying/filtering that would be done is from a textbox that searches the directory depth such as

$textbox2.text = 2

which would only pull:
\\share\folder\subfolder\
\\share\folder2\subfolder\
\\share\folder3\subfolder\

So it would only search 2 directories deep (based on \'s, ignoring the first 2 and 1 after fileserver name)

I'm not sure how to:
1) save the initial dataviewgrid as a variable
2) When filtered from the textbox how do I repopulate the datagridview with the filtered data?

The initial data is imported using:

$listbox1_SelectedIndexChanged={

#clears dataview on re-click
$datagridview1.DataSource = $null
$datagridview1.Refresh()

$SQLServer = "fileserver"
$SQLDBName = "AD_Validation"
$selected = $listbox1.SelectedItem

if ($textbox2.Text)
{
$SqlQuery = "select Path from share_table WHERE Permission like '%$selected %' and len(path) - len(replace(path,'\','')) < (3 + $deep);"
}
else
{
$SqlQuery = "select Path from share_table WHERE Permission like '%$selected %';"
}

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True"

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd

$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)

$SqlConnection.Close()

#loads the datagridview
Load-DataGridView $datagridview1 $DataSet.Tables[0]

$countlabel.Text = "Total Shares: $($datagridview1.RowCount)"
$countlabel.Visible = $true

}

Working on the text change...but I'm unsure how to filter out ALL results pulled, save it to a variable, and then reload that $datagridview1 with the filtered results

$textbox2_TextChanged = {
$global:deep = $textbox2.Text

for ($i = 0; $i -lt $datagridview1.RowCount; $i++)
{
if (([regex]::matches($datagridview1.Rows[$i].Cells['Path'].Value, "\\")).Count -le ($deep + 3))
{
Load-DataGridView $datagridview1 "with all rows that are less than or equal to $deep"
}
}
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: Filter Datagridview to same datagridview

Post by jvierra »

To filter a DataGridView you must use a data table with the grid. Yu can set a filter on the view and it will take effect immediately.

$datagridview1.DataSource.DefaultView.RowFilter = 'Size > 12'

To clear the filter just assign it to an empty string.

It uses SQL syntax for filters (WHERE).

https://msdn.microsoft.com/en-us/librar ... .110).aspx
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: Filter Datagridview to same datagridview

Post by jvierra »

Here is how to load the data:
  1. $dt= New-Object System.Data.DataTable
  2. $SqlAdapter.Fill($dt)
  3. $datagridview1.DataSource = $dt
User avatar
supportMIB
Posts: 62
Last visit: Thu Feb 29, 2024 11:17 am

Re: Filter Datagridview to same datagridview

Post by supportMIB »

I dont see a method

$datagridview1.DataSource.DefaultView my options show 'equals, get hash code, gettype and getstring'

I tried putting this line in just incase the IDE wasnt picking it up right:

$datagridview1.DataSource.DefaultView.RowFilter = '\\server\share\folder'

Which didnt yield any results. I think this might be because I have not specified a column for the search. (in this case the column is 'Path').
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: Filter Datagridview to same datagridview

Post by jvierra »

You have to use a data table and not the load-Datagrid function.
User avatar
supportMIB
Posts: 62
Last visit: Thu Feb 29, 2024 11:17 am

Re: Filter Datagridview to same datagridview

Post by supportMIB »

$SqlAdapter.Fill($DataSet) causes about 9 seconds of delay while the datagridview is being loaded, is there a way so it doesnt say 'Not responding' in the application if the form is clicked?

In a loop I have used '[System.Windows.Forms.Application]::DoEvents()' with success, but not sure how to do something similar here.
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: Filter Datagridview to same datagridview

Post by jvierra »

No. Events are blocking. You could use a job to load the data table. For DGV do not use a dataset as it will behave differently. Use a datatable. Do not use an adapter - use a reader and load the table.
  1. $dt = New-Object System.Data.DataTable
  2. $rdr = $cmd.ExecuteReader()
  3. $dr.Load($rdr)
This is usually much faster.
This topic is 7 years and 3 months old and has exceeded the time allowed for comments. Please begin a new topic or use the search feature to find a similar but newer topic.
Locked