Hitchhiker's Guide to Visual Studio and SQL Server Best Practice Architectures and Examples

by Vaughn, William R.; Blackburn, Peter

• ISBN13: 9780321243621
• ISBN10: 0321243625
• Edition: 7th
• Format: Paperback
• Copyright: 2006-11-02
• Publisher: Addison-Wesley Professional

Summary

Since 1994 when he wrote his first "Hitchhikerrs"s Guide", William Vaughn has been providing developers all over the world the intimate details of how SQL Server can be accessed and managed from RAD languages like Visual Basic and Visual Basic .NET. With the 7th Edition, Bill has completely rewritten this encyclopedic work from cover to covergiving readers his insightful views on how applications should be built to maximize both developer and code performance. Visual Studio and the languages it hosts have never been as sophisticated as they are todaythe same can be said for SQL Server. This makes it even more important for developers to understand how to best leverage their features without being held back by their complexity. Thatrs"s what this book is all aboutmaking it easier for developers regardless of their know-how. The 7th edition is unique in that itrs"s designed to provide not only up-to-date tutorials on the latest development tools provided by Visual Studio and SQL Server, but also a solid platform of architectural advice and rich examples for developers trying to choose between the myriad of platform options. Beginners and experts alike will find comprehensive step-by-step instructions that can make the readerrs"s introduction to the latest versions of Visual Studio and SQL Server far easier. Key topic coverage includes: bull; Data access architectures and how to choose the best strategy for Windows Forms, ASP.NET, XML Web Services, and SQL Server CLR executables. Where do these make sense and how much will they cost to build and maintain? bull; SQL Server and relational database fundamentals and inner-machinery. How does SQL Server work and why is it important that developers know? bull; Making the development experience more productive through judicious use of the Visual Studio toolset, and how to know when the wizards can help. bull; Using the latest ADO.NET data provider efficiently and safely. bull; How to protect the security of your databaseand your jobby avoiding common mistakes. bull; How to build secure, efficient, scalable applications in less time with fewer resourceshow to create faster code faster. bull; How to leverage the potential of SQL Server CLR executables and knowing when these features make sense. bull; How to work with your DBA to maintain database integrity and security. bull; Working with the new Visual Studio report controls to expose your organizationrs"s data safely and easily with or without leveraging existing SQL Server Reporting Services technology. William R. Vaughnis the President of Beta V Corporation and a Microsoft MVP. In 2000 he retired from Microsoft after 14 years to focus on mentoring, speaking, and writing. His specialty is data access application design especially when connecting to SQL Serverover the years hers"s written 12 books on the subject. Hers"s a popular speaker at technical conferences all over the world where his wit and no-holds-barred technical insights win him rave reviews. William is also a member of the prestigious INETA Speakerrs"s Bureau. His works include articles for SQL Server Magazine and a bi-weekly editorial for Processor Magazine as well as books published by Microsoft Press and Apress.

William R. Vaughn is the President of Beta V Corporation and a Microsoft MVP

Table of Contents

