ADO.NET 3.5 Cookbook, 2nd Edition
Bill Hamilton
Beijing Cambridge Farnham Kln Sebastopol Tokyo
A Note Regarding Supplemental Files
Supplemental files and examples for this book can be found at http://examples.oreilly.com/9780596101404/. Please use a standard desktop web browser to access these files, as they may not be accessible from all ereader devices.
All code files or examples referenced in the book will be available online. For physical books that ship with an accompanying disc, whenever possible, weve posted all CD/DVD content. Note that while we provide as much of the media content as we are able via free download, we are sometimes limited by licensing restrictions. Please direct any questions or concerns to .
Preface
Microsoft ADO.NET 3.5 is the latest data access technology from Microsoft. ADO.NET is a collection of classes that are part of the .NET Framework, and is designed to provide consistent access to data in loosely coupled n -tier application architectures such as web services. ADO.NET can be used to access a variety of data sources including databases such as Microsoft SQL Server, Oracle, and Microsoft Access, as well as XML, OLE DB, and ODBC data sources.
ADO.NET separates data access from manipulation. Connected classes available in .NET data providers connect to a data source, execute commands, and retrieve results. Disconnected classes let you access and manipulate data offline and later synchronize changes with the underlying data source. XML support is tightly integrated with ADO.NET, allowing you to load, access, and manipulate data using XML as well as the disconnected classes simultaneously.
ADO.NET is very different from its predecessor ADO. With the increasing popularity of .NET and ADO.NET, there are many questions from developers about how to solve specific problems and how to implement solutions most efficiently. This book is a reference containing solutions and techniques that make using ADO.NET easier and more productive. You may have already encountered some of these problems; others you may never see. Some of the solutions are responses to problems that have been posted in various discussion groups, while others are real problems encountered while building applications.
This book is organized into chapters, with each chapter containing solutions (stated as recipes) to a specific problem category. Each recipe consists of a single question and its solution followed by a discussion. The question-answer format provides complete solutions to problems, making it easy to read and use. Every recipe contains a complete, documented code sample showing you how to solve the specific problem, as well as a discussion of how the underlying technology works and a discussion of alternatives, limitations, and other considerations when appropriate.
Whats New in the Second Edition
A lot has changed since the first edition of this book. ADO.NET has had two significant releasesversions 2.0 and 3.5. SQL Server has also had two major releases with SQL Server 2005 and SQL Server 2008. The .NET Framework has had three major releases with versions 2.0, 3.0, and 3.5. And Visual Studio has had two major releases with Visual Studio 2005 and 2008. This book is updated for the latest version of all of these technologies.
This book is a significant revision from the first edition. In addition to the obvious reason for revising this book in order to cover technology changes, I wanted to improve on the first edition in a number of important ways. First, I wanted to make the book easier to use and more accessible. In addition to updating and adding recipes to cover new or changed technology and tools, the recipes have been reorganized and new recipes added to fill in gaps that I and others saw. A number of new recipes in the early chapters address core concepts in depth. The second change that I made was to rewrite most of the solutions as Windows console applications. While the Windows Forms solutions in the first edition had a certain elegance, I felt that the additional code required and the complexity in both building and presenting the solutions distracted from the core objective of demonstrating solutions to ADO.NET problems. It also made it difficult to show output from the solutions. Windows and Web Forms solutions are presented as necessary, especially when demonstrating how to use ADO.NET controls. Ive also rewritten code to focus on and highlight the solution and to programmatically explore key concepts rather than relying on the reader running and interacting with the solution. Third, I wanted to make the book more usable as a standalone resourcethat is, without loading code samples. Sample output has been included for each solution. And finally, I wanted to make the book more useful and readable in many small waysIve clarified and expanded (and in some cases reduced) explanations, increased consistency in the way solutions are presented, and of course fixed a few errors along the way.
Who This Book Is For
You dont have to be an experienced .NET developer to use this book; it is designed for users of all levels. This book provides solutions to problems that developers face every day. Reference or tutorial books can teach general concepts but do not usually provide help solving real-world problems. This book teaches by example, the natural way for most people to learn.
Although some of the samples in this book use advanced techniques, the problems they address are frequently faced by developers with all levels of experience. The code samples are all complete, well commented, and thoroughly explained to help you apply them and solve your own problems quickly, easily, and efficiently.
At the same time, you will understand exactly how and why the solution works, the requirements, trade-offs, and drawbacks. This book is designed to move you up the learning curve quickly.
This book presents code together with output so that you can use book without loading the actual code. Code generated automatically by Visual Studio is not shown. You dont need to retype the code in this book since it is available in both C# and Visual Basic on the OReilly web site (http://www.oreilly.com/catalog/9780596101404). T-SQL and PL/SQL code is also available for download.
What You Need to Use This Book
To run the samples in this book, you will need a computer running Windows 2000 or later. The Web Forms solutions require Microsoft Internet Information Server (IIS) version 5.1 or later.
The solutions in this book were written using Microsoft Visual Studio 2008. Most solutions will work with Visual Studio 2005. Many of the samples will run on Microsoft SQL Server 2000. Others require SQL Server 2005 and a few require SQL Server 2008. Oracle 10g is used to build the Oracle solutions, although Oracle 8 i version 3 (release 8.1.7) or later is all you need.
How This Book Is Organized
This book is organized into 12 chapters, each of which focuses on a particular topic in creating ADO.NET solutions. Each recipe consists of a specific problem, stated as a question, followed by a solution and discussion. Here is a summary of each chapter:
,
Connecting to DataThe solutions in this chapter show how to connect to a variety of data sources from ADO.NET. Connecting to data sources involves connections strings, security-related issues including storing connection strings and how to use them, the different authentication methods available, and how to enable the user to build a connection string at runtime. Solutions show how to set up, monitor, and optimize connection pooling, and how to use transactions with pooled connection.