New SHAREPoint Kompendium Magazin Article About ECS Web Service Designer For SharePoint

In the last issue of the SHAREPoint Kompendium Magazin (Band 6, 2014) I published an article in German about the brand new ECS Web Service Designer for SharePoint 2010/2013 from Theobald Software. The application is a desktop windows application allowing you to visually design SAP driven Web Services for SharePoint.

Stock Quote Add-In for Excel 2013

I have developed a new version of the popular ‚MSN-like Stock Quotes Add-In For Excel 2007‘ for Excel 2013. The user defined function PSQ is exactly the same as in previous version, but it will retrieve stock data asynchronously. This makes the Excel user interface more responsive to user actions, especially if you have to query for a lot of stock symbols.

You may download source and binary files from the Codeplex site:

http://pmstockquote.codeplex.com

PMStockQuote-Screenshot-About PMStockQuote-Screenshot-Update

NOTE: I do not have the time to provide support for this tool. You should read carefully the README.txt with installtion instructions.

Nice HTML5 demos with SAP stock quantity and customer data using ERPConnect

A couple of weeks ago I have implemeted two HTML5 demos in C# using ASP.NET MVC, Web API and jQuery. The demos are displaying SAP customer and stock quantity data. The SAP/.NET connectivity is handled by the ERPConnect library from Theobald Software. The primary goal was to show how easily you can integrate good looking visual effects with jQuery.

ImageImage

The customer demo (CIS) adds hidden table rows with a horizontal silder showing an order quantity diagram. The stock quantity demo (SIS) displays mutliple table rows once the user clicks on the top row. In addition it will add an extra row showing a chart.

YouTube video of the stock quantity demo
YouTube video of the customer information demo

jQuery / CSS libraries or tools used in demos:
– Bootstrap (twitter)
– jQuery.Shadow
– jqBarGraph
– pagePeel
– progressbutton.jQuery
– liteAccordion

The samples are part of the ERPConnect library. You can download a trail version from Theobald Software. ERPConnect is a lean .NET assembly that lets you develop robust SAP interfaces without a great degree of effort and most of all, without an elaborate infrastructure or any additional middleware.

ERPConnect

Fiddler and the 401 (Unauthorized) error with ASP.NET Web API using Integrated Windows Authentication (WIA)

A few days ago I had a real strange problem while using HttpClient in combination with ASP.NET Web API and integrated windows authentication (IIS Express). To keep it simple, I always got a 401 (Unauthorized) error returned from the localhost website, even though I was setting up the HttpClient correctly (see screenshots) and the HttpClient was sending the authorization header (negotiate).

In my code is was using the URI http://localhost.fiddler:51275/api/values to access the REST API. The .fiddler part in the URI allows Fiddler to monitor the HTTP traffic between the website and the console application. For more details about this setting take a look at Fiddler’s homepage: http://www.fiddler2.com/fiddler/help/hookup.asp.

After researching a while I found out, that exactly the .fiddler URI addition is part of the problem for the 401 error I always received. Microsoft changed the logic of the NTLM authentication for some of the security classes in .NET version 3.5 SP1 (see link below) in order to avoid relefection attacks. A reflection attack is a method of attacking a challenge-response authentication system. The HttpClient uses those changed classes internally to connect to the website.

So, the solution is quite simple. We just need to add the registry key BackConnectionHostNames (important: Multi-Value String) to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\ Control\Lsa\MSV1_0 section in the registry with the value localhost.fiddler. A full explanation about the security concepts in WIA you will find at Microsoft’s site: Changes to NTLM authentication for HTTPWebRequest in Version 3.5 SP1

You can easily reproduce this error by creating a standard MVC4 web application with a Web API controller using windows authentication (see screenshot for settings). Then you add a console application to the project from which access the REST API (ValuesController).

401-Fiddler
401-Registry
401-VisualStudio2012-WIA
401-HttpClientSample

How To Integrate SAP Business Data Into SharePoint 2010 Using BCS Services And BCS Connector From Theobald Software

The Business Connectivity Services (BCS) of SharePoint 2010 provide a great way to fully integrate external data into the SharePoint. In most cases developers are integrating SQL database tables into the BCS services. But how do we connect to a SAP system? How do we integrate real world SAP business data like tables and function modules or BAPIs into SharePoint?

The answer is just a few clicks away. Theobald Software just released the ERPConnect Services (ECS) for SharePoint 2010 product suite and the product includes a great tool named BCS Connector. The BCS Connector allows developer to create SAP-driven BDC models in minutes with just a couple of clicks.

The BCS Connector application can be installed on client machines and will connect remotely to the SharePoint BCS service application. In this post I will give you an overview of the tool by creating a BDC model with an entity called Customer. We will also create two operations for the entity, a Finder and SpecificFinder method. Both methods are using the SAP built-in function module SD_RFC_CUSTOMER_GET. This is a very simple SAP function returning a list of customers.

To create a new BDC model, first you must enter the SAP and the SharePoint connection data after starting the BCS Connector application (see screenshot for SAP connection below).

