Friday, September 14, 2007

K2.net InfoPath Document Attachment Solution

1. Introduction
This article will provide a solution for the document attachment issue with K2.net as attaching documents into data fields can cause the K2.net database to grow at an uncontrolled rate. This issue commonly arises with InfoPath but also occurs with an ASP.net or Win Form workflow. Many workflows have a requirement for attaching documents. An example would be if a user makes a purchase request maybe some legal document must be signed and then attached to the submission form.

2. Problem Background
If InfoPath is being used developers will want to use the document attachment functionality to attach the file to the InfoPath form. The problem is that InfoPath will take the attached document and serialize it into the XML of the InfoPath form. This is a problem as the size of the InfoPath XML file will grow as each file is attached. This becomes even a bigger problem when the XML is attached into a K2.net process as the K2.net database will grow significantly with each process instance.

The K2.net database will continue to grow because the XML of the InfoPath form will be stored numerous times; this is how it works. When an InfoPath form is attached to a K2 process the XML for the InfoPath form is stored at the process level as well as at the activity level. The reason why is each destination user (which could have their own slot) may require their own copy of the XML. In the preceding rule of the activity where the InfoPath client event resides, some code is generated that will copy the XML from process level into each activity instance for each and every destination user. Then on the succeeding rule, the XML from the activity instance of the user who finishes the activity will have their XML copied back into the process level.

Understanding this let’s gain an understanding of what will happen if a document is attached to the InfoPath form. If there is a process that has a three step approval, for the first approval there are ten possible destination users, for the second there are five and for the third there are two. In this example the originator creates an InfoPath form and adds three attachments to the InfoPath form each 1MB. Because the documents are serialized into the XML for the InfoPath, 54 MB is the total minimum amount of space that would be consumed in the database per process instance (3MB for the process and 3 MB for each activity instance). In reality most business documents are not 1MB. Note this does not include consumption of space for the other data fields in the process. This number will go up if there is any rejection paths as new activity instances are created. The amount of space can even grow more if an audit trail is turned on for the XML.

This is not a fault or a short coming of the K2.net database. The K2.net database is a database that has been specifically normalized for workflow statement management, not for managing unique data points. This issue would applicable not just InfoPath forms, this issue would be present if any file attachment is serialized into either process or activities data fields.

3. Solution
A solution for this problem is to store the attached documents externally. This can be simply resolved by storing the documents externally in WSS. To accomplish the solution the WSS web services provided by K2.net will be reused. The solution below was initially developed for K2.net 2003, InfoPath 2003 and WSS 2.0 but was a reused successfully with K2.net 2003, InfoPath 2007 and WSS 3.0. This solution would also apply to a BlackPearl implementation.


4. Web Services
The following web service is written in VB.net can be translated to C# if needed. There are three web methods that will be created: UploadFileToFolder, UploadInfoPathAttachmentToFolder and GetFilesFromFolder.

The web service will require a reference to http://[servername]/_vti_bin/K2SPSList.asmx. This is the web service that is used by K2.net.

4.1 UploadFileToFolder
This web method purpose is to upload a file to a folder in SharePoint. This web service requires a folder name and a document name. In this case, the folder name should be a unique name like a requisition number. The web method will create the folder if the folder does not already exist. If the document with the same name already exists, the web method will overwrite it (that could be changed).

Note that whatever account is used to connect to the K2.net web service will upload the file. This will not be the user is actually uploading the file (like the destination user).

Public Sub UploadFileToFolder(ByVal strWssServerUrl As String, ByVal strWssSite As String, _
ByVal strWssSiteDocLib As String, ByVal strFolderName As String, ByVal strFileName As String, _
ByVal bytes As [Byte]())

Dim objK2Wss As K2WssWebService.K2SPSList

Try
If strFolderName Is Nothing Or strFolderName = "" Or strFolderName.Length = 0 Then
Throw New Exception("A Unique ID is required.")
End If

objK2Wss = New K2WssWebService.K2SPSList
objK2Wss.Url = GetK2WssWebServiceURL()
'connect with default IE account
'objK2Wss.Credentials = System.Net.CredentialCache.DefaultCredentials
'connect with a service account
objK2Wss.Credentials = New System.Net.NetworkCredential(GetWSSUserString(), _
GetWSSUserPasswordString(), GetWSSUserDomainString())

Dim strFullFolderName As String = strWssSiteDocLib & "/" & strFolderName

