• 软件测试技术
  • 软件测试博客
  • 软件测试视频
  • 开源软件测试技术
  • 软件测试论坛
  • 软件测试沙龙
  • 软件测试资料下载
  • 软件测试杂志
  • 软件测试人才招聘
    暂时没有公告

字号: | 推荐给好友 上一篇 | 下一篇

从一个程序出发详细研究DataReader

发布: 2007-5-26 13:55 | 作者: 佚名 | 来源: 互连网 | 查看: 21次 | 进入软件测试论坛讨论

领测软件测试网
  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();
   }
  }

延伸阅读

文章来源于领测软件测试网 https://www.ltesting.net/


关于领测软件测试网 | 领测软件测试网合作伙伴 | 广告服务 | 投稿指南 | 联系我们 | 网站地图 | 友情链接
版权所有(C) 2003-2010 TestAge(领测软件测试网)|领测国际科技(北京)有限公司|软件测试工程师培训网 All Rights Reserved
北京市海淀区中关村南大街9号北京理工科技大厦1402室 京ICP备10010545号-5
技术支持和业务联系:info@testage.com.cn 电话:010-51297073

软件测试 | 领测国际ISTQBISTQB官网TMMiTMMi认证国际软件测试工程师认证领测软件测试网