使用 Powershell 插入 SQL Server 日期 [英] Insert into SQL Server date using Powershell

查看:51

我正在使用 PowerShell 导入 .CSV 文件,我需要将其内容保存到数据库中.

我正在使用下面的代码从 .CSV 文件中提取信息,并将其放入数据库

foreach($data 中的$row) {$sql = "插入产品(评级、PARVALUE、描述、优惠券、到期日、日期、CALLDATE、FIRSTCOUPON、CUSIP、PRICEYIELD、COM)值(`'$(($row.rating).trim())', `'$(($row."PAR VALUE").trim())',`'$(($row.DESCRIPTION).trim())', `'$(($row.COUPON).trim())', `'$(($row.MATURITY))', `'$(([datetime]$row."DATED DATE"))', `'$(($row."CALL DATE").trim())', `'$(($row."FIRST COUPON"))', `'$(($row.CUSIP).trim())', `'$(($row."PRICE/YIELD").trim())',`'$(($row.COM).trim())' `)"$command.CommandText = $sql$command.ExecuteNonQuery() |出空}

$row.MATURITY/$row."CALL DATE" 和 $row."FIRST COUPON" 列都是日期 (数据库中的列的类型为 DATE).

但是插入将它作为字符串传递,数据库抱怨它无法将字符串转换为 DATE.如何在 PowerShell 命令中将其作为日期传递,以便正确写入数据库(作为 DATE 类型)?

谢谢!

解决方案

字符串连接是一个非常糟糕的主意.它不仅会打开您的代码进行 SQL 注入,而且非常脆弱.

使用参数化查询.假设您使用的是 SQL Server:

$sql = "INSERT INTO Offers (RATING, PARVALUE, description, COUPON, MATURITY, DATEDDATE, CALLDATE, FIRSTCOUPON, CUSIP, PRICEYIELD, COM) " +"值(@RATING、@PARVALUE、@DESCRIPTION、@COUPON、@MATURITY、@DATEDDATE、@CALLDATE、@FIRSTCOUPON、@CUSIP、@PRICEYIELD、@COM)"$command.CommandText = $sql# 添加您的参数并定义它们的类型以匹配产品表中的类型# System.Data.SqlDbType 类型包括所有 SQL Server 数据类型# 数据类型映射见https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql-server-data-type-mappings[void]$command.Parameters.Add('@RATING', [System.Data.SqlDbType]::NVarChar)[void]$command.Parameters.Add('@PARVALUE', [System.Data.SqlDbType]::NVarChar)[void]$command.Parameters.Add('@DESCRIPTION', [System.Data.SqlDbType]::NVarChar)[void]$command.Parameters.Add('@COUPON', [System.Data.SqlDbType]::NVarChar)[void]$command.Parameters.Add('@MATURITY', [System.Data.SqlDbType]::Date)[void]$command.Parameters.Add('@DATEDDATE', [System.Data.SqlDbType]::Date)[void]$command.Parameters.Add('@CALLDATE', [System.Data.SqlDbType]::Date)[void]$command.Parameters.Add('@FIRSTCOUPON', [System.Data.SqlDbType]::NVarChar)[void]$command.Parameters.Add('@CUSIP', [System.Data.SqlDbType]::NVarChar)[void]$command.Parameters.Add('@PRICEYIELD', [System.Data.SqlDbType]::NVarChar)[void]$command.Parameters.Add('@COM', [System.Data.SqlDbType]::NVarChar)# 我在这里默认为 NVARCHAR,但您应该使用适当的列数据类型foreach($data 中的 $row){# 设置每个参数的值$command.Parameters['@RATING'].Value = $row.rating.trim()$command.Parameters['@PARVALUE'].Value = $row."PAR VALUE".trim()$command.Parameters['@DESCRIPTION'].Value = $row.DESCRIPTION.trim()$command.Parameters['@COUPON'].Value = $row.COUPON.trim()$command.Parameters['@MATURITY'].Value = [datetime]$row.MATURITY$command.Parameters['@DATEDDATE'].Value = [datetime]$row."DATED DATE"$command.Parameters['@CALLDATE'].Value = [datetime]$row."CALL DATE"$command.Parameters['@FIRSTCOUPON'].Value = $row."FIRST COUPON"$command.Parameters['@CUSIP'].Value = $row.CUSIP.trim()$command.Parameters['@PRICEYIELD'].Value = $row."PRICE/YIELD".trim()$command.Parameters['@COM'].Value = $row.COM.trim()[无效]$command.ExecuteNonQuery()}

如果您使用的 RDBMS 与 SQL Server 不同,则您的代码可能会略有不同.

如果您在将 $row 中的字符串转换为日期时间时遇到问题,可以使用 ParseExact 函数:

$command.Parameters['@MATURITY'].Value = [datetime]::ParseExact($row.MATURITY, 'dd.MM.yyyy', [cultureinfo]::InvariantCulture)

或者你可以使用Get-Date

$command.Parameters['@MATURITY'].Value = Get-Date $row.MATURITY -Format 'dd.MM.yyyy'

参数化查询唯一复杂的部分是,如果你需要在数据库中设置一个值为NULL,那么参数的值需要是[System.DBNull]::Value,不是 $null.

  • 原文

