100165602.htm
来自「C#高级编程(第三版),顶死你们。。 。up」· HTM 代码 · 共 153 行 · 第 1/2 页
HTM
153 行
<p class="2" style="MARGIN-TOP: 0cm; MARGIN-LEFT: 21.45pt; MARGIN-RIGHT: 0cm; FTEL: 18.45pt"><span lang="EN-GB">}</span></p>
<p class="MsoNormal"><span style="FONT-FAMILY: 宋体">前面的代码包含其他章节介绍的许多熟悉的</span><span lang="EN-US">C#</span><span style="FONT-FAMILY: 宋体">功能。要编译该示例，使用下面的命令：</span></p>
<p class="a6" style="MARGIN-TOP: 8.15pt; MARGIN-LEFT: 21.45pt; MARGIN-RIGHT: 0cm; FTEL: 18.45pt"><strong><span lang="EN-GB">csc /t:exe /debug+ DataReaderExample.cs /r:System.Data.dll</span></strong></p>
<p class="MsoNormal"><span style="FONT-FAMILY: 宋体">在前面的示例中，下面的代码根据源连接字符串，创建一个新</span><span lang="EN-US">OLE DB.NET</span><span style="FONT-FAMILY: 宋体">数据库连接：</span></p>
<p class="a6" style="MARGIN-TOP: 8.15pt; BACKGROUND: #f2f2f2; MARGIN-LEFT: 21.45pt; MARGIN-RIGHT: 0cm; FTEL: 18.45pt"><span lang="EN-GB"> OleDbConnection conn = new OleDbConnection(source);</span></p>
<p class="a6" style="BACKGROUND: #f2f2f2; MARGIN-LEFT: 21.45pt; FTEL: 18.45pt"><span lang="EN-GB"> conn.Open();</span></p>
<p class="a6" style="MARGIN-TOP: 0cm; BACKGROUND: #f2f2f2; MARGIN-LEFT: 21.45pt; MARGIN-RIGHT: 0cm; FTEL: 18.45pt"><span lang="EN-GB"> OleDbCommand cmd = new OleDbCommand(select, conn);</span></p>
<p class="MsoNormal"><span style="FONT-FAMILY: 宋体">第三行根据特定的</span><span lang="EN-US">Select</span><span style="FONT-FAMILY: 宋体">语句创建一个新</span><span lang="EN-US">OleDbCommand</span><span style="FONT-FAMILY: 宋体">对象</span><span style="FONT-FAMILY: 宋体">，</span><span style="FONT-FAMILY: 宋体">以及执行命令时所使用的数据库连接。当有一个有效的命令时，就需要执行它，返回一个初始化了的</span><span lang="EN-US">OleDbDataReader</span><span style="FONT-FAMILY: 宋体">：</span></p>
<p class="a6" style="MARGIN-TOP: 8.15pt; MARGIN-LEFT: 21.45pt; MARGIN-RIGHT: 0cm; FTEL: 18.45pt"><a ftel="OleDbDataReader1"><span lang="EN-GB"> OleDbDataReader aReader = cmd.ExecuteReader();</span></a></p>
<p class="MsoNormal"><span lang="EN-US">OleDbDataReader</span><span style="FONT-FAMILY: 宋体">是一个只向前的连接游标</span><span style="FONT-FAMILY: 宋体">，</span><span style="FONT-FAMILY: 宋体">即只能沿着一个方向遍历记录</span><span style="FONT-FAMILY: 宋体">，</span><span style="FONT-FAMILY: 宋体">而使用的数据库连接一直打开</span><span style="FONT-FAMILY: 宋体">，</span><span style="FONT-FAMILY: 宋体">直到关闭</span><span lang="EN-US">DataReader</span><span style="FONT-FAMILY: 宋体">为止。</span></p>
<p class="a3" style="MARGIN-TOP: 8.15pt; FTEL: 21.45pt"><span style="FONT-FAMILY: 黑体">提示：</span></p>
<p class="a1" style="FTEL: 21.45pt"><span lang="EN-US">OleDbDataReader</span><span style="FONT-FAMILY: 楷体_GB2312">会使数据库连接一直处于打开状态，直到显式关闭为止。</span><span lang="EN-US"> </span></p>
<p class="MsoNormal"><span lang="EN-US">OleDbDataReader</span><span style="FONT-FAMILY: 宋体">类不能直接实例化，它总是通过</span><span lang="EN-US">OleDbCommand</span><span style="FONT-FAMILY: 宋体">类的</span><span lang="EN-US">ExecuteReader</span><span style="FONT-FAMILY: 宋体">方法调用而返回。打开了一个数据读取器后，就可以用各种方式访问包含在该读取器中的数据。</span></p>
<p class="MsoNormal"><span style="FONT-FAMILY: 宋体; LETTER-SPACING: -0.1pt">关闭</span><span lang="EN-US" style="LETTER-SPACING: -0.1pt">OleDbDataReader</span><span style="FONT-FAMILY: 宋体; LETTER-SPACING: -0.1pt">对象</span><span lang="EN-US" style="LETTER-SPACING: -0.1pt">(</span><span style="FONT-FAMILY: 宋体; LETTER-SPACING: -0.1pt">显式调用</span><span lang="EN-US" style="LETTER-SPACING: -0.1pt">Close()</span><span style="FONT-FAMILY: 宋体; LETTER-SPACING: -0.1pt">或通过垃圾收集器收集对象</span><span lang="EN-US" style="LETTER-SPACING: -0.1pt">)</span><span style="FONT-FAMILY: 宋体; LETTER-SPACING: -0.1pt">时，</span><span style="FONT-FAMILY: 宋体">底层的连接也会关闭。这取决于调用了哪个</span><span lang="EN-US">ExecuteReader()</span><span style="FONT-FAMILY: 宋体">方法。如果调用了</span><span lang="EN-US">ExecuteReader()</span><span style="FONT-FAMILY: 宋体">方法，并传递了</span><span lang="EN-US">CommandBehavior.CloseConnection</span><span style="FONT-FAMILY: 宋体">，就会在关闭读取器的时候关闭连接。</span></p>
<p class="MsoNormal"><span lang="EN-US">OleDbDataReader</span><span style="FONT-FAMILY: 宋体">类有一个索引器，可以使用常见的数组语法访问任何字段</span><span lang="EN-US">(</span><span style="FONT-FAMILY: 宋体">但不是类型安全的访问</span><span lang="EN-US">)</span><span style="FONT-FAMILY: 宋体">：</span></p>
<p class="2" style="MARGIN-TOP: 8.15pt; MARGIN-LEFT: 21.45pt; MARGIN-RIGHT: 0cm; FTEL: 18.45pt"><span lang="EN-GB"> object o = aReader[0];</span></p>
<p class="2" style="MARGIN-TOP: 0cm; MARGIN-LEFT: 21.45pt; MARGIN-RIGHT: 0cm; FTEL: 18.45pt"><span lang="EN-GB"> object o = aReader["CategoryID"];</span></p>
<p class="MsoNormal"><span style="FONT-FAMILY: 宋体">假定</span><span lang="EN-US">CategoryID</span><span style="FONT-FAMILY: 宋体">字段是</span><span lang="EN-US">SELECT</span><span style="FONT-FAMILY: 宋体">语句中用于生成阅读器的第一个字段</span><span style="FONT-FAMILY: 宋体">，</span><span style="FONT-FAMILY: 宋体">那么这两行语句的功能就是相同的</span><span style="FONT-FAMILY: 宋体">，</span><span style="FONT-FAMILY: 宋体">但后者比前者慢一些。为了验证这一点，编写一个简单的测试程序，从打开的数据读取器上对同一列进行一百万次的迭代访问，获取一些足够大的数字，虽然在一个循环中可能并不会对同一列访问一百万次，但按每秒来计算，就可能编写出最佳的代码。</span></p>
<p class="MsoNormal" style="LINE-HEIGHT: 17pt"><span style="FONT-FAMILY: 宋体">另外，数字索引器平均每</span><span lang="EN-US">0.09</span><span style="FONT-FAMILY: 宋体">秒就进行一百万次的访问，而文本索引器则需要</span><span lang="EN-US">0.63</span><span style="FONT-FAMILY: 宋体">秒。原因是文本方法是从模式的内部查找列号，再使用列号的顺序进行访问。如果知道这个区别，就可以更好地访问数据。</span></p>
<p class="MsoNormal" style="LINE-HEIGHT: 17pt"><span style="FONT-FAMILY: 宋体">是否应使用数字索引器？也许，但还有一种更好的方式。</span></p>
<p class="MsoNormal" style="LINE-HEIGHT: 17pt"><span style="FONT-FAMILY: 宋体">除了上面给出的索引器外，</span><span lang="EN-US">OleDbDataReader</span><span style="FONT-FAMILY: 宋体">还有一组类型安全的方法可以用于读取列，这些方法都可以进行自我解释，且都以</span><span lang="EN-US">Get</span><span style="FONT-FAMILY: 宋体">开头。有一些方法可以读取大多数类型的数据，例如</span><span lang="EN-US">GetInt32</span><span style="FONT-FAMILY: 宋体">、</span><span lang="EN-US"> GetFloat</span><span style="FONT-FAMILY: 宋体">和</span><span lang="EN-US">GetGuid</span><span style="FONT-FAMILY: 宋体">等。</span></p>
<p class="MsoNormal" style="LINE-HEIGHT: 17pt"><span style="FONT-FAMILY: 宋体">前面使用</span><span lang="EN-US">GetInt32</span><span style="FONT-FAMILY: 宋体">的一百万次迭代用了</span><span lang="EN-US">0.06</span><span style="FONT-FAMILY: 宋体">秒，数字索引器中的系统开销是获取数据类型，调用与</span><span lang="EN-US">GetInt32</span><span style="FONT-FAMILY: 宋体">相同的代码，然后装箱</span><span lang="EN-US">(</span><span style="FONT-FAMILY: 宋体">本实例是拆箱</span><span lang="EN-US">)</span><span style="FONT-FAMILY: 宋体">为一个整数。如果以前知道这种模式，并希望使用加密数字而不是列名，以避免对每个列访问使用类型安全的函数，这样运行速度就会比使用文本格式的列名快</span><span lang="EN-US">10</span><span style="FONT-FAMILY: 宋体">倍</span><span lang="EN-US">(</span><span style="FONT-FAMILY: 宋体">选择同一列的上百万个副本</span><span lang="EN-US">)</span><span style="FONT-FAMILY: 宋体">。</span></p>
<p class="MsoNormal" style="LINE-HEIGHT: 17pt"><span style="FONT-FAMILY: 宋体">在可维护性和速度之间有一个折中的问题。如果必须使用数字索引器，就应在类的范围内为每一个要访问的列定义常量。上面的代码可以用于从任何</span><span lang="EN-US">OLE DB</span><span style="FONT-FAMILY: 宋体">数据库中选择数据，但有许多</span><span lang="EN-US">SQL Server</span><span style="FONT-FAMILY: 宋体">的特定类可以使用，但其便利性有明显的损失。</span></p>
<p class="MsoNormal" style="LINE-HEIGHT: 17pt"><span style="FONT-FAMILY: 宋体">下面的示例与上一示例基本相同，但在这个实例中用</span><span lang="EN-US" style="LETTER-SPACING: 0.1pt">SQL</span><span style="FONT-FAMILY: 宋体; LETTER-SPACING: 0.1pt">提供程序和</span><span lang="EN-US" style="LETTER-SPACING: 0.1pt">SQL</span><span style="FONT-FAMILY: 宋体; LETTER-SPACING: 0.1pt">类的引用</span><span style="FONT-FAMILY: 宋体">替换了</span><span lang="EN-US">OLE DB</span><span style="FONT-FAMILY: 宋体">提供程序和对</span><span lang="EN-US">OLE DB</span><span style="FONT-FAMILY: 宋体">类的所有引用。代码的变化已经突出显示出来了。该示例在</span><span lang="EN-US">04</span><span lang="EN-US" style="FONT-FAMILY: 'Baskerville BE Regular'">_</span><span lang="EN-US">DataReaderSql</span><span style="FONT-FAMILY: 宋体">目录下：</span></p>
<p class="a6" style="MARGIN-TOP: 8.15pt; MARGIN-LEFT: 21.45pt; MARGIN-RIGHT: 0cm; FTEL: 18.45pt"><span lang="EN-US">using System;</span></p>
<p class="2" style="MARGIN-LEFT: 21.45pt; LINE-HEIGHT: 15pt; FTEL: 18.45pt"><span lang="EN-GB">using System.Data.SqlClient;</span></p>
<p class="a6" style="MARGIN-LEFT: 21.45pt; LINE-HEIGHT: 15pt; FTEL: 18.45pt"><span lang="EN-GB">public class DataReaderSql</span></p>
<p class="a6" style="MARGIN-LEFT: 21.45pt; LINE-HEIGHT: 15pt; FTEL: 18.45pt"><span lang="EN-GB">{</span></p>
<p class="a6" style="MARGIN-LEFT: 21.45pt; LINE-HEIGHT: 15pt; FTEL: 18.45pt"><span lang="EN-GB"> public static int Main(string[] args)</span></p>
<p class="a6" style="MARGIN-LEFT: 21.45pt; LINE-HEIGHT: 15pt; FTEL: 18.45pt"><span lang="EN-GB"> {</span></p>
<p class="2" style="MARGIN-LEFT: 21.45pt; LINE-HEIGHT: 15pt; FTEL: 18.45pt"><span lang="EN-GB"> string source ="server=(local)\\NetSDK;" +</span></p>
<p class="2" style="MARGIN-LEFT: 21.45pt; LINE-HEIGHT: 15pt; FTEL: 18.45pt"><span lang="EN-GB"> "</span><span lang="EN-GB"> </span><span lang="EN-GB">integrated security=SSPI;" + </span></p>
<p class="2" style="MARGIN-LEFT: 21.45pt; LINE-HEIGHT: 15pt; FTEL: 18.45pt"><span lang="EN-GB"> "database=northwind";</span></p>
<p class="a6" style="MARGIN-LEFT: 21.45pt; LINE-HEIGHT: 15pt; FTEL: 18.45pt"><span lang="EN-GB"> string select = "SELECT ContactName,CompanyName FROM Customers";</span></p>
<p class="2" style="MARGIN-LEFT: 21.45pt; LINE-HEIGHT: 15pt; FTEL: 18.45pt"><span lang="EN-GB"> SqlConnection conn = new SqlConnection(source);</span></p>
<p class="a6" style="MARGIN-LEFT: 21.45pt; LINE-HEIGHT: 15pt; FTEL: 18.45pt"><span lang="EN-GB"> conn.Open();</span></p>
<p class="2" style="MARGIN-LEFT: 21.45pt; LINE-HEIGHT: 15pt; FTEL: 18.45pt"><span lang="EN-GB"> SqlCommand cmd = new SqlCommand(select , conn);</span></p>
<p class="2" style="MARGIN-LEFT: 21.45pt; LINE-HEIGHT: 15pt; FTEL: 18.45pt"><span lang="EN-GB"> SqlDataReader aReader = cmd.ExecuteReader();</span></p>
<p class="a6" style="MARGIN-LEFT: 21.45pt; LINE-HEIGHT: 15pt; FTEL: 18.45pt"><span lang="EN-GB"> while(aReader.Read())</span></p>
<p class="a6" style="MARGIN-LEFT: 21.45pt; LINE-HEIGHT: 15pt; FTEL: 18.45pt"><span lang="EN-GB"> Console.WriteLine("'{0}' from {1}" , aReader.GetString(0) ,</span></p>
<p class="a6" style="MARGIN-LEFT: 21.45pt; LINE-HEIGHT: 15pt; FTEL: 18.45pt"><span lang="EN-GB"> aReader.GetString(1));</span></p>
<p class="a6" style="MARGIN-LEFT: 21.45pt; LINE-HEIGHT: 15pt; FTEL: 18.45pt"><span lang="EN-GB"> aReader.Close();</span></p>
<p class="a6" style="MARGIN-LEFT: 21.45pt; LINE-HEIGHT: 15pt; FTEL: 18.45pt"><span lang="EN-GB"> conn.Close();</span></p>
<p class="a6" style="MARGIN-LEFT: 21.45pt; LINE-HEIGHT: 15pt; FTEL: 18.45pt"><span lang="EN-GB"> return 0;</span></p>
<p class="a6" style="MARGIN-LEFT: 21.45pt; LINE-HEIGHT: 15pt; FTEL: 18.45pt"><span lang="EN-GB"> }</span></p>
<p class="a6" style="MARGIN-TOP: 0cm; MARGIN-LEFT: 21.45pt; MARGIN-RIGHT: 0cm; FTEL: 18.45pt"><span lang="EN-GB">}</span></p>
<p class="MsoNormal" style="LINE-HEIGHT: 18pt"><span style="FONT-FAMILY: 宋体">注意区别是什么？如果键入这些代码，用</span><span lang="EN-US">sql</span><span style="FONT-FAMILY: 宋体">替换所有的</span><span lang="EN-US">OleDb</span><span style="FONT-FAMILY: 宋体">，改变数据源字符串，重新编译。这是很容易的。</span></p>
<p class="MsoNormal" style="LINE-HEIGHT: 18pt"><span style="FONT-FAMILY: 宋体">对</span><span lang="EN-US">Sql</span><span style="FONT-FAMILY: 宋体">提供程序的索引器进行相同的性能测试，这次数字索引器也使用</span><span lang="EN-US">0.13</span><span style="FONT-FAMILY: 宋体">秒就完成了百万次的访问，基于索引器的字符串运行了</span><span lang="EN-US">0.65</span><span style="FONT-FAMILY: 宋体">秒，本机的</span><span lang="EN-US">Sql</span><span style="FONT-FAMILY: 宋体">提供程序要比</span><span lang="EN-US">OleDb</span><span style="FONT-FAMILY: 宋体">快，直到我在</span><span lang="EN-US">.NET</span><span style="FONT-FAMILY: 宋体">版本中测试这一部分，情况都是这样。可以肯定这是不正常的，因为我使用的是最简单的测试</span><span lang="EN-US">(</span><span style="FONT-FAMILY: 宋体">对同一个值选择</span><span lang="EN-US">1 000 000</span><span style="FONT-FAMILY: 宋体">次</span><span lang="EN-US">)</span><span style="FONT-FAMILY: 宋体">，在托管</span><span lang="EN-US">SQL</span><span style="FONT-FAMILY: 宋体">提供程序上进行真实的测试，会得到更好的性能。</span></p></div>
<!-- page -->
<div class="page" style="text-align: center">
<a href="100165601.htm">上一页</a> <a href="index.html">首页</a> <a href="100165603.htm">下一页</a>
</div>
<div style="margin: 0px auto; width: 700px; border: solid 1px #0b5f98;">
<div style="float: left; width: 16px; background-color: #0b5f98; color: White; padding: 1px;">
图书导读
</div>
<div style="float: right; width: 670px; text-align: left; line-height: 16pt; padding-left: 2px">
<!--导读-->
<h1 id="divCurrentNode2" style="color: #b83507; width: 100%; text-align: left; font-size: 12px; padding-left: 2px">当前章节:<a href='100165602.htm'><font color='red'>21.4 快速数据访问：数据读取器</font></a></h1>
<div id="divRealteNod2" style="padding-left: 2px">
<div style='float:left;width:49%'>·<a href='100165599.htm'>21.3 命令</a></div><div style='float:right;width:49%'>·<a href='100165600.htm'>21.3.1 执行命令</a></div><div style='float:left;width:49%'>·<a href='100165601.htm'>21.3.2 调用存储过程</a></div><div style='float:right;width:49%'>·<a href='100165603.htm'>21.5 管理数据和关系：DataSet类</a></div><div style='float:left;width:49%'>·<a href='100165604.htm'>21.5.1 数据表</a></div><div style='float:right;width:49%'>·<a href='100165605.htm'>21.5.2 数据列</a></div></div>
</div>
</div>
</div>
<!-- 评论 -->
<!-- 今日推荐 -->
</div>
<!-- 页脚 -->
<div id="foot">
<img src="../../book/readbook.aspx@node=5602&bookid=16&bookname=21.4++_25bf_25ec_25cb_25d9_25ca_25fd_25be_25dd_25b7_25c3_25ce_25ca_25a3_25ba_25ca_25fd_25be_25dd_25b6_25c1_25c8_25a1_25c6_25f7" alt="" width="0" height="0" />
<style>
</div>
</body>
</html>
⌨️ 快捷键说明
复制代码Ctrl + C
搜索代码Ctrl + F
全屏模式F11
增大字号Ctrl + =
减小字号Ctrl + -
显示快捷键?