PowerShell WinForm SQL查询日期范围过滤异常及需求实现

核心问题分析 #

1. 强制日期过滤导致无结果：原代码无论用户是否需要日期过滤，都会在SQL中添加DateTime >= '$startDate' AND DateTime <= '$endDate'条件，而DateTimePicker默认值为当前日期，导致未明确选择日期时，只会查询当天的记录（若当天无数据则返回空）。
2. 日期匹配逻辑错误：数据库中DateTime字段包含时间信息，直接用<= '$endDate'会仅匹配结束日期当天00:00:00的记录，所有当天晚于0点的记录都会被排除。
3. SQL注入风险：直接将用户输入的客户编号拼接到SQL语句中，存在安全隐患。

修正后的完整代码 #

Add-Type -AssemblyName System.Windows.Forms
Add-Type -AssemblyName System.Drawing

# 创建窗体
$form = New-Object Windows.Forms.Form
$form.Text = "客户支票付款记录查询"
$form.Size = New-Object Drawing.Size(820, 550)
$form.StartPosition = "CenterScreen"
$form.FormBorderStyle = [Windows.Forms.FormBorderStyle]::FixedDialog
$form.MaximizeBox = $false
$form.MinimizeBox = $false

# 客户编号输入区域
$labelCustNum = New-Object Windows.Forms.Label
$labelCustNum.Location = New-Object Drawing.Point(10, 20)
$labelCustNum.Size = New-Object Drawing.Size(200, 20)
$labelCustNum.Text = "输入客户编号:"
$form.Controls.Add($labelCustNum)

$textBoxCustNum = New-Object Windows.Forms.TextBox
$textBoxCustNum.Location = New-Object Drawing.Point(10, 50)
$textBoxCustNum.Size = New-Object Drawing.Size(200, 20)
$form.Controls.Add($textBoxCustNum)

# 日期过滤控制区域
$checkBoxEnableDateFilter = New-Object Windows.Forms.CheckBox
$checkBoxEnableDateFilter.Location = New-Object Drawing.Point(325, 20)
$checkBoxEnableDateFilter.Size = New-Object Drawing.Size(150, 20)
$checkBoxEnableDateFilter.Text = "启用日期过滤"
$form.Controls.Add($checkBoxEnableDateFilter)

$startDatePicker = New-Object Windows.Forms.DateTimePicker
$startDatePicker.Location = New-Object Drawing.Point(325, 50)
$startDatePicker.Enabled = $false
$form.Controls.Add($startDatePicker)

$labelDateRange = New-Object Windows.Forms.Label
$labelDateRange.Location = New-Object Drawing.Point(485, 50)
$labelDateRange.Size = New-Object Drawing.Size(40, 20)
$labelDateRange.Text = "至"
$form.Controls.Add($labelDateRange)

$endDatePicker = New-Object Windows.Forms.DateTimePicker
$endDatePicker.Location = New-Object Drawing.Point(535, 50)
$endDatePicker.Enabled = $false
$form.Controls.Add($endDatePicker)

# 绑定复选框与日期选择器的启用状态
$checkBoxEnableDateFilter.Add_CheckedChanged({
    $startDatePicker.Enabled = $checkBoxEnableDateFilter.Checked
    $endDatePicker.Enabled = $checkBoxEnableDateFilter.Checked
})

# 功能按钮区域
$buttonExecute = New-Object Windows.Forms.Button
$buttonExecute.Location = New-Object Drawing.Point(10, 90)
$buttonExecute.Size = New-Object Drawing.Size(100, 30)
$buttonExecute.Text = "查询付款记录"
$form.Controls.Add($buttonExecute)

$buttonPrint = New-Object Windows.Forms.Button
$buttonPrint.Location = New-Object Drawing.Point(120, 90)
$buttonPrint.Size = New-Object Drawing.Size(100, 30)
$buttonPrint.Text = "打印结果"
$buttonPrint.Add_Click({
    $printDialog = New-Object Windows.Forms.PrintDialog
    $printDialog.UseEXDialog = $true

    if ($printDialog.ShowDialog() -eq [Windows.Forms.DialogResult]::OK) {
        $printDocument = New-Object Drawing.Printing.PrintDocument
        $printDocument.DocumentName = "客户支票付款记录"
        $printDocument.Add_PrintPage({
            param($sender, $e)
            $e.Graphics.DrawString("客户支票付款记录", (New-Object Drawing.Font "Arial", 16), [Drawing.Brushes]::Black, 100, 100)

            $rowIndex = 0
            foreach ($row in $dataGridView.Rows) {
                if ($row.IsNewRow) { continue }
                $text = ""
                $columnIndex = 0
                foreach ($cell in $row.Cells) {
                    $text += $cell.Value.ToString()
                    if ($columnIndex -lt $row.Cells.Count - 1) {
                        $text += "  "
                    }
                    $columnIndex++
                }
                $e.Graphics.DrawString($text, (New-Object Drawing.Font "Arial", 12), [Drawing.Brushes]::Black, 100, 150 + $rowIndex * 20)
                $rowIndex++
            }
        })

        $printDocument.Print()
    }
})
$form.Controls.Add($buttonPrint)

