PnP PowerShell code to connect the sql database and insert and update the data from SPO Library

Copper Contributor

PnP PowerShell script to read the data from SharePoint online document library and insert or update the data to the SQL database table based on the condition. 

1 Reply

Hi @Ganesh955,

for connecting to a SQL database and performing the insertion or update operations based on data from a SharePoint Online document library, you can try the following code as a an example:

# Connect to SharePoint Online
Connect-PnPOnline -Url "https://yourdomain.sharepoint.com/sites/yoursite" -Credentials (Get-Credential)

# Connect to the SQL database
$serverName = "your_server_name"
$databaseName = "your_database_name"
$username = "your_username"
$password = "your_password"
$connectionString = "Server=$serverName;Database=$databaseName;User ID=$username;Password=$password;"
$connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)
$connection.Open()

try {
# Get data from SharePoint Online document library
$listName = "Your Document Library"
$items = Get-PnPListItem -List $listName

foreach ($item in $items) {
# Extract required fields from SharePoint item
$title = $item["Title"]
$fileUrl = $item.FieldValues["FileRef"]

# Check condition and perform insert/update in SQL database
$query = ""
if ($condition) {
# Insert operation
$query = "INSERT INTO YourTable (Title, FileUrl) VALUES ('$title', '$fileUrl')"
} else {
# Update operation
$query = "UPDATE YourTable SET Title = '$title' WHERE FileUrl = '$fileUrl'"
}

$command = New-Object System.Data.SqlClient.SqlCommand($query, $connection)
$command.ExecuteNonQuery()
}

Write-Host "Data inserted/updated successfully in SQL database."
}
catch {
Write-Host "An error occurred while processing data: $_"
throw
}
finally {
# Close the SQL connection
$connection.Close()
}

Make sure to enter your values:

  • "https://yourdomain.sharepoint.com/sites/yoursite" --> the URL of your SharePoint Online site.
  • "your_server_name" --> the name of your SQL server.
  • "your_database_name" --> the name of your SQL database.
  • "your_username" and "your_password" --> the credentials to connect to your SQL database.
  • "Your Document Library" --> the name of your SharePoint Online document library.
  • "YourTable" --> the name of the table in your SQL database.

 

Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.

Kindest regards

Leon Pavesic