Skip to main content

 

Reference

 

 

OutSystems

Call a Stored Procedure

  • Edit
    Collaborate with us
    Edit this page on GitHub
  • To call a stored procedure you have to use some .NET native objects that you can obtain by calling the RuntimePublic.Db API methods. To obtain them, all query objects have a GetDriver<ClassName> method that returns the language native object.

    When using .NET native objects, make sure that you comply with:

    • The database engine's specific syntax
    • The specific behaviors of the driver

    Example

    In this example, we have an external database accessed through a connection called 'Data Warehouse' that is defined in the environment management console.

    We will call a store procedure called COMPUTE_SCORE, which calculates the score performance for an employee. To do it, it receives the employee's name and the output parameter, an integer number, and returns that parameter with the value correspondent to the performance for the employee.

    C# Code Sample

    using OutSystems.RuntimePublic.Db;
    
    // [...]
    
    // Get a reference (dbaProvider) to the external database "Data Warehouse"
    DatabaseProvider dbaProvider = DatabaseAccess.ForExternalDatabase("Data Warehouse");
    
    SqlHelper sqlHelper = dbaProvider.SqlHelper;
    
    using (RequestTransaction trans = dbaProvider.GetRequestTransaction()) {
        using (Command cmd = trans.CreateCommand("COMPUTE_SCORE")) {
            // There is no API to set the command type so you need to access the
            // underlining ADO.NET objects
            cmd.GetDriverCommand().CommandType = CommandType.StoredProcedure;
    
            // Set the employee (input) and performanceScore (output) parameters
            cmd.CreateParameter("employee", DbType.String, "John Baker");
            var performanceScore = cmd.CreateParameter("score", DbType.Int32, DBNull.Value);
    
            // Set parameter direction on ADO.NET Data Parameter
            // The driver will fill the output parameter with a value after the query executes
            performanceScore.GetDriverParameter().Direction = ParameterDirection.Output;
    
            // Call the store procedure
            cmd.ExecuteNonQuery();
    
            // Print the store procedure's output
            Console.WriteLine(performanceScore.Value);
        }
    }
    
    • Was this article helpful?