微调 Powershell SQL 脚本

Question

我的公司有一个跟踪员工锻炼的程序。当我们制定此程序时，我们没有考虑向该计划添加或删除员工的功能。

我在PowerShell中编写了一个脚本，使我们能够比在SSMS中更轻松地执行此操作。我想看看是否有人可以帮助我清理一下并对其进行微调。

我最头疼的是每当我们执行函数时都会返回的1或-1。我还想问他们是否完成，然后循环或退出。现在，它只是在完成后立即退出。

<#Writes the invoker to log#>
$trandate = Get-Date 
$tranuser = $env:UserName
<# Variables to open the connection to the SQL server #>
$sqlcn = New-Object System.Data.SqlClient.SqlConnection
$sqlcn.ConnectionString = "server=10.10.1.19VTSWORKOUT;Integrated 
Security=true;Database=VTSWORKOUT;"

<# Read what the user wants to do #>
$input = Read-Host "Do you want to [A]dd a New Employee, [R]emove an Employee or [E]xit?"
switch($input){
<# Stuff for adding an employee to the database #>
A{
$eid = Read-Host "What is the Employees ID number?"
$fname = Read-Host "What is the Employees first name?"
$lname = Read-Host "What is the Employees last name?"
$dept = Read-Host "What department is the Employee in?"
$pay = Read-Host "Is the Employee Salaried? [0]Yes or [1]No"
$hire = Read-Host "When was the Employee hired? Input as MM-DD-YYYY"
Out-File -FilePath "L:PersonnelWorkoutAppworkouts.log" -Append -InputObject "On $trandate, $tranuser  added Employee# $eid, $fname $lname"
$sqlcn.Open()
$sqlcmd = $sqlcn.CreateCommand()
$query = "INSERT INTO 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 for removing an employee from the database#>
R{
<#Collect reason for removal#>
$reason = Read-Host -Prompt "Why are you deleting this employee?"
$eid = Read-Host "What is the ID number of the Employee you want to remove?"
$sqlcn.Open()
$sqlcmd = $sqlcn.CreateCommand()
$query = "SELECT EmployeeID,FirstName, LastName from Employees WHERE EmployeeID = @eid"
$sqlcmd.CommandText = $query 
$sqlcmd.Parameters.AddWithValue("@eid", $eid) | Out-Null
$sqlcmd.ExecuteNonQuery()
$Reader = $sqlcmd.ExecuteReader()
$arry = @()
while ($Reader.Read()) {
$row = @{}
for ($i = 0; $i -lt $reader.FieldCount; $i++)
{
$row[$reader.GetName($i)] = $reader.GetValue($i)
}
#convert hashtable into an array of PSObjects
$arry+= new-object psobject -property $row
}
$sqlcn.Close()
write-host $arry
$empResult = Read-Host "Is that the correct employee? [Y]es or [N]o"
<#If the correct employee was found, continue below.
If the wrong employee was returned, Kill Program #>
switch($empResult) {
Y{
Out-File -FilePath "L:PersonnelWorkoutAppworkouts.log" -Append -InputObject "On $trandate, $tranuser deleted Employee $eid for the following reason: $reason"
$sqlcn.Open()
$sqlcmd = $sqlcn.CreateCommand()
$query = "DELETE FROM Employees WHERE 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:PersonnelWorkoutAppworkouts.log" -Append -InputObject "On $trandate, $tranuser tried to deleted Employee $eid. But exited the program before doing so."
Write-Host "Please restart the program. If the issue persists, please contact the IT department."
Read-Host -Prompt "Press Enter to exit"
}
}
}
<# Line to exit the program #>
E{
exit
}
}

任何关于清理的想法将不胜感激。

Answer 1

这是题外话，但我会给你一个答案。

通常，您根本不想使用Parameters.AddWithValue()，因为这会将每个参数作为 NVARCHAR 发送。 它没有被弃用，但使用它不是一个好主意。 如果您有日期时间或其他非字符串参数，则最终可能会遇到问题。 通常最好使用Parameters.Add()：

$sqlcmd.Parameters.Add("@eid", [System.Data.SqlDbType]::Int).Value = $eid

显然，[System.Data.SqlDbType]中使用的数据类型应与数据库中实际列的数据类型匹配。 这样做的好处是，不需要将任何返回值发送到Out-Null或转换为[void]。

这也是一团糟：

$sqlcmd.ExecuteNonQuery()
$Reader = $sqlcmd.ExecuteReader()
$arry = @()
while ($Reader.Read()) {
$row = @{}
for ($i = 0; $i -lt $reader.FieldCount; $i++)
{
$row[$reader.GetName($i)] = $reader.GetValue($i)
}
#convert hashtable into an array of PSObjects
$arry+= new-object psobject -property $row
}

首先，执行查询两次。ExecuteNonQuery()和ExecuteReader()都将执行查询！ 您可以在脚本中多次执行此操作。

其次，你可以这样做：

$DataTable = New-Object System.Data.DataTable
$DataTable.Load($sqlcmd.ExecuteReader())

然后，如果你真的不想使用DataTable——它们比自定义对象更复杂，但实际上并没有那么糟糕——你可以这样做来非常轻松地将其转换为通用对象：

$Data = $DataTable | ConvertTo-Csv -NoTypeInformation | ConvertFrom-Csv

不过，这将使所有内容都成为字符串，因此请确保这是您想要的。 您也可以尝试以下操作：

$Data = $DataTable | Select-Object -Property <list>

您不想使用Select-Object *因为您将获得您可能不想要的额外属性。

这也执行了两次查询：

$sqlcn.Open()
$sqlcmd = $sqlcn.CreateCommand()
$query = "DELETE FROM Employees WHERE 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()

$sqlcmd.ExecuteNonQuery()和$adp.fill($data)都执行查询！ 此外，ExecuteNonQuery()返回受影响的记录数。 你可以这样做：

$sqlcmd.ExecuteNonQuery() | Out-Null

或者这个：

[void]$sqlcmd.ExecuteNonQuery()

但你真正应该做的是验证结果是否是你所期望的。 你不应该为 INSERT 或 DELETE 语句获得 -1。

了解如何查找要调用的方法的文档，并了解可能的返回值是什么以及原因。 所有 .Net 方法都详细记录在 MSDN 上。 你几乎总是可以通过谷歌搜索"C#"找到它们。 你也会找到可以轻松转换为 PowerShell 的 C# 示例。