英文:
sqldataadapter is not working in powershell
问题
请查找发出数据库选择查询的代码部分。
我正在将输出加载到一个表中，但不起作用。
选择查询没问题，因为我在数据库中运行相同的查询并得到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
{
# 确保以下代码块的输出不会污染返回值
$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"
# 定义与 SQL 数据库的连接
$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
}
}
表格没有加载。
我尝试了另一种解决方法，也不起作用。
请查看这个问题的链接：
英文:
please find the code that issues a select query to data base.
I am loading the output to a table which is not working
The select query is fine as I run the same query in database and get 1 row as output.
$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
}
Following is the function that executes the query and returns the adapter.
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
}
}
The table is not getting loaded.
I tried another workaround which is also not working.
Please find the link to that question
答案1
得分: 1
你可以首先尝试使用ExecuteReader()方法来填充数据表的简单代码片段，看看基本查询是否返回任何数据或不返回。
$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)
这段代码用于执行数据库查询并将结果填充到数据表中。
英文:
Can you try with simple code snippet first with ExecuteReader() to populate the datatable? See if the basic query is returning any data or not,
$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);
答案2
得分: 0
以下是您要翻译的部分：
问题出在返回值上。PowerShell有一些让人讨厌的返回行为。我使用了一个解决方法来使我的代码工作。我用一个全局变量来初始化数据表，而不是使用返回值。然后在需要的代码中访问这个全局变量。
function Global:MsSqlQueryExecutor(
$SqlQuery,
$logfile,
$QueryType
)
{
try
{
# 确保以下代码块的输出不会污染返回值
$Null = @(
Add-Content -Value " $TimeinSec 日志：设置要执行的SQL查询" -Path $logfile
$SQLUsername = "aaa"
$SQLPassword = "aaa"
$Database = "aaa"
$SQLServer = "aaat"
# 定义与SQL数据库的连接
$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 日志：准备执行查询：$SqlQuery 已完成" -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 日志：$QueryType 查询的执行：$SqlQuery 已成功完成。" -Path $logfile
}
else
{
$Global:sqlexecoutput = $SqlCmd.ExecuteReader()
Add-Content -Value " $TimeinSec 日志：$QueryType 查询的执行：$SqlQuery 已成功完成。" -Path $logfile
}
)
}
catch
{
Add-Content -Value " $TimeinSec 错误：查询失败：$SqlQuery" -Path $logfile
Add-Content -Value $_.Exception.Message -Path $logfile
退出
}
finally
{
$SqlConnection.Close()
$SqlConnection.Dispose();
Add-Content -Value " $TimeinSec 清理：连接已释放" -Path $logfile
}
}
英文:
The issue was with return. Powershell has some annoying return behaviour. I used a workaround to make my code work. instead of return I used a global variable to initialize the datatable. This global variable was then accessed in the code where i need.
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
}
}
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论