以下内容演示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();
? 便宜香港vps 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 24877286
