Professional ADO.NET 2 Programming with SQL Server 2005, Oracle and MySQL (P1)

Chia sẻ: Tan Giang | Ngày: | Loại File: PDF | Số trang:30

Thêm vào BST

Báo xấu

192
lượt xem 63
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

A few years ago, Microsoft released the 1.0 Framework of ADO.NET and revolutionized the way we access data. It was a drastic change that took some getting used to, but for the most part, developers who made the switch love it. It’s now over three years later, and ADO.NET 2.0 is here. It provides all the same features that we’ve come to love in ADO.NET 1.0 and adds some new ones to provide even more functionality and make repetitive, mundane tasks much simpler. Throughout this book, we dig deeply into many of these new features....

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Professional ADO.NET 2 Programming with SQL Server 2005, Oracle and MySQL (P1)

Professional ADO.NET 2 Programming with SQL Server 2005, Oracle® and MySQL® , Wallace B. McClure Gregory A. Beamer John J. Croft IV J. Ambrose Little Bill Ryan Phil Winstanley David Yack Jeremy Zongker
Professional ADO.NET 2 Programming with SQL Server 2005, Oracle® and MySQL® ,
Professional ADO.NET 2 Programming with SQL Server 2005, Oracle® and MySQL® , Wallace B. McClure Gregory A. Beamer John J. Croft IV J. Ambrose Little Bill Ryan Phil Winstanley David Yack Jeremy Zongker
Professional ADO.NET 2 Programming with SQL Server 2005, Oracle® and MySQL® , Published by Wiley Publishing, Inc. 10475 Crosspoint Boulevard Indianapolis, IN 46256 www.wiley.com Copyright © 2006 by Wiley Publishing, Inc., Indianapolis, Indiana Published simultaneously in Canada ISBN-13: 978-0-7645-8437-4 ISBN-10: 0-7645-8437-5 Manufactured in the United States of America 10 9 8 7 6 5 4 3 2 1 1B/RW/RR/QV/IN Library of Congress Cataloging-in-Publication Data is available from the publisher. No part of this publication may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, scanning, or otherwise, except as permitted under Section 107 or 108 of the 1976 United States Copyright Act, without either the prior written permission of the Publisher, or authorization through payment of the appropriate per-copy fee to the Copyright Clearance Center, Inc., 222 Rosewood Drive, Danvers, MA 01923, (978) 750-8400, fax (978) 646-8600. Requests to the Publisher for permission should be addressed to the Legal Department, Wiley Publishing, Inc., 10475 Crosspoint Blvd., Indianapolis, IN 46256, (317) 572-3447, fax (317) 572-4355 or online at http://www.wiley.com/go/permissions. LIMIT OF LIABILITY/DISCLAIMER OF WARRANTY: THE PUBLISHER AND THE AUTHOR MAKE NO REPRESENTATIONS OR WARRANTIES WITH RESPECT TO THE ACCURACY OR COMPLETENESS OF THE CONTENTS OF THIS WORK AND SPECIFICALLY DISCLAIM ALL WARRANTIES, INCLUDING WITHOUT LIMITATION WARRANTIES OF FITNESS FOR A PARTICULAR PURPOSE. NO WARRANTY MAY BE CREATED OR EXTENDED BY SALES OR PROMOTIONAL MATERIALS. THE ADVICE AND STRATEGIES CONTAINED HEREIN MAY NOT BE SUITABLE FOR EVERY SITUATION. THIS WORK IS SOLD WITH THE UNDERSTANDING THAT THE PUBLISHER IS NOT ENGAGED IN RENDERING LEGAL, ACCOUNTING, OR OTHER PROFESSIONAL SERVICES. IF PROFESSIONAL ASSISTANCE IS REQUIRED, THE SERVICES OF A COMPETENT PROFESSIONAL PERSON SHOULD BE SOUGHT. NEITHER THE PUBLISHER NOR THE AUTHOR SHALL BE LIABLE FOR DAMAGES ARISING HERE- FROM. THE FACT THAT AN ORGANIZATION OR WEBSITE IS REFERRED TO IN THIS WORK AS A CITATION AND/OR A POTENTIAL SOURCE OF FURTHER INFORMATION DOES NOT MEAN THAT THE AUTHOR OR THE PUBLISHER ENDORSES THE INFORMATION THE ORGANIZATION OR WEBSITE MAY PROVIDE OR RECOMMENDATIONS IT MAY MAKE. FURTHER, READERS SHOULD BE AWARE THAT INTERNET WEBSITES LISTED IN THIS WORK MAY HAVE CHANGED OR DISAP- PEARED BETWEEN WHEN THIS WORK WAS WRITTEN AND WHEN IT IS READ. For general information on our other products and services please contact our Customer Care Department within the United States at (800) 762-2974, outside the United States at (317) 572-3993 or fax (317) 572-4002. Trademarks: Wiley, the Wiley logo, Wrox, the Wrox logo, Programmer to Programmer, and related trade dress are trademarks or registered trademarks of John Wiley & Sons, Inc. and/or its affiliates, in the United States and other countries, and may not be used without written permission. Linux is a registered trademark of Linus Torvalds. MySQL is a registered trademark of MySQL AB A Company. All other trademarks are the property of their respective owners. Wiley Publishing, Inc., is not associated with any product or vendor mentioned in this book. Wiley also publishes its books in a variety of electronic formats. Some content that appears in print may not be available in electronic books.
About the Authors Wallace B. McClure Wallace B. McClure graduated from the Georgia Institute of Technology in 1990 with a bachelor of science degree in electrical engineering. He continued his education there, receiving a master’s degree in the same field in 1991. Since that time, he has done consulting and development for such companies as Coca-Cola, Bechtel National, Magnatron, and Lucent Technologies, among others. Products and services have included work with ASP, ADO, XML, and SQL Server, as well as numerous applications in the Microsoft .NET Framework. Wally McClure specializes in building applications that have large numbers of users and large amounts of data. He is a Microsoft MVP and an ASPInsider, and a partner in Scalable Development, Inc. You can read Wally’s blog at http://weblogs.asp.net/wallym/. Gregory A. Beamer Gregory A. Beamer is a solutions architect specializing in Microsoft Internet technologies. Greg got involved in programming in the early 1990s with Visual Basic 3 and has stayed on the leading edge of Microsoft Internet technologies since the Denali beta (ASP 1.0). Greg first worked with .NET with the PDC 2000 beta and has been on both the SQL Server 2005 and .NET 2.0 betas since spring 2003. When Greg is not working, he spends his time with his wife, Tiffany, and their four daughters, Rebecca, Emily, Annabelle, and Miranda. John J. Croft IV John J. Croft IV graduated from the Georgia Institute of Technology in 1991, receiving a bachelor’s degree in mechanical engineering. He then spent five years consulting for large companies, including Coca-Cola, BellSouth, and MCI. Work at these companies primarily involved C and C++ programming and object-oriented systems analysis. His various clients have included both Fortune 100s and small startup companies. Their problems have ranged drastically, from large databases and executive informa- tion systems to lithotripter control and satellite telemetry. Croft has completed projects with Java, XML, and, recently, C# and .NET applications. He is a partner in Scalable Development, Inc. J. Ambrose Little Ambrose is the editor-in-chief of the ASPAlliance, an ASPInsider, and a Microsoft ASP.NET MVP who currently works as a Web architect for a large credit union in Tampa, Florida. Previously, he worked as a consultant at Verizon, creating XML Web Services and middle-tier components, and for BOK Financial’s Web Services department creating ASP.NET applications for their intranet. His pre-.NET programming experience consists mostly of developing Web applications using ASP and VB COM/DCOM for several years. He has a bachelor’s degree in medieval European history, which remains an interest. Apart from developing software, he enjoys movies, reading, writing, foosball, chess, tennis, badminton, and spend- ing time with his wonderful family.
About the Authors Bill Ryan Bill currently works as a senior software developer for TiBA Solutions in Greenville, SC. He is also a Windows Embedded MVP, has served on Macromedia’s Flash Advisory Board, and helps run two popular .NET Focused Web sites (www.devbuzz.com and www.knowdotnet.com) and his blog www.msmvps.com/WilliamRyan. After earning his master’s degree in business administration, Bill began work as a statistical analyst, but quickly realized that his true love was programming. He has worked in multiple industries, including financial services/securities, manufacturing, health care, phar- maceuticals, and, currently, consulting. Bill is a frequent speaker at user’s group meetings, has spoken at multiple Microsoft Code Camps, and has hosted multiple MSDN Webcasts. Although technologically related things consume most of his time, Bill’s other interests include cult films, economics, Freemasonry, cuckoo clocks, and, most important, his girlfriend, Kim, and her daughter, Sarah. Phil Winstanley Phil Winstanley is a Web applications developer working for Portfolio Europe, located in Manchester, England. He has been involved with ASP.NET since its inception, and has developed a deep understand- ing of the platform. As a Microsoft MVP (Most Valuable Professional), member of the ASPInsiders, co-owner of Microsoft Web Developers UK, and the North West England Regional Director for the .NET Exchange, Phil is deeply embedded in the development community and works closely with the Web Platforms team at Microsoft, regularly visiting the developers in Redmond, Washington. David Yack David is the president of Colorado Technology Consultants, Inc. He is a hands-on technology consultant with solid management experience in privately held and Fortune 500 companies and has over 15 years of experience in the IT industry. David is currently responsible for leading Colorado Technology Consultants’ focus on Microsoft .NET technologies. David is an active participant in the Microsoft development community, ranging from the Denver .NET user group to Microsoft’s Public Communities, such as www.asp.net and http://aspalliance.com. David is the leader of the South Colorado .NET user group. David is recognized by Microsoft as a .NET MVP (Most Valuable Professional). Jeremy Zongker Jeremy Zongker is a software development manager who works primarily on data-driven ASP.NET applications with Microsoft SQL Server databases. He is a Microsoft Certified Solutions Developer for .NET and a 2004 MVP for ASP.NET. Jeremy is the founder and senior developer for Trilitech, LLC, a Broken Arrow, Oklahoma, software development company. vi
Credits Acquisitions Editor Project Coordinator Bob Elliott Michael Kruzil Development Editor Graphics and Production Specialists Gabrielle Nabi Carrie Foster Denny Hager Production Editor Jennifer Heleine William A. Barton Alicia B. South Technical Editor Quality Control Technicians Wiley - Dreamtech India Pvt Ltd Amanda Briggs John Greenough Copy Editor Luann Rouff Media Development Specialists Angela Denny Editorial Manager Kit Malone Mary Beth Wakefield Travis Silvers Vice President and Publisher Proofreading and Indexing Joseph B. Wikert TECHBOOKS Production Services
For my wife, Ronda, my two children, Kirsten and Bradley, and the rest of my family. —Wallace B. McClure To my loving wife and four daughters, and to God, without whom the aforementioned miracles would not be possible. —Greg Beamer To my wife, Valerie, for her support, and to my boys, Jack and Conor, for their patience on the weekends while I was writing. —John J. Croft IV To my mom and stepfather, for putting up with me all of these years and always being there. To my girlfriend, Kim, and her daughter, Sarah, for always being able to make me smile. —Bill Ryan For my wife, Julie, and my two great kids, Drew and Jacqueline. —David Yack For my wife, Jeanette, for her support, patience, and understanding during the many hours I worked on this book. —Jeremy Zongker To my caring father and mother, my loving brother, and to the Almighty, for giving me the power to work every day. —Anand Narayanaswamy, Technical Editor
Acknowledgments The initial planning and thinking about this book began during a discussion of SQL Server futures in July 2001. The discussion was with Rob Howard during a trip to Microsoft to discuss the first book I was working on at that time. After that, I stayed involved in what was happening in ADO.NET by going to the SQL Server Yukon Technical Preview in Bellevue, Washington, in February 2002 and by working with the ASP.NET and SQL Server teams at Microsoft since July 2003. Shortly after the excitement of talking with Bob Elliott at Wiley about this book wore off, it became apparent that I would need to put together an author team that knew about the problems Microsoft was trying to solve with ADO.NET Version 2. It is fortunate that I had recently been named a Microsoft MVP and an ASPInsider. Based on memberships in those groups, I was able to work with and gain the respect of Jeremy Zongker, Ambrose Little, and Phil Winstanley. From that group, I was able to meet David Yack, William (Bill) Ryan, and Gregory Beamer. Adding these six people to John Croft and myself, we created a really good group to work with. I want to thank them for working together very well, for working quickly, and for examining the new features that are provided by ADO.NET 2 that are of partic- ular interest to developers and readers of this book. Personally, I would like to thank Bob Elliott for keeping me focused on what was going on and working with us to develop this book. Our thanks also go out to the editorial staff at Wiley. Their help keeping us on track as “life happened” was appreciated. The work of our technical editor, Anand Narayanaswamy, was impressive, and his attention to detail was great. Many other people behind the scenes have worked hard on the book. By pulling this group together, Wiley created a team that was dedicated to creating the best possible book on ADO.NET Version 2. For that, we are truly appreciative. — Wallace B. McClure and the author team
Contents Acknowledgments xi Contents xiii Introduction xxv What This Book Is About xxv Who Should Buy This Book xxv How to Use This Book xxvi What This Book Covers xxvi Providing Feedback xxvii Chapter 1: History of Data Access 1 The Early Days 2 CODASYL 2 IMS 3 Relational Databases 3 The Birth of Universal Data Access 4 ODBC 4 OLE-DB 5 Data Access Consumers 6 DAO 6 RDO 7 ADO 8 ADO.NET 10 ADO.NET 2.0 11 Summary 12 For More Information 12 Chapter 2: Standardized Database Objects and Design 13 Creating Databases 13 Naming Conventions 15 Tables 15 Stored Procedures 17 Primary Keys 19 Foreign Keys 20 Indexes 22
Contents Views 22 Help with Normalization 22 Enforcing Security 23 Creating Compiled Views of Data (Reports) 23 Normalizing 24 Why Normalize Data? 24 Types of Normalization 25 Designing a Normalized Database 26 Ensuring Quality Data 26 Making a Flat Database Normalized 28 A Black Cloud on the Horizon 30 Working with Someone Else’s Database 30 Don’t Make It Worse 31 Using Views for Database Abstraction 31 Using ADO.NET to Create a Normalized View of the Data 32 Building Strongly Typed Business Objects 33 Bringing Them Together 34 To Delete or Not to Delete . . . 35 Getting at the Data from Your Code 35 Summary 37 For More Information 37 Chapter 3: ADO.NET Essentials 39 Not Another ADO Release! 39 No Revolutions 40 Obsolete APIs 40 APIs in Their Twilight Months 41 The Generic Factory Model 42 Providers 43 ADO.NET to the Rescue 44 DbProviderFactories 46 Generic Factory versus Specific Providers 47 Pros 47 Cons 47 Writing a Helper Class 48 Data Sources 51 Connection Strings 53 Connection String Collection 54 Intelligent Connection Strings 54 Provider-Specific Connection Strings 55 xiv
Contents Conditionally Adding Connection String Parameters 57 Provider-Specific Connection String Parameters 57 Connections 60 Opening a Connection (and Making Sure It’s Open) 60 Closing a Connection (and Making Sure It’s Closed) 61 Managing Exceptions 62 Provider-Specific Features 66 Schema Metadata 67 Available Information 68 Restrictions 73 Reserved Words 75 Source of the Schema Information 76 Uses for Schema Metadata 77 Commands 77 DbCommand from a Factory 77 DbCommand from a DbConnection 78 Provider-Specific Commands 78 QuoteIdentifier and UnquotedIdentifier 78 Adding DbParameters to a DbCommand 79 Parameters Are Not Generic 80 ExecuteNonQuery 81 ExecuteReader 81 ExecuteScalar 83 Output Parameters, Return Codes, Scalars, and DataReaders 84 DataSet 88 Manually Populating a DataSet 89 Using DataAdapters 89 Using DataReaders 90 DataTable 90 RowState 91 DataView 91 Serialization 92 DataTableReader 93 Streaming 93 Namespace Qualified Tables 94 Indexing Engine 94 DataSet, DataTable, DataReader, or an Object? 95 Showing Data to Users without Any Manipulation 95 Editing Data That Lives in One Table 95 Editing Data Spread across More Than One Table 95 xv
Contents Editing Continuously Updated Data for Which the Chance of Collisions Is High 96 Getting One Value from a Database 96 Summary 96 For More Information 96 Chapter 4: Standard Data Types 97 Data Types in SQL Server 98 CHAR (CHARACTER) 98 VARCHAR (CHAR VARYING or CHARACTER VARYING) 98 TEXT and VARCHAR(MAX) 99 National Character Storage 99 INT (INTEGER) 99 BIGINT 100 SMALLINT 100 TINYINT 100 DATETIME (TIMESTAMP) 100 SMALLDATETIME 100 REAL (FLOAT(24)) and FLOAT (FLOAT and DOUBLE PRECISION) 101 NUMERIC and DECIMAL (DEC and DEC(p,s)) 101 MONEY and SMALLMONEY 102 BINARY, VARBINARY, IMAGE, and VARBINARY(MAX) (BINARY VARYING) 102 Using BINARY to Store Flags 103 BIT 107 TIMESTAMP 107 SQL_VARIANT 107 UNIQUEIDENTIFIER 108 XML 108 TABLE and CURSOR 108 Data Types in ADO.NET and .NET 109 SqlTypes 109 Other Alternatives 113 Mapping SQL Server Data Types to .NET 114 Summary 116 For More Information 116 Chapter 5: ADO.NET Integration with XML 117 What This Chapter Covers 118 Where XML Is Today 119 Data Exchange 119 XML Web Services 119 xvi
Contents Configuration Files 120 Text Markup 120 Design Goals for System.Xml 2.0 120 XmlReader and XmlWriter 121 Factory Methods 121 Easier Object Serialization 122 Conversion between XML Types and Framework Types 123 Other XmlReader Enhancements 124 Designer Enhancements 124 XML Designer 124 XSL Debugging 125 XSD Enhancements 126 Security 127 XPathDocument 128 Editing 128 Validation 131 Change Notification 133 XSLT Improvements 134 Performance 134 Where XML Is Heading 135 XPath 2.0 135 XSLT 2.0 and XQuery 135 XML Views 136 ObjectSpaces 136 Summary 137 For More Information 137 Chapter 6: Transactions 139 Basic Concepts 139 A.C.I.D 140 Transaction Types 141 Isolation Levels 142 Creating a Local Transaction 143 Distributed Transactions 145 Distributed Transactions in ADO.NET 2.0 146 Monitoring Transactions and Their Performance 149 How Does This Affect Local Data? 150 Nested Transactions 153 Transactions in Web Services 155 Flow-Through Transactions 156 xvii
Contents Getting System.Transactions to Work Correctly 156 Summary 157 For More Information 157 Chapter 7: Data Binding 159 Windows Forms versus Web Applications 159 The Concept of Data Binding 160 Options for Getting the Data 160 One-Way Binding versus Two-Way Binding 163 Data Binding in ASP.NET 163 Benefits of the Data Source Concept 164 Data Source Controls Provided with ASP .NET 2.0 164 Passing Parameters to Data Source Controls 165 Validation of Parameter Data 166 Data Source Caching 167 The ASP .NET UI Controls 169 SQLDataSource Control 170 Beware of Provider-Specific Syntax 175 ObjectDataSource Control 176 Table Adapter and Typed DataSets 185 Generating DataSource Controls 189 Windows Forms Applications 191 Where Did My Data Components Go? 192 Dragging and Dropping a Table 192 Data Sources 192 The Windows Form UI Controls 197 Summary 199 For More Information 199 Chapter 8: Building a Custom ADO.NET Data Provider 201 A Brief Overview 202 AdsConnection 203 AdsConnectionStringBuilder 209 AdsCommand 211 AdsDataReader 219 User Properties versus Active Directory Properties 221 Other AdsDataReader Members 226 AdsDataAdapter 228 AdsFactory 231 xviii