CODEWITHSUNDEEP
.netsql-serverpowershellt-sql
xhr489
xhr489

Reputation: 2309

.Net SQL Server queries from PowerShell

So I have this connection

$connectionString = "Data Source=Something ;Initial Catalog=Something ;Integrated Security=SSPI" 
$connection = New-Object -TypeName System.Data.SqlClient.SqlConnection($connectionString)
$query = "select MAX(ID) from [dbo].[Table];" 
$command = New-Object -TypeName System.Data.SqlClient.SqlCommand($query, $connection)
$connection.Open()
$NewID = $command.ExecuteScalar() 
$connection.Close()

echo $NewID

And it works fine and does the job. Say I want to INSERT A ROW. Do I need the last 3 lines possibly with another method in $command.method()? In which line is the query actually executed?

Question 2: What method should I use if I want to get the whole result set to PowerShell?

Upvotes: 1

Views: 168

Answers (2)

marsze
marsze

Reputation: 17035

If you want to execute a query without a result (like an INSERT) you can use ExecuteNonQuery

$command.ExecuteNonQuery()

If you want to to return non-scalar results, use ExecuteReader.

Upvotes: 1

user1390375
user1390375

Reputation: 736

###...your code up to $NewID = ...

$rdr = $command.ExecuteReader(1)
#get-type $rdr

$dt = new-object system.data.datatable
$dt.load($rdr)

$dt | out-gridview

Upvotes: 1

Related Questions