Fetch a whole row from SQL Server with PowerShell

Question

So I use ExecuteScalar() to get one value from a table. But how can I get multiple values, i.e. values from multiple columns.

$connectionString = "Data Source=ServerName;Initial Catalog=DB_Name; Integrated Security=SSPI" 
$connection = New-Object -TypeName System.Data.SqlClient.SqlConnection($connectionString)
$query = "select col1, col2, col3 from table where col1= x;" 
$command = New-Object -TypeName System.Data.SqlClient.SqlCommand($query, $connection)
$connection.Open()
$NewBatchID = $command.ExecuteScalar() 
$connection.Close()

Dan Guzman · Accepted Answer · 2020-11-04 13:44:24Z

4

One method, using .NET objects in PowerShell, is with a SqlDataAdapter. This can load multiple rows and columns into a DataTable for subsequent use.

This example assumes a single row is returned:

$connectionString = "Data Source=ServerName;Initial Catalog=DB_Name; Integrated Security=SSPI" 
$query = "select col1, col2, col3 from table where col1= x;"

$sqlDataAdapter = New-Object -TypeName System.Data.SqlClient.SqlDataAdapter($query, $connectionString)
$dataTable = New-Object -TypeName System.Data.DataTable
[void]$sqlDataAdapter.Fill($dataTable)
$col1 = $dataTable.Rows[0]["col1"]
$col2 = $dataTable.Rows[0]["col2"]
$col3 = $dataTable.Rows[0]["col3"]

answered Nov 4, 2020 at 13:44

Dan Guzman

46.9k3 gold badges52 silver badges77 bronze badges

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

17 Comments

Dan Guzman Over a year ago

A SQL time returns a .NET Timespan, IIRC. You can use ToString() to get the value formatted as hours, minutes, seconds. fractional seconds: $col1.ToString() or use the Timespan properties individually, like $col1.Hours.

Dan Guzman Over a year ago

@David, the SqlDataAdapter takes care of the low-level plumbing and processing. It instantiates a connection object using the specified connection string, creates a command object with the specified query, opens the connection, executes the query with ExecuteReader, loads the data table with SqlDataReader.Read() and finally closes the connection. You could do it all yourself like the code in the answer from @PalleDue.

Dan Guzman Over a year ago

The syntax one uses for the same .NET objects varies by language. Being familiar with C#, I tend to use the C#-like syntax, such as $sqlDataAdapter = New-Object System.Data.SqlClient.SqlDataAdapter($query, $connectionString). You can even throw a semi-colon at the end for eye candy ;-)

Dan Guzman Over a year ago

One note is that one needs to invoke static methods and properties differently in PS, by enclosing the class name followed by 2 colons. For example, [DateTime]::Now instead of DateTime::Now like one would in C#. A lot takes getting used to compared to a compiled .NET language.

Dan Guzman Over a year ago

@xhr489, yes. Instead of System.Data.SqlClient.SqlDataAdapter, use the equivalent object of the System.Data.Odbc namespace object System.Data.Odbc.OdbcDataAdapter and change the connection string for the ODBC driver you are using (keywords vary by driver). SQL Server example (e.g. "Driver={ODBC Driver 18 for SQL Server};Server=ServerName;Database=DB_Name;Trusted_Connection=Yes").

|

Palle Due · Accepted Answer · 2020-11-04 13:55:45Z

You can also use a DataReader:

$connectionString = "Data Source=ServerName;Initial Catalog=DB_Name; Integrated Security=SSPI" 
$connection = New-Object -TypeName System.Data.SqlClient.SqlConnection($connectionString)
$query = "select col1, col2, col3 from table where col1= x;" 
$command = New-Object -TypeName System.Data.SqlClient.SqlCommand($query, $connection)
$connection.Open()

$dataReader = $command.ExecuteReader()
$fieldCount = $dataReader.FieldCount
while ($dataReader.Read()) 
{
    for ($i = 0; $i -lt $fieldCount; $i++) 
    {
        Write-Host "$($dataReader.GetName($i)) is $($dataReader.GetValue($i))." 
    }
}

$connection.Close()

Personally I dislike both SqlDataAdapter and DataReaders because of all the boilerplate code. I think it should have been done in a more elegant way.

Collectives™ on Stack Overflow

Fetch a whole row from SQL Server with PowerShell

2 Answers 2

17 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

17 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related