. Net Data Access Application Block)

--------------------------------------------------------------------------------

Summary
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 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 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 Visual Basic and C # Microsoft. applicationblocks. Data projects to generate an Assembly named Microsoft. applicationblocks. Data. dll. This Assembly includes a sqlhelper class (which includes the core functions used to execute database commands) and a sqlhelperparametercache class (which 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.
For more information, see. 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 creates a Microsoft application blocks for. Net (Microsoft Application Block for. Net) submenu in the program menu. This sub-menu contains a 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
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 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)

[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 for managing parameters and preparing 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. 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 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.
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
If you have any questions, comments, or suggestions about the Data Access Application Block, please email the devfdbck@microsoft.com and we will provide feedback in a timely manner.
Application blocks for. NET is designed to assist in the development of. Net distributed applications. The sample code and documentation are provided as they are. Although we have been tested and considered a stable code set, we do not provide support for it as traditional Microsoft products do.
We have also created a newsgroup to help you use application blocks for. Net (. NET application block ). You can consult with colleagues, colleagues, and Microsoft support experts in online open forums.
Others can also benefit from your questions and comments. Our development team will view newsgroups every day:
News group: web-based readers
Http://msdn.microsoft.com/newsgroups/loadframes.asp? ICP = msdn & slcid = US & newsgroup = Microsoft. Public. DOTNET. distributed_apps)
Newsgroup: NNTP Reader
News: // msnews.microsoft.com/microsoft.public.dotnet.distributed_apps)
Do you want to learn and use. NET functions? You are welcome to work with Microsoft technology centers technical experts to learn the best development experience. For details, visit the http://www.microsoft.com/business/services/mtc.asp ).
Do you need more help? Please visit the new customer support service advisory services. This solution can meet your small-scale consulting needs. For more information about advisory services, visit the http://support.microsoft.com/default.aspx? SCID = FH; en-US; offer58 & Fr = 0 & SD = gn & Ln = en-US & Ct = SD & SE = Nona (English ).
More information
The Design and Development of Data Access Application Block is based on the best development experience and general design principles discussed in the data access in. NET architecture Guide (English. Read this guide to learn more about data access.