Introduction

 

Introduction

Microsoft ADO.NET 2.0 represents a major improvement in data access capabilities. You'll see little resemblance between the object model in the original release of ADO included in Microsoft Visual Studio 6 and the improved object model in the new release included in Visual Studio 2005. ADO.NET 2.0 contains many changes that simplify development by requiring less code, and it has new capabilities that enable you to write more database-agnostic code, which means you will have a much easier time writing code without knowledge of the database server that will be used.

This book dives deep into advanced implementations of the new and old ADO.NET features that make up the current release. Complex problems rarely have a single solution, so I offer a comparative analysis whenever possible to highlight the benefits and drawbacks of several possible solutions.

Who This Book Is For

Although this book does cover advanced topics, readers are not expected to be advanced programmers, but it is important that you have solid programming skills and experience using Visual Studio. Every chapter starts by laying the foundation for the topic covered in the chapter. To help you understand the topics covered later in the book, the first two chapters even give you an overview of the ADO.NET classes. If you understand the need to store data in a database and have had some experience using any version of ADO, this book is for you.

If you are already familiar with the material covered in a given chapter, then by all means, skip ahead as needed, and don't forget that in many cases the index can direct you straight to the solution for a problem that you are trying to solve.

 
 

How This Book Is Organized

This book is probably best used as a reference that provides ideas for and solutions to the problems that you encounter when working with data, but I have connected topics in each chapter so you can also get a more comprehensive view by reading the book sequentially. You can follow cross-references to other chapters as needed to strengthen your understanding of the given topic.

Conventions

Throughout this book, names of classes, properties, variables, and other program elements are in italics. Structured Query Language (SQL) statements such as the WHERE clause are in uppercase.

Microsoft Visual Basic and C# code samples are shown together in the following format.

image from book

Visual Basic

Visual Basic version of cool code here
 

image from book

image from book

C#

C# version of cool code here
 

image from book

Output, or code that is shown for reading purposes only (that is, anything you are not expected to type), is presented in a screened format, as follows.

image from book

Sample Output

Item    Cost
  1    123.45
  2      2.99
  3     83.21
  4    543.56
 

image from book

 
 

System Requirements

You'll need the following hardware and software to build and run the code samples for this book:

  • Microsoft Windows XP Professional with Service Pack 2 (SP2), Windows Server 2003 with Service Pack 1 (SP1), or Windows 2000 with Service Pack 4 (SP4).

  • Visual Studio 2005 Standard Edition or Professional Edition. (The latter is required for Chapter 9 sample code.)

  • Microsoft SQL Server 2005 Express (included with Visual Studio 2005) and/or Microsoft SQL Server 2005. (See the next section.)

  • 600 MHz Pentium or compatible processor (1 GHz Pentium recommended).

  • 192 MB RAM (256 MB or more recommended).

  • 800 x 600 or higher resolution video monitor with at least 256 colors (1024 x 768 High Color 16-bit recommended).

  • DVD-ROM drive.

  • Microsoft Mouse or compatible pointing device.

SQL Server 2005 vs. SQL Server 2005 Express Edition

I have tried to use SQL Server 2005 Express Edition (SQL Server Express) where possible because it's easy to use. Further, SQL Server Express makes it easy to set up standalone samples because the database files can simply be included in the project. Some chapters use SQL Server 2005 because the features being covered are not available in SQL Server Express or because they are easier to use in SQL Server 2005.

The following table shows which release of SQL Server 2005 is used in the sample code, using these entries:

  • N/A Not applicable; database access not required

  • Yes Used in the code sample

  • No Not usable for the code sample

  • Possible Can optionally be used in place of the other version but requires connection string change

SQL Server Edition Used for Sample Code

Chapter and Topics

SQL Server 2005

SQL Server 2005 Express Edition

1: Overview of ADO.NET Disconnected Classes

N/A

N/A

2: Overview of ADO.NET Connected Classes

Yes, Possible

Yes, Possible

3: ADO.NET Trace Logging

Yes

No

4: Advanced Connectivity to the Data Store

   
  1. ConnectionStringBuilder

  1. Possible

  1. Yes

  1. Asynchronous access

  1. Yes

  1. Possible

  1. Connection pooling/failover

  1. Yes

  1. No

  1. StatisticsTest

  1. Possible

  1. Yes

5: Working with Disconnected Data

Possible

Yes

6: Working with Relational Disconnected Data

Possible

Yes

7: Working with the Windows Data Grid Control

Possible

Yes

8: Working with the Web Data Grid Control

Possible

Yes

9: Working with the SQLCLR

Yes

No

10: Understanding Transactions

Possible

Yes

11: Retrieving Metadata

Possible

Yes

12: Data Caching for Performance

   
  1. SqlDependency

  1. Yes

  1. No

  1. SqlCacheDependency

  1. Yes

  1. Possible

  1. CommandNotification

  1. Yes

  1. No

13: Implementing Security

   
  1. Security

  1. N/A

  1. N/A

  1. PartialTrust

  1. Possible

  1. Yes

  1. EncryptedWebSite

  1. Possible

  1. Yes

  1. SqlInjection

  1. Possible

  1. Yes

14: Working with Large Objects (LOBs, BLOBs, and CLOBs)

Possible

Yes

15: Working with XML Data

Possible

Yes

Configuring SQL Server 2005 Express Edition

This book requires that you have access to SQL Server 2005 Express Edition (and/or SQL Server 2005) to create and use the Northwind Traders and the Pubs sample databases. If you are using SQL Server 2005 Express, log in as Administrator on your computer, and follow these steps to grant access to the user account that you will use for performing the exercises.

  1. On the Windows Start menu, click All Programs, Accessories, Command Prompt to open a command prompt window.

  2. In the command prompt window, type the following command:

    sqlcmd -S YourServer\SQLExpress -E
     

    Replace YourServer with the name of your computer. You can find the name of your computer by running the hostname command in the command prompt window before running the sqlcmd command.

  3. At the 1> prompt, type the following command, including the square brackets, and then press Enter:

    SP_GRANTLOGIN [YourServer\UserName]
     

    Replace YourServer with the name of your computer, and replace UserName with the name of the user account that you will be using.

  4. At the 2> prompt, type the following command, and then press Enter:

    GO
     

    If you see an error message, make sure that you typed the SP_GRANTLOGIN command correctly, including the square brackets.

  5. At the 1> prompt, type the following command, including the square brackets, and then press Enter:

    SP_ADDSRVROLEMEMBER [YourServer\UserName], dbcreator
    
     

  6. At the 2> prompt, type the following command, and then press Enter:

    GO
     

    If you see an error message, make sure that you typed the SP_ADDSRVROLEMEMBER command correctly, including the square brackets.

  7. At the 1> prompt, type the following command, and then press Enter:

    EXIT
     

  8. Close the command prompt window.