Beginning C# 2008 Databases (eBook)

Syed Fahad Gilani has more than 15 years of experience in computing. He sold his first program at the age of 10.

Contents at a Glance 5
Contents 6
About the Authors 20
About the Technical Reviewer 21
Acknowledgments 22
Introduction 24
Who This Book Is For 24
What This Book Covers 24
How This Book Is Organized 25
How to Download the Sample Code 25
Getting Your Tools 26
Obtaining Visual Studio 2008 27
Installing SQL Server Management Studio Express 28
Installing the Northwind Sample Database 29
Installing the AdventureWorks Sample Database 34
Summary 38
Getting to Know Your Tools 39
Microsoft . NET Framework Versions and the Green Bit and Red Bit Assembly Model 39
Using Microsoft Visual Studio 2008 40
Using SQL Server Management Studio Express 46
Summary 50
Getting to Know Relational Databases 51
What Is a Database? 51
Choosing Between a Spreadsheet and a Database 52
Why Use a Database? 52
Benefits of Using a Relational Database Management System 53
Comparing Desktop and Server RDBMS Systems 53
The Database Life Cycle 55
Mapping Cardinalities 56
Understanding Keys 58
Understanding Data Integrity 60
Normalization Concepts 60
Drawbacks of Normalization 62
Summary 62
Writing Database Queries 63
Comparing QBE and SQL 64
Beginning with Queries 65
Common Table Expressions 66
GROUP BY Clause 68
PIVOT Operator 70
ROW_ NUMBER() Function 72
PARTITION BY Clause 73
Pattern Matching 74
Aggregate Functions 80
DATETIME Functions 83
Joins 85
Summary 96
Manipulating Database Data 97
Retrieving Data 97
Using SELECT INTO Statements 107
Inserting Data 112
Updating Data 115
Deleting Data 117
Summary 118
Using Stored Procedures 119
Creating Stored Procedures 119
Modifying Stored Procedures 127
Displaying Definitions of Stored Procedures 130
Renaming Stored Procedures 131
Working with Stored Procedures in C# 132
Deleting Stored Procedures 139
Summary 141
Using XML 142
Defining XML 142
Why XML? 143
Benefits of Storing Data As XML 143
Understanding XML Documents 144
Understanding the XML Declaration 146
Converting Relational Data to XML 146
Using the xml Data Type 153
Summary 156
Understanding Transactions 157
What Is a Transaction? 157
When to Use Transactions 158
Understanding ACID Properties 159
Transaction Design 160
Transaction State 160
Specifying Transaction Boundaries 161
T- SQL Statements Allowed in a Transaction 161
Local Transactions in SQL Server 2005 161
Distributed Transactions in SQL Server 2005 163
Guidelines to Code Efficient Transactions 164
How to Code Transactions 165
Summary 178
Getting to Know ADO.NET 179
Understanding ADO. NET 179
The Motivation Behind ADO. NET 180
Moving from ADO to ADO. NET 181
Understanding ADO. NET Architecture 184
Working with the SQL Server Data Provider 186
Working with the OLE DB Data Provider 193
Working with the ODBC Data Provider 199
Data Providers Are APIs 209
Summary 210
Making Connections 211
Introducing the Data Provider Connection Classes 211
Connecting to SQL Server Express with SqlConnection 212
Improving Your Use of Connection Objects 221
Connecting to SQL Server Express with OleDbConnection 227
Summary 230
Executing Commands 231
Creating a Command 231
Executing Commands 237
Executing Commands with Multiple Results 241
Executing Statements 244
Command Parameters 249
Summary 255
Using Data Readers 256
Understanding Data Readers in General 256
Getting Data About Data 272
Getting Data About Tables 277
Using Multiple Result Sets with a Data Reader 280
Summary 285
Using Datasets and Data Adapters 286
Understanding the Object Model 287
Working with Datasets and Data Adapters 291
Propagating Changes to a Data Source 310
Concurrency 331
Using Datasets and XML 332
Using Data Tables Without Datasets 336
Understanding Typed and Untyped Datasets 339
Summary 340
Building Windows Forms Applications 342
Understanding Windows Forms 342
User Interface Design Principles 343
Best Practices for User Interface Design 343
Working with Windows Forms 346
Understanding the Design and Code Views 348
Sorting Properties in the Properties Window 349
Setting Properties of Solutions, Projects, and Windows Forms 351
Working with Controls 352
Setting Dock and Anchor Properties 356
Adding a New Form to the Project 361
Implementing an MDI Form 363
Summary 368
Building ASP.NET Applications 369
Understanding Web Functionality 369
Introduction to ASP. NET and Web Pages 371
Understanding the Visual Studio 2008 Web Site Types 371
Layout of an ASP. NET Web Site 374
Using Master Pages 382
Summary 388
Handling Exceptions 389
Handling ADO. NET Exceptions 389
Handling Database Exceptions 399
Summary 410
Working with Events 411
Understanding Events 411
Properties of Events 412
Design of Events 412
Common Events Raised by Controls 413
Event Generator and Consumer 414
Summary 422
Working with Text and Binary Data 423
Understanding SQL Server Text and Binary Data Types 423
Storing Images in a Database 424
Retrieving Images from a Database 433
Working with Text Data 439
Summary 450
Using LINQ 451
Introduction to LINQ 452
Architecture of LINQ 453
LINQ Project Structure 455
Using LINQ to Objects 457
Using LINQ to SQL 459
Using LINQ to XML 465
Summary 467
Using ADO.NET 3.5 468
Understanding ADO. NET 3.5 Entity Framework 468
Understanding the Entity Data Model 469
Working with the Entity Data Model 470
Summary 484
Index 485