一個程序詳細研究DataReader

　　//***********************************************************
//*名稱一個程序詳細研究DataReader同時顯示Command用法
//*功能演示DataReader的各項操作
//*說明如果需要文字說明請查看我的BLOG裡面關於的文章
//*作者雪冬寒
//*BOLG
//***********************************************************
using System;
using SystemData;
using SystemDataSqlClient;
using SystemDataOleDb;

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

　　public SqlReader()
 {
  conn = new SqlConnection(connStr);
 }
 
 //**************************************
 //*演示DataReader的兩種取值方法
 //**************************************
 public void basicReader()
 {
  string sql=select * from friend;
  SqlCommand cmd;
  cmd = connCreateCommand();
  cmdCommandText = sql;
  connOpen();
  SqlDataReader reader = cmdExecuteReader();
  while(readerRead())
  {
   ConsoleWriteLine(No:{}\tName:{}\tPhoneNum:{}\tAddress:{}readerGetInt()ToString()readerGetString()reader[]ToString()reader[Faddress]ToString());
  }
  showSplit();
  readerClose();
  connClose();
 }
 
 //**************************************
 //*演示帶參數查詢的操作使用SqlCilent
 //**************************************
 public void hasParamReader()
 {
  SqlCommand cmd;
  cmd = connCreateCommand();
  string sql = select FnameFphoneFaddress from friend where Fid > @Fid;
  cmdCommandText = sql;
  SqlParameter param = new SqlParameter(@FidSqlDbTypeInt);
  paramValue = ;
  cmdParametersAdd(param);
  connOpen();
  //當關閉reader的時候同時關閉數據庫連接
  SqlDataReader reader = cmdExecuteReader(CommandBehaviorCloseConnection);
  while(readerRead())
  {
   ConsoleWriteLine(Name:{}\tPhoneNum:{}\tAddress:{}readerGetString()readerGetString()readerGetString());
  }
  showSplit();
  //無需關閉conn系統會自動調用這個方法來關閉conn的
  readerClose(); 
 }
 
 //**************************************
 //*演示帶參數查詢的操作使用OleDb
 //**************************************
 public void hasOledbParamReader()
 {
  SqlCommand cmd;
  cmd = connCreateCommand();
  string sql = select FnameFphoneFaddress from friend where Fid > ?;
  string oledbConnStr = Provider=sqloledb; + connStr;
  OleDbConnection oleConn = new OleDbConnection(oledbConnStr);
  OleDbCommand oleCmd = new OleDbCommand(sqloleConn);
  oleCmdParametersAdd(nothing);
  oleConnOpen();
  OleDbDataReader oleReader = oleCmdExecuteReader();
  while(oleReaderRead())
  {
   ConsoleWriteLine(Name:{}\tPhoneNum:{}\tAddress:{}oleReaderGetString()oleReaderGetString()oleReaderGetString());
  }
  showSplit();
  oleReaderClose();
  oleConnClose();
 }
 
 //**************************************
 //*演示存儲過程的輸出參數
 //**************************************
 public void outParamShow()
 {
  SqlCommand cmd;
  cmd = connCreateCommand();
  cmdCommandText = GetInfo;
  cmdCommandType = CommandTypeStoredProcedure;
  SqlParameter param = cmdParametersAdd(@Fid);
  param = cmdParametersAdd(@FnameSqlDbTypeVarChar);
  paramDirection = ParameterDirectionOutput;
  param = cmdParametersAdd(@FphoneSqlDbTypeVarChar);
  paramDirection = ParameterDirectionOutput;
  connOpen();
  cmdExecuteNonQuery();
  string Fname = cmdParameters[@Fname]ValueToString();
  string Fphone = cmdParameters[@Fphone]ValueToString();
  ConsoleWriteLine(Fname +    + Fphone);
  connClose();
  showSplit();
 }
 
 //**************************************
 //*演示讀取多個無關記錄集
 //************************************** 
 public void multiResult()
 {
  SqlCommand cmd;
  cmd = connCreateCommand();
  string sqla = select Fname from friend;
  string sqlb = select Fphone from friend;
  cmdCommandText = sqla + ; + sqlb;
  connOpen();
  SqlDataReader reader= cmdExecuteReader();
  int i = ;
  do
  {
   ConsoleWriteLine(第 + iToString() + 個記錄集內容如下\n);
   while(readerRead())
   {
    ConsoleWriteLine(reader[]ToString() + \t);
   }
   i++;
  }while(readerNextResult()); //NextResult()移動到下一個記錄集
  readerClose();
  connClose();
  showSplit();
 }
 
 //**************************************
 //*使用DataReader獲得數據庫模式信息
 //************************************** 
 public void getSchema()
 {
  SqlCommand cmd;
  cmd = connCreateCommand();
  string sql = select FidFnameFphone from friend;
     cmdCommandText = sql;
     connOpen();
     SqlDataReader reader = cmdExecuteReader();
     DataTable SchemaTable = readerGetSchemaTable();
     
     DataRowCollection SchemaColumns = SchemaTableRows;
     DataColumnCollection SchemaProps = SchemaTableColumns;  
     foreach(DataRow SchemaColumn in SchemaColumns)
     {
      foreach(DataColumn SchemaColumnProp in SchemaProps)
      {
       ConsoleWriteLine(SchemaColumnPropColumnName + = + SchemaColumn[SchemaColumnPropColumnName]ToString());
      }
     }
     readerClose();
     connClose();
     showSplit();
 }
 
 //**************************************
 //*從數據庫讀取二進制數據的代碼段
 //*該代碼段只是讀取二進制的片斷不是
 //*整個程序所以不能執行你可以把它
 //*集成到你的WinForm項目裡面
 //************************************** 
 public void getBinary()
 {
  /*
  SystemIOMemoryStream stream = new SystemIOMemoryStream();
  SystemIOBinaryWriter writer = new SystemIOBinaryWriter(stream);  
  int BufferSize = ;
  byte[] Buffer = new Byte[BufferSize];
  long Offset = ;
  long BytesRead = ;
  do
  {
   BytesRead = readerGetBytes(OffsetBufferBufferSize);
   writerWriter(Buffer(int)BytesRead);
   writerFlush();
   Offset += BytesRead;
  }
  while(BytesRead == BufferSize);
  */
 }
 
 //添加輸出分隔
 private void showSplit()
 {
  ConsoleWriteLine(\n********************************************************************\n);
 }
 
 public static void Main(string [] args)
 {
  SqlReader sqlreader = new SqlReader();
  
  sqlreaderbasicReader();
  
  sqlreaderhasParamReader();
  
  sqlreaderhasOledbParamReader();
  
  sqlreaderoutParamShow();
  
  sqlreadermultiResult();
  
  sqlreadergetSchema();
 } 
}

From:http://tw.wingwit.com/Article/program/net/201311/11352.html