PowerShell Scripting

PowerShell Script for a SharePoint calendar list. The scripts adds data from to SharePoint calendar list. The calendar data will be populated from a database of any application. A power shell script will be task scheduled to run in the interval of every 15 days and update the SP calendar list fetching the data from the database view of clarity. Every time the calendar will be deleted and recreated using a template file before loading the data.



#---------------------------------------------------------------------------------------------------------
#Created By: Nitin Tiwari
#Created Date: 6/3/2012
#Discription: 
#--------------------------------------------------------------------------------------------------------
#-------Deleting the List From Existing site---------#
param([string]$url, [string]$List)
[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint")



function Get-SPSite([string]$url) {


New-Object Microsoft.SharePoint.SPSite($url)
}
$url= 'http://servername:11/'
$List= 'Name of Calendar'
function Get-SPWeb([string]$url) {


$SPSite = Get-SPSite $url
return $SPSite.OpenWeb()
$SPSite.Dispose()
}


function Remove-SPField([string]$url, [string]$List) {


$OpenWeb = Get-SPWeb $url
$OpenList = $OpenWeb.Lists[$List]
  
if($openList -eq $null)
{


}
else
{


$OpenList.Delete()
}
$OpenWeb.Dispose()
}




if($url -AND $List) { Remove-SPField -url $url -List $List }


#-----Creating the Lsit from Template-------#
# Specify your Site collection URL
#set-variable -option constant -name url  -value $url
$site = Get-SPSite($url) 
$web = $site.OpenWeb() 
$listTemplates = $site.GetCustomListTemplates($web)
$web.Lists.Add("Calendar", "Calendar", $listTemplates["Calendar"])
$site.Dispose
Echo Finish
#----Establishing the database connection and reading the data count----#
#add-pssnapin sqlserverprovidersnapin100 
#add-pssnapin sqlservercmdletsnapin100 
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection 
$SqlConnection.ConnectionString = "Server=WIN-9VAS57CM29D\KMPDEVELOPMENT;Database=ABC_Calendar;Integrated Security=True" 
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand 
$SqlCmd.CommandText = "select * from V_DeploymentCalendar" 
$SqlCmd.Connection = $SqlConnection 
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter 
$SqlAdapter.SelectCommand = $SqlCmd 
$DataSet = New-Object System.Data.DataSet 
$SqlAdapter.Fill($DataSet) 
$SqlConnection.Close() 
#$DataSet.Tables[0]
Write-output "Database is " $DataSet.Tables[0].Rows.Count
#-----Reading the data and updating the field--------#


$SPSite=New-Object Microsoft.SharePoint.SPSite($url)


$OpenList = $SPSite.OpenWeb().Lists[$List]


function is-null($value){
  return  [System.DBNull]::Value.Equals($value)
}


foreach ($Row in $DataSet.Tables[0].Rows)
{
$NewItem = $OpenList.Items.Add()
if(is-null $Row.ProjectName)
{
$NewItem["Title"]=''
}
else
{
$NewItem["Title"] = $Row.ProjectName
}

if(is-null $Row.DeploymentActualStartDate)
{
#$NewItem["Deployment Date"]=''
}
else
{
$NewItem["Deployment Date"]= [DateTime]$Row.DeploymentActualStartDate
}

if(is-null $Row.ProjectName)
{
$NewItem["Name of Project"]=''
}
else
{
$NewItem["Name of Project"] = $Row.ProjectName
}

if(is-null $Row.ProjectManager)
{
$NewItem["Name of Project Manager"]=''
}
else
{
$NewItem["Name of Project Manager"] = $Row.ProjectName
}

if(is-null $Row.TechnicalLead)
{
$NewItem["Change Owner"]=''
}
else
{
$NewItem["Change Owner"] = $Row.ProjectName
}

if(is-null $Row.SystemsImpacted)
{
$NewItem["Impacted Application"]=''
}
else
{
$NewItem["Impacted Application"] = $Row.ProjectName
}

if(is-null $Row.Location)
{
$NewItem["Location"]=''
}
else
{
$NewItem["Location"] = $Row.ProjectName
}
$NewItem.Update()




}


$SPSite.Dispose()