Microsoft® Application Blocks for .NET

Data Access Application Block Overview

Information in this document, including URL and other Internet Web site references, is subject to change without notice. Unless otherwise noted, the example companies, organizations, products, domain names, e-mail addresses, logos, people, places and events depicted herein are fictitious, and no association with any real company, organization, product, domain name, e-mail address, logo, person, place or event is intended or should be inferred. Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights under copyright, no part of this document may be reproduced, stored in or introduced into a retrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording, or otherwise), or for any purpose, without the express written permission of Microsoft Corporation. Microsoft, MS-DOS, Windows, Visual C#, Visual Basic, Visual C++, Visual Studio, and Win32 are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. © 2002 Microsoft Corporation. All rights reserved. Version 1.0 The names of actual companies and products mentioned herein may be the trademarks of their respective owners.

Contents Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 What Does the Data Access Application Block Include? . . . . . . . . . . . . . . . . . . . . . . . 3 The Visual Studio .NET Projects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 The Documentation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 System Requirements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4 Downloading and Installing the Data Access Application Block . . . . . . . . . . . . . . . . . . 5 Using the Data Access Application Block . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6 Executing Commands with the SqlHelper Class. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7 Managing Parameters with the SqlHelperParameterCache Class . . . . . . . . . . . . . . . . 9 Internal Design . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12 SqlHelper Class Implementation Details . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12 SqlHelperParameterCache Class Implementation Details . . . . . . . . . . . . . . . . . . . . 13 Frequently Asked Questions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14 Feedback and Support . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16 More Information. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16 Collaborators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17

Introduction Are you involved in the design and development of data access code for .NET 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 application. If you use it, you will reduce the amount of custom code you need to create, test, and maintain. Specifically, the Data Access Application Block helps you: ● Call stored procedures or SQL text commands. ● Specify parameter details. ● Return SqlDataReader, DataSet, or XmlReader objects. 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 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-grained control of data access behavior in a wide range of use cases. Future releases of ADO.NET may address this scenario with a different model.

2

Data Access Application Block Overview

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 Block ● Using the Data Access Application Block ● Internal Design ● Frequently Asked Questions ● Feedback and Support ● 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, which 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 individual 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 SqlhelperParameterCache, 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, covering a range of common use cases which helps 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 includes deployment and update options, as well as security related information. ● Reference. This is a comprehensive API reference section that details the classes and interfaces comprising the Data Access Application Block.

4

Data Access Application Block Overview

