使用 PowerShell 将数据从 SQL Server 表导入到 SharePoint 列表

要求：
我们有一个外部 CRM 应用程序，要从中提取数据并将其插入到 SharePoint 列表中！

PowerShell 从 SQL Server 读取数据并将其插入 SharePoint：

我们通过根据 SQL Server 表映射相关列来创建一个新列表“项目”，然后这是我的 PowerShell 脚本，用于将数据从 SQL Server 导入到 SharePoint 列表。

Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

#Get Sharepoint List
$Web = Get-SPWeb "https://portal.crescent.com/ProjectPipeline/"
$List = $Web.Lists["Projects"]

$ConnectionString = "Server=CrescentSQL;Database=CRM_Integrated_DB;Integrated Security=True"
$SPName = "rpt_Project_pipeline"  #Stored Procedure outputs data. This can be a table, view or sp

#execute the Stored Procedure to Fetch Data
$sqlConnection = new-object System.Data.SqlClient.SqlConnection
$sqlConnection.ConnectionString = $ConnectionString
$sqlConnection.Open()
$sqlCommand = new-object System.Data.SqlClient.SqlCommand
$sqlCommand.CommandTimeout = 120
$sqlCommand.Connection = $sqlConnection
$sqlCommand.CommandType=[System.Data.CommandType]'StoredProcedure'
$sqlCommand.CommandText = $SPName
   
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $sqlCommand
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$sqlCommand.Dispose()

## Fetch data from Stored Procedure
write-host "Data Import Starts..."
ForEach ($Project in $DataSet.Tables[0]) 
{ 
 #Update Existing Project if the match found
    $SPQuery = New-Object Microsoft.SharePoint.SPQuery 
    $SPQuery.Query = "<Where><Eq><FieldRef Name='ProjectName' /><Value Type='Text'>" + $Project.'Project Name' + "</Value></Eq></Where>"
    $ListItems = $List.GetItems($SPQuery) 
    if($ListItems.Count -gt 0)
    {
        foreach($Item in $ListItems)
        { 
            #update existing Project data
            $Item["Company Type"] = $Project.'Company Type'
            $Item["Sector"] = $Project.'Sector'
            $Item["Country"] = $Project.'Country'
            $Item["Deal Lead"] = $Project.'Deal Lead'
            $Item["Investment Date"] = $Project.'Investment Date'
            $Item["Investment Amount"] = $Project.'Investment Amount'
            $Item["Industry"] = $Project.'Industry'

   try
            {
                $Item.Update() 
            }
            catch
            {
                write-host "Item ID: " $Item["ID"] "not updated"
            }
        }    
    } # Update List Item complete
  
   else #Add New Item in case no match for Project Name
   {
        if([string]::IsNullOrEmpty($Project.'Project Name')){
            #write-host "Project Name is empty and data not updated"
        }
        else{
            $newItem = $List.Items.Add()
            $newItem["Project"] = $Project.'Project Name'
            $newItem["Company Name"] = $Project.'Company Name'
            $newItem["Company Type"] = $Project.'Company Type'
            $newItem["Sector"] = $Project.'Sector'
            $newItem["Country"] = $Project.'Country'
            $newItem["Deal Lead"] = $Project.'Deal Lead'
            $newItem["Investment Date"] = $Project.'Investment Date'
            $newItem["Investment Amount"] = $Project.'Investment Amount'
            $newItem["Industry"] = $Project.'Industry'
       
            TRY
            {
                $newItem.Update()
            }
            CATCH
            {
                Write-Host "Project Name: " $Project.'Project Name' "NOT UPDATED FROM TABLE"
            }
        }
    }
}
write-host "Data Import completed!"