天魔窟

Tips for Successful Data Access

Summary: Discusses techniques to speed up data retrieval for data-driven applications, considering successful strategies for querying, paging, sorting, and caching.

Contents
Data Access and Execution Time
The Data Reader Object
Internals of the SqlDataReader Class
Command Behaviors
Caching Strategies
Strategies for Paging and Sorting
Summary

The property HasRows has been added in version 1.1 of the Microsoft® .NET Framework and indicates whether the SqlDataReader contains rows or is empty.
（就是说在 .NET 1.0 里边，还是使用 if(rdr.Read()) 来判断有没有记录了）

First and foremost, methods that accept indexes are faster than methods requiring a column name, although they are perhaps less practical from a developer's standpoint, as it is less intuitive to deal with a call like reader[3] than reader["LastName"]. A column name is always resolved internally into a 0-based column index. The data reader's GetOrdinal method provides for this translation service.
（使用下标总是快一些。显然，所以我在 ASP 中总是使用 rs.GetRows() 方法，使用下标来取字段值）

The GetValue method is also slightly faster than the Item accessors. The Item property simply calls into GetValue and in so doing transforms the column name into an index. The difference between GetSqlValue and GetValue is all in the type they return. The former returns a .NET type that mimics a native SQL Server type; the latter returns the content of a field as an instance of a base .NET type. The data reader features a handful of type-specific methods, such as GetString, GetDateTime, GetSqlString, and GetSqlDateTime. The GetXXX methods just call into the corresponding GetSqlXXX and then extract the actual value from the SQL wrapper type.
（使用 GetXXX(下标) 的方法比 rdr[字段名] 的方法快！）

GetSqlXXX methods are slightly faster than corresponding GetXXX methods. The reason for the better performance lies in the fact that the data types in the .NET Framework SqlDbType enumeration closely match the internal representation of types made by SQL Server. As a result, no conversion is required to return data to the application.
（哦，我多是用 GetXXX()……）

For example, if you attempt to store a null value contained in one of the columns retrieved in a .NET type, at a minimum you get an exception. By contrast, nothing bad happens if you store a null value in a SQL type. The IsNull method on each SQL type lets you check for the null value later. The IsDBNull method on the data reader class lets you check whether the data is storing null when GetXXX methods are used, but you have to check it right after you retrieve the data to avoid exceptions.
（嗯，使用 GetXXX() 之前，先用 rdr.IsDBNull(下标) 检查～）

Finally, note that when you close the reader, the underlying connection is not automatically closed. Calling the Close method on the connection object is therefore a necessity.
（使用 SqlHelper 之类的数据访问类，就不必考虑这些了 :)）

To operate with a data reader, you first have to call its Read method. The method moves the internal pointer forward and selects the next record, if any. By default, the values of all columns are cached internally and then returned by the various GetXXX and GetSqlXXX methods. If you know you need only a few fields, the SequentialAccess behavior helps to optimize your code.
（使用 rdr 的值之前先 Read()！至于“SequentialAccess”以前倒没有注意过……）