Metro Toronto .NET User Group Meeting September 8th: Managed Code in Sql Server 2005

On September 8th I'll be speaking at the .NET User Group here in Toronto. I'll be talking about how developers can take advantage of Sql Server 2005's ability to host managed code. Full abstract and registration details are here.

VSTS Beta 3 and Go Live News

There is good news and bad news with Somasegar's blog today (http://blogs.msdn.com/somasegar/archive/2005/08/22/451026.aspx)

The bad news is that VSTS team foundation server is not going to be released on November 7th with the rest of Visual Studio 2005. We could have speculated that much so that should not be news to too many people. Instead, we'll have to wait for the first quarter of 2006.

The good news is that in September, we are going to be seeing Release Candidates for the rest of Visual Studio 2005 and a Beta 3 of Team Foundation Server. The really good news is that sounds like the quality is going to take a huge leap - so much so that MS is going to be offerring a Go Live License and support to premier customers who want to start using VSTS for their development efforts.

Unexpected Locking in SQL Server 2005

I was working a little bit with SQL Server 2005 today and ran across an unexpected (at least to me) situation. From within the Management Studio, I opened up a table to view the contents.  Nothing complicated, although I did modify the default SQL slightly to reorder the results.  Then, in another query tab, I executed a script that did a DROP TRIGGER against the table that I had just opened. Problem is, the DROP statement just hung there. Waiting on a lock.  Specifically, waiting on exclusive access to the table. A lock that it couldn’t get because I had opened the table through Management Studio. Like I said, unexpected.

The solution was simple once I saw who was holding the lock. Close down the tab and everything was hunky-dory.  But the time I spent researching the problem is gone, never to be recovered. I also did a bit of digging into why this was so unexpected. It doesn’t appear to happen in Enterprise Manager. Simply opening a table and retrieve all of the rows does not establish a lock on the records. Doesn’t even appear to if you start to edit a particular record. Chalk it up to one more thing that is new and improved.

 

Getting out of the Longhorn Habit

One of the problems with keeping one eye on the next version of software is getting too comfortable with the name.  I still say Whidbey instead of VS.NET 2005 and it will take me a while yet before that problem goes away. And Microsoft has just added another synonym to that problem.  Please welcome Microsoft Vista as the ‘real’ name for that which has been called Longhorn for what, about two years. Yeah, like I’m going to be able to make that adjustment quickly.

Invalid XPath Expression in a BizTalk Mapping

While working on a BizTalk mapping for a client, I ran across an unusual problem.  When the mapping was tested, an XSL transform error was thrown. Specifically, the error message was:

XSL transform error: (0,0) : 'userCSharp:(string(*[local-name()='Times' and namespace-uri()='']/*[local-name()='WindowCloseTime' and namespace-uri()='']/text()))' is an invalid XPath expression. 'userCSharp:(string(*[local-name()='Times' and namespace-uri()='']/*[local-name()='WindowCloseTime' and namespace-uri()='']/text()))' has an invalid qualified name.

Since this came from the XSL that is generated by the mapping, the first step I took in identifying the problem (ok…the first step after scratching my head for 30 minutes or more) was to examine the XSL for the mapping. This can be created by running the Validate Map option from the Solution Explorer. My search for this particular XPath expression found it in an XSL segment that looked like the following.

<xsl:variable select="userCSharp:(string(*[local-name()='Times' and namespace-uri()='']/*[local-name()='WindowCloseTime' and namespace-uri()='']/text()))" name="var:v18"></xsl:variable> 
<xsl:if test="string($var:v15)='true'">
  <xsl:variable select="string($var:v18)" name="var:v19"></xsl:variable>
  <xsl:attribute name="FIRST_PU_MINUTE">
     <xsl:value-of select="$var:v19"></xsl:value-of>
  </xsl:attribute>
</xsl:if>

Now that I have a better look, it appears that the userCSharp namespace has attached itself to an XPath expression. Normally, it is attached to a function that is defined further down in the XSL file. No wonder it’s invalid.

The good thing is that I was able to see from the attribute name that the problem is in the mapping for the FIRST_PU_MINUTE attribute. When I looked, it turned out that the scripting functoid I was using hadn’t been defined. Instead of defining the external assembly function, I had left it as the default, commented-out code. Once I corrected that problem, my transform error went away.

Update: The XSL fragment didn't appear when I first posted it. My bad. Now it makes much more sense.

Updating Config Files in ASP.NET 2.0

One of the new features of ASP.NET 2.0 is the ability to not only more easily read from config files, but also to update them. If you’re looking for a pretty good description of what’s possible, check out the ASP.NET QuickStart Tutorial. Going over the basic process isn’t the point of this post. Instead, I want to talk about an unexpected, but yet expected, side effect.

The situation is quite common. Your web site utilizes Session variables. Over the course of your application, you update one of the config files that you had defined. All is fine and wonderful right up to the point where a Session variable was accessed. For some unknown reason, it was gone.

