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.

Advertisements

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

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

How To Use SharePoint 2010 Secure Store As Single Sign-On Service For SAP Applications Using ERPConnect

*** NEW! My blog moved to my homepage at http://www.parago.de! ***

The Secure Store Service in SharePoint 2010 replaces the Single Sign-on Shared Service of MOSS 2007 and provides an easy way to map user credentials of external resources like SAP systems to Windows users. During the process of developing SAP interfaces using the very handy ERPConnect library from Theobald Software you have to open a R3 connection with the SAP system using SAP account credentials (username and password).

In most cases you will use a so called technical user with limited access rights to execute or query objects in SAP, but a SAP system saves a lot of sensitive data which cannot all be shown to all users. So, creating a new secure store in SharePoint 2010 to save the SAP user credentials will be the solution. Accessing the secure store from program code is quite simple.

A trail version of the ERPConnect library can be downloaded at www.theobald-software.com.

Secure Store Configuration

The Secure Store Service will be managed by the Central Administration (CA) of SharePoint 2010 under Application Management > Manage service applications > Secure Store Service:

Screenshot1

As the screenshot above shows is it possible to create multiple target applications within one Secure Store Service.

Clicking the New button will open the Create New Secure Store Target Application page. In this dialog you have to enter the new Target Application ID, a display name, a contact email address and other application related details (see screenshot below).

Screenshot2

Next, the application fields must be defined:

Screenshot3

It’s important to select the field type User Name and Password, because our implementation later on will check the target application for those two field types.

In the last dialog step the application administrator must be defined. After defining the administrator and clicking the Ok button SharePoint is creating a new secure store:

Screenshot5

Next, the Windows users must be mapped to the SAP user credentails. Therefore mark the checkbox for the newly created secure store SAPCredentialsStore and click the Set button in the toolbar. This opens the following dialog:

Screenshot6

The Credential Owner is the Windows user for whom the SAP user credentials will be set. Enter the SAP username and password and click the Ok button to save them.

That’s it !

Secure Store Programming

Accessing the secure store by code is simple. We implement a SecureStore class which will encapsulate all the access logic. A second class called SecureStoreCredentials contains the retrieved user credentials returned by the method GetCurrentCredentials of the SecureStore class.

But first, we need to create a Visual Studio 2010 SharePoint project and reference a couple of assemblies. You can directly enter the file paths in the Reference dialog (Browse tab) to add the assemblies:

Microsoft.BusinessData.dll
C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\ISAPI\Microsoft.BusinessData.dll

Microsoft.Office.SecureStoreService.dll
C:\Windows\assembly\GAC_MSIL\Microsoft.Office.SecureStoreService\14.0.0.0__71e9bce111e9429c\Microsoft.Office.SecureStoreService.dll

The following code shows the SecureStore class implementation:

internal class SecureStore
{
  public string ApplicationId { get; private set; }

  public SecureStore(string applicationId)
  {
    if(string.IsNullOrEmpty(applicationId))
      throw new ArgumentNullException("applicationId");
    if(!IsApplicationValid(applicationId))
      throw new ArgumentException(string.Format("Target application with ID '{0}' is not defined.", applicationId));

    ApplicationId = applicationId;
  }

  public SecureStoreCredentials GetCurrentCredentials()
  {
    SecureStoreProvider provider = new SecureStoreProvider { Context = SPServiceContext.Current };
    string userName = string.Empty;
    string password = string.Empty;

    using(SecureStoreCredentialCollection data = provider.GetCredentials(ApplicationId))
    {
      foreach(ISecureStoreCredential c in data)
      {
        if(c != null)
        {
          if(c.CredentialType == SecureStoreCredentialType.UserName)
            userName = GetDecryptedCredentialString(c.Credential);
          else if(c.CredentialType == SecureStoreCredentialType.Password)
            password = GetDecryptedCredentialString(c.Credential);
        }
      }
    }

    if(string.IsNullOrEmpty(userName) || string.IsNullOrEmpty(password))
      throw new SecureStoreException("Credentials for the current Windows user are not valid or not defined.");

    return new SecureStoreCredentials(userName, password);
  }