Once you have entered the SAP connection data press the OK button and start adding your new BDC entity. To add an entity that is connected to a SAP function module SD_RFC_CUSTOMER_GET press the New button on the lower left side. A new wizard dialog will pop up. Select Function and press the Next button.

Then search for the function module and press the Next button to select the structure of our new BDC entity.

The last wizard page shows us a list with all possible entity structures available for this function module. Select the table CUSTOMER_T, including our customer data, and press the Finish button.

Now we have created our new entity Customer, but we still need to rename the entity from CUSTOMER_T to Customer. Each entity in BCS services must define at least two operations or methods, a so called Finder method to return a list of entities and a SpecificFinder method to return a specific entity within the list.

You also need to define the identifier fields within the entity by clicking the checkbox for the field KUNNR (Customer ID). You may also rename the field or all fields. Next, we create the Finder method by clicking the New button.

The Finder option is already selected. Just press the Finish button and the BCS Connector is automatically creating everything else and will open afterwards the Edit Operation dialog.

This dialog allows you to define the return parameter, input parameters and filters for the entity operation. Actually to execute the SAP function module SD_RFC_CUSTOMER_GET we need to define a value for the input parameters KUNNR or NAME1. For demonstration purpose I just define a name pattern for field NAME1. This query returns all customers that starts with T. What you can define as input parameter depends on the function itself. Clicking on the Preview button displays a list of all filtered customers.

In the same way we are creating the SpecificFinder method:

So, finally we have created a new entity with two entity operations and now we are able to save it on the SharePoint server. Just press the Save Model button. This will result in a new BDC model created on the server:

You can find the BDC models within the Central Administration of SharePoint 2010.

So far we just created a model, but we also want to display the customer data within an external list. We can create an external list using the SharePoint Designer or the BCS Connector. I will show you the second option. Switch to the External Lists tab of the ribbon bar and click on the New External List button.

The New External List dialog has pre-selected all values. Click on the Create button and you are done. You may also change the name of the external list. The final external list looks as follows:

That was really easy and you can even export the BDC Model to Visual Studio 2010 and do additional customizing.

Further information about the product ERPConnect Services and BCS Connector can be found here:

http://www.theobald-software.com/en/products/erpconnectservices.htm

FluentSP – The Fluent SharePoint API

Download FluentSP 1.0 from Codeplex.com

More information on my new homepage at http://www.parago.net

Once you are doing a lot of SharePoint programming you know you often have to write lengthy pieces of code to implement simple tasks like querying SharePoint lists. Nowadays you can read a lot of fluent APIs or fluent interface. For instance, jQuery, a JavaScript library that had successfully introduced a fluent API to handle the hierarchical structure of the HTML documents.

Today, I want to introduce a small library I have developed, FluentSP, a modern fluent interface around the classic SharePoint 2010 API. By using FluentSP instead of the classic SharePoint API, you will be able to chain methods and act on sets of items of the underlying SharePoint objects.

What is a fluent API?
Checkout this CodeProject article A Look at Fluent APIs and the Wikipedia article Fluent interface.

To start into the fluent API you call the Use() method on SPSite, SPWeb, SPWebCollection or SPListCollection. The Use() method is implemented as an extension method that will return the entry facade object (see facade table below). Another entry point to the fluent API is the static class FluentSP with its static methods CurrentSite, CurrentWeb, CurrentLists or RootWebLists.

SPContext.Current.Site.Use()... // => Returns the SPSiteFacade as entry point

// OR:
FluentSP.CurrentSite()...       // => Returns the SPSiteFacade as entry point 

Using the entry facade instance you can start chaining the available facade methods as follows:

