Microsoft. applicationblocks (1)

Data Access Application Block encapsulates the best experience in accessing Microsoft SQL Server databases in terms of performance and resource management. You can easily. the. NET application uses it as a construction block, reducing the number of custom code that needs to be created, tested, and maintained from the page. In particular, the data access application block can help you:

◆ Call stored procedures or SQL text commands.

◆ Specify the Parameter Details.

◆ Return sqldatareader, dataset, or xmlreader objects.

Example

[C #]
Dataset DS = sqlhelper. executedataset (
Connectionstring,
Commandtype. storedprocedure,
"Getproductsbycategory ",
New sqlparameter ("@ categoryid", categoryid ));

The document data access application block mainly includes the following content:

◆ Use Data Access Application Block to develop applications. This section includes a Quick Start example, which contains a variety of common usage scenarios to help you quickly and easily master the use of Data Access Application Block (Data Access Application Block.

◆ Data Access application block design and implementation. This section includes background design principles to help you gain an in-depth understanding of the design and implementation of the Data Access Application Block.

◆ Deployment and operation. This section includes the installation information, including deployment and update options, and security-related information.

◆ Reference. This section contains a comprehensive api reference, which details the classes and interfaces that constitute the Data Access Application Block.

Figure 1 shows the main elements of the Data Access Application Block.

498) This. style. width = 498; "> Figure 1: the data access application blocksqlhelperparametercache class provides the command parameter caching function to improve performance. This class is used internally by many execute methods (especially those that only run the stored procedure. The data access client can also directly use it to cache specific parameter sets of specific commands. The sqlhelper class provides five shared (Visual Basic) or static (C #) Methods: executenonquery, executedataset, executereader, executescalar, and executexmlreader. Each method provides a set of consistent overloading. This provides a good way to use the sqlhelper class to execute commands. It also provides necessary flexibility for developers to select a way to access data. Each method overload supports different method parameters, so developers can determine how to pass connection, transaction, and parameter information. All methods implemented in the class support the following overloading: [C #]
Execute * (sqlconnection connection, commandtype, string commandtext)

Execute * (sqlconnection connection, commandtype, string commandtext, Params sqlparameter [] commandparameters)

Execute * (sqlconnection connection, string spname, Params object [] parametervalues)

Execute * (sqlconnection connection, commandtype, string commandtext)

Execute * (sqlconnection connection, commandtype, string commandtext, Params sqlparameter [] commandparameters)

Execute * (sqlconnection connection, string spname, Params object [] parametervalues)

In addition to these overloading, other methods except executexmlreader also provide another overload: connection information can be transmitted as a connection string rather than a connection object, as shown in the following method signature:
Sqlhelper class Implementation Details

[C #]
Execute * (string connectionstring, commandtype, string commandtext)

Execute * (string connectionstring, commandtype, string commandtext, Params sqlparameter [] commandparameters)

Execute * (string connectionstring, string spname, Params object [] parametervalues)

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 public methods,SqlhelperClass also contains some special functions for managing parameters and preparing commands to be executed. No matter what method is called by the client, all commands are implemented by usingSqlcommandObject. Run thisSqlcommandAll parameters must be added toParametersAnd must be set accordingly.Connection,Commandtype,CommandtextAndTransactionAttribute.SqlhelperThe special functions in the class are mainly used to provide a consistent way to execute commands on the SQL Server database without considering the implementation of the overload methods called by client applications.SqlhelperThe special utility functions in the 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.

Sqlhelperparametercache class Implementation Details

Parameter arrays are cached in dedicatedHashtable. Parameters retrieved from the cache are copied internally, so that the client application can change the parameter value and perform other operations without affecting the cached parameter array. Dedicated shared FunctionsCloneparametersFor this purpose.