If this particular situation sounds familiar, there could very well be a good reason. It is similar to what happens if web.config gets updated on an active virtual directory. At least the symptoms are. What is actually happening is that the web application gets restarted. So it should be a surprise, should it.

Well, no. With one minor exception. In the particular case we were looking at, the config file was actually defined as an external file. So in web.config there was an entry similar to:

<customAppSettings configSource=”customAppSettings.config” />

So the actual updates were taking place outside of the web.config file. Since web.config isn’t updated, the application shouldn’t restart. Should it?

Well it turns out that the default behavior of the Config API is that changes to the external file cause an application restart just like web.config. And with the application restart comes the loss of Session information

There is a way to modify this default behavior. In the <section> tag where the config section is defined, the restartOnExternalChanges attribute gets set to false.

<section name="customAppSettings" type="ObjectSharp.Demo.CustomAppSettingsSection, ObjectSharp.Demo, Version=1.0.0.0, Culture=neutral, PublicKeyToken=a140608022e61b4b" restartOnExternalChanges="false" />

All of a sudden updating the config file doesn’t cause an application restart and all of the little session variables remain fat and happy.

 

Missing vcvarsall.bat in VS.NET 2005 Command Prompt

I took a few moments to dig into a nagging problem this weekend.  Seems that whenever I launched the Visual Studio 2005 Command Prompt, I received an error message indicating that a file called vcvarsall.bat couldn’t be found.  A thorough search of the Visual Studio 8 directory found nothing of that name, although there was the old standby of vsvars32.bat. I took a further search through ladybug and I see that it had already been reported and marked as fixed for the release version. If you find this annoying, there is a workaround available.  The details can be found at http://lab.msdn.microsoft.com/productfeedback/ViewWorkaround.aspx?FeedbackID=FDBK24480#2, but it works out to be either install C++ or change the shortcut for the command prompt to run vsvars32.bat.

BizTalk -> More on Constructing Messages and Configuration Information for an Orchestration

I recently was reading Scott's entry on Constructing Messages from Scratch with Embedded Resources. This is a good method, so take a look a look at it HERE.
Below is a variant on Constructing Messages in an Orchestration. This also can be used as a method to read any type of configuration information into an Orchestration.
This method needs:

1) A Sql Server table to store the templates for the XML Messages and/or Configuration Information.
2) A BizTalk XML Schema.
3) A BizTalk Map.


A Sql Server Table
Below is a create statement for the table with three columns:

if exists (select * from dbo.sysobjects where id = object_id(N'[GenericCodes]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [GenericCodes]
GO

CREATE TABLE [GenericCodes] (
 [Code] [varchar] (50)  NOT NULL ,
 [Value] [varchar] (7000)  NOT NULL ,
 [Description] [varchar] (500) NULL ,
 CONSTRAINT [PK_GenericCodes] PRIMARY KEY  CLUSTERED
 (
  [Code]
 )  ON [PRIMARY]
) ON [PRIMARY]
GO

Some sample data for the table is as below:

a) The Code column is the Primary Key of the table that describes the contents of the row.
b) The Value column contains the actual template XML message or Configuration value.
c) The Description column contains a description/purpose of the template xml message or configuration value,
 
In the example sample data above, the first two rows:

BaseXML_401ExtraInfoRequest
BaseXML_401FlatFileTemplate

contain template xml for messages that need to be constructed from scratch in an orchestration, sample as below:

<ns0:ShippingInfo ShipToAddress="Some Address" ShipToCity="Some City" ShipToCountry="Some Country" xmlns:ns0="http://ConstructingXMLMessages.ShippingInfo" />

The remainder of the rows, contains configuration information for a process:

Request401_FIFOMIBSend -> How many Processor orchestrations can be running at one time (1 or many)
Request401_MaxNumberofFilesToBatch -> How many incoming messages to batch together using a looping sequential convoy
Request401_TestMode -> To indicate if the system is in test mode or production mode.
Request401_TimeoutForFileBatching -> The max time to wait for the next message to batch.
Response401_DB2Process401Results_CommandTimeout -> Passed to a ADO.Net Helper component to update a DB2 database.
Response401_DB2Process401Results_Retries -> Used to configure a Component Retry Pattern.
Response401_DB2Process401Results_RetryInterval -> Used to configure a Component Retry Pattern.
TwoDigitYearSupport -> To help translate two digit years to four digit years in a map.
 

A BizTalk XML Schema

Create a BizTalk Schema called XmlTemplateAndConfigInfo.xsd as below:

Each node (could be an element or attribute) is of Data Type string. Notice that each nodes name matches a Code column name from a row in the Configuration table.
(Note: For this method to work, It is important to match the names. This will be explained below)
Each node is promoted to a distinguished field.
 

A BizTalk Map


Create a BizTalk Map called Map_PopulateXMLTemplateAndConfigInfo.btm as below:

Notice that the source and destination schemas use the same schema (XmlTemplateAndConfigInfo.xsd) that was described above.

A DataBase Lookup Functoid and Value Extractor functoid are used to populate each node on the Destination Schema.

The Configuration information for a DataBase lookup functoid looks like below:

