Microsoft Data Access Application Block data acess Application Block V2 (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 use your own. NET applicationProgramUse it as a construction block, and reduce the customization that needs to be created, tested, and maintained from the pageCode.

In my opinion, one of the biggest advantages of daab is that daab helps developers automatically manage database connections. the connection object is the most resource-consuming component for database access. If the using {} keyword is not used or the dispose method is used to forcibly release the connection resource, the connection object can be recycled only after the garbage collector is started, if data is used to access the application to construct blocks, developers do not have to deal with resource recycling issues at all.

Data Access Application Block helps you:

Call stored procedures or SQL text commands.
Specify the Parameter Details.
Returns the sqldatareader, dataset, or xmlreader object.

For example, in an application that references data access application block, you can simply call a stored procedure in a line of code and generate a dataset, as shown below:

[Visual Basic]
Dim ds as dataset = sqlhelper. executedataset (_
Connectionstring ,_
Commandtype. storedprocedure ,_
"Getproductsbycategory ",_
New sqlparameter ("@ categoryid", categoryid ))
 
[C #]
Dataset DS = sqlhelper. executedataset (
Connectionstring,
Commandtype. storedprocedure,
"Getproductsbycategory ",
New sqlparameter ("@ categoryid", categoryid ));

The Microsoft. applicationblocks. Data. dll Assembly includes a sqlhelper class (which contains the core functions used to execute database commands) and a sqlhelperparametercache class (which provides parameter discovery and caching functions ).

The sqlhelper class provides a set of static methods that can be used to send many different types of commands to the SQL Server database.

The sqlhelperparametercache class provides command parameter caching 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 six 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:

[Visual Basic]
Execute * (byval connection as sqlconnection ,_
Byval commandtype as commandtype ,_
Byval commandtext as string)

Execute * (byval connection as sqlconnection ,_
Byval commandtype as commandtype ,_
Byval commandtext as string ,_
Byval paramarray commandparameters () as sqlparameter) // The paramarray in VB.net is equivalent to the Params keyword in C #, that is, the parameter Array

Execute * (byval connection as sqlconnection ,_
Byval spname as string ,_
Byval paramarray parametervalues () as object)

/* The sqlhelper class method does not need the sqlconnection parameter for transaction-type overloading. In this version, the connection information is derived from the sqltransaction object, so you do not need to include the sqlconnection object parameter in the method signature. */

Execute * (byval transaction as sqltransaction ,_
Byval commandtype as commandtype ,_
Byval commandtext as string)

Execute * (byval transaction as sqltransaction ,_
Byval commandtype as commandtype ,_
Byval commandtext as string ,_
Byval paramarray commandparameters () as sqlparameter)

Execute * (byval transaction as sqltransaction ,_
Byval spname as string ,_
Byval paramarray parametervalues () as object)

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:

[Visual Basic]
Execute * (byval connectionstring as string ,_
Byval commandtype as commandtype ,_
Byval commandtext as string)

Execute * (byval connectionstring as string ,_
Byval commandtype as commandtype ,_
Byval commandtext as string ,_
Byval paramarray commandparameters () as sqlparameter)

Execute * (byval connectionstring as string ,_
Byval spname as string ,_
Byval paramarray parametervalues () as object)

Executexmlreader does not support connection strings because, unlike the sqldatareader object, the xmlreader object does not provide a method to automatically close the connection when xmlreader is disabled. If the client passes the connection string, the connection object associated with xmlreader cannot be closed after the client completes the operation on xmlreader.

By referring to the data access application block assembly and importing the Microsoft. applicationblocks. Data namespace, you can easily write code using any sqlhelper class method, as shown in the following code example:

[Visual Basic]
Imports Microsoft. applicationblocks. Data

[C #]
Using Microsoft. applicationblocks. Data;

After importing a 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 ");

Use sqlhelperparametercache to manage Parameters
The sqlhelperparametercache class provides three public sharing methods for managing parameters. They are:

Cacheparameterset. Stores the sqlparameters array in the cache.
Getcachedparameterset. Retrieves a copy of the cached parameter array.
Getspparameterset. An overload method is used to retrieve the corresponding parameters of a specified Stored Procedure (first query the database and then cache the results for future queries ).
Cache and retrieval Parameters
You can cache an array of sqlparameter objects by using the cacheparameterset method. This method creates a key by connecting the connection string and command text, and then stores the parameter array in hashtable.

To retrieve parameters from the cache, use the getcachedparameterset method. This method returns an array of sqlparameter objects that are cached (corresponding to the connection string and command text passed to this method) the parameter name, value, direction, and data type are initialized.

Note: The connection string used as the key of the parameter set is matched by a simple string comparison. The connection string used to retrieve parameters from getcachedparameterset must be exactly the same as the connection string used to store these parameters through cacheparameterset. The concatenation strings with different syntaxes are not considered to be matched even if the semantics is the same.

I personally think that sqlhelperparametercache is not very useful, so I don't want to detail it.

How can I use executedataset to return a dataset containing multiple tables?
Create a stored procedure that can return multiple row sets (by executing multiple select statements or calling other stored procedures in a nested manner) and run the procedure using the executedataset method, you can retrieve datasets that contain multiple tables.

For example, assume that your database contains the following stored procedures.

Create procedure getcategories
As
Select * from categories
Go
Create procedure getproducts
As
Select * from products

You can create a primary Stored Procedure for nested calling of these procedures, as shown in the following code example.

Create procedure getcategoriesandproducts
As
Begin
Exec getcategories
Exec getproducts
End

Executing this primary stored procedure using the executedataset method returns a dateset containing two tables: one containing the classified data and the other containing the product data.

Note: The executedataset method does not provide a custom name for the returned table. The number of the first table is always 0, the name is table, the number of the second table is 1, and the name is Table1.