Insert data to a Sharepoint list retrived from an MS SQL database

Some month ago i had to insert numerous data basically stored in an MS SQL database into a Sharepoint list. Well, you can say here.. let’s have an enterprise licenced Sharepoint application and use Business Data Connection Service (in MOSS 2007) or BCS (in MOSS 2010) and the task can be closed.

Oh yes.. if you have lot of money that’s the correct and supported solution. But what if you have only a free Windows Sharepoint Services or Sharepoint Fundation? Well, Powershell gives a hand again 🙂

You need only to create a script which connects to the correct database then retrives the data from it then inserts those data to the destination Sharepoint list. Cool, isn’t it?

Have you alread know how to connect to a sharepoint list? Or you want to connect Sharepoint remotely?

First issue: Connect to the MS SQL Server

</span>
<pre>$SQLSERVER="SQLServer\Instance_name"
$Database="Gookart"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=$SQLSERVER;Database=$DATABASE;Integrated Security=True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = "select * from TableName"
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()

If the connection was unsuccessfull or the query failed we’ll have an Error elemnt in the $Error variable. So please check its value after this script block.

Note: You can use the $ErrorActionPreference=”SilentlyContinue” before running this block and then switch it back to on after the block ($ErrorActionPreference=”Continue”)

Well if you need another Connection string (eg using other credential) just visit: http://www.connectionstrings.com/
In case of using other usercredential for connecting the SQL server we have one more little issue: your username and password will be store as an unencrypted string.

I think in that case you should create an invoke-command in your script which is run using an other credential. and use integrated security in the command script block. (I will have a post on that also in the near future 🙂 )

Please don’t use select * 🙂 preferred is a normal defined query against the SQL server…

So after having run this script block will have the data retrived from the SQL Server stored in the variable $DataSet

Second issue: Get the data from $DataSet variable


$LotsOfData = $DataSet.Tables[0]
 foreach ($data in $LotsOfData) {
 # Let's have a look up for the value which belong to the line i want to insert to the Sharepoint List (okay, i could have done it using the where statement in the SQL query.. for now just play a bit)

 if ($data.number.tostring() -eq "213"){
 $value = $data.gokart_ID
 }
 }

#Let's get the list from the Sharepoint Site
[System.reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint")
 $site = new-object Microsoft.SharePoint.SPSite("http://gokartsite")
 $web = $site.RootWeb
 $lista = $web.lists | where { $_.title -eq "Data from SQL"}

if ($value.tostring().length -gt 0)

# If we have founc such a gookart then let's insert the parameters to our sharepoint list
 {
 $items = $lista.items
 foreach ($item in $items)
 # Do a lookup against the Sharepoint list to get the record where we can insert the gookart data to
 {
 if ($item["Run"] -eq "FormaGokart")
 {
 $item["GokartID"] = $value
 $item.update()
 }
 }
}
 $web.Dispose()
 $site.Dispose()

Third issue: Scheduling the created Powershell script to update the Sharepoint list regularly

To create a scheduled task in Windows Server please follow this link 🙂

Let’s have fun 🙂 see you guys…

4 thoughts on “Insert data to a Sharepoint list retrived from an MS SQL database”

  1. Hi,

    I am a novice in this field and got stuck with your code. Kindly help me out.
    My requirement is to fill up all the details in the database to a SharePoint list and it has to be updated once the database is update, not immediately, may be once in a day.

    I have the following things:
    1. SharePoint Service 3/4 – I don’t have sharepoint server and also don’t want to use designer also. You code is what I want
    2. Sharepoint site – http://sharepoint
    3. List Name – Tiken – It has two Column – TK, NV
    4. Database server name – Sharepoint
    5. Database Name – ReportServer
    6. Table Name – Tejas – It has two column – TK, NV

    Pls help me out.

    Thanks and Regards,
    Tiken M

    Note: Kindly let me know the debug point where I can see if my connection to the database is successful or not also. I am struggling here itself.

    1. $SQLSERVER=”sharepoint”
      $Database=”ReportServer”
      $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
      $SqlConnection.ConnectionString = “Server=$SQLSERVER;Database=$DATABASE;Integrated Security=True”
      $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
      $SqlCmd.CommandText = “select tk, nv from Tejas”
      $SqlCmd.Connection = $SqlConnection
      $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
      $SqlAdapter.SelectCommand = $SqlCmd
      $DataSet = New-Object System.Data.DataSet
      $SqlAdapter.Fill($DataSet)
      $SqlConnection.Close()

      [System.reflection.Assembly]::LoadWithPartialName(“Microsoft.SharePoint”)
      $site = new-object Microsoft.SharePoint.SPSite(“http://sharepoint”)
      $web = $site.RootWeb
      $lista = $web.lists | where { $_.title -eq “Tiken”}
      $LotsOfData = $DataSet.Tables[0]
      foreach ($data in $LotsOfData) {
      $newitem= $lista.items.Add()
      $newitem[“TK”]= $data.TK.tostring()
      $newitem[“NV”]= $data.NV.tostring()
      $newitem.update()
      }
      $web.Dispose()
      $site.Dispose()

      debugpoint: if the connectino is unsuccessful then it will drop you an error message and the $Dataset variable will be $null

  2. Thank you for your prompt reply. I have learn a lot from your above code and some research n the net. Thanking you, Tiken M

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s