更好的使用SQLHelper类
微软提供的Data Access Application Block中的SQLHelper类中封装了最常用的数据操作，各个使用者调用他而写的代码也有很大区别。
Data Access Application Block for .NET
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/daab-rm.asp
Data Access Application Block for .NET
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/daab-rm.asp
Internal Design
The Data Access Application Block includes the full source code and a comprehensive guide to its design. In this section, the main implementation details are described.
SqlHelper Class Implementation Details
The SqlHelper class is designed to encapsulate data access functionality through a set of static methods. Because it is not designed to be inherited from or instantiated, the class is declared as a non-inheritable class with a private constructor.
Each of the methods implemented in the SqlHelper class provides a consistent set of overloads. This provides a well defined pattern for executing a command by using the SqlHelper class, while giving developers the necessary level of flexibility in how they choose to access data. The overloads provided for each method support different method arguments, so developers can decide how connection, transaction, and parameter information should be passed. The methods implemented in the SqlHelper class are:
- ExecuteNonQuery. This method is used to execute commands that do not return any rows or values. They are generally used to perform database updates, but they can also be used to return output parameters from stored procedures.
- ExecuteReader. This method is used to return a SqlDataReader object that contains the resultset returned by a command.
- ExecuteDataset. This method returns a DataSet object that contains the resultset returned by a command.
- ExecuteScalar. This method returns a single value. The value is always the first column of the first row returned by the command.
- ExecuteXmlReader. This method returns an XML fragment from a FOR XML query.
- FillDataset. This method is similar to ExecuteDataset, except that a pre-existing DataSet can be passed in, allowing additional tables to be added.
- UpdateDataset. This method updates a DataSet using an existing connection and user-specified update commands. It is typically used with CreateCommand.
- CreateCommand. This method simplifies the creation of a SQL command object by allowing a stored procedure and optional parameters to be provided. This method is typically used with UpdateDataset.
- ExecuteNonQueryTypedParams. This method executes a non-query operation using a data row instead of parameters.
- ExecuteDatasetTypedParams. This method executes a DataSet creation operation, using a data row instead of parameters.
- ExecuteReaderTypedParams. This method returns a data reader using a data row instead of parameters.
- ExecuteScalarTypedParams. This method returns a scalar using a data row instead of parameters.
- ExecuteXmlReaderTypedParams. This method executes an XmlReader using a data row instead of parameters.
In addition to the public methods, the SqlHelper class includes a number of private functions, which are used to manage parameters and prepare commands for execution. Regardless of the method implementation called by the client, all commands are executed by using a SqlCommand object. Before this SqlCommand object can be executed, any parameters must be added to its Parameters collection, and the Connection, CommandType, CommandText, and Transaction properties must be set appropriately. The private functions in the SqlHelper class are primarily designed to provide a consistent way to execute commands against a SQL Server database, regardless of the overloaded method implementation called by the client application. The private utility functions in the SqlHelper class are:
- AttachParameters. A function used to attach any necessary SqlParameter objects to the SqlCommand being executed.
- AssignParameterValues. A function used to assign values to the SqlParameter objects.
- PrepareCommand. A function used to initialize the properties of the command, such as its connection, transaction context, and so on.
- ExecuteReader. This private implementation of ExecuteReader is used to open a SqlDataReader object with the appropriate CommandBehavior to manage the lifetime of the connection associated with the reader most efficiently.
SqlHelperParameterCache Class Implementation Details
Parameter arrays are cached in a private Hashtable. Internally, the parameters retrieved from the cache are copied so that the client application can change parameter values, and so on, without affecting the cached parameter arrays. A private shared function named CloneParameters is used for this purpose.