  public static bool IsApplicationValid(string applicationId)
  {
    if(string.IsNullOrEmpty(applicationId))
      throw new ArgumentNullException("applicationId");

    SecureStoreProvider provider = new SecureStoreProvider { Context = SPServiceContext.Current };

    foreach(TargetApplication application in provider.GetTargetApplications())
    {
      if(application.ApplicationId == applicationId)
      {
        ReadOnlyCollection<ITargetApplicationField> fields = provider.GetTargetApplicationFields(applicationId);
        bool existsUserNameDefinition = false;
        bool existsPasswordDefinition = false;

        foreach(TargetApplicationField field in fields)
        {
          if(field.CredentialType == SecureStoreCredentialType.UserName)
            existsUserNameDefinition = true;
          else if(field.CredentialType == SecureStoreCredentialType.Password)
            existsPasswordDefinition = true;
        }

        if(existsUserNameDefinition && existsPasswordDefinition)
          return true;
      }
    }

    return false;
  }

  public static string GetDecryptedCredentialString(SecureString secureString)
  {
    IntPtr p = Marshal.SecureStringToBSTR(secureString);

    try
    {
      return Marshal.PtrToStringUni(p);
    }
    finally
    {
      if(p != IntPtr.Zero)
        Marshal.FreeBSTR(p);
    }
  }
}

The constructor checks if an application ID is passed and if it’s valid by calling the static method IsApplicationValid. In first place, the IsApplicationValid method is creating an instance of the SecureStoreProvider class to get access to Secure Store Service. The SecureStoreProvider class provides all methods to talk to the SharePoint service. Then, the method queries for all target applications and checks for the given application. If the application has been created and can be found, the method will analyse the application field definitions. The IsApplicationValid method then looks for two fields of type User Name and Password (see above).

The GetCurrentCredentials method is actually trying to get the SAP user credentials from the store. The method is creating an instance of the SecureStoreProvider class to get access to service and then calls the GetCredentials method of the provider class. If credentials are available the method decrypt the username and password from type SecureString using the internal method GetDecryptedCredentialString. It then will wrap and return the data into an instance of the SecureStoreCredentails class.

For more details of the implementation see the source code.

Accessing SAP Using The Secure Store Credentials

The sample and test code calls the SAP function module SD_RFC_CUSTOMER_GET to retrieve all customer data that match certain criteria (where NAME1 starts with Te*):

Screenshot7

The following code shows the implementation of the Test button click event:

protected void OnTestButtonClick(object sender, EventArgs e)
{
  string licenseKey = "<LICENSEKEY>";
  string connectionStringFormat = "CLIENT=800 LANG=EN USER={0} PASSWD={1} ASHOST=HAMLET ...";

  R3Connection connection = null;

  try
  {
    LIC.SetLic(licenseKey);

    ...

    SecureStoreCredentials credentials =
      new SecureStore(ApplicationID.Text).GetCurrentCredentials();
    string connectionstring =
      string.Format(connectionStringFormat, credentials.UserName, credentials.Password);

    connection = new R3Connection(connectionstring);
    connection.Open();

    RFCFunction function = connection.CreateFunction("SD_RFC_CUSTOMER_GET");
    function.Exports["NAME1"].ParamValue = "Te*";
    function.Execute();

    ResultGrid.DataSource = function.Tables["CUSTOMER_T"].ToADOTable();
    ResultGrid.DataBind();

    OutputLabel.Text = string.Format("The test called...",
      ApplicationID.Text, Web.CurrentUser.Name, Web.CurrentUser.LoginName,
      credentials.UserName);
  }
  catch(Exception ex)
  {
    WriteErrorMessage(ex.Message);
  }
  finally
  {
    if(connection != null && connection.Ping())
      connection.Close();
  }
}

The interesting part is the retrieval of the SAP user credentials from the secure store defined in the text box named ApplicationID. The application ID will be passed as parameter to the constructor of the SecureStore class. After creating the instance the method GetCurrentCredentials will be called to ask the store for the credentials of the current Windows user.

After the user credential query has been successfully executed the SAP connection string will be constructed. Then the connection string will then be used to create an instance of the R3Connection class to connect with the SAP system. The remaining code is just calling the function module SD_RFC_CUSTOMER_GET and binding the result to the SPGridView.

Download Source Code | Download Article (PDF)

How To Access SAP Business Data From Silverlight 4 ClientsUsing WCF RIA Services

The introduction of Microsoft’s WCF RIA Services for Silverlight 4 simplified very much the development process of N-tier business applications using Silverlight and ASP.NET. By using this new technology we can also easy access and integrate SAP business data in Silverlight clients.

This article shows how to provide a SAP domain service as web service that will be consumed by a Silverlight client. The sample application will allow the user to query customer data. The service uses LINQ to SAP from Theobald Software to connect to a SAP R/3 system.

Project Setup

The first step in setting up a new Silverlight 4 project with WCF RIA Services is to create a solution using the Visual Studio template Silverlight Navigation Application:



