sqldataadapter在PowerShell中不起作用。

huangapple
go评论102阅读模式
英文:

sqldataadapter is not working in powershell

问题

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

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

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

  1. function Global:MsSqlQueryExecutor(
  2.     $SqlQuery,
  3.     $logfile,
  4.     $QueryType
  5.     )
  6. {	
  7.     $Global:sqloutput = $Null
  8.     try
  9.     {
  10.         # 确保以下代码块的输出不会污染返回值							
  11.         $Null = @(	
  12.             Add-Content -Value "$TimeinSec Log: Setting up the sql query to execute" -Path $logfile
  14.             $SQLUsername = "aa"
  15.             $SQLPassword = "aa"
  16.             $Database = "aa"
  17.             $SQLServer = "aa.aa.aa.windows.net"
  19.             # 定义与 SQL 数据库的连接 
  20.             $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
  21.             $SqlConnection.ConnectionString = "Server=$SQLServer;Database=$Database;User ID=$SQLUsername;Password=$SQLPassword;Trusted_Connection=False;Encrypt=True;Connection Timeout=5000;"			   
  23.             $SqlCmd = New-Object System.Data.SqlClient.SqlCommand							
  24.                         
  25.             $SqlCmd.CommandText = $SqlQuery
  26.             $SqlCmd.Connection = $SqlConnection
  27.             $SqlCmd.CommandType = [System.Data.CommandType]::Text
  28.             $SqlCmd.CommandTimeout = 0						
  30.             Add-Content -Value "$TimeinSec Log: Preparation to execute query: $SqlQuery is completed" -Path $logfile
  32.             if ($SqlConnection.State -eq [System.Data.ConnectionState]'Closed') 
  33.             {
  34.                 $SqlConnection.Open()
  35.             }
  37.             if ($QueryType -eq 'SELECT')
  38.             {
  39.                 $adp = New-Object System.Data.SqlClient.SqlDataAdapter $SqlCmd
  40.                 $dtst = New-Object System.Data.DataSet
  41.                 $adp.Fill($dtst)
  42.                 $Global:sqloutput = $dtst.Tables[0]
  43.                 Add-Content -Value "$TimeinSec Log: Execution of the $QueryType query: $SqlQuery completed successfully." -Path $logfile
  44.             }
  45.             else
  46.             {
  47.                 $Global:sqloutput = $SqlCmd.ExecuteReader()
  48.                 Add-Content -Value "$TimeinSec Log: Execution of the $QueryType query: $SqlQuery completed successfully." -Path $logfile
  49.             }
  50.                                         
  51.         )
  52.         return $Global:sqloutput	
  53.     }
  54.     catch
  55.     {				
  56.         Add-Content -Value "$TimeinSec Error: Failed to Query: $SqlQuery" -Path $logfile
  57.         Add-Content -Value $_.Exception.Message -Path $logfile
  58.         EXIT					
  59.     }	
  60.     finally
  61.     {
  62.         $SqlConnection.Close()
  63.         $SqlConnection.Dispose();
  64.         Add-Content -Value "$TimeinSec Cleanup: Connection is disposed" -Path $logfile	
  65.     }
  66. }

表格没有加载。

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

请查看这个问题的链接：

https://stackoverflow.com/questions/59611471/read-method-inside-sqldatareader-in-powershell-is-not-working-with-while-loop?noredirect=1#comment105386383_59611471

英文:

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.

  1. $SqlQuery = "select InputFiles,OutputFiles from $mastTableNm where JobName = '$jobname'"; 
  3. 				$sqloutqryreader = MsSqlQueryExecutor $SqlQuery $logfile 'SELECT'
  4. 				
  5. 				Add-Content -Value "$TimeinSec Log: Reading data from sql reader" -Path $logfile	
  6. 				
  7. 				$mstrtab = new-object System.Data.DataTable 
  8. 				$mstrtab.Load($sqloutqryreader) 
  9. 				echo $mstrtab
  10. 				
  11. 				ForEach($mstrjobrw in $mstrtab) 
  12. 				{
  13. 					Add-Content -Value "$TimeinSec Log: Reading data from sql reader $mstrjobrw.InputFiles $mstrjobrw.OutputFiles " -Path $logfile										
  14. 				}

