PART 1 ■ ■ ■ Working with SQL Server Express
■CHAPTER 1 Getting Started with SQL Server Express . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
■CHAPTER 2 Graphical and Command-Line Query Tools . . . . . . . . . . . . . . . . . . . . . . . . . . . 33
■CHAPTER 3 Exploring, Creating, and Recovering Databases . . . . . . . . . . . . . . . . . . . . . . 73
■CHAPTER 4 Data Types, Tables, and Constraints . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 107
■CHAPTER 5 Creating Queries from a Single Database Object . . . . . . . . . . . . . . . . . . . . 155
■CHAPTER 6 Querying Multiple Database Objects and Manipulating Result Sets . . . 191
■CHAPTER 7 Leveraging Database Objects That Encapsulate T-SQL . . . . . . . . . . . . . . . 247
■CHAPTER 8 Managing SQL Server Express Security. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 307
PART 2 ■ ■ ■ Working with Visual Basic Express
and Visual Web Developer Express
■CHAPTER 9 Introduction to Visual Basic Express and Windows Forms . . . . . . . . . . . . 371
■CHAPTER 10 Introduction to Visual Web Developer Express,
Web Pages, and Web Forms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 409
■CHAPTER 11 Programming ADO.NET . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 445
■CHAPTER 12 Programming DataAdapter and DataSet Objects . . . . . . . . . . . . . . . . . . . . 485
■CHAPTER 13 Using Visual Database and Form Design Tools . . .
625 trang |
Chia sẻ: longpd | Lượt xem: 2791 | Lượt tải: 3
Bạn đang xem trước 20 trang tài liệu Beginning SQL Server 2005 Express, để xem tài liệu hoàn chỉnh bạn click vào nút DOWNLOAD ở trên
Rick Dobson
Beginning SQL Server
2005 Express Database
Applications
with Visual Basic Express and
Visual Web Developer Express
From Novice to Professional
5238CH00_FM 11/18/05 4:29 PM Page i
Beginning SQL Server 2005 Express Database Applications
with Visual Basic Express and Visual Web Developer Express From Novice to Professional
Copyright © 2006 by Rick Dobson
All rights reserved. No part of this work may be reproduced or transmitted in any form or by any means,
electronic or mechanical, including photocopying, recording, or by any information storage or retrieval
system, without the prior written permission of the copyright owner and the publisher.
ISBN (pbk): 1-59059-523-8
Printed and bound in the United States of America 9 8 7 6 5 4 3 2 1
Trademarked names may appear in this book. Rather than use a trademark symbol with every occurrence
of a trademarked name, we use the names only in an editorial fashion and to the benefit of the trademark
owner, with no intention of infringement of the trademark.
Lead Editors: Tony Davis and Matthew Moodie
Technical Reviewer: Cristian Lefter
Editorial Board: Steve Anglin, Dan Appleman, Ewan Buckingham, Gary Cornell, Tony Davis, Jason Gilmore,
Jonathan Hassell, Chris Mills, Dominic Shakeshaft, Jim Sumser
Project Manager: Beth Christmas
Copy Edit Manager: Nicole LeClerc
Copy Editors: Damon Larson and Freelance Editorial Services
Assistant Production Director: Kari Brooks-Copony
Production Editor: Kelly Winquist
Compositors: Dina Quan and Diana Van Winkle, Van Winkle Design Group
Proofreader: April Eddy
Indexer: Valerie Perry
Cover Designer: Kurt Krames
Manufacturing Director: Tom Debolski
Distributed to the book trade worldwide by Springer-Verlag New York, Inc., 233 Spring Street, 6th Floor,
New York, NY 10013. Phone 1-800-SPRINGER, fax 201-348-4505, e-mail orders-ny@springer-sbm.com, or
visit
For information on translations, please contact Apress directly at 2560 Ninth Street, Suite 219, Berkeley,
CA 94710. Phone 510-549-5930, fax 510-549-5939, e-mail info@apress.com, or visit
The information in this book is distributed on an “as is” basis, without warranty. Although every precaution
has been taken in the preparation of this work, neither the author(s) nor Apress shall have any liability to
any person or entity with respect to any loss or damage caused or alleged to be caused directly or indirectly
by the information contained in this work.
The source code for this book is available to readers at in the Source Code section.
You will need to answer questions pertaining to this book in order to successfully download the code.
5238CH00_FM 11/18/05 4:29 PM Page ii
Contents at a Glance
About the Author. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xiii
About the Technical Reviewer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xv
Acknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xvii
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xix
PART 1 ■ ■ ■ Working with SQL Server Express
■CHAPTER 1 Getting Started with SQL Server Express . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
■CHAPTER 2 Graphical and Command-Line Query Tools . . . . . . . . . . . . . . . . . . . . . . . . . . . 33
■CHAPTER 3 Exploring, Creating, and Recovering Databases . . . . . . . . . . . . . . . . . . . . . . 73
■CHAPTER 4 Data Types, Tables, and Constraints . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 107
■CHAPTER 5 Creating Queries from a Single Database Object . . . . . . . . . . . . . . . . . . . . 155
■CHAPTER 6 Querying Multiple Database Objects and Manipulating Result Sets . . . 191
■CHAPTER 7 Leveraging Database Objects That Encapsulate T-SQL . . . . . . . . . . . . . . . 247
■CHAPTER 8 Managing SQL Server Express Security. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 307
PART 2 ■ ■ ■ Working with Visual Basic Express
and Visual Web Developer Express
■CHAPTER 9 Introduction to Visual Basic Express and Windows Forms . . . . . . . . . . . . 371
■CHAPTER 10 Introduction to Visual Web Developer Express,
Web Pages, and Web Forms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 409
■CHAPTER 11 Programming ADO.NET . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 445
■CHAPTER 12 Programming DataAdapter and DataSet Objects . . . . . . . . . . . . . . . . . . . . 485
■CHAPTER 13 Using Visual Database and Form Design Tools . . . . . . . . . . . . . . . . . . . . . . 515
■INDEX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 575
iii
5238CH00_FM 11/18/05 4:29 PM Page iii
5238CH00_FM 11/18/05 4:29 PM Page iv
About the Author. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xiii
About the Technical Reviewer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xv
Acknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xvii
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xix
PART 1 ■ ■ ■ Working with SQL Server Express
■CHAPTER 1 Getting Started with SQL Server Express. . . . . . . . . . . . . . . . . . . . . . 3
What Is SQL Server Express? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
Who Is SQL Server Express For? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
Hobbyists and Other Nonprofessional Developers . . . . . . . . . . . . . . . . . . . . . 6
Business Analysts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
Database Administrators and Operations Specialists . . . . . . . . . . . . . . . . . . 7
Professional Developers in Need of a Free, Modern Database . . . . . . . . . . . 8
SQL Server Express vs. SQL Server 2005 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
Similarities . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
Differences . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
SQL Server Express vs. MSDE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
Installing SQL Server Express. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
Performing a System Check for SQL Server Express. . . . . . . . . . . . . . . . . . 12
Registration, Feature, and Instance Name Screens . . . . . . . . . . . . . . . . . . . 13
Service Account and Authentication Mode Screens. . . . . . . . . . . . . . . . . . . 14
Collation, Error Reporting, and Ready to Install Screens . . . . . . . . . . . . . . . 15
Using SQL Server Express Query Tools . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
Query Tools for SQL Server Express . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
Configuring SQL Server Express to Start Automatically. . . . . . . . . . . . . . . . 17
Configuring SQL Server Express for Network Access . . . . . . . . . . . . . . . . . 19
Configuring the Windows XP Firewall for SQL Server Express . . . . . . . . . . 21
Connecting from the SSMS-Based Query Tool . . . . . . . . . . . . . . . . . . . . . . . 22
Connecting from Remote Down-Level Clients . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
Installing Sample Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
Connecting to SQL Server Express from Visual Studio 2003 . . . . . . . . . . . 25
Connecting to SQL Server Express from Access Projects . . . . . . . . . . . . . . 27
Connecting to SQL Server Express from Access Linked Tables . . . . . . . . . 28
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31
v
Contents
5238CH00_FM 11/18/05 4:29 PM Page v
■CONTENTSvi
■CHAPTER 2 Graphical and Command-Line Query Tools . . . . . . . . . . . . . . . . . . 33
Using the SSMS-Based Query Tool . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33
Connecting to SQL Server Instances . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34
Using Database, Table, and View Designers . . . . . . . . . . . . . . . . . . . . . . . . . 41
Designing, Running, Saving, and Rerunning SQL Scripts . . . . . . . . . . . . . . 51
Using the sqlcmd Utility. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60
Connecting to SQL Server Instances . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60
Running Statements from a File. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 62
Saving Output to a File . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64
Dynamically Running and Batching sqlcmd Statements . . . . . . . . . . . . . . . 65
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 71
■CHAPTER 3 Exploring, Creating, and Recovering Databases . . . . . . . . . . . . . 73
Overview of Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 73
Introducing Database Concepts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74
Types of Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75
Getting Meta Data About Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75
Using sp_helpdb for Database Help. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 76
Using System Catalog Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 78
Searching for and Copying Databases with Windows Explorer. . . . . . . . . . 83
Using CREATE DATABASE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 84
Just Naming the Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 84
Designating Data Files in the ON Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . 85
Using the LOG ON Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 87
Attaching and Detaching Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 88
Attaching Databases with CREATE DATABASE . . . . . . . . . . . . . . . . . . . . . . . 89
Copying Files, the Auto-Close Feature, and sp_detach_db . . . . . . . . . . . . . 95
Backing Up and Restoring Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 98
Overview of Database Recovery Models . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99
Choosing and Modifying a Database’s Recovery Model . . . . . . . . . . . . . . 101
Performing a Full Data Backup and Restore . . . . . . . . . . . . . . . . . . . . . . . . 102
Performing Differential Backups and Restores . . . . . . . . . . . . . . . . . . . . . . 103
Cleaning Up the Backup and Restore Scripts . . . . . . . . . . . . . . . . . . . . . . . 106
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 106
■CHAPTER 4 Data Types, Tables, and Constraints . . . . . . . . . . . . . . . . . . . . . . . . . 107
Learning About Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 107
Numbers and Dates. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 108
Character and Binary Byte Streams. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 114
Miscellaneous . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 117
Creating Tables and Using Data Types. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 121
Creating a Table with Columns. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 121
5238CH00_FM 11/18/05 4:29 PM Page vi
■CONTENTS vii
Managing Data Integrity with Basic Constraints and Column Properties. . . . . . 128
Inserting Data for a Subset of Table Columns. . . . . . . . . . . . . . . . . . . . . . . 128
Not Allowing Null Values in a Column . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 131
Designating Default Column Values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 132
Designating a Column as a Primary Key . . . . . . . . . . . . . . . . . . . . . . . . . . . 135
Managing Data Integrity with Sophisticated Constraints . . . . . . . . . . . . . . . . . . . 139
Using CHECK Constraints . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 139
Using Multicolumn Constraints. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 143
Using Foreign Key Constraints . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 145
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 154
■CHAPTER 5 Creating Queries from a Single Database Object . . . . . . . . . . . 155
Specifying Items to Select from a Data Source . . . . . . . . . . . . . . . . . . . . . . . . . . 155
Specifying SELECT List Items. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 156
Using the Current Database Context or a Different Database . . . . . . . . . . 165
Specifying Queries from Another Server Instance . . . . . . . . . . . . . . . . . . . 168
Filtering, Grouping, and Aggregating . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 175
Filtering for Exact and Approximate Character Matches . . . . . . . . . . . . . . 175
Filtering for Values in a Range . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 177
Grouping and Aggregating . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 183
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 189
■CHAPTER 6 Querying Multiple Database Objects
and Manipulating Result Sets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 191
Joining Data Sources. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 191
Inner Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 192
Outer Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 195
Cross Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 198
Self-Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 200
Joins for More Than Two Data Sources . . . . . . . . . . . . . . . . . . . . . . . . . . . . 205
Using Subqueries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 208
Including a Subquery in a SELECT List . . . . . . . . . . . . . . . . . . . . . . . . . . . . 208
Including a Subquery in a WHERE Clause . . . . . . . . . . . . . . . . . . . . . . . . . . 211
Including a Correlated Subquery in a WHERE Clause. . . . . . . . . . . . . . . . . 214
Explicitly Ordering and Ranking Rows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 215
Controlling Row Order with the ORDER BY Clause. . . . . . . . . . . . . . . . . . . 215
Ranking Result Set Rows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 219
Manipulating Result Sets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 224
Saving a Result Set with the INTO Clause. . . . . . . . . . . . . . . . . . . . . . . . . . 225
Appending Result Sets to One Another . . . . . . . . . . . . . . . . . . . . . . . . . 229
Reusing Queries with Common Table Expressions . . . . . . . . . . . . . . . . 232
Converting Between Relational and Cross-Tabulated Tables . . . . . . . . . 239
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 245
5238CH00_FM 11/18/05 4:29 PM Page vii
■CHAPTER 7 Leveraging Database Objects That Encapsulate T-SQL . . . . 247
Creating and Using Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 247
Performing Data Access with a View . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 248
Performing Data Modification with a View . . . . . . . . . . . . . . . . . . . . . . . . . 252
Processing Meta Data with System Views . . . . . . . . . . . . . . . . . . . . . . . . . 256
Creating and Using User-Defined Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 259
Creating and Using FN User-Defined Functions . . . . . . . . . . . . . . . . . . . . . 260
Creating and Using IF User-Defined Functions . . . . . . . . . . . . . . . . . . . . . . 263
Creating and Using Stored Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 267
Returning Result Sets Without Parameters . . . . . . . . . . . . . . . . . . . . . . . . . 268
Returning Result Sets and Input Parameters . . . . . . . . . . . . . . . . . . . . . . . 271
Returning Scalar Values with Output Parameters. . . . . . . . . . . . . . . . . . . . 278
Processing Return Status Values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 282
Performing Data Manipulation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 284
Creating and Using Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 292
Learning to Reference Inserted and Deleted Tables. . . . . . . . . . . . . . . . . . 293
Rolling Back in an AFTER Trigger . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 295
Archiving Changes to a Table with Triggers . . . . . . . . . . . . . . . . . . . . . . . . 297
Using an INSTEAD OF Trigger with a View. . . . . . . . . . . . . . . . . . . . . . . . . . 302
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 305
■CHAPTER 8 Managing SQL Server Express Security . . . . . . . . . . . . . . . . . . . . . 307
Overview of Security Concepts. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 307
Exploring and Creating Principals. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 308
Exploring Principals . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 309
Creating Principals. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 313
Assigning Permissions to Principals. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 324
Overview of Securables and Permissions . . . . . . . . . . . . . . . . . . . . . . . . . . 325
Assigning Permissions via the Fixed Server Roles . . . . . . . . . . . . . . . . . . . 327
Assigning Permissions via the Fixed Database Roles . . . . . . . . . . . . .