Data Access Application Block for .NET
Chris Brooks, Graeme Malcolm, Alex Mackman, Edward Jezierski, Jason Hogg, Diego Gonzalez (Lagash), Pablo Cibraro (Lagash) and Julian Cantore (Lagash) Microsoft Corporation
April 2002
Updated June 2003
Summary: The Data Access Application Block is a .NET component that contains optimized data access code that will help you call stored procedures and issue SQL text commands against a SQL Server database It returns SqlDataReader, DataSet, and XmlReader objects You can use it.. as a building block in your own .NET-based application to reduce the amount of custom code you need to create, test, and maintain. The download provides full Visual C # and Visual Basic .NET source code and comprehensive documentation.
.
Introduction
Are you involved in the design and development of data access code for .NET-based applications? Have you ever felt that you write the same data access code again and again? Have you wrapped data access code in helper functions that let you call a stored Procedure in One Line? IF SO, The Microsoft® Data Access Application Block for .NET is for you.
The Data Access Application Block encapsulates performance and resource management best practices for accessing Microsoft SQL Server ™ databases. It can easily be used as a building block in your own .NET-based application. If you use it, you will reduce the amount of custom Code you need to create, test, and mosttain.
Specifically, The Data Access Application Block Helps you:
· Call Stored Procedures Or SQL Text Commands.
· Specify Parameter Details. · Return SqlDataReader, Dataset, or XmlReader Objects.
· Use strongly typed datasets.
For Example, In An Application That References The Data Access Application Block, You Can Call A Stored Procedure and Generate A DataSet in a Single 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: This Application Block for .NET design is based on reviews of successful .NET-based applications It is provided as source code that you can use as-is or customized for your application It is not an indication of future direction for Microsoft.. ADO.NET LIBRARIES, WHICH Are Built for Fine-Graped Control of Data Access Behavior in a Wide Range of Ado.Net May Address this Scenario with a different model.
The Remainder of this overview is Divided Into The Following Sections:
What Does The Data Access Application Block Include?
Downloading and Installing The Data Access Application
Using The Data Access Application Block
Internal Design
FREQUENTLY Asked Questions
Feedback and support
More information
Collaborators
What Does The Data Access Application Block Include?
The source code for the Data Access Application Block is provided, together with Quick Start sample applications that you can use to test its functionality. The Data Access Application Block also includes comprehensive documentation to help you work with and learn about the code provided.The Visual Studio .NET Projects
Microsoft Visual Basic® .NET and Microsoft Visual C # ® source code is supplied for the Data Access Application Block, together with a Quick Start Samples client application in each language that you can use to test common scenarios. This helps increase your understanding of how the Data Access Application Block Works. You are Also Free To Customize The Source Code To Suit Your Requirements.
The Visual Basic and C # Microsoft.ApplicationBlocks.Data projects can each be compiled to produce an assembly named Microsoft.ApplicationBlocks.Data.dll. This assembly includes a class called SqlHelper, which contains the core functionality for executing database commands, and a second class Called SQLHELPERPARETERCACHE, WHICH Provides Parameter Discovery and Caching FunctionAlity.
The Documentation
The Documentation for The Data Access Application Block Includes The Following Main Sections:
· Developing Applications with the Data Access Application Block. This section includes quick start samples that cover a range of common use cases. These will help you start to use the Data Access Application Block quickly and easily.
· Design and Implementation of the Data Access Application Block. This section includes background design philosophy information that provides insights into the design and implementation of the Data Access Application Block.
· Deployment and Operations. This section includes installation information that covers deployment and update options, as well as security related information. · Reference. This is a comprehensive API reference section that explains the classes and interfaces which comprise the Data Access Application Block.
System Requirements
To Run Version 2.0 of Data Access Application Block, you need the folload:
· Microsoft Windows® 2000, Windows XP Professional, or Windows 2003 Operating System
· Microsoft .NET Framework Software Development Kit (SDK), Version 1.1, Version 1.1
· Microsoft Visual Studio® 2003 Development System
· A Database Server Running SQL Server 7.0 or Later
To Run The 1.0 Version of The Data Access Application Block, you need the folload:
Microsoft Windows 2000 or Windows XP Professional
· The RTM Version of The .NET Framework SDK
· The RTM Version of Visual Studio .NET (Recommended But Not Required)
· A Database Server Running SQL Server 7.0 or Later
Downloading and Installing The Data Access Application Block
A Windows Installer File Containing The Signed Data Access Application Block Assembly and Comprehensive Documentation IS AVAILABLE.
The install process creates a Microsoft Application Blocks for .NET submenu on your Programs menu. On the Microsoft Application Blocks for .NET submenu, there is a Data Access submenu that includes options to launch the documentation and to launch the Data Access Application Block Visual Studio .NET SOLUTION.
You can Download Version 1.0 of the data access application block from the microsoft download center.you can Download Version 2.0 of the data access application block from the microsoft download center
Using The Data Access Application Block
THIS Section Discusses How To Use The Data Access Application Block to Execute Database Commands and Manage Parameters. The Main Elements of The Data Access Application Block Are Illustrated In Figure 1.
Figure 1. Data Access Application Block
.
The SqlHelperParameterCache class provides command parameter caching functionality used to improve performance. This is used internally by a number of the Execute methods (specifically, the overloads that are designed to execute only stored procedures). It can also be used directly by the data access client To Cache Specific Parameter Sets for Specific Commands.
Executing Commands with the sqlhelper class
The SqlHelper class provides 13 Shared (Visual Basic) or static (C #) methods as shown in the diagram above. Each of the methods implemented provides a consistent set of overloads. This provides a well defined pattern for executing a command by using the SqlHelper class , while giving developers the necessary level of flexibility in how they choose to access data. The overloads provided for each method support different method arguments, so developers can decide how connection, transaction, and parameter information should be passed. All of the 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 other than ExecuteXmlReader, UpdateDataset, and CreateCommand provide overloads to allow connection information to be passed as a connection string, rather than as a connection object, as shown in the following method signatures:
[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 The ExecuteXmlReader does not support a connection string because-unlike SqlDataReader objects-an XmlReader object does not provide a way to close connections automatically when the XmlReader is closed. Clients that passed a connection string would have no way of closing the connection object associated with .
You can write code that uses any of the SqlHelper class methods simply by referencing the Data Access Application Block assembly and importing the Microsoft.ApplicationBlocks.Data namespace, as illustrated in the following code sample.
[Visual Basic]
Imports Microsoft.ApplicationBlocks.Data
[C #]
Using Microsoft.ApplicationBlocks.data;
After the namespace has been imported, you can call, as illustrate in the folload code sample.
[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");
Managing parameters with the sqlhelperparametercache class
The SQLHELPARPARETERCACHE CLASS Provides Three Public Shared Methods That Can Be Used to Manage Parameters. Theese Methods Are:
· CacheParameterSet. Used to store an array of sqlparameters in the cache.
· GetCachedParameterset. Used to retrieve a copy of a cached parameter array.
GetSpparameterset. An overloaded method used to retrieve the appropriate parameters for a specified stiled procedure by querying the database forfuture queries.
Caching and Retrieving Parameters
An array of SqlParameter objects can be cached by using the CacheParameterSet method. This method creates a key by concatenating the connection string and command text, and then stores the parameter array in the Hashtable.
To retrieve the parameter from the cache, the GetCachedParameterSet method is used. This method returns an array of SqlParameter objects initialized with the names, directions, data types, and so on, of the parameters in the cache corresponding to the connection string and command text Passed to the method.
Note The connection string used as a key for the parameter set is matched using a simple string comparison. The connection string used to retrieve parameters from GetCachedParameterSet must be absolutely identical to the connection string used to store those parameters with CacheParameterSet. A syntactically different connection string Even if semantically equivalent, Will Not Result in An Exact Match.The Following Code Shows How Parameters for a Transact-Sql Statement Can Be Cached and Retrieved by 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 parametersconst 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 commnd
DataSet DS;
DS = SQLHELPER.EXECUtedataSet (conn_string,
CommandType.StoredProcedure,
SQL, StoredParams;
Retrieving Stored Procedure Parameters
SqlHelperParameterCache also provides a way to retrieve an array of parameters for a specific stored procedure. An overloaded method named GetSpParameterSet with two implementations provides this functionality. This method attempts to retrieve the parameters for the specified stored procedure from the cache. If the parameters are not cached, they are retrieved internally using the .NET SqlCommandBuilder class and added to the cache for subsequent requests. The appropriate parameter settings are then assigned for each parameter, before the parameters are returned in an array to the client. The following code shows how the Parameters for the Salesbycategory Stored Procedure In The Northwind Database Can Be Retrieved. [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 Parameterssqlparameter StoredParams = New Sqlparameter [2];
StoredParams = SQLHELPARPARETERCACHE.GETSPAARETERSET (conn_string,
SpName);
StoredParams [0] .value = "beverages";
StoredParams [1] .value = "1997";
// use the parameters in a commnd
DataSet DS;
DS = SQLHELPER.EXECUtedataSet (conn_string, commandtype.storedProcedure,
SPNAME, StoredParams);
Internal Design
.
SQLHELPER CLASS IMPLEMENTATION DETAILS
The SqlHelper class is designed to encapsulate data access functionality through a set of static methods. Because it is not designed to be inherited from or instantiated, the class is declared as a non-inheritable class with a private constructor.
Each of the methods implemented in the SqlHelper class provides a consistent set of overloads. This provides a well defined pattern for executing a command by using the SqlHelper class, while giving developers the necessary level of flexibility in how they choose to access data. The overloads PROVIDED For Each Method Support Different Method Arguments, So Developers CAN Decide How Connection, Transaction, and Parameter Information Should Be Passed. The Methods Implement in The Sqlhelper Class Are:
· ExecuteNonQuery. This method is used to execute commands that do not return any rows or values. They are generally used to perform database updates, but they can also be used to return output parameters from stored procedures.
· ExecuteReader. This method is used to return a SqlDataReader object that contains the resultset returned by a command. · ExecuteDataset. This method returns a DataSet object that contains the resultset returned by a command.
................................ ...CRITATION SCIENCE.
· ExecutexmlReader. This Method Returns An XML Fragment from a for XML Query.
· FillDataSet. This Method is Similar To ExecuteTedataset, Except this a pre-existing DataSet Can Be Passed in, Allowing Additional Tables To Be Added.
UpdatedataSet. This Method Updates a Dataset Using An Existing Connection and User-Specified Update Commands. It is type uspically used with createcommand.
CreateCommand. This Method Simmand The Creation of a SQL Command Object by Allowing A Stored Procedure and Optional Parameters To Be provides used with updateDataSet.
· ExecutenonQuerytypedParams. This Method Executes a Non-Query Operation Using A Data Row Instead of Parameters.
· ExecuteDataSettypedParams. This Method Executes a DataSet Creation Operation, Using A Data Row Instead of Parameters.
· ExecuteReadertypedParams. This Method Returns A Data Reader Using A Data Row Instead of Parameters.
· ExecutescalartypedParams. This Method Returns A Scalar Using A Data Row Instead of Parameters.
· ExecutexmlReadertypedParams. This Method Executes An XmlReader Using A Data Row Instead of Parameters.
In addition to the public methods, the SqlHelper class includes a number of private functions, which are used to manage parameters and prepare commands for execution. Regardless of the method implementation called by the client, all commands are executed by using a SqlCommand object. Before this SqlCommand object can be executed, any parameters must be added to its Parameters collection, and the Connection, CommandType, CommandText, and Transaction properties must be set appropriately. The private functions in the SqlHelper class are primarily designed to provide a consistent way to execute commands against a SQL Server database, regardless of the overloaded method implementation called by the client application The private utility functions in the SqlHelper class are:.. · AttachParameters A function used to attach any necessary SqlParameter objects to the SqlCommand being executed.
................. ..
PrepareCommand. A Function Used to Initialize The Properties of The Command, Such As ITS Connection, Transaction Context, And So ON.
· ExecuteReader. This private implementation of ExecuteReader is used to open a SqlDataReader object with the appropriate CommandBehavior to manage the lifetime of the connection associated with the reader most efficiently.
SQLHELPERPARAMETERCACHE CLASS IMPLEMENTATION DETAILS
Parameter arrays are cached in a private Hashtable. Internally, the parameters retrieved from the cache are copied so that the client application can change parameter values, and so on, without affecting the cached parameter arrays. A private shared function named CloneParameters is used for this Purpose.Frequently Asked Questions
What's new in this release?
The 2.0 Release of The Data Access Application Block Includes The Following New Features:
· Support for strongly Typed DataSets with the FillDataSet Method
· Support for committing updates to a dataset back to the database
Additional Helper Methods with support for DataRow Type Parameters
· Minor bug fixes
The RTM Release of The Data Access Application Block Includes The Following New Features and Changes from The Beta 2.0 Release:
· Transactional overloads of SqlHelper class methods no longer require a SqlConnection parameter. In this release, the connection information is derived from the SqlTransaction object, thus eliminating the need to include a SqlConnection object parameter in the method signature.
· The GetSpParameterSet method now uses the ADO.NET CommandBuilder class's DeriveParameters method to ascertain the parameters required by a stored procedure. This is more efficient than the technique used in the beta 2.0 release, where the information was retrieved by directly querying the database.
Can I use xcopy deployment to deploy the data access coplication block askASSEMBLIES?
YES. After IT I Compiled, The Microsoft, ApplicationBlocks.Data.dll Assembly Can Be Deployed Using Xcopy.
When should I use the ExecuteDataset method and when should I use the ExecuteReader method? The real question here is when should you return multiple rows of data in a DataSet object, and when should you use a DataReader. The answer depends on the needs of your particular application and your priorities in terms of flexibility versus raw performance. A DataSet gives you a flexible, disconnected, relational view of your data while a DataReader provides an extremely high performance, read only, forward only cursor.
If you use a parameter array to return output value, be aware, you must extract the value, you close the sqldataareader object.
For a Comprehensive Compaison of Datasets and DataReaders, See The Data Access Architecture Guide At http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/daag.asp.
When Should I Use the execute * typedparams methods?
These methods are designed to capitalize on the support for strongly typed DataSets. They allow you to pass in an entire typed data row as a parameter for a stored procedure, rather than an array of all the parameters that would normally map to all the fields of The Table.
How do I use executetaset to return a dataset containing multiple tables?
You can retrieve a DataSet containing multiple tables by creating a stored procedure that returns multiple rowsets (either by executing multiple SELECT statements or by making nested calls to other stored procedures), and executing it using the ExecuteDataset method.
For Example, Suppose You Have The Following Stored Procedures in Your Database.
Create Procedure Getcategories
AS
Select * from categories
Go
Create Procedure GetProducts
AS
Select * from ProductSyou CREATE A MASTER Stored Procedure That Makes Nested Calls To The Following Code Sample.
Create Procedure GetcategoriesandProducts
AS
Begin
Exec getCategories
EXEC GETPRODUCTS
End
Executing this master storeing the product data.
Note The ExecuteDataset method does not provide a way to assign custom names to the tables returned. The first table is always numbered 0 and named Table, the second is numbered 1 and named Table1, and so on.
Are there any other application blocks?
The Data Access Application Block is one of several Application Blocks that are being released. These Application Blocks solve the common problems that developers face from one project to the next. They can be plugged into .NET-based applications quickly and easily.
Feedback and support
The Application Blocks for .NET are designed to jumpstart development of .NET distributed applications. The sample code and documentation is provided "as-is." Support is available through Microsoft Product Support for a fee.
To Learn More About .NET BEST PRACTICES, PLEASE VIT THE PATTERNS & PRACTICCIES Web Page.
To participate in an online collaborative development environment on this topic, join the GotDotNet workspace:. Microsoft Patterns & Practices Data Access for .NET Workspace Please share your Data Access Block questions, suggestions, and customizations with the community in this workspace.
Questions? Comments? Suggestions? For feedback on the content of this article, please e-mail us at devfdbck@microsoft.com.
More InformationThe Data Access Application Block is designed and developed based on the best practices and general design principles discussed in the .NET Data Access Architecture Guide at http://msdn.microsoft.com/library/default.asp?url=/library/ EN-US / DNBDA / HTML / DAAG.ASP. Read this Guide to Learn More About Data Access.
Collaborators
Many thanks to the 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), Bernard Chen (Sapient), and Matt Drucker (Turner Broadcasting).
Thanks, also, to the content team: Tina Burden (Entirenet), Shylender Ramamurthy (Infosys Technologies Ltd), Filiberto Selvas Patino, and Roberta Leibovitz and Colin Campbell (Modeled Computation LLC)