FluentSP.CurrentSite().Web("Home").List("Tasks").Items().ForEach(i => // Do something with the item i of type SPListItem...);

// OR:
FluentSP.CurrentSite()
  .Web("Home")
    .List("Tasks")
      .Items()
      .ForEach(i => // Do something with...);

Each facade object is actually wrapping an underlying data item, for instance the SPSiteFacade class is the fluent wrapper of the SPSite class. Depending on what kind of facade methods you are calling the method is returning either the current facade instance (e.g., ForEach() or Where()) or the method is returning a new child facade object (e.g. Items()). During the process of chaining methods in such a way you will build up a tree or hierarchy of facade instances. In order to step back to the parent or previous facade instance you need to call the End() method:

site.Use()
       .RootWeb()
         .Site()
       .End()		// Returns SPWebFacade  as parent facade
         .Site()
       .End()		// Returns SPWebFacade  as parent facade
     .End();		// Returns SPSiteFacade as parent facade

FluentSP is currently missing a number of possible useful methods, but you can easily extend the FluentSP API with custom facade classes and extension methods, see below and source code for implementation examples.

Samples

SPSite site = SPContext.Current.Site;

// ----------------------------

// Outputs titles of all lists of the root web where the list title starts with T
site.Use().RootWeb().Lists().Where(l => l.Title.StartsWith("T")).ForEach(l => Console.WriteLine(l.Title));

// Outputs titles of all lists of the root web where the list title ends with a ts (using RegEx)
site.Use().RootWeb().Lists("ts$").ForEach(l => Console.WriteLine(l.Title)).Count(out c);

// Outputs titles of all lists of the root web in ascending order where the starts with T
site.Use().RootWeb().Lists().Where(l => l.Title.StartsWith("T")).OrderBy(l => l.Title).ForEach(l => Console.WriteLine(l.Title));

// Outputs titles of all lists of the root web in descending order where the starts with T
site.Use()
    .RootWeb()
      .Lists()
      .Where(l => l.Title.StartsWith("T"))
      .OrderByDescending(l => l.Title)
      .ForEach(l => Console.WriteLine(l.Title));

// ----------------------------

// Delete all items in the Members list, then add 7 new members and then select and output 
// the titles of a few of the newly created items
site.Use()
    .RootWeb()
      .List("Members")
      .Do(w => Console.WriteLine("Deleting all members..."))
       .Items()
       .Delete()
      .End()
      .Do(w => Console.WriteLine("Adding all members..."))
      .AddItems(7, (i, c) => i["Title"] = "Member " + c)
       .Items()
       .Skip(2)
       .TakeUntil(i => ((string)i["Title"]).EndsWith("6"))
       .ForEach(i => Console.WriteLine(i["Title"]));

// ----------------------------

// Search for lists that are created by specific a user and depending on the results
// displays different messages by calling the IfAny or IfEmpty methods
site.Use()
    .RootWeb()
      .Lists()
      .ThatAreCreatedBy("Unknown User")
      .IfAny(f => f.ForEach(l => Console.WriteLine(l.Title)))
      .IfAny(l => l.Title.StartsWith("M"), f => Console.WriteLine("Lists found that starts with M*"))
      .IfEmpty(f => Console.WriteLine("No lists found for user"))
    .End()
    .Do(w => Console.WriteLine("---"))
      .Lists()
      .ThatAreCreatedBy("System Account")
      .IfAny(f => f.ForEach(l => Console.WriteLine(l.Title)));

// ----------------------------

var items = new List<SPListItem>();

// Query with Skip and TakeUnitl methods
site.Use().RootWeb().List("Members").Items().Skip(2).TakeUntil(i => i.Title.EndsWith("5")).ForEach(i => { items.Add(i); Console.WriteLine(i.Title); });

// Query with Skip and TakeWhile methods
site.Use()
    .RootWeb()
      .List("Members")
       .Items()
       .Skip(2)
       .TakeWhile(i => i.Title.StartsWith("Member"))
       .ForEach(i => { items.Add(i); Console.WriteLine(i.Title); })
      .End()
       .Items()
       .Where(i => i.Title == "XYZ")
       .ForEach(i => { items.Add(i); Console.WriteLine(i.Title); });

// ----------------------------

// Adds new items using the Do method with the passed facade object
site.Use()
    .RootWeb()
    .AllowUnsafeUpdates()
      .List("Members")
      .Do((f, l) => {
        for(int c = 1; c <= 5; c++)
          f.AddItem(i => i["Title"] = "Standard Member #" + c);
      })
      .AddItem(i => i["Title"] = "Premium Member")
       .Items()
        .OrderBy(i => i.Title)
        .ForEach(i => Console.WriteLine(i["Title"]));

Extensibility Samples

// This sample is using the ThatAreCreatedBy extension method defined in Extensions.cs to show how to extend the fluent API
site.Use()
        .RootWeb()
          .Lists()
          .ThatAreCreatedBy("System Account", "jbaurle")
          .Count(c => Console.WriteLine("Lists found: {0}", c))
          .ForEach(l => Console.WriteLine(l.Title));

// This sample uses the new SPWebApplicationFacade extenion defined in SPwebApplicationFacade.cs to show how to extend the fluent API
site.WebApplication.Use()
              .Sites()
              .ForEach(i => Console.WriteLine(i.Url));

// This sample uses an alternative implementation for SPSiteFacade defined in SPSiteFacadeAlternate.cs to show how to extend the fluent API
site.WebApplication.Use().WithFirstSite().DoSomething();
site.Use<SPSiteFacadeAlternate<BaseFacade>>().DoSomething();

The custom method ThatAreCreatedBy which is used in the first query of the extensibility samples is implemented as follows:

static class Extensions
{
  public static SPListCollectionFacade<TParentFacade> ThatAreCreatedBy<TParentFacade>(this SPListCollectionFacade<TParentFacade> facade, params string[] names)
    where TParentFacade : BaseFacade
  {
    // NOTE: This sample uses the GetCollection method of the given facade instance to retrieve the current 
    // collection and adds the its query (see LINQ Deferred Execution). The Set method updates the 
    // underlying collection. The GetCurrentFacade method will then return the current facade to allow 
    // method chaining.

    if(names.Length > 0)
      facade.Set(facade.GetCollection().Where(i => names.Contains(i.Author.Name)));

    return facade.GetCurrentFacade();
  }
}

For more samples and details check out the source code you can download from Codeplex.

Built-In Facades and Methods

See Codeplex