SqlDataReader类及其代码示例

 

若要创建 SqlDataReader，必须调用 SqlCommand 对象的 ExecuteReader 方法，而不要直接使用构造函数。

（SqlDataReader reader = comm.ExecuteReader();）

在使用 SqlDataReader 时，关联的 SqlConnection 正忙于为 SqlDataReader 服务，对 SqlConnection 无法执行任何其他操作，只能将其关闭。除非调用 SqlDataReader 的 Close 方法，否则会一直处于此状态。例如，在调用 Close 之前，无法检索输出参数。

SqlDataReader 的用户可能会看到在读取数据时另一进程或线程对结果集所做的更改。但是，确切的行为与执行时间有关。

当 SqlDataReader 关闭后，只能调用 IsClosed 和 RecordsAffected 属性。尽管当 SqlDataReader 存在时可以访问 RecordsAffected 属性，但是请始终在返回 RecordsAffected 的值之前调用 Close，以保证返回精确的值。

 

DataReader 对象提供一种向前的，行流的，读取数据的方式，它具有3个特点：

1：只能读取数据， 不能对数据库的记录进行创建，修改和删除

2：是一种向前的读取数据的方式， 不能再次回头读取上一回记录。

3：不能在IIS的内容中保持数据，直接传递数据到显示对象。

通过列名称，索引以及Get（）方法可以从DataReader对象中获取数据或值。

string FiledName; //列名称

int   FiledIndex ；   //列索引

DataReader dr = SqlMyCommand.ExecuteReader();//创建DataReader对象

object filedValue = dr[FiledName];

object fieldValue = dr[FieldIndex];

object fieldValue = dr.GetString(1);

下面是一些iSqlDataReader相关的示例代码

private void btnStrong_Click(object sender, EventArgs e)
        {
            //string connstr = "Data Source = localhost; Initial Catalog = NorthWind; User ID = sa; Password = sapass";
            SqlConnection conn = new SqlConnection(ConnString);

            SqlCommand comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandText = "Select * from Training";

            conn.Open();
            SqlDataReader reader = comm.ExecuteReader();

            StringBuilder strResult = new StringBuilder();

            strResult.AppendFormat("{0}      {1}      {2}      {3}/r",
                    reader.GetName(0),   reader.GetName(1), reader.GetName(2).ToString(), reader.GetName(3)); 
            while (reader.Read())
            {
                //strResult.AppendFormat("{0}      {1}      {2}      {3}/r", reader.GetString(0), reader.GetString(1), reader.GetInt32(2).ToString(), reader.GetString(3));
                strResult.AppendFormat("{0}      {1}      {2}      {3}/r",
                    (string)reader["trainingCode"], reader["trainingName"], reader.GetInt32(2).ToString(), reader.GetString(3));
            }

            rchResult.Text = strResult.ToString();

            reader.Close();
            conn.Close();
        }

        private void btnSchema_Click(object sender, EventArgs e)
        {
            //string connstr = "Data Source = localhost; Initial Catalog = NorthWind; User ID = sa; Password = sapass";
            SqlConnection conn = new SqlConnection(ConnString);

            SqlCommand comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandText = "Select * from Customers";

            conn.Open();
            SqlDataReader reader = comm.ExecuteReader();

            StringBuilder strResult = new StringBuilder();

            DataTable tblschema = reader.GetSchemaTable();

            foreach (DataRow row in tblschema.Rows)
            {
                foreach (DataColumn col in tblschema.Columns)
                {
                    strResult.AppendFormat("{0} = {1}   ", col.ColumnName, row[col]);// 获取架构信息 注意2个foreach 的参数
                }
                strResult.Append("/r/r/r");
            }

            rchResult.Text = strResult.ToString();

            reader.Close();
            conn.Close();

        }

        private void btnGetValues_Click(object sender, EventArgs e)
        {
            //string connstr = "Data Source = localhost; Initial Catalog = NorthWind; User ID = sa; Password = sapass";
            SqlConnection conn = new SqlConnection(ConnString);

            SqlCommand comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandText = "Select * from Training";

            conn.Open();
            SqlDataReader reader = comm.ExecuteReader();

            Object[] values = new Object[reader.FieldCount];

            StringBuilder strResult = new StringBuilder();

            strResult.AppendFormat("{0}      {1}      {2}      {3}/r",
                    reader.GetName(0), reader.GetName(1), reader.GetName(2).ToString(), reader.GetName(3));

            while (reader.Read())
            {
                reader.GetValues(values);
                strResult.AppendFormat("{0}      {1}      {2}      {3}/r",
                   (string)values[0], values[1], values[2], values[3]);
            }

            rchResult.Text = strResult.ToString();

            reader.Close();
            conn.Close();

        }

        private void btnIndexer_Click(object sender, EventArgs e)
        {
            //string connstr = "Data Source = localhost; Initial Catalog = NorthWind; User ID = sa; Password = sapass";
            SqlConnection conn = new SqlConnection(ConnString);

            SqlCommand comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandText = "Select * from Training";

            conn.Open();
            SqlDataReader reader = comm.ExecuteReader();
           
            StringBuilder strResult = new StringBuilder();

            reader.Read();
            for (int i=0;i < reader.FieldCount; i++)
            {
                // column name
             String name = reader.GetName(i);
           
             // get name and value by ordinal
             strResult.AppendFormat("column {0}: {1}/r",
                        reader.GetName(i), reader[i]);
                       
                // get ordinal and value by name           
             strResult.AppendFormat("column {0}: {1}/r",
                        reader.GetOrdinal(name), reader[name]);           
            }     

            rchResult.Text = strResult.ToString();

            reader.Close();
            conn.Close();

        }

        private void btnMultiResult_Click(object sender, EventArgs e)
        {
            //string connstr = "Data Source = localhost; Initial Catalog = NorthWind; User ID = sa; Password = sapass";
            SqlConnection conn = new SqlConnection(ConnString);

            SqlCommand comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandText = "Select * from Training; Select EmployeeID, LastName, FirstName from Employees";
            conn.Open();
            SqlDataReader reader = comm.ExecuteReader();
            StringBuilder strResult = new StringBuilder();

            strResult.AppendFormat("/r/r{0}      {1}      {2}/r",
                        reader.GetName(0), reader.GetName(1), reader.GetName(2));

            if (reader.NextResult())
            {
                while (reader.Read())
                {
                    strResult.AppendFormat("{0}      {1}      {2}/r",
                        reader["EmployeeID"], reader["LastName"], reader["FirstName"]);
                }
            }

            rchResult.Text = strResult.ToString();

            reader.Close();
            conn.Close();

        }