) Data Access Application Block-sqlhelper class

SqlhelperClass is used to encapsulate the data access function through a set of static methods. This class cannot be inherited or instantiated, so it is declared as a non-inherited class that contains a dedicated constructor.

InSqlhelperEach method implemented in the class provides a set of consistent overloading. This provides a good useSqlhelperClass to execute commands, while providing developers with the necessary flexibility to select the way to access data. Each method overload supports different method parameters, so developers can determine how to pass connection, transaction, and parameter information. InSqlhelperThe methods implemented in the class include:

•	Executenonquery. This method is used to execute commands that do not return any rows or values. These commands are usually used to execute database updates, but can also be used to return output parameters of stored procedures.
•	Executereader. This method is used to returnSqldatareaderObject that contains the result set returned by a command.
•	Executedataset. This method returnsDatasetObject that contains the result set returned by a command.
•	Executescalar. This method returns a value. This value is always the first column in the first line returned by the command.
•	Executexmlreader. This method returnsFor XMLThe XML fragment to query.
•	Filldataset. This method is similarExecutedatasetThe difference is that pre-existingDatasetTo allow additional tables to be added.
•	Updatedataset. This method uses existing connections and user-specified update commands to updateDataset. It usually correspondsCreatecommandCommand.
•	Createcommand. This method allows you to provide stored procedures and optional parameters to simplify the creation process of SQL command objects. This method usually worksUpdatedatasetIn combination.
•	Executenonquerytypedparams. This method uses data rows instead of parameters to perform non-query operations.
•	Executedatasettypedparams. This method uses data rows instead of parameters for execution.DatasetCreate operation.
•	Executereadertypedparams. This method uses data rows instead of parameters to return data readers.
•	Executescalartypedparams. This method uses data rows instead of parameters to return scalar values.
•	Executexmlreadertypedparams. This method uses data rows instead of parameters for execution.Xmlreader.

in addition to these common methods, the sqlhelper class also contains specialized functions for parameter management and command preparation. Regardless of the method implementation called by the client, all commands are executed by using the sqlcommand object. Before executing this sqlcommand object, you must add all parameters to its parameters set, connection , commandtype , commandtext , and transaction must be set accordingly. attribute. The special functions in the sqlhelper class are mainly used to provide a consistent method for executing commands on the SQL Server database, the implementation of the overload method called by the client application Program is not considered. Special utility functions in the sqlhelper class include:

•	Attachparameters. This function is usedSqlparameterThe object is appended toSqlcommand.
•	Assignparametervalues. This function is usedSqlparameterObject assignment.
•	Preparecommand. This function is used to initialize command attributes (such as connection and transaction context.
•	Executereader.ExecutereaderIs used to openSqldatareaderObject, and use the correspondingCommandbehaviorManage the lifecycle of connections associated with the reader in the most effective way.

How to Use the sqlhelper class?

By referencing data to access the application block assembly and importing the Microsoft. applicationblocks. Data namespace, you can writeCode, As shown in the following code example:

 
[Visual Basic] imports Microsoft. applicationblocks. Data [C #] using Microsoft. applicationblocks. Data;
 

After importing the namespace, you can call any execute * method, as shown in the following code example:

 
[Visual Basic] Dim ds as dataset = sqlhelper. executedataset (_ "Server = (local); database = northwind; Integrated Security = true;", _ commandtype. text, "select * from products") [C #] dataset DS = sqlhelper. executedataset ("Server = dataserver; database = northwind; Integrated Security = sspi;", commandtype. text, "select * from products ");