Foreword	p. xxi
Acknowledgments	p. xxiii
About the Authors	p. xxv
Introduction	p. 1
Exploring Application Architectures	p. 9
Introduction	p. 9
Choosing the "Right" Architecture	p. 10
Understanding Your Toolset	p. 12
A Brief History of ADO and ADO.NET	p. 13
Jet and ODBC Are Born	p. 13
Data Access Objects (DAO) Is Born	p. 15
OLE DB Is Spawned	p. 16
AD0.NET Is Created	p. 17
Recognizing Application Design Constraints	p. 19
What Makes "Good," "Better," and "Best" Designs?	p. 20
Designing Before Implementing	p. 23
Choosing the Right Data Access Interface	p. 25
Choosing the Right Data Access Provider	p. 26
What About SQL Server Everywhere Edition?	p. 27
Using the SQL Server Native Client	p. 35
Choosing the Right Database Management System	p. 35
What Does SQL Server Cost?	p. 35
What About SQL Server Security?	p. 36
What About Performance?	p. 38
What About Multi-User Issues?	p. 48
What About Scalability?	p. 50
What Are the Limits?	p. 51
What Can Get in the Way on the Client?	p. 53
What Can Get in the Way on the Server?	p. 54
What About Maintenance and Administration?	p. 55
Managing Maintenance Tasks	p. 56
The Importance of Restoring Data	p. 56
Managing Resources	p. 62
Understanding Basic Data Access Architectures	p. 63
Multi-Tiered Designs	p. 64
Building Stand-Alone Applications	p. 67
ASP Browser-Based Architectures	p. 84
XML Web Service Architectures	p. 85
Summary	p. 86
How Does SQL Server Work?	p. 87
Introduction	p. 87
Servers and Versions	p. 88
SQL Server Side-By-Side	p. 90
Introducing...SQL Server Express Edition	p. 92
What About the Workgroup Edition?	p. 102
Installing SQL Server	p. 103
Running SQL Server Services	p. 111
Inspecting SQL Server Services	p. 111
Starting a SQL Server Service	p. 113
Touring the SQL Server System Databases	p. 115
SQL Server Instances	p. 115
Multiple Instances or Multiple Databases?	p. 116
User and System Databases	p. 117
Creating a User Database	p. 119
Managing User Databases	p. 120
Understanding the SQL Server Security System	p. 120
Addressing Objects in SQL Server 2005	p. 121
How SQL Server 2005 Addresses the Ownership Chain Issue	p. 126
Understanding Logins, Usernames, and Schemas	p. 127
Creating Security Personas	p. 134
Managing SQL Server Connections	p. 135
Understanding the Data Access Interface Role	p. 136
Understanding the Protocols	p. 136
Opening a Connection	p. 137
Debugging a Connection	p. 141
Choosing a Connection Strategy	p. 145
Using the SQL Query Tools	p. 147
Creating a New Query	p. 148
Creating Select Queries	p. 150
Using Aliases for Table and Column Names	p. 153
Walking Through the Select Operation	p. 154
Understanding the Where Clause	p. 155
Using Parameters to Pass Literal Values	p. 156
Limiting the Number of Rows Returned	p. 157
Using the Where Clause to Perform Joins	p. 158
Dealing with Special-Case Query Problems	p. 164
Creating and Querying Views	p. 178
T-SQL Views	p. 179
Database Snapshots	p. 182
Implementing Business Rules	p. 182
Implementing Business Rules with Extended Properties	p. 184
Implementing Business Rules with T-SQL Rules, Constraints, and Defaults	p. 190
User-Defined (Aliased) Types	p. 193
Implementing Constraints	p. 194
Managing Databases and Queries with Batches and Scripts	p. 197
Scripts Versus Batches	p. 198
The Query Optimizer and the Query Plan	p. 199
Viewing and Tuning the Query Plan	p. 200
Examining Client Statistics and Alternate Plans	p. 201
Executing the Query Plan	p. 206
Understanding the Buffer Cache	p. 207
Managing Cached Query Plans	p. 207
Executing Action Commands	p. 210
Inserting New Rows	p. 211
Updating Rows	p. 219
Introducing Stored Procedures	p. 231
Introducing Triggers	p. 233
Creating a DML Trigger	p. 234
Creating a DDL Trigger	p. 235
Using Transactions to Protect Data Integrity	p. 235
Administrative Functions	p. 237
Attaching a .MDF Database File	p. 237
Setting the Auto Close Option	p. 239
Importing and Exporting Data	p. 240
Backing Up SQL Server Databases	p. 240
Working with the Transaction Log	p. 242
Summary	p. 243
Relational Databases 101	p. 245
Introduction	p. 245
Getting Started with Solid Database Design	p. 246
Understanding Relational Database Normalization	p. 248
Creating Tables, Rows, and Columns	p. 251
How SQL Server Stores Relational Databases	p. 252
Choosing the Right Data Type	p. 265
Summary	p. 272
Getting Started with Visual Studio	p. 273
Introduction	p. 273
How I Got Here	p. 274
Installing the "Right" Version of Visual Studio	p. 275
Dealing with CTP or Beta Shards	p. 276
Walking Through a Typical Installation	p. 276
Installing the MSDN Documentation	p. 281
Launching Visual Studio	p. 282
Installing the Visual Studio 2005 Image Library	p. 283
Customizing Visual Studio 2005	p. 284
Saving and Loading Custom Configurations	p. 287
Creating Custom Project Templates	p. 289
Configuring Online or Local Help	p. 296
Configuring Server (or Database) Explorers	p. 297
Creating a New Database with the Server Explorer	p. 298
Creating a New Database in Code	p. 300
Creating and Managing Database Connections	p. 301
Managing Database Objects with the Server Explorer	p. 309
Changing Existing Objects	p. 321
Managing Queries with the Query Designer	p. 323
Getting Familiar with the Query Designer	p. 324
The Query Designer Panes	p. 324
What's a "Derived" Table?	p. 327
Changing the Query Type	p. 328
The Query Designer-a Recap	p. 337
Using the Server Explorer to View Synonyms	p. 338
Using the Server Explorer to Manage Types	p. 340
Using the Server Explorer to Manage Assemblies	p. 343
Using the Server Explorer to Manage Servers	p. 343
Summary	p. 347
Managing Executables with the Server Explorer	p. 349
Introduction	p. 349
Creating and Editing Stored Procedures	p. 350
Comparing SQL Server 2005 and Visual Studio 2005	p. 351
Enter the SQL Server Management Studio Express	p. 352
Creating New Stored Procedures	p. 352
Creating and Editing Complex Stored Procedures	p. 359
Executing T-SQL with Run Selection	p. 363
Debugging Stored Procedures	p. 364
T-SQL Debugging Against a Local Instance	p. 365
Debugging with Breakpoints Within Stored Procedures	p. 367
Debugging Stored Procedures on Remote Instances	p. 371
Debugging Stored Procedures from Code	p. 375
Using the Server Explorer to Manage Functions	p. 377
Creating T-SQL Functions with the Server Explorer	p. 380
Summary	p. 389
Building Data Sources, DataSets, and TableAdapters	p. 391
Why Create YADAI?	p. 392
Is Strongly Typed Data Important?	p. 394
What Is a Data Source?	p. 396
What Is a TableAdapter?	p. 400
What's Missing in the TableAdapter?	p. 402
Creating Database-Based Data Sources	p. 403
Building a Single-Table TableAdapter	p. 404
Configuring a TableAdapter	p. 412
Adding Parameters to a Query	p. 422
Choosing Query Methods to Generate	p. 425
Binding to the TableAdapter Using Drag and Drop	p. 429
Customizing the Generated UI	p. 432
Using SmartTags to Manage Bound Controls	p. 434
Binding to "Details"	p. 435
Binding to Custom Controls	p. 436
Using a TableAdapter Directly-Without Drag-and-Drop?	p. 436
Managing DataTable Classes in the Data Source Designer	p. 438
The Generated Form Code	p. 440
Moving Data Sources Between Applications	p. 441
Creating Web Service Data Sources	p. 442
What Is a Web Service?	p. 443
Constructing a Sample Web Service	p. 444
Consuming and Testing Web Services	p. 454
Exposing the Web Service Data Source in the UI	p. 457
Summary	p. 459
Managing Data Tools and Data Binding	p. 461
Introduction	p. 461
Touring the Visual Studio Toolbox	p. 463
Exposing Deprecated Data Access Controls	p. 464
Finding the Right Control	p. 465
Understanding the Tool Tray	p. 466
Using the DataSet Toolbox Element	p. 467
Introduction to Data Binding	p. 468
Complex Versus Simple Binding	p. 470
Using the BindingSource Class	p. 476
Using the EndEdit and CancelEdit Methods	p. 485
Data Binding Using the BindingSource Class	p. 488
Using the BindingNavigator Control	p. 489
Using the DataGridView Control	p. 492
Using the ProgressBar Control	p. 494
Summary	p. 495
Getting Started with ADO.NET	p. 497
Introduction	p. 497
Approaching Data Access Challenges	p. 498
ADO.NET from 50,000 Feet	p. 500
Working with Data Sources	p. 501
Using the Visual Studio Object Browser to Explore ADO.NET	p. 502
Exploring ADO.NET with a Class Diagram	p. 504
Deciphering the Namespaces	p. 508
Getting a Handle on the Class Names	p. 508
Using Shorthand to Address Classes	p. 509
Instantiating ADO.NET Objects	p. 510
Understanding Code Scope	p. 510
Understanding Object Instantiation	p. 512
The Using Block in Visual Basic.NET	p. 514
Exploring the System.Data.SqlClient Namespace	p. 515
Choosing the Right Provider-When it's Not SQL Server	p. 516
Exploring the SqlClient Namespace	p. 518
The SqlConnection Class	p. 519
Exploring the System.Data Namespace	p. 528
Deciphering System.Data Naming Conventions	p. 529
Understanding the System.Data.DataSet and DataTable Objects	p. 530
To Join or Not to Join-That's The Question	p. 545
Summary	p. 547
Getting Connected	p. 549
Introduction	p. 549
Connectivity Strategies-That Include Security	p. 549
Choosing a Connection Strategy	p. 550
Configuring the Server and Firewall	p. 552
Connection Strategies	p. 559
Connecting Just in Time	p. 560
Persistent Connections	p. 561
Understanding MARS and Its Implications	p. 562
Choosing the Right Scope for the Connection Object	p. 563
Establishing a Connection	p. 564
Finding Servers and Instances in your Domain	p. 565
Writing Code to Create Connections	p. 568
What Is a ConnectionString?	p. 569
Building a ConnectionString with the SqlConnectionStringBuilder Class	p. 569
Protecting the ConnectionString	p. 571
Persisting the ConnectionString	p. 571
Building a Connection Object	p. 573
Setting the Server Name Keyword	p. 578
Establishing an Alias to Your Instance	p. 580
Revisiting Other Keywords	p. 581
Configuring the Connection Pool Keyword Values	p. 584
Understanding and Managing the Connection Pool	p. 585
Monitoring the Connection Pool	p. 596
Keeping the Connection Pool from Overflowing	p. 597
Building a ConnectionString for Other Providers	p. 599
Getting Visual Studio to Build Your ConnectionString	p. 602
Opening and Closing Connections (Semi-) Automatically	p. 607
Understanding the Connection Properties	p. 607
Using the Connection Methods	p. 610
Handling Connection Events	p. 617
Managing Connection Exceptions	p. 619
Sourcing Exceptions	p. 621
Understanding the SqlException Class	p. 621
Dealing with Exceptions	p. 624
Summary	p. 626
Managing SqlCommand Objects	p. 627
Introduction	p. 627
Creating SqlCommand Objects	p. 628
Understanding the SqlCommand Properties	p. 628
Understanding the SqlCommand Methods	p. 636
Understanding the SqlCommand Support Functions	p. 642
Integrating Ad Hoc Queries into Your Application	p. 646
Preventing SQL Injection Attacks	p. 651
Coding Parameter Queries	p. 654
Inserting Ad Hoc Queries	p. 655
Understanding the SqlParameterCollection Class	p. 657
Understanding the SqlParameter Class	p. 659
Validating the Value	p. 675
Writing Code to Validate the Value	p. 677
Handling Complex Parameter Design Issues	p. 682
Understanding SqlCommand Events	p. 686
Executing Stored Procedures	p. 686
Handling Output, Input/Output, and Return Value Parameters	p. 687
Handling Rowsets with Output Parameters	p. 688
Summary	p. 691
Executing SqlCommand Objects	p. 693
Introduction	p. 693
Executing SqlCommand Objects Synchronously	p. 694
Choosing the "Right" Execute Function	p. 695
CommandBehavior.SequentialAccess	p. 703
CommandBehavior.SingleResult and CommandBehavior.SingleRow	p. 703
Understanding the SqlDataReader	p. 704
Fetching Data from the Stream	p. 705
Fetching Metadata from the SqlDataReader Stream	p. 712
SqlDataReader Best Practice Designs	p. 714
Populating Data Structures	p. 714
Efficient Population by Design	p. 715
How Does Population Work?	p. 719
Populating InfoMessage Messages	p. 720
Populating the Output and Return Value Parameters	p. 721
Populating with the Data Table Load Method	p. 722
Populating Additional Resultsets	p. 724
Fetching Rows Asynchronously	p. 729
Executing Commands Asynchronously	p. 732
Coding the Asynchronous SqlCommand Functions	p. 733
Waiting for Godot or Your Asynchronous Operation to Complete	p. 738
Summary	p. 747
Managing Updates	p. 749
Introduction	p. 749
Using Wizards to Generate Update Action Commands	p. 750
What the CommandBuilder Is Supposed to Do	p. 751
Handling Concurrency-Update and Insert Collisions	p. 753
How the ADO.NET CommandBuilder Works	p. 756
Coding the CommandBuilder	p. 757
Update Alternatives: Using the TableAdapter Configuration Wizard	p. 760
Updating with the TableAdapter	p. 760
Managing Generated Parameters	p. 765
Complex Updates with Server-Side Logic	p. 768
Using Events to Execute Your Own Update Logic	p. 769
Stepping Through the UpdateEvents Example	p. 771
Managing Batch Mode Updates	p. 779
Managing Concurrency by Design or Collision	p. 782
Managing an @@Identity Crisis	p. 784
Summary	p. 791
Managing Server Report Parameters	p. 960
Resetting Parameter Values	p. 965
Interesting ServerReport Parameters	p. 965
Interesting ServerReport Methods and Functions	p. 966
Managing Parameters	p. 967
Capturing Parameters	p. 969
Report Parameters	p. 975
Advanced Reporting Techniques	p. 978
Managing Click-Through Reports	p. 979
Using the SubReport Report Item	p. 985
What's in the RDLC?	p. 988
Converting RDL to RDLC	p. 989
Implementing the Matrix Report	p. 991
The Data Considerations for a Report	p. 991
The Initial Select Query	p. 993
Grouping the Data	p. 994
Building the Matrix Report	p. 997
Summary	p. 1009
Summary and Wintry: Where We Are Now	p. 1011
Installing the Examples and Test Databases	p. 1015
Installing the Examples	p. 1015
Installing the Example Databases	p. 1016
Attaching the Example Databases	p. 1017
Establishing an Alias to Your Instance	p. 1017
Chapter-Specific Configuration Issues	p. 1019
Getting Started with ADO.NET	p. 1019
Managing CLR Executables	p. 1020
Summary	p. 1020
Reinstalling the DACW and Other Missing Functionality in Visual Studio	p. 1021
Monitoring SQL Server	p. 1025
Monitoring SQL Server with the SQL Profiler	p. 1026
Adding Filter Criteria	p. 1026
Configuring SQL Profiler	p. 1027
Starting the Trace	p. 1029
Evaluating the Trace	p. 1031
A Few SQL Profiler Tips	p. 1032
Monitoring SQL Server and ADO.NET with Performance Counters	p. 1032
The ADO.NET SqlClient Performance Counters	p. 1035
Summary	p. 1038
Creating and Managing Server-Side Cursors	p. 1039
Why Are Server-side Cursors Important?	p. 1039
How Does ADO.NET Implement Cursors?	p. 1040
How Are Server-Side Cursors Managed?	p. 1041
What Do Server-Side Cursors Cost?	p. 1043
How Can ADO.NET Create a Server-Side Cursor?	p. 1043
Opening, Closing, and Re-opening the Connection	p. 1044
Creating the Cursor	p. 1045
Fetching Data from the Cursor	p. 1046
Fetching the Entire Cursor	p. 1048
Updating with a Server-Side Cursor	p. 1049
Summary	p. 1050
Index	p. 1051
Table of Contents provided by Ingram. All Rights Reserved.

Excerpts

Foreword Foreword In 1992, Microsoft first launched Microsoft Access Version 1.0 as a competitor for FoxPro (which it bought), Btrieve, Paradox, PowerBuilder, and DBASE. Many think that this was Microsoft's first foray into the world of database engines. It wasn't--Microsoft introduced its first DBMS system in 1988 when it joined with Sybase and Ashton-Tate to launch its own version of SQL Server. While designed for use with OS/2, Microsoft quickly moved to leverage the considerable work they had been doing on their new NT operating system. This made the Microsoft version faster, smaller, and more competitive. It would eventually become the market leader in a very competitive DBMS industry. Microsoft's first "RAD" development tool for Windows was created in 1991 with the introduction of Visual Basic. While Visual Basic took three versions to integrate data access into its user interface, Visual Basic was not the first Microsoft BASIC language to access SQL Server. That honor goes to Quick Basic after a Microsoft developer working on the SQL Server team built his own data access interface to make it easier to access SQL Server's only published API interface, DB-Library. Again, Visual Basic has evolved into the world-class Visual Basic 6.0 and more recently Visual Basic .NET languages. Much of the success of SQL Server can be directly tied to the ability of developers at all skill levels to create data access applications that leverage SQL Server's power, scalability, and flexibility. I joined Microsoft in 1988 where I had the pleasure of serving as the first program manager for SQL Server. Our teams worked with Sybase early on and eventually helped create the Open Database Connectivity (ODBC) standard. Later in my career, we helped bring together the Visual Studio family of developer tools and drive the Win32 API, customer requirements, and product specification for the Microsoft Windows NT operating system. Since then, I have held many different roles at Microsoft and most recently have taken on the responsibility of Senior Vice President of the Server and Tools business unit that encompasses both SQL Server and the development languages. During these years, we all witnessed SQL Server's market share grow steadily from an "all other" slice to over 50% market share and Visual Basic grow to be the most popular language on the planet. However, one problem Microsoft faced throughout this evolutionary period was documentation, training, and user-education. Since the SQL Server documentation team focused its attention primarily on SQL Server, and the Visual Studio User Education team focused primarily on user interface, tools, local data, and language issues, a gap was created that only someone familiar with both disciplines could tackle. From the very earliest versions of SQL Server, while working for Microsoft University, Bill Vaughn wrote SQL Server courseware, managed and mentored SQL Server trainers, and wrote books and articles. This entire body of work was focused on helping developers understand how SQL Server and development languages like Visual Basic and Visual Basic .NET can be used to best leverage the power of SQL Server. His efforts to tie SQL Server and Visual Basic together were continued while he worked for the Visual Basic user education team and since retired from Microsoft in 2000. I first met Bill in 1988, and he was often a thorn in my team's side as he lobbied for more emphasis on SQL Server and IT data issues and better access to SQL Server's sophisticated features. It was clear that Bill was not afraid to express his opinions on how the languages and tools should be adapted to handle what he called "IT data." I guess this is because Bill had worked predominantly with very large databases at Electronic Data Systems and earlier