System Requirements To run the Data Access Application Block, you need the following: ● Microsoft Windows® 2000, Windows XP Professional ● The RTM version of the .NET Framework SDK (available for downloading at http://msdn.microsoft.com/downloads/default.asp?url=/downloads/sample.asp?url =/msdn-files/027/000/976/msdncompositedoc.xml&frame=true) ● The RTM version of Visual Studio® .NET (recommended but not required) ● A SQL Server 7.0 or later database server

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. To open the Data Access Application Block download page, visit http://msdn.microsoft.com/downloads/default.asp?URL=/downloads/sample.asp?url =/msdn-files/027/001/942/msdncompositedoc.xml.

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. Data Access Application Block

Data Access Client

SqlHelper

ExecuteNonQuery T-SQL Statement or Stored Procedure

ExecuteDataset ExecuteReader

int

ExecuteScalar

DataSet

ExecuteXmlReader

SqlDataReader

SqlHelperParameterCache

object XmlReader CacheParameterSet GetCachedParameterSet SqlParameter Array

GetSpParameterSet

Figure 1 Data Access Application Block

The SqlHelper class provides a set of static methods that you can use to execute a variety of different command types against a SQL Server database. 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 overrides 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.

Using the Data Access Application Block

7

Executing Commands with the SqlHelper Class The SqlHelper class provides five Shared (Visual Basic) or static (C#) methods named ExecuteNonQuery, ExecuteDataset, ExecuteReader, ExecuteScalar, and ExecuteXmlReader. 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)

8

Data Access Application Block Overview

In addition to these overloads, all methods other than ExecuteXmlReader 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 the XmlReader when they had finished with it.

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 any of the Execute* methods, as illustrated in the following code sample. [Visual Basic] Dim ds As DataSet = SqlHelper.ExecuteDataset( _ "SERVER=(local);DATABASE=Northwind;INTEGRATED SECURITY=True;", _ CommandType.Text, "SELECT * FROM Products")

Using the Data Access Application Block

9

[C#] DataSet ds = SqlHelper.ExecuteDataset( _ "SERVER=DataServer;DATABASE=Northwind;INTEGRATED SECURITY=sspi;", _ CommandType.Text, "SELECT * FROM Products");

Managing Parameters with the SqlHelperParameterCache Class The SqlHelperParameterCache class provides three public shared methods that can be used to manage parameters. These 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 stored procedure by querying the database once and then caching the results for future 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, values, 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 'These will form the key used to store and retrieve the 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"

(continued)

10

Data Access Application Block Overview

(continued) '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.GetCachedParameterSet(CONN_STRING, sql) storedParams(0).Value = 2 storedParams(1).Value = 3 'Use the parameters in a command 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 Category=@Cat AND SupplierID = @Sup"; //Cache the parameters SqlParameter[] paramsToStore = new SqlParameter[2]; paramsToStore[0] = New SqlParameter("@Cat", SqlDbType.Int); paramsToStore[1] = New SqlParameter("@Sup", SqlDbType.Int); SqlHelperParameterCache.CacheParameterSet(CONN_STRING, sql, paramsToStore); //Retrieve the parameters from the cache SqlParameter storedParams = new SqlParameter[2]; storedParams = SqlHelperParameterCache.GetCachedParameterSet(CONN_STRING, sql); storedParams(0).Value = 2; storedParams(1).Value = 3; //Use the parameters in a command DataSet ds; ds = SqlHelper.ExecuteDataset(CONN_STRING, CommandType.StoredProcedure, sql, storedParams);

Using the Data Access Application Block

11

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 'These will form the key used to store and retrieve the parameters Const 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 = SqlHelperParameterCache.GetSpParameterSet(CONN_STRING, spName) storedParams(0).Value = "Beverages" storedParams(1).Value = "1997" 'Use the parameters in a command 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 = SqlHelperParameterCache.GetSpParameterSet(CONN_STRING, spName); storedParams[0].Value = "Beverages"; storedParams[1].Value = "1997"; //Use the parameters in a command DataSet ds; ds = SqlHelper.ExecuteDataset(CONN_STRING, CommandType.StoredProcedure, spName, storedParams);

Internal Design The Data Access Application Block includes the full source code and a comprehensive guide to its design. In this section, the main implementation details are described.

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 implemented 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. ● ExecuteScalar. This method returns a single value. The value is always the first column of the first row returned by the command. ● ExecuteXmlReader. This method returns an XML fragment from a FOR XML query.

Internal Design

13

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. ● AssignParameterValues: A function used to assign values to the SqlParameter objects. ● 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 in order 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 RTM release of 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 Application Block assemblies? Yes. After it is compiled, the Microsoft,ApplicationBlocks.Data.dll assembly can be XCOPY deployed.

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. DataSets give you a flexible, disconnected, relational view of your data while DataReaders provide an extremely high performance, read only, forward only cursor. For a comprehensive comparison of DataSets and DataReaders, see the Data Access Architecture Guide.

How do I use ExecuteDataset 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.

Frequently Asked Questions

15

For example, suppose you have the following stored procedures in your database. CREATE AS SELECT GO CREATE AS SELECT

PROCEDURE GetCategories * FROM Categories PROCEDURE GetProducts * FROM Products

You could create a master stored procedure that makes nested calls to these procedures, as illustrated in the following code sample. CREATE PROCEDURE GetCategoriesAndProducts AS BEGIN EXEC GetCategories EXEC GetProducts END

Executing this master stored procedure with the ExecuteDataset method returns a single DataSet containing two tables; one containing the category data and the other containing 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 applications quickly and easily.

Feedback and Support Questions? Comments? Suggestions? For feedback on the Data Access Application Block, please send an e-mail message to [email protected]. The Application Blocks for .NET are designed to jumpstart development of .NET distributed applications. The sample code and documentation is provided “as-is.” While it has undergone testing and is considered a robust code set, it is not supported like a traditional Microsoft product. A newsgroup has also been created to assist you with the Application Blocks for .NET. Use this newsgroup to consult with your counterparts, peers, and Microsoft Support Professionals in an online, open forum. Everyone else benefits from your questions and comments, and our development team is monitoring the newsgroup on a daily basis: ● Newsgroup: Web-Based Reader http://msdn.microsoft.com/newsgroups/loadframes.asp?icp=msdn&slcid=us&newsgroup =microsoft.public.dotnet.distributed_apps ● Newsgroup: NNTP Reader news://msnews.microsoft.com/microsoft.public.dotnet.distributed_apps Do you want to learn and harness the power of .NET? Work side by side with technology experts at the Microsoft Technology Centers to learn development best practices. For more information about Microsoft Technology Centers, please visit http://www.microsoft.com/business/services/mtc.asp. Need more help? Check out the latest addition to support services, Advisory Services, a proactive solution for your small scale consulting needs. For more information about Advisory Services, please visit http://support.microsoft.com /default.aspx.

More Information The Data Access Application Block is designed and developed based on the best practices and general design principles discussed in the Data Access in .NET Architecture Guide. 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, Andrew Roubin (Vorsite Corp.), Jeffrey Richter (Wintellect), Bernard Chen (Sapient), and Matt Drucker (Turner Broadcasting). Thanks, also, to the content team: Tina Burden (Entirenet), Shylender Ramamurthy (Infosys Technologies Ltd), and Filiberto Selvas Patino.