' Check if folder exists
If Not objK2Wss.FolderExist(strWssSite, strFullFolderName) Then
' Create the folder
Dim strCreateFolderErrorMsg As String
objK2Wss.CreateFolder(strWssSite, _
strFullFolderName, strCreateFolderErrorMsg)

' Check if error creating the folder
If strCreateFolderErrorMsg <> "" Then
Throw New Exception(strCreateFolderErrorMsg)
End If
End If

' upload the document
Dim strUploadFileErrorMsg As String
objK2Wss.UploadDocument(strWssServerUrl, strWssSite, _
strFullFolderName, strFileName, _
bytes, True, strUploadFileErrorMsg)

' Check if there was an error uploading the file
If strUploadFileErrorMsg <> "" Then
Throw New Exception(strUploadFileErrorMsg)
End If
Catch ex As Exception
Throw ex
Finally
objK2Wss = Nothing
End Try
End Sub

4.2 UploadInfoPathAttachmentToFolder
This method will call UploadFileToFolder but its specific purpose is to accept a document that has been attached into an InfoPath form. Documents that have been attached into an InfoPath form have some header information added to the bits of the document. For instance, the file name needs to be stripped of the bits.


Public Sub UploadInfoPathAttachmentToFolder(ByVal strWssServerUrl As String, ByVal strWssSite As String, _
ByVal strWssSiteDocLib As String, ByVal strFolderName As String, ByVal byteIPFileAttachment As [Byte]())

Dim i As Integer

Try
' Get the length of the file name from the IP file attachment header
Dim iNameBufferLen As Integer = byteIPFileAttachment(20) * 2

' Create binary array for the file name
Dim byteFileName(iNameBufferLen) As Byte

' Get the file name
For i = 0 To iNameBufferLen
byteFileName(i) = byteIPFileAttachment(24 + i)
Next

' Translate file name to a string variable
Dim asciiChars() As Char = System.Text.UnicodeEncoding.Unicode.GetChars(byteFileName)
Dim strFileName As New String(asciiChars)
strFileName = strFileName.Substring(0, strFileName.Length - 1)

' Create binary arrary for the file. This is
' the total file lenght minue the header and the file name length
Dim byteFileContent(byteIPFileAttachment.Length - (24 + byteFileName.Length)) As Byte

' Get the file bytes
i = 0
For i = 0 To byteFileContent.Length - 1
byteFileContent(i) = byteIPFileAttachment(24 + (byteFileName.Length - 1) + i)
Next

' Upload the file to WSS
UploadFileToFolder(strWssServerUrl, strWssSite, strWssSiteDocLib, strFolderName, strFileName, byteFileContent)
Catch ex As Exception
Throw ex
End Try

End Sub

4.3 GetFilesFromFolder
This web method will retrieve all of the file names from a specific folder in SharePoint and will return an XML document.

Public Function GetFilesFromFolder(ByVal strWssServerUrl As String, ByVal strWssSite As String, _
ByVal strWssSiteDocLib As String, ByVal strFolderName As String) As Xml.XmlDocument

Dim objK2Wss As K2WssWebService.K2SPSList

'XML writer
Dim sw As New System.IO.StringWriter
Dim xtw As New System.Xml.XmlTextWriter(sw)

Try
If strFolderName Is Nothing Or strFolderName = "" Or strFolderName.Length = 0 Then
Throw New Exception("A Unique ID is required.")
End If

objK2Wss = New K2WssWebService.K2SPSList
objK2Wss.Url = GetK2WssWebServiceURL()
objK2Wss.Credentials = New System.Net.NetworkCredential(GetWSSUserString(), _
GetWSSUserPasswordString(), GetWSSUserDomainString())

Dim strFullFolderName As String = strWssSiteDocLib & "/" & strFolderName

' Check if folder exists
If Not objK2Wss.FolderExist(strWssSite, strFullFolderName) Then
' It is valid for this folder to not exist for a request,
' thus this web service should not return an error but empty XML...
' Folders for a request are only created when a document is uploaded.
xtw.WriteStartElement("Files")
xtw.WriteEndElement() 'Files
Else
' get all of the files from web service
Dim strErrorMsg As String
Dim strFiles As String() = objK2Wss.GetFolderFiles(strWssServerUrl, _
strWssSite, strFullFolderName, strErrorMsg)

If strErrorMsg <> "" Then
Throw New Exception(strErrorMsg)
End If

xtw.WriteStartElement("Files")

