Introduction to the namespace Data Access Application Block Used by sqlhelper

Microsoft application blocks for. netdata Access Application Block overview

Chris Brooks, Graeme Malcolm, Alex macman, and Edward jezierski
Microsoft Corporation

April 2002

Abstract:Data Access Application Block is a. NET component that contains Optimized Data Access Code. It can help users call stored procedures and issue SQL text commands to SQL Server databases. It returns sqldatareader, dataset, and xmlreader objects. You can use it as a construction block in your. NET application 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 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 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.

The rest of this overview includes the following:

What content does the Data Access Application Block contain?

Download and install Data Access Application Block

Use Data Access Application Block

Internal Design

FAQs

Feedback and support

Collaborators

What content does the Data Access Application Block contain?

Provides source code of Data Access Application Block and Quick Start example applications. You can use these applications to test their functions. Data Access Application Block also includes comprehensive documents to help you use and understand the provided code.

Visual Studio. NET Project

Microsoft Visual Basic that provides data access application block. net and Microsoft Visual C # source code, as well as quick start sample client applications for each language, you can use these applications to test common solutions. This helps you better understand how Data Access Application Block works. You can also customize source code to meet your needs.

You can compile the Visual Basic and C # Microsoft. applicationblocks. Data projects to generate a project namedMicrosoft. applicationblocks. Data. dll. This Assembly includesSqlhelperClass (including the core functions used to execute database commands) andSqlhelperparametercacheClass (provides parameter discovery and caching functions ).

Document

The document of Data Access Application Block mainly includes the following:

• 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.
• Design and Implementation of Data Access Application Block. 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.

System Requirements

To run Data Access Application Block, you must meet the following requirements:

• Microsoft Windows 2000, Windows XP Professional
• . NET Framework SDK (English) RTM version
• RTM version of Visual Studio. NET (recommended but not required)
• SQL Server 7.0 or later Database Server

Download and install Data Access Application Block

You can obtain a Windows installer file that contains the signed data access application block assembly and comprehensive documentation.

The installation process will createMicrosoft application blocks for. net(Microsoft Application Block for. Net) Sub-menu. This sub-menu containsData Access(Data Access) Sub-menu, including options for starting the document and options for starting the Data Access Application Block Visual Studio. NET solution.

Go to msdn downloads for download.

Use Data Access Application Block

This section describes how to use data access application block to execute database commands and manage parameters. Figure 1 shows the main elements of the Data Access Application Block.

Figure 1: Data Access Application Block

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.

FAQs what new features does this version provide?

Compared with Data Access Application Block beta 2.0, this RTM version includes the following new features and changes:

• SqlhelperTransactional overload of class methods is no longer requiredSqlconnectionParameters. In this version, the connection information is fromSqltransactionObject, so you do not need to includeSqlconnectionObject parameters.
• Now,GetspparametersetUse ADO. netCommandbuilderClassDeriveparametersMethod to Determine the parameters required by 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 GetCategoriesASSELECT * FROM CategoriesGOCREATE PROCEDURE GetProductsASSELECT * FROM Products

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

CREATE PROCEDURE GetCategoriesAndProductsASBEGINEXEC GetCategoriesEXEC GetProductsEND

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 method to specify a custom name for the returned table. The number of the first table is always0, Name:Table, The number of the second table is1, Name:Table1, And so on.

Are there other application blocks?

Data Access Application Block is one of the several application blocks to be released. These application blocks can solve common problems encountered by developers in different projects. They can be quickly and conveniently inserted into. NET applications.

Feedback and support