. NET Data Access Application Block (application)

access|application| Program | access | Data Digest
The data Access Application Block is a. NET component that contains optimized data access code that can help users invoke stored procedures and emit SQL text commands to SQL Server databases. It returns SqlDataReader, datasets, and XmlReader objects. You can use it as a building block in your own. NET application to reduce the number of custom code that you need to create, test, and maintain. You can download the full C # and Visual Basic. NET source code as well as the consolidated documentation.

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

Brief introduction
Are you working on the design and development of. NET Application data access code? Do you feel like you're always writing the same data access code over and over again? Have you ever wrapped your data access code in a Helper function so that you can call a stored procedure on a single line? If so, Microsoft Data Access Application Block for. NET is designed for you.
Data Access Application Block encapsulates the best experience in performance and resource management for accessing Microsoft SQL Server databases. You can easily use it as a building block in your. NET applications, reducing the number of custom code you need to create, test, and maintain from the page.
In particular, Data Access Application Block can help you:
Invokes a stored procedure or SQL text command.
Specifies the parameter details.
Returns a SqlDataReader, DataSet, or XmlReader object.
For example, in an application that references the Data Access application block, you can simply call a stored procedure and generate a DataSet in one line of code, as follows:
[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 blocks for. NET) is designed based on a detailed study of successful. NET applications. It is provided as source code, you can use it as is, or you can customize it for your own application. The application block does not represent the direction of future Microsoft ado.net libraries. The Microsoft Ado.net Library is built to achieve precise control over data access behavior in a variety of uses. Future versions of Ado.net may use different models to implement this scenario.
The remainder of this overview includes the following sections:
What does the Data Access application block contain?
Download and install the Data Access Application Block
Using the Data Access Application Block
Internal design
Problems
Feedback and Support
Collaborators

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

What does the Data Access application block contain?
Provides the source code for the Data Access Application Block and the QuickStart sample application that you can use to test its functionality. The Data Access Application Block also includes a comprehensive document to help you use and understand the code you provide.
Visual Studio. NET Project
Provides Microsoft visual Basic. NET and Microsoft Visual C # source code for Data Access Application block, and QuickStart sample client applications for each language that you can use Test common scenarios. This helps to deepen your understanding of how the Data Access Application Block works. You can also customize the source code to suit your needs.
You can compile Visual Basic and C # Microsoft.ApplicationBlocks.Data projects to generate an assembly named Microsoft.ApplicationBlocks.Data.dll. The assembly includes a SqlHelper class (which contains core functionality for executing database commands) and a SqlHelperParameterCache class (which provides parameter discovery and caching capabilities).
Document
The documentation for Data Access Application Block includes the following:
Use the Data Access Application block to develop your application. This section includes QuickStart samples, which contain a variety of common uses that can help you quickly and easily master the use of the data Access Application block.
The design and implementation of Data Access application Block. This section includes background design principle information, so that users can have a deeper understanding of the design and implementation of Data Access application Block.
Deploy and run. This section includes installation information, which contains deployment and update options and security-related information.
Reference. This section contains a comprehensive API reference detailing the classes and interfaces that make up the Data Access Application Block.
System Requirements
To run Data Access Application block, you need to meet the following requirements:
Microsoft Windows 2000, Windows XP Professional
The RTM version of the. NET Framework SDK (English)
The RTM version of Visual Studio. NET (recommended, but not required)
SQL Server 7.0 or later database server

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

Download and install the Data Access Application Block
You can get a Windows installer file that contains the signed Data Access Application Block assembly and the consolidated document.
The installation process will create a Microsoft application Blocks for in your Programs menu. NET (Microsoft Application Block for. net) submenu. This submenu has a data Access submenu that includes options for starting the document and options for starting the Data Access Application Block Visual Studio. NET solution.
Please go to MSDN Downloads for download.

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