' Loop over files and build a list of files
' for the unique id
Dim i As Integer
For i = 0 To strFiles.Length - 1
If Not (strFiles(i) Is Nothing) Then
xtw.WriteStartElement("File")
xtw.WriteElementString("FileName", strFiles(i))
xtw.WriteElementString("FileUrl", strWssServerUrl & "/" & _
strWssSite & "/" & strFullFolderName & "/" & _
strFiles(i))
xtw.WriteEndElement() 'File
End If
Next

xtw.WriteEndElement() 'Files
End If

Dim xmlDoc As New System.Xml.XmlDocument
xmlDoc.LoadXml(sw.ToString())

Return xmlDoc
Catch ex As Exception
Throw ex
End Try
End Function

5. How to Connect to InfoPath
To get this hooked up to InfoPath is easy and will require no .net enabled code.


5.1 Add a Binary Data Field
Add a base64Binary data field to the InfoPath form. This value will only be set temporarily and it will be submitted to the web webserive.



Even though this violates one my best practices of not polluting the XSD schema of your InfoPath form with UI specific notes. However with InfoPath it is not possible to have a secondary data source with a base64Binary.

5.1 Add Submit Data Connection
Make a data connection to the UploadInfoPathAttachmentToFolder web method using the Data Connection Wizard. Do the following:

  • Use the “Submit Data” option
  • Select a web service
  • Enter the url to the web service
  • Select the UploadInfoPathAttachmentToFolder web method
  • End a value for all of the parameters from the web method. For the strFolderName make sure you a unique value and for byteIPFileAttachment set the data field from the first step.
  • Then finish the wizard.

5.3 Add Get Files Data Connection
Now create a data connection to the web method to return all of the files using the Data Connection Wizard. Do the following:
  • Prior to this you will need to modify the web service to return a hard coded set of dummy values otherwise you will receive an error while making the data connection. A quick solution is to temporarily change the GetFilesFromFolder to return a hard coded sample XML in the correct format. Make sure that there is more than one node returned in the XML. If not, InfoPath will not infer that it is not possible that the web method can return more than one file.
  • Use the “Receive Data” option
  • Select a web service
  • Enter the url to the web service
  • Select the GetFilesFromFolder web method
  • In the final step select the checkbox to run this when the form is opened to retrieve any files that may already be uploaded. This will only work if the unique number for the folder name is generated before the InfoPath form is opened. If that is not possible, modify the code in the web method to not throw an exception when strFolderName is null. Instead return back an empty string of XML.
  • Finish the wizard
  • Remove the hard coded XML.

5.4 Add Controls to Form
First move the base64Binary field that was added in the first step to the form as a document attachment control. Then drag and drop a button onto the form. Finally go Data Source task pane and drag and drop the File collection for the GetFiles data connection as a repeating table.

Something similar to following can be created.

For the repeating table change the control within it to be a hyperlink control and use the following configuration.


5.5 Create Rules for the Button
Now we need to add some rules to the Upload File button. Double click on the button and the properties window should appear. Press the Rules button and then press the Add button to create a new rule. First add a Set Condition to make sure that the File (base64Binary field) is not blank. Then add an action to upload the attachment. Then clear out the File (base64Binary) field. Finally add another action to return all the files that are currently available.

5.6 User Experience
The user experience will be that they will add a file to the form using the InfoPath file attachment control. They will then press the button and then the file name will appear in the repeating list immediately below. When they click on the link the file will be opened in a new window.

6. Using this Solution
Now you have the ability to start attaching documents to your processes without adversely affecting the size of the InfoPath form. As well, the services can be used outside of InfoPath and are re-useable for all processes you create in the future like SmartForms, Custom ASP.net pages, WinForms, etc.

4 comments:

sam said...

How about renaming the attachment based on a form field? i.e I have a text field called "EmpFile" and lets say its value is fileA.txt and attachment file is fileB.txt.

How can I save the attachment as fileA.txt (which is value of "EmpFile" text field)
thanks

Jason Apergis said...

Sam,

If you need to do that you will need to add a parameter to the web service to take in that value. FYI this is a solution that I wrote for K2.net 2003. I have since updated it for K2 blackpearl but I have not had the time to post it. It is on my todo list.

Jason

Jamey said...

Any thoughts on how to add 'drag and drop' functionality for selecting the file(s) to be uploaded? This would replace the infopath attachment control and let users simply drag attachments onto the form.

Jason Apergis said...

Jamey - have not put much thought to it but would be a nice usability feature if Microsoft provided that for InfoPath...