.Net SQL Server queries from PowerShell

Question

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?

marsze · Accepted Answer · 2020-09-22 17:32:54Z

1

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.

answered Sep 22, 2020 at 17:32

marsze

17.5k6 gold badges59 silver badges67 bronze badges

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

2 Comments

xhr489 Over a year ago

So '$command = New-Object -TypeName System.Data.SqlClient.SqlCommand($query, $connection)' does not do anything?

marsze Over a year ago

It creates the SqlCommand object that you use for adding parameters, executing SQL commands and returning the results. Have a look at the docs.

user1390375 · Accepted Answer · 2020-09-22 17:15:05Z

1

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

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

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

$dt | out-gridview

answered Sep 22, 2020 at 17:15

user1390375

7566 silver badges5 bronze badges

1 Comment

marsze Over a year ago

Your answer is not bad but maybe you should explain it.

Collectives™ on Stack Overflow

.Net SQL Server queries from PowerShell

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related