如何以编程方式更新 ODC 文件 (Shubh Raj Singh)

Categories: Other
Tags: No Tags
Comments: No Comments
Published on: 2012 年 02 月 29 日

[原文发表地址]  How to Programmatically Update an ODC File (Shubh Raj Singh)

[原文发表时间]  2012-02-09 9:16 AM

这篇文章描述了如何通过使用 SharePoint 功能,或.Net代码编写的控制台应用程序或PowerShell 脚本,以编程方式更新ODC 文件。有关内容:

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

Office 数据连接文件的概述

Office 数据连接 (ODC) 文件包含了有关如何连接到数据源的连接信息,并供读者在各种文档中(例如Microsoft Excel 工作簿)检索数据。连接信息可被存储在工作簿或连接文件中,例如 (ODC) 文件 (.odc) 或通用的数据连接 (UDC) 文件 (.udcx)。

通常,当构建一个从数据源获取数据的Excel服务报告时,会用到ODC 文件。作为执行的一部分,此报告可以部署在多个环境中,如开发、 过渡、 测试和生产环境中。每个环境可能会有不同的数据源,因此连接字符串也会不同。连接字符串应在基于它所部署的环境的ODC 文件中更改。这通常是一个手动步骤 ;不过这项任务也可以自动进行。这种方法就是这篇文章的主题。

你可以通过使用 SharePoint 功能或.Net 代码编写的控制台应用程序或PowerShell 脚本来以编程方式更新ODC 文件。

使用控制台应用程序的方法,需要在SharePoint 网站发布文件以及一些额外的代码或本篇文章中没有涵盖到的部署步骤。PowerShell 方法需要上传以及发布ODC 文件到 SharePoint 网站。

一个 ODC 文件可供一个或多个 Excel 文件使用。ODC 文件包含以下类型的信息:

  • 连接字符串
  • 命令类型
  • 命令文本
  • SSO 应用程序 ID
  • 凭据方法
  • 是否始终使用连接文件

ODC 文件的说明

ODC 文件是一个包含了 XML 嵌入部分的 HTML 文件。ODC 文件中的 XML 为数据源确定了核心的连接信息。此信息包括:

  • 数据提供特定的连接字符串,用来建立并打开到数据源的连接,
  • 用于获取数据的查询文本
  • 特定表或从中获取数据的多维数据集的名称
  • 关于如何阐释查询文本、 多维数据集或表名称的提示
  • 该标志指明 ODC 文件总是用于连接和查询数据源(相对于应用程序使用数据连接信息的缓存版本)
  • 需要使用数据源的特定身份验证信息。如果服务器应用程序正在使用 ODC 文件获取数据,此信息将通常用于连接数据源

以下的 HTML 是 ODC 文件内的 ODC 连接字符串的示例:

<odc:OfficeDataConnection xmlns:odc="urn:schemas-microsoft-com:office:odc" xmlns="http://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>

一个C# 代码示例

