Applies to: Nevron Vision for SharePoint web parts (WSS3.0, SharePoint 2007/2010/2013)
How to dynamically change the database server connection for the Chart and Gauge web parts?
This topic explains how to update the database server connection dynamically, for each Chart and Gauge web parts, when working in different environment (Development and Production). You may have multiple SharePoint environments – for example Development and Production environments. Let’s say that for the different environments you need to have different Data Source connections, you may have multiple SQL servers (for Dev and Prod).
In development, we will use: SharePoint Server (spDev); SQL Server (spDevSQL);
In production, we will use: SharePoint Server (spProd); SQL Server (spProdSQL);
Once you create your web application which uses Nevron web parts, the web parts are connected to the development SQL server (spDevSQL). To automate the deployment to production, you may want to do it by using Powershell scripts (see below).
---------------------------------------------------------------------------------------------------
Script 1 – Backup site collection at development server. The script exports the site to a file and copies the file to the WFE server (spProd).
Backup-SPSite -identity http://spDev/sites/Dashboards -path ($outPath + $exportFileName) -Force
Copy-item -path ($outPath + $exportFileName) -destination "\\spProd\c$\SharePointImports\"
Script 2 – Restore site collection at production server. The second script installs the site on the WFE (spProd).
Restore-SPSite -identity http://spProd/sites/Dashboards -path $importFileName -Force -DatabaseServer "spProdSQL" -DatabaseName "WSS_Content"
---------------------------------------------------------------------------------------------------
After deployment, you will need to point the Chart and Gauge web parts to the production SQL server (spProdSQL) as they will still be connected to spDevSQL.
Dynamically changing the database server connection for the Chart and Gauge web parts can be achieved via expressions. This is possible because all aspects of the database connection (connecting string, SQL query etc.) can be dynamic (i.e. driven by expressions). To actually change the connection you need some parameter to tell you whether you are at the DEVELOPMENT or the PRODUCTION server.
A nice way to do it is to develop on a site with a specific page URL that is different from the production one. Let’s say your DEVELOPMENT site is http://spDev. In the case where at a production server the database is always called in the same name, the production site is of no importance. You can write the following expression in the Connection String for the database connection:
Connection String:
="Data Source=" + IF(Params![SiteUrl] = "http://spDev", "spDevSQL", " spProdSQL ")+"\;Initial Catalog=NevronSPVisionExamplesDB;Integrated Security=True"
For the purpose of this example and additional info, we will also create a Title which uses the following expression:
="URL Custom param: " + Params![SiteUrl] + "<br/>" + "Connection to: " + IF(Params![SiteUrl] = "http://spDev", "Dev SQL", "Prod SQL") + “ Server”
When the web part runs on the http://spDev site, it will link to the spDevSQL database. When the web part runs on any other site, it will link to the spProdSQL database.
In this example we have actually used a predefined parameter, but it can even be a user specified parameter – see Pivot Engine > Variables > Parameters in the help file for more info.
Article ID: 227, Created On: 10/17/2012, Modified: 1/29/2013