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"
}
}
Filter Datagridview to same datagridview
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.
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.
- supportMIB
- Posts: 62
- Last visit: Thu Feb 29, 2024 11:17 am
Re: Filter Datagridview to same datagridview
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
$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
Re: Filter Datagridview to same datagridview
Here is how to load the data:
- $dt= New-Object System.Data.DataTable
- $SqlAdapter.Fill($dt)
- $datagridview1.DataSource = $dt
- supportMIB
- Posts: 62
- Last visit: Thu Feb 29, 2024 11:17 am
Re: Filter Datagridview to same datagridview
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').
$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').
- supportMIB
- Posts: 62
- Last visit: Thu Feb 29, 2024 11:17 am
Re: Filter Datagridview to same datagridview
$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.
In a loop I have used '[System.Windows.Forms.Application]::DoEvents()' with success, but not sure how to do something similar here.
Re: Filter Datagridview to same datagridview
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.
This is usually much faster.
- $dt = New-Object System.Data.DataTable
- $rdr = $cmd.ExecuteReader()
- $dr.Load($rdr)