How to Programmatically Update an ODC File (Shubh Raj Singh)

  • Article

This post describes how to update ODC files programmatically by using either a SharePoint feature or a console application written in .Net code, or with a PowerShell script. The contents pertain to:

  • Microsoft SharePoint Server 2010
  • Microsoft SharePoint Server 2010 – Excel Services
  • Microsoft SQL Server 2008

Overview of Office Data Connection Files

An Office Data Connection (ODC) file contains connection information about how to connect to a data source and retrieve data for use in various documents such as Microsoft Excel workbooks. Connection information can either be stored in the workbook or in a connection file, such as an (ODC) file (.odc) or a Universal Data Connection (UDC) file (.udcx).

Typically, an ODC file is used while building Excel services reports that fetch data from a data source. As part of the implementation, this report can be deployed in multiple environments such as development, staging, test, and production. Each environment may have a different data source and hence a different connection string. The connection string should be changed in the ODC file as based on the environment it is deployed in. Usually this is a manual step; however this task can be automated. This approach is the topic of this post.

You can update ODC files programmatically by using either a SharePoint feature or a console application written in .Net code, or with a PowerShell script.

With the console application approach, files need to be published on the SharePoint site with some additional code or deployment steps which are not covered in this article. The PowerShell approach takes care of uploading and publishing the ODC file to the SharePoint site.

One ODC file can be used for one or more Excel files. ODC files contain the following types of information:

  • Connection string
  • Command Type
  • Command Text
  • SSO Application ID
  • Credentials Method
  • Whether to always use the connection file

Description of an ODC File

An ODC file is a HTML file that contains embedded sections of XML. The XML in an ODC file determines the core connection information for the data source. This information includes:

  • Data provider-specific connection string that is used to establish and open a connection to the data source
  • Query text that is used to fetch data
  • Name of the specific table or cube from which to fetch data
  • Hints about how the query text, cube, or table name is interpreted
  • Flag indicating that the ODC file is always used to connect to and query the data source (as opposed to an application using a cached version of the data connection information)
  • Specific authentication information to use for the data source. If a server application is using the ODC file to fetch data, this information will often be used for connecting to the data sources

The following HTML is an example of an ODC connection string inside of an ODC file:

<odc:OfficeDataConnection xmlns:odc="urn:schemas-microsoft-com:office:odc" xmlns="https://www.w3.org/TR/REC-html40">
<odc:Connection odc:Type="OLEDB">
<odc:ConnectionString>Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=<Database Name>;Data Source=<Database Server>;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=<Computer Name>;Use Encryption for Data=False;Tag with column collation when possible=False</odc:ConnectionString>
<odc:CommandType>SQL</odc:CommandType>
<odc:CommandText> &quot;<Database Name>&quot;.&quot;dbo&quot;.&quot;<Stored Procedure Name>&quot;</odc:CommandText>
<odc:SSOApplicationID>ExcelServicesApplicationID</odc:SSOApplicationID>
<odc:CredentialsMethod>Stored</odc:CredentialsMethod>
<odc:AlwaysUseConnectionFile/>
</odc:Connection>
</odc:OfficeDataConnection>

A C# Code Example

The following is a simple console application that shows how to change values inside an ODC file. This code can be used in a feature or a console application with all the previous parameter values provided from a configuration file. This code changes the highlighted values in the data connection XML shown above.

