AxSQLServerCE.psm1

function Connect-SdfFile {
    [CmdletBinding()]
    param (
        [Parameter(Mandatory=$true)]
        [System.IO.FileInfo]$Path,
        [int]$TimeOut = 10,
        [int]$MaxDBSize = 4091
    )
    
    begin {
    }
    process {
        if ($Path.Exists) {
            $ConnStr = "Data Source='{0}';File Access Retry Timeout={1};Max Database Size={2}" -f $Path.FullName, $TimeOut, $MaxDBSize
            $script:SdfConnection = New-Object "System.Data.SqlServerCe.SqlCeConnection" $ConnStr
            $script:SdfCommand = New-Object "System.Data.SqlServerCe.SqlCeCommand"
            $script:SdfCommand.CommandType = [System.Data.CommandType]"Text"
            $script:SdfCommand.Connection = $script:SdfConnection
            Write-Verbose "Connection to $script:SdfConnection has been established"
        }
        else {
            Write-Warning "$($Path.FullName) not found"
        }
    }
    end {
    }
}
$Script:Config = Import-Clixml $PSScriptRoot\config.clixml
$Script:Config.QueryRootPath = $Script:Config.QueryRootPath -f $PSScriptRoot
function Invoke-SdfCmd {
    [CmdletBinding()]
    param (
        # Parameter help description
        [Parameter(ParameterSetName = "Typed",
            Position = 0,
            Mandatory = $true)]
        [String]$TQuery,
        [Parameter(ParameterSetName = "Saved",
            Position = 1,
            Mandatory = $false)]
        [string[]]$ReplaceVariables

    )

    DynamicParam {
        $ParameterName = 'Query'
        $QueryNames = foreach ($File in Get-ChildItem -Path $Script:Config.QueryRootPath -File -Filter *.sql -Recurse) {
            $BasePath = $File.Fullname -replace $($Config.QueryRootPath -replace '\\', '\\') -replace '\.sql$'
            $BasePath.trim('\')
        }
        $ParameterAttribute = New-Object System.Management.Automation.ParameterAttribute
        $ParameterAttribute.ParameterSetName="Saved"
        $ParameterAttribute.Mandatory = $true
        $ParameterAttribute.Position = 0
        $AttributeCollection = New-Object System.Collections.ObjectModel.Collection[System.Attribute]
        $AttributeCollection.Add($ParameterAttribute)
        $ValidateSetAttribute = New-Object System.Management.Automation.ValidateSetAttribute($QueryNames)
        $AttributeCollection.Add($ValidateSetAttribute)
        $RuntimeParameter = New-Object System.Management.Automation.RuntimeDefinedParameter($ParameterName, [string], $AttributeCollection)
        $RuntimeParameterDictionary = New-Object System.Management.Automation.RuntimeDefinedParameterDictionary
        $RuntimeParameterDictionary.Add($ParameterName, $RuntimeParameter)
        return $RuntimeParameterDictionary
    }
    
    begin {
        if (!$script:SdfConnection){
            throw 'No SDF connection found. Run Connect-SdfFile prior to running this command.'
        }
        $QueryString=''
        if ($PsCmdlet.ParameterSetName -eq "Saved"){
            Write-Verbose "Executing a saved query"
            $QueryPath = '{0}\{1}.sql' -f $Config.QueryRootPath, $PsBoundParameters[$ParameterName]
            if (test-path $QueryPath) {
                $QueryString = $(Get-Content $QueryPath -raw) -f $ReplaceVariables
            }
            else {
                Write-Warning "$QueryPath not found"
                continue
            }
        }
        if ($PsCmdlet.ParameterSetName -eq "Typed"){
            Write-Verbose "Executing a typed query"
            $QueryString = $TQuery
        }
        Write-Verbose "Executing: $QueryString"
        $script:SdfCommand.CommandText = $QueryString
        $script:SdfConnection.Open()
    }
    
    process {
        if ($QueryString -match 'SELECT'){
            Write-Verbose "Running a Reader query"
            $DataTable = new-object "System.Data.DataTable"
            $DataReader = $script:SdfCommand.ExecuteReader()
            $DataTable.Load($DataReader)
            $script:SdfConnection.Close()
            $DataTable
        }
        else {
            Write-Verbose "Running a NonQuery query"
            $NonQuery = $script:SdfCommand.ExecuteNonQuery()
            Write-Verbose $NonQuery
        }
    }

    end {
        try {$script:SdfConnection.Close()} catch {}
    }
}
function New-SdfFile {
    [CmdletBinding()]
    param (
        [Parameter(Mandatory=$true)]
        [System.IO.FileInfo]$Path,
        # Just a Switch
        [switch]$Force,
        [switch]$Connect
    )
    
    begin {
        if ($Path.Exists){
            if ($Force){
                Remove-Item -Path $Path.FullName
            } else {
                throw "$($Path.FullName) exists. Use the Force switch to overwrite."
            }
        }
    }
    
    process {
        $ConnStr = "Data Source='{0}'" -f $Path.FullName
        $db = New-Object "System.Data.SqlServerCe.SqlCeEngine" $ConnStr
        $db.CreateDatabase()
        $db.Dispose()
        $Path.Refresh()
        if ($Path.Exists){
            Write-Verbose "$($Path.Fullname) has been created"
            if ($Connect){
                Connect-SdfFile -Path $Path.Fullname
            }
        }
        Else {
            Write-Warning "Could not create $($Path.Fullname)"
        }
    }
    
    end {
    }
}
function Set-SavedQuery {
    [CmdletBinding()]
    param (
        [Parameter(Mandatory = $true, Position = 0)]
        [string]$QueryName,
        [Parameter(Mandatory = $true, Position = 1)]
        [string[]]$Query,
        [Parameter(Mandatory = $false, Position = 2)]
        [switch]$Force,
        [System.IO.DirectoryInfo]$UpdateQueryPath
    )
    
    begin {
        if ($UpdateQueryPath) {
            try {
                $Script:Config | Export-Clixml $PSScriptRoot\config.clixml
            }
            catch { 
                Write-Warning "You need write access to $PSScriptRoot in order to update the Saved Queries path"
                Continue
            }
            if (!$UpdateQueryPath.exists) {
                try {
                    $Null = New-Item -ItemType Directory -Path $UpdateQueryPath -Force -ErrorAction stop
                }
                Catch {
                    Write-Warning "Could not create $UpdateQueryPath"
                    continue
                }
            }
            else {
                Write-warning "You have chosen an existing path. Only sql-files in this directory will be available for this module"
            }
            foreach ($File in $(Get-ChildItem -Path $Script:Config.QueryRootPath -File *.sql -Recurse)) {
                $TargetPath = Split-path $($File.fullname -replace $($Script:Config.QueryRootPath -replace '\\','\\'),$UpdateQueryPath)
                if (!$(Test-PAth $TargetPath)){
                    $Null = New-Item -ItemType Directory -Path $TargetPath
                }
                Copy-Item $File.Fullname -Destination $TargetPath
            } 
            $Script:Config.QueryRootPath = $UpdateQueryPath
            $Script:Config | Export-Clixml $PSScriptRoot\config.clixml
            Write-Verbose "Query root path is now: $($Script:Config.QueryRootPath)"
        }

        $FilePath = '{0}\{1}.sql' -f $Script:Config.QueryRootPath, $QueryName
        if ($(test-path $FilePath)) {
            if ($Force) {
                Write-Warning "Updating saved query: $QueryName"
            }
            else {
                Write-Warning "$QueryName exists. Use Force switch to overwrite"
                Continue
            }
        }
    }
    
    process {
        try {
            $Query | Out-File $FilePath -Force -ErrorAction Stop
        }
        catch {
            Write-warning "You do not have write permissions to $($Script:Config.QueryRootPath). Change the saved queries path using the -UpdateQueryPath switch or run the cmdlet elevated"
        }
        
    }
    
    end {
    }
}