SharePoint 2010
March 14th, 2011
2 comments
This post is part of the Second Wednesday Demo Session, Click here for more info about additional demo posts.
In a previous post we used Windows PowerShell to Create a Database, add a table and populate the table with alot of entries. In the last part of this Demo we’ll use SharePoint 2010 to populate a List in SharePoint based on a Table in SQL and finally Script up alot of Computers based on a SharePoint List.First let’s take a look at the SharePoint List.
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.
function Get-SQLComputer {
$Connection = New-Object System.Data.SqlClient.SqlConnection
$Connection.ConnectionString = "server=SQL01;database=TestDB;trusted_connection=true;"
# open Connection
$Connection.Open()
$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $Connection
# Add Query
$Command.CommandText = "SELECT * FROM Computers"
# read from table
$Reader = $Command.ExecuteReader()
$Counter = $Reader.FieldCount
# itterate each row
while ($Reader.Read()) {
$SQLObject = @{}
for ($i = 0; $i -lt $Counter; $i++) {
$SQLObject.Add(
$Reader.GetName($i),
$Reader.GetValue($i)
);
}
# return hashTable
$SQLObject
}
# Close Connection
$Connection.Close()
}
The function above returns the rows in the table. Now let’s add them to our custom SHarePoint list.
Note that the function above doesn’t require any input. If you write a function that takes input when performing a query you should consider using parameterized queries to avoid possible injections. here’s a short example:
function Get-SQL([string]$ComputerName) {
$Connection = New-Object System.Data.SqlClient.SqlConnection
$Connection.ConnectionString = "server=SQL01;database=TestDB;trusted_connection=true;"
$Connection.Open()
$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $Connection
$Command.CommandText = "SELECT * FROM Computers WHERE Computer = @ComputerName"
# These two lines are added to avoid injections
$SQLParameter = New-Object System.Data.SqlClient.SqlParameter("@ComputerName", $ComputerName)
[void]$command.Parameters.Add($SQLParameter)
$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()
}
First we use Add-PSSNapin to add the SharePoint 2010 CmdLets.
PS > Add-PSSNapin Microsoft.SharePoint.PowerShellIf your not running in sta mode, set the threadoption to “ReuseThread”.
PS > $host.Runspace.ThreadOptions = "ReuseThread"Next we use Get-SPWeb to return a specific Site.
PS > $spWeb = Get-SPWeb http://SP01Now we get the “Computer” list using the GetList() method.
PS > $spList = $spWeb.GetList("/Lists/Computers")
The spList has a method, AddItem(), which we can use to add a new Item in the SharePoint List. the example below demonstartes how we use the Get-SQLComputer functionto add the computers from the SQL table to a list in SharePoint 2010.
PS > Get-SQLComputer | ForEach {
>> $item = $spList.AddItem()
>> $item["Title"] = $($_.Computer).TrimEnd()
>> $item["Description"] = $($_.Description).TrimEnd()
>> $item.Update()
>> }
Finally we dispose of the spWeb object.
PS > $spWeb.Dispose()If we take a look at the list now, the Computers are added.
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.First we get the specific list.
PS > $spWeb = Get-SPWeb http://SP01
PS > $spList = $spWeb.GetList("/Lists/Computers")
The spList contains a property, ItemCount, that show us how many ListItems that are in the list.
PS > $spList.ItemCount 202The spList also contains a property, Items, that returns all items in the list. Note that this can consume alot of unnecesary memory if your working with large lists so a better way of retrieving listitems is by using a CAML query.
In this example we only want listItems where Create equals Yes. Here’s an example on how to write such a query.
PS > $spQuery = New-Object Microsoft.SharePoint.SPQuery; PS > $query = "<Where><Eq><FieldRef Name='Create' /><Value Type='Boolean'>1</Value></Eq></Where>" PS > $spQuery.Query = $queryAn easy way of getting your CAML queries correct is by using a CAML query builder. Now we can Get the specific listItems using the GetItems() method and the spQuery object as input.
PS > $listItems = $spList.GetItems($spQuery)If we count the number of listItems in the ListItem Collection we’ll see that it only contains 3 items.
PS > $listItems.Count 3We can use the Select-Object cmdlet to Select the properties that we want to work with.
PS > $listItems | Select-Object @{Name="Name";Expression={$_.Title}},
>> @{Name="Description";Expression={$_["Description"]}}
Name Description
---- -----------
DT001 Added Using PowerShell
DT003 Added Using PowerShell
DT005 Added Using PowerShell
Finally, let’s go ahead and create the computers in Active Directory. In this example well start a Rmote background job that does the work for us.
PS > $computers = $listItems | Select-Object @{Name="Name";Expression={$_.Title}},
>> @{Name="Description";Expression={$_["Description"]}}
>> Invoke-Command -ComputerName DC01 -ScriptBlock {
>> Import-Module ActiveDirectory
>> $args | Foreach {
>> New-ADComputer -Name $($_.Name) -Description $($_.Description) -Path "OU=LabCenter,DC=PowerShell,DC=nu"
>> }
>> } -ArgumentList $computers -AsJob
>>
Id Name State HasMoreData Location Command
-- ---- ----- ----------- -------- -------
7 Job7 Running True dc01 ...
When the job completes the Computers are created in Active Directory
[?]
When the User or Group logs into SharePoint 2010 he/she will be able to view content, Update List Items but not Create or Delete List Items.
