sql server - Fine-tuning Powershell SQL Script -

my company has program tracks our employee workouts. when had program made, did not think adding ability add or remove employee program.

i wrote script in powershell allows easier in ssms. see if can me clean bit , fine tune it.

my biggest headache 1 or -1 gets returned anytime execute function. ask if finished, loop or exit. right exits done.

<#writes invoker log#> $trandate = get-date  $tranuser = $env:username <# variables open connection sql server #>  $sqlcn = new-object system.data.sqlclient.sqlconnection $sqlcn.connectionstring = "server=10.10.1.19\vtsworkout;integrated  security=true;database=vtsworkout;"   <# read user wants #> $input = read-host "do want [a]dd new employee, [r]emove employee or [e]xit?" switch($input){  <# stuff adding employee database #>  a{ $eid = read-host "what employees id number?" $fname = read-host "what employees first name?" $lname = read-host "what employees last name?" $dept = read-host "what department employee in?" $pay = read-host "is employee salaried? [0]yes or [1]no" $hire = read-host "when employee hired? input mm-dd-yyyy"          out-file -filepath "l:\personnel\workoutapp\workouts.log" -append -inputobject "on $trandate, $tranuser  added employee# $eid, $fname $lname"      $sqlcn.open()     $sqlcmd = $sqlcn.createcommand()     $query = "insert employees values (@eid,@lname,@fname,@dept,@pay,@hire)"     $sqlcmd.commandtext = $query     $sqlcmd.parameters.addwithvalue("@eid", $eid) | out-null     $sqlcmd.parameters.addwithvalue("@fname", $fname) | out-null     $sqlcmd.parameters.addwithvalue("@lname", $lname) | out-null     $sqlcmd.parameters.addwithvalue("@dept", $dept) | out-null     $sqlcmd.parameters.addwithvalue("@pay", $pay) | out-null     $sqlcmd.parameters.addwithvalue("@hire", $hire) | out-null      $sqlcmd.executenonquery()     $sqlcn.close()   }  <# stuff removing employee database#>  r{  <#collect reason removal#>     $reason = read-host -prompt "why deleting employee?"      $eid = read-host "what id number of employee want remove?"     $sqlcn.open()     $sqlcmd = $sqlcn.createcommand()     $query = "select employeeid,firstname, lastname employees employeeid = @eid"     $sqlcmd.commandtext = $query      $sqlcmd.parameters.addwithvalue("@eid", $eid) | out-null     $sqlcmd.executenonquery()     $reader = $sqlcmd.executereader()     $arry = @()     while ($reader.read()) {     $row = @{}     ($i = 0; $i -lt $reader.fieldcount; $i++)     {         $row[$reader.getname($i)] = $reader.getvalue($i)     }     #convert hashtable array of psobjects     $arry+= new-object psobject -property $row     }      $sqlcn.close()     write-host $arry     $empresult = read-host "is correct employee? [y]es or [n]o"      <#if correct employee found, continue below.     if wrong employee returned, kill program #>     switch($empresult) {      y{                             out-file -filepath "l:\personnel\workoutapp\workouts.log" -append -inputobject "on $trandate, $tranuser deleted employee $eid following reason: $reason"                             $sqlcn.open()                             $sqlcmd = $sqlcn.createcommand()                             $query = "delete employees employeeid = @eid"                             $sqlcmd.commandtext = $query                             $sqlcmd.parameters.addwithvalue("@eid", $eid)                             $sqlcmd.executenonquery()                              $adp = new-object system.data.sqlclient.sqldataadapter $sqlcmd                             $data = new-object system.data.dataset                             $adp.fill($data) | out-null                             $sqlcn.close()                           }     n{     out-file -filepath "l:\personnel\workoutapp\workouts.log" -append -inputobject "on $trandate, $tranuser tried deleted employee $eid. exited program before doing so."     write-host "please restart program. if issue persists, please contact department."     read-host -prompt "press enter exit"     }     } }  <# line exit program #>  e{ exit } } 

any thoughts on cleaning appreciated.

this off-topic, i'll give answer.

generally, don't want use parameters.addwithvalue() @ all, because sends every parameter nvarchar. it's not deprecated, it's not idea use it. if you've got datetimes or other non-string parameters, can end problems. it's preferable use parameters.add():

$sqlcmd.parameters.add("@eid", [system.data.sqldbtype]::int).value = $eid 

obviously, datatype use [system.data.sqldbtype] should match datatype of actual column in database. has benefit there won't return value need send out-null or cast [void].

this mess:

$sqlcmd.executenonquery() $reader = $sqlcmd.executereader() $arry = @() while ($reader.read()) {     $row = @{}     ($i = 0; $i -lt $reader.fieldcount; $i++)     {         $row[$reader.getname($i)] = $reader.getvalue($i)     }     #convert hashtable array of psobjects     $arry+= new-object psobject -property $row } 

first, you're executing query twice. both executenonquery() , executereader() execute query! multiple times in script.

second, can this:

$datatable = new-object system.data.datatable $datatable.load($sqlcmd.executereader()) 

then, if don't want work datatable -- they're more complex custom object not bad -- can convert generic object pretty easily:

$data = $datatable | convertto-csv -notypeinformation | convertfrom-csv 

this make string, though, sure that's want. might try this:

$data = $datatable | select-object -property <list> 

you don't want use select-object * because you'll properties don't want.

this executing query twice:

$sqlcn.open() $sqlcmd = $sqlcn.createcommand() $query = "delete employees employeeid = @eid" $sqlcmd.commandtext = $query $sqlcmd.parameters.addwithvalue("@eid", $eid) $sqlcmd.executenonquery()  $adp = new-object system.data.sqlclient.sqldataadapter $sqlcmd $data = new-object system.data.dataset $adp.fill($data) | out-null $sqlcn.close() 

both $sqlcmd.executenonquery() , $adp.fill($data) execute query! additionally, executenonquery() returns number of records affected. this:

$sqlcmd.executenonquery() | out-null 

or this:

[void]$sqlcmd.executenonquery() 

but should verify result expect. shouldn't getting -1 insert or delete statements.

learn documentation methods you're calling , understand possible return values , why. .net methods thoroughly documented on msdn. can find them googling "c# ". you'll find c# examples can converted powershell, too.

wiki