Beginning C Sharp 2005 Databases

527 Pages·2006·13.16 MB·English

by  From Novice to Professional

CYAN YELLOW
MAGENTA BLACK
PANTONE 123 CV
www.sharexxx.net - free books & magazines
BOOKS FOR PROFESSIONALS BY PROFESSIONALS® THE EXPERT’S VOICE® IN .NET
Companion
eBook Available
Beginning C# 2005 Databases:
From Novice to Professional Beginning
Dear Reader,
Thousands of C# programmers have become database professionals through
the first two editions of this bok. This third edition is even more comprehen- Beginning
sive, but, as always, it assumes only basic knowledge of C#-and absolutely no
experience with databases. Everything you need to know to get off to the fastest
Author of start in database programming is clearly explained by examples you can use in
Beginning professional, real-world programs.
VB 2005 Databases Database proficiency requires basic understanding of relational concepts,
reasonable competence with the database language SQL, and clear knowledge
of how to interface between C# programs and databases. We introduce all con-
cepts carefuly, in the order in which you can best learn them, and then we C# 2005
gradually use them in concert to reinforce your understanding.
You’ll learn how to define, query, and manipulate database data with T-SQL
and how to manage SQL Server Express databases with SQL Server
Management Studio Express. You’ll learn about ADO.NET and how to use it to
access databases from C#. You’ll even learn how to write stored procedures and
call them from C# programs. You’ll learn much more besides. Our focus is
always on fundamental concepts and techniques that won’t change even when
database technology does. Databases
The major changes to this edition are due to new technology. We cover the
new XML data type and show you how to use it and other T-SQL features to
manipulate XML-techniques that many experienced T-SQL programmers often
don’t know well. The most dramatic change is the new final chapter, which intro-
duces Language-Integrated Query (LINQ), a powerful alternative to traditional
ADO.NET programming that promises to be the future of al data access in .NET. From Novice to Professional
So, whatever your database background or programming needs, we’re sure
you’ll find in here all you need to become a highly proficient database pro-
grammer. We believe you’ll have as much fun learning database programming
Companion eBook as we have doing database programming. What every C# programmer needs to know about
Enjoy,
SQL Server 2005, T-SQL, ADO.NET 2.0, and LINQ
Jim Huddleston
See last page for details
on $10 eBook version THE APRESS ROADMAP
Beginning C# 2005 Pro C# 2005 and the .NET Expert C# 2005 Business
Databases 2.0 Platform, Third Edition Objects, Second Edition
SOURCE CODE ONLINE James Huddleston
www.apress.com ISBN 1-59059-777-X
53999 Ranga Raghuram, Syed Fahad Gilani,
US $39.99
Jacob Hammer Pedersen, and Jon Reid
Shelve in Programming
Languages/C#
User level:
Beginner–Intermediate 6 89253 59777 4 9 781590 597774
this print for content only—size & color not accurate spine = 0.998" 528 page count
Huddleston
C# 2005 Databases
777Xfmfinal.qxd 11/18/06 2:31 PM Page i
Beginning C# 2005
Databases
From Novice to Professional
James Huddleston, Ranga Raghuram,
Syed Fahad Gilani, Jacob Hammer Pedersen,
and Jon Reid
777Xfmfinal.qxd 11/18/06 2:31 PM Page ii
Beginning C# 2005 Databases: From Novice to Professional
Copyright © 2006 by James Huddleston, Ranga Raghuram, Syed Fahad Gilani, Jacob Hammer Pedersen,
and Jon Reid
All rights reserved. No part of this work may be reproduced or transmitted in any form or by any means,
electronic or mechanical, including photocopying, recording, or by any information storage or retrieval
system, without the prior written permission of the copyright owner and the publisher.
ISBN-13 (pbk): 978-1-59509-777-4
ISBN-10 (pbk): 1-59059-777-X
Printed and bound in the United States of America 9 8 7 6 5 4 3 2 1
Trademarked names may appear in this book. Rather than use a trademark symbol with every occurrence
of a trademarked name, we use the names only in an editorial fashion and to the benefit of the trademark
owner, with no intention of infringement of the trademark.
Lead Editor: Jonathan Hassell
Technical Reviewer: Vidya Vrat Agarwal
Editorial Board: Steve Anglin, Ewan Buckingham, Gary Cornell, Jason Gilmore, Jonathan Gennick,
Jonathan Hassell, James Huddleston, Chris Mills, Matthew Moodie, Dominic Shakeshaft, Jim Sumser,
Keir Thomas, Matt Wade
Project Manager: Elizabeth Seymour
Copy Edit Manager: Nicole Flores
Copy Editors: Nicole Abramowitz, Liz Welch
Assistant Production Director: Kari Brooks-Copony
Production Editor: Janet Vail
Compositor: Linda Weidemann, Wolf Creek Press
Proofreader: April Eddy
Indexer: Kevin Broccoli/Broccoli Information Management
Cover Designer: Kurt Krames
Manufacturing Director: Tom Debolski
Distributed to the book trade worldwide by Springer-Verlag New York, Inc., 233 Spring Street, 6th Floor,
New York, NY 10013. Phone 1-800-SPRINGER, fax [phone], e-mail
777Xfmfinal.qxd 11/18/06 2:31 PM Page iii
To Jared, Quinn, and Tess
I love you.
—Jim Huddleston
777Xfmfinal.qxd 11/18/06 2:31 PM Page iv
777Xfmfinal.qxd 11/18/06 2:31 PM Page v
Contents at a Glance
About the Author . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xvii
About the Technical Reviewer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xix
Acknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxi
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxiii
■CHAPTER 1 Getting Our Tools . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
■CHAPTER 2 Getting to Know Our Tools . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
■CHAPTER 3 Introducing SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43
■CHAPTER 4 Introducing ADO.NET . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65
■CHAPTER 5 Introducing Connections . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 95
■CHAPTER 6 Introducing Commands . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 115
■CHAPTER 7 Introducing Data Readers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 141
■CHAPTER 8 Introducing Datasets and Data Adapters. . . . . . . . . . . . . . . . . . . . . . . 171
■CHAPTER 9 Introducing Data Binding. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 227
■CHAPTER 10 Understanding Tables and Relationships . . . . . . . . . . . . . . . . . . . . . . 251
■CHAPTER 11 Learning More About Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 277
■CHAPTER 12 Using Stored Procedures. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 313
■CHAPTER 13 Handling Exceptions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 335
■CHAPTER 14 Using Transactions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 359
■CHAPTER 15 Working with ADO.NET Events . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 379
■CHAPTER 16 Working with Text and Binary Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . 403
■CHAPTER 17 Using XML. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 431
■CHAPTER 18 Introducing LINQ. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 459
■INDEX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 487
v
777Xfmfinal.qxd 11/18/06 2:31 PM Page vi
777Xfmfinal.qxd 11/18/06 2:31 PM Page vii
Contents
About the Author . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xvii
About the Technical Reviewer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xix
Acknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxi
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxiii
■CHAPTER 1 Getting Our Tools . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
Installing VCSE and SSE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2
Installing SSMSE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
Installing the SQL Server Documentation . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
Installing a Sample Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
Installing the Northwind Creation Script . . . . . . . . . . . . . . . . . . . . . . . 10
Creating the Northwind Sample Database . . . . . . . . . . . . . . . . . . . . . 12
Uninstalling the Northwind Creation Script . . . . . . . . . . . . . . . . . . . . . 14
Summary. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
■CHAPTER 2 Getting to Know Our Tools. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
Using SSMSE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
Configuring VCSE. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30
Using BOL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35
Summary. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40
■CHAPTER 3 Introducing SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43
What Is SQL? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43
Retrieving Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44
Performing Simple Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44
Using the WHERE Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48
Sorting Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52
Inserting Data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55
Updating Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57
vii
777Xfmfinal.qxd 11/18/06 2:31 PM Page viii
viii ■CONTENTS
Deleting Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59
T-SQL Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60
Numeric Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60
Money Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61
Character String Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61
Date and Time Data Types. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 62
Binary Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 62
Other Data Types. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 62
Data Type Precedence . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63
Summary. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64
■CHAPTER 4 Introducing ADO.NET . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65
Why ADO.NET?. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65
From ADO to ADO.NET . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65
ADO.NET Isn’t a New Version of ADO. . . . . . . . . . . . . . . . . . . . . . . . . . 66
ADO.NET and the .NET Base Class Library . . . . . . . . . . . . . . . . . . . . . 67
Understanding ADO.NET Architecture . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 68
Using the SQL Server Data Provider . . . . . . . . . . . . . . . . . . . . . . . . . . . 71
Using the OLE DB Data Provider . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 78
Using the ODBC Data Provider. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 83
Data Providers Are APIs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 93
Summary. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 94
■CHAPTER 5 Introducing Connections . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 95
Introducing the Data Provider Connection Classes . . . . . . . . . . . . . . . . . . . 95
Connecting to SSE with SqlConnection . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 96
Try It Out: Using SqlConnection. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 96
How It Works . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 98
Debugging Connections to SQL Server . . . . . . . . . . . . . . . . . . . . . . . 101
Security and Passwords in SqlConnection . . . . . . . . . . . . . . . . . . . . 102
How to Use SQL Server Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 103
Connection String Parameters for SqlConnection . . . . . . . . . . . . . . 103
Connection Pooling . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 105
777Xfmfinal.qxd 11/18/06 2:31 PM Page ix
■CONTENTS ix
Improving Your Use of Connection Objects. . . . . . . . . . . . . . . . . . . . . . . . . 105
Using the Connection String in the Connection Constructor. . . . . . 105
Displaying Connection Information . . . . . . . . . . . . . . . . . . . . . . . . . . . 106
Try It Out: Displaying Connection Information . . . . . . . . . . . . . . . . . 106
How It Works . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 108
Connecting to SSE with OleDbConnection . . . . . . . . . . . . . . . . . . . . . . . . . 111
Try It Out: Connecting to SSE with the OLE DB Data Provider . . . . 111
How It Works . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113
Summary. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 114
■CHAPTER 6 Introducing Commands. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 115
Creating a Command . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 116
Try It Out: Creating a Command with a Constructor . . . . . . . . . . . . 116
How It Works . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 117
Associating a Command with a Connection . . . . . . . . . . . . . . . . . . . 118
Try It Out: Setting the Connection Property . . . . . . . . . . . . . . . . . . . . 118
How It Works . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 118
Assigning Text to a Command. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 119
Try It Out: Setting the CommandText Property . . . . . . . . . . . . . . . . . 120
How It Works . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 121
Executing Commands . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 121
Try It Out: Using the ExecuteScalar Method . . . . . . . . . . . . . . . . . . . 122
How It Works . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 124
Executing Commands with Multiple Results. . . . . . . . . . . . . . . . . . . . . . . . 125
Try It Out: Using the ExecuteReader Method . . . . . . . . . . . . . . . . . . 125
How It Works . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 127
Executing Statements. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 128
Try It Out: Using the ExecuteNonQuery Method . . . . . . . . . . . . . . . . 128
How It Works . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 131
Command Parameters . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 133
Try It Out: Using Command Parameters . . . . . . . . . . . . . . . . . . . . . . 134
How It Works . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 137
The Prepare Method . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 138
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 140