How to integrate SAP into .NET applications like SharePoint

Hauptmenü

Archiv der Kategorie: ASP.NET (MVC)

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.

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.

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.

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).

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).

Theobald Software released a new product suite called ERPConnect Services for SharePoint 2010. The product contains a new kind of query language named XtractQL to query SAP business data in a SQL-like manner. XtractQL or XQL is a combination of SQL and ABAP language syntax statements to simplify querying SAP systems.

XtractQL allows querying SAP objects like tables, BW-Cubes and SAP Queries. In addition XtractQL executes function modules (BAPIs) and returns metadata for objects. It can even execute MDX statements. Each XQL query is returning a data table object as result set. In case of the execution function modules the caller must define the returning table explicitly. XtractQL is very useful in situations where you want or need to handle dynamic statements.

ERPConnect Services for SharePoint 2010 (ECS) provides an additional tool, the XtractQL Explorer (see screenshot below), to learn more about the query language and to test XQL queries. You can use this tool independent of SharePoint 2010, but you need access to a SAP system.

The screenshot shows different samples of XQL queries. The following list shows a couple of other samples of XQL queries you can use in your SharePoint applications:

SELECT TOP 5 * FROM T001W WHERE FABKL = ‚US‘

This query selects the top 5 records of the SAP table T001W where the field FABKL equals the value US.

This query selects all records and fields of the SAP table MARA using a custom SAP function module to retrieve the data called Z_XTRACT_IS_TABLE.

SELECT MAKTX AS [ShortDesc], MANDT, SPRAS AS Language FROM MAKT

This query selects all records of the SAP table MAKT. The result set will contains three fields named ShortDesc, MANDT and Language.

SELECT TOP 30 LIPS-LFIMG, LIPS-MATNR, TEXT_LIKP_KUNNR AS CustomerID

FROM QUERY ‚S|ZTHEO02|ZLIKP‘

WHERE SP$00002 BT ‚0080011000‘AND ‚0080011999‘

This statement executes the SAP Query „S|ZTHEO02|ZLIKP“ (name includes the workspace, user group and the query name). As you can see XtractQL extends the SQL syntax with ABAP or SAP specific syntax elements. This way you can define fields using the LIPS-MATNR format and SAP-like where clauses like „SP$00002 BT ‚0080011000‘AND ‚0080011999‘“.

SELECT * FROM BWQUERY ‚0D_DECU/VARDEMO01‘

WHERE MAT03 EQ ‚M03‘, SALESORG BT ‚1000‘ AND ‚3000

This query executes a query against a specific SAP BW cube using a where clause.

DESCRIBE FUNCTION ‚SD_RFC_CUSTOMER_GET‘ GET EXPORTS

This query returns metadata about the export parameters of the SAP function module.

DESCRIBE QUERY ‚G|ZTHEO1|ZTHEOSQUERY‘ GET FIELDS

This query returns metadata about the defined SQP Query.

EXECUTE FUNCTION ‚SD_RFC_CUSTOMER_GET‘

EXPORTS KUNNR=’0000003340′

TABLES CUSTOMER_T INTO @RETVAL;

This query executes the SAP function module SD_RFC_CUSTOMER_GET and returns as result the table CUSTOMER_T (defined as @RETVAL).

ERPConnect Services runtime provides three kind of libraries to integrate with your application: Silverlight, Desktop and SharePoint client library. See product documentation for more information.

Here an example of how to use the libraries in code, e.g. within a web part project:

using ERPConnectServices;

…

ERPConnectServiceClient client = new ERPConnectServiceClient();

DataTable dt = client.ExecuteXQL(„SELECT TOP 50 * FROM MAKT“);

The ExecuteXQL method call of the ERPConnect Service Application in SharePoint will execute the XtractQL command against the defined SAP system.

There is also another way to use XtractQL. ERPConnect Services for SharePoint 2010 includes a Visual Studio 2010 Designer plugin. Similar to the LINQ to SQL designer, the ECS designer creates automatically code for you, see screenshot below:

You will find more information about the XtractQL syntax and ERPConnect Services for SharePoint 2010 product itself at the website of Theobald Software. There is also an evaluation version available.

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:

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.

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:

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:

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.

// 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:

During the process of creating a complex SharePoint application you often need to send mails and create text files based on SharePoint data elements like SPListItem or SPWeb. Mail templates for instance mostly contain specific list item data. It would be helpful sometimes if the text generation itself is template-driven.

This article shows how to implement a generic template manager based on the free DotLiquid templating system with SharePoint specific extensions. This allows you for example to iterate through all SharePoint lists available within a SiteCollection and render only details for lists which contain Task in their title:

The screenshot below shows the result of the rendered template sample:

Of course the technique implemented in this article can also be used in conjunction with other technologies or applications, it’s not only SharePoint related.

DotLiquid Template Engine

The DotLiquid template engine is a C# port of the Ruby’s Liquid templating system and is available for .NET 3.5 and above. DotLiquid is open source and can be downloaded at dotliquidmarkup.org. The software is also available as NuGet package for Visual Studio.

The templating system includes features like variable, text replacement, conditional evaluation and loop statements that are similar to common programming languages. The language elements consists of tags and filter constructs.

