UseCommandBehavior.cs

using System;
using System.Data.SqlClient;
using System.Data;
using System.Text;

namespace NET.MST.Ninth.UseCommandBehavior
{
    partial class UseCommandBehavior
    {
        /// <summary>
        /// 测试方法
        /// </summary>
        /// <param name="args"></param>
        static void Main(string[] args)
        {
            //建立连接
            SqlConnection con = new SqlConnection(conn_String);
            try
            {
                //测试使用了CommandBehavior.CloseConnection的方法
                Console.WriteLine("测试使用了CommandBehavior.CloseConnection的方法：");
                SqlDataReader sdr = GetReader_CloseConnection(con);
                while (sdr.Read()) { }
                sdr.Close();
                Console.WriteLine("读取完毕后的连接状态：" + con.State.ToString());
                //测试没有使用CommandBehavior.CloseConnection的方法
                Console.WriteLine("测试没有使用CommandBehavior.CloseConnection的方法：");
                SqlDataReader sdr1 = GetReader_NoCloseConnection(con);
                while (sdr1.Read()) { }
                sdr1.Close();
                Console.WriteLine("读取完毕后的连接状态：" + con.State.ToString());
                Console.Read();
            }
            finally
            {
                //确保连接被关闭
                if (con.State != ConnectionState.Closed)
                    con.Close();
            }
        }
    }

    partial class UseCommandBehavior
    {
        const String conn_String = "Server=localhost;Integrated Security=true;database=NetTest";
        const String Sql = "select * from dbo.DepartCost";       
        /// <summary>
        /// 使用CommandBehavior.CloseConnection
        /// </summary>
        /// <param name="con">为了测试需要，传入连接对象</param>
        /// <returns></returns>
        static SqlDataReader GetReader_CloseConnection(SqlConnection con)
        {
            try
            {
                //打开连接，执行查询
                //并且返回SqlDataReader
                con.Open();
                SqlCommand cmd = con.CreateCommand();
                cmd.CommandText = Sql;
                SqlDataReader dr = cmd.ExecuteReader
                                (CommandBehavior.CloseConnection);
                return dr;
            }
            finally
            {
                //因为使用了CommandBehavior.CloseConnection，
                //这里不需要关闭连接
                //con.Close();
            }
        }
        /// <summary>
        /// 不使用CommandBehavior.CloseConnection
        /// </summary>
        /// <param name="con">为了测试需要，传入连接对象</param>
        /// <returns></returns>
        static SqlDataReader GetReader_NoCloseConnection(SqlConnection con)
        {
            try
            {
                //打开连接，执行查询
                //并且返回SqlDataReader
                con.Open();
                SqlCommand cmd = con.CreateCommand();
                cmd.CommandText = Sql;
                SqlDataReader dr = cmd.ExecuteReader();
                return dr;
            }
            finally
            {
                //为了使返回的SqlDataReader可用，这里不能关闭连接
                //con.Close();
            }
        }
    }
}