CodeFluent Entities Documentation
Developing Microsoft Office Synchronizable Lists
See Also Send comments on this topic.
CodeFluent Entities > Tutorials > Using The Core Edition > Integrating Microsoft Office > Developing Microsoft Office Synchronizable 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 SharePoint List Synchronizer For Excel 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}.Web"
     
      deploy="true"
      createVirtualRoot="true"
      webServiceVirtualRootName="{0}.Office"
      sourceDirectories="%CF_TEMPLATES_PATH%\OfficeWebService"
      webSiteSourceDirectories="%CF_TEMPLATES_PATH%\OfficeWebSite"
      webServicePhysicalRootPath="..\{0}.Web" />
  </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.

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, in Visual Studio, add a new ASP.NET Web Application named Sample.Web which will contain the web services 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 actual classes building the Office lists in the Sample.Web\Office\Web\Services folder, and a web site was generated at the web application's root Sample.Web. 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.Web project.

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

Compile the project.

Right-click on the project and update it's web properties so that it is hosted in IIS and not the ASP.NET Development Server (aka Cassini). Set the root url to match your application in IIS (it should match the value of the webServiceVirtualRootName producer attribute which in our case was Sample.Office).

Launch the project and your browser should start in your web application. Now that your development environment is set-up and that the list were generated we need to configure our web site in IIS so it emulates a SharePoint server.

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 template before being able to connect to the IIS application without an error.

Check-out the following tutorials to configure your web site in IIS:

Configuring Lists For Excel

Configuring Lists For Access

 

See Also