通过实例学C#之SqlDataReader类

目录

简介

获取方式

常用属性

FieldCount

HasRows

IsClosed

Item[Int32]

Item[String]

常用方法

Close()

GetName(int32)

GetDataTypeName(int32)

GetFieldType(Int32)

GetOrdinal(String)

GetSchemaTable()

GetSqlValue(Int32)

IsDBNull(Int32)

Read()

---

简介

        可以读取数据库中的数据。

---

获取方式

        SqlDataReader没有构造函数，只能通过函数返回值获得，如SqlCommand的ExecuteReader()就能返回一个SqlDataReader实例。

static void Main(string[] args)
{
    string connectString = "server=.;database=student;User ID=lgh;Pwd=asd123";
    SqlConnection connection = new SqlConnection(connectString);

    string commandString = "select * from student.dbo.info";
    SqlCommand command = new SqlCommand(commandString, connection);

    connection.Open();

    SqlDataReader reader=command.ExecuteReader();

    connection.Close();

    Console.ReadKey();
}

---

常用属性

FieldCount

        执行指令后，如果选中了某一目标行，获取这一行具有多少列。

 static void Main(string[] args)
 {
     string connectString = "server=.;database=student;User ID=lgh;Pwd=asd123";
     SqlConnection connection = new SqlConnection(connectString);

     string commandString = "select * from student.dbo.info";

     SqlCommand command = new SqlCommand(commandString, connection);

     connection.Open();

     SqlDataReader reader=command.ExecuteReader();
     Console.WriteLine("选中行的列数是："+reader.FieldCount);

     connection.Close();

     Console.ReadKey();
 }

运行结果：
选中行的列数是：3

---

HasRows

        如果返回的结果包含一行或者多行，那么返回true，否则，返回false。

---

IsClosed

        当SqlDataReader实例被创建后，值为true；如果调用方法Close()以后，值为false。

---

Item[Int32]

        通过列序号来获取指定列的值。（注意，刚生成的SqlDataReader实例中无数据，需要使用方法Read()读取一行数据，如果有多行，则需要多次使用Read()方法。列序号从0开始计数。

static void Main(string[] args)
{
    string connectString = "server=.;database=student;User ID=lgh;Pwd=asd123";
    SqlConnection connection = new SqlConnection(connectString);

    string commandString = "select * from student.dbo.info";
    string commandString1 = "create database tiger";
    string commandString2 = @"select * from student.dbo.info where name='卢**'";

    SqlCommand command = new SqlCommand(commandString, connection);
    connection.Open();
    SqlDataReader reader=command.ExecuteReader();

    reader.Read();
    Console.WriteLine(reader[0]);
    Console.WriteLine(reader[1]);
    Console.WriteLine(reader[2]);

    reader.Close();
    connection.Close();
    Console.ReadKey();
}

运行结果：
卢**
35
engineer

---

Item[String]

        与Item[int32]类似，只是该属性是以数据库的列名称来获取值。

static void Main(string[] args)
{
    string connectString = "server=.;database=student;User ID=lgh;Pwd=asd123";
    SqlConnection connection = new SqlConnection(connectString);

    string commandString = "select * from student.dbo.info";
    string commandString1 = "create database tiger";
    string commandString2 = @"select * from student.dbo.info where name='卢**'";

    SqlCommand command = new SqlCommand(commandString, connection);
    connection.Open();
    SqlDataReader reader=command.ExecuteReader();

    reader.Read();
    Console.WriteLine(reader["name"]);
    Console.WriteLine(reader["age"]);
    Console.WriteLine(reader["job"]);

    reader.Close();
    connection.Close();
    Console.ReadKey();
}

运行结果：
卢**
35
engineer
​

---

常用方法

Close()

        关闭当前打开的SqlDataReader实例。

---

GetName(int32)

        获取指定列的名称。

static void Main(string[] args)
{
    string connectString = "server=.;database=student;User ID=lgh;Pwd=asd123";
    SqlConnection connection = new SqlConnection(connectString);

    string commandString = "select * from student.dbo.info";
    string commandString1 = "create database tiger";
    string commandString2 = @"select * from student.dbo.info where name='卢**'";

    SqlCommand command = new SqlCommand(commandString, connection);
    connection.Open();
    SqlDataReader reader=command.ExecuteReader();

    try 
    {
        Console.WriteLine(reader.GetName(0));
        Console.WriteLine(reader.GetName(1));
        Console.WriteLine(reader.GetName(2));
    } 
    catch (Exception e)
    {
        Console.WriteLine(e.Message);
    }

    reader.Close();
    connection.Close();

    Console.ReadKey();
}

