SqlDataReader

C# ADO.NET Call Stored Procedures with SqlDataReader

Call SQL Server stored procedures and stream‑query results using SqlDataReader, key takeaways:

1. SqlCommand.CommandType = CommandType.StoredProcedure marks the command to run a stored procedure
2. Pass stored procedure parameters via SqlParameter
3. ExecuteReader(CommandBehavior.CloseConnection) reads streaming data
4. SqlDataReader is a forward‑only, stream‑based reader; always close it after use

Stored Procedure Definition

You need to create the stored procedure in your database before invoking it.

create proc myProc
@id int
as
select * from [dbo].[Article] where id>@id
go

-- Test call
exec myProc @id=5Code language: JavaScript (javascript)

Accepts input parameter @id and fetches records from the Article table where id exceeds the supplied value.

Call Stored Procedures in C#

Use ExecuteReader to get a SqlDataReader for multiple result rows.

string connectionString = "Data Source=.;Initial Catalog=db;Integrated Security=SSPI;";
using (SqlConnection conn = new SqlConnection(connectionString))
{
    conn.Open();
    SqlCommand sqlCom = new SqlCommand();
    sqlCom.Connection = conn;
    sqlCom.CommandText = "myProc";
    sqlCom.CommandType = CommandType.StoredProcedure;

    SqlParameter param = new SqlParameter("@id", SqlDbType.Int, 8);
    param.Value = 4;
    param.Direction = ParameterDirection.Input;
    sqlCom.Parameters.Add(param);

    SqlDataReader reader = sqlCom.ExecuteReader(CommandBehavior.CloseConnection);
    StringBuilder sb = new StringBuilder();
    while (reader.Read())
    {
        sb.AppendLine(reader[0] + "-" + reader[1]);
    }
    reader.Close();
    MessageBox.Show(sb.ToString());
}Code language: C# (cs)

Dispose Reader with using to prevent resource leaks

string connectionString = "Data Source=.;Initial Catalog=db;Integrated Security=SSPI;";
using (SqlConnection conn = new SqlConnection(connectionString))
{
    conn.Open();
    using (SqlCommand sqlCom = new SqlCommand("myProc", conn))
    {
        sqlCom.CommandType = CommandType.StoredProcedure;//Mark command as stored procedure
        // Add input parameter
        sqlCom.Parameters.Add("@id", SqlDbType.Int).Value = 4;

        // CommandBehavior.CloseConnection: auto‑close connection when reader closes
        using (SqlDataReader reader = sqlCom.ExecuteReader(CommandBehavior.CloseConnection))
        {
            StringBuilder sb = new StringBuilder();
            while (reader.Read())
            {
                // reader[0] = first column, reader[1] = second column; reader["ColumnName"] also works
                sb.AppendLine($"{reader[0]}-{reader[1]}");
            }
            MessageBox.Show(sb.ToString());
        }
        // Manual reader.Close() is unnecessary; using handles cleanup automatically
    }
}Code language: C# (cs)

Three CommandType Enumerations

• CommandType.Text: Run regular SQL statements
• CommandType.StoredProcedure: Execute stored procedures
• CommandType.TableDirect: Read database tables directly

If you omit StoredProcedure, the runtime treats the procedure name as plain SQL and throws errors!

SqlParameter Parameter Directions

• ParameterDirection.Input: Input parameter (pass values in, most common)
• ParameterDirection.Output: Output parameter
• ParameterDirection.InputOutput: Bidirectional input‑output
• ParameterDirection.ReturnValue: Stored procedure return value

CommandBehavior.CloseConnection Purpose

When SqlDataReader calls Close() or gets disposed, it automatically closes the associated SqlConnection.
Best used when returning a Reader from a method; connections clean up once external code finishes reading data.

Key SqlDataReader Characteristics

1. Forward‑only read‑only: Traverse rows forward only with Read(), no backtracking or data modification
2. Holds open connection: While the reader stays open, the database connection cannot run other operations
3. Mandatory resource cleanup: Call reader.Close() manually or prefer automatic disposal via using
4. Two column‑reading approaches:
   csharp reader[0]; // Read by index reader["Title"]; // Read by field name (better readability)

Common Pitfalls

1. Forgot CommandType.StoredProcedure → runtime failure
2. Mismatched parameter name casing or spelling vs stored procedure → parameter binding failure
3. Reader left open → connections remain occupied, exhausting connection pool
4. Run new database queries inside while(reader.Read()): one connection cannot host multiple active readers
5. Missing parameters or mismatched data types in stored procedure → execution errors

Comparison

Execution Method	Use‑Case	Return Object
ExecuteNonQuery	Insert / Update / Delete stored procedures	int rows‑affected
ExecuteScalar	Single‑row single‑column (aggregate queries)	object
ExecuteReader	Multi‑row datasets	SqlDataReader (streaming read)

SqlDataReader