C# Code Snippet: Read Data from Stored Procedure

by

I love clear, concise, easy to read code. Here’s a fairly minimalist .NET 2.0 code snippet to read data using a stored procedure that is hopefully all those things. It makes use of the using statement which guarantees that resources held by types that implement IDisposable will be released even if an exception is thrown (using is syntactic sugar for an enclosing try/catch/finally loop):

// Execute a stored proc to read data
using (SqlConnection conn = new SqlConnection(this.connectionString))
{
    using (SqlCommand cmd = conn.CreateCommand())
    {
        cmd.CommandText = "ProcName";
        cmd.CommandType = CommandType.StoredProcedure;
 
        // Add any input Params...
        cmd.Parameters.AddWithValue("@SomeIDParam", myID);
 
        conn.Open();
 
        // Assuming Stored Proc returns a set of records...
        using (SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
        {
            while (rdr.Read())
            {
                // do something with each rdr row ... 
            }
        }
 
        // OR alternatively, 
        // if your proc returns a single valued result (e.g. an image)
        // (i.e. query of the form "SELECT imgColumnName FROM Table WHERE ID= ?"
        // byte[] img = (byte[])cmd.ExecuteScalar();  
    }
}