Following is the function that executes the query and returns the adapter.

  1. function Global:MsSqlQueryExecutor(
  2. 						$SqlQuery,
  3. 						$logfile,
  4. 						$QueryType
  5. 						)
  6. 	{	
  7. 		$Global:sqloutput = $Null
  8. 			try
  9. 				{
  10. 				  # make sure that the output from the following code block does not pollute return value							
  11. 				  $Null = @(	
  12. 						Add-Content -Value "$TimeinSec Log: Setting up the sql query to execute" -Path $logfile
  13. 					
  14. 						$SQLUsername = "aa"
  15. 						$SQLPassword = "aa"
  16. 						$Database = "aa"
  17. 						$SQLServer = "aa.aa.aa.windows.net"
  19. 						# Define the connection to the SQL Database 
  20. 						$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
  21. 						$SqlConnection.ConnectionString = "Server=$SQLServer;Database=$Database;User ID=$SQLUsername;Password=$SQLPassword;Trusted_Connection=False;Encrypt=True;Connection Timeout=5000;"			   
  22. 					 
  23. 						$SqlCmd = New-Object System.Data.SqlClient.SqlCommand							
  24. 								
  25. 						$SqlCmd.CommandText = $SqlQuery
  26. 						$SqlCmd.Connection = $SqlConnection
  27. 						$SqlCmd.CommandType = [System.Data.CommandType]::Text
  28. 						$SqlCmd.CommandTimeout = 0						
  29. 						
  30. 						Add-Content -Value "$TimeinSec Log: Preparation to execute query: $SqlQuery is completed" -Path $logfile
  31. 						
  32. 						if ($SqlConnection.State -eq [System.Data.ConnectionState]'Closed') 
  33. 							{
  34. 								$SqlConnection.Open()
  35. 							}
  36. 						
  37. 						if ($QueryType -eq 'SELECT')
  38. 						{
  39. 							$adp = New-Object System.Data.SqlClient.SqlDataAdapter $SqlCmd
  40. 							$dtst = New-Object System.Data.DataSet
  41. 							$adp.Fill($dtst)
  42. 							$Global:sqloutput = $dtst.Tables[0]
  43. 							Add-Content -Value "$TimeinSec Log: Execution of the $QueryType query: $SqlQuery completed successfully." -Path $logfile
  44. 						}
  45. 						else
  46. 						{
  47. 							$Global:sqloutput = $SqlCmd.ExecuteReader()
  48. 							Add-Content -Value "$TimeinSec Log: Execution of the $QueryType query: $SqlQuery completed successfully." -Path $logfile
  49. 						}
  50. 												
  51. 					)
  52. 				 return $Global:sqloutput	
  53. 				}
  54. 			catch
  55. 				{				
  56. 					Add-Content -Value "$TimeinSec Error: Failed to Query: $SqlQuery" -Path $logfile
  57. 					Add-Content -Value $_.Exception.Message -Path $logfile
  58. 					EXIT					
  59. 				}	
  60. 			finally
  61. 				{
  62. 					$SqlConnection.Close()
  63. 					$SqlConnection.Dispose();
  64. 					Add-Content -Value "$TimeinSec Cleanup: Connection is disposed" -Path $logfile	
  65. 				}
  66. 					
  67. 	}

The table is not getting loaded.

I tried another workaround which is also not working.

Please find the link to that question

https://stackoverflow.com/questions/59611471/read-method-inside-sqldatareader-in-powershell-is-not-working-with-while-loop?noredirect=1#comment105386383_59611471

答案1

得分: 1

