AdoSamples.cs

using System;
using System.Data.SqlClient;
using System.Data;
using System.Collections.Generic;

namespace AdoUsageDemo
{
    // While I prefer to use LINQ for these types of database actions, we have some code
    // in some of our applications that has been around for a while where we cannot take the
    // time to upgrade them.  So this is to demonstrate how to refactor older ADO.NET code in
    // such a way as to most efficiently use the connection pool.  Comments are welcome.
    public class AdoSamples
    {
        private const string SCALAR_SELECT = "select singlevalue from sometable where someCondition = 1";
        private const string MULTI_ROW_SELECT = "select valueA from someTable";
        private const string SINGLE_ROW_MULTI_COLUMN_SELECT = "select valueA, valueB from someTable where someCondition = 1";
        private const string CONNECTION_STRING = "your connection string";

        public IEnumerable<string> MultipleResultDemo()
        {
            // You may be tempted to try and manage a single connection object if you have
            // lots of cases in your code where you need to connect to the same server, it is
            // best to let the connection pool manage this.  If you create your connections
            // within 'using' statements and make sure that they are closed properly then 
            // the connection pool will keep some of them around for a while and re-use them
            // without actually destroying and recreating them.
            using (IDbConnection con = new SqlConnection(CONNECTION_STRING))
            {
                // The SqlConnection has a command factory that will return a command object 
                // and use itself as the associated connection.
                using (IDbCommand cmd = con.CreateCommand())
                {
                    // always specify the command type.  If you don't, then the server will have to
                    // make an assumption and it may not be the correct one!
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = MULTI_ROW_SELECT;
                    
                    con.Open();
                    IDataReader reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        yield return reader.GetString(0);
                    }
                } // cmd is closed and properly disposed of as we move out of scope
            } // con is closed and properly disposed of as we move out of scope.
        }

        public void SingleRowMultiColumnDemo()
        {
            String valueA;
            int valueB;
            using (IDbConnection con = new SqlConnection(CONNECTION_STRING))
            {
                using (IDbCommand cmd = con.CreateCommand())
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = SINGLE_ROW_MULTI_COLUMN_SELECT;
                    
                    con.Open();
                    IDataReader reader = cmd.ExecuteReader();
                    // I used 'if' here rather than 'while' because I know that I am only 
                    // expecting a single row.  
                    if (reader.Read())
                    {
                        valueA = reader.GetString(reader.GetOrdinal("valueA"));
                        valueB = reader.GetInt32(reader.GetOrdinal("valueB"));
                    }
                }
            }

            // do whatever you need with valueA and valueB.
        }

        public void ScalarSelectDemo()
        {
            string singleValue;
            using (IDbConnection con = new SqlConnection(CONNECTION_STRING))
            {
                using (IDbCommand cmd = con.CreateCommand())
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = SCALAR_SELECT;
                    con.Open();
                    // since I'm only getting a single column from a single result set, then I 
                    // can call ExecuteScalar to get that one value.  The return type is "Object"
                    // so it is up to me to deal with converting that and properly handling the case
                    // where I may get something unexpected.  
                    singleValue = cmd.ExecuteScalar().ToString();
                }
            }
        }
    }
}