PowerShell.nu

The Get-SQL function retrieved data from SQL and presented it in a HashTable Array.

Let’s create a similar function that we can use when updating, inserting or deleting data from an SQL server.

function Set-SQL ([string]$Query,[string]$ConnString) {

if ($ConnString) {

	if($ConnString -match '"*"') {
		$ConnString = $ConnString.TrimStart('"')
		$ConnString = $ConnString.TrimEnd('"')
	}

} else {

	# Default ConnectionString
	$ConnString =
	"server=SQL;database=master;trusted_connection=true;"

}

$Connection = New-Object System.Data.SQLClient.SQLConnection

$Connection.ConnectionString = $ConnString
$Connection.Open()

$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $Connection
$Command.CommandText = $Query

return $Reader = $Command.ExecuteNonQuery()

$Connection.Close()

}

Examples on Running the Function

PS > Set-SQL -Query "INSERT INTO Region (RegionID,RegionDescription) VALUES ('5','Even More North')" `
-ConnString "server=Server;database=Northwind;trusted_connection=true;"

PS > Set-SQL "DELETE * FROM Region WHERE RegionID = '5'" `
-ConnString "server=Server;database=Northwind;trusted_connection=true;"

In a previous post I described a little about PowerShell and SQL, how to connect and how to handle different queries through PowerShell.

Now let’s put it all togheter and create a more handy function.

function Get-SQL ([string]$Query,[string]$ConnString) {

if ($ConnString) {

	if($ConnString -match '"*"') {
		$ConnString = $ConnString.TrimStart('"')
		$ConnString = $ConnString.TrimEnd('"')
	}

} else {

	# Default Connection String
	$ConnString =
	"server=ServerName;database=DbName;trusted_connection=true;"

}

$Connection = New-Object System.Data.SQLClient.SQLConnection

$Connection.ConnectionString = $ConnString
$Connection.Open()

$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $Connection
$Command.CommandText = $Query

$Reader = $Command.ExecuteReader()
$Counter = $Reader.FieldCount
while ($Reader.Read()) {
	$SQLObject = @{}
	for ($i = 0; $i -lt $Counter; $i++) {
		$SQLObject.Add(
			$Reader.GetName($i),
			$Reader.GetValue($i));
	}
	$SQLObject
}

$Connection.Close()

}

Examples on Running the Function:

PS > Get-SQL "SELECT * FROM Products"

