CodeFluent Documentation Send comments on this topic.
Microsoft Office Lists
See Also
CodeFluent Entities > Tutorials > Using The Core Edition > Microsoft Office Lists

Glossary Item Box

Creating the foundations of your application

Before taking care of the user interface, we must set up a data tier. The data tier is the place where the application will persist all of its data and is therefore commonly referred to as the persistence layer. On top of that layer, CodeFluent allows us to generate a middle tier which will contain a set of objects enabling developers to:

  1. manipulate the data objects stored in the persistence layer,

  2. define the business logic of our application.

Therefore, this middle tier is known as the Business Object Model (BOM). In the end, whatever the user interface or the type of architecture is, the persistence layer (containing the data) and the business layer (containing the business logic) are always the fundamental pillars of an application. Since those two layers are common to all types of applications, their creation won't be detailed in this article; instead this article extends the The Persistence and the Business Layers one by creating Microsoft Office Synchronizable Lists, consumable in Excel and Access.

Office Synchronization Lists

Since Office 2003, Microsoft introduced SharePoint Lists which allows an Office client such as Excel to manipulate lists of data provided by SharePoint. This allows users to keep the information in their Excel/Access tables synchronized with the information that appears on the SharePoint site. This feature is available for Excel 2003+ and Access 2007+.

A slight note though: starting from Excel 2007, Microsoft inhibited the feature making it read-only, as they made the choice to privilege data updates through Access 2007 or VBA solutions (see http://support.microsoft.com/kb/930006 for more information). However, the feature is still available in Excel 2007 and upper but required the user to run in Excel 2003 compatibility mode. This being said, we created a small component named Excel 2007 Table Synchronizer which once installed, opens automatically synchronizable lists in the compatibility mode so that those Excel lists are readable/writable by default. Please check http://office.microsoft.com/en-us/excel/HA010843681033.aspx?pid=CH062528481033 or http://office.microsoft.com for more information regarding SharePoint lists and this synchronization feature.

Creating the Web Service

CodeFluent thanks to its Microsoft Office Lists Producer allows developers to generate web services that emulate a SharePoint server so that Office clients such as Excel and Access can consume data from your own business application. First of all we're going to have to modify the Sample.Producers.xml model part created in the The Persistence and the Business Layers tutorial, in order to add this producer.

Open the Sample.Producers.xml file and override its content with the following:

Sample.Producers.xml Copy Code
<cf:project xmlns:cf="http://www.softfluent.com/codefluent/2005/1"
           defaultNamespace="Sample"
           defaultConnectionString="server=(local);database={0};Integrated Security=true">
 <cf:producer typeName="CodeFluent.Producers.SqlServer.SqlServerProducer, CodeFluent.Producers.SqlServer">
   <configuration targetDirectory="../{0}.Persistence"/>
 </cf:producer>
 <cf:producer name="BOM Producer" typeName="CodeFluent.Producers.CodeDom.CodeDomProducer, CodeFluent.Producers.CodeDom">
   <configuration
     compileWithVisualStudio="true"
     outputName="bin\Debug\{0}.dll"
     targetDirectory="../{0}"
     produceWebBitsServer="false">
   </configuration>
 </cf:producer>

 <cf:producer name="MS Office Lists Producer"
              typeName="CodeFluent.Producers.Office.ListProducer, CodeFluent.Producers.Office">
   <configuration
     compile="false"
     targetDirectory="..\{0}.Office.Web"
     targetBaseNamespace="{0}.Office.Web"
    
     deploy="true"
     createVirtualRoot="true"
     webServiceVirtualRootName="{0}.Office"
     sourceDirectories="%CF_TEMPLATES_PATH%\OfficeWebService"
     webSiteSourceDirectories="%CF_TEMPLATES_PATH%\OfficeWebSite"
     webServicePhysicalRootPath="..\{0}.Office.Web\Site" />
 </cf:producer>
</cf:project>

Apart from adding a new producer, you can notice that we removed the produceWebHandler="false" in the Business Object Model Producer as we're going to need it since the BOM will be used in a web context now.

Note: Here are a few comments regarding the configuration attributes specified to the Microsoft Office Lists Producer.

We set compile to false since we'll compile with Visual Studio.

The targetDirectory points to our project.

By default the generated web service source file (Lists.cs) is contained in the [DefaultNamespace].Office.Web.Services namespace. If no such folders exist, CodeFluent will create them. In our case we have a base namespace named [DefaultNamespace].Office.Web. By specifying the targetBaseNamespace, we're indicating CodeFluent to only create missing folders starting from this base and not the default one (which is the DefaultNamespace).

Setting deploy to true indicates CodeFluent to generate and deploy web service and web site files (ASPX, ASCX, ASMX, etc.) to the webServicePhysicalRootPath.

Setting createVirtualRoot indicates CodeFluent to create the web site in IIS. Its default value is true, so after the first generation it's a time saver to disable it.

The webServiceVirtualRootName indicates the virtual root name in IIS.

The sourceDirectories attribute points to a template which CodeFluent will use to generate the web service files.

The webSiteSourceDirectories attribute points to a template which CodeFluent will use to generate the web site files.

The webServicePhysicalRootPath indicates where the web files should be generated and the physical path the IIS application should point to.

Before generating create a new class library project named Sample.Office.Web which will contain the web service exposing the synchronizable lists, as well as a web site to easily retrieve those lists.

In the Sample.Design directory, launch the Generated.bat script to generate the model.

CodeFluent generated the Office list content in the Sample.Office.Web\Services for the service logic and Sample.Office.Web\Site for the IIS application content. We're now going to add the newly generated files to Visual Studio.

Including the generated source files in Visual Studio

First we're going to start with the Business Object Model (BOM). Since we removed the produceWebHandler inhibition, a file named HttpHandler.cs was generated in the Sample.Web directory. Add it to the project:

You now have to add some extra references to the project:

Once the references added, compile the project.

Now that our BOM is up-to-date, we're going to take care of the Sample.Office.Web project.

We now have to add the needed references to compile the project:

Compile the project.

We're now going to add a post-build event to automatically deploy the built Sample.Office.Web assembly to the IIS application bin directory (Sample.Office.Web\Site\bin). To do so, right click on the Sample.Office.Web project, select Properties. In the Properties window, select the Build Events tab, and in the post-build text area, copy/paste the following command line:

Post-build event command line Copy Code
xcopy * "$(ProjectDir)Site\bin" /Y /R /E

By adding this post-build event the built assembly will automatically be deployed to the IIS application bin directory.

Build the project again to deploy the assembly: our folder to which IIS will point to is ready!

Note: If running IIS 7.0 the application is created by default in the DefaultAppPool to which the App.Config template isn't compliant. You're going to have to change the application pool to the ClassicNetAppPool or to change the web.config before being able to connect to the IIS application without an error.

Lists are now generated, check the following tutorials to deploy and use them:

Configuring MS Office Lists For Excel

Configuring MS Office Lists For Access

 

See Also