namespace UpdateODCFile
{
using System.Linq;
using System.Text;
using System.Xml;

/// <summary>
/// Class to change the ODC file parameters
/// </summary>

class Program
{

/// <summary>
/// Index from where tag begins
/// </summary>

private static int sourceIndex = 0;

/// <summary>
/// Index from where tag ends
/// </summary>

private static int destinationIndex = 0;

static void Main(string[] args)
{
// You can keep the source an destination file path same if you want to
// overwrite the existing file with new values.

string odcFilePath = @"C:ReportsConnection.odc";
string destinationFilePath = @"C:ReportsConnection.odc";

// Provide values for following patameters that will be changed in the ODC file.

string databaseName = "<your database name>";
string serverName = "<your server name>";
string SSOApplicationID = "<application ID>";string provider = "<Provider>";
string integratedSecurity = "<integrated security>";
string persistSecurityInfo = "<persist security info>";
string useProcedure = "<use procedure>";
string autoTranslate = "<auto translate>";
string packetSize = "<packet size>";
string workSatationID = "<workstation ID>";
string encryptionData = "<encryption data>";
string tagWithCollation = "<tag with column collation>";

The following code reads the ODC file and retrieves the ODC connection XML data from it.

string xmlConnection = GetConnectionString(odcFilePath);
XmlDocument odcXmlConnection = new XmlDocument();
odcXmlConnection.LoadXml(xmlConnection);
XmlNamespaceManager nameManager = new XmlNamespaceManager(odcXmlConnection.NameTable);
nameManager.AddNamespace("odc", odcXmlConnection.DocumentElement.NamespaceURI);
XmlNodeList nodelistConnectionString = odcXmlConnection.SelectNodes("//odc:Connection/odc:ConnectionString", nameManager);

The following code changes the connection properties mentioned above.

StringBuilder finalConnectionString = CreateNewConnectionString(databaseName, serverName, provider, integratedSecurity, persistSecurityInfo, useProcedure, autoTranslate, packetSize, workSatationID, encryptionData, tagWithCollation, nodelistConnectionString);
nodelistConnectionString[0].InnerText = finalConnectionString.ToString();

The following code changes the SSOApplicationID property in the XML.

XmlNodeList nodelistSSOApplicationID = odcXmlConnection.SelectNodes("//odc:Connection/odc:SSOApplicationID", nameManager);
nodelistSSOApplicationID[0].InnerText = SSOApplicationID;

The following code saves the updated file on to a disk. When this is complete, the file can be published to SharePoint.

SaveConnectionString(odcFilePath, destinationFilePath, odcXmlConnection.OuterXml);

}

The following code finds the connection string tag in the ODC file and returns the full connection string.

/// <summary>
/// Finds the connection string in the ODC file
/// </summary>

/// <param name="filePath">path of ODC file</param>
/// <returns>office data connection xml</returns>

public static string GetConnectionString(string filePath)
{
string xmlConnection = string.Empty;
System.IO.StreamReader myFile = new System.IO.StreamReader(filePath);
string myString = myFile.ReadToEnd();
sourceIndex = myString.IndexOf("<odc:OfficeDataConnection");
destinationIndex = myString.IndexOf("</odc:OfficeDataConnection");
xmlConnection = myString.Substring(sourceIndex, destinationIndex - sourceIndex + 27);
myFile.Close();

   return xmlConnection;

}

The following code saves the connection string tag to the ODC file and saves it to a disk.

/// <summary>
/// save the changed ODC file
/// </summary>

/// <param name="filePath">path at which you want to read the ODC file</param>
/// <param name="newFilePath">path at which you want to save</param>
/// <param name="connectionString">new data connection string to be changed</param>

public static void SaveConnectionString(string filePath, string newFilePath, string connectionString)
{
string xmlConnection = string.Empty;
System.IO.StreamReader myFile = new System.IO.StreamReader(filePath);
string myString = myFile.ReadToEnd();
myFile.Close();
string lessString = myString.Remove(sourceIndex, destinationIndex - sourceIndex + 27);
myString = lessString.Insert(sourceIndex, connectionString);
System.IO.StreamWriter writer = new System.IO.StreamWriter(newFilePath);
writer.Write(myString);
}

The following code finds the properties in the ODC file and then changes them. If a property does not require any changes, it can be removed from this code. The new connection string will be returned to the Main function.

/// <summary>
/// Function to replace the old values with new one.
/// </summary>

/// <param name="databaseName">database Name</param>
/// <param name="serverName">server Name</param>
/// <param name="provider">provider</param>
/// <param name="integratedSecurity">integrated Security</param>
/// <param name="persistSecurityInfo">persist Security Info</param>
/// <param name="useProcedure">use Procedure</param>
/// <param name="autoTranslate">auto Translate</param>
/// <param name="packetSize">packet Size</param>
/// <param name="workSatationID">work Satation ID</param>
/// <param name="encryptionData">encryption Data</param>
/// <param name="tagWithCollation">tag With Collation</param>
/// <param name="nodelistConnectionString">nodelist Connection String</param>
/// <returns>final connection string</returns>

private static StringBuilder CreateNewConnectionString(string databaseName, string serverName, string provider, string integratedSecurity, string persistSecurityInfo, string useProcedure, string autoTranslate, string packetSize, string workSatationID, string encryptionData, string tagWithCollation, XmlNodeList nodelistConnectionString)
{
string[] connectionStringArray = nodelistConnectionString[0].InnerText.Split(';');
StringBuilder finalConnectionString = new StringBuilder();
foreach (string connections in connectionStringArray.ToList())
{
string[] splitOnEqual = connections.Split('=');
switch(splitOnEqual[0])
{
case "Initial Catalog": splitOnEqual[1] = databaseName;
break;

         case "Data Source": splitOnEqual[1] = serverName;
break;

         case "Provider": splitOnEqual[1] = provider;
break;

         case "Integrated Security": splitOnEqual[1] = integratedSecurity;
break;

         case "Persist Security Info": splitOnEqual[1] = persistSecurityInfo;
break;

         case "Use Procedure for Prepare": splitOnEqual[1] = useProcedure;
break;

         case "Auto Translate": splitOnEqual[1] = autoTranslate;
break;

         case "Packet Size": splitOnEqual[1] = packetSize;
break;

         case "Workstation ID": splitOnEqual[1] = workSatationID;
break;

         case "Use Encryption for Data": splitOnEqual[1] = encryptionData;
break;

         case "Tag with column collation when possible": splitOnEqual[1] = tagWithCollation;
break;

      }

      finalConnectionString = finalConnectionString.Append(splitOnEqual[0]);
finalConnectionString = finalConnectionString.Append("=");
finalConnectionString = finalConnectionString.Append(splitOnEqual[1]);
finalConnectionString = finalConnectionString.Append(";");

   }

   finalConnectionString = finalConnectionString.Remove((finalConnectionString.Length-1), 1);
return finalConnectionString;

}
}
}

