PowerShell 技能连载 - 数据库操作技巧

在 PowerShell 中处理数据库操作是一项常见任务，特别是在数据管理和自动化过程中。本文将介绍一些实用的数据库操作技巧。

首先，我们需要安装必要的模块：

# 安装数据库操作模块
Install-Module -Name SqlServer -Force

连接数据库并执行查询：

# 设置数据库连接参数
$server = "localhost"
$database = "TestDB"
$query = @"
SELECT 
    员工ID,
    姓名,
    部门,
    职位,
    入职日期
FROM 员工信息
WHERE 部门 = '技术部'
ORDER BY 入职日期 DESC
"@

# 执行查询
$results = Invoke-Sqlcmd -ServerInstance $server -Database $database -Query $query

# 显示结果
Write-Host "技术部员工列表："
$results | Format-Table

执行参数化查询：

# 创建参数化查询
$query = @"
SELECT 
    订单号,
    客户名称,
    订单金额,
    下单日期
FROM 订单信息
WHERE 下单日期 BETWEEN @开始日期 AND @结束日期
AND 订单金额 > @最小金额
"@

# 设置参数
$params = @{
    开始日期 = "2024-01-01"
    结束日期 = "2024-03-31"
    最小金额 = 10000
}

# 执行参数化查询
$results = Invoke-Sqlcmd -ServerInstance $server -Database $database -Query $query -Parameters $params

执行事务操作：

# 开始事务
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = "Server=$server;Database=$database;Integrated Security=True"
$connection.Open()
$transaction = $connection.BeginTransaction()

try {
    # 执行多个操作
    $query1 = "UPDATE 库存 SET 数量 = 数量 - 1 WHERE 产品ID = 1"
    $query2 = "INSERT INTO 订单明细 (订单号, 产品ID, 数量) VALUES ('ORD001', 1, 1)"
    
    Invoke-Sqlcmd -ServerInstance $server -Database $database -Query $query1 -Transaction $transaction
    Invoke-Sqlcmd -ServerInstance $server -Database $database -Query $query2 -Transaction $transaction
    
    # 提交事务
    $transaction.Commit()
    Write-Host "事务执行成功"
}
catch {
    # 回滚事务
    $transaction.Rollback()
    Write-Host "事务执行失败：$_"
}
finally {
    $connection.Close()
}

批量数据导入：

# 准备批量数据
$data = @(
    @{
        产品名称 = "笔记本电脑"
        价格 = 5999
        库存 = 10
    },
    @{
        产品名称 = "无线鼠标"
        价格 = 199
        库存 = 50
    }
)

# 创建数据表
$table = New-Object System.Data.DataTable
$table.Columns.Add("产品名称")
$table.Columns.Add("价格")
$table.Columns.Add("库存")

# 添加数据
foreach ($item in $data) {
    $row = $table.NewRow()
    $row["产品名称"] = $item.产品名称
    $row["价格"] = $item.价格
    $row["库存"] = $item.库存
    $table.Rows.Add($row)
}

# 批量导入数据
Write-SqlTableData -ServerInstance $server -Database $database -TableName "产品信息" -InputData $table

一些实用的数据库操作技巧：

1. 数据库备份：

   # 创建数据库备份
   $backupPath = "C:\Backups\TestDB_$(Get-Date -Format 'yyyyMMdd').bak"
   $backupQuery = @"
   BACKUP DATABASE TestDB
   TO DISK = '$backupPath'
   WITH FORMAT, MEDIANAME = 'TestDBBackup', NAME = 'TestDB-Full Database Backup'
   "@
   
   Invoke-Sqlcmd -ServerInstance $server -Query $backupQuery

2. 数据库还原：

   # 还原数据库
   $restoreQuery = @"
   RESTORE DATABASE TestDB
   FROM DISK = '$backupPath'
   WITH REPLACE
   "@
   
   Invoke-Sqlcmd -ServerInstance $server -Query $restoreQuery

3. 数据库维护：

   # 更新统计信息
   $updateStatsQuery = @"
   UPDATE STATISTICS 员工信息 WITH FULLSCAN
   UPDATE STATISTICS 订单信息 WITH FULLSCAN
   "@
   
   Invoke-Sqlcmd -ServerInstance $server -Database $database -Query $updateStatsQuery

这些技巧将帮助您更有效地处理数据库操作。记住，在执行数据库操作时，始终要注意数据安全性和性能影响。同时，建议使用参数化查询来防止 SQL 注入攻击。