How to get row count as an int using powershell and SQL query

Question

_{Wednesday, April 8, 2020 12:58 PM}

I have a script that should pull up the row count of a table as an int when I use | select -ExpandProperty "table Name I get an error"

Function Query($Query) {
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection 
$SqlConnection.ConnectionString = "Server=$Server;Initial Catalog=$Database;Integrated Security=SSPI" 
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand 
$SqlCmd.Connection = $SqlConnection 
$SqlCmd.CommandText = $Query 
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter 
$SqlAdapter.SelectCommand = $SqlCmd 
$DataSet = New-Object System.Data.DataSet 
$a=$SqlAdapter.Fill($DataSet)
$SqlConnection.Close() 
$DataSet.Tables[0] }

# the Query below is to obtain all the values from the table 
        $Result = Query "select count(*) from [dbo].[$Mytable] "  
         $Result

Column1

      2

How can I get to print $Result as an int without the column1 above?

Thanks a lot for the help 

All replies (6)

_{Wednesday, April 8, 2020 1:11 PM}

Please follow the rules of this forum. Edit your post and fix the code.  Code needs to be correctly formatted and posted so it can be read and copied.

• How to ask questions in a technical forum
• How to post code in Technet Forums

\(ツ)_/

_{Wednesday, April 8, 2020 1:12 PM}

Short answer: $result.Count1

You need to learn basic PowerShell where you would learn this in teh first chapter or the first hour of class.

\(ツ)_/

_{Wednesday, April 8, 2020 1:24 PM}

Thank you for the advice on how to post questions but the answer provide still bring me the same results.

what i'm trying to do in the Query is to get rid off the Column1 and print the answer as an int. 

Column1

      2

_{Wednesday, April 8, 2020 1:27 PM}

Thank you for the advice on how to post questions but the answer provide still bring me the same results.

what i'm trying to do in the Query is to get rid off the Column1 and print the answer as an int. 

Column1

      2

 

Please fix you original post and I will show you what is wrong.

\(ツ)_/

_{Wednesday, April 8, 2020 1:38 PM}

You still didn't format the code correctly and so it is readable.

$conn = [System.Data.SqlClient.SqlConnection]::New("Server=$Server;Initial Catalog=$Database;Integrated Security=SSPI")
$conn.Open()
$cmd = $conn.CreateCommand()
$cmd.CommandText = "select count(*) from [dbo].[$Mytable] "  
$rdr = $cmd.ExecuteReader()
$dt = [System.Data.DataTable]::new()
$dt.Load($rdr)
$conn.Close()
$dt.Column1

This code has been tested and always works.

\(ツ)_/

_{Wednesday, April 8, 2020 1:42 PM}

The following is the easy way to do this.

$dt = Invoke-SqlCmd -Query "select count(*) from [dbo].[$Mytable]" -ServerInstance $Server -Database $Database 
$dt.Column1

\(ツ)_/