How to use client-side cursor for SqlConnection/SqlDataReader?

Question

I have a SQL Server 2014 instance and a client application written in C#.

From this application I retrieve data using the code below. Using some empirical testing, it seems server-side cursors are being used. For my particular use-case I would like to use client-side cursors. How can this be done?

How can I make my connection use client-side cursors?

<connectionStrings>
   <clear/>
   <add name="myDB" 
        connectionString="Server=SERVER\INSTANCE;Database=test"
        providerName="System.Data.SqlClient" />
</connectionStrings>

C# client code:

using (var connection = OpenConnection())
{
    using (var command = connection.CreateCommand())
    {
        command.CommandType = CommandType.StoredProcedure;
        command.CommandText = "dbo.my_stored_procedure";

        using (var reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                object retrievedObject = reader.GetValue(0);
                // Use object...
            }
        }
    }
}

Useful links:

• Client-Side Cursors Versus Server-Side Cursors
• Cursor Types (SQLSRV Driver)

Answer 1

Assuming by a "client-side cursor" you mean you want to transfer all the results to the client immediately rather than progressively reading from server as it executes, just load it into a DataTable.

DataTable dt = new DataTable();
dt.Load(reader);
foreach (var row in dt.Rows) { /* Your data is in row now */}