PS > Get-SQL "SELECT * FROM Products" `
-Server "server=NewServer;database=DbName;trusted_connection=true;"

Working with Databases through PowerShell can be a little tricky since PowerShell doesn’t contain any SQL CmdLets. Let’s start connecting to our SQL database using the SQLConnection object:

PS > $Connection = New-Object System.Data.SQLClient.SQLConnection
PS > $Connection


StatisticsEnabled                : False
ConnectionString                 :
ConnectionTimeout                : 15
Database                         :
DataSource                       :
PacketSize                       : 8000
ServerVersion                    :
WorkstationId                    : Server
FireInfoMessageEventOnUserErrors : False
State                            : Closed
Site                             :
Container                        :

Next, we have to set the ConnectionString, this defines which DataBase that we want to connect to.
Note that the authentication method that is used in this example is integrated authentication. If you use non-integrated authentiacation, you can add “userid=username;password=password;” to the connection string.

Note that the database we will use in these examples is Northwind which is a sample database from Microsoft that you can download for free at Microsoft.com

PS > $Connection.ConnectionString =
"server=Server;database=Northwind;trusted_connection=true;"
PS > $Connection.ConnectionString


server=Server;database=Northwind;trusted_connection=true;

And when all is set, we open the Connection.

PS > $Connection.Open()

When you are finished using your database, you can close it with the close() method.

PS > $Connection.Close()

Now that we’ve opened a connection to our database, we can start with a couple of queries. The four basic queries are:

• SELECT
• INSERT
• UPDATE
• DELETE

When executing queries, use the SQLCommand Object. Note that if you use INSERT, UPDATE or DELETE, you won’t get a value returned so you should use the ExecuteNonQuery() method. In case of SELECT, you should use the ExecuteReader() method. Let’s start looking at the SELECT query:

Northwind contains a table called Products. So if we want to see all Columns in Products we would use “SELECT * from Products” to retrieve all columns and rows:

Note. In order to retrieve the data from our database, we have to use the ExecuteReader() method and
loop through it. Column Names are retrieved through .GetName() and values are retrieved through .GetValue()

PS > $Command = New-Object System.Data.SQLClient.SQLCommand
PS > $Command.Connection = $Connection
PS > $Command.CommandText = "SELECT * FROM Products"

PS > $Reader = $Command.ExecuteReader()
PS > $Counter = $Reader.FieldCount
while ($Reader.Read()) {
	for ($i = 0; $i -lt $Counter; $i++) {
		@{ $Reader.GetName($i) = $Reader.GetValue($i); }
	}
}

Name                           Value
----                           -----
ProductID                      1
ProductName                    Chai
SupplierID                     1
CategoryID                     1
QuantityPerUnit                10 boxes x 20 bags
UnitPrice                      18,0000
UnitsInStock                   39
UnitsOnOrder                   0
ReorderLevel                   10
Discontinued                   False
ProductID                      2
ProductName                    Chang
SupplierID                     1
CategoryID                     1
QuantityPerUnit                24 - 12 oz bottles
UnitPrice                      19,0000
UnitsInStock                   17
UnitsOnOrder                   40
ReorderLevel                   25
Discontinued                   False
ProductID                      3
ProductName                    Aniseed Syrup
SupplierID                     1
CategoryID                     2
QuantityPerUnit                12 - 550 ml bottles
UnitPrice                      10,0000

If we want a specific Row in the table, we can specify it in our SELECT query.

PS > $Command = New-Object System.Data.SQLClient.SQLCommand
PS > $Command.Connection = $Connection
PS > $Command.CommandText = "SELECT * FROM Products where ProductName = 'Röd kaviar'"

PS > $Reader = $Command.ExecuteReader()
PS > $Counter = $Reader.FieldCount
PS > while ($Reader.Read()) {
	for ($i = 0; $i -lt $Counter; $i++) {
		@{ $Reader.GetName($i) = $Reader.GetValue($i); }
	}
}

Name                           Value
----                           -----
ProductID                      73
ProductName                    Röd Kaviar
SupplierID                     17
CategoryID                     8
QuantityPerUnit                24 - 150 g jars
UnitPrice                      15,0000
UnitsInStock                   101
UnitsOnOrder                   0
ReorderLevel                   5
Discontinued                   False

And if we want to check out another table, all we have to do is change the SELECT query as shown below.

PS > $Command = New-Object System.Data.SQLClient.SQLCommand
PS > $Command.Connection = $Connection
PS > $Command.CommandText = "SELECT * FROM Region"
PS > $Reader = $Command.ExecuteReader()
PS > $Counter = $Reader.FieldCount
PS > while ($Reader.Read()) {
	for ($i = 0; $i -lt $Counter; $i++) {
		@{ $Reader.GetName($i) = $Reader.GetValue($i); }
	}
}


Name                           Value
----                           -----
RegionID                       1
RegionDescription              Eastern
RegionID                       2
RegionDescription              Western
RegionID                       3
RegionDescription              Northern
RegionID                       4
RegionDescription              Southern

Let’s check out the INSERT query. INSERT is used when you want to add new data to a table. First specify the Columns that you want to INSERT into and the specify the Values.

Let’s play a little with the Region table.

PS > $Command = New-Object System.Data.SQLClient.SQLCommand
PS > $Command.Connection = $Connection
PS > $Command.CommandText =
	"INSERT INTO Region (RegionID,RegionDescription) VALUES ('5','Even More North')"
PS > $Command.ExecuteNonQuery()

1

The return value is 1 since the row was affected.

Let’s check that the row really was affected by using a SELECT query:

PS > $Command = New-Object System.Data.SQLClient.SQLCommand
PS > $Command.Connection = $Connection
PS > $Command.CommandText = "SELECT * FROM Region where RegionID = '5'"
PS > $Reader = $Command.ExecuteReader()
PS > $Counter = $Reader.FieldCount
PS > while ($Reader.Read()) {
	for ($i = 0; $i -lt $Counter; $i++) {
		@{ $Reader.GetName($i) = $Reader.GetValue($i); }
	}
}


Name                           Value
----                           -----
RegionID                       5
RegionDescription              Even More North

The UPDATE query modifies existing data. It’s often used with a WHERE clause to limit the range of affect to the specified rows. Let’s UPDATE the row that we INSERTED above.

PS > $Command = New-Object System.Data.SQLClient.SQLCommand
PS > $Command.Connection = $Connection
PS > $Command.CommandText =
"UPDATE Region set RegionDescription = 'Go West' WHERE RegionID = '5'"

PS > $Command.ExecuteNonQuery()

1

And again, we check that our row was affected.

PS > $Command = New-Object System.Data.SQLClient.SQLCommand
PS > $Command.Connection = $Connection
PS > $Command.CommandText = "SELECT * FROM Region where RegionID = '5'"
PS > $Reader = $Command.ExecuteReader()
PS > $Counter = $Reader.FieldCount
PS > while ($Reader.Read()) {
	for ($i = 0; $i -lt $Counter; $i++) {
		@{ $Reader.GetName($i) = $Reader.GetValue($i); }
	}
}


Name                           Value
----                           -----
RegionID                       5
RegionDescription              Go West

DELETE will remove data from the database.

PS > $Command = New-Object System.Data.SQLClient.SQLCommand
PS > $Command.Connection = $Connection
PS > $Command.CommandText = "DELETE * FROM Region WHERE RegionID = '5'"
PS > $Command.ExecuteNonQuery()

1

And, finally, we check that the data really was deleted.

PS > $Command = New-Object System.Data.SQLClient.SQLCommand
PS > $Command.Connection = $Connection
PS > $Command.CommandText = "SELECT * FROM Region"
PS > $Reader = $Command.ExecuteReader()
PS > $Counter = $Reader.FieldCount
PS > while ($Reader.Read()) {
	for ($i = 0; $i -lt $Counter; $i++) {
		@{ $Reader.GetName($i) = $Reader.GetValue($i); }
	}
}


Name                           Value
----                           -----
RegionID                       1
RegionDescription              Eastern
RegionID                       2
RegionDescription              Western
RegionID                       3
RegionDescription              Northern
RegionID                       4
RegionDescription              Southern

Below is the Code used in this post.

$Connection = New-Object System.Data.SQLClient.SQLConnection
$Connection.ConnectionString =
  "server=Server;database=Northwind;trusted_connection=true;"
$Connection.Open()

$Query = "SELECT * FROM Products

$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $Connection
$Command.CommandText = $Query

$Reader = $Command.ExecuteReader()
$Counter = $Reader.FieldCount
while ($Reader.Read()) {
	for ($i = 0; $i -lt $Counter; $i++) {
		@{ $Reader.GetName($i) = $Reader.GetValue($i); }
	}
}