Connect SQL Server from PowerShell

In this post we will see how to connect local SQL server and select data from it. Lets review the script:

$conn = New-Object System.Data.SqlClient.SqlConnection
$conn.ConnectionString = “Server=MACHINE_NAME\SQL_INSTANCE_NAME;Database=DB_NAME;User ID=USERNAME;Password=PASSWORD;”

$sqlcmd = New-Object System.Data.SqlClient.SqlCommand
$sqlcmd.Connection = $conn
$sqlcmd.CommandText = "select top 10 * from dbo.Items"

$sqladapter = New-Object System.Data.SqlClient.SqlDataAdapter
$sqladapter.SelectCommand = $sqlcmd

$dataset = New-Object System.Data.DataSet

$sqladapter.Fill($dataset)
$conn.Close()

$dataset.Tables[0] | format-table -AutoSize

For C# programmers, this script is self-explanatory. First we defined our SQL connection string, you can place your actual Machine Name, SQL Instance Name, Database Name, User Name and Password. Then create SQLCommand and SQLAdapter objects with required values and get the result in DataSet variable.

To output our data table to console we use the command:
$DataSet.Tables[0] | format-table -AutoSize

The Format-Table cmdlet formats the output of a command as a table.
The Autosize parameter adjusts the column widths to minimize truncation

Here is sample output:

For the purpose of this demo, I created a test SQL table:

CREATE TABLE [dbo].[Items](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ItemCode] [varchar](10) NOT NULL,
    [ItemName] [varchar](50) NOT NULL,
 CONSTRAINT [PK_Items] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO