Working with Databases using PowerShell can be a little tricky from time to time. In this post we’ll take a look at how to work with database tables using .NET and we’ll also check out some CmdLets available. 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); }
}
}
SQL Server 2008 R2 includes two snapins, SqlServerCmdletSnapin100 and SqlServerProviderSnapin100.
The SqlServerCmdletSnapin100 adds the Invoke-SQLCmd cmdLet. So instead of typing the code in the examples above
we can simply Add the snapin to our current session and use the CmdLet.
PS > Add-PSSNapin SqlServerCmdletSnapin100
PS > Invoke-Sqlcmd -Database NorthWind -Query "SELECT * FROM Products" -ServerInstance Server01
The SqlServerProviderSnapin100 adds a new PSDrive that when working with SQL.
PS > Add-PSSnapin SqlServerProviderSnapin100
PS > Get-PSDrive
Name Used (GB) Free (GB) Provider Root
---- --------- --------- -------- ----
A FileSystem A:\
Alias Alias
C 15,07 84,83 FileSystem C:\
cert Certificate \
D FileSystem D:\
Env Environment
Function Function
HKCU Registry HKEY_CURRENT_USER
HKLM Registry HKEY_LOCAL_MACHINE
SQLSERVER SqlServer SQLSERVER:\
Variable Variable
WSMan WSMan
After the SnapIn is added we can use the Provider to access SQL Server.
PS > cd SQLServer:
PS SQLSERVER:\> dir
Here’s an example how you can add multiple rows in a table using a for loop.
The List contains three Columns that we will use: Title, Description and Create. Create in this example is a Yes/No column.
In a previous post we populated a Table in SQL with Computers. Let’s take a look at how we can read information from that list. The function below demonstrates how we can read data from a table.
Finally, let’s create some of the computers in Active-Directory. The SharePoint List had a Yes/No Column named Create. Let’s only create computers where the field is set to Yes.
In this example I’ve checked the Create filed for: DT001, DT003 and DT005. Let’s see how we can retrieve does specific items from SharePoint 2010 wqithout retrieving the whole ListItem Collection.