I'm using PowerShell to import a .CSV file and I need to save its contents to a database.

I'm using the code below to pull information from the .CSV file, and put it into the database

foreach($row in $data) {
    $sql = "INSERT INTO Offerings (RATING, PARVALUE, DESCRIPTION, COUPON, MATURITY, DATEDDATE, CALLDATE, FIRSTCOUPON, CUSIP, PRICEYIELD, COM) VALUES( `
    '$(($row.rating).trim())', `
    '$(($row."PAR VALUE").trim())',`
    '$(($row.DESCRIPTION).trim())', `
    '$(($row.COUPON).trim())', `
    '$(($row.MATURITY))', `
    '$(([datetime]$row."DATED DATE"))', `
    '$(($row."CALL DATE").trim())', `
    '$(($row."FIRST COUPON"))', `
    '$(($row.CUSIP).trim())', `
    '$(($row."PRICE/YIELD").trim())',`
    '$(($row.COM).trim())' `
    )"

    $command.CommandText = $sql
    $command.ExecuteNonQuery() | out-null
}

The columns $row.MATURITY/$row."CALL DATE" and $row."FIRST COUPON" are all dates (the columns in the database are of type DATE).

But the insert is passing it as a string, which the database complains that it can't convert string to DATE. How can I pass it as a date in the PowerShell command so it will write correctly to the database (as DATE type)?

Thanks!

解决方案

String concatenation is a very bad idea. It doesn't just open your code to SQL injection, it's extremely fragile.

Use a parameterized query. Assuming you're using SQL Server:

$sql = "INSERT INTO Offerings (RATING, PARVALUE, DESCRIPTION, COUPON, MATURITY, DATEDDATE, CALLDATE, FIRSTCOUPON, CUSIP, PRICEYIELD, COM) " +
    "VALUES (@RATING, @PARVALUE, @DESCRIPTION, @COUPON, @MATURITY, @DATEDDATE, @CALLDATE, @FIRSTCOUPON, @CUSIP, @PRICEYIELD, @COM)"

$command.CommandText = $sql

# Add your parameters and define their types to match the types in the Offerings table
# The System.Data.SqlDbType type includes all SQL Server data types
# For data type mapping, see https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql-server-data-type-mappings
[void]$command.Parameters.Add('@RATING', [System.Data.SqlDbType]::NVarChar)
[void]$command.Parameters.Add('@PARVALUE', [System.Data.SqlDbType]::NVarChar)
[void]$command.Parameters.Add('@DESCRIPTION', [System.Data.SqlDbType]::NVarChar)
[void]$command.Parameters.Add('@COUPON', [System.Data.SqlDbType]::NVarChar)
[void]$command.Parameters.Add('@MATURITY', [System.Data.SqlDbType]::Date)
[void]$command.Parameters.Add('@DATEDDATE', [System.Data.SqlDbType]::Date)
[void]$command.Parameters.Add('@CALLDATE', [System.Data.SqlDbType]::Date)
[void]$command.Parameters.Add('@FIRSTCOUPON', [System.Data.SqlDbType]::NVarChar)
[void]$command.Parameters.Add('@CUSIP', [System.Data.SqlDbType]::NVarChar)
[void]$command.Parameters.Add('@PRICEYIELD', [System.Data.SqlDbType]::NVarChar)
[void]$command.Parameters.Add('@COM', [System.Data.SqlDbType]::NVarChar)
# I've defaulted to NVARCHAR here, but you should use the appropriate column data type

foreach ($row in $data) {
    # Set the values of each parameter
    $command.Parameters['@RATING'].Value      = $row.rating.trim()
    $command.Parameters['@PARVALUE'].Value    = $row."PAR VALUE".trim()
    $command.Parameters['@DESCRIPTION'].Value = $row.DESCRIPTION.trim()
    $command.Parameters['@COUPON'].Value      = $row.COUPON.trim()
    $command.Parameters['@MATURITY'].Value    = [datetime]$row.MATURITY
    $command.Parameters['@DATEDDATE'].Value   = [datetime]$row."DATED DATE"
    $command.Parameters['@CALLDATE'].Value    = [datetime]$row."CALL DATE"
    $command.Parameters['@FIRSTCOUPON'].Value = $row."FIRST COUPON"
    $command.Parameters['@CUSIP'].Value       = $row.CUSIP.trim()
    $command.Parameters['@PRICEYIELD'].Value  = $row."PRICE/YIELD".trim()
    $command.Parameters['@COM'].Value         = $row.COM.trim()

    [void]$command.ExecuteNonQuery()
}

Your code may vary slightly if you're using a different RDBMS than SQL Server.

If you're having trouble converting strings in $row into datetimes, you can use the ParseExact function:

$command.Parameters['@MATURITY'].Value    = [datetime]::ParseExact($row.MATURITY, 'dd.MM.yyyy', [cultureinfo]::InvariantCulture)

Or you can use Get-Date

$command.Parameters['@MATURITY'].Value    = Get-Date $row.MATURITY -Format 'dd.MM.yyyy'

The only complicated part of parameterized queries is that if you need to set a value to NULL in the databse, then the value of the parameter needs to be [System.DBNull]::Value, not $null.