目录
简介
可以读取数据库中的数据。
获取方式
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时无数据,需要执行此函数才有数据。