Integrating Domino Data with .Net

For all articles relating to Domino to .Net conversion please see this post

---

Database can be downloaded here

For sometime I have been working on a generic solution to get Lotus Notes/Domino data out of domino and published in such a way that is is available to non-notes applications. This includes .Net which I currently develop in along with Domino. There are a number of third party products to do this Proposion (http://www.proposion.com/ ) is a good one, but it is .net only and costs $$. I prefer a DIY solution.

Getting most of the data out of notes in pretty easy, except for the rich-text. Domino stores richtext in a proprietory way. You can export richtext via DXL, but this tends to produce masses of XML that is useless to everything but Domino. I wanted to come up with a solution that would allow me to get the rich-text in a way that could be used by other apps. The best most generic approach would be as html.

If you view a notes document via a browser the domino server automatically renders all of the visible data( and form design) to html. But to get a single field from this form is difficult because you don’t know where one field starts and .Really what you need is a way of retrieving the html data of a single richtext field in some dynamic way… DXL to the rescue.

Domino has something called form formulas. This allows the developer to say which form is used to show a document in a speciifc view. Using the DXL Importer you can dynamically create forms and views. So it is possible to dynamically create a view and a form for each richtext field on a document that will only show the html of a richtext field when that document is viewed from a browser.

What I have done it written a lotusscript agent that produces an xml document representing the requested notes document as below.

<RESPONSE>
<DOCUMENT>
<ID>0AEEF2158B83FEA04A2571D1000C08D1</ID>
<CREATED>21/08/2006 12:11:26</CREATED>
<MODIFIED>11/09/2006 10:36:40</MODIFIED>
+< ITEMS ></ ITEMS>
</DOCUMENT>
</RESPONSE>

The Items element contains all of the notes item data. Any text,date,author,reader,names or number field is represented in the xml and the values can be read directly from the xml (such as below)

<ITEM>
<NAME>$Revisions</NAME>
<TYPE>1024</TYPE>
<VALUES>
<VALUE>30/12/1899 00:00:00</VALUE>
<VALUE>21/08/2006 12:11:49</VALUE>
<VALUE>21/08/2006 13:05:35</VALUE>
<VALUE>21/08/2006 13:36:23</VALUE>
<VALUE>21/08/2006 13:36:58</VALUE>
<VALUE>11/09/2006 10:34:55</VALUE>
</VALUES>
</ITEM>
<ITEM>
<NAME>$UpdatedBy</NAME>
<TYPE>1074</TYPE>
<VALUES>
<VALUE>CN=User1/O=MyOU</VALUE>
<VALUE>CN=User2/O=MyOU</VALUE>
</VALUES>
</ITEM>
<ITEM>
<NAME>DeliveredDate</NAME>
<TYPE>1024</TYPE>
<VALUES>
<VALUE>21/08/2006 12:11:49</VALUE>
</VALUES>
</ITEM>
<ITEM>
<NAME>From</NAME>
<TYPE>1074</TYPE>
<VALUES>
<VALUE>CN=TheSender/O=MyOU@MyDomain</VALUE>
</VALUES>
</ITEM>
<ITEM>
<NAME>Subject</NAME>
<TYPE>1280</TYPE>
<VALUES>
<VALUE>This is a nice message</VALUE>
</VALUES>
</ITEM>
<ITEM>
<NAME>Assign</NAME>
<TYPE>1280</TYPE>
<VALUES>
<VALUE>Me</VALUE>
</VALUES>
</ITEM>

Rich text fields (TYPE 1) are shown with an RTURL element. The RTURL Element value is a url that will render the HTML of that field only. This html can then be stored in SQL, parsed/filtered etc etc

<ITEM>
<NAME>Body</NAME>
<TYPE>1</TYPE>
<RTURL>/CES/IMRequests.nsf/$$DotNetDocsView_Body/1AF569B27F9E66074A256FCD007BE90E?Open</RTURL>
<FILES>
<FILE>folder.gif</FILE>
</FILES>
</ITEM>

Attachments (TYPE 1024 ) are shown with a URL element. The URL value is a url to the file attachment(binary stream). This stream can be stored in SQL or save to disk.

<ITEM>
<NAME>$FILE</NAME>
<TYPE>1084</TYPE>
<SOURCE>folder.gif</SOURCE>
<URL>/DEV/NotesExport.nsf/$$DotNetDocsView_$FILE/1AF569B27F9E66074A256FCD007BE90E/$FILE/folder.gif</URL>
</ITEM>

The text of the SOURCE Element of the file matches the name of the FILE element in the RichText Item. So it is possible to match which richtext field a file belongs to if there are more than one rich-text fields on a form.

The agent also support a couple of other commands that allows for the view navigation to gather a document collection so that data extraction can be automated in bulk if required.

To see the agent in action you need to copy it to an existing notes database and sign it with an id that can run agents on your domino server and has manager access to the database. Then from a browser open the url

http://<dominoserver>/<pathtodb>/dotNetDataViewer.xml?OpenAgent&Action=recreateview

This will create a generic the default view used by the agent to return data. This view is called $$DotNetDocsView. You can go into the database and update the design and change the selection formula as required.

Next in the browser go to the url

http://<dominoserver>/<pathtodb>/dotNetDataViewer.xml?OpenAgent&Action=viewchunk

This will give you an xml document that contains the ids of the first 100 documents in the $$DotNetDocsView. ( if you specify a Param=<docid> on the viewchunk action it will return the next 100 documents after the specified <docid>).

Using one of the ids listed you can see how a document is represented by going to the url

http://<dominoserver>/<pathtodb>/dotNetDataViewer.xml?OpenAgent&Action= viewdoc&Param=<docid_of_document>

Where <docid_of_document> is the universal id of the doucment to view.
This will generate the xml for this document in the format above.

I am currently writing a .net application that uses this agent to pull data out of notes using this technique. It is very easy because you simply have to create a httprequest; read it into a buffer and then parse it through an xmldocument. You then have all of the notes data from a remote notes server available to your .net app as an xml document. You could then bind it to a datatable or whatever..

There is a little bit of extra formatting required of the html that comes from the richtext filed to strip out any notes related URLs, but that can be handled by the application that does the data querying/extraction.

The agent that does all this work is called “dotNetDataViewer.xml”