| |
| |
Foreword | |
| |
| |
Acknowledgments | |
| |
| |
About the Authors | |
| |
| |
Introduction | |
| |
| |
| |
Exploring Application Architectures | |
| |
| |
Introduction | |
| |
| |
Choosing the "Right" Architecture | |
| |
| |
Understanding Your Toolset | |
| |
| |
A Brief History of ADO and ADO.NET | |
| |
| |
Jet and ODBC Are Born | |
| |
| |
Data Access Objects (DAO) Is Born | |
| |
| |
OLE DB Is Spawned | |
| |
| |
AD0.NET Is Created | |
| |
| |
Recognizing Application Design Constraints | |
| |
| |
What Makes "Good," "Better," and "Best" Designs? | |
| |
| |
Designing Before Implementing | |
| |
| |
Choosing the Right Data Access Interface | |
| |
| |
Choosing the Right Data Access Provider | |
| |
| |
What About SQL Server Everywhere Edition? | |
| |
| |
Using the SQL Server Native Client | |
| |
| |
Choosing the Right Database Management System | |
| |
| |
What Does SQL Server Cost? | |
| |
| |
What About SQL Server Security? | |
| |
| |
What About Performance? | |
| |
| |
What About Multi-User Issues? | |
| |
| |
What About Scalability? | |
| |
| |
What Are the Limits? | |
| |
| |
What Can Get in the Way on the Client? | |
| |
| |
What Can Get in the Way on the Server? | |
| |
| |
What About Maintenance and Administration? | |
| |
| |
Managing Maintenance Tasks | |
| |
| |
The Importance of Restoring Data | |
| |
| |
Managing Resources | |
| |
| |
Understanding Basic Data Access Architectures | |
| |
| |
Multi-Tiered Designs | |
| |
| |
Building Stand-Alone Applications | |
| |
| |
ASP Browser-Based Architectures | |
| |
| |
XML Web Service Architectures | |
| |
| |
Summary | |
| |
| |
| |
How Does SQL Server Work? | |
| |
| |
Introduction | |
| |
| |
Servers and Versions | |
| |
| |
SQL Server Side-By-Side | |
| |
| |
Introducing...SQL Server Express Edition | |
| |
| |
What About the Workgroup Edition? | |
| |
| |
Installing SQL Server | |
| |
| |
Running SQL Server Services | |
| |
| |
Inspecting SQL Server Services | |
| |
| |
Starting a SQL Server Service | |
| |
| |
Touring the SQL Server System Databases | |
| |
| |
SQL Server Instances | |
| |
| |
Multiple Instances or Multiple Databases? | |
| |
| |
User and System Databases | |
| |
| |
Creating a User Database | |
| |
| |
Managing User Databases | |
| |
| |
Understanding the SQL Server Security System | |
| |
| |
Addressing Objects in SQL Server 2005 | |
| |
| |
How SQL Server 2005 Addresses the Ownership Chain Issue | |
| |
| |
Understanding Logins, Usernames, and Schemas | |
| |
| |
Creating Security Personas | |
| |
| |
Managing SQL Server Connections | |
| |
| |
Understanding the Data Access Interface Role | |
| |
| |
Understanding the Protocols | |
| |
| |
Opening a Connection | |
| |
| |
Debugging a Connection | |
| |
| |
Choosing a Connection Strategy | |
| |
| |
Using the SQL Query Tools | |
| |
| |
Creating a New Query | |
| |
| |
Creating Select Queries | |
| |
| |
Using Aliases for Table and Column Names | |
| |
| |
Walking Through the Select Operation | |
| |
| |
Understanding the Where Clause | |
| |
| |
Using Parameters to Pass Literal Values | |
| |
| |
Limiting the Number of Rows Returned | |
| |
| |
Using the Where Clause to Perform Joins | |
| |
| |
Dealing with Special-Case Query Problems | |
| |
| |
Creating and Querying Views | |
| |
| |
T-SQL Views | |
| |
| |
Database Snapshots | |
| |
| |
Implementing Business Rules | |
| |
| |
Implementing Business Rules with Extended Properties | |
| |
| |
Implementing Business Rules with T-SQL Rules, Constraints, and Defaults | |
| |
| |
User-Defined (Aliased) Types | |
| |
| |
Implementing Constraints | |
| |
| |
Managing Databases and Queries with Batches and Scripts | |
| |
| |
Scripts Versus Batches | |
| |
| |
The Query Optimizer and the Query Plan | |
| |
| |
Viewing and Tuning the Query Plan | |
| |
| |
Examining Client Statistics and Alternate Plans | |
| |
| |
Executing the Query Plan | |
| |
| |
Understanding the Buffer Cache | |
| |
| |
Managing Cached Query Plans | |
| |
| |
Executing Action Commands | |
| |
| |
Inserting New Rows | |
| |
| |
Updating Rows | |
| |
| |
Introducing Stored Procedures | |
| |
| |
Introducing Triggers | |
| |
| |
Creating a DML Trigger | |
| |
| |
Creating a DDL Trigger | |
| |
| |
Using Transactions to Protect Data Integrity | |
| |
| |
Administrative Functions | |
| |
| |
Attaching a .MDF Database File | |
| |
| |
Setting the Auto Close Option | |
| |
| |
Importing and Exporting Data | |
| |
| |
Backing Up SQL Server Databases | |
| |
| |
Working with the Transaction Log | |
| |
| |
Summary | |
| |
| |
| |
Relational Databases 101 | |
| |
| |
Introduction | |
| |
| |
Getting Started with Solid Database Design | |
| |
| |
Understanding Relational Database Normalization | |
| |
| |
Creating Tables, Rows, and Columns | |
| |
| |
How SQL Server Stores Relational Databases | |
| |
| |
Choosing the Right Data Type | |
| |
| |
Summary | |
| |
| |
| |
Getting Started with Visual Studio | |
| |
| |
Introduction | |
| |
| |
How I Got Here | |
| |
| |
Installing the "Right" Version of Visual Studio | |
| |
| |
Dealing with CTP or Beta Shards | |
| |
| |
Walking Through a Typical Installation | |
| |
| |
Installing the MSDN Documentation | |
| |
| |
Launching Visual Studio | |
| |
| |
Installing the Visual Studio 2005 Image Library | |
| |
| |
Customizing Visual Studio 2005 | |
| |
| |
Saving and Loading Custom Configurations | |
| |
| |
Creating Custom Project Templates | |
| |
| |
Configuring Online or Local Help | |
| |
| |
Configuring Server (or Database) Explorers | |
| |
| |
Creating a New Database with the Server Explorer | |
| |
| |
Creating a New Database in Code | |
| |
| |
Creating and Managing Database Connections | |
| |
| |
Managing Database Objects with the Server Explorer | |
| |
| |
Changing Existing Objects | |
| |
| |
Managing Queries with the Query Designer | |
| |
| |
Getting Familiar with the Query Designer | |
| |
| |
The Query Designer Panes | |
| |
| |
What's a "Derived" Table? | |
| |
| |
Changing the Query Type | |
| |
| |
The Query Designer-a Recap | |
| |
| |
Using the Server Explorer to View Synonyms | |
| |
| |
Using the Server Explorer to Manage Types | |
| |
| |
Using the Server Explorer to Manage Assemblies | |
| |
| |
Using the Server Explorer to Manage Servers | |
| |
| |
Summary | |
| |
| |
| |
Managing Executables with the Server Explorer | |
| |
| |
Introduction | |
| |
| |
Creating and Editing Stored Procedures | |
| |
| |
Comparing SQL Server 2005 and Visual Studio 2005 | |
| |
| |
Enter the SQL Server Management Studio Express | |
| |
| |
Creating New Stored Procedures | |
| |
| |
Creating and Editing Complex Stored Procedures | |
| |
| |
Executing T-SQL with Run Selection | |
| |
| |
Debugging Stored Procedures | |
| |
| |
T-SQL Debugging Against a Local Instance | |
| |
| |
Debugging with Breakpoints Within Stored Procedures | |
| |
| |
Debugging Stored Procedures on Remote Instances | |
| |
| |
Debugging Stored Procedures from Code | |
| |
| |
Using the Server Explorer to Manage Functions | |
| |
| |
Creating T-SQL Functions with the Server Explorer | |
| |
| |
Summary | |
| |
| |
| |
Building Data Sources, DataSets, and TableAdapters | |
| |
| |
Why Create YADAI? | |
| |
| |
Is Strongly Typed Data Important? | |
| |
| |
What Is a Data Source? | |
| |
| |
What Is a TableAdapter? | |
| |
| |
What's Missing in the TableAdapter? | |
| |
| |
Creating Database-Based Data Sources | |
| |
| |
Building a Single-Table TableAdapter | |
| |
| |
Configuring a TableAdapter | |
| |
| |
Adding Parameters to a Query | |
| |
| |
Choosing Query Methods to Generate | |
| |
| |
Binding to the TableAdapter Using Drag and Drop | |
| |
| |
Customizing the Generated UI | |
| |
| |
Using SmartTags to Manage Bound Controls | |
| |
| |
Binding to "Details" | |
| |
| |
Binding to Custom Controls | |
| |
| |
Using a TableAdapter Directly-Without Drag-and-Drop? | |
| |
| |
Managing DataTable Classes in the Data Source Designer | |
| |
| |
The Generated Form Code | |
| |
| |
Moving Data Sources Between Applications | |
| |
| |
Creating Web Service Data Sources | |
| |
| |
What Is a Web Service? | |
| |
| |
Constructing a Sample Web Service | |
| |
| |
Consuming and Testing Web Services | |
| |
| |
Exposing the Web Service Data Source in the UI | |
| |
| |
Summary | |
| |
| |
| |
Managing Data Tools and Data Binding | |
| |
| |
Introduction | |
| |
| |
Touring the Visual Studio Toolbox | |
| |
| |
Exposing Deprecated Data Access Controls | |
| |
| |
Finding the Right Control | |
| |
| |
Understanding the Tool Tray | |
| |
| |
Using the DataSet Toolbox Element | |
| |
| |
Introduction to Data Binding | |
| |
| |
Complex Versus Simple Binding | |
| |
| |
Using the BindingSource Class | |
| |
| |
Using the EndEdit and CancelEdit Methods | |
| |
| |
Data Binding Using the BindingSource Class | |
| |
| |
Using the BindingNavigator Control | |
| |
| |
Using the DataGridView Control | |
| |
| |
Using the ProgressBar Control | |
| |
| |
Summary | |
| |
| |
| |
Getting Started with ADO.NET | |
| |
| |
Introduction | |
| |
| |
Approaching Data Access Challenges | |
| |
| |
ADO.NET from 50,000 Feet | |
| |
| |
Working with Data Sources | |
| |
| |
Using the Visual Studio Object Browser to Explore ADO.NET | |
| |
| |
Exploring ADO.NET with a Class Diagram | |
| |
| |
Deciphering the Namespaces | |
| |
| |
Getting a Handle on the Class Names | |
| |
| |
Using Shorthand to Address Classes | |
| |
| |
Instantiating ADO.NET Objects | |
| |
| |
Understanding Code Scope | |
| |
| |
Understanding Object Instantiation | |
| |
| |
The Using Block in Visual Basic.NET | |
| |
| |
Exploring the System.Data.SqlClient Namespace | |
| |
| |
Choosing the Right Provider-When it's Not SQL Server | |
| |
| |
Exploring the SqlClient Namespace | |
| |
| |
The SqlConnection Class | |
| |
| |
Exploring the System.Data Namespace | |
| |
| |
Deciphering System.Data Naming Conventions | |
| |
| |
Understanding the System.Data.DataSet and DataTable Objects | |
| |
| |
To Join or Not to Join-That's The Question | |
| |
| |
Summary | |
| |
| |
| |
Getting Connected | |
| |
| |
Introduction | |
| |
| |
Connectivity Strategies-That Include Security | |
| |
| |
Choosing a Connection Strategy | |
| |
| |
Configuring the Server and Firewall | |
| |
| |
Connection Strategies | |
| |
| |
Connecting Just in Time | |
| |
| |
Persistent Connections | |
| |
| |
Understanding MARS and Its Implications | |
| |
| |
Choosing the Right Scope for the Connection Object | |
| |
| |
Establishing a Connection | |
| |
| |
Finding Servers and Instances in your Domain | |
| |
| |
Writing Code to Create Connections | |
| |
| |
What Is a ConnectionString? | |
| |
| |
Building a ConnectionString with the SqlConnectionStringBuilder Class | |
| |
| |
Protecting the ConnectionString | |
| |
| |
Persisting the ConnectionString | |
| |
| |
Building a Connection Object | |
| |
| |
Setting the Server Name Keyword | |
| |
| |
Establishing an Alias to Your Instance | |
| |
| |
Revisiting Other Keywords | |
| |
| |
Configuring the Connection Pool Keyword Values | |
| |
| |
Understanding and Managing the Connection Pool | |
| |
| |
Monitoring the Connection Pool | |
| |
| |
Keeping the Connection Pool from Overflowing | |
| |
| |
Building a ConnectionString for Other Providers | |
| |
| |
Getting Visual Studio to Build Your ConnectionString | |
| |
| |
Opening and Closing Connections (Semi-) Automatically | |
| |
| |
Understanding the Connection Properties | |
| |
| |
Using the Connection Methods | |
| |
| |
Handling Connection Events | |
| |
| |
Managing Connection Exceptions | |
| |
| |
Sourcing Exceptions | |
| |
| |
Understanding the SqlException Class | |
| |
| |
Dealing with Exceptions | |
| |
| |
Summary | |
| |
| |
| |
Managing SqlCommand Objects | |
| |
| |
Introduction | |
| |
| |
Creating SqlCommand Objects | |
| |
| |
Understanding the SqlCommand Properties | |
| |
| |
Understanding the SqlCommand Methods | |
| |
| |
Understanding the SqlCommand Support Functions | |
| |
| |
Integrating Ad Hoc Queries into Your Application | |
| |
| |
Preventing SQL Injection Attacks | |
| |
| |
Coding Parameter Queries | |
| |
| |
Inserting Ad Hoc Queries | |
| |
| |
Understanding the SqlParameterCollection Class | |
| |
| |
Understanding the SqlParameter Class | |
| |
| |
Validating the Value | |
| |
| |
Writing Code to Validate the Value | |
| |
| |
Handling Complex Parameter Design Issues | |
| |
| |
Understanding SqlCommand Events | |
| |
| |
Executing Stored Procedures | |
| |
| |
Handling Output, Input/Output, and Return Value Parameters | |
| |
| |
Handling Rowsets with Output Parameters | |
| |
| |
Summary | |
| |
| |
| |
Executing SqlCommand Objects | |
| |
| |
Introduction | |
| |
| |
Executing SqlCommand Objects Synchronously | |
| |
| |
Choosing the "Right" Execute Function | |
| |
| |
CommandBehavior.SequentialAccess | |
| |
| |
CommandBehavior.SingleResult and CommandBehavior.SingleRow | |
| |
| |
Understanding the SqlDataReader | |
| |
| |
Fetching Data from the Stream | |
| |
| |
Fetching Metadata from the SqlDataReader Stream | |
| |
| |
SqlDataReader Best Practice Designs | |
| |
| |
Populating Data Structures | |
| |
| |
Efficient Population by Design | |
| |
| |
How Does Population Work? | |
| |
| |
Populating InfoMessage Messages | |
| |
| |
Populating the Output and Return Value Parameters | |
| |
| |
Populating with the Data Table Load Method | |
| |
| |
Populating Additional Resultsets | |
| |
| |
Fetching Rows Asynchronously | |
| |
| |
Executing Commands Asynchronously | |
| |
| |
Coding the Asynchronous SqlCommand Functions | |
| |
| |
Waiting for Godot or Your Asynchronous Operation to Complete | |
| |
| |
Summary | |
| |
| |
| |
Managing Updates | |
| |
| |
Introduction | |
| |
| |
Using Wizards to Generate Update Action Commands | |
| |
| |
What the CommandBuilder Is Supposed to Do | |
| |
| |
Handling Concurrency-Update and Insert Collisions | |
| |
| |
How the ADO.NET CommandBuilder Works | |
| |
| |
Coding the CommandBuilder | |
| |
| |
Update Alternatives: Using the TableAdapter Configuration Wizard | |
| |
| |
Updating with the TableAdapter | |
| |
| |
Managing Generated Parameters | |
| |
| |
Complex Updates with Server-Side Logic | |
| |
| |
Using Events to Execute Your Own Update Logic | |
| |
| |
Stepping Through the UpdateEvents Example | |
| |
| |
Managing Batch Mode Updates | |
| |
| |
Managing Concurrency by Design or Collision | |
| |
| |
Managing an @@Identity Crisis | |
| |
| |
Summary | |
| |
| |
Managing Server Report Parameters | |
| |
| |
Resetting Parameter Values | |
| |
| |
Interesting ServerReport Parameters | |
| |
| |
Interesting ServerReport Methods and Functions | |
| |
| |
Managing Parameters | |
| |
| |
Capturing Parameters | |
| |
| |
Report Parameters | |
| |
| |
Advanced Reporting Techniques | |
| |
| |
Managing Click-Through Reports | |
| |
| |
Using the SubReport Report Item | |
| |
| |
What's in the RDLC? | |
| |
| |
Converting RDL to RDLC | |
| |
| |
Implementing the Matrix Report | |
| |
| |
The Data Considerations for a Report | |
| |
| |
The Initial Select Query | |
| |
| |
Grouping the Data | |
| |
| |
Building the Matrix Report | |
| |
| |
Summary | |
| |
| |
| |
Summary and Wintry: Where We Are Now | |
| |
| |
| |
Installing the Examples and Test Databases | |
| |
| |
Installing the Examples | |
| |
| |
Installing the Example Databases | |
| |
| |
Attaching the Example Databases | |
| |
| |
Establishing an Alias to Your Instance | |
| |
| |
Chapter-Specific Configuration Issues | |
| |
| |
| |
Getting Started with ADO.NET | |
| |
| |
| |
Managing CLR Executables | |
| |
| |
Summary | |
| |
| |
| |
Reinstalling the DACW and Other Missing Functionality in Visual Studio | |
| |
| |
| |
Monitoring SQL Server | |
| |
| |
Monitoring SQL Server with the SQL Profiler | |
| |
| |
Adding Filter Criteria | |
| |
| |
Configuring SQL Profiler | |
| |
| |
Starting the Trace | |
| |
| |
Evaluating the Trace | |
| |
| |
A Few SQL Profiler Tips | |
| |
| |
Monitoring SQL Server and ADO.NET with Performance Counters | |
| |
| |
The ADO.NET SqlClient Performance Counters | |
| |
| |
Summary | |
| |
| |
| |
Creating and Managing Server-Side Cursors | |
| |
| |
Why Are Server-side Cursors Important? | |
| |
| |
How Does ADO.NET Implement Cursors? | |
| |
| |
How Are Server-Side Cursors Managed? | |
| |
| |
What Do Server-Side Cursors Cost? | |
| |
| |
How Can ADO.NET Create a Server-Side Cursor? | |
| |
| |
Opening, Closing, and Re-opening the Connection | |
| |
| |
Creating the Cursor | |
| |
| |
Fetching Data from the Cursor | |
| |
| |
Fetching the Entire Cursor | |
| |
| |
Updating with a Server-Side Cursor | |
| |
| |
Summary | |
| |
| |
Index | |