The first Input parameter for a DataBase lookup functoid is the lookup value. In this case it should be set to a value of a row from the Primary Key Code column in the configuration table GenericCodes. Instead of Hard Coding this value in for example (Request401_TestMode), a link from the source schema is used. When the link has been created, go to the properties window as below and for the Source Links property set the value to -> Copy name. Therefore instead of the value of the source node being mapped over, the name of the node will be mapped over. This is why it is important for the name of each node to match a rows Code value in the GenericCodes table.  

The second Input parameter for a DataBase Lookup functoid is the DataBase Connection string A UDL file was used to hold the connection string for the database as described HERE

A Value Extractor Functoid is then used to extract the XML Template or Configuration Value and map it the Destination node.


A Sample Orchestration to use the XML templates and Configuration Values

Sample Orchestration as below:

The following Messages and Variables are created in the Orchestration View for the Orchestration:


Messages:
msgEmptyXmlTemplateAndConfigInfo MessageType is set to the XmlTemplateAndConfigInfo.xsd schema discussed above.
msgPopulatedXmlTemplateAndConfigInfo MessageType is set to the XmlTemplateAndConfigInfo.xsd schema discussed above.
msgCreateFromXMLTemplate is a test message that is constructed from an XML Template.

Variables:
Both varXMLDomEmptyXmlTemplateAndConfigInfo and varXMLDomCreateFromXMLTemplate are variables of type -> System.Xml.XmlDocument
They are used to help in message construction.


1) In the First construct shape in the Orchestration -> Construct EmptyXmlTemplateConfigInfo
A Message Assignment shape is used to construct message -> msgEmptyXmlTemplateAndConfigInfo
The code in the Message Assignment is as below:

// First need an empty message to construct the Empty Config Info XML Message
// Note: The XML is just the Root Node Name and the target namespace of the schema XmlTemplateAndConfigInfo.xsd
// This is hardcoded, but should never change.
// This constructed message is needed in the next orchestration shape which uses a map to construct the msgPopulatedXmlTemplateAndConfigInfo 
// message.
varXMLDomEmptyXmlTemplateAndConfigInfo.LoadXml(@"<ns0:XmlTemplateAndConfigInfo xmlns:ns0=""http://Demo.XmlTemplateAndConfigInfo""/>");
msgEmptyXmlTemplateAndConfigInfo = varXMLDomEmptyXmlTemplateAndConfigInfo;


2) In the Second construct shape -> Construct PopulatedXmlTemplateConfigInfo
A Transform shape is used to construct message -> msgPopulatedXmlTemplateAndConfigInfo.

The map -> Map_PopulateXMLTemplateAndConfigInfo.btm is used in the transform shape.
Message msgEmptyXmlTemplateAndConfigInfo is used as the Transform Source Message
Message msgPopulatedXmlTemplateAndConfigInfo is used as the Transform Destination Message 

The map -> Map_PopulateXMLTemplateAndConfigInfo.btm will then be invoked to construct the
msgPopulatedXmlTemplateAndConfigInfo. The nodes in this message will be populated from the values in Sql
table GenericCodes.

3) In the third construct shape, this is an example of constructing a message from an XML template,
that originates from the Sql table GenericCodes.
A Message assignment shape is used to construct message -> msgCreateFromXMLTemplate


// Load the Template XML from the msgPopulatedXmlTemplateAndConfigInfo.BaseXML_401ExtraInfoRequest node.
// Because Distinguished fields are used, then the intellisense works as below
varXMLDomCreateFromXMLTemplate.LoadXml(msgPopulatedXmlTemplateAndConfigInfo.BaseXML_401ExtraInfoRequest);
msgCreateFromXMLTemplate = varXMLDomCreateFromXMLTemplate;


4) Below is an example of reading the msgPopulatedXmlTemplateAndConfigInfo for Configuration information.
The Decide shape (Decide If In Test Mode, Test Mode Rule) uses the following expression to determine if in test mode:

// Intellisense can be used because the Nodes in the XmlTemplateAndConfigInfo.xsd were promoted as
// Distinguished Fields.
msgPopulatedXmlTemplateAndConfigInfo.Request401_TestMode == "1"


Finally

The good things about this method are:

1) No Code. You do not have to write any .Net Helper code to help read in the XML templates and Configuration Values into an Orchestration.
2) This is a simple method that I originally used at a client where .NET skills were scarce. Therefore why burden them with .NET code to maintain/test etc. when they do not have the resources to do so.
3) As soon as the value in the GenericCode tables changes it will be automatically visible.
4) A tool such as Query Analyzer or the Sql Enterprise Manager can be used to change the XML Template and Configuration values in the database.
5) This is a central repository of configuration data that any BizTalk Server in the Group will point to.


The not so good things about this method are:

1) For each node to be populated in the Map_PopulateXMLTemplateAndConfigInfo.btm map, a separate round trip to the database must be done. But we are talking milliseconds.

