使用ODBCDataAdapter加载多结果集时未加载全部列的问题排查

使用ODBC驱动执行包含多条SELECT语句的SQL脚本，生成多个结果集，但加载至DataSet的结果不符合预期。填充DataAdapter后DataSet内容如下：

ReportType                     CreatedDate            CountofFirmCreated
----------                     -----------            ------------------
Firms Created                  27/03/2023 12:00:00 am                 14
Firms Created                  28/03/2023 12:00:00 am                 15
Firms Created                  29/03/2023 12:00:00 am                 10
Firms Created                  30/03/2023 12:00:00 am                 20
Firms Created                  31/03/2023 12:00:00 am                 22
Firms Created                  1/04/2023 12:00:00 am                  24
Firms Created                  2/04/2023 12:00:00 am                  39
Firms Created                  3/04/2023 12:00:00 am                  40
Firms Created                  4/04/2023 12:00:00 am                  73
Firms Created                  5/04/2023 12:00:00 am                  91
Firms: Users Invited
Firms: Users Invited
Firms: Users Invited
Firms: Users Invited
Firms: Users Invited
Firms: Users Invited
Firms: Users Invited
Firms: Users Invited
Firms: Users Invited
Firms: Users Invited

可见第一个结果集加载正常，但后续所有结果集仅加载一列数据（所有结果集均含三列，仅列名不同，共6个结果集）。相关代码如下：

$connectionString = "Driver={ODBC Driver 17 for SQL Server};Server=$servername;Database=$database;UID=$username;Password=$password;Authentication=ActiveDirectoryInteractive;MultipleActiveResultSets=True"

# Create and open SQL connection
$connection = New-Object System.Data.Odbc.OdbcConnection
$connection.ConnectionString = $connectionString
$connection.Open()

# Create a SQL command object
$command = New-object System.Data.Odbc.OdbcCommand($script, $connection)

$ds = New-Object system.Data.DataSet

$adapter = New-Object System.Data.Odbc.OdbcDataAdapter $command
$adapter.FillSchema($ds, [System.Data.SchemaType]::Source)
$adapter.Fill($ds) | Out-Null
$ds.Tables

问题出在FillSchema的使用逻辑上：当执行包含多结果集的SQL脚本时，FillSchema只会读取第一个结果集的架构信息，并将该架构强制应用到后续自动创建的所有DataTable中。后续结果集的列名与第一个不匹配，导致DataSet仅保留了能匹配的列（比如此处的ReportType），其余列被丢弃。

快速修正方案 #

直接移除FillSchema调用，仅使用Fill方法即可让DataSet自动为每个结果集生成匹配的架构：

$connectionString = "Driver={ODBC Driver 17 for SQL Server};Server=$servername;Database=$database;UID=$username;Password=$password;Authentication=ActiveDirectoryInteractive;MultipleActiveResultSets=True"

$connection = New-Object System.Data.Odbc.OdbcConnection
$connection.ConnectionString = $connectionString
$connection.Open()

$command = New-object System.Data.Odbc.OdbcCommand($script, $connection)

$ds = New-Object system.Data.DataSet

$adapter = New-Object System.Data.Odbc.OdbcDataAdapter $command
# 移除FillSchema调用，让Fill自动处理多结果集的架构
$adapter.Fill($ds) | Out-Null
$ds.Tables

手动控制架构的方案 #

如果需要更精确地控制每个结果集的架构，可以通过DataReader遍历每个结果集，手动创建并填充DataTable：

$connectionString = "Driver={ODBC Driver 17 for SQL Server};Server=$servername;Database=$database;UID=$username;Password=$password;Authentication=ActiveDirectoryInteractive;MultipleActiveResultSets=True"

$connection = New-Object System.Data.Odbc.OdbcConnection
$connection.ConnectionString = $connectionString
$connection.Open()

$command = New-object System.Data.Odbc.OdbcCommand($script, $connection)
$reader = $command.ExecuteReader()

$ds = New-Object system.Data.DataSet

# 遍历所有结果集，为每个结果集单独创建DataTable
do {
    $table = New-Object System.Data.DataTable
    $table.Load($reader)
    $ds.Tables.Add($table)
} while ($reader.NextResult())

$connection.Close()
$ds.Tables

这种方式会为每个结果集独立加载完整的架构和数据，确保所有列都能正确填充。

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