Link to home
Create AccountLog in
Avatar of whorsfall
whorsfallFlag for Australia

asked on

T-SQL Select via powershell

Hi,

I am trying to work out got to make a generic type routine that I can run SQL command
to select form a database in powershell.

Specifically I am tyring to code it so I don't have to hard code each column name
and value to display.

Is there a way I can use powershell to put the output into a powershell custom
object easily.

Here is the code I am starting with below.

So I am wanting to make work out some powershell code that will take
the output for the SQLReader and put the result set into an easy
object to manipulate.

It seems that the line:

$Counter = $sqlReader.FieldCount

Seems to cause the rest of the code to fail.
Would would be good is make the code work below.
What would be 'ideal' (top points) is to make the
result set into a nice powershell table / object

e.g.

Name    Age     Sex
====    ====    ====
Ward    44      Male
Jenny   33      Female

So if there is a better way to let me achive my ideal
please let me know.

Thanks,

Ward.

$connection_string = "server=.\SQLEXPRESS;database=SMS_P01;Integrated Security=sspi"

$sqlConnection = new-object System.Data.SqlClient.SqlConnection $connection_string
$sqlConnection.Open()

 
$sql = "select * from Employees"

$sqlCommand = $sqlConnection.CreateCommand()
$sqlCommand.CommandText = $sql

$sqlReader = $sqlCommand.ExecuteReader()

$Counter = $sqlReader.FieldCount
while ($sqlReader.Read()) 
	{	
	for ($i = 0; $i -lt $Counter; $i++) 
		{
			@{ $sqlReader.GetName($i) = $sqlReader.GetValue($i); 
		}
	}
}

$sqlConnection.Close()

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Barry Cunney
Barry Cunney
Flag of Ireland image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of YZlat
What error are you getting?