DataReader方法详解

最新推荐文章于 2020-06-24 02:16:17 发布

ASPNETCHENGXU

最新推荐文章于 2020-06-24 02:16:17 发布

阅读量1.3k

点赞数

CC 4.0 BY-SA版权

文章标签： string sql cmd 数据库 database server

本文链接：https://blog.youkuaiyun.com/aspnetchengxu/article/details/4105845

本文通过实例展示了如何使用 C# 中的 SqlDataReader 进行数据库操作，包括基本的数据读取、带参数查询、存储过程输出参数、读取多个记录集及获取数据库模式信息。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

以下内容演示DataReader的各项操作：

using System;
using System.Data;
using System.Data.SqlClient;

//建立Sql连接

//可以将连接字符串定义为const，以便测试

string strConn=@"server=suo/sqlexpress;uid=suo;pwd=***;database=test";

SqlConnection conn=new SqlConnection(strConn);

//首先是DataReader的两种取值方法

public void GetValue()

{

string sql="SELECT * FROM friend";

SqlCommand cmd=new SqlCommand(sql,conn);

SqlDataReader reader=cmd.ExecuteReader();

while(reader.Read())

{

//一种为reader.GetString(index)或者reader[index].ToString();另一种为reader["字段名"].ToString()

Console.WriteLine("No:{0}/tName:{1}/tPhoneNum:{2},/tAddress:{3}",reader.GetInt32(0).ToString(),reader.GetString(1),reader[2].ToString(),reader["Faddress"].ToString());
}
reader.Close();
conn.Close();

}

//带参数查询的操作

public void HasParam()
{
SqlCommand cmd = conn.CreateCommand();
string sql = "SELECT Fname,Fphone,Faddress FROM friend WHERE Fid > @Fid";
cmd.CommandText = sql;
SqlParameter param = new SqlParameter("@Fid",SqlDbType.Int,4);
param.Value = 15;
cmd.Parameters.Add(param);
conn.Open();
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);//当关闭reader的时候同时关闭数据库连接
while(reader.Read())
{
Console.WriteLine("Name:{0}/tPhoneNum:{1}/tAddress:{2}",reader.GetString(0),reader.GetString(1),reader.GetString(2));
}
reader.Close(); //此时这里无需关闭conn,系统会自动调用这个方法来关闭conn连接
}

//存储过程的输出参数
public void OutputParam()
{
SqlCommand cmd= conn.CreateCommand();
cmd.CommandText = "GetInfo";
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter param = cmd.Parameters.Add("@Fid",16);
param = cmd.Parameters.Add("@Fname",SqlDbType.VarChar,8);
param.Direction = ParameterDirection.Output;
param = cmd.Parameters.Add("@Fphone",SqlDbType.VarChar,8);
param.Direction = ParameterDirection.Output;
conn.Open();
cmd.ExecuteNonQuery();
string Fname = cmd.Parameters["@Fname"].Value.ToString();
string Fphone = cmd.Parameters["@Fphone"].Value.ToString();
Console.WriteLine(Fname + " " + Fphone);
conn.Close();
}

//读取多个无关记录集

public void MultiResult()
{
SqlCommand cmd=conn.CreateCommand();
string sqla = "SELECT Fname FROM friend";
string sqlb = "SELECT Fphone FROM friend";
cmd.CommandText = sqla + ";" + sqlb;
conn.Open();
SqlDataReader reader= cmd.ExecuteReader();
int i = 1;
do
{
   Console.WriteLine("第" + i.ToString() + "个记录集内容如下：/n");
   while(reader.Read())
   {
    Console.WriteLine(reader[0].ToString() + "/t");
   }
   i++;
}

while(reader.NextResult()); //NextResult()移动到下一个记录集
reader.Close();
conn.Close();
}

//获取数据库模式信息

public void GetSchema()
{
SqlCommand cmd;
cmd = conn.CreateCommand();
string sql = "SELECT Fid,Fname,Fphone FROM friend";
  cmd.CommandText = sql;
conn.Open();
  SqlDataReader reader = cmd.ExecuteReader();
  DataTable SchemaTable = reader.GetSchemaTable();
  DataRowCollection SchemaColumns = SchemaTable.Rows;
  DataColumnCollection SchemaProps = SchemaTable.Columns;
  foreach(DataRow SchemaColumn in SchemaColumns)
  {
           foreach(DataColumn SchemaColumnProp in SchemaProps)
           {
                   Console.WriteLine(SchemaColumnProp.ColumnName + "=" + SchemaColumn[SchemaColumnProp.ColumnName].ToString());
           }
}

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

}

声明：以上内容非本人原创，详情请查阅《C#数据库入门经典（第二版）》作者：James Huddleston