######### Export SPList to SQL ############# function ExecuteSQLInsert($sqlVAL1,$sqlVAL2,$sqlVAL3)
{
$ErrorActionPreference = 'stop' # Prepare script for stopping
$SqlCmd.CommandText ="INSERT INTO Companydetails(CompanyId,CompanyName,CompanyName1) VALUES ($sqlVAL1,'$sqlVAL2','$sqlVAL3')" # Configure TSQL
Try
{
$SqlCmd.ExecuteNonQuery() #
}
Catch
{
Write-Warning "$_" # Report Errors
Write-Warning $SqlCmd.CommandText
Write-host
}
}
######connect to SQL database windows authentication ###########
$connection= new-object system.data.sqlclient.sqlconnection #Set new object to connect to sql database
$Connection.ConnectionString ="Your SQL Details"
# Connectiongstring setting for <ServerName> <databasename> with window authentication
#$Connection.ConnectionString ="server=<ServerName>;database=<databasename>;User Id=<username>;Password=<password>;trusted_connection=False; # Connectiongstring setting for <ServerName> <databasename> with SQL authentication <username><password>
Write-host "connection information:"
$connection #List connection information
Write-host "Connecting to database.."
$connection.open() #Open Connection
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand #setting object to use sql commands
$SqlCmd.Connection = $connection
######### Get SPList ########
$spWeb = Get-SPWeb -identity "http://SiteName/" # Get SPWeb
$list = $spWeb.Lists["CompanyDetails"] # Get SPList
foreach ($item in $list.items)
{
$groupcol = $Item["CompanyName1"]
$test
foreach($group in $groupcol)
{
$test+=$group.lookupvalue
}
$Lookup = new-object Microsoft.SharePoint.SPFieldLookupValue($item["CompanyName"])
$User = $Lookup.LookupValue;
ExecuteSQLInsert $item.ID $User value
}
$connection.Close()
$spWeb.Dispose()
######Import SQLData to SPList ###########
$connection= new-object system.data.sqlclient.sqlconnection #Set new object to connect to sql database
$Connection.ConnectionString ="SQl Server Details" # Connectiongstring setting for <ServerName> <databasename> with window authentication
#$Connection.ConnectionString ="server=<ServerName>;database=<databasename>;User Id=<username>;Password=<password>;trusted_connection=False; # Connectiongstring setting for <ServerName> <databasename> with SQL authentication <username><password>
Write-host "connection information:"
$connection #List connection information
Write-host "Connecting to database.."
$connection.open() #Open Connection
######### SQL query #############
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand #setting object to use sql commands
$SqlQuery = "SELECT orderId, productId, UnitPrice, Quantity, Discount FROM OrderDetails " #set SQL query
$SqlCmd.CommandText = $SqlQuery # get query
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter #
$SqlAdapter.SelectCommand = $SqlCmd #
$SqlCmd.Connection = $connection
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlAdapter.Dispose()
$connection.Close()
$DataSet.Tables[0]
######### Add to SPList ########
$spWeb = Get-SPWeb -identity "http://Site Name" # Get SPWeb
$list = $spWeb.Lists["Orders"] # Get SPList
foreach ($Row in $DataSet.Tables[0].Rows)
{
$item = $list.Items.Add();
$item["Title"] = $Row["orderId"]
$item["ProductId"] = $Row["ProductId"]
$item["UnitPrice"] = $Row["UnitPrice"]
$item["Quantity"] = $Row["Quantity"]
$item["Discount"] = $Row["Discount"]
$item.Update()
}
$spWeb.Dispose()