Skip to content

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

Spend $50 to get a free DVD!

ISBN-10: 0321243625

ISBN-13: 9780321243621

Edition: 7th 2007

Authors: William R. Vaughn, Peter Blackburn

List price: $64.99
Blue ribbon 30 day, 100% satisfaction guarantee!
what's this?
Rush Rewards U
Members Receive:
Carrot Coin icon
XP icon
You have reached 400 XP and carrot coins. That is the daily max!

Description:

Updated for the latest version of Visual Studio and SQL Server, this book gives tips, techniques, advice, and examples that will be invaluable to database developers and administrators with a basic understanding of the product. The accompanying CD-ROM will include code examples from the book.
Customers also bought

Book details

List price: $64.99
Edition: 7th
Copyright year: 2007
Publisher: Addison Wesley Professional
Publication date: 11/2/2006
Binding: Mixed Media
Pages: 1128
Size: 7.00" wide x 9.00" long x 2.50" tall
Weight: 3.432
Language: English

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