Generate_UDCX__SQL

This will generate a UDCX file to query a SQL table.
Language:
PowerShell
Keywords:
UDCX
Code Snippet

Function Generate_UDCX__SQL( [Microsoft.SharePoint.SPList]$DataConnectionLibrary

                           , [System.String]$tFileName

                           , [System.String]$UdcName

                           , [System.String]$UdcDescription

                           , [System.String]$SqlConnString

                           , [System.String]$SqlQuery

                           ) {

    if (!$DataConnectionLibrary.RootFolder.Files[$tFileName].Exists)

    {

 

        $ms = New-Object System.Io.MemoryStream

        $sw = New-Object System.Io.StreamWriter($ms)

 

        $sw.WriteLine([System.String]::Format("<?xml version=`"1.0`" encoding=`"UTF-8`"?>"))

        $sw.WriteLine([System.String]::Format("<?MicrosoftWindowsSharePointServices ContentTypeID=`"0x010100B4CBD48E029A4ad8B62CB0E41868F2B0`"?>"))                             # Data Connection

        $sw.WriteLine([System.String]::Format("<udc:DataSource MajorVersion=`"2`" MinorVersion=`"0`" xmlns:udc=`"http://schemas.microsoft.com/office/infopath/2006/udc`">"))    # v2 = InfoPath

        $sw.WriteLine([System.String]::Format(" <udc:Name>{0}</udc:Name>", $UdcName))

        $sw.WriteLine([System.String]::Format(" <udc:Description>{0}</udc:Description>", $UdcDescription))

        $sw.WriteLine([System.String]::Format(" <udc:Type MajorVersion=`"2`" MinorVersion=`"0`" Type=`"Database`">"))

        $sw.WriteLine([System.String]::Format("       <udc:SubType MajorVersion=`"0`" MinorVersion=`"0`" Type=`"`"/>"))

        $sw.WriteLine([System.String]::Format(" </udc:Type>"))

        $sw.WriteLine([System.String]::Format(" <udc:ConnectionInfo Purpose=`"ReadOnly`" AltDataSource=`"`">"))

        $sw.WriteLine([System.String]::Format("       <udc:WsdlUrl>{0}</udc:WsdlUrl>", $WsUrl_Wsdl))

        $sw.WriteLine([System.String]::Format("       <udc:SelectCommand>"))

        $sw.WriteLine([System.String]::Format("             <udc:ListId/>"))

        $sw.WriteLine([System.String]::Format("             <udc:WebUrl/>"))

        $sw.WriteLine([System.String]::Format("             <udc:ConnectionString>{0}</udc:ConnectionString>", $SqlConnString))

        $sw.WriteLine([System.String]::Format("             <udc:ServiceUrl UseFormsServiceProxy=`"false`"/>"))

        $sw.WriteLine([System.String]::Format("             <udc:SoapAction/>"))

        $sw.WriteLine([System.String]::Format("             <udc:Query>{0}</udc:Query>", $SqlQuery))

        $sw.WriteLine([System.String]::Format("       </udc:SelectCommand>"))

        $sw.WriteLine([System.String]::Format("       <udc:UpdateCommand>"))

        $sw.WriteLine([System.String]::Format("             <udc:ServiceUrl UseFormsServiceProxy=`"false`"/>"))

        $sw.WriteLine([System.String]::Format("             <udc:SoapAction/>"))

        $sw.WriteLine([System.String]::Format("             <udc:Submit/>"))

        $sw.WriteLine([System.String]::Format("             <udc:FileName>Specify a filename or formula</udc:FileName>"))

        $sw.WriteLine([System.String]::Format("             <udc:FolderName AllowOverwrite=`"`"/>"))

        $sw.WriteLine([System.String]::Format("       </udc:UpdateCommand>"))

        $sw.WriteLine([System.String]::Format("       <!--udc:Authentication><udc:SSO AppId='' CredentialType='' /></udc:Authentication-->"))

        $sw.WriteLine([System.String]::Format(" </udc:ConnectionInfo>"))

        $sw.WriteLine([System.String]::Format("</udc:DataSource>"))

        #$sw.WriteLine([System.String]::Format("")

        $sw.Flush()

 

        $tFileName_SiteRelativeUrl = $DataConnectionLibrary.RootFolder.Url + "/" + $tFileName

 

        Write-Host([System.String]::Format("Creating [{0}] : UDCX to SQL", $tFileName_SiteRelativeUrl))

 

        # store file contents

        $tFile = $DataConnectionLibrary.RootFolder.Files.Add($tFileName_SiteRelativeUrl, $ms)

 

        # Update metadata (Title, ContentType), approve if necessary

        $tItem = $tFile.Item

        $tItem["Title"] = $tFileName

        $tItem["ContentTypeId"] = $DataConnectionLibrary.ContentTypes["Universal Data Connection File"].Id

        $tItem.Update()

 

        if ($tItem.ParentList.EnableModeration) {

            $tFile.Approve("")

        }

    }

 

} # Function Generate_UDCX__SQL


Example

Generate_UDCX__SQL $tWeb.Lists["DCL"] `

                   "Customers.udcx" `

                   "Customers" `

                   "Return list of Customers from the CRM database" `

                   "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=CRMDB;Data Source=SQLSERVER;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=LOCAL;Use Encryption for Data=False;Tag with column collation when possible=False" `

                   "SELECT [ID], [Name] from [dbo].[Customers] ORDER BY [Name]"


Created 2012-03-22
comments powered by Disqus
Login