你可以首先尝试使用ExecuteReader()方法来填充数据表的简单代码片段，看看基本查询是否返回任何数据或不返回。

  1. $jobname = "<jobname>"
  2. $mastTableNm = "<tablename>"
  3. $sqlConn = New-Object System.Data.SqlClient.sqlConnection "<Your Connection String Here>"
  4. $sqlConn.Open()
  5. $sqlCommand = $sqlConn.CreateCommand()
  6. $sqlCommand.CommandText = "select InputFiles, OutputFiles from $mastTableNm where JobName = '$jobname'"
  7. Write-Host $sqlCommand.CommandText
  8. $result = $sqlCommand.ExecuteReader()
  9. $dtTable = New-Object System.Data.DataTable
  10. $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,

  1. $jobname = &quot;&lt;jobname&gt;&quot;
  2. $mastTableNm = &quot;&lt;tablename&gt;&quot;
  3. $sqlConn = New-Object System.Data.SqlClient.sqlConnection &quot;&lt;Your Connection String Here&gt;&quot;;
  4. $sqlConn.Open();
  5. $sqlCommand = $sqlConn.CreateCommand();
  6. $sqlCommand.CommandText = &quot;select InputFiles,OutputFiles from $mastTableNm where JobName = &#39;$jobname&#39;&quot;;
  7. Write-Host $sqlCommand.CommandText;
  8. $result = $sqlCommand.ExecuteReader();
  9. $dtTable = New-Object System.Data.DataTable;
  10. $dtTable.Load($result);

答案2

得分: 0

以下是您要翻译的部分：

问题出在返回值上。PowerShell有一些让人讨厌的返回行为。我使用了一个解决方法来使我的代码工作。我用一个全局变量来初始化数据表，而不是使用返回值。然后在需要的代码中访问这个全局变量。

  1. function Global:MsSqlQueryExecutor(
  2.                         $SqlQuery,
  3.                         $logfile,
  4.                         $QueryType
  5.                         )
  6.     {   
  7.             try
  8.                 {
  9.                   # 确保以下代码块的输出不会污染返回值							
  10.                   $Null = @(    
  11.                         Add-Content -Value " $TimeinSec 日志：设置要执行的SQL查询" -Path $logfile
  12.                     
  13.                         $SQLUsername = "aaa"
  14.                         $SQLPassword = "aaa"
  15.                         $Database = "aaa"
  16.                         $SQLServer = "aaat"
  17.     
  18.                         # 定义与SQL数据库的连接
  19.                         $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
  20.                         $SqlConnection.ConnectionString = "Server=$SQLServer;Database=$Database;User ID=$SQLUsername;Password=$SQLPassword;Trusted_Connection=False;Encrypt=True;Connection Timeout=5000;"			   
  21.                      
  22.                         $SqlCmd = New-Object System.Data.SqlClient.SqlCommand                            
  23.                                 
  24.                         $SqlCmd.CommandText = $SqlQuery
  25.                         $SqlCmd.Connection = $SqlConnection
  26.                         $SqlCmd.CommandType = [System.Data.CommandType]::Text
  27.                         $SqlCmd.CommandTimeout = 0						
  28.                         
  29.                         Add-Content -Value " $TimeinSec 日志：准备执行查询：$SqlQuery 已完成" -Path $logfile
  30.                         
  31.                         if ($SqlConnection.State -eq [System.Data.ConnectionState]'Closed') 
  32.                             {
  33.                                 $SqlConnection.Open()
  34.                             }
  35.                         
  36.                         if ($QueryType -eq 'SELECT')
  37.                         {
  38.                             $adp = New-Object System.Data.SqlClient.SqlDataAdapter $SqlCmd
  39.                             $dtst = New-Object System.Data.DataSet
  40.                             $adp.Fill($dtst)
  41.                             $Global:sqlexecoutput = $dtst.Tables[0]
  42.                             Add-Content -Value " $TimeinSec 日志：$QueryType 查询的执行：$SqlQuery 已成功完成。" -Path $logfile
  43.                         }
  44.                         else
  45.                         {
  46.                             $Global:sqlexecoutput = $SqlCmd.ExecuteReader()
  47.                             Add-Content -Value " $TimeinSec 日志：$QueryType 查询的执行：$SqlQuery 已成功完成。" -Path $logfile
  48.                         }
  49.                                             
  50.                     )				 
  51.                 }
  52.             catch
  53.                 {				
  54.                     Add-Content -Value " $TimeinSec 错误：查询失败：$SqlQuery" -Path $logfile
  55.                     Add-Content -Value $_.Exception.Message -Path $logfile
  56.                     退出					
  57.                 }	
  58.             finally
  59.                 {
  60.                     $SqlConnection.Close()
  61.                     $SqlConnection.Dispose();
  62.                     Add-Content -Value " $TimeinSec 清理：连接已释放" -Path $logfile	
  63.                 }
  64.                     
  65.     }
