CODEWITHSUNDEEP
c#sqlpowershell
Andreas
Andreas

Reputation: 865

Return output from SQL Command in Powershell using c# classes

I am running a Powershell script that is able to execute SQL queries using the c# classes:

$MasterDatabaseConnection = New-Object System.Data.SqlClient.SqlConnection
$MasterDatabaseConnection.ConnectionString = "<Connectionstring>"
$MasterDatabaseConnection.Open()
#Create & Run query
$MasterDatabaseCommand = New-Object System.Data.SqlClient.SqlCommand
$MasterDatabaseCommand.Connection = $MasterDatabaseConnection
$MasterDatabaseCommand.CommandTimeout = 1200;
$MasterDatabaseCommand.CommandText = "select top 1 * from TestTable"
$Results = $MasterDatabaseCommand.ExecuteNonQuery()

$Results

However, How can I get the function ExecuteNonQuery() to return the actual output of the row I am fetching? It only returns the success or not of the query.

I cannot solve this by using other powershell-modules or c# programs.

Upvotes: 1

Views: 895

Answers (1)

Marc Gravell
Marc Gravell

Reputation: 1062865

ExecuteNonQuery doesn't return rows. The clue is the "non query" part (it is intended for state-change operations that do not yield rows - UPDATE, for example). You want ExecuteReader for row-based data, or ExecuteScalar for a single cell (the first column of the first row of the first grid). With ExecuteReader, a typical usage would be (in no specific language):

reader = command.ExecuteQuery()
while reader.Read()
    # look at reader reader.GetValues(), reader[index], reader.GetValue(column),
    # or reader.GetString(), reader.GetInt32(), etc

If the query could contain multiple grids, this extends to

reader = command.ExecuteQuery()
do
    while reader.Read()
        # the same per-grid code as before
while reader.NextResult()

(i.e. the per-row Read() calls preceed each row, and the per-grid NextResult() calls follow each grid consumed)

Note that all of the connection, the command, and the reader implement IDisposable, and should be disposed after use. If this is a simple batch script that will terminate entirely when exiting, you might get away with not doing this (relying on process tear-down instead).

Upvotes: 1

Related Questions