How Can I Execute SQL Queries Directly from PowerShell?

Executing SQL Queries from PowerShell on Your Local Machine

PowerShell provides the ability to execute arbitrary SQL queries on a SQL Server instance. This can be done with just stock .NET and PowerShell. Here's a function that simplifies the 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

}

This function requires you to specify the data source (usually your machine name or .SQLEXPRESS), the database name, and the SQL command you want to execute.

Once you have this function defined, you can execute arbitrary queries from within PowerShell by calling Invoke-SQL with the appropriate parameters. For example, the following command will execute the query "SELECT * FROM Customers" on the "MyDatabase" database on the local SQL Server instance:

Invoke-SQL -database "MyDatabase" -sqlCommand "SELECT * FROM Customers"

This will return a dataset containing the results of the query, which you can then work with in PowerShell.

This function has been further enhanced into a PowerShell module available on GitHub: https://github.com/ChrisMagnuson/InvokeSQL. By installing this module, you can automatically load the Invoke-SQL function into your PowerShell environment and use it seamlessly without having to define it manually.

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