Tài liệu Beginning C Sharp 2005 Databases

Introduction xix
Chapter 1: Database Fundamentals 1
What Is a Database? 2
Relational Database Management Systems 3
Object Oriented Database Management Systems 8
Additional Features of RDBMSes 8
Joins 9
Functions 9
Views 9
Stored Procedures 10
Triggers 10
E-mail 10
Indexes 11
Security 11
Concurrency Control 12
Transactions 13
Remote Access 13
Backups 14
Mirroring and Partitioning 14
Management Tools 14
What RDBMSes Are Available? 15
MySQL and PostgreSQL 15
DB2, Oracle, and SQL Server 16
SQL Server 2005 Express Edition 16
How Do You Use a Database? 17
A SQL Primer 18
XML 30
Summary 33
Exercises 34
xiv
Contents
Chapter 2: Databases and C# 35
Database Access in C# 36
ADO.NET 36
Data Binding 46
Visual Tools 47
Express Products 47
SQL Server Management Studio Express 50
Examples 51
Case Study Introduction 65
Summary 67
Exercises 68
Chapter 3: Viewing Data 69
Creating Data Sources 70
Automatic Data Source Configuration 70
Manual Data Source Configuration 75
Advanced Data Source Configuration 87
Binding to Data Sources 90
The BindingSource Control 91
Binding to List Controls 94
Binding to DataGridView Controls 95
The BindingNavigator Control 102
Adding a Navigable DataGridView in a Single Step 103
Binding to Detail Views 104
Filtering Data 107
Summary 110
Exercises 111
Chapter 4: Modifying Data 113
Data Modification Prerequisites 113
Database Security 114
Database Views 115
Application Data Sources 116
How to Avoid Data Being Overwritten 117
Modifying Data from Data-Bound Controls 119
Adding Rows with GUID Primary Keys 125
How BindingNavigator Saves Data 125
Canceling Updates 125
xv
Contents
Updating Data from Detail Views 126
Updating Multiple Tables 130
Updating Long Text Data for DataGridView Displays 141
Saving Data When the Application Closes 145
Summary 146
Exercises 147
Chapter 5: Databases and the Internet 149
Web versus Windows Applications 150
Data Binding in ASP.NET 151
ASP.NET Data Connections 152
Local Database File Connections 153
Remote Connections 153
ASP.NET Data Source Controls 157
Data Source Configuration 158
Additional Data Source Control Functionality 163
ASP.NET Data Display Control Summary 163
GridView 164
DataList 164
Repeater 165
FormView 166
DetailsView 166
An Introduction to Templates 167
Viewing and Editing Data in List Views 170
Viewing Data 170
Additional Configuration 174
Editing Data 176
Viewing and Editing Data in Detail Views 184
Master/Detail Views 187
Summary 190
Exercises 191
Chapter 6: Accessing Databases Programmatically 193
Database Connections 194
Creating Connections 194
Connection State 196
Connection Pooling 197
Database Commands 197
Creating Commands 197
xvi
Contents
Command Types 198
Command Execution Methods 198
Parameterized Commands 200
Protecting Against SQL Injection 201
Data Reader Objects 205
Creating Data Reader Objects 205
Reading Data 206
Connections for Data Reader Objects 210
Data Adapter Objects 212
Configuring Data Adapters 213
Extracting Database Data 218
Modifying Database Data 219
Customizing Data Adapter Behavior 220
DataSet Objects 226
Populating DataSet objects 226
Defining and Using Table Relationships 228
Typed DataSet Classes 232
Summary 239
Exercises 240
Chapter 7: Views and Stored Procedures 241
Database Views 242
Creating Views 243
Updating Data Through Views 254
Stored Procedures 256
Creating Stored Procedures 258
Using Stored Procedures 264
Updating Data Through Stored Procedures 271
Conditional Logic and Looping 272
Cursors 276
Summary 289
Exercises 290
Chapter 8: Custom Data Objects 291
Custom Data Objects 291
n-Tier Application Design Primer 293
Passing Data Reader Objects 294
Using Existing .NET Classes 298
Basic Data-Aware Class Design 303
DataAwareObjects Class Library 304
xvii
Contents
Binding to Object Data 320
Windows Applications 320
Web Applications 323
Summary 332
Exercises 333
Chapter 9: Transactions and Concurrency 335
Concurrency 336
Last in Wins 336
Optimistic Concurrency Management in ADO.NET 339
Concurrency Violations in Client Applications 346
Resolving Concurrency Violations 353
Transactions 358
SQL Transactions 359
.NET Transactions 363
Summary 373
Exercises 374
Chapter 10: Working with Disconnected Data 375
Exposing Web Service Data 376
Web Service Primer 376
Exposing Database Data 383
Consuming Web Service Data 387
Caching Web Service Data 400
Web Method Output Caching 401
Caching Data in Application State 402
Caching Data in the Application Cache 404
Summary 414
Exercises 415
Chapter 11: SQL Server CLR Integration 417
Overview of CLR Integration 418
Enabling CLR Integration 419
Adding Assemblies to SQL Server 420
Requirements of CLR Integration Code 422
Registering CLR Code 422
Common Features of CLR Integrated Code 422
Code Structure 423
Data Access 423
xviii
Contents
Call Context 424
SqlPipe 424
Managed Functions and Stored Procedures 426
Scalar Functions 426
Table-Valued Functions 432
Aggregate Functions 437
Stored Procedures 445
Summary 455
Exercises 456
Appendix A: Installation 457
Appendix B: Database Installation 467
Appendix C: Answers to Exercises 473
Index 483