sqldataadapter is not working in powershell

Question

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

read() method inside sqldatareader in powershell is not working with while loop

is it throwing any exception? or just doesn't return anything? — Kundan
– Kundan, Commented Jan 6, 2020 at 15:41
@kundan its not throwing exception. It just doesn't return anything — TomG
– TomG, Commented Jan 7, 2020 at 4:34

Kundan · Accepted Answer · 2020-01-07 09:16:29Z

1

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);

answered Jan 7, 2020 at 9:16

Kundan

1,4252 gold badges16 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

TomG Over a year ago

I have found the issue. the problem is with return. somehow the value returned from function does not persist once the function call is over. The workaround is to use a global variable and use it once the function call is over.

TomG · Accepted Answer · 2020-01-08 08:18:21Z

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  
                }

    }

Collectives™ on Stack Overflow

sqldataadapter is not working in powershell

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related