2) Each orchestration that needs the configuration values, would have to add the necessary Construct shapes to invoke the Population map. But, it would be easy to create one orchestration to centralize this procedure and then let the other orchestrations call into this "Configuration Orchestration" using a Call Orchestration Shape. The "Configuration Orchestration" would then return an msgPopulatedXmlTemplateAndConfigInfo message as an out parameter.

Just a Note

I always try to do a bit of research on my Blog topics so I can point the reader to other techniques and different views. So below are some links to other blogs on BizTalk and Configuration. Depending on your needs, take your pick:

1) The BizTalk Configuration Dilemma
2) Using the Rules Engine for orchestration configuration values *BizTalk Sample*...
3).NET configuration files with Orchestration Hosts 
4) How to store configuration data in the Enterprise Single Sign-On Database (SSO)
5)  Using the BTSNTSvc.exe.config
6) Maybe BizTalk 2006, has some new features for configuration? Just
installed my copy and have not had the time to look yet.

BizTalk 2004 -> Integrating with Sharepoint 2003 and when NOT to use the BizTalk WSS Adapter

The Sharepoint Send/Receive Adapter released last March of 2004 on GotDotNet can be downloaded HERE.
It provides functionality to add (Send) items from BizTalk into Sharepoint Libraries and to pull (Receive) items that have been added into a Sharepoint Library and to submit them into the BizTalk environment. The Sharepoint Adapter is a great example of how a custom BizTalk 2004 Adapter can be built to allow BizTalk to communicate with any type of application.

Last year in 2004, I was part of a team working on a Sharepoint 2003 Portal/BizTalk 2004 project. BizTalk 2004 was used to integrate an HR (Human Resources) system with Sharepoint WSS libraries and Sharepoint Portal User Profiles. In fact, BizTalk was used to aid in the replication of the data from the HR system (in real time) to the Sharepoint Portal Server (i.e. any changes to the HR Employee Profiles (updates/inserts/deletes) were to be replicated in a timely manner to the Sharepoint Portal Server(s) WSS Lists and/or User Profiles).

More Specifically:

1) Employee profiles from the HR system were inserted, updated and deleted in a Sharepoint Library Lists.
2) Employee Resumes (doc,pdf,rtf formats) from the HR system were inserted,updated and deleted in Sharepoint Document Library Lists.
3) Employee Photos (tiff,jpg formats) from the HR System were inserted updated and deleted in Sharepoint Picture Library Lists. 
4) Employee profiles from the HR system were used to create, update and delete Sharepoint User Profiles and to also generate MySites for each User Profile.
Note: User Profiles and MySites is a feature of Sharepoint Portal Server 2003 and not Windows Sharepoint Services (WSS). User Profiles can be used to store information about each portal user. Additionally each portal user can have their own MySite, where they can add/modify and delete their own specific content and have control of their own Web Site within the portal.


Some of the specific requirements/functionality/infrastructure of the integration project:

1) There were two Sharepoint 2003 Portal Servers:
a) English Portal Server (The English Portal Server was a load balanced web farm implemented with two physical servers hosting the English Portal.)
b) French Portal Server

2)
a) The English portal hosted the English User Profiles and English MySites.
On the English Portal there were two main WSS sub sites -> an English Sub Site and a French Sub Site.
Note: there was some discussion on moving the French Sub Site to the Physical French Portal server, but at the time of development the English and French Sub Sites were on the English Portal. Therefore the Employee profile, Resume and Picture libraries were duplicated on the English and French Sub sites of the English portal.
Note: We were trying to have one main list with both the English and French columns at the portal level and then create views of the this main list down on the English and French Sub Sites. This did not work. Therefore the duplicate libraries -> one on each sub site was used as below:

English Employee List:

French Employee List:


b) The French Portal Server hosted the French User Profiles and MySites


3) Any change made about an employee on the HR System (insert/update/delete), was to be replicated across to the Sharepoint Employee Profile List.

4) Any change to an Employee Picture or Employee Resume on the HR System (insert/update/delete), was to be replicated across to the Sharepoint Employee Picture Libraries and Resume Libraries. A Document or Picture Library can be thought of a regular Sharepoint list, except that a File is also associated with the list.

Additionally the HR system could indicate if only the actual Resume file or Picture file changed or only if text information about the the Employees changed (ie, their name or the city they worked in). This could be used to aid in performance -> i.e. if only the persons name changed, then do not bother updating the actual Resume Binary or Picture Binary and only update the Sharepoint List text columns (i.e. First Name, Last Name, City).

Below is the Detail View of the English Picture Library:

Below is the ThumbNail View of the English Picture Library:

