//***********************************************************
//*名称：一个程序详细研究DataReader,同时显示Command用法
//*功能：演示DataReader的各项操作。
//*说明：如果需要文字说明请查看我的BLOG里面关于ado.net的文章
//*作者：雪冬寒
//*BOLG：http://blog.csdn.net/bineon
//***********************************************************
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;

class SqlReader
{
const string connStr = "data source=bineon;user id=sa;password=87345587;initial catalog=contract";
SqlConnection conn;


public SqlReader()
{
conn = new SqlConnection(connStr);
}

//**************************************
//*演示DataReader的两种取值方法
//**************************************
public void basicReader()
{
string sql="select * from friend";
SqlCommand cmd;
cmd = conn.CreateCommand();
cmd.CommandText = sql;
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while(reader.Read())
{
Console.WriteLine("No:{0}\tName:{1}\tPhoneNum:{2},\tAddress:{3}",reader.GetInt32(0).ToString(),reader.GetString(1),reader[2].ToString(),reader["Faddress"].ToString());
}
showSplit();
reader.Close();
conn.Close();
}

//**************************************
//*演示带参数查询的操作，使用SqlCilent
//**************************************
public void hasParamReader()
{
SqlCommand cmd;
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();
//当关闭reader的时候同时关闭数据库连接
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
while(reader.Read())
{
Console.WriteLine("Name:{0}\tPhoneNum:{1}\tAddress:{2}",reader.GetString(0),reader.GetString(1),reader.GetString(2));
}
showSplit();
//无需关闭conn,系统会自动调用这个方法来关闭conn的。
reader.Close();
}

//**************************************
//*演示带参数查询的操作,使用OleDb
//**************************************
public void hasOledbParamReader()
{
SqlCommand cmd;
cmd = conn.CreateCommand();
string sql = "select Fname,Fphone,Faddress from friend where Fid > ?";
string oledbConnStr = "Provider=sqloledb;" + connStr;
OleDbConnection oleConn = new OleDbConnection(oledbConnStr);
OleDbCommand oleCmd = new OleDbCommand(sql,oleConn);
oleCmd.Parameters.Add("nothing",15);
oleConn.Open();
OleDbDataReader oleReader = oleCmd.ExecuteReader();
while(oleReader.Read())
{
Console.WriteLine("Name:{0}\tPhoneNum:{1}\tAddress:{2}",oleReader.GetString(0),oleReader.GetString(1),oleReader.GetString(2));
}
showSplit();
oleReader.Close();
oleConn.Close();
}

//**************************************
//*演示存储过程的输出参数
//**************************************
public void outParamShow()
{
SqlCommand cmd;
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(Fnam