以下是一个简单的控制台应用程序示例,演示了如何更改 ODC 文件内的值。此代码可在功能或控制台应用程序中与从配置文件中所提供的之前参数值一起使用。此代码更改了上面所示的数据连接XML 中突出显示的值。

   1: namespace UpdateODCFile 

   2: { 

   3: using System.Linq; 

   4: using System.Text; 

   5: using System.Xml;

   6:  

   7: /// <summary> 

   8: /// Class to change the ODC file parameters 

   9: /// </summary>

  10:  

  11: class Program 

  12: {

  13:  

  14: /// <summary> 

  15: /// Index from where tag begins 

  16: /// </summary>

  17:  

  18: private static int sourceIndex = 0;

  19:  

  20: /// <summary> 

  21: /// Index from where tag ends 

  22: /// </summary>

  23:  

  24: private static int destinationIndex = 0;

  25:  

  26: static void Main(string[] args) 

  27: { 

  28: // You can keep the source an destination file path same if you want to 

  29: // overwrite the existing file with new values.

  30:  

  31: string odcFilePath = @"C:\Reports\Connection.odc"; 

  32: string destinationFilePath = @"C:\Reports\Connection.odc";

  33:  

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

  35:  

  36: string databaseName = "<your database name>"; 

  37: string serverName = "<your server name>"; 

  38: string SSOApplicationID = "<application ID>";string provider = "<Provider>"; 

  39: string integratedSecurity = "<integrated security>"; 

  40: string persistSecurityInfo = "<persist security info>"; 

  41: string useProcedure = "<use procedure>"; 

  42: string autoTranslate = "<auto translate>"; 

  43: string packetSize = "<packet size>"; 

  44: string workSatationID = "<workstation ID>"; 

  45: string encryptionData = "<encryption data>"; 

  46: string tagWithCollation = "<tag with column collation>";

下面的代码读取了 ODC 文件,并从它检索ODC 连接XML 数据。

   1: string xmlConnection = GetConnectionString(odcFilePath); 

   2: XmlDocument odcXmlConnection = new XmlDocument(); 

   3: odcXmlConnection.LoadXml(xmlConnection); 

   4: XmlNamespaceManager nameManager = new XmlNamespaceManager(odcXmlConnection.NameTable); 

   5: nameManager.AddNamespace("odc", odcXmlConnection.DocumentElement.NamespaceURI); 

   6: XmlNodeList nodelistConnectionString = odcXmlConnection.SelectNodes("//odc:Connection/odc:ConnectionString", nameManager);

下面的代码更改了上面提到的连接属性。

   1: StringBuilder finalConnectionString = CreateNewConnectionString(databaseName, serverName, provider, integratedSecurity, 

   2: persistSecurityInfo, useProcedure, autoTranslate, packetSize, workSatationID, encryptionData, tagWithCollation, 

   3: nodelistConnectionString); 

   4: nodelistConnectionString[0].InnerText = finalConnectionString.ToString();

下面的代码更改了XML中的 SSOApplicationID 属性。

   1: XmlNodeList nodelistSSOApplicationID = odcXmlConnection.SelectNodes("//odc:Connection/odc:SSOApplicationID", nameManager); 

   2: nodelistSSOApplicationID[0].InnerText = SSOApplicationID;

下面的代码将更新的文件保存到了磁盘上。完成后,该文件可以发布到 SharePoint。

   1: SaveConnectionString(odcFilePath, destinationFilePath, odcXmlConnection.OuterXml);

   2:  

   3: }

下面的代码查找了 ODC 文件中的连接字符串标记,并返回完整的连接字符串。

   1: /// Finds the connection string in the ODC file 

   2: /// </summary>

   3:  

   4: /// <param name="filePath">path of ODC file</param> 

   5: /// <returns>office data connection xml</returns>

   6:  

   7: public static string GetConnectionString(string filePath) 

   8: { 

   9:    string xmlConnection = string.Empty; 

  10:    System.IO.StreamReader myFile = new System.IO.StreamReader(filePath); 

  11:    string myString = myFile.ReadToEnd(); 

  12:    sourceIndex = myString.IndexOf("<odc:OfficeDataConnection"); 

  13:    destinationIndex = myString.IndexOf("</odc:OfficeDataConnection"); 

  14:    xmlConnection = myString.Substring(sourceIndex, destinationIndex - sourceIndex + 27); 

  15:    myFile.Close();

  16:  

  17:    return xmlConnection;

  18:  

  19: }

下面的代码将连接字符串标记保存到 ODC 文件中,并将其保存到磁盘。

   1: /// <summary> 

   2: /// save the changed ODC file 

   3: /// </summary>

   4:  

   5: /// <param name="filePath">path at which you want to read the ODC file</param> 

   6: /// <param name="newFilePath">path at which you want to save</param> 

   7: /// <param name="connectionString">new data connection string to be changed</param>

   8:  

   9: public static void SaveConnectionString(string filePath, string newFilePath, string connectionString) 

  10: { 

  11:    string xmlConnection = string.Empty; 

  12:    System.IO.StreamReader myFile = new System.IO.StreamReader(filePath); 

  13:    string myString = myFile.ReadToEnd(); 

  14:    myFile.Close(); 

  15:    string lessString = myString.Remove(sourceIndex, destinationIndex - sourceIndex + 27); 

  16:    myString = lessString.Insert(sourceIndex, connectionString); 

  17:    System.IO.StreamWriter writer = new System.IO.StreamWriter(newFilePath); 

  18:    writer.Write(myString); 

  19: } 

  20:  

下面的代码在ODC 文件中查找属性,然后更改它们。如果属性不需要任何更改,可以从这段代码中删除它。新的连接字符串将返回到Main功能。

   1: /// <summary> 

   2: /// Function to replace the old values with new one. 

   3: /// </summary>

   4:  

   5: /// <param name="databaseName">database Name</param> 

   6: /// <param name="serverName">server Name</param> 

   7: /// <param name="provider">provider</param> 

   8: /// <param name="integratedSecurity">integrated Security</param> 

   9: /// <param name="persistSecurityInfo">persist Security Info</param> 

  10: /// <param name="useProcedure">use Procedure</param> 

  11: /// <param name="autoTranslate">auto Translate</param> 

  12: /// <param name="packetSize">packet Size</param> 

  13: /// <param name="workSatationID">work Satation ID</param> 

  14: /// <param name="encryptionData">encryption Data</param> 

  15: /// <param name="tagWithCollation">tag With Collation</param> 

  16: /// <param name="nodelistConnectionString">nodelist Connection String</param> 

  17: /// <returns>final connection string</returns>

  18:  

19: 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)

  20: { 

  21:    string[] connectionStringArray = nodelistConnectionString[0].InnerText.Split(';'); 

  22:    StringBuilder finalConnectionString = new StringBuilder(); 

  23:    foreach (string connections in connectionStringArray.ToList()) 

  24:    { 

  25:       string[] splitOnEqual = connections.Split('='); 

  26:       switch(splitOnEqual[0]) 

  27:       { 

  28:          case "Initial Catalog": splitOnEqual[1] = databaseName; 

  29:          break;

  30:  

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

  32:          break;

  33:  

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

  35:          break;

  36:  

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

  38:          break;

  39:  

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

  41:          break;

  42:  

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

  44:          break;

  45:  

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

  47:          break;

  48:  

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

  50:          break;

  51:  

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

  53:          break;

  54:  

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

  56:          break;

  57:  

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

  59:          break;

  60:  

  61:       }

  62:  

  63:       finalConnectionString = finalConnectionString.Append(splitOnEqual[0]); 

  64:       finalConnectionString = finalConnectionString.Append("="); 

  65:       finalConnectionString = finalConnectionString.Append(splitOnEqual[1]); 

  66:       finalConnectionString = finalConnectionString.Append(";");

  67:  

  68:    }

  69:  

  70:    finalConnectionString = finalConnectionString.Remove((finalConnectionString.Length-1), 1); 

  71:    return finalConnectionString;

  72:  

  73: } 

  74: } 

  75: }

  76:  