$buttonCloseScript = New-Object Windows.Forms.Button
$buttonCloseScript.Text = "关闭程序"
$buttonCloseScript.Location = New-Object Drawing.Point(230, 90)
$buttonCloseScript.Size = New-Object Drawing.Size(100, 30)
$buttonCloseScript.Add_Click({
    $form.Close()
})
$form.Controls.Add($buttonCloseScript)

# 数据展示区域
$dataGridView = New-Object Windows.Forms.DataGridView
$dataGridView.Location = New-Object Drawing.Point(10, 140)
$dataGridView.Size = New-Object Drawing.Size(780, 350)
$dataGridView.AutoSizeColumnsMode = [Windows.Forms.DataGridViewAutoSizeColumnsMode]::Fill
$dataGridView.AllowUserToAddRows = $false
$dataGridView.RowHeadersVisible = $false
$form.Controls.Add($dataGridView)

# 查询执行函数
function ExecuteQuery {
    $server = ".\PCAMERICA"
    $database = "lyleoil"
    $user = "sa"
    $password = "pcAmer1ca"

    # 基础查询语句
    $queryParts = @(
        "SELECT CustNum,",
        "       ABS(Trans_Amount) AS Payment,",
        "       Payment_Info AS Check_Number,",
        "       FORMAT(DateTime, 'MMMM dd, yyyy hh:mm tt') AS Payment_Date",
        "FROM AR_Transactions",
        "WHERE CustNum = @CustNum",
        "  AND Payment_Method = 'CH'"
    )

    # 如果启用日期过滤，追加日期条件
    if ($checkBoxEnableDateFilter.Checked) {
        # 使用DATEADD确保包含结束日期当天的所有时间记录
        $queryParts += "  AND DateTime >= @StartDate AND DateTime < DATEADD(day, 1, @EndDate)"
    }

    $queryParts += "ORDER BY DateTime DESC;"
    $query = $queryParts -join "`n"

    # 创建数据库连接与命令
    $connectionString = "Server=$server;Database=$database;User Id=$user;Password=$password;"
    $connection = New-Object Data.SqlClient.SqlConnection($connectionString)
    $command = $connection.CreateCommand()
    $command.CommandText = $query

    # 添加参数，避免SQL注入
    $command.Parameters.Add("@CustNum", [Data.SqlDbType]::VarChar).Value = $textBoxCustNum.Text
    if ($checkBoxEnableDateFilter.Checked) {
        $command.Parameters.Add("@StartDate", [Data.SqlDbType]::Date).Value = $startDatePicker.Value.Date
        $command.Parameters.Add("@EndDate", [Data.SqlDbType]::Date).Value = $endDatePicker.Value.Date
    }

    try {
        $connection.Open()
        $adapter = New-Object Data.SqlClient.SqlDataAdapter $command
        $dataset = New-Object Data.DataSet
        $adapter.Fill($dataset) | Out-Null
        $connection.Close()

        $dataGridView.DataSource = $dataset.Tables[0]
    }
    catch {
        [Windows.Forms.MessageBox]::Show($_.Exception.Message, "查询错误", "Ok", "Error")
    }
}

# 绑定按钮点击事件
$buttonExecute.Add_Click({
    ExecuteQuery
})

# 显示窗体
$form.ShowDialog()

关键修改说明 #

1. 添加日期过滤开关：新增复选框启用日期过滤，只有勾选时才会启用日期选择器并添加日期过滤条件，未勾选时查询全部符合条件的记录。
2. 修正日期匹配逻辑：使用DateTime >= @StartDate AND DateTime < DATEADD(day, 1, @EndDate)，确保包含结束日期当天的所有时间记录（例如结束日期为2023-07-05时，会匹配到2023-07-05 23:59:59之前的所有记录）。
3. 参数化查询：将用户输入的客户编号和日期转换为SQL参数，彻底避免SQL注入风险。
4. 优化打印逻辑：跳过DataGridView中的空行（IsNewRow判断），避免打印空白内容。

内容的提问来源于stack exchange，提问作者Chad