Sqlhelper User Guide

Abstract: Data Access Application Block is a. NET component that includes optimized data access. Code To help users call stored procedures and issue SQL text commands to SQL Server databases. It returns sqldatareader, dataset, and xmlreader objects. You can go to your own. NET application Program Use it as a construction block to reduce the number of custom code that needs to be created, tested, and maintained. You can download the complete C # and Visual Basic. net Source code And comprehensive documentation.

Introduction
Are you engaged in the design and development of. NET application data access code? Do you think you are always writing the same data access code? Have you ever wrapped the data access code in the Helper function so that you can call stored procedures in one row? If so, Microsoft & reg; Data Access Application Block for. NET is designed for you.

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 it as a constructor block in your. NET application, reducing the number of custom code that needs to be created, tested, and maintained from the page.

In particular, 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 ));

Note: Application Block for. Net (Application Block for. net) is designed based on a detailed study of successful. NET applications. It is provided in the form of source code, which can be used as is, or customized for your own applications. This application block does not represent the future development direction of the Microsoft ADO. Net Library. The Microsoft ADO. Net Library is created to precisely control data access behavior in various use cases. In the future, ADO. NET may use different models to implement this scheme.

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.

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:

[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)

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

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)

[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 reloads, other methods except executexmlreader 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)

[C #]
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 sqldatareader, xmlreader 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.
The following code uses the sqlhelperparametercache class to cache and retrieve parameters of a Transact-SQL statement.

[Visual Basic]
'Initialize the connection string and command text
They will constitute keys used to store and retrieve Parameters
Const conn_string as string = _
"Server = (local); database = northwind; Integrated Security = true ;"
Dim SQL as string = _
"Select productname from products" + _
"Where category = @ cat and supplierid = @ sup"

'Cache Parameters
Dim paramstostore (1) as sqlparameter
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
Dim storedparams (1) as sqlparameter
Storedparams = sqlhelperparametercache. getcachedparameterset (_
Conn_string, SQL)
Storedparams (0). value = 2
Storedparams (1). value = 3

'Use parameters in the command
Dim ds as Dataset
DS = sqlhelper. executedataset (conn_string ,_
Commandtype. Text ,_
SQL, storedparams)

[C #]
// 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.

[Visual Basic]
'Initialize the connection string and command text
They will constitute keys used to store and retrieve Parameters
Const conn_string as string = _
"Server = (local); database = northwind; Integrated Security = true ;"
Dim spname as string = "salesbycategory"

'Retrieve Parameters
Dim storedparams (1) as sqlparameter
Storedparams = sqlhelperparametercache. getspparameterset (_
Conn_string, spname)
Storedparams (0). value = "beverages"
Storedparams (1). value = "1997"

'Use parameters in the command
Dim ds as Dataset
DS = sqlhelper. executedataset (conn_string ,_
Commandtype. storedprocedure ,_
Spname, storedparams)

[C #]
// 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 );

Internal Design
The Data Access Application Block contains the complete source code and a comprehensive guide to its design. This section describes the main implementation details.

Sqlhelper class Implementation Details
The sqlhelper class is used to encapsulate data access 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.

Each method implemented in the sqlhelper class 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. The methods implemented in the sqlhelper 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 return the sqldatareader object, which contains the result set returned by a command.
executedataset. This method returns the DataSet object, which 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 returns the XML fragment of the for XML query.
in addition to these common methods, the sqlhelper class also contains some special functions used to manage parameters and prepare commands to be executed. Regardless of the method implementation called by the client, all commands are executed through the sqlcommand object. Before the sqlcommand object can be executed, all parameters must be added to the parameters set, and the connection, commandtype, commandtext, and transaction attributes must be correctly set. The special functions in the sqlhelper class are mainly used to provide a consistent method for issuing commands to the SQL Server database without considering the implementation of the overload methods called by client applications. Special utility functions in the sqlhelper class include:

Attachparameters: This function is used to connect all necessary sqlparameter objects to the running sqlcommand.
Assignparametervalues: This function is used to assign values to sqlparameter objects.
Preparecommand: This function is used to initialize command attributes (such as connection and transaction environment.
Executereader: This dedicated executereader implementation is used to open the sqldatareader object through the appropriate commandbehavior, so as to most effectively manage the validity period of the connection associated with the reader.
Sqlhelperparametercache class Implementation Details
The parameter array is cached in the dedicated hashtable. The parameters retrieved from the cache are replicated internally, so that the client application can change the parameter values and perform other operations without affecting the cached parameter arrays. The dedicated sharing Function cloneparameters can achieve this purpose.

FAQs
What new features are included in this version?
Compared with Data Access Application Block beta 2.0, this RTM version includes the following new features and changes:

The sqlhelper method does not need the sqlconnection parameter for transactional overload. 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.
Now, the getspparameterset method uses the deriveparameters method of the ADO. Net commandbuilder class to determine the parameters required for the stored procedure. This is more efficient than querying the database directly in Beta 2.0.
Can xcopy be used to deploy the data access application block assembly?
Yes. Microsoft. applicationblocks. Data. dll assembly can be deployed using xcopy after compilation.

When should I use the executedataset method and the executereader method?
In fact, when should multiple data rows in the DataSet object be returned and datareader be used. The answer depends on your application's specific needs and your choice between flexibility and original performance. Dataset provides you with a flexible and disconnected view of data, while datareader provides you with a superior performance, read-only, forward cursor only. For more information about dataset and datareader, see Data Access architecture guide ).

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.