Get-LocateCE.ps1

Get-LocateCE.ps1

if ($PSScriptRoot) { $location = $PSScriptRoot } else { $location = (Get-Location).Path }
$dll = "$location\System.Data.SqlServerCe.dll"

# look into SQLiteDataAdapter
if ($GetDll) {
	$wc = New-Object System.Net.WebClient
    $url = "https://netnerds.net/System.Data.SqlServerCe.dll"
    $wc.DownloadFile($url,$dll)
}

# make it write to a temp db then replace
if (!(Test-Path $dll)) { throw "System.Data.SqlServerCe.dll not found. You can isntall SQL Server CE, or automatically download the DLL using -GetDll" }

[void][Reflection.Assembly]::LoadFile($dll)
$database = "$location\locate.sdf"
$connectionString = "Data Source=$database;"

if (!(Test-Path $database)) {
	# Create database
	$engine = New-Object System.Data.SqlServerCe.SqlCeEngine $connectionString
	$engine.CreateDatabase()
	$engine.Dispose()
	
	$connection = New-Object System.Data.SqlServerCe.SqlCeConnection $connectionString
	$connection.Open()
	$command = New-Object System.Data.SqlServerCe.SqlCeCommand 
	$command.Connection = $connection

	# Create table, check if primary key is automatically unique
	$table = "CREATE TABLE [Files] ([Name] nvarchar(450) PRIMARY KEY)"
	$command.CommandText = $table
	[void]$command.ExecuteNonQuery()

	$command.Dispose()
	$connection.Close()
	$connection.Dispose()
}      

#########################################################################################
$connection = New-Object System.Data.SqlServerCe.SqlCeConnection $connectionString
$connection.Open()
$command = New-Object System.Data.SqlServerCe.SqlCeCommand 
$command.Connection = $connection

# delete table
$command.CommandText = "DROP TABLE [Files]"
[void]$command.ExecuteNonQuery()
$command.CommandText = "CREATE TABLE [Files] ([Name] nvarchar(450) PRIMARY KEY)"
[void]$command.ExecuteNonQuery()       

$transaction = $connection.BeginTransaction()
	
Function Get-Filenames ($path) {
	Set-Variable -ErrorAction SilentlyContinue -Name files
	
	# Get Directories
	try
	{
	   $files = [IO.Directory]::GetFiles($path)
	   [System.IO.DirectoryInfo]$directoryInfo = New-Object IO.DirectoryInfo($path)
	   $folders = $directoryInfo.GetDirectories() | Where-Object {$_.Name -ne "`$Recycle.Bin" -and $folder -ne "System Volume Information" }
		  
	} catch { $folders = @()}
	
	foreach($filename in $files) 
		{
			$filename = $filename.replace('\\','\')
			$filename = $filename.replace("'","''")
			$command.CommandText = "insert into files values ('$filename')"
			[void]$command.ExecuteNonQuery()
		}

	# Create list of non-indexed directories
	$exculde = @($env:APPDATA)
	$exclude += $env:LOCALAPPDATA
	$exclude += $env:ProgramData
	$exclude += $env:TMP
	$exclude += $env:TEMP
	
	try {
		foreach($folder in $folders)
		{ 
			if ($exclude -notcontains "$path$folder") { Get-Filenames("$path\$folder")}
		}
	} catch {}
	Remove-Variable -ErrorAction SilentlyContinue -Name files 
}

Measure-command { Get-Filenames("C:\") }
$transaction.Commit()

$command.Dispose()
$connection.Close()
$connection.Dispose()

<#
$connection = New-Object System.Data.SqlServerCe.SqlCeConnection $connectionString
$connection.Open()
$command = New-Object System.Data.SqlServerCe.SqlCeCommand 
$command.Connection = $connection

Measure-Command {
	$file = "SqlServerCe"
	$sql = "select name from files where name like '%$file%'"
	$command.CommandText = $sql
	$datatable = New-Object System.Data.DataTable
	$datatable.load($command.ExecuteReader())
}
$command.Dispose()
$connection.Close()
$connection.Dispose()
#>