Qihangnet's Blog! - 一个程序详细研究DataReader

Wednesday, March 23, 2005

//***********************************************************

//*名称：一个程序详细研究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(Fname + "  " + Fphone);
  conn.Close();
  showSplit();
}

//**************************************

//*演示读取多个无关记录集

//**************************************

public void multiResult()

  SqlCommand cmd;
  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();
  showSplit();
}

//**************************************

//*使用DataReader获得数据库模式信息

//**************************************

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();
     showSplit();
}

//**************************************

//*从数据库读取二进制数据的代码段

//*该代码段只是读取二进制的片断，不是

//*整个程序，所以不能执行，你可以把它

//*集成到你的WinForm项目里面。

//**************************************

public void getBinary()

  /*
  System.IO.MemoryStream stream = new System.IO.MemoryStream();
  System.IO.BinaryWriter writer = new System.IO.BinaryWriter(stream);
  int BufferSize = 1024;
  byte[] Buffer = new Byte[BufferSize];
  long Offset = 0;
  long BytesRead = 0;
  do

   BytesRead = reader.GetBytes(2,Offset,Buffer,0,BufferSize);
   writer.Writer(Buffer,0,(int)BytesRead);
   writer.Flush();
   Offset += BytesRead;
  }
  while(BytesRead == BufferSize);
  */
}

//添加输出分隔

private void showSplit()

  Console.WriteLine("\n********************************************************************\n");
}

public static void Main(string [] args)

  SqlReader sqlreader = new SqlReader();

  sqlreader.basicReader();

  sqlreader.hasParamReader();

  sqlreader.hasOledbParamReader();

  sqlreader.outParamShow();

  sqlreader.multiResult();

  sqlreader.getSchema();
}
}

CSharp | DATABASE

posted on Wednesday, March 23, 2005 5:08:34 PM (China Standard Time, UTC+08:00)