How Can I Execute SQL Server Queries Using PowerShell?

Executing SQL Server Queries Using PowerShell

When working with SQL Server databases, it's often necessary to execute queries directly from the command line. PowerShell provides a powerful way to achieve this, allowing you to manage your database operations efficiently.

Implementing Query Execution with PowerShell

To execute a query on a SQL Server instance using PowerShell, you can utilize the System.Data.SqlClient namespace. Here's a step-by-step function that will facilitate this process:

function Invoke-SQL {
    param(
        [string] $dataSource = ".\SQLEXPRESS",
        [string] $database = "MasterData",
        [string] $sqlCommand = $(throw "Please specify a query.")
      )

    $connectionString = "Data Source=$dataSource; " +
            "Integrated Security=SSPI; " +
            "Initial Catalog=$database"

    $connection = new-object system.data.SqlClient.SQLConnection($connectionString)
    $command = new-object system.data.sqlclient.sqlcommand($sqlCommand,$connection)
    $connection.Open()
    
    $adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command
    $dataset = New-Object System.Data.DataSet
    $adapter.Fill($dataSet) | Out-Null
    
    $connection.Close()
    $dataSet.Tables
}

Usage

To utilize this function, simply specify the data source, database, and SQL command as parameters:

$results = Invoke-SQL -DataSource ".\SQLEXPRESS" -Database "Northwind" -SqlCommand "SELECT * FROM Customers"

The results of the query will be stored in the $results variable as a dataset. You can then access the individual tables and rows as needed.

Additional Information

• This function leverages the .NET System.Data.SqlClient namespace to enable direct SQL Server query execution.
• The Integrated Security=SSPI parameter in the connection string uses the current Windows authentication credentials to connect to the database.
• The function uses a SqlDataAdapter to retrieve the results of the query and populate a DataSet.
• The Out-Null command at the end of the $adapter.Fill call suppresses the display of output from the data filling process.

The above is the detailed content of How Can I Execute SQL Server Queries Using PowerShell?. For more information, please follow other related articles on the PHP Chinese website!