Can I tell if an ado.net DbCommand is a query or not (before executing it)

Question

I am trying to write a Powershell script to run a general SQL command against a database. The idea is that Run-SQL "select ..." will run the SQL text against the currently open database. If the SQL statement is a query, it should return a DataTable. If it is a non-query (DDL or DML) it should return nothing ($null).

In order to do this, I need to know which method (ExecuteReader or ExecuteNonQuery) to execute against the command. Is there a way to determine this? (I'm happy to prepare the command if that helps).

As an alternative, I can add a -query argument to be supplied by the user, which distinguishes the two cases, but as a potential user, I'd find this annoying (as, in my view, I have already said whether it's a query by the SQL I used, why say again?)

My key use is for Oracle databases, so an Oracle-specific answer is OK with me, although I'd prefer something generic.

Answer 1

I think you could just use ExecuteReader whether it's a query or not. It may be overkill but in some quick experiments with doing an UPDATE ($reader returns nothing) and a COUNT ($reader[0] outputs scalar result) - it just seems to work.

$connstr = "server=.\SQLEXPRESS;Database=AdventureWorks;" + 
           "Integrated Security=true;Persist Security Info=False"

$conn = new-object System.Data.SqlClient.SqlConnection $connstr    
#$query = "UPDATE Production.Product SET Name = 'ACME' WHERE Name = 'Blade'"
$query = "SELECT Count(*) FROM Production.Product"

$cmd = new-object System.Data.SqlClient.SqlCommand $query,$conn
$conn.Open()
try
{
    $reader = $cmd.ExecuteReader()
    while ($reader.Read())
    {
        $reader[0]
    }
}
finally
{
    $conn.Dispose()
}

Answer 2

As an alternative to what Keith said you could try

$sql = 'Select count(1) From SomeTable;'
$sql = $sql.TrimStart(' ')
if ($sql -match "^select") { Write-Host 'ExecuteReader' }
else { Write-Host 'ExecuteNonQuery'}

The trim is there in case the SQL command has a leading space since $sql = ' select ' won't match "^select"