5) When a File is added to a Document library, an actual URL (for example: http://spserver/sites/EnglishSite/EmployeePictures/Bob.jpg) 
is generated that can be retrieved from a Sharepoint API method call as below:

url = spListItem.File.ServerRelativeUrl;

This URL was sent back to the calling BizTalk Orchestration, so that it could ultimately update a Sharepoint Portal Server User Profile URL property. For example, one of the default properties for UserProfiles is the picture URL. When the MySite of the user is navigated to, the picture url is used to automatically display the picture on the users MySite as below:

Below is the edit page for Bob the Builder's User Profile:


Below is the MySite for Bob The Builder.
Note: That the User Profile for Bob the Builder is used to populate the MySite.


6) If the particular Sharepoint Library did not exist, then automatically create it.
For example if BizTalk submitted an Employee Profile(s) to Sharepoint for processing and the particular library list did not exist, then: 
a) Automatically create the list.
b) Populate the list from the submitted HR profile(s).

7) Any change made about an employee on the HR System (insert/update/delete), was to be replicated across to the Sharepoint User Profile Database.

8) A possible future enhancement was to have the information about Resumes also stored in Sharepoint Portal Areas. In this implementation Areas were used to represent the taxonomy of the company.

From the above functionality and requirements it became clear that it was going to be difficult or impossible to use the existing WSS adapter directly. Much finer grained control of the Sharepoint functionality was needed in order to implement the system.
Therefore there were two choices:

Choice 1) Extend the WSS BizTalk Adapter to handle the additional functionality.
Choice 2) Let Biztalk call custom web methods hosted by web services installed on Sharepoint Portal Servers. The custom web methods would receive as parameters information about employees in an XML format and additionally binary employee resumes and pictures. Custom code in the web methods would utilize the Sharepoint API methods in order to insert/update/delete items in the Sharepoint Lists and the Sharepoint User Profiles. The Web Methods could also return results, such as the URL's of the pictures or resumes to set into Sharepoint Portal User Profile properties.

In the end choice 2) was chosen to implement the solution.
The particular customer where I was doing the work had a number of strong .NET/Sharepoint/Web Services developers so it ultimately can down to the lead developers choice and he preferred choice number 2).

Choice 1), is also a viable option, but in retrospect, I am thankful that the custom web services method was chosen. Each Sharepoint List had it's own little quirks and the functionality to populate them varied from list to list. In the end it was much easier to have a separate set of web methods to control the population of each Sharepoint list and  the Portal User Profiles. To make the code more modular, a common set of Sharepoint helper methods was used, so that  code did not have to be duplicated, from web method to web method. If there were more Sharepoint Lists to be populated and the number of list to be populated grew over time, then using an adapter becomes more attractive. One of the things you lose by writing custom web methods, is that each List is tightly bound to a web method, so if the number of Sharepoint lists grow then the number of web methods also grows.

Below is a more detailed discussion of how the Custom Code was implemented and discusses in some detail using the Sharepoint API. Additionally at the end of this blog you can download the code that populates the Sharepoint Employee List.

A simplified flow of information from the HR System to Sharepoint went something like this:

1) From the HR System, information about the employees was delivered into Biztalk in an XML format.


2) A BizTalk Orchestration would then subscribe to the incoming Employee message.

A simplified Employee XML message is as below:

<ns0:EmployeeProfiles xmlns:ns0="http://BobTheBuilder">
  <EmployeeProfile>
    <EMPLOYEE_ID>1</EMPLOYEE_ID>
    <LAST_NAME>The Builder</LAST_NAME>
    <FIRST_NAME>Bob</FIRST_NAME>
    <PHONE>111-1111</PHONE>
    <CITY>Toronto</CITY>
    <FUNCTION>Owner</FUNCTION>
    <PROCESSLISTINDICATOR>-1</PROCESSLISTINDICATOR>
  </EmployeeProfile>
  <EmployeeProfile>
    <EMPLOYEE_ID>2</EMPLOYEE_ID>
    <LAST_NAME>The Contractor</LAST_NAME>
    <FIRST_NAME>Wendy</FIRST_NAME>
    <PHONE>222-2222</PHONE>
    <CITY>Mississauga</CITY>
    <FUNCTION>Contractor</FUNCTION>
    <PROCESSLISTINDICATOR>-1</PROCESSLISTINDICATOR>
  </EmployeeProfile>
</ns0:EmployeeProfiles>


The XML was converted to a string in the BizTalk Orchestration Expression shape. An example is as below.

varXMLDomEmployee = msgEmployee;
// Now the following can be used to extract the XML as a string
strEmployeeXML = varXMLDomEmployee.OuterXML.
More details about the above can be found HERE

If the Employee information was associated with a Resume, or a Picture, then in the Orchestration a .NET Helper Component using ADO.NET was called to retrieve the file from Sql Server. The code would return the Resume or Picture Binary as a string, from a Sql Server text column . An excerpt of the code is as below:

string result;
System.Byte[] resumeImageBytes;
resumeImageBytes =  (System.Byte[]) this.sqlCmdGetResumeImage.ExecuteScalar();
result = System.Convert.ToBase64String(resumeImageBytes);
return result; 

3) The string of Employee XML and optionally the Base64String of the Employee Resume or Picture was then sent to the Sharepoint Portal Servers from the BizTalk Orchestration via a Web Port to a Web Service installed on the Sharepoint Portal Servers. More detail about this can be found HERE

4) The particular web method on the portal Server would then accept as parameters the string of XML and optionally the binary Resume or Picture (as a string).

