数据库操作方法示例－－DataReader

详细研究DataReader,同时显示Command用法.

功能：演示DataReader的各项操作。

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

class SqlReader
{
 const string connStr = "data source=localhost;user id=sa;password=123456;initial catalog=Test";
 SqlConnection conn;
 
 public SqlReader()
 {
  conn = new SqlConnection(connStr);
 }
 

1.演示DataReader的两种取值方法

 public void basicReader()
 {
  string sql="select * from TableName";
  SqlCommand cmd =conn.CreateCommand();
  cmd.CommandText = sql;
  conn.Open();
  SqlDataReader dr= cmd.ExecuteReader();
  while(dr.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();
  dr.Close();
  conn.Close();
 }
 

2.演示带参数查询的操作，使用SqlCilent

 public void hasParamReader()
 {
  SqlCommand cmd;
  cmd = conn.CreateCommand();
  string sql = "select * from TableName where FieldID > @FieldID ";
  cmd.CommandText = sql;
  SqlParameter param = new SqlParameter("@FieldID ",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();
 }
 

 3.演示存储过程的输出参数
 public void outParamShow()
 {
  SqlCommand cmd;
  cmd = conn.CreateCommand();
  cmd.CommandText = "GetInfo";
  cmd.CommandType = CommandType.StoredProcedure;
  SqlParameter param = cmd.Parameters.Add("@FieldID ",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();
 }
 
4.演示读取多个无关记录集
 public void multiResult()
 {
  SqlCommand cmd;
  cmd = conn.CreateCommand();
  string sql1= "select Fname from TableName";
  string sql2 = "select Fphone from TableName";
  cmd.CommandText = sql1 + ";" + sql2;
  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();
 }
 
5.使用DataReader获得数据库模式信息
 public void getSchema()
 {
  SqlCommand cmd;
  cmd = conn.CreateCommand();
  string sql = "select *  from TableName";
     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();
 }
 

6从数据库读取二进制数据的代码段,该代码段只是读取二进制的片断，不是整个程序，所以不能执行，可以把它集成到你的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();
 }
}