) SqlHelper User Guide

Introduction

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 detailed research on 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.

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

SqlHelperParameterCacheClass provides command parameter caching to improve performance. This class consists of manyExecuteMethods (especially those that only run the Stored Procedure) are used internally. The data access client can also directly use it to cache specific parameter sets of specific commands.

Execute commands using the SqlHelper class

SqlHelperClass provides fiveShared(Visual Basic) orStatic(C #) method. They are:ExecuteNonQuery,ExecuteDataset,ExecuteReader,ExecuteScalarAndExecuteXmlReader. Each method 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. 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 commandType,string commandText)Execute* (SqlConnection connection, CommandType commandType,string commandText, params SqlParameter[] commandParameters)Execute* (SqlConnection connection, string spName,params object[] parameterValues)Execute* (SqlConnection connection,CommandType commandType, string commandText)Execute* (SqlConnection connection,CommandType commandType, string commandText,params SqlParameter[] commandParameters)Execute* (SqlConnection connection,string spName, params object[] parameterValues)

In addition to these overloadingExecuteXmlReaderOther methods also provide another overload: connection information can be passed 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 commandType,string commandText)Execute* (string connectionString, CommandType commandType,string commandText,params SqlParameter[] commandParameters)Execute* (string connectionString, string spName,params object[] parameterValues)

Note: ExecuteXmlReaderThe connection string is not supported becauseSqlDataReaderDifferent objects,XmlReaderObject inXmlReaderThe method of automatically closing the connection is not provided when the connection is closed. If the client passes the connection stringXmlReaderAndXmlReaderThe associated connection object.

By referring to the Data Access Application Block assembly and importing the Microsoft. ApplicationBlocks. Data namespace, you can easily write and use anySqlHelperClass method code, 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;INTEGRATEDSECURITY=sspi;", _CommandType.Text, "SELECT * FROM Products");

Use SqlHelperParameterCache to manage Parameters

SqlHelperParameterCacheClass provides three public sharing methods that can be used to manage parameters. They are:

• CacheParameterSet. UsedSqlParametersThe array is stored 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

UseCacheParameterSetMethod, can be cachedSqlParameterObject array. This method creates a key by connecting the connection string and command text, and then stores the parameter array inHashtable.

To retrieve parameters from the cache, useGetCachedParameterSetMethod. 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.

[Visual Basic] 'initialize the connection String and command text'. They constitute the key 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 parameter Dim paramsToStore (1) as SqlParameterparamsToStore (0) = New SqlParameter ("@ Cat", SqlDbType. int) paramsToStore (1) = New SqlParameter ("@ Sup", SqlDbType. int) SqlHelperParameterCache. cacheParameterSet (CONN_STRING, _ SQL, _ paramsToStore) 'retrieve the parameter Dim storedParams (1) As SqlParameterstoredParams = SqlHelperParameterCache from the cache. getCachedParameterSet (_ CONN_STRING, SQL) storedParams (0 ). value = 2 storedParams (1 ). value = 3' use the Dim ds As DataSetds = SqlHelper in the command. executeDataset (CONN_STRING, _ CommandType. text, _ SQL, storedParams) [C #] // initialize the connection string and command text // they constitute the key const string CONN_STRING = "SERVER = (local); DATABASE = Northwind; integrated security = True; "; string spName =" SELECT ProductName FROM Products "+" WHERE Category = @ Cat AND SupplierID = @ Sup "; // cache parameter 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 the parameter SqlParameter storedParams = new SqlParameter [2] From the cache; storedParams = SqlHelperParameterCache. getCachedParameterSet (CONN_STRING, SQL); storedParams (0 ). value = 2; storedParams (1 ). value = 3; // use the DataSet ds parameter in the Command; ds = SqlHelper. executeDataset (CONN_STRING, CommandType. storedProcedure, SQL, storedParams );

Retrieving stored procedure parameters

SqlHelperParameterCacheIt also provides methods for retrieving parameter Arrays for specific stored procedures. Is namedGetSpParameterSetThe overload method provides this function, which includes 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 shows how to retrieve the information in the Northwind database:SalesByCategoryStored procedure parameters.

[Visual Basic] 'initialize the connection String and command text'. They constitute the key Const CONN_STRING As String = _ "SERVER = (local); DATABASE = Northwind; integrated security = True; "Dim spName As String =" SalesByCategory "'search parameter Dim storedParams (1) As SqlParameterstoredParams = SqlHelperParameterCache. getSpParameterSet (_ CONN_STRING, spName) storedParams (0 ). value = "Beverages" storedParams (1 ). value = "1997" 'in the command, use the Dim ds As DataSetds = SqlHelper. executeDataset (CONN_STRING, _ CommandType. storedProcedure, _ spName, storedParams) [C #] // initialize the connection string and command text // they constitute the key const string CONN_STRING = "SERVER = (local); DATABASE = Northwind; integrated security = True; "; string spName =" SalesByCategory "; // the retrieval parameter SqlParameter storedParams = new SqlParameter [2]; storedParams = SqlHelperParameterCache. getSpParameterSet (CONN_STRING, spName); storedParams [0]. value = "Beverages"; storedParams [1]. value = "1997"; // use the DataSet ds parameter in the Command; 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

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.

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 implementation the client calls, all commands useSqlCommandObject. InSqlCommandBefore an object can be executed, all parameters must be addedParametersAnd must be set correctly.Connection,CommandType,CommandTextAndTransactionAttribute.SqlHelperThe special functions in the class are mainly used to provide a consistent method for issuing commands to the SQL Server database, without considering the implementation of the overloaded methods called by client applications. The 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 private ExecuteReader implementation is used through the appropriateCommandBehaviorOpen the SqlDataReader object to most effectively manage the validity period of the connection associated with the reader.

SqlHelperParameterCache class Implementation Details

Parameter arrays are cached in dedicatedHashtable. 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. Dedicated shared FunctionsCloneParametersThis can be achieved.