DataReader 程序性能优化

using System.Data.SqlClient;

namespace DataReaderTypedAccessors
{
    class Program
    {
        static void Main(string[] args)
        {
            int loops = 100;     //本示例循环的次数
 



              int contactID;
            string firstName;
            string middleName = null;
            string lastName;


            int startTick = 0;
            int elapsedTick;
            

            string sqlConnectString = "Data Source=(local);Integrated security=SSPI;Initial Catalog=AdventureWorks;";


            string sqlSelect = "SELECT ContactID, FirstName, MiddleName, LastName FROM Person.Contact";



            Console.WriteLine("---DataReader column value access timing test, {0} iterations---\n", loops);


            SqlConnection connection = new SqlConnection(sqlConnectString);
            SqlCommand command = new SqlCommand(sqlSelect, connection);
            connection.Open();



            elapsedTick = 0;
            for (int i = 0; i < loops; i++)
            {
                // Create the DataReader and retrieve all fields for each
                // record using a typed accessor with a column ordinal
                using (SqlDataReader dr = command.ExecuteReader())
                {
                    startTick = Environment.TickCount;
                    while (dr.Read())
                    {
                        contactID = dr.GetInt32(0);
                        firstName = dr.GetString(1);
                        middleName = dr.IsDBNull(2) ? null : dr.GetString(2);
                        lastName = dr.GetString(3);
                    }

                    elapsedTick += Environment.TickCount - startTick;
                }
            }
            Console.WriteLine("Typed accessor (写法 3): Ticks = {0}", elapsedTick);



            elapsedTick = 0;
            for (int i = 0; i < loops; i++)
            {
                // Create the DataReader and retrieve all fields for each
                // record using a column ordinal
                using (SqlDataReader dr = command.ExecuteReader())
                {
                    startTick = Environment.TickCount;
                    while (dr.Read())
                    {
                        contactID = Convert.ToInt32(dr[0]);
                        firstName = Convert.ToString(dr[1]);
                        middleName = Convert.ToString(dr[2]);
                        lastName = Convert.ToString(dr[3]);
                    }

                    elapsedTick += Environment.TickCount - startTick;
                }
            }
            Console.WriteLine("Column ordinal (写法 1): Ticks = {0}", elapsedTick);



            elapsedTick = 0;
            for (int i = 0; i < loops; i++)
            {
                // Create the DataReader and retrieve all fields for each
                // record using a column field name
                using (SqlDataReader dr = command.ExecuteReader())
                {
                    startTick = Environment.TickCount;
                    while (dr.Read())
                    {
                        contactID = Convert.ToInt32(dr["ContactID"]);
                        firstName = Convert.ToString(dr["FirstName"]);
                        middleName = Convert.ToString(dr["MiddleName"]);
                        lastName = Convert.ToString(dr["LastName"]);
                    }


                    elapsedTick += Environment.TickCount - startTick;
                }
            }
            Console.WriteLine("Column name (写法 2): Ticks = {0}", elapsedTick);



            Console.WriteLine("\nPress any key to continue.");
            Console.ReadKey();
        }
    }
}

using System.Data.SqlClient;


namespace DataReaderColumnOrdinals
{
    class Program
    {
        static void Main(string[] args)
        {
            int coContactID, coFirstName, coLastName;


            string sqlConnectString = "Data Source=(local);Integrated security=SSPI;Initial Catalog=AdventureWorks;";


            string sqlSelect = "SELECT TOP 5 * FROM Person.Contact";



            SqlConnection connection = new SqlConnection(sqlConnectString);
            SqlCommand command = new SqlCommand(sqlSelect, connection);
            connection.Open();



            //创建 DataReader 并取得多个字段的「列序号 (ordinals)」。
              //应避免在循环中调用 GetOrdinal 方法，以免影响性能
              using (SqlDataReader drSchema =  command.ExecuteReader(CommandBehavior.SchemaOnly))
            {
                //SqlDataReader.GetOrdinal 方法: 在给定「列 (字段)」名称的情况下，获取各自对应的列序号
                  coContactID = drSchema.GetOrdinal("ContactID");
                coFirstName = drSchema.GetOrdinal("FirstName");
                coLastName = drSchema.GetOrdinal("LastName");                
            }



            //输出字段序列号 (column ordinals)
            Console.WriteLine("--- 字段在表中的序列号 Column ordinals ---");
            Console.WriteLine("ContactID = {0}, FirstName = {1}, LastName = {2}",
                coContactID, coFirstName, coLastName);




            Console.WriteLine("\n--- Index-based accessor (写法 1) ---");


            using (SqlDataReader dr = command.ExecuteReader())
            {
                while (dr.Read())
                {
                    // Output fields using the column ordinals as accessors
                    Console.WriteLine("{0}\t{1}, {2}",
                        dr[coContactID], dr[coLastName], dr[coFirstName]);
                }
            }




            Console.WriteLine("\n--- .NET typed accessor (写法 3) ---");


            using (SqlDataReader dr = command.ExecuteReader())
            {
                while (dr.Read())
                {
                    // Output fields using the column ordinals as accessors
                    Console.WriteLine("{0}\t{1}, {2}",
                        dr.IsDBNull(coContactID) ?
                            "NULL" : dr.GetInt32(coContactID).ToString(),
                        dr.IsDBNull(coLastName) ?
                            "NULL" : dr.GetString(coLastName),
                        dr.IsDBNull(coFirstName) ?
                            "NULL" : dr.GetString(coFirstName));
                }
            }




            Console.WriteLine("\n--- Provider-specific typed accessor (写法 4) ---");


            using (SqlDataReader dr = command.ExecuteReader())
            {
                while (dr.Read())
                {
                    // Output fields using the column ordinals as accessors
                    //GetSqlString() 方法后面，不用再加上 ToString() 转型
                    Console.WriteLine("{0}\t{1}, {2}",
                         dr.IsDBNull(coContactID) ?
                            "NULL" : dr.GetSqlInt32(coContactID).ToString(),
                         dr.IsDBNull(coLastName) ?
                            "NULL" : dr.GetSqlString(coLastName),
                         dr.IsDBNull(coFirstName) ?
                            "NULL" : dr.GetSqlString(coFirstName));
                }
            }



            connection.Close();



            Console.WriteLine("\nPress any key to continue.");
            Console.ReadKey();
        }
    }
}

using (SqlConnection conn = new SqlConnection(strConnString))
{
    conn.Open();
    using (SqlCommand cmd = new SqlCommand())
    {
        cmd.Connection = conn;
        cmd.CommandText = "SELECT * FROM Employees";

        using (SqlDataReader dr = cmd.ExecuteReader())
        {
            Object[] objArray;  //包含目前数据行中，所有字段的值

              while (dr.Read())
            {
                objArray = new Object[dr.FieldCount];   //dr.FieldCount 表示字段的数量，以此决定数组的大小
                dr.GetValues(objArray);     //将整条记录存储在数组中

                //从数组中，第一、第二个字段的数据取出
                DropDownList1.Items.Add(objArray[0].ToString() + ", " + objArray[1].ToString());
            }
        }
    }
}