Microsoft Application Blocks for .NET
Data Access Application Block Overview
Chris Brooks, Graeme Malcolm, Alex Mackman, Edward JezierskiMicrosoft Corporation
April 2002
Summary: Data Access Application Block is a .NET component, an optimized data access code, can help 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 application to reduce the number of custom code that needs to be created, tested, and maintained. You can download the full C # and Visual Basic .Net source code and integrated documentation.
Introduction
Are you engaged in the design and development of .NET application data access code? Do you think you always write 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 row? If so, Microsoft® Data Access Application Block for .NET is designed for you.
Data Access Application Block will access the performance of the Microsoft SQL ServerTM database to be packaged together with the best experience in resource management. You can easily use it as a structural block in your own .NET application, reducing the number of custom code that needs to create, test, and maintained from the page.
In particular, Data Access Application Block can help you:
Call the stored procedure or SQL text command. Specify parameter details. Return to SqlDataReader, Dataset, or XmlReader object.
For example, in an application that references the Data Access Application Block, you can simply call the stored procedure in a row 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 the successful .NET application for detailed research. 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 established for accurate 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:
What are the contents of Data Access Application Block Download and install Data Access Application Block
Use Data Access Application Block
Internal design
common problem
Feedback and support
Partner
What are the contents of Data Access Application Block
Provides the source code for Data Access Application Block and Quick Start Sample Applications, you can use these applications to test their features. The Data Access Application Block also includes a comprehensive document that helps you use and understand the code provided.
Visual Studio .NET project
Provides Microsoft Visual Basic® .NET and Microsoft Visual C #TM source code for Data Access Application Block, and quick start sample client applications for each language, you can use these applications to test common scenarios. This helps to deepen your understanding of the working principle of 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. This assembly includes a SQLHELPER class (which contains core functions for performing database commands) and a SQLHELPARPARETERCACHE class (providing parameter discovery and cache function).
Documentation
Data Access Application Block's documentation mainly includes the following:
Develop applications using the Data Access Application Block. This section includes a quick start example, which contains a variety of common usage, which can 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 principle information so that users can understand the design and implementation of Data Access Application Block. Deploy and run. This section includes installation information, which contains deployment and update options and information related to security. reference. This section contains integrated API reference, which details the classes and interfaces that make up the Data Access Application Block.
System Requirements
To run the Data Access Application Block, you need to meet the following requirements:
Microsoft Windows® 2000, Windows XP Professional .NET Framework SDK (English) 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 get a Windows installer file that contains the signed Data Access Application Block assembly and the integrated document.
The installation process will create a Microsoft Application Blocks for .NET (Microsoft Application Block) submenu in your "Programs" menu. There is a Data Access submenu in this submenu, including options for starting documents and options for launching the Data Access Application Block Visual Studio .NET solution. Go to MSDN Downloads (English) to download.
Use Data Access Application Block
This section discusses how to use the Data Access Application Block 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 send many 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 who run the stored procedure). 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 five SHARED (Visual Basic) or Static (C #) methods, which are: ExecutenonQuery, ExecuteDataSet, ExecuteReader, ExecuteScalar, and ExecutexmlReader. Each method implemented provides a group of consistent overloads. 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. 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, other methods other than EXECUTEXMLREADER provide another overload: allowing connection information as a connection string instead of a connection object, as shown in the following method signs:
[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 SqlDataReader object is different.
XmlReader object
The XMLReader is not provided with a method of automatically shutting up the connection. If the client passes the connection string, then when the client is completed
After the XMLReader operation, it will not be shut down and
XmlReader associated connection objects.
By referring to the Data Access Application Block assembly and imports Microsoft.ApplicationBlocks.Data namespace, you can easily write code for any SQLHELPER class method, as shown in the following code example: [Visual Basic]
Imports Microsoft.ApplicationBlocks.Data
[C #]
Using Microsoft.ApplicationBlocks.data;
After importing 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 SQLHELPERPARAMETERCACACHE 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. Getsparameterset. 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.
To retrieve parameters from the cache, use the getCachedParameterSet method. This method returns an array of SQLParameter objects that have initiated the name, value, direction, and data type of parameters in the cache (corresponding to the connection string of the method and the command text).
note:
The connection string used as the key to 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]
'Initializing the connection string and command text
'They will make up the keys used to store and retrieve parameters
Conn_string as string = _
"Server = (local); database = northwind; integrated security = true;"
DIM SQL AS STRING = _
"Select ProductName from Products" _ _ _
"Where category = @ cat and support = @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
'Retrieving parameters from the cache
Dim StoredParams (1) AS SQLPARAMETER
StoredParams = SQLHELPARPARETERCACHE.GETCAADPARETERSET (_
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 will make 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 support = @SUP";
// Cache parameter
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 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 the command
DataSet 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. An overloaded method called GetSpparameterSet 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's SQLCommandBuilder class from the internal retrieval and add them to the cache for subsequent search 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]
'Initializing the connection string and command text
'They will make up the keys used to store and retrieve parameters
Conn_string as string = _
"Server = (local); database = northwind; integrated security = true;"
DIM spname as string = "Salesbycategory"
'Retrieving parameters
Dim StoredParams (1) AS SQLPARAMETER
StoredParams = SQLHELPARPARETERCACHE.GETSPAARETERSET (_
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 will make the 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 = SQLHELPARPARETERCACHE.GETSPAARETERSET (
CONN_STRING, SPNAME);
StoredParams [0] .value = "beverages";
StoredParams [1] .value = "1997";
// Use the parameters in the command
DataSet DS;
DS = SQLHELPER.EXECUtedataSet (conn_string,
CommandType.StoredProcedure,
SPNAME, StoredParams);
Internal design
The Data Access Application Block contains a complete source code and a comprehensive guide on 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 method implemented in the SQLHELPER class includes: 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.
In addition to these public methods, the SQLHELPER class also includes some dedicated functions for managing parameters and ready to execute. All commands are executed by the SQLCommand object regardless of the client call. All parameters must be added to the parameters collection before the SQLCommand object is executed, and the connection, commandtype, commandtext, and transaction properties must be set correctly. The dedicated function in the SQLHELPER class is primarily used to provide a consistent way to send commands to the SQL Server database without considering the overload method of the client application call. The dedicated utility functions in the SQLHELPER class include:
AttachParameters: This function is used to connect all necessary SQLParameter objects to running SQLCommand. AssignParameterValues: This function is used to assign a value for the SQLParameter object. PrepareCommand: This function is used to initialize the properties of the command (such as a connection, transaction environment, etc.). ExecuteReader: This dedicated ExecuteReader implements open SQLDataReader objects via the appropriate Commandbehavior to manage the validity period of the connection associated with the reader.
SQLHELPERPARAMETERCACHE class implementation details
The parameter number is bundled in a dedicated HashTable. Copying the parameters retrieved from the cache so that the client application can change the parameter value and other operations without affecting the cache. Dedicated Shared Functions CloneParameters can be achieved.
common problem
What new addresses are included in this release?
This RTM version contains the following new features and changes compared to the Data Access Application Block Beta 2.0 version:
The transaction overload of the SQLHELPER class is no longer needed to 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. Now, the GetSpparameterSet method uses the DeriveParameters method for the ADO.NET CommandBuilder class to determine the parameters required for the stored procedure. This is more efficient to retrieve information directly by querying the database in the beta version 2.0.
Can I deploy a Data Access Application Block assembly using the XCOPY deployment method?
can. Microsoft.ApplicationBlocks.Data.dll The assembly can be deployed using XCOPY after compiling. When should I use an ExecuteTASET method, when should I use the ExecuteReader method?
This problem is actually when it should return multiple data lines in the DataSet object, 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 you with a flexible and disconnection of data, while DataReader provides you with excellent performance, read-only, just ahead. For a comprehensive comparison of DataSet and DataReader, see Data Access Architecture Guide.
How do I use executedataSet to return a dataset containing multiple tables?
By creating a stored procedure that can return multiple rows (nested by performing multiple SELECT statements or making the other stored procedure), and use the ExecuteTaset method to perform the process, you can retrieve the data sets 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 Execute this primary stored process will return a DateSet, which contains two tables: a table contains classified data, and the other table contains product data.
note:
The executeDataSet method is not provided to specify 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, the name is
TABLE1, so on.
Are there other application blocks?
The 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 application.
Feedback and support
If you have any questions, comments, and suggestions for Data Access Application Block, please send emails to devfdbck@microsoft.com, we will provide feedback in time.
Application Blocks for .NET is intended to assist in developing .NET distributed applications. The sample code and document are provided as it is. Although it has been tested and considered a stable code set, we are not supported by traditional Microsoft products.
We also created a newsgroup that can help you use the Application Blocks for .NET (.net application block). You can consult your colleagues, colleagues, and Microsoft support from the Journalic Group's Open Forum.
Others can benefit from your questions and comments, our development team will view the newsgroup daily: News Group: WEB-based readers http://msdn.microsoft.com/newsgroup/loadframes.asp?icp=msdn&slcid = US & news = microsoft.public.dotnet.distribute_apps: nntp reader news: //msnews.microsoft.com/Microsoft.public.dotNet.distribute_apps (English)
Do you want to learn and use the .NET's feature? You are welcome to work side by side with Microsoft Technology Centers, you will learn best development experience. For more information, please visit http://www.microsoft.com/business/services/mtc.asp.
Do you need more help? Visit the new customer support service Advisory Services, which can meet your small-scale consultation needs. For more information on Advisory Services, please visit http://support.microsoft.com/default.aspx?scid=fh;n-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 Data Access in .NET Architecture Guide (English). Please read this guide to learn more about data access.
Partner
Thanks to the following contributors and reviewers: (. Vorsite Corp) Susan Warren, Brad Abrams, Andy Dunn, Michael Day, Mark Ashton, Gregory Leake, Steve Busby, Kenny Jones, David Schleifer, Andrew Roubin, Jeffrey Richter (Wintellect), Bernard Chen (Sapient) and Matt Drucker (Turner Broadcasting).
At the same time, I would like to thank the following members of the content group: Tina Burden (EntireNet), Shyleenet Ramamurthy (Infosys Technologies Ltd) and Filiberto Selvas Patino.
