Read Tables from SAP R/3 using SAP.NET Connector

• Download source - 85.5 Kb
• Download demo project - 102 Kb

Introduction

Using SAP's .NET Connector is very easy, all it takes to manage it is a little work and effort. This article provides you an introductory example on how to use the Connector within C#.

The example will teach you:

1. How to use SAP.Connector.SAPLogonDestination to let you select any destination provided by your saplogon.ini file.
2. How to make an RFC call to the SAP R/3 System. Especially the example shows how to call the RFC_READ_TABLE function module to read from any table within your SAP system.
3. How to extract the field values of the data rows returned by the RFC call.

Any GUI stuff is omitted to keep the example as simple as possible and to focus on the Connector's use.

Background

You should have some experiences with ABAP/4 and function modules and the SAP.NET Connector installed. Any project that uses the Connector must have a reference to SAP.Connector.dll. The Connector is available at http://service.sap.com/connectors and an overview is given at http://www.microsoft-sap.com/net_connector.aspx.

Using the code

If you want to use DBReader in your application, download and unzip the source file. Add a reference to the library assembly DBReader.dll that comes with the sources. DBReader.dll provides the following classes:

1. DBReader.TableReader, wrapper for the RFC_READ_TABLE call to SAP.
2. DBReader.ResultSet, to manage the returned dataset.
3. DBReader.ResultColumn, to hold a column of the returned dataset.
4. DBReader.Proxy, wrapper for the proxy generated by the Connector wizard.
5. DBReader.Logon, wrapper for SAP.Connector.SAPLogonDestination.
6. DBReader.ConnectionInfo, to hold logon information.

If you want to see how it works, download and unzip the demo file to disk. Either run DBReaderDemo.exe in the bin subfolder of the demo project from console directly, or open DBReaderDemo.sln into Visual Studio and run the demo.

The TableReaderDemo class shows how to use the DBReader for a query to SAP. Consider the following SQL statement in ABAP:

    SELECT TABNAME, TABCLASS
      FROM DD02L
     WHERE TABNAME LIKE 'NP%'
           AND ( TABCLASS EQ 'TRANSP' OR
                 TABCLASS EQ 'CLUSTER' OR
                 TABCLASS EQ 'POOL' OR
                 TABCLASS EQ 'VIEW' )

The equivalent code to achieve this with DBReader from outside SAP shows a demo function of class TableReaderDemo:

    public void demoTableReader(){
      // Show the available destinations and let the user select one.

      string destName = this.askForDestination();
      if(destName == null)
        return;

      // Get the selected destination object.

      SAP.Connector.Destination dest = this.saplogon.getDestinationByName(
        destName);
      Console.WriteLine("Connecting to '" + destName + "'");

      // Information like system number, SAPServer etc. are already

      // obtained by the .Net Connector from the c:\windows\saplogon.ini file.

      // It remains to add these information:

      dest.Client   = 1;
      Console.Write("User:     ");
      dest.Username = Console.ReadLine(); 
      Console.Write("Password: ");
      dest.Password = Console.ReadLine();
      dest.Language = "EN";

      // Setup the TableReader

      SAPReader.TableReader tabReader = new TableReader(dest);
      // Specify the table to read.

      // The table DD02L contains metadata of all the available

      // tables on a SAP system.

      tabReader.QueryTable = "DD02L";
      // How many rows of the table should be skipped.

      tabReader.RowSkip = 10;
      // How many rows should be selected (at most, if available).

      tabReader.RowCount = 6;
      // Yes, we want to see data.

      // Default is NoData=true, i.e., only table information is retrieved.

      tabReader.NoData = false;
      // Select FOI (fields of interest)

      SAPReader.SAPKernel.RFC_DB_FLD tabNameField  = 
          tabReader.addQueryField("TABNAME");
      SAPReader.SAPKernel.RFC_DB_FLD tabClassField = 
          tabReader.addQueryField("TABCLASS");
      //

      // Add the WHERE clause

      string pattern = "NP%";
      // Select the names of all the transparent, cluster, pool

      // and view tables containing the pattern:

      tabReader.addWhereClause("TABNAME LIKE '" + pattern + "'");
      tabReader.addWhereClause("AND ( TABCLASS EQ 'TRANSP' OR");
      tabReader.addWhereClause("      TABCLASS EQ 'CLUSTER' OR");
      tabReader.addWhereClause("      TABCLASS EQ 'POOL' OR");
      tabReader.addWhereClause("      TABCLASS EQ 'VIEW' )");

      // Force to read the table from SAP

      string abapException = tabReader.read();
      if(abapException == null){
        // Output the result:

        SAPReader.ResultSet RS = tabReader.getResultSet();
        Console.WriteLine("\n\nTableData read:");
        Console.WriteLine(tabNameField.Fieldname + "\t\t" + 
           tabClassField.Fieldname);
        Console.WriteLine(
           "-----------------------------------------------------");
        for(int i = 0; i < RS.LineCount; i++){
          Console.Write(RS.getEntryAt(tabNameField.Fieldname, i).Trim());
          Console.Write("\t\t" + 
               RS.getEntryAt(tabClassField.Fieldname, i).Trim());
          Console.WriteLine();
        }
      }
      else{
        Console.WriteLine("ABAP-Exception: " + abapException );
      }
      // That's it!

    }

