# encoding: ascii
# api: powershell
# title: Write-FileInfoToSQL.ps1
# description: Demonstration script for getting file names and last update times into a SQL Server table.
# version: 0.1
# type: script
# author: Chad Miller
# license: CC0
# function: Clear-Fill
# x-poshcode-id: 3006
# x-archived: 2016-05-21T13:19:15
# x-published: 2012-10-17T12:42:00
#
#
# ---------------------------------------------------------------------------
### <Script>
### <Author>
### Chad Miller 
### </Author>
### <Description>
### Reports data model directories and bool value as to whether they have
### an htm file of the same name as the project directory.
### </Description>
### <Usage>
### ./Write-FileInfoToSQL.ps1
### </Usage>
### </Script>
# ---------------------------------------------------------------------------

$sqlserver = 'SQL1' 
$dbname = 'DataAdmin'
$tblname = 'data_model_fill'

#######################
function Clear-Fill
{

    $connString = "Server=$sqlserver;Database=$dbname;Integrated Security=SSPI;"
    $conn = new-object System.Data.SqlClient.SqlConnection $connString
    $conn.Open()
    $cmd = new-object System.Data.SqlClient.SqlCommand("TRUNCATE TABLE $tblname", $conn)
    $cmd.ExecuteNonQuery()
    $conn.Close()

} #Clear-Fill

#######################
function Out-DataTable 
{
    param($Properties="*")
    Begin
    {
        $dt = new-object Data.datatable  
        $First = $true 
    }
    Process
    {
        $DR = $DT.NewRow()  
        foreach ($item in $_ |  Get-Member -type *Property $Properties ) {  
          $name = $item.Name
          if ($first) {  
            $Col =  new-object Data.DataColumn  
            $Col.ColumnName = $name
            $DT.Columns.Add($Col)
          }  
            $DR.Item($name) = $_.$name  
        }  
        $DT.Rows.Add($DR)  
        $First = $false  
    }
    End
    {
        return @(,($dt))
    }

}# Out-DataTable 

#######################
function Write-DataTableToDatabase
{ 
    param($destServer,$destDb,$destTbl)
    process
    {
        $connectionString = "Data Source=$destServer;Integrated Security=true;Initial Catalog=$destdb;"
        $bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $connectionString
        $bulkCopy.DestinationTableName = "$destTbl"
        $bulkCopy.WriteToServer($_)
    }

}# Write-DataTableToDatabase

#######################
Clear-Fill
get-childitem "\\z001.contoso.com\wwwroot`$\Playbook\Databases\DataModels" | where {$_.PSIsContainer -eq $true} | select name, @{name='htm';Expression={test-path "\\z001.contoso.com\wwwroot`$\Playbook\Databases\DataModels\$($_.Name)\$($_.Name).htm"}}, @{name='lastWrite';Expression={if (test-path "\\z001.contoso.com\wwwroot`$\Playbook\Databases\DataModels\$($_.Name)\$($_.Name).htm") {$last = Get-Item "\\z001.contoso.com\wwwroot`$\Playbook\Databases\DataModels\$($_.Name)\$($_.Name).htm" | Select LastWriteTime;$last.LastWriteTime}}} |  
Out-DataTable | Write-DataTableToDatabase $sqlserver $dbname $tblname