Code Example using a PowerShell

The following is a PowerShell script to accomplish the same task described previously.

# Initialize the variables

$BICenterSite = "<Site where you have to upload the updated ODCs>"
$reportingDBname = "<Database Name>"
$databaseServer = "<Database Server Name>"
$listName = "<List Name where ODCs have to be uploaded>"
$ODCFilesFolder = "<Folder path where all the ODCs are present>"
$SubFolderName = "<Sub Folder Name>"
$fileEntry = [IO.Directory]::GetFiles($ODCFilesFolder)

# reading all the ODC files from the given folder
foreach($fileName in $fileEntry)
{
$w=Get-Content $fileName
$d=[string]$w
if($d.Contains("<odc:OfficeDataConnection"))
{
[int] $i = $d.IndexOf("<odc:OfficeDataConnection")
[int] $j = $d.IndexOf("</odc:OfficeDataConnection>")
[xml] $xmlPart = $d.SubString($i, ($j-$i+27))
$con = $xmlPart.OfficeDataConnection.Connection | Select ConnectionString
[string] $connectionString = $con.ConnectionString
[string] $finalString = ""
$stringArray = $connectionString.Split(";")

      foreach($element in $stringArray)
{
$item = $element.Split("=")
if($item[0] -eq "Initial Catalog")
{
$item[1]=$reportingDBname
}

         if($item[0] -eq "Data Source")
{
$item[1]=$databaseServer
}

         # Put all other parameters here in separate if blocks which you want to change.
$finalString=$finalString+$item[0]+"="+$item[1]+";"
}

      [string] $finalXmlNode = "<odc:ConnectionString>"+$finalString+"</odc:ConnectionString>"
[string] $finalXml = $xmlPart.OuterXml
[int] $k = $finalXml.IndexOf("<odc:ConnectionString>")
[int] $l = $finalXml.IndexOf("</odc:ConnectionString>")
$finalXml=$finalXml.Remove($k,($l-$k+23))
$finalXml=$finalXml.Insert($k,$finalXmlNode)
[string] $odc=$d.Remove($i,($j-$i+27))

      $odc=$odc.Insert($i,$finalXml)
Set-Content -Path $fileName -Value $odc -Force

   }

}

$site = New-Object Microsoft.SharePoint.SPSite($BICenterSite)
$web = $site.OpenWeb()
$list = $web.Lists[$listName]
$fileCollection = $list.RootFolder.SubFolders[$SubFolderName].Files
# Uploading the ODC files on SharePoint server
foreach ($file in $fileEntry)
{
$stream = $file.OpenRead()
$uploaded = $fileCollection.Add($file.Name, $stream, $TRUE)
$uploaded.Item.File.Publish("Published by PowerShell script")
"Uploaded " + $file.Name
if ($stream) {$stream.Dispose()}

}

# Disposing the web and site objects
if ($web) {$web.Dispose()}
if ($site) {$site.Dispose()}

Conclusion

This post explained how to write code to update ODC files programmatically. This approach can be used to update the ODC files when moving them from one environment to another. The PowerShell script can be useful for automated deployment of ODC files.

You can find more information on the topics discussed here at the following locations:

Create and Use a Data Connection Library

Use an Office Data Connection (.odc) with Reports (Reporting Services in SharePoint Integrated Mode)

Technorati Tags: Shubh Raj Singh,Office Data Connection