ISqlExecutorExtensions.ExecuteCursor(string sqlText, out DbResultSet resultSet, params object[] parameters) Method

.NET Standard 2.x

Executes the SQL statement and returns the result set in a cursor.

Namespace: PowerScript.Bridge

Assembly: PowerScript.Bridge.dll

Syntax

  public static void ExecuteCursor(this ISqlExecutor sqlExecutor, string sqlText, 
  out DbResultSet resultSet, params object[] parameters)

Parameters

sqlText System.String

A raw SQL statement which contains the parameter placeholders.

The proper syntax of a parameter placeholder is specific to the data source (e.g. @newFirstName for SQL Server). To make a parameterized SQL (e.g. update Person set FirstName = @newFirstName where PersonId = 1), these placeholders are filled in with the actual parameter values when the SQL statement is executed.

resultSet PowerScript.Bridge.DbResultSet

The result set returned after the SQL statement is executed.

parameters System.Object[]

(Optional) One or more ParamValue objects which contain the values corresponding to the SQL parameter identifier names.

You can also pass the arguments directly in the order in which each parameter identifier name appears in sqlText for the first time, without using the ParamValue object.

Remarks

This method uses command behavior of CommandBehavior.SequentialAccess internally, which provides a way for the DataReader to handle rows that contain columns with large binary values.

It creates each model object and reads data from database to this model object only when the data is asked for. You can use this method to save resources and improve performance by deferring the initialization of expensive objects until they are requested.

After obtaining the required data, you need to call the DbResultSet.Close method to release the resources occupied by the database connection and DataReader. Before the returned DBResult object is closed, the current DataContext cannot be used for other data operations. Otherwise, an exception will be thrown.

Examples

The following code example demonstrates how to use the ExecuteCursor method to execute a SQL statement in a cursor and return the result set.

using System;
using PowerScript.Bridge;

namespace Appeon.ApiDoc.ISqlExecutorExtensionsExamples
{
    public class ExecuteCursorExample
    {
        private readonly SchoolContext _context;

        public ExecuteCursorExample(SchoolContext dataContext)
        {
            // Sets the data context
            _context = dataContext;
        }

        public void Example()
        {
            // Defines a SQL statement which gets the data of StudentGrade table according to StudentID.
            string sql = "SELECT EnrollmentID, Grade, CourseID FROM StudentGrade WHERE StudentID = @StudentID";

            int studentID = 2;
            // Executes the SELECT statement in a cursor and stores the result sets in the DbResultSet object.
            _context.SqlExecutor.ExecuteCursor(sql, out DbResultSet resultSet, studentID);

            // Gets another row of data.
            resultSet.Next();

            // Shows the result set.
            while (resultSet.SqlCode == 0)
            {
                Console.WriteLine($"EnrollmentID: {resultSet.GetValue<int>("EnrollmentID")}, " +
                    $"Grade: {resultSet.GetValue<decimal>("Grade")}, " +
                    $"CourseID: {resultSet.GetValue<int>("CourseID")}");

                resultSet.Next();
            }

            resultSet.Close();

            /*This code produces the following output:
            
            EnrollmentID: 1, Grade: 4.00, CourseID: 2021
            EnrollmentID: 2, Grade: 3.50, CourseID: 2030
            */
        }
    }
}

Applies to

.NET Standard

2.x