Exporting -import data from/ to sql server's table to sharepoint list

Exporting Sharepoint list data to sql server and importing sql server 's table data to SharePoint list  using Power shell


 Whenever we are intergrating sharepoint application with another application then developer require that operations.



####### 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()



In Next post will see how to retrive sharepoint  list/libraries data to sql server table using console application..

~Sunit Kanyan