Visual Studio 2010 is then asking you to create an additional web application, which hosts the Silverlight application. It’s important to select the checkbox Enable WCF RIA Services (see screenshot below):


After clicking the Ok button, Visual Studio generates a solution with two projects, one Silverlight 4 project and one ASP.NET project. In the next section we are creating the SAP data access layer using the LINQ to SAP designer.

LINQ to SAP

The LINQ to SAP provider and its Visual Studio 2010 designer offers a very handy way to design SAP interfaces visually. The designer will generate the code for the SAP data access layer automatically, similar to LINQ to SQL. The LINQ provider is part of the .NET library ERPConnect.net from Theobald Software. The company offers a demo version for download on its homepage.

The next step is to create the needed LINQ to SAP file by opening the Add New Item dialog:


LINQ to SAP is internally called LINQ to ERP.

Clicking the Add button will create a new ERP file and opens the LINQ designer. Now, drag the Function object from the toolbox and drop it onto the designer surface. If you have not entered the SAP connection data so far, you are now asked to do so:


Enter the connection data for your SAP R/3 system and then click the Ok button. Next, search for and select the SAP function module named SD_RFC_CUSTOMER_GET. The function module provides a list of customer data.

The RFC Function modules dialog opens and let you define the necessary parameters:


In the above function dialog, change the method name to GetCustomers and mark the Pass checkbox for the NAME1 parameter in the Exports tab. Also set the variable name to namePattern. On the Tables tab mark the Return checkbox for the table parameter CUSTOMER_T and set the table and structure name to CustomerTable and CustomerRow:


After clicking the Ok button and saving the ERP file, the LINQ designer will generate a SAPContext class which contains a method called GetCustomers with an input parameter named namePattern. This method executes a search for SAP customer data allowing the user to enter a wildcard pattern. The method returns a table of customer data:


On the LINQ designer level (click on the free part of the LINQ designer surface) the property Create Object Outside Of Context Class must be set to True:


Now, we finally add a Customer class which we use in our SAP domain service later on. This class and its values will be transmitted to the Silverlight client by the WCF RIA Services. It’s important to set the Key attribute on the identifier fields for WCF RIA Services, otherwise the project will not compile:


That’s it! We now have our SAP data access layer ready to use and can start adding the domain service in the next section.

SAP Domain Service

The next step is to add the SAP domain service to our web project. A domain service is a specialized WCF service and is one of the core constructs of WCF RIA Services. The service exposes operations that can be called from the client generated code. On the client side we use the domain context to access the domain service on the server side.

Add a new Domain Service Class and name it SAPService:


In the upcoming dialog create an empty domain service class by just clicking the Ok button:


Next, we add the service operation GetCustomers to the SAP service with a name pattern parameter. The operation then returns a list of Customer objects. The Query attribute limits the result set to 200 entries.

The operation uses the visually designed SAP data access logic to retrieve the SAP customer data. First of all, an instance of the SAPContext class will be created using a connection string (see sample in code). For more details regarding the SAP connection string see the ERPConnect.net manual.

The LINQ to SAP context class contains the GetCustomers method which we will call using the given namePattern parameter. Next, the operation creates an instance of the Customer class for each customer record returned by SAP.

The license code for the ERPConnect.net library is set in the constructor of our domain service class.


That’s all we need on the server side.

In the next section we are implementing the Silverlight client.

Silverlight Client

The implementation of the client side is straightforward. The home view contains a DataGrid control to display the list of customer data as well as a search area with TextBox and Button controls to allow users to enter name search pattern.

The click event handler of the load button, called OnLoadButtonClick, will execute the SAP service. The boilerplate code to access the web service was generated by WCF RIA Services in the subfolder Generated_Code in the Silverlight project.

First of all, an instance of the SAPContext will be created. Then we load the query GetCustomersQuery and execute the service operation on the server side using WCF RIA Services. If the domain service returns an error, the callback anonymous method will mark the error as handled and display the error message.

If the execution of the service operation succeeded the result set gets displayed in the DataGrid control.


The next screenshot shows the final result:


That’s it.

Summary

This article has shown how easy SAP customer data can be integrate within Silverlight clients using tools like WCF RIA Services and LINQ to SAP. It is quite simple to extend the SAP service to integrate all kind of operations.

Download Source Code

New article about integrating SAP data into SharePoint 2010

I wrote a new article about SharePoint 2010, SAP and BCS (Business Connectivity Services). I have published this article and a demo project with source code on Codeproject:

How To Integrate SAP Business Data Into SharePoint 2010 Using Business Connectivity Services And LINQ to SAP

A PDF version of the article can be downloaded from my Homepage.

Feel free to send me your feedback.