powershell脚本将json文件至SQL Server

一、问题引入

之前写过一篇博客关于 T-SQL 脚本将 json 文件至 SQL Server，但 T-SQL 脚本只能在 SSMS 软件下运行。

现在迫切需要一种能在操作系统 shell 中运行的脚本，在 StackOverflow 中查询到 powershell 可以将 json 文件导入 SQL Server。

二、解决过程

💡 需求是递归指定目录下的所有 json 格式文件，解析 json 内容并插入指定数据库的表

1️⃣ 案例测试条件

准备了一个目录，其中目录下存在子目录，在子目录下新建文件（ json 格式，ansi 编码）,一共准备了四个文件，其中一个文件内容如下：

{
    "FixtureID": 2,
    "EventDate":"2023-02-27 01:09:58",
    "Venue":"vanue2023-02-27",
    "CityName":"杭州",
    "EventID":0
}

2️⃣ 数据库新建表

create table TestTable
(
    FixtureID int,
    EventDate datetime2(0),
    Venue     varchar(50),
    CityName  nvarchar(20),
    EventID   tinyint
)

3️⃣ 新建并修改powershell脚本

# SQL导入文件夹json. 将xxx替换为实际参数

$connectionString = "Server=xxx.xxx.xxx.xxx;Database=xxx;Integrated Security=false;User ID=xxx;Password =xxx;"

Function Insert_YourTable($json) {
    $connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)
    $insertQuery = @"
INSERT INTO dbo.xxx(
     FixtureID
    ,EventDate
    ,Venue
    ,CityName
    ,EventID
)
VALUES(
     @FixtureID
    ,@EventDate
    ,@Venue
    ,@CityName
    ,@EventID
);
"@
    $insertCommand = New-Object System.Data.SqlClient.SqlCommand($insertQuery, $connection)
    [void]($insertCommand.Parameters.Add("@FixtureID", [System.Data.SqlDbType]::Int).Value = if($null -eq $json.FixtureID) {[DbNull]::Value} else {$json.FixtureID} )
    [void]($insertCommand.Parameters.Add("@EventDate", [System.Data.SqlDbType]::DateTime2).Value = if($null -eq $json.EventDate) {[DbNull]::Value} else {$json.EventDate} )
    [void]($insertCommand.Parameters.Add("@Venue", [System.Data.SqlDbType]::VarChar, 50).Value = if($null -eq $json.Venue) {[DbNull]::Value} else {$json.Venue} )
    [void]($insertCommand.Parameters.Add("@CityName", [System.Data.SqlDbType]::NVarChar, 20).Value = if($null -eq $json.CityName) {[DbNull]::Value} else {$json.CityName} )
    [void]($insertCommand.Parameters.Add("@EventID", [System.Data.SqlDbType]::TinyInt).Value = if($null -eq $json.EventID) {[DbNull]::Value} else {$json.EventID} )   
    $connection.Open()
    try {
        [void]$insertCommand.ExecuteNonQuery()
    }
    catch [System.Data.SqlClient.SqlException] {
        Write-Host "Error inserting record. Violate PRIMARY KEY rule || $json" -ForegroundColor Yellow
    }
    finally {
        $Error.Clear()
    }
    $connection.Close()
}

# ############
# ### MAIN ###
# ############

try {
    $jsonFiles = Get-ChildItem "D:\temp\time\*.info" -Recurse
    foreach ($jsonFile in $jsonFiles) {
        Write-Host "Importing $($jsonFile.FullName)..."
        try {
            #$json = [System.IO.File]::ReadAllText($jsonFile.FullName) | ConvertFrom-Json
            $json = Get-Content $jsonFile.FullName -Encoding oem | ConvertFrom-Json
        }
        catch [System.ArgumentException]{
            Write-Host "Error parsing json. File ignored" -ForegroundColor Yellow
        }
        Insert_YourTable -json $json
    }
}
catch {
    throw    
}

💡 重点语句解释

$connectionString = "Server=xxx.xxx.xxx.xxx;Database=xxx;Integrated Security=false;User ID=xxx;Password =xxx;"
通过powershell连接数据库，需要指定数据库服务器的ip，数据库名称，用户名和密码

$json = Get-Content $jsonFile.FullName -Encoding oem | ConvertFrom-Json
json 格式文件编码是 ansi，读取时不能采用默认编码（utf8）。json 字符串转换 json 字典只能是 utf8 编码。
此处读取文件编码格式采用 MS-DOS 系统默认编码(oem: Uses the default encoding for MS-DOS and console programs)

4️⃣ 数据库验证

三、反思总结

powershell 功能很强大，作为windows系统内置 shell 无疑是性能最好的。

大胆假设，小心求证。

四、参考引用

Import an entire directory of json files with SQL Server 2016