Microsoft application blocks for. Net (2)

Execute commands using the sqlhelper class
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:

 

 

 

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:

 

 

 

Execute * (string connectionstring, commandtype,
String commandtext)

 

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

 

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

 

Note:

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 Data Access Application BlockProgramSet and import the Microsoft. applicationblocks. Data namespace. You can easily writeCode, As shown in the following code example:

 

 

Using Microsoft. applicationblocks. Data;

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

 

 

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 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.
The following code uses the sqlhelperparametercache class to cache and retrieve parameters of a Transact-SQL statement.

 

 

 

// Initialize the connection string and command text
// They form keys used to store and retrieve Parameters
Const string conn_string =
"Server = (local); database = northwind; Integrated Security = true ;";
String spname = "select productname from products" +
"Where category = @ cat and supplierid = @ sup ";

 

// Cache Parameters
Sqlparameter [] paramstostore = new sqlparameter [2];
Paramstostore [0] = new sqlparameter ("@ cat", sqldbtype. INT );
Paramstostore [1] = new sqlparameter ("@ sup", sqldbtype. INT );
Sqlhelperparametercache. cacheparameterset (conn_string,
SQL,
Paramstostore );

 

// Retrieve parameters from the cache
Sqlparameter storedparams = new sqlparameter [2];
Storedparams = sqlhelperparametercache. getcachedparameterset (
Conn_string, SQL );
Storedparams (0). value = 2;
Storedparams (1). value = 3;

 

// Use parameters in the command
Dataset Ds;
DS = sqlhelper. executedataset (conn_string,
Commandtype. storedprocedure,
SQL, storedparams );

 

Retrieving stored procedure parameters
Sqlhelperparametercache also provides methods for retrieving parameter Arrays for specific stored procedures. An overload method named getspparameterset provides this function, which contains two implementations. This method attempts to retrieve the parameters of a specific stored procedure from the cache. If these parameters are not cached, use the. NET sqlcommandbuilder class to retrieve them from the internal cache and add them to the cache for later retrieval requests. Then, specify the corresponding parameter settings for each parameter, and return these parameters to the client in an array. The following code retrieves the parameters of the salesbycategory stored procedure in the northwind database.

 

 

// Initialize the connection string and command text
// They form keys used to store and retrieve Parameters
Const string conn_string =
"Server = (local); database = northwind; Integrated Security = true ;";
String spname = "salesbycategory ";

 

// Retrieve Parameters
Sqlparameter storedparams = new sqlparameter [2];
Storedparams = sqlhelperparametercache. getspparameterset (
Conn_string, spname );
Storedparams [0]. value = "beverages ";
Storedparams [1]. value = "1997 ";

 

// Use parameters in the command
Dataset Ds;
DS = sqlhelper. executedataset (conn_string,
Commandtype. storedprocedure,
Spname, storedparams );