SqlDataReader及其本质

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Data;

using System.Data.SqlClient;

namespace 查询表值结果集

{

    class Program

    {

        static void Main(string[] args)

        {

            string connectionString = "server=.;database=Test;integrated security=true";

            using (SqlConnection connection = new SqlConnection(connectionString))

            {

                connection.Open();

                using (SqlCommand cmd = new SqlCommand())

                {

                    string sql = "select * from TbClass;select * from TbStudent";

                    cmd.CommandText = sql;

                    cmd.Connection = connection;

                    //ExecuteReader（）用来执行一条查询结果为表值结果集的select语句

                    //它返回一个SqlDataReader类型的对象

                    //SqlDataReader不直接存储数据，它只是一个指挥棒，它的作用与foreach那个指针

                    //非常类似。

                    //2.SqlDataReader只能从前往后读，不能往回退

                    //3.由于数据并不是真正读到了.NET程序的内存空间里，所有，通过SqlDataReader读取数据时

                    //必须保证SqlDataReader对象一直处于连接状态

                    //4.SqlDataReader只能读取数据，不能修改数据

                    //结论：

                    //ExcuteNoQuery(),ExcuteScalar(),ExcuteReader()

                    //1.其实这三个方法可以执行任何sql语句

                    //2.只是它们的返回值不同，这就决定了ExcuteNoQuery()更适合执行增，删，该

                    //ExcuteScalar()更适合执行返回一个值得select语句

                    //ExcuteReader()更适合执行返回表值结果的select语句

                    SqlDataReader reader = cmd.ExecuteReader();

                    #region MyRegion

                    //if (reader.HasRows)//判断sql语句执行后的结果集中有没有记录

                    //{

                    //    试图往外读一行数据

                    //    while (reader.Read())

                    //    {

                    //        1.使用字段名作为索引

                    //        注意：不能使用没有在sql语句中出现的字段名

                    //        建议尽量少用这种方式，效率最低

                    //         Console.Write(reader["clsId"].ToString()+"\t");

                    //        Console.Write(reader["clsName"].ToString()+"\t");

                    //        Console.Write(reader["clsTeacher"].ToString()+"\t");

                    //        2.使用索引

                    //         Console.Write(reader[0].ToString()+"\t");

                    //        Console.Write(reader[1].ToString()+"\t");

                    //        Console.Write(reader[2].ToString()+"\t");

                    //        //3.

                    //        Console.Write(reader.GetValue(0).ToString() + "\t");

                    //        Console.Write(reader.GetInt32(0).ToString() + "\t");

                    //        Console.Write(reader.GetValue(1).ToString() + "\t");

                    //        Console.Write(reader.GetValue(2).ToString() + "\t");

                    //        4.假如根本就不知道查询的字段列表情况

                    //        for (int i = 0; i < reader.FieldCount; i++)

                    //        {

                    //            Console.Write(reader.GetValue(i).ToString() + "\t");

                    //        }

                    //            Console.WriteLine();

                    //    }

                    //}  

                    #endregion

                    do

                    {

                        if (reader.HasRows)

                        {

                            while (reader.Read())

                            {

                                for (int i = 0; i < reader.FieldCount; i++)

                                {

                                    Console.Write(reader.GetValue(i).ToString()+"\t");

                                }

                                Console.WriteLine();

                            }

                        }

                    } while (reader.NextResult());

                }                        

            }

        }

    }

}