使用 PowerShell代码示例

下面是一个 PowerShell 脚本用来完成与之前所述的相同任务。

   1: # Initialize the variables

   2:  

   3: $BICenterSite = "<Site where you have to upload the updated ODCs>" 

   4: $reportingDBname = "<Database Name>" 

   5: $databaseServer = "<Database Server Name>" 

   6: $listName = "<List Name where ODCs have to be uploaded>" 

   7: $ODCFilesFolder = "<Folder path where all the ODCs are present>" 

   8: $SubFolderName = "<Sub Folder Name>" 

   9: $fileEntry = [IO.Directory]::GetFiles($ODCFilesFolder)

  10:  

  11: # reading all the ODC files from the given folder 

  12: foreach($fileName in $fileEntry) 

  13: { 

  14:    $w=Get-Content $fileName 

  15:    $d=[string]$w 

  16:    if($d.Contains("<odc:OfficeDataConnection")) 

  17:    { 

  18:       [int] $i = $d.IndexOf("<odc:OfficeDataConnection") 

  19:       [int] $j = $d.IndexOf("</odc:OfficeDataConnection>") 

  20:       [xml] $xmlPart = $d.SubString($i, ($j-$i+27)) 

  21:       $con = $xmlPart.OfficeDataConnection.Connection | Select ConnectionString 

  22:       [string] $connectionString = $con.ConnectionString 

  23:       [string] $finalString = "" 

  24:       $stringArray = $connectionString.Split(";")

  25:  

  26:       foreach($element in $stringArray) 

  27:       { 

  28:          $item = $element.Split("=") 

  29:          if($item[0] -eq "Initial Catalog") 

  30:          { 

  31:             $item[1]=$reportingDBname 

  32:          }

  33:  

  34:          if($item[0] -eq "Data Source") 

  35:          { 

  36:             $item[1]=$databaseServer 

  37:          }

  38:  

  39:          # Put all other parameters here in separate if blocks which you want to change. 

  40:          $finalString=$finalString+$item[0]+"="+$item[1]+";" 

  41:       }

  42:  

  43:       [string] $finalXmlNode = "<odc:ConnectionString>"+$finalString+"</odc:ConnectionString>" 

  44:       [string] $finalXml = $xmlPart.OuterXml 

  45:       [int] $k = $finalXml.IndexOf("<odc:ConnectionString>") 

  46:       [int] $l = $finalXml.IndexOf("</odc:ConnectionString>") 

  47:       $finalXml=$finalXml.Remove($k,($l-$k+23)) 

  48:       $finalXml=$finalXml.Insert($k,$finalXmlNode) 

  49:       [string] $odc=$d.Remove($i,($j-$i+27))

  50:  

  51:       $odc=$odc.Insert($i,$finalXml) 

  52:       Set-Content -Path $fileName -Value $odc -Force

  53:  

  54:    }

  55:  

  56: }

  57:  

  58: $site = New-Object Microsoft.SharePoint.SPSite($BICenterSite) 

  59: $web = $site.OpenWeb() 

  60: $list = $web.Lists[$listName] 

  61: $fileCollection = $list.RootFolder.SubFolders[$SubFolderName].Files 

  62: # Uploading the ODC files on SharePoint server 

  63: foreach ($file in $fileEntry) 

  64: { 

  65:    $stream = $file.OpenRead() 

  66:    $uploaded = $fileCollection.Add($file.Name, $stream, $TRUE) 

  67:    $uploaded.Item.File.Publish("Published by PowerShell script") 

  68:    "Uploaded " + $file.Name 

  69:    if ($stream) {$stream.Dispose()}

  70:  

  71: }

  72:  

  73: # Disposing the web and site objects 

  74: if ($web) {$web.Dispose()} 

  75: if ($site) {$site.Dispose()}

  76:  

结论

这篇文章解释了如何编写代码来以编程方式更新 ODC 文件。当从一个环境移动ODC到另一个环境中时,这种方法可用于更新ODC文件。PowerShell 脚本可用于自动化部署 ODC 文件。

你可以在以下地方找到本文中讨论的主题的详细信息:

创建和使用数据连接库

将 Reports( SharePoint 集成模式下的报告服务)与Office 数据连接 (.odc)一起使用

No Comments - Leave a comment

Leave a comment


Welcome , today is 星期六, 2017 年 06 月 24 日