Performing a Query by Using the SELECT Command

The .NET Framework Data Provider for mySAP Business Suite exposes the SAP system as an ADO.NET data source. With the .NET Framework Data Provider for mySAP Business Suite, you can query SAP artifacts by executing a SELECT statement.

To query SAP artifacts using the Data Provider for SAP, perform the following steps:

To perform a query
  1. Include a reference (and a using statement in your code) to Microsoft.Data.SAPClient.

  2. Create a SAPConnection object by using a Data Provider for SAP connection string. For more information about the connection string, see The Data Provider for the SAP Connection String.

  3. Open a connection to the SAP system by invoking Open on the SAPConnection.

  4. Create a SAPCommand object from the SAPConnection.

  5. Specify the SELECT statement in the CommandText property of the SAPCommand. If necessary, you can specify parameters using SAPParameter objects. For more information about how to query SAP artifacts using a SELECT statement, see Syntax for a SELECT Statement. For examples of how to specify a BAPI or RFC, see Examples for SELECT Statement.

  6. Execute the command to perform the query and obtain the results in a SAPDataReader.

  7. Read the results from the SAPDataReader.

  8. When you are finished using them, close (or dispose) the SAPConnection and the SAPDataReader.

The Data Provider for SAP also exposes a SAPClientFactory class, which you can use to create SAPConnection, SAPCommand and SAPConnection objects. For more information about the ADO.NET classes extended by the Data Provider for SAP, see Extending ADO.NET Interfaces.

The following example writes the results of a select on a parameterized inner join statement to the console.

using System;
using System.Collections.Generic;
using System.Text;

using Microsoft.Data.SAPClient;

namespace SapAdoSelect
{
    class Program
    {
        static void Main(string[] args)
        {
        /// <summary>
        /// select top 1 * from sflight inner join spfli on sflight.connid = spfli.connid where spfli.connid = @connid
        /// </summary>

        string connstr = "TYPE=A; ASHOST=YourSapHost; SYSNR=00; CLIENT=800; LANG=EN; USER=YourUserName; PASSWD=YourPassword;";

           using (SAPConnection conn = new SAPConnection(connstr))
            {
                conn.Open();
                using (SAPCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "select top 1 * from sflight inner join spfli on sflight.connid = spfli.connid where spfli.connid = @connid";
                    cmd.Parameters.Add(new SAPParameter("@connid", 17));                    
                    using (SAPDataReader dr = cmd.ExecuteReader())
                    {
                        do
                        {
                            int rows = 0;
                            while (dr.Read())
                            {
                                rows++;
                                StringBuilder b = new StringBuilder();
                                for (int i = 0; i < dr.FieldCount; i++)
                                {
                                    b.Append(dr[i].ToString()+" ");
                                }
                                Console.WriteLine("row {0}: {1} ", rows, b.ToString());
                            }
                            Console.WriteLine("Number of rows:{0}", rows);

                        } while (dr.NextResult());
                    }
                }
            }
        }
    }
}
Show: