Professional ADO.NET 2 : Programming with SQL Server 2005, Oracle, and MySQL

ADO.NET revolutionized the way data was accessed through SQL Server, Oracle, and MySQL. With Microsoft's release of ADO.NET 2, ADO and the .NET Framework are integrated with SQL Server for the first time-enabling you to program .NET applications directly within the SQL Server database. Packed with sample code and recommended best practices for using ADO.NET 2, this code-intensive book explores the new data types that are available in the 2.0 Framework and discusses the appropriate time and way to use them. You'll learn how to make repetitive, mundane tasks much simpler and you'll walk away with a solid foundation for developing database-driven applications. What you will learn from this book The basics of creating a connection, executing a query, and returning a result Best uses for Oracle in the ADO.NET Framework The many new features that are available for XML How to use the full text search capabilities of Microsoft(r) SQL Server 2005 Methods for retrieving data and presenting it in various ways Why MySQL is a viable option for data storage Who this book is for This book is for experienced database developers who want to learn the latest release of ADO.NET 2.0. Knowledge of ADO.NET 1.0, general .NET development, and Microsoft SQL Server is necessary. Wrox Professional guides are planned and written by working programmers to meet the real-world needs of programmers, developers, and IT professionals. Focused and relevant, they address the issues technology professionals face every day. They provide examples, practical solutions, and expert education in new technologies, all designed to help programmers do a better job. Professional ADO.NET 2......Page 2 About the Authors......Page 7 Acknowledgments......Page 13 Contents......Page 15 Who Should Buy This Book......Page 27 What This Book Covers......Page 28 Providing Feedback......Page 30 1. History of Data Access......Page 31 CODASYL......Page 32 Relational Databases......Page 33 ODBC......Page 34 OLE-DB Consumers......Page 35 DAO......Page 36 RDO......Page 37 ADO......Page 38 ADO.NET......Page 40 ADO.NET 2.0......Page 41 For More Information......Page 42 Creating Databases......Page 43 Tables......Page 45 Columns......Page 46 Stored Procedures......Page 47 Primary Keys......Page 49 Foreign Keys......Page 50 Help with Normalization......Page 52 Creating Compiled Views of Data (Reports)......Page 53 Why Normalize Data?......Page 54 Real-world Normalization......Page 55 Ensuring Quality Data......Page 56 Making a Flat Database Normalized......Page 58 Working with Someone Else’s Database......Page 60 Using Views for Database Abstraction......Page 61 Using ADO.NET to Create a Normalized View of the Data......Page 62 Building Strongly Typed Business Objects......Page 63 Bringing Them Together......Page 64 Getting at the Data from Your Code......Page 65 For More Information......Page 67 Not Another ADO Release!......Page 69 Obsolete APIs......Page 70 APIs in Their Twilight Months......Page 71 The Generic Factory Model......Page 72 Providers......Page 73 ADO.NET to the Rescue......Page 74 DbProviderFactories......Page 76 Cons......Page 77 Writing a Helper Class......Page 78 Data Sources......Page 81 Connection Strings......Page 83 Intelligent Connection Strings......Page 84 Provider-Specific Connection Strings......Page 85 Provider-Specific Connection String Parameters......Page 87 Opening a Connection (and Making Sure It’s Open)......Page 90 Closing a Connection (and Making Sure It’s Closed)......Page 91 Managing Exceptions......Page 92 Provider-Specific Features......Page 96 Schema Metadata......Page 97 Available Information......Page 98 CompositeIdentifierSeparatorPattern......Page 99 IdentifierCase......Page 100 ParameterNamePattern......Page 101 SupportedJoinOperators......Page 102 Restrictions......Page 103 Reserved Words......Page 105 Source of the Schema Information......Page 106 DbCommand from a Factory......Page 107 QuoteIdentifier and UnquotedIdentifier......Page 108 Adding DbParameters to a DbCommand......Page 109 Parameters Are Not Generic......Page 110 ExecuteReader......Page 111 CloseConnection......Page 112 ExecuteScalar......Page 113 Output Parameters......Page 114 Return Codes......Page 115 Scalars......Page 117 DataSet......Page 118 Using DataAdapters......Page 119 DataTable......Page 120 DataView......Page 121 Serialization......Page 122 Streaming......Page 123 Indexing Engine......Page 124 Editing Data Spread across More Than One Table......Page 125 For More Information......Page 126 4. Standard Data Types......Page 127 VARCHAR (CHAR VARYING or CHARACTER VARYING)......Page 128 INT (INTEGER)......Page 129 SMALLDATETIME......Page 130 NUMERIC and DECIMAL (DEC and DEC(p,s))......Page 131 BINARY, VARBINARY, IMAGE, and VARBINARY(MAX) (BINARY VARYING)......Page 132 Using BINARY to Store Flags......Page 133 SQL_VARIANT......Page 137 TABLE and CURSOR......Page 138 SqlTypes......Page 139 Other Alternatives......Page 143 Mapping SQL Server Data Types to .NET......Page 144 For More Information......Page 146 5. ADO.NET Integration with XML......Page 147 What This Chapter Covers......Page 148 XML Web Services......Page 149 Design Goals for System.Xml 2.0......Page 150 Factory Methods......Page 151 Easier Object Serialization......Page 152 Conversion Between XML Types and Framework Types......Page 153 XML Designer......Page 154 XSL Debugging......Page 155 XSD Enhancements......Page 156 Code Access Security......Page 157 Editing......Page 158 Validating Output......Page 161 Schema Inference......Page 162 Change Notification......Page 163 Performance......Page 164 XSLT 2.0 and XQuery......Page 165 ObjectSpaces......Page 166 For More Information......Page 167 Basic Concepts......Page 169 A.C.I.D......Page 170 Transaction Types......Page 171 Isolation Levels......Page 172 Creating a Local Transaction......Page 173 Distributed Transactions......Page 175 Distributed Transactions in ADO.NET 2.0......Page 176 Monitoring Transactions and Their Performance......Page 179 How Does This Affect Local Data?......Page 180 Nested Transactions......Page 183 Transactions in Web Services......Page 185 Getting System.Transactions to Work Correctly......Page 186 For More Information......Page 187 Windows Forms versus Web Applications......Page 189 Options for Getting the Data......Page 190 Data Binding in ASP.NET......Page 193 Data Source Controls Provided with ASP.NET 2.0......Page 194 Passing Parameters to Data Source Controls......Page 195 Validation of Parameter Data......Page 196 Data Source Caching......Page 197 Choosing a Caching Technique......Page 198 The ASP.NET UI Controls......Page 199 Configuring the SQLDataSource Using the Wizard......Page 200 Dragging and Dropping a Table......Page 203 Key Properties of the SQLDataSource......Page 204 Beware of Provider-Specific Syntax......Page 205 ObjectDataSource Control......Page 206 Key Properties of the ObjectDataSource Control......Page 207 Key Events on the ObjectDataSource......Page 208 Supporting Paging......Page 209 Building a Class to Work with ObjectDataSource......Page 210 Using Your Objects on the Page......Page 212 Defining the Parameters......Page 213 Modifications after the Wizard Completes......Page 214 Table Adapter and Typed DataSets......Page 215 DBDirect Methods......Page 216 Configuring the Connection......Page 217 Configuring Stored Procedure Names......Page 218 Generating DataSource Controls......Page 219 Windows Forms Applications......Page 221 Data Sources......Page 222 Adding and Using a Database Data Source......Page 223 Choosing Your Database Objects......Page 224 Adding and Using an Object Data Source......Page 225 Choosing the Type of Control for a Data Source......Page 226 BindingSource......Page 227 BindingNavigator......Page 228 For More Information......Page 229 8. Building a Custom ADO.NET Data Provider......Page 231 A Brief Overview......Page 232 AdsConnection......Page 233 AdsConnectionStringBuilder......Page 239 AdsCommand......Page 241 AdsDataReader......Page 249 Tables......Page 251 Columns......Page 252 Other AdsDataReader Members......Page 256 AdsDataAdapter......Page 258 AdsFactory......Page 261 Summary......Page 263 For More Information......Page 264 9. T-SQL Language and Enhancements......Page 265 An In-Depth Look at T-SQL......Page 268 Structured Exception Handling......Page 270 OUTPUT......Page 279 Top X......Page 280 Common Table Expressions......Page 282 PIVOT......Page 284 ROW_NUMBER()......Page 286 RANK()......Page 287 NTile()......Page 288 TABLESAMPLE......Page 289 WAITFOR......Page 292 DDL Triggers......Page 293 For More Information......Page 294 SQL Server and ADO.NET......Page 295 Asynchronous Commands in ADO.NET......Page 296 BeginExecuteNonQuery......Page 297 Asynchronous Callback Object Version......Page 299 Asynchronous Operations with No Callback Object......Page 300 BeginExecuteXMLReader......Page 301 Asynchronous Operations in ASP.NET......Page 302 IAsyncResult......Page 305 Multiple Active Result Sets......Page 306 Technical Issues with MARS......Page 307 Enumerating SQL Servers......Page 308 Connection Pooling......Page 309 Password Management......Page 310 Building a SQL Connection with the SqlConnectionStringBuilder Class......Page 311 SQL Server Types (SqlTypes)......Page 313 Structures......Page 314 Using SqlTypes......Page 317 Using SqlDbType......Page 320 Bulk Copy with SQL Server......Page 321 Provider Statistics......Page 322 How to Use the SqlCacheDependency in an ASP.NET Application......Page 323 SQL Server 2005......Page 324 SQL Server 7 and 2000......Page 325 Uses for SqlCacheDependency......Page 326 SqlDependency......Page 327 For More Information......Page 330 Extended Stored Procedures......Page 331 Set-Based Programming......Page 332 Using CLR Objects......Page 333 Creating CLR Objects......Page 334 SQL Server Projects......Page 336 Deployment......Page 337 Debugging......Page 339 Assemblies and CLR Object Tables/Functions......Page 340 SqlTriggerContext......Page 341 EventData for DDL Triggers......Page 343 Sample Code......Page 344 Sample Code......Page 348 Scalar-Valued Functions......Page 349 Table-Valued Functions......Page 350 Aggregates......Page 351 Calling a CLR User-Defined Aggregate Function......Page 353 User-Defined Sample Code......Page 354 Manually Creating Tabular Results......Page 356 The Context Connection......Page 357 Placing Business Logic......Page 358 SQL Server Management Objects......Page 359 Utility Classes......Page 360 Creating Objects......Page 361 Creating a Database......Page 362 Creating a Table, Its Columns, and an Index......Page 364 Creating a User......Page 366 Creating a Foreign Key......Page 367 Backup......Page 368 Restore......Page 370 Scripting with SMO......Page 371 Simple Scripting......Page 372 XML Web Services Processing in the Database......Page 373 Creating an Endpoint......Page 374 For More Information......Page 376 12. Notification Services......Page 377 Subscription Management Application......Page 378 Generator......Page 379 Configuring the Instance......Page 380 The Application Definition File......Page 382 Adding the SQL NS Instance......Page 386 Retrieving a List of Subscribers......Page 387 Adding/Removing a Subscriber......Page 388 Device List......Page 390 Device Edit......Page 391 Subscription List......Page 393 Subscription Edit......Page 394 Firing an Event......Page 397 For More Information......Page 398 An Introduction to Service Broker......Page 399 Scenario......Page 401 Setup......Page 402 Message Types......Page 404 Contracts......Page 407 Queues......Page 408 Services......Page 410 The Web Application......Page 411 Customer Sign-Up Service......Page 413 E-mail Sending Service......Page 416 Routes......Page 420 Queues......Page 424 Service Broker Catalog Views......Page 426 Making It Easier......Page 429 Summary......Page 430 For More Information......Page 431 What Is Full-Text Searching?......Page 433 Terminology......Page 434 What Does a Full-Text Index Look Like?......Page 435 Automatic Change Tracking Population......Page 436 DDL Setup......Page 437 Setup with the SQL Server Management Studio......Page 438 Full-Text Properties......Page 441 CONTAINS......Page 442 FREETEXT......Page 444 FREETEXTTABLE......Page 445 FORMSOF......Page 446 NEAR......Page 447 Language Settings......Page 448 Ranking......Page 449 How Results Are Ranked......Page 450 Transforming Noise Words......Page 451 BLOBs......Page 452 Extending Full-Text Search with Filters......Page 455 For More Information......Page 456 15. Reporting......Page 457 SQL Server Setup......Page 458 Web Application, or “Look Ma, No Code ...” (Part One)......Page 460 Our Simple Report in Reporting Services, or “Look Ma, no Code ...” (Part 2)......Page 462 A Brief Primer on Report Definition Language......Page 466 Using the Reporting Service Server......Page 468 Contacting Reporting Services via an URL......Page 469 The rs Namespace......Page 470 The rc Namespace......Page 472 URL Parameters in Action......Page 473 Using SOAP to Contact the Report Server......Page 477 Setting up the Web Application......Page 478 DeviceInfo......Page 483 EXCEL......Page 484 HTML......Page 485 IMAGE......Page 487 PDF......Page 488 XML......Page 489 Building Report Models, or How to Slough Some of the Work off on the Biz Guys!......Page 490 Creating the Model......Page 491 Creating a Report......Page 492 Summary......Page 494 For More Information......Page 495 Open Source and Licensing......Page 497 MySQL......Page 498 SQL Compliance......Page 499 Database Features......Page 503 Working with .NET......Page 505 MaxDB (formerly SAPdb)......Page 507 SQL Compliance......Page 508 Database Features......Page 509 Installation......Page 511 Working with .NET......Page 513 Resource Information......Page 514 Database Features......Page 515 Installation......Page 518 Working with .NET......Page 519 Resource Information......Page 521 Ingres......Page 522 Database Features......Page 523 Installation......Page 524 Working with .NET......Page 525 Resource Information......Page 526 PostgreSQL......Page 527 Database Features......Page 528 Installation......Page 529 Working with .NET......Page 530 Resource Information......Page 531 Database Features......Page 532 Working with .NET......Page 534 SQL Compliance......Page 538 Database Features......Page 539 Working with .NET......Page 540 Resource Information......Page 541 Summary......Page 542 For More Information......Page 543 Choosing an Oracle Data Provider......Page 545 Common Provider Classes......Page 546 Microsoft Oracle Provider......Page 547 Oracle Data Provider for .NET (ODP.NET)......Page 548 Oracle Developer Tools for Visual Studio .NET......Page 549 Getting Started with Oracle Explorer......Page 552 Getting a Connection......Page 553 Array Binding......Page 556 Filling a DataTable......Page 559 Generically Filling a DataTable......Page 560 Using the OracleDataReader......Page 561 Inserting New Rows......Page 562 OracleException Class......Page 563 Using Tracing to Find Problems......Page 564 Simple Package Definition......Page 565 Regular Expression Support......Page 566 Database Change Notification......Page 568 Using Notification with ASP.NET......Page 569 Oracle Services for Microsoft Transaction Server......Page 573 Building a .NET Stored Procedure......Page 574 Step 3—Deploy the New Stored Procedure......Page 575 For More Information......Page 578 Appendix A. Constants/Enums in ADO.NET......Page 579 Index......Page 615 A......Page 617 B......Page 618 C......Page 619 D......Page 621 E......Page 624 F......Page 625 I......Page 626 J......Page 627 M......Page 628 O......Page 630 P......Page 632 R......Page 634 S......Page 635 T......Page 640 U......Page 641 W......Page 642 X......Page 643 Z......Page 644