Get count of rows from MSSQL database with Powershell

Question

I'm trying to get the number of rows from a Microsoft SQL database table in Powershell as an integer and am stuck peeling the figure out of the object Powershell is returning.

I have;

$SQLServer = "MSSQL01"   $SQLDBName = "TheTable" $SqlQuery = "select count(*) from cases where opened_date =< '2014-07-30'"

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True"

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand $SqlCmd.CommandText = $SqlQuery $SqlCmd.Connection = $SqlConnection

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $SqlAdapter.SelectCommand = $SqlCmd

$DataSet = New-Object System.Data.DataSet $SqlAdapter.Fill($DataSet)

$SqlConnection.Close()

Now $DataSet.Tables[0] in this instance returns an object like;

Table Name
-----------

45

How can I grab just the 45 as an int?

Look at ExecuteScalar instead of filling an adapter

Eris
– Eris

2014-07-31 08:28:16 +00:00
Commented Jul 31, 2014 at 8:28 — Eris
– Eris, Commented Jul 31, 2014 at 8:28

jessehouwing · Accepted Answer · 2016-10-06 18:11:04Z

3

Look at ExecuteScalar:

$SQLServer = "MSSQL01"   
$SQLDBName = "TheTable" 
$SqlQuery = "select count(*) from cases where opened_date =< '2014-07-30'"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection 
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand $SqlCmd.CommandText = $SqlQuery $SqlCmd.Connection = $SqlConnection
$SqlConnection.Open() 
$Rows= [Int32]$SqlCmd.ExecuteScalar()
$SqlConnection.Close()

edited Oct 6, 2016 at 18:11

answered Jul 31, 2014 at 8:35

jessehouwing

117k23 gold badges287 silver badges385 bronze badges

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

3 Comments

baralong Over a year ago

You should to remove $DataSet from the ExecuteScalar call. The method doesn't take a parameter and $DataSet isn't initialised in your example.

Elomis Over a year ago

Seems like I'm close but I get "ExecuteScalar requires an open and available Connection. The connection's current state is closed", your example code above doesn't differ from mine but for the executescalar v filling an adapter, why does the connection now not open?

jessehouwing Over a year ago

Updated. You need to explicitly open yourself.

Excalibur · Accepted Answer · 2016-10-19 18:06:52Z

2

Here's a version which provides two encapsulated functions, based on @jessehouwing's answer. This also handles an optional port, choice of TrustedAuth vs. User/Passwd Authentication, and an optional WHERE clause.

function createSqlConnection($sqlServer, $sqlPort, $dbName, $user, $password) {
    $sqlConnection = New-Object System.Data.SqlClient.sqlConnection
    if ($sqlPort) { $sqlServer = "$sqlServer,$sqlPort" }
    if ($user -and $password) {
        $sqlConnection.ConnectionString = "Server=$sqlServer; Database=$dbName; User Id=$user; Password=$password"
    } else {
        $sqlConnection.ConnectionString = "Server=$sqlServer; Database=$dbName; Integrated Security=True"
    }

    return $sqlConnection
}

function getRowCount($sqlConnection, $table, $where = "1=1") {
    $sqlQuery = "SELECT count(*) FROM $table WHERE $where"
    $sqlConnection.Open()
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand($sqlQuery, $sqlConnection)
    $row_count = [Int32] $SqlCmd.ExecuteScalar()
    $sqlConnection.Close()
    return $row_count
}

To use it:

$dbConn = createSqlConnection 'db-server' $null 'myDatabase' $user $password
$count = getRowCount $dbConn 'the_table'

answered Oct 19, 2016 at 18:06

Excalibur

3,3682 gold badges29 silver badges33 bronze badges

1 Comment

jessehouwing Over a year ago

Your optional where clause adds a nice SQL injection vulnerability to the function.

Raf · Accepted Answer · 2014-09-01 10:32:48Z

1

Using ExecuteScalar is a much better solution. If, for some reason, you want to stick with the DataSet use either:

$DataSet.Tables[0].'Table Name'

or

$DataSet.Tables[0] | Select-Object -ExpandProperty "Table Name"

edited Sep 1, 2014 at 10:32

answered Jul 31, 2014 at 10:46

Raf

10.2k1 gold badge34 silver badges42 bronze badges

Collectives™ on Stack Overflow

Get count of rows from MSSQL database with Powershell

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related