The engine can also be easily extended by implementing and adding custom filters and/or tags. This article actually shows how to extend the DotLiquid and implement SharePoint specific parts.

Output markup is surrounded by curly brackets {{…}} and tag markup by {%…%}. Output markup can take filter definitions like upcase. Filters are simple static methods, where the first parameter is always the output of the left side of the filter and the return value of the filter will be the new left value when the next filter is run. When there are no more filters, the template will receive the resulting string.

There are a big number of standard filters available to use, but later on we will implement a custom filter method for SharePoint. The result of the above rendered template looks like:

The User and Task classes inherit from the Drop class. This is an important class in DotLiquid. The next sections explains the class in more detail. It is out of scope of this article to discuss all the features for DotLiquid in detail. For more information please see the homepage of DotLiquid (dotliquidmarkup.org) or the website of the original creator of the Liquid template language at liquidmarkup.org. You will find there a lot of manuals and sample code.

Template Manager

The TemplateManager class is a wrapper over the DotLiquid template engine and provides SharePoint support. The class allows to cache parsed templates, to register tags and filters and render them using a top-level custom Drop class named SharePointDrop:

The Render method is using the SharePointDrop class to support objects like SPListItem or SPListCollection. The Drop class as key concept of DotLiquid must be explained in detail. The DotLiquid template engine is focusing on making templates safe. A Drop is a class which allows you to export DOM like objects. DotLiquid, by default, only accepts a limited number of types as parameters to the Render method. These data types include the .NET primitive types (integer, float, string, etc.), and some collection types including IDictionary, IList and IIndexable (a custom DotLiquid interface).

If DotLiquid would support arbitrary types, then it could result in properties or methods being unintentionally exposed to template authors. To prevent this, DotLiquid templating system uses Drop classes that use an opt-in approach to exposing object data.

The SharePointDrop class main objective is to solve the problem of casting unsupported data types like SPListItem or SPListItemCollection and other SharePoint related types. Therefore the class is overriding the BeforeMethod method of the Drop class to analyse the requested variable value. If the variable is available in the value context the method will try to cast the data type to a known Drop type by calling the MayConvertToDrop method of the DropHelper class:

The method parameter (field name of the SPListItem) of the BeforeMethod method can contain underscores which are replaced by spaces. So, field names with spaces like Start Date of the Task item must be defined in the template as {{task.Start_Date}}.

The SPPropertyDrop class, also part of the solution of this article, is a generic Drop implementation which exposes all properties of an object and may cast them if needed into an Drop objects again. For implementation details see the source code.

Filters and Tags

The solution is also providing a custom filter and tag implementation. The filter called sp_format_date (see template above) is implemented by the method SPFormatDate and is calling the FormatDate method of the class SPUtility form the SharePoint API:

Prior to Microsoft SharePoint 2010 there was no official documented way to programmatically use the built-in ULS service (Unified Logging Service) to log own custom messages. There are still solutions available on the internet that can be used, but SharePoint 2010 now supports full-blown logging service support.

To log a message to the SharePoint log files just call the WriteTrace method of the SPDiagnosticsService class:

The problem with this technique is that the log entry does not contain any category information, instead SharePoint uses the value "Unknown":

Of course, that does not matter if someone develops small solutions. Developing custom solutions you may want to implement an own logging service with custom categories and UI integration within the Central Administration (CA) of SharePoint 2010.

This article shows how to develop a custom logging service that integrates with the Diagnostic Logging UI of the Central Administration:

Custom Logging Service

A custom logging service must inherit from the SPDiagnosticsServiceBase class. This is the base class for diagnostic services in SharePoint and it offers the option to log messages to log files via ULS and to the Windows Event Log. By overriding the ProvideAreas method the service provides information about diagnostic areas, categories and logging levels. A diagnostic area is a logical container of one or more categories.

The sample service defines one diagnostic area and one category (used by application pages) for this area:

The area name as well as the category names will be also shown in the Diagnostic Logging UI of the CA. It is also possible to define a resource DLL to localize the names.

The service will offer the two static methods WriteTrace and WriteEvent. WriteTrace writes the log message to the SharePoint log files, usually saved in the SharePoint folder C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\LOGS.

The WriteEvent writes the log message to the Windows Event Log. The event source is called like the AreaName and later on created within the FeatureReceiver:

The custom logging service must be registered with SharePoint 2010 to show up in the Diagnostic Logging UI of the CA. The event sources also must be created on each server of the SharePoint farm. These two registration steps can be bundle within the FeatureActivated override method of the FeatureReceiver.

The FeatureActivated override is calling the RegisterLoggingService helper method to register with the SharePoint system if the service is not already available. Since one of the base classes of LoggingService is the SPService class which provides an Update and Provision method, we can register the new service farm-wide.

The second step is to create a new Windows Event Log source. Therefore we have to go through the collection of SharePoint farm servers and remotely add the new source by generating registry entries on each server.

To unregister we redo the registration steps by overriding FeatureDeactivating method of the FeatureReceiver class. The UnRegisterLoggingService method then deletes the service and removes all registry keys on all servers in the SharePoint farm.

The sample solution also created an application page to test the logging service. The source code is available as download.

Enter a test message and press the Log button. The message will be logged to the Windows Event Log: