将日期插入 SQL [英] Inserting a date into SQL
问题描述
我指定了以下日期时间:
I have the following datetime specified:
$dateChanged = Get-Date -Format ("yyyy-MM-dd hh:mm:ss.fff")
我想使用以下 PowerShell 将其插入 SQL 日期时间字段:
I would like to insert this into a SQL datetime field using the following PowerShell:
INSERT INTO myTable (name, dateChanged) VALUES ($name, $dateChanged);
我收到以下错误:
使用0"个参数调用ExecuteNonQuery"的异常:'11'附近的语法不正确."
Exception calling "ExecuteNonQuery" with "0" argument(s): "Incorrect syntax near '11'."
注意:这里的当前时间是 11:28.
NOTE: The current time here is 11:28.
我尝试重新格式化 dateChanged 变量无济于事.
I have tried reformatting the dateChanged variable to no avail.
推荐答案
您完全可以通过简单的字符串替换在 PowerShell 中执行 SQL,例如您可以传递给 Invoke-Sqlcmd
.我建议你不要:它会让你很容易被注入,你必须特别注意格式化日期、NULL
值、浮点值、字符串......这里有两个可以帮助你做的函数.NET 方式:
You can absolutely do SQL in PowerShell by simple string replacement, like the "parameters" you can pass to Invoke-Sqlcmd
. I suggest you don't: it leaves you open to injection and you have to take special care to format dates, NULL
values, floating-point values, strings... Here's two functions that help you do it the .NET way:
function ConvertFrom-DataReader($datareader) {
$values = New-Object Object[] $datareader.FieldCount
$names = New-Object string[] $datareader.FieldCount
$anyNameEmpty = $false
for ($i = 0; $i -lt $values.Length; $i++) {
$names[$i] = $datareader.GetName($i)
if (-not $names[$i]) { $anyNameEmpty = $true }
}
while ($datareader.Read()) {
$v = $datareader.GetValues($values)
if ($anyNameEmpty) {
Write-Output $values
} else {
$result = New-Object psobject
for ($i = 0; $i -lt $v; $i++) {
Add-Member `
-InputObject $result `
-MemberType NoteProperty `
-Name $names[$i] `
-Value $values[$i]
}
Write-Output $result
}
}
}
function Invoke-SqlCommand(
[string] $ConnectionString,
[string] $CommandText,
[hashtable] $Parameters = @{}
) {
$connection = New-Object System.Data.SqlClient.SqlConnection($ConnectionString)
try {
$connection.Open()
$command = New-Object System.Data.SqlClient.SqlCommand
$command.Connection = $connection
$command.CommandText = $commandText
$command.CommandType = [System.Data.CommandType]::Text
foreach ($Name in $Parameters.Keys) {
$command.Parameters.AddWithValue($Name, $Parameters[$Name]) | Out-Null
}
ConvertFrom-DataReader $command.ExecuteReader()
} finally {
$connection.Close()
}
}
示例使用:
Invoke-SqlCommand `
-ConnectionString "Data Source=(localdb)\mssqllocaldb" `
-CommandText "DECLARE @myTable TABLE(name VARCHAR(100), dateChanged DATETIME);
INSERT INTO @myTable (name, dateChanged) VALUES (@name, @dateChanged);
SELECT * FROM @myTable" `
-Parameters @{
name = "Winston"
dateChanged = Get-Date
}
这并不完美(您可以使用适当的 cmdlet,AddWithValue
有问题),您可能认为它对于一次性查询来说太过分了,但与文本替换相比,它仍然是一个更好的构建方式.
This is not perfect (you could use proper cmdlets, AddWithValue
has issues) and you might consider it overkill for a one-off query, but it's still a much better thing to build on than textual replacement.
这篇关于将日期插入 SQL的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!