运行结果：
name
age
job

---

GetDataTypeName(int32)

        获取指定列的在数据库中的数据类型。

static void Main(string[] args)
{
    string connectString = "server=.;database=student;User ID=lgh;Pwd=asd123";
    SqlConnection connection = new SqlConnection(connectString);

    string commandString = "select * from student.dbo.info";
    string commandString1 = "create database tiger";
    string commandString2 = @"select * from student.dbo.info where name='卢**'";

    SqlCommand command = new SqlCommand(commandString, connection);
    connection.Open();
    SqlDataReader reader=command.ExecuteReader();

    try 
    {
        Console.WriteLine(reader.GetDataTypeName(0));
    } 
    catch (Exception e)
    {
        Console.WriteLine(e.Message);
    }

    connection.Close();

    Console.ReadKey();
}

运行结果：
nchar

---

GetFieldType(Int32)

        获取指定列值作为当前程序读取的数据类型。

static void Main(string[] args)
{
    string connectString = "server=.;database=student;User ID=lgh;Pwd=asd123";
    SqlConnection connection = new SqlConnection(connectString);

    string commandString = "select * from student.dbo.info";
    string commandString1 = "create database tiger";
    string commandString2 = @"select * from student.dbo.info where name='卢**'";

    SqlCommand command = new SqlCommand(commandString, connection);
    connection.Open();
    SqlDataReader reader=command.ExecuteReader();

    try 
    {
        Console.WriteLine(reader.GetFieldType(0));
    } 
    catch (Exception e)
    {
        Console.WriteLine(e.Message);
    }

    connection.Close();

    Console.ReadKey();
}

运行结果：
System.String

---

GetOrdinal(String)

        给定列名，返回列序号。（从0开始）

static void Main(string[] args)
{
    string connectString = "server=.;database=student;User ID=lgh;Pwd=asd123";
    SqlConnection connection = new SqlConnection(connectString);

    string commandString = "select * from student.dbo.info";
    string commandString1 = "create database tiger";
    string commandString2 = @"select * from student.dbo.info where name='卢**'";

    SqlCommand command = new SqlCommand(commandString, connection);
    connection.Open();
    SqlDataReader reader=command.ExecuteReader();

    Console.WriteLine(reader.GetOrdinal("name"));
    Console.WriteLine(reader.GetOrdinal("job"));
    Console.WriteLine(reader.GetOrdinal("age"));

    connection.Close();
    Console.ReadKey();
}

运行结果：
0
2
1

---

GetSchemaTable()

        返回一个 DataTable，它描述 SqlDataReader的列元数据。

---

GetSqlValue(Int32)

        可以在不清楚目标数值类型的情况下获取目标值。

static void Main(string[] args)
{
    string connectString = "server=.;database=student;User ID=lgh;Pwd=asd123";
    SqlConnection connection = new SqlConnection(connectString);

    string commandString = "select * from student.dbo.info";
    string commandString1 = "create database tiger";
    string commandString2 = @"select * from student.dbo.info where name='卢**'";

    SqlCommand command = new SqlCommand(commandString, connection);
    connection.Open();
    SqlDataReader reader=command.ExecuteReader();

    reader.Read();
    Console.WriteLine(reader.GetSqlValue(0));
    Console.WriteLine(reader.GetSqlValue(1));
    Console.WriteLine(reader.GetSqlValue(2));

    reader.Close();
    connection.Close();
    Console.ReadKey();
}

运行结果：
卢**
35
engineer

---

IsDBNull(Int32)

        获取目标值是否为null，是，返回true，否则，返回false。

static void Main(string[] args)
{
    string connectString = "server=.;database=student;User ID=lgh;Pwd=asd123";
    SqlConnection connection = new SqlConnection(connectString);

    string commandString = "select * from student.dbo.info";
    string commandString1 = "create database tiger";
    string commandString2 = @"select * from student.dbo.info where name='卢**'";

    SqlCommand command = new SqlCommand(commandString2, connection);
    connection.Open();
    SqlDataReader reader=command.ExecuteReader();

    reader.Read();
    Console.WriteLine(reader.GetSqlValue(0));
    Console.WriteLine(reader.GetSqlValue(1));
    Console.WriteLine(reader.GetSqlValue(2));
    Console.WriteLine(reader.IsDBNull(2));

    reader.Close();
    connection.Close();
    Console.ReadKey();
}

运行结果：
卢**
31
Null
True

---

Read()

        让 SqlDataReader实例前进到下一条记录，刚创建sqlDataReader时无数据，需要执行此函数才有数据。