5) On the Web Server side, strongly typed datasets were used to hold configuration information for the Sharepoint Lists/Portal User Profiles and also included mapping information between the incoming Employee XML and the Sharepoint List Columns/ User Profile Properties. The dataset is as below.

XML installed on the Web Service side would then populate the dataset. A sample of the XML to populate the dataset is as below:

<?xml version="1.0" standalone="yes" ?>
<SharePointListData xmlns="http://tempuri.org/SharePointListData.xsd">
 <SharePointList>
    <SharePointListID>1</SharePointListID>
    <WebLocation>sites/EnglishSite</WebLocation>
    <SharePointListDescription>Employee Profiles List</SharePointListDescription>
    <SharePointListTitle>Employee Directory</SharePointListTitle>
    <SharePointListPKColumn>EmployeeID</SharePointListPKColumn>
    <XMLSourcePKColumn>EMPLOYEE_ID</XMLSourcePKColumn>
    <TemplateName>Custom List</TemplateName>
    <Language>en</Language>
 </SharePointList>
 <SharePointListColumns>
    <SharePointColumnName>EmployeeID</SharePointColumnName>
    <XMLSourceColumnToMap>EMPLOYEE_ID</XMLSourceColumnToMap>
    <SharePointListID>1</SharePointListID>
 </SharePointListColumns>
 <SharePointListColumns>
    <SharePointColumnName>Last Name</SharePointColumnName>
    <XMLSourceColumnToMap>LAST_NAME</XMLSourceColumnToMap>
    <SharePointListID>1</SharePointListID>
 </SharePointListColumns>
 <SharePointListColumns>
    <SharePointColumnName>First Name</SharePointColumnName>
    <XMLSourceColumnToMap>FIRST_NAME</XMLSourceColumnToMap>
    <SharePointListID>1</SharePointListID>
 </SharePointListColumns>
 <SharePointListColumns>
    <SharePointColumnName>Phone</SharePointColumnName>
    <XMLSourceColumnToMap>PHONE</XMLSourceColumnToMap>
    <SharePointListID>1</SharePointListID>
 </SharePointListColumns>
 <SharePointListColumns>
    <SharePointColumnName>City</SharePointColumnName>
    <XMLSourceColumnToMap>CITY</XMLSourceColumnToMap>
    <SharePointListID>1</SharePointListID>
 </SharePointListColumns>
 <SharePointListColumns>
    <SharePointColumnName>Function</SharePointColumnName>
    <XMLSourceColumnToMap>FUNCTION</XMLSourceColumnToMap>
    <SharePointListID>1</SharePointListID>
 </SharePointListColumns>
 <SharePointList>
    <SharePointListID>2</SharePointListID>
    <WebLocation>sites/FrenchSite</WebLocation>
    <SharePointListDescription>Les Profils d'employé Enumèrent</SharePointListDescription>
    <SharePointListTitle>Annuaire d'employé</SharePointListTitle>
    <SharePointListPKColumn>EmployeeID</SharePointListPKColumn>
    <XMLSourcePKColumn>EMPLOYEE_ID</XMLSourcePKColumn>
  <TemplateName>Custom List</TemplateName>
  <Language>fr</Language>
 </SharePointList>
 <SharePointListColumns>
    <SharePointColumnName>EmployeeID</SharePointColumnName>
    <XMLSourceColumnToMap>EMPLOYEE_ID</XMLSourceColumnToMap>
    <SharePointListID>2</SharePointListID>
 </SharePointListColumns>
 <SharePointListColumns>
    <SharePointColumnName>Nom de famille</SharePointColumnName>
    <XMLSourceColumnToMap>LAST_NAME</XMLSourceColumnToMap>
    <SharePointListID>2</SharePointListID>
 </SharePointListColumns>
 <SharePointListColumns>
    <SharePointColumnName>Prénom</SharePointColumnName>
    <XMLSourceColumnToMap>FIRST_NAME</XMLSourceColumnToMap>
    <SharePointListID>2</SharePointListID>
 </SharePointListColumns>
 <SharePointListColumns>
    <SharePointColumnName>Téléphone</SharePointColumnName>
    <XMLSourceColumnToMap>PHONE</XMLSourceColumnToMap>
    <SharePointListID>2</SharePointListID>
 </SharePointListColumns>
 <SharePointListColumns>
    <SharePointColumnName>Ville</SharePointColumnName>
    <XMLSourceColumnToMap>CITY</XMLSourceColumnToMap>
    <SharePointListID>2</SharePointListID>
 </SharePointListColumns>
 <SharePointListColumns>
    <SharePointColumnName>Fonction</SharePointColumnName>
    <XMLSourceColumnToMap>FUNCTION</XMLSourceColumnToMap>
    <SharePointListID>2</SharePointListID>
 </SharePointListColumns>
</SharePointListData> 