英文:

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.

  1. function Global:MsSqlQueryExecutor(
  2. 						$SqlQuery,
  3. 						$logfile,
  4. 						$QueryType
  5. 						)
  6. 	{	
  7. 			try
  8. 				{
  9. 				  # make sure that the output from the following code block does not pollute return value							
  10. 				  $Null = @(	
  11. 						Add-Content -Value &quot;$TimeinSec Log: Setting up the sql query to execute&quot; -Path $logfile
  12. 					
  13. 						$SQLUsername = &quot;aaa&quot;
  14. 						$SQLPassword = &quot;aaa&quot;
  15. 						$Database = &quot;aaa&quot;
  16. 						$SQLServer = &quot;aaat&quot;
  18. 						# Define the connection to the SQL Database 
  19. 						$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
  20. 						$SqlConnection.ConnectionString = &quot;Server=$SQLServer;Database=$Database;User ID=$SQLUsername;Password=$SQLPassword;Trusted_Connection=False;Encrypt=True;Connection Timeout=5000;&quot;			   
  21. 					 
  22. 						$SqlCmd = New-Object System.Data.SqlClient.SqlCommand							
  23. 								
  24. 						$SqlCmd.CommandText = $SqlQuery
  25. 						$SqlCmd.Connection = $SqlConnection
  26. 						$SqlCmd.CommandType = [System.Data.CommandType]::Text
  27. 						$SqlCmd.CommandTimeout = 0						
  28. 						
  29. 						Add-Content -Value &quot;$TimeinSec Log: Preparation to execute query: $SqlQuery is completed&quot; -Path $logfile
  30. 						
  31. 						if ($SqlConnection.State -eq [System.Data.ConnectionState]&#39;Closed&#39;) 
  32. 							{
  33. 								$SqlConnection.Open()
  34. 							}
  35. 						
  36. 						if ($QueryType -eq &#39;SELECT&#39;)
  37. 						{
  38. 							$adp = New-Object System.Data.SqlClient.SqlDataAdapter $SqlCmd
  39. 							$dtst = New-Object System.Data.DataSet
  40. 							$adp.Fill($dtst)
  41. 							$Global:sqlexecoutput = $dtst.Tables[0]
  42. 							Add-Content -Value &quot;$TimeinSec Log: Execution of the $QueryType query: $SqlQuery completed successfully.&quot; -Path $logfile
  43. 						}
  44. 						else
  45. 						{
  46. 							$Global:sqlexecoutput = $SqlCmd.ExecuteReader()
  47. 							Add-Content -Value &quot;$TimeinSec Log: Execution of the $QueryType query: $SqlQuery completed successfully.&quot; -Path $logfile
  48. 						}
  49. 												
  50. 					)				 
  51. 				}
  52. 			catch
  53. 				{				
  54. 					Add-Content -Value &quot;$TimeinSec Error: Failed to Query: $SqlQuery&quot; -Path $logfile
  55. 					Add-Content -Value $_.Exception.Message -Path $logfile
  56. 					EXIT					
  57. 				}	
  58. 			finally
  59. 				{
  60. 					$SqlConnection.Close()
  61. 					$SqlConnection.Dispose();
  62. 					Add-Content -Value &quot;$TimeinSec Cleanup: Connection is disposed&quot; -Path $logfile	
  63. 				}
  64. 					
  65. 	}

huangapple
  • 本文由 发表于 2020年1月6日 23:21:00
  • 转载请务必保留本文链接：https://go.coder-hub.com/59614633.html
  • powershell
  • sqldataadapter
  • sqldatareader
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定