SQLdataAdapter 在 PowerShell 中不起作用

Question

请找到向数据库发出选择查询的代码。 我正在将输出加载到一个不起作用的表中 选择查询很好，因为我在数据库中运行相同的查询并获得 1 行作为输出。

$SqlQuery = "select InputFiles,OutputFiles from $mastTableNm where JobName = '$jobname'"; 
$sqloutqryreader = MsSqlQueryExecutor $SqlQuery $logfile 'SELECT'
Add-Content -Value "$TimeinSec Log: Reading data from sql reader" -Path $logfile    
$mstrtab = new-object System.Data.DataTable 
$mstrtab.Load($sqloutqryreader) 
echo $mstrtab
ForEach($mstrjobrw in $mstrtab) 
{
Add-Content -Value "$TimeinSec Log: Reading data from sql reader $mstrjobrw.InputFiles $mstrjobrw.OutputFiles " -Path $logfile                                      
}

下面是执行查询并返回适配器的函数。

function Global:MsSqlQueryExecutor(
$SqlQuery,
$logfile,
$QueryType
)
{   
$Global:sqloutput = $Null
try
{
# make sure that the output from the following code block does not pollute return value                           
$Null = @(    
Add-Content -Value "$TimeinSec Log: Setting up the sql query to execute" -Path $logfile
$SQLUsername = "aa"
$SQLPassword = "aa"
$Database = "aa"
$SQLServer = "aa.aa.aa.windows.net"
# Define the connection to the SQL Database 
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=$SQLServer;Database=$Database;User ID=$SQLUsername;Password=$SQLPassword;Trusted_Connection=False;Encrypt=True;Connection Timeout=5000;"             
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand                           
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
$SqlCmd.CommandType = [System.Data.CommandType]::Text
$SqlCmd.CommandTimeout = 0                      
Add-Content -Value "$TimeinSec Log: Preparation to execute query: $SqlQuery is completed" -Path $logfile
if ($SqlConnection.State -eq [System.Data.ConnectionState]'Closed') 
{
$SqlConnection.Open()
}
if ($QueryType -eq 'SELECT')
{
$adp = New-Object System.Data.SqlClient.SqlDataAdapter $SqlCmd
$dtst = New-Object System.Data.DataSet
$adp.Fill($dtst)
$Global:sqloutput = $dtst.Tables[0]
Add-Content -Value "$TimeinSec Log: Execution of the $QueryType query: $SqlQuery completed successfully." -Path $logfile
}
else
{
$Global:sqloutput = $SqlCmd.ExecuteReader()
Add-Content -Value "$TimeinSec Log: Execution of the $QueryType query: $SqlQuery completed successfully." -Path $logfile
}
)
return $Global:sqloutput   
}
catch
{               
Add-Content -Value "$TimeinSec Error: Failed to Query: $SqlQuery" -Path $logfile
Add-Content -Value $_.Exception.Message -Path $logfile
EXIT                    
}   
finally
{
$SqlConnection.Close()
$SqlConnection.Dispose();
Add-Content -Value "$TimeinSec Cleanup: Connection is disposed" -Path $logfile  
}
}

表未加载。

我尝试了另一种解决方法，但也不起作用。

请找到该问题的链接

PowerShell 中 sqldatareader 中的 read(( 方法无法使用 while 循环

Answer 1

你能先尝试使用简单的代码片段来填充数据表吗？查看基本查询是否返回任何数据，

$jobname = "<jobname>"
$mastTableNm = "<tablename>"
$sqlConn = New-Object System.Data.SqlClient.sqlConnection "<Your Connection String Here>";
$sqlConn.Open();
$sqlCommand = $sqlConn.CreateCommand();
$sqlCommand.CommandText = "select InputFiles,OutputFiles from $mastTableNm where JobName = '$jobname'";
Write-Host $sqlCommand.CommandText;
$result = $sqlCommand.ExecuteReader();
$dtTable = New-Object System.Data.DataTable;
$dtTable.Load($result);

Answer 2

问题在于返回。Powershell有一些烦人的返回行为。我使用了一种解决方法来使我的代码正常工作。我没有返回，而是使用全局变量来初始化数据表。然后在我需要的代码中访问此全局变量。

function Global:MsSqlQueryExecutor(
$SqlQuery,
$logfile,
$QueryType
)
{   
try
{
# make sure that the output from the following code block does not pollute return value                           
$Null = @(    
Add-Content -Value "$TimeinSec Log: Setting up the sql query to execute" -Path $logfile
$SQLUsername = "aaa"
$SQLPassword = "aaa"
$Database = "aaa"
$SQLServer = "aaat"
# Define the connection to the SQL Database 
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=$SQLServer;Database=$Database;User ID=$SQLUsername;Password=$SQLPassword;Trusted_Connection=False;Encrypt=True;Connection Timeout=5000;"             
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand                           
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
$SqlCmd.CommandType = [System.Data.CommandType]::Text
$SqlCmd.CommandTimeout = 0                      
Add-Content -Value "$TimeinSec Log: Preparation to execute query: $SqlQuery is completed" -Path $logfile
if ($SqlConnection.State -eq [System.Data.ConnectionState]'Closed') 
{
$SqlConnection.Open()
}
if ($QueryType -eq 'SELECT')
{
$adp = New-Object System.Data.SqlClient.SqlDataAdapter $SqlCmd
$dtst = New-Object System.Data.DataSet
$adp.Fill($dtst)
$Global:sqlexecoutput = $dtst.Tables[0]
Add-Content -Value "$TimeinSec Log: Execution of the $QueryType query: $SqlQuery completed successfully." -Path $logfile
}
else
{
$Global:sqlexecoutput = $SqlCmd.ExecuteReader()
Add-Content -Value "$TimeinSec Log: Execution of the $QueryType query: $SqlQuery completed successfully." -Path $logfile
}
)                
}
catch
{               
Add-Content -Value "$TimeinSec Error: Failed to Query: $SqlQuery" -Path $logfile
Add-Content -Value $_.Exception.Message -Path $logfile
EXIT                    
}   
finally
{
$SqlConnection.Close()
$SqlConnection.Dispose();
Add-Content -Value "$TimeinSec Cleanup: Connection is disposed" -Path $logfile  
}
}