The above XML contains all the information necessary to create and/or get a handle to the necessary Sharepoint list(s) and contains the mappings between the incoming XML and the Sharepoint Columns. For example: The LAST_NAME node in the incoming XML would map to the Last Name column in the English Sharepoint list and would also map to the Nom de famille column in the French Sharepoint list. This configuration XML was then stored in the Web Services Cache. When the XML file changed, the File Dependency Cache would automatically load in the new XML file. In this way, the configuration information about the Sharepoint Lists and mappings could be changed on the fly.

Below is Sample Web Dependency Cache loading code. 

result.ReadXml(System.Web.HttpContext.Current.Server.MapPath(fileName));
// Save to the Web Cache. Put dependency on file, so when file is changed, old copy will get flushed from the Cache
System.Web.HttpContext.Current.Cache.Insert(cacheKey,
result,
new System.Web.Caching.CacheDependency(System.Web.HttpContext.Current.Server.MapPath(fileName)));


6) The Web Method would then call a helper class, that would actually do the processing
against the Sharepoint List(s)/User Profile Database with the Employee XML and the Configuration Dataset.

a) The Helper class would first determine if the required Sharepoint List(s) existed. The configuration dataset contained the necessary information to create the Sharepoint List(s) if they did not exist, otherwise just get a handle to the existing list. The code to create the list looks something like the below:

 try
 {
  // If the below WSS list exists, then get it.
  employeeList = this.siteCollection[0].AllWebs[(sharepointListRow.WebLocation)].Lists[sharepointListRow.SharePointListTitle];
 }
 catch (System.ArgumentException ex)
 {
  // Means the WSS list did not exist, therefore create it.
  employeeList = null;
  System.Diagnostics.Debug.WriteLine("The List does not exist. error is as follows -> " + ex.ToString()); 
 }
 if (employeeList == null)
 {
  // Create the WSS List
  string listTitle="" ,listDescription = "";
  listTitle = sharepointListRow.SharePointListTitle;
  if (!sharepointListRow.IsSharePointListDescriptionNull())
  {
   listDescription = sharepointListRow.SharePointListDescription;
  }
  else
  {
   listDescription = sharepointListRow.SharePointListTitle;
  }
  // This will add the new list to the List Collection of the Site.
  siteCollection[0].AllWebs[sharepointListRow.WebLocation].Lists.Add(listTitle,  // Title of New List
                  listDescription,       // Description of the List
                  Microsoft.SharePoint.SPListTemplateType.GenericList); // Type of list need to create

  // This will get a handle to the just added list
  employeeList = siteCollection[0].AllWebs[sharepointListRow.WebLocation].Lists[listTitle];
  // Now add the necessary columns to the newly created list.
  // Also add the columns to the Default View of the List.
  // Note: This code only handles text type columns.
  Microsoft.SharePoint.SPView defaultView = employeeList.DefaultView;
  Microsoft.SharePoint.SPViewFieldCollection spViewFieldsCollect = defaultView.ViewFields;
  foreach (SharePointListData.SharePointListColumnsRow spColumn in sharepointListRow.GetSharePointListColumnsRows())
  {
    employeeList.Fields.Add(spColumn.SharePointColumnName ,Microsoft.SharePoint.SPFieldType.Text,false);
    spViewFieldsCollect.Add(spColumn.SharePointColumnName);
  }
  defaultView.Update();
 }

b) The Helper class would then take the Configuration dataset and use it to map the Employee XML into the correct Sharepoint List columns. An indicator is also passed in with the employee XML. If it is set to -1, then the Employee in the Sharepoint list must be deleted, otherwise the Employee must be added to the list if it is not found or updated if it is found. A column in the Sharepoint list is used as a primary key for CAML Queries.

public void setDataInEmployeeSPList(string employeeXml,Microsoft.SharePoint.SPList employeeList, SharePointListData.SharePointListRow sharepointListRow )

{

 System.Xml.XmlElement employeeChildNode;
 System.Xml.XmlDocument employee = new System.Xml.XmlDocument();
 employee.LoadXml(employeeXml);
 System.Xml.XmlNode xmlNode;

 xmlNode = employee.DocumentElement["PROCESSLISTINDICATOR"];
 string deleteListIndicator = xmlNode.InnerText;

 xmlNode = employee.DocumentElement[sharepointListRow.XMLSourcePKColumn];
 string employeeID = xmlNode.InnerText;

 Microsoft.SharePoint.SPListItem spListItemEmployee;
 Microsoft.SharePoint.SPListItemCollection spListItemsEmployees;
 // Call method that does CAML query to try to find the Employee in the list.
 // If found, we will either update or delete it in the list.
 spListItemsEmployees = this.QueryListForItems(employeeList,
             sharepointListRow.SharePointListPKColumn,
             employeeID);
             
 if (deleteListIndicator == "-1")
 {
  if (spListItemsEmployees.Count == 1)
  {
   // Means must delete the Employee from the list
   spListItemEmployee = spListItemsEmployees[0];
   employeeList.Items.DeleteItemById(spListItemEmployee.ID);
   //employeeList.Update();
  }
  else
  {
   // Means could not find the item in the list to delete 
   System.Diagnostics.Debug.WriteLine("Could not find the Employee to delete in the library list.");    
     
  }
 }
 else // Either the Employee must be updated or deleted in the Employee list
 {
  spListItemEmployee = null;
  switch (spListItemsEmployees.Count)
  {
   case 1:  // Found an existing Employee -> Update
    System.Diagnostics.Debug.WriteLine("Found in library");
    spListItemEmployee = spListItemsEmployees[0];
    break;
   case 0: // Employee does not exist. Create a new employee item in the list
    System.Diagnostics.Debug.WriteLine("Did not find in library");
    spListItemEmployee = employeeList.Items.Add();
    break;
  }
  // Now set the information from the Employee XML to the Employee Sharepoint List Columns
  foreach (SharePointListData.SharePointListColumnsRow employeeColumnsRow in sharepointListRow.GetSharePointListColumnsRows())
  {
   employeeChildNode = employee.DocumentElement[employeeColumnsRow.XMLSourceColumnToMap];
   if (employeeChildNode != null)
   {
    spListItemEmployee[employeeColumnsRow.SharePointColumnName] = employeeChildNode.InnerText;
   }  
  }
  if (spListItemEmployee != null)
   spListItemEmployee.Update();
 }
}

