ADO.NET Cookbook

How This Book Is Organized

This book is organized into 10 chapters, each of which focus 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. To give you an overview of this book’s contents, the following paragraphs summarize each chapter:

Chapter 1

The 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, and the different authentication methods available. Solutions show how to set up, monitor, and optimize connection pooling.

Chapter 2

The DataSet is a data source-independent, disconnected, in-memory relational database that provides sophisticated navigational capabilities. The solutions in this chapter show how to retrieve data using SQL statements, parameterized SQL statements, stored procedures, and batched queries, into both untyped and strongly typed DataSet objects. Solutions show how to understand the data that is returned. You’ll learn how to use messaging and web services to retrieve data.

Chapter 3

The solutions in this chapter focus on searching for records in views and tables, calculating values based on values in the same or other tables, and navigating data relations between tables. You’ll understand how to use globalization and localization to create applications for multiple cultures. Solutions show alternate techniques to retrieve hierarchical data, including the COMPUTE BY and SHAPE clauses.

Chapter 4

This chapter focuses on issues related to inserting and updating data, and using web services, remoting, and messaging to update data. You’ll learn how to manage auto-increment columns with SQL Server and sequences with Oracle. Solutions show how to change primary keys and how to use GUID primary keys, as well as how to work with master-detail data.

Chapter 5

This chapter focuses on copying data between ADO.NET classes and between ADO and ADO.NET classes, serializing and deserializing data, merging data, encrypting data, and securing login credentials.

Chapter 6

The solutions in this chapter show how to use manual and automatic transactions and DBMS transactions from ADO.NET. You’ll learn how to identify and handle concurrency errors, how to set isolation levels, how to simulate pessimistic concurrency, how to use SQL Server pessimistic concurrency with locking hints, and how to update master-detail data without concurrency errors.

Chapter 7

This chapter focuses on binding simple and complex data to Web Forms and Windows Forms. You’ll learn how to manage master-detail data in Web Forms, how to update complex data, how to data-bind images, and how to use Crystal Reports dynamically at runtime. Solutions show how to control user editing of data with data views. For applications without a user interface, you’ll learn how to use design-time features in classes without a user interface.

Chapter 8

The solutions in this chapter show how to use XML with ADO.NET. You’ll learn how to load schema and data from XML into a DataSet, about the DiffGram format and how to use it to determine what changes were made to a DataSet. You’ll also learn how to read XML data directly from a SQL Server using FOR XML. Solutions show how to use XPath queries, how to control the format of XML output, and how to use XML template queries to fill a DataSet. Optimizing update performance by batching data updates with OpenXML is shown.

Chapter 9

This chapter shows how to improve application performance and responsiveness with asynchronous processing as well as how to cancel those processes, how to cache data to improve performance while retrieving data, and how to use custom paging to improve performance over automatic paging. Solutions show how to work with BLOB data in SQL Server and Oracle. You’ll learn how to optimize loading data into a SQL Server using bulk loading with SQL XML and how to optimize updating data with batch updates by handling DataAdapter events. You’ll also learn how to effectively debug stored procedures using Visual Studio .NET.

Chapter 10

This chapter shows how to get schema information and metadata from databases, manage database objects, and enumerate installed OLE DB providers and ODBC drivers using DDL, SQL-DMO, information schema views, and system stored procedures. You’ll learn how to use JRO to manage a Microsoft Access database. A solution shows how to get a query execution plan using the SQL Server SET statement.

In some cases, recipes are related. Where appropriate, recipes reference other solutions.

The Appendix A contains a brief tutorial, Converting from C# to VB Syntax.