SELECT [softwareThoughts] from Tyler

I wish I'd have known about PowerShell a while ago. I had long given up on this and turned to cygwin, dreamed about carving out enough spare time to augment IronPython to have a handy windows scripting api.

Anyhow, it's a little awkward but I think I like it. Better to show than tell, here's a script that does a little communication with SQL Server via osql.exe and the SqlClient api:

function main
{
 $basedir = "D:\prj"

 push-location($basedir)

 $upgradeDirs = UseTheseUpgradeDirs(dir "Upgrade Scripts")

 push-location("Upgrade Scripts")
 foreach ($dir in $upgradeDirs)
 {
  RunScriptsIn($dir)
 }
 pop-location
 
 RunScriptsIn('Views')
 RunScriptsIn('Functions')
 RunScriptsIn('Stored Procedures')
 
 pop-location
}




function RunScriptsIn($dir)
{
 push-location($dir)
 $files = dir *.sql, *.prc, *.viw, *.udf
 foreach($file in $files)
 {
  write-host $file.name
  $results = osql -n -S localhost -E -i "$file"
  write-host $results
 }
 pop-location
}



function UseTheseUpgradeDirs( $dirs )
{
 $useThese = new-object System.Collections.ArrayList
 
 $conn = new-object System.data.SqlClient.SqlConnection(
  [String]::Format("Server={0};Database={1};User={2};Password={3};",
    $server, $database, $user, $password))
    
 $conn.open()
 
 foreach($dir in $dirs)
 {
  $sql = 
   "select count(1) from version where build >= '" + $dir.name.SubString(5) + "'"
  $cmd = new-object System.Data.SqlClient.SqlCommand($sql, $conn)
  $reader = $cmd.ExecuteReader()
  $foobar = $reader.Read()
  if ($reader[0]>=1)
  {
   $useThese.Add($dir)
  }
  $reader.close()
 }

 $conn.close()
 return($useThese)
}

main