/// <summary>
/// Will query a list in Sharepoint. Only one column in the list is queried. For example City = "Toronto"
/// </summary>
/// <param name="listToQuery">The SPList to query</param>
/// <param name="columnToQuery">The name of the column to query in the SPList</param>
/// <param name="queryValue">The value to query for in the column</param>
/// <returns>A collection of List items that were returned by the query</returns>
public Microsoft.SharePoint.SPListItemCollection QueryListForItems(Microsoft.SharePoint.SPList listToQuery,
 string columnToQuery,
 string queryValue)
{
 Microsoft.SharePoint.SPListItemCollection result;
 Microsoft.SharePoint.SPQuery spQuery = new Microsoft.SharePoint.SPQuery();
 spQuery.Query = "<Where><Eq><FieldRef Name='"+ columnToQuery +"'/><Value Type='Text'>" +  queryValue + "</Value></Eq></Where>";
 result = listToQuery.Items.List.GetItems(spQuery);
 return result;

}

c) In the end, there was not too much code to write, somewhere between 50 and 100 lines of code to get the job done.

Some Notes about the Sharepoint API's

1) The Sharepoint API's will give the maximum amount of control over a WSS or Portal Site.
2) In order to use the WSS API you must make a reference to Microsoft.SharePoint.dll. The Sharepoint dll contains methods to interact with Sharepoint lists, Document/Picture libraries and much more.
3) In order to use the Sharepoint Portal API you must make a reference to the Microsoft.SharePoint.Portal.dll. The Portal dll contains methods to interact with a portals User Profile database, areas and much more.
4) The Sharepoint API's are organized into a series of collections. For example:
a) A Portal contains a collection of WSS sites
b) A WSS site contains a collection of Sharepoint Lists
c) A Sharepoint List contains a collection of Sharepoint columns and Items that contains the data in the List
5) The API's are fairly intuitive, but like everything have their own set of nuances.
For example when adding an new item into a Generic list, it looks something like the below:

// Just add the new item to the Items collection of the list
spListItemEmployee = employeeList.Items.Add();

But when adding a new item into a Document Library or Picture list, it looks something like the below:

// Must add the new item to the lists Folder Files collection 
file = resumeList.SharePointFolder.Files.Add(xmlNode.InnerText /* Note.doc comes from PS+ ".doc"*/,Convert.FromBase64String(resumeByteStream)); 

BizTalk 2006

When BizTalk 2006 ships, it will include a built in out of the box WSS adapter. I am sure there will be improvements to the existing BizTalk 2004 WSS adapter that can be downloaded from GotDotNet. Read more about the new out of the box WSS adapter and other BizTalk 2006 Adapter additions and enhancements HERE

HERE download the sample code that populates a Sharepoint Employee List. Read the Readme.txt before installing and running.
Note: You must have Sharepoint WSS installed and Visual Studio 2003 on a Windows Server 2003 machine in order for the sample to work.

 

 

 

 

Building Outlook Add-ins with .NET Managed Code

Why would you want to do that you ask? Well for developers, probably the main reason would be to rewrite or wrap up some ugly lotus notes application (or all of lotus notes for that matter) and expose it in outlook. But seriously, there is a good chance your end user might spend more time in Outlook then any other application they have installed. And then of course, outlook has some context that might be helpful. Maybe, just maybe, when a user wants to talk to a customer, they open up their contact details in outlook first. Nothing worst than having to then alt-tab to another application and lookup the same contact in another system, perhaps to lookup their order history, etc. Or maybe you'd just like to rip through the information in outlook (contacts, appointments, emails) and integrate that data with some other application.

The outlook support for VSTO 2005 was a well kept secret until yesterday at TechEd when this was demonstrated at the keynote. How long are you going to have to wait before you can get your hands on this? How fast is your internet connection?