After the table was read successfully, the ResultSet is traversed row-wise (although the data is stored column-wise there) and printed to the screen.

Points of Interest

The fragmented screenshot below shows the console output for this example.

The SAPReader.Logon saplogon is an attribute of class TableReaderDemo and is derived from SAP.Connector.SAPLogonDestination. If the user selects a destination name, the saplogon selects the corresponding destination parameters:

    public SAP.Connector.Destination getDestinationByName(string name){
      // Map the name used for displaying the available destination,

      // e.g. at SAPLogon, to the internal name used to address this item.

      // BTW, the internal name (key) is derived from saplogon.ini.

      string destName = this.GetDestinationNameFromPrintName(name);
      // null returned if the destination does not exist.

      if(destName == null || destName == "" ){
        Console.WriteLine(this.GetType().ToString() 
          + ".getDestinationByName: Destination " + name + 
                  " does not exist."
        );
        Console.WriteLine("Available Destinations are: ");
        this.printAvailableDestinations(Console.Out);
        Environment.Exit(0);
      }
      // This is the key statement for selecting the 

      // desired destination item:

      this.DestinationName = destName;
      // Now all information is retrieved from the SAPLogon's ini file

      // to the respective variables of 'this' destination object.

      // (The ini file is stored in the private variable:

      // SAP.Connector.SAPLogonDestination.saplogon.fileName)


      return (SAP.Connector.Destination)this;
    }

The RFC call is performed by TableReader this way:

    public string read(){
      if(this.proxy.connected() == false){
        this.proxy.connectSAP();
      }
      
      try {
        // Force the proxy to make the rfc call synchronously

        // See documentation for RFC_READ_TABLE Function Module at

        // your SAP system for further details (Transaction SE37).

        this.proxy.SAPProxy.Rfc_Read_Table(this.delim, this.noData, 
          this.qTable, this.rowCount, this.rowSkip,
          ref this.data, ref this.fields, ref this.options);
        
        // Check if data found

        if( this.NoData == false && this.data.Count == 0 ){
          return "No data found";
        }
        
        // NoData set

        if(this.NoData == true){
          for( int i = 0; i < this.fields.Count; i++){
            this.results.addEntry(this.fields[i], "NoData");
          }
        }
        // NoData not set

        // Save data to the column-wise ResultSet.

        else if(this.data.Count > 0 ){
          //Loop over data rows

          for( int i = 0; i < this.data.Count; i++){
            //Loop over fields

            for(int j = 0; j < this.fields.Count; j++){
              string val = this.parseTableRow(this.fields[j], this.data[i]);
              this.results.addEntry(this.fields[j], val);
            }
          }
        }//else

      }
      catch (SAP.Connector.RfcSystemException ex) {
        System.Text.StringBuilder msg = new System.Text.StringBuilder();
        msg.Append("Table: " + this.qTable);
        foreach(SAPKernel.RFC_DB_FLD field in this.fields){
          msg.Append("\nFields: " + field.Fieldname);
        }
        foreach(SAPKernel.RFC_DB_OPT opt in this.options){
          msg.Append("\nOptions: " + opt.Text);
        }
        Console.WriteLine(msg + "\n" 
          + "Error calling SAP RFC \n" + ex.ToString() + "\n" + ex.ErrorCode,
          "Problem with SAP"
          );

        return ex.ErrorCode;
      }
      catch(SAP.Connector.RfcAbapException ex){
        return ex.ErrorCode;
      }
      return null;
    }

Data returned by the RFC call is stored row-wise. A value of a field in a certain row can be extracted by using the offset and length stored in each field structure returned:

    public string parseTableRow(SAPReader.SAPKernel.RFC_DB_FLD field, 
         SAPReader.SAPKernel.TAB512 dataRow ){
      // Length of the field

      int len = int.Parse(field.Length);
      // Position where the field's value starts in the data row

      int offset = int.Parse(field.Offset);
      
      // The data row, containing all the field values concatenated

      // by SAP's rfc_read_table

      string row = dataRow.Wa;
      string retValue = "";

      try{
        if(offset < row.Length){
          // Read the field's value starting at the position specified 

          // by offset.

          if(offset + len > row.Length)
            // Read until the end of the row string

            retValue = dataRow.Wa.Substring(offset);
          else
            // Read only len characters, otherwise.

            retValue = dataRow.Wa.Substring(offset,len);
        }
      }catch(System.ArgumentOutOfRangeException e){
        Console.WriteLine(e.ToString());
        Environment.Exit(0);
      }
      return retValue;
    }

If you have any comments or suggestions on this, please let me know!