Http://www.microsoft.com/china/msdn/library/EnterpriseDevelopment/builddistapp/vsdnbdadaab_rm.mspx
Data Access Application Block for .NET
Release Date: 6/18/2004
| Update Date: 6/18/2004
Chris Brooks, Graeme Malcolm, Alex Mackman, Edward Jezierski, Jason Hogg, Diego Gonzalez (Lagash), Pablo Cibraro (Lagash) and Julian Cantore (Lagash)
Microsoft Corporation
Summary: Data Access Application Block is a .NET component that contains an optimized data access code that helps users call stored procedures and send SQL text commands to the SQL Server database. It returns SqlDataReader, Dataset, and XmlReader objects. You can use it as a structural block in your own .NET-based application to reduce the number of custom code that needs to be created, tested, and maintained. You can download the full Visual C # and Visual Basic .Net source code and integrated documentation.
Note that this document references some features available in version 2.0 of the data access application block.
Introduction
Are you engaged in the design and development of application data access code based on .NET? Have you had this feeling - always feel that you have written the same data access code repeatedly? Have you ever packaged in the Helper function in a helper function to be able to call the stored procedure in a line? If so, then Microsoft? Data Access Application Block for .NET is what you need.
Data Access Application Blocks will be packaged with best practices in the performance and resource management of the Microsoft SQL Server database. You can easily use it as a structural block in your own .NET-based application, thereby reducing the number of custom code that needs to be created, test, and maintained.
In particular, data access application blocks can help you:
• Call the stored procedure or SQL text command. • Specify parameter details. • Return to SqlDataReader, Dataset, or XmlReader object. • Use a strong type of DataSet.
For example, in an application that references the data access application block, you can call the stored procedure and generate a DataSet in a row code, 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: The .NET application block is designed based on the successful .NET-based application. It is available in the form of source code, you can use it as it can be customized for your own application. The application block does not represent the development direction of the future Microsoft ADO.NET library. The Microsoft ADO.NET library is generated for exact control of data access behavior in various usage. The future ADO.NET version may use different models to implement this solution. The rest of this summary includes the following:
This page
What are the contents of the data access application blocks? Download and Install Data Access Application Block Use Data Access Application Block Internal Design FAQ Fastback and Support for More Information Partners
What are the contents of the data access application blocks?
Provides the source code for data access application blocks and Quick Start Sample Applications, you can use these applications to test their features. Data Access Application Blocks also include integrated documents that can help you use and understand the code provided.
Visual Studio .NET project
Provides the data access application block Microsoft Visual Basic® .NET and Microsoft Visual C # "source code, and quick start sample client applications for each language, you can use these applications to test universal scenarios. This helps to deepen your understanding of the working principle of the data access application block. 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 called Microsoft.ApplicationBlocks.Data.dll. The assembly includes a class called SQLHELPER (which contains core functions for executing database commands) and a class named SQLHELPARPARAMETERCACHE (it provides parameter discovery and cache function).
Documentation
Data Access Application Blocks The documentation mainly includes the following:
• Use data access application block development applications. This section contains some quick start examples covering many common use cases. These examples can help you start using data access application blocks quickly and easily. • Data Access Application Block Design and Implementation. This section includes background design principle information so that users can in-depth understanding of the design and implementation of data access applications. • Deploy and run. This section includes installation information, which contains deployment and update options and information related to security. • Reference. This section contains a complete API reference material, which describes the classes and interfaces that make up the data access application block.
System Requirements
To run the Data Access Application Block version 2.0, you need to meet the following conditions:
• Microsoft Windows? 2000, Windows XP Professional or Windows 2003 Operation System • Microsoft .NET Framework Software Development Kit (SDK) version 1.1, version 1.1 • Microsoft Visual Studio? 2003 Development System • Run SQL Server 7.0 or later database server
To run the Data Access Application Block version 1.0, you need to meet the following conditions:
• Microsoft Windows 2000 or Windows XP Professional • .NET Framework SDK RTM version • RTM version of Visual Studio .NET (recommended, but not required) • Running SQL Server 7.0 or later database server
Back to top
Download and Install Data Access Application Blocks
You can get a Windows installer file that contains the signed data access application block assembly and integrated document.
The installation process will create a Microsoft Application Blocks for .NET submenu in your Programs menu. On the Microsoft Application Blocks for .NET submenu, there is a Data Access submenu that contains options for launching a document and launching the Data Access Application Block Visual Studio.net solution. You can download the Data Access Application Block version 1.0 from Microsoft Download Center from Microsoft Download Center
You can download the Data Access Application Block version 2.0 from Microsoft Download Center
Back to top
Use data access application blocks
This section discusses how to use data access application blocks to perform database commands and management 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 perform a variety of different types of commands to the SQL Server database.
The SQLHELPARPARETERCACHE class provides command parameter cache functionality that can be used to improve performance. This class is internally used by many Execute methods (especially those that only perform memory procedures). Data Access Clients can also use it directly to cache specific parameter sets of a particular command.
Use the SQLHELPER class to execute the command
The SQLHELPER class provides 13 Shared (Visual Basic) or Static (C #) methods, as shown above. Each method implemented provides a group of consistent overloads. This provides a well-defined model using the SQLHELPER class to perform commands, while providing the necessary flexibility for developers to access data. Overloading of each method supports different method parameters, the developer can determine how to pass connection, transaction, and parameter information. All methods implemented in the 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, all methods (except Executexmlreader, UpdatedataSet, and CreateCommand) provide overload to allow connection information as a connection string instead of being transmitted as 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 the XMLReader object does not provide a method of automatically shutting down the connection with the SqlDataReader object. If the client passes the connection string, the connection object associated with XmlReader cannot be turned off when the client completes the operation of the XMLReader. UpdatedataSet uses existing connections, and CreateCommand uses the SqlConnection object.
Access the application block assembly by reference to the data and import Microsoft.ApplicationBlocks.Data namespace, you can 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 the 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 SQLHELPERPARETERCACHE class management parameters
The SQLHELPARPARETERCACACHE class provides three public sharing methods that can be used to manage parameters. They are:
• CacheParameterSet. Used to store the SQLParameters array into the cache. • GetCachedParameterset. A copy of the parameter array used to retrieve the cache. • GetSpparameterSet. An overload method for retrieving the corresponding parameters of the specified stored procedure (first query a database, then cache the result to query the query).
Cache and retrieval parameters
The SQLParameter object array can be cached by using the CacheParameterSet method. This method creates a key by connecting the connection string and command text, and then stores the argument array in the Hashtable.
In order to retrieve parameters from the cache, the GetCachedParameterSet method is used. This method returns an array of SQLParameter objects that have initiated the name, direction and data type of parameters in parameters in a specific cache (which pass the cache and the connection string of the method and the command text).
Note The connection string used as the key of the parameter set matches through a simple string. The connection string used to retrieve parameters from the getCachedParameterSet must be identical to the connection string used to store these parameters through the cacheparameterSet. Syntax different connection strings will not be considered to match even if the semantics are the same.
The following code shows how to cache and retrieve the parameters of the Transact-SQL statement using the SQLHELPERPARAMETERCACHE class.
[Visual Basic]
'Initialize the Connection String and Command Text
'There Will Form The Key Used to Store and Retrieve The Parameters
Conn_string as string = _
"Server = (local); database = northwind; integrated security = true;"
DIM SQL AS STRING = _
"Select ProductName from Products Where CategoryId = @ cat" _ "and support = @SUP"
'Cache the 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 the Parameters from the cache
Dim StoredParams (1) AS SQLPARAMETER
StoredParams = SQLHELPERPARAMETERCACHE.GETCECHEDPARETERSET (
CONN_STRING, SQL)
StoredParams (0) .value = 2
StoredParams (1) .value = 3
'Use the parameters in a commnd
DIM DS AS Dataset
DS = SQLHELPER.EXECUtedataSet (conn_string, commandtype.text, sql, _
StoredParams)
[C #]
// Initialize the connection string and command text
// THESE WILL FORM The Key Used to Store and Retrieve The Parameters
Const string conn_string =
"Server = (local); database = northwind; integrated security = true;"
String spname =
"SELECT ProductName from Products Where CategoryId = @ cat"
"And support = @SUP";
// cache the parameters
Sqlparameter [] paramstostore = new sqlparameter [2];
Paramstostore [0] = New SqlParameter ("@ cat", sqldbtype.int);
Paramstostore [1] = New SqlParameter ("@ sup", sqldbtype.int);
SQLHELPARPARETERCACHE.CACHEPARAMETERSET (CONN_STRING, SQL,
ParamstoStore;
// Retrieve The Parameters from the cache
SQLParameter StoredParams = New Sqlparameter [2];
StoredParams = SQLHELPERPARAMETERCACHE.GETCECHEDPARETERSET (
CONN_STRING, SQL);
StoredParams (0) .value = 2;
StoredParams (1) .value = 3;
// use the parameters in a commandDataSet DS;
DS = SQLHELPER.EXECUtedataSet (conn_string,
CommandType.StoredProcedure,
SQL, StoredParams;
Retrieve stored procedure parameters
SQLHELPERPARETERCACHE also provides a method for retrieving parameter arranging arguments for a particular stored procedure. A overload method called GetSpparameters provides this feature that contains two implementations. This method attempts to retrieve the parameters of a specific stored procedure from the cache. If these parameters have not been cached, use the .NET SQLCOMMAndBuilder class from the internal retrieval and add them to the cache for subsequent requests. Then, specify the corresponding parameter setting for each parameter, and finally these parameters are returned to the client in an array. The following code shows how to retrieve the parameters of the SalesbyCategory stored procedure in the Northwind database.
[Visual Basic]
'Initialize the Connection String and Command Text
'There Will Form The Key Used to Store and Retrieve The Parameters
Conn_string as string = _
"Server = (local); database = northwind; integrated security = true;"
DIM spname as string = "Salesbycategory"
'Retrieve the parameters
Dim StoredParams (1) AS SQLPARAMETER
StoredParams = SQLHELPARPARETERCACHE.GETSPAAMETERSET (CONN_STRING, SPNAME)
StoredParams (0) .value = "beverages"
StoredParams (1) .value = "1997"
'Use the parameters in a commnd
DIM DS AS Dataset
DS = SQLHELPER.EXECUtedataSet (conn_string, _
CommandType.StoredProcedure, _
SpName, StoredParams
[C #]
// Initialize the connection string and command text
/ THESE WILL FORM The Key Used to Store and Retrieve The Parameters
Const string conn_string =
"Server = (local); database = northwind; integrated security = true;"
String spname = "Salesbycategory";
// Retrieve The Parameters
SQLParameter StoredParams = New Sqlparameter [2];
StoredParams = SQLHELPARPARETERCACHE.GETSPAARETERSET (conn_string,
SpName);
StoredParams [0] .value = "beverages";
StoredParams [1] .value = "1997";
// use the parameters in a commandDataSet DS;
DS = SQLHELPER.EXECUtedataSet (conn_string, commandtype.storedProcedure,
SPNAME, StoredParams);
Back to top
Internal design
Data Access Application Blocks contain complete source code and comprehensive guidelines for its design. This section describes the details about the main implementation.
SQLHELPER class implementation details
SQLHELPER class is used to encapsulate data access functions through a set of static methods. This class cannot be inherited or instantiated, so it is declared as an unusually inherited class containing a dedicated constructor.
Each method implemented in the SQLHELPER class provides a consistent overload. This provides a good way to use the SQLHELPER class to perform commands, while providing the developer's way to access data, provide the necessary flexibility. Overloading of each method supports different method parameters, the developer can determine how to pass connection, transaction, and parameter information. The methods implemented in the SQLHELPER class include:
• ExecutenonQuery. This method is used to perform commands that do not return any rows or values. These commands are usually used to perform database updates, but can also be used to return output parameters for stored procedures. • EXECUTEREADER. This method is used to return the 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 of the first line returned by the command. • ExecutexmlReader. This method returns the XML fragment of the for XML query. • FillDataSet. This approach is similar to the executedataSet, which is different from the pre-existing DataSet, allowing additional tables to be added. • UpdatedataSet. This method updates the DataSet using an existing connection and user-specified update command. It is usually used in conjunction with the CreateCommand command. • CREATECOMMAND. This method allows the provision of stored procedures and optional parameters to simplify the creation process of the SQL command object. This method is usually used in conjunction with UpdatedataSet. • ExecuteNonQuerytypedParams. This method uses data rows instead of parameters to perform non-inquiry operations. • ExecutedataSettypedParams. This method uses data rows instead of parameters to perform DataSet creation operations. • ExecuteReadertypedParams. This method uses data rows instead of parameters to return to the data reader. • ExecuteScalrtypedParams. This method uses a data line instead of parameters to return scales. • ExecutexmlReadertypedParams. This method uses data rows instead of parameters to perform XMLReader.
In addition to these public methods, the SQLHELPER class also contains some dedicated functions for managing parameters and preparing commands to be executed. All commands are executed by using SQLCommand objects regardless of the client call. Before performing this SQLCommand object, you must add all parameters to its parameters collection and must be set accordingly, and the CONNECTION, CommandType, CommandText, and Transaction properties must be set accordingly. The dedicated function in the SQLHELPER class is primarily used to provide a consistent way to execute commands for the SQL Server database without considering the overloading method called by the client application. The dedicated utility functions in the SQLHELPER class include:
• attachparameters. This function is used to attach any required SQLParameter object to SQLCommand to be executed. • AssignParameterValues. This function is used to assign a value to the SQLParameter object. PREPARCOMMAND. This function is used to initialize the properties of the command (such as connection, transaction context, etc.). • EXECUTEREADER. This dedicated implementation of ExecuteReader is used to open the SqlDataReader object and use the corresponding CommandBehavior to manage the lifetime associated with the reader in the most efficient way. SQLHELPERPARAMETERCACHE class implementation details
The parameter number is dedicated to a dedicated HashTable. The parameters retrieved from the cache are replicated inside so that the client application can change the parameter value and other operations without affecting the cache parameter array. The dedicated shared function cloneParameters is used to achieve this.
Back to top
common problem
What new addresses are included in this release?
The 2.0 version of the Data Access Application Block contains the following new features:
• Using the FillDataSet method supports strong type DataSet • Supports submitting the update to the DataSet back to Database • Provides an additional Helper method and support the DataRow type parameters • You must repair a small error
This RTM version contains the following new features and changes compared to the Data Access Application Block Beta 2.0 version:
• Transaction overloads of the SQLHELPER class no longer need a SQLConnection parameter. In this release, the connection information is derived from the SQLTransaction object, so it is not necessary to include the SQLConnection object parameters in the method signature. • The GetSpparameterSet method is now using the ADO.NET CommandBuilder class's DeriveParameters method to determine the parameters required for stored procedures. This is more efficient than the Query database directly in the beta version 2.0.
Can I deploy a data access application block assembly using the XCOPY deployment method?
can. After compiling, you can use XCOPY to deploy Microsoft.ApplicationBlocks.Data.dll assembly.
When should I use an ExecuteTASET method, when should I use the ExecuteReader method?
This problem is actually when it should return multiple lines of data in the DataSet object, and when you should use DataReader. The answer depends on the specific needs of your application, and your hits you flexibly and the original performance. DataSet provides a flexible, disconnected relationship view, while DataReader provides a cursor with high performance, read-only function, and only enter.
If you use the parameter array to return the output value, remember that you must extract these values (using the SQLParameter value attribute) after turning off the SqlDataReader object.
For comprehensive comparisons for DataSets and DataReaders, see "Data Access Structure Guide", URL: http://msdn.microsoft.com/library/default.asp? Url = / library / en-us / dnbda / html / daag .asp.
When should I use the execute * typedparams method?
These methods are designed to use support for strong type DataSet. Use them to pass through the parameters of the stored procedure, rather than being passed as arrays that are typically mapped to all parameters in all domains.
How do I use executedataSet to return a dataset containing multiple tables? By creating a stored procedure that can return multiple rows (by performing multiple SELECT statements, or by nested other stored procedures), and use the ExecuteTaset method to perform this procedure, you can retrieve the DataSet containing multiple tables.
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 nesternly call these processes, as shown in the following code example.
Create Procedure GetcategoriesandProducts
AS
Begin
Exec getCategories
EXEC GETPRODUCTS
End
Using the ExecuteDataSet method to perform this primary storage process, you return a single DataSet containing two tables, a table contains a category data, and the other table contains product data.
Note The executeDataSet method does not provide a method of assigning a custom name to the returned table. The number of the first table is always 0, and is named Table, the number of the second table is always 1, and is named Table1, and so on.
Are there other application blocks?
Data Access Application Block is one of the upcoming application blocks. These application blocks can solve the common problems encountered in different projects. They can be quickly and easily inserted into the .NET-based application.
Back to top
Feedback and support
The .NET application block is used to promote the development of .NET distributed applications. The sample code and document are provided as it is. Charge support can be obtained by Microsoft product support.
To learn more about .NET Best Practices, please visit the Patterns & Practices page.
To participate in online collaboration development environments related to this topic, join the GotdotNET workspace: Microsoft Patterns & Practices Data Access for .NET Workspace. In the community of the workspace, you can meet your problems, suggestions, and customization of data access blocks.
Do you have any questions, comments and suggestions? To feedback this article, please send an email to: devfdbck@microsoft.com Contact us.
Back to top
More information
Data Access Application Block is based on "Net Data Access Architecture Guide" (URL is http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/daag. The best practices and general design principles discussed in ASPs are designed and developed. Please read this guide to learn more about data access.
Back to top
Partner
Thank you very much following contributors and reviewers: Susan Warren, Brad Abrams, Andy Dunn, Michael Day, Mark Ashton, Gregory Leake, Steve Busby, Kenny Jones, David Schleifer, Pablo Castro, Michael Pizzo, Paul Andrew, Cihan Biyikoglu, Eugenio Pace , Roger Lamb, Nick Takos, Andrew Roubin (Vorsite Corp.), Jeffrey Richter (Wintelle,), and Matt Drucker (Turner Broadcasting). At the same time, I would also like to thank the Content Working Group: Tina Burden (EntireN), ShyleNologies Ltd, Filiberto Selvas Patino, Roberta Leibovitz, and Colin Campbell (Modiland Computation LLC).
Go to the original English page