Using the Data Access Application Block
This section discusses how to use the data Access Application block to execute database commands and administrative 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 emit many different types of commands to the SQL Server database.
The SqlHelperParameterCache class provides command-parameter caching, which can be used to improve performance. This class is used internally by many Execute methods, especially those that run only stored procedures. The data access client can also use it directly to cache a specific set of parameters for a particular command.
To execute a command using the SqlHelper class
The SqlHelper class provides five types of Shared (Visual Basic) or Static (C #) methods: ExecuteNonQuery, ExecuteDataset, ExecuteReader, ExecuteScalar and ExecuteXmlReader. Each of the methods implemented provides a consistent set of overloads. This provides a good way to use the SqlHelper class to execute commands, while providing the necessary flexibility for developers to choose how to access the data. The overloads of each method support different method parameters, so the developer can determine how the connection, transaction, and parameter information is passed. All methods implemented in a class support the following overloads:
[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 overloads, methods other than ExecuteXmlReader provide another overload: allowing connection information to 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: ExecuteXmlReader does not support connection strings because: Unlike SqlDataReader objects, the XmlReader object does not provide a way to automatically close the connection when XmlReader closes. If the client passes the connection string, the connection object associated with XmlReader cannot be closed after the client completes an operation on XmlReader.
By referencing the Data Access Application Block assembly and importing the Microsoft.ApplicationBlocks.Data namespace, you can easily write code that uses any of the SqlHelper class methods. As shown in the following code example:
[Visual Basic]
Imports Microsoft.ApplicationBlocks.Data

[C #]
Using Microsoft.ApplicationBlocks.Data;
After you import a namespace, you can invoke any execute* method, as shown in the following code example:
[Visual Basic]
Dim ds as DataSet = SqlHelper.ExecuteDataset (_
"Server= (local);D atabase=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");
Using the SqlHelperParameterCache class to manage parameters
The SqlHelperParameterCache class provides three common shared methods that you can use to manage parameters. They are:
CacheParameterSet. Used to store the sqlparameters array in the cache.
Getcachedparameterset. A copy of the cached parameter array to retrieve.
GetSpParameterSet. An overloaded method that retrieves the appropriate parameters for the specified stored procedure (querying the database first, and then caching the results for future queries).
Caching and Retrieving parameters
By using the CacheParameterSet method, you can cache an array of SqlParameter objects. This method creates a key by connecting the connection string and the 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 have been initialized with the names, values, orientations, and data types of the parameters in the cache (corresponding to the connection string passed to the method and the command text).
Note: The connection string used as the key for the parameter set is matched by a simple string comparison. The connection string used to retrieve parameters from Getcachedparameterset must be identical to the connection string used to store the parameters by CacheParameterSet. Connection strings with different syntax are not considered to match, even if they are semantically identical.
The following code shows how to use the SqlHelperParameterCache class to cache and retrieve parameters for Transact-SQL statements.
[Visual Basic]
' Initialize connection string and command text
' They will form the 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"

' Cached 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)

' Retrieving 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 commands
Dim DS as DataSet
ds = SqlHelper.ExecuteDataset (Conn_string, _
CommandType.Text, _
SQL, storedparams)

[C #]
Initializing connection strings and command text
They will form the 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);

Retrieving parameters from the cache
SqlParameter storedparams = new Sqlparameter[2];
Storedparams = Sqlhelperparametercache.getcachedparameterset (
conn_string, SQL);
Storedparams (0). Value = 2;
Storedparams (1). Value = 3;

To use parameters in a 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 overloaded method named GetSpParameterSet provides this functionality, and it contains two implementations. This method attempts to retrieve the parameters of a particular stored procedure from the cache. If these parameters have not been cached, the. NET SqlCommandBuilder classes are retrieved from the inside and added to the cache for subsequent retrieval requests. Then, specify the appropriate parameter settings for each parameter, and finally return the parameters as an array to the client. The following code shows how to retrieve the parameters of a salesbycategory stored procedure in the Northwind database.
[Visual Basic]
' Initialize connection string and command text
' They will form the 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 commands
Dim DS as DataSet
ds = SqlHelper.ExecuteDataset (Conn_string, _
CommandType.StoredProcedure, _
Spname, Storedparams)

[C #]
Initializing connection strings and command text
They will form the keys used to store and retrieve parameters
Const string conn_string =
"Server= (local); Database=northwind; integrated security=true; ";
string spname = "SalesByCategory";

Retrieving parameters
SqlParameter storedparams = new Sqlparameter[2];
Storedparams = Sqlhelperparametercache.getspparameterset (
Conn_string, spname);
Storedparams[0]. Value = "Beverages";
STOREDPARAMS[1]. Value = "1997";

To use parameters in a command
DataSet ds;
ds = SqlHelper.ExecuteDataset (Conn_string,
CommandType.StoredProcedure,
Spname, storedparams);

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

Internal design
The Data Access Application Block contains complete source code and a comprehensive guide to its design. This section describes the detailed information about the primary implementation.
SqlHelper Class Implementation Details
The SqlHelper class is used to encapsulate data access functionality through a set of static methods. The class cannot be inherited or instantiated, so it is declared as an inheritable class that contains a private constructor.
Each of the methods implemented in the SqlHelper class provides a consistent set of overloads. This provides a good way to use the SqlHelper class to execute commands, while providing the necessary flexibility for developers to choose how to access the data. The overloads of each method support different method parameters, so the developer can determine how the connection, transaction, and parameter information is passed. 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 typically used to perform database updates, but can also be used to return the output parameters of a stored procedure.
ExecuteReader. This method is used to return a SqlDataReader object that contains the result set returned by a command.
ExecuteDataset. This method returns a DataSet object 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 row returned by the command.
ExecuteXmlReader. This method returns an XML fragment of the FOR XML query.
In addition to these public methods, the SqlHelper class also contains specialized functions for managing parameters and preparing the commands to be executed. Regardless of the method implementation that the client invokes, all commands are executed through the SqlCommand object. All parameters must be added to the Parameters collection before the SqlCommand object can be executed, and the Connection, CommandType, CommandText, and Transaction properties must be set correctly. The specialized functions in the SqlHelper class are used primarily to provide a consistent way to issue commands to the SQL Server database, regardless of the overloaded method implementations invoked by the client application. The specialized utility functions in the SqlHelper class include:
Attachparameters: This function is used to connect all the 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 the properties of a command, such as a connection, a transaction environment, and so on.
ExecuteReader: This dedicated ExecuteReader implementation is used to open SqlDataReader objects through the appropriate CommandBehavior to most effectively manage the validity of connections associated with the reader.
SqlHelperParameterCache Class Implementation Details
The parameter array is cached in the private Hashtable. Internal replication of parameters retrieved from the cache so that client applications can change parameter values and perform other operations without affecting the cached parameter array. Private Shared function cloneparameters can accomplish this.

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

Problems
What new features are included in this release?
The RTM version contains the following new features and changes compared to the Data Access Application Block Beta 2.0 version:
The transaction type overload of the SqlHelper class method no longer requires a SqlConnection parameter. In this release, the connection information is derived from the SqlTransaction object, so you do not have to include SqlConnection object parameters in the method signature.
The GetSpParameterSet method now uses the DeriveParameters method of the Ado.net CommandBuilder class to determine the parameters required by the stored procedure. This is more efficient than the Beta 2.0 version of querying the database directly to retrieve information.
Can you use the XCOPY deployment method to deploy the Data Access Application Block assembly?
OK. The Microsoft.ApplicationBlocks.Data.dll assembly can be deployed using XCOPY after compilation.
When should you use the ExecuteDataset method, and when should you use the ExecuteReader method?
This question is actually when you should return multiple rows of data in the DataSet object and when you should use DataReader. The answer depends on the specific needs of your application and your trade-offs between flexibility and raw performance. The dataset provides you with a flexible and disconnected view of the data, while DataReader provides you with a performance-only, read-only, forward-only cursor. For a comprehensive comparison of datasets and DataReader, see the Data Access Architecture Guide (English).
How do I use ExecuteDataset to return a dataset that contains more than one table?
You can retrieve a dataset that contains more than one table by creating a stored procedure that returns multiple rowsets (by executing multiple SELECT statements or nesting calls to other stored procedures) and using the ExecuteDataset method to perform the procedure.
For example, suppose 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 to make nested calls to these procedures, as shown in the following code example.
CREATE PROCEDURE getcategoriesandproducts
As
BEGIN
EXEC getcategories
EXEC getproducts
End
Executing this master stored procedure by using the ExecuteDataset method returns a Dateset containing two tables: one table contains categorical data and the other contains product data.
Note: The ExecuteDataset method does not provide a method for specifying a custom name for the returned table. The first table is always numbered 0, the name is table, the second table is numbered 1, the name is Table1, and so on.
Are there any other application blocks?
Data Access Application Block is one of several application blocks that will be released. These application blocks can address common problems that developers encounter with different projects. They can be quickly and easily inserted into a. NET application.

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

Feedback and Support
If you have any questions, comments, and suggestions for Data Access Application block, please send an email to devfdbck@microsoft.com and we will provide feedback in a timely manner.
Application Blocks for. NET is designed to assist in developing. NET distributed applications. Sample code and documentation are provided as is. Although tested and considered to be a stable set of code, we do not support it as traditional Microsoft products do.
We also created a newsgroup to help you use the application Blocks for. NET (. NET application Block). Through newsgroups, you can support expert advice to peers, colleagues, and Microsoft in an online open forum.
Others can also benefit from your questions and comments, and our development team will view newsgroups every day:
Newsgroups: Web-based readers
http://msdn.microsoft.com/newsgroups/loadframes.asp?icp=msdn&slcid=us&newsgroup= Microsoft.public.dotnet.distributed_apps (English)
Newsgroups: NNTP Readers
News://msnews.microsoft.com/microsoft.public.dotnet.distributed_apps (English)
Do you want to learn and take advantage of. NET features? You are welcome to work alongside technical experts from Microsoft Technology Centers, and you will learn the best development experience. For more information, please visit http://www.microsoft.com/business/services/mtc.asp (English).
Do you need more help? Visit the new Customer Support Service advisory services, which can meet your small 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 the data Access Application block is based on the best development experience and common design principles discussed in the data access in. NET Architecture Guide (English). Please read the guide for more information about data access.