Beginning VB 2008 Databases: From Novice to Professional (Beginning: from Novice to Professional)

Beginning VB 2008 Databases: From Novice to Professional......Page 1
Contents at a Glance......Page 7
Contents......Page 9
About the Authors......Page 23
About the Technical Reviewer......Page 25
Acknowledgments......Page 27
What This Book Covers......Page 29
Contacting the Author......Page 30
Getting Your Tools......Page 31
Obtaining Visual Studio 2008......Page 32
Installing SQL Server Management Studio Express......Page 33
Installing the Northwind Creation Script......Page 34
Creating the Northwind Sample Database......Page 36
Installing the AdventureWorks Creation Script......Page 39
Creating the AdventureWorks Sample Database......Page 40
Summary......Page 43
Microsoft .NET Framework Versions and the Green Bit and Red Bit Assembly Model......Page 45
Using Microsoft Visual Studio 2008......Page 46
Try It Out: Creating a Simple Console Application Project Using Visual Studio 2008......Page 48
Using SQL Server Management Studio Express......Page 50
Summary......Page 54
What Is a Database?......Page 55
Benefits of Using a Relational Database Management System......Page 56
Server Databases......Page 57
The Database Life Cycle......Page 58
Mapping Cardinalities......Page 59
Understanding Keys......Page 61
Entity Integrity......Page 62
Normalization Concepts......Page 63
Summary......Page 64
Comparing QBE and SQL......Page 65
Try It Out: Running a Simple Query......Page 67
Common Table Expressions......Page 68
Try It Out: Creating a CTE......Page 69
Try It Out: Using the GROUP BY Clause......Page 70
How It Works......Page 71
Try It Out: Using the PIVOT Operator......Page 72
How It Works......Page 73
How It Works......Page 74
Try It Out: Using the PARTITION BY Clause......Page 75
Pattern Matching......Page 76
How It Works......Page 77
How It Works......Page 78
How It Works......Page 79
How It Works......Page 80
Try It Out: Using the MIN, MAX, SUM, and AVG Functions......Page 81
Try It Out: Using the COUNT Function......Page 82
Try It Out: Using T-SQL Date and Time Functions......Page 83
How It Works......Page 84
Joins......Page 85
Try It Out: Writing an Inner Join......Page 86
How It Works......Page 87
Try It Out: Writing an Inner Join Using Correlation Names......Page 88
How It Works......Page 89
How It Works......Page 90
Outer Joins......Page 91
Try It Out: Adding an Employee with No Orders......Page 92
Try It Out: Using LEFT OUTER JOIN......Page 93
How It Works......Page 94
Summary......Page 95
Retrieving Data......Page 97
Try It Out: Running a Simple Query......Page 98
How It Works......Page 99
Try It Out: Refining Your Query......Page 100
Using Comparison Operators in a WHERE Clause......Page 101
Sorting Data......Page 103
Try It Out: Writing an Enhanced Query......Page 104
How It Works......Page 105
Try It Out: Creating a New Table......Page 106
How It Works......Page 107
Try It Out: Using SELECT INTO to Copy Table Structure......Page 108
How It Works......Page 109
Try It Out: Inserting a New Row......Page 111
How It Works......Page 112
Updating Data......Page 113
How It Works......Page 114
Deleting Data......Page 116
Summary......Page 117
Try It Out: Working with a Stored Procedure in SQL Server......Page 119
How It Works......Page 121
Try It Out: Creating a Stored Procedure with an Input Parameter......Page 122
How It Works......Page 123
Try It Out: Creating a Stored Procedure with an Output Parameter......Page 124
How It Works......Page 125
Try It Out: Modifying the Stored Procedure......Page 126
Try It Out: Viewing the Definition of Your Stored Procedure......Page 128
Try It Out: Renaming a Stored Procedure......Page 129
Try It Out: Executing a Stored Procedure with No Input Parameters......Page 130
How It Works......Page 132
Try It Out: Executing a Stored Procedure with Parameters......Page 133
How It Works......Page 135
Try It Out: Deleting a Stored Procedure......Page 136
Summary......Page 137
Defining XML......Page 139
Benefits of Storing Data As XML......Page 140
Understanding XML Documents......Page 141
Understanding the XML Declaration......Page 142
Try It Out: Using FOR XML RAW (Attribute Centric)......Page 143
How It Works......Page 144
How It Works......Page 145
How It Works......Page 146
Try It Out: Using FOR XML AUTO......Page 147
Observations About FOR XML AUTO Formatting......Page 148
How It Works......Page 149
Try It Out: Storing and Retrieving XML Documents......Page 150
Summary......Page 151
What Is a Transaction?......Page 153
Understanding ACID Properties......Page 154
Transaction Design......Page 155
Specifying Transaction Boundaries......Page 156
Local Transactions in SQL Server 2005......Page 157
Distributed Transactions in SQL Server 2005......Page 158
Guidelines to Code Efficient Transactions......Page 159
Try It Out: Coding a Transaction in T-SQL......Page 160
How It Works......Page 164
Try It Out: What Happens When the First Operation Fails......Page 165
Try It Out: What Happens When the Second Operation Fails......Page 166
How It Works......Page 167
Try It Out: Working with ADO.NET Transactions......Page 168
How It Works......Page 170
Summary......Page 171
Understanding ADO.NET......Page 173
Moving from ADO to ADO.NET......Page 174
ADO.NET Isn’t a New Version of ADO......Page 175
ADO.NET and the .NET Base Class Library......Page 176
Understanding ADO.NET Architecture......Page 177
Working with the SQL Server Data Provider......Page 179
Try It Out: Creating a Simple Console Application Using the SQL Server Data Provider......Page 180
How It Works......Page 182
Working with the OLE DB Data Provider......Page 184
Try It Out: Creating a Simple Console Application Using the OLE DB Data Provider......Page 185
How It Works......Page 187
Working with the ODBC Data Provider......Page 188
Creating an ODBC Data Source......Page 189
Try It Out: Creating a Simple Console Application Using the ODBC Data Provider......Page 195
How It Works......Page 196
Data Providers As APIs......Page 197
Summary......Page 198
Introducing the Data Provider Connection Classes......Page 199
Try It Out: Using SqlConnection......Page 200
How It Works......Page 202
Debugging Connections to SQL Server......Page 204
How to Use SQL Server Security......Page 205
Connection String Parameters for SqlConnection......Page 206
Displaying Connection Information......Page 207
Try It Out: Displaying Connection Information......Page 208
How It Works......Page 209
Connecting to SQL Server Express with OleDbConnection......Page 211
Try It Out: Connecting to SQL Server Express with the OLE DB Data Provider......Page 212
How It Works......Page 213
Summary......Page 214
Try It Out: Creating a Command with a Constructor......Page 215
Try It Out: Setting the Connection Property......Page 217
Assigning Text to a Command......Page 218
How It Works......Page 219
Try It Out: Using the ExecuteScalar Method......Page 220
How It Works......Page 222
Try It Out: Using the ExecuteReader Method......Page 223
How It Works......Page 224
Try It Out: Using the ExecuteNonQuery Method......Page 225
How It Works......Page 227
Command Parameters......Page 228
Try It Out: Using Command Parameters......Page 229
Summary......Page 232
Understanding Data Readers in General......Page 233
Try It Out: Looping Through a Result Set......Page 234
How It Works......Page 235
Try It Out: Using Ordinal Indexers......Page 236
How It Works......Page 238
Using Typed Accessor Methods......Page 239
Try It Out: Using Typed Accessor Methods......Page 242
Getting Data About Data......Page 244
Try It Out: Getting Information About a Result Set with a Data Reader......Page 245
How It Works......Page 247
Try It Out: Getting Schema Information......Page 248
How It Works......Page 250
Try It Out: Handling Multiple Result Sets......Page 251
How It Works......Page 253
Summary......Page 254
Using Datasets and Data Adapters......Page 255
A Brief Introduction to Datasets......Page 256
A Brief Introduction to Data Adapters......Page 257
A Brief Introduction to Data Tables, Data Columns, and Data Rows......Page 258
Try It Out: Populating a Dataset with a Data Adapter......Page 259
How It Works......Page 261
Try It Out: Dynamically Filtering and Sorting Data in a Dataset......Page 262
How It Works......Page 265
Comparing FilterSort to PopDataSet......Page 267
Try It Out: Refining Data with a Data View......Page 268
How It Works......Page 270
Try It Out: Modifying a Data Table in a Dataset......Page 271
How It Works......Page 273
UpdateCommand Property......Page 274
Try It Out: Propagating Dataset Changes to a Data Source......Page 275
How It Works......Page 277
Try It Out: Propagating New Dataset Rows to a Data Source......Page 278
How It Works......Page 281
Try It Out: Propagating New Dataset Rows to a Data Source......Page 282
How It Works......Page 284
Try It Out: Using SqlCommandBuilder......Page 285
How It Works......Page 287
Concurrency......Page 288
Try It Out: Extracting a Dataset to an XML File......Page 289
How It Works......Page 291
Try It Out: Populating a Data Table with a Data Adapter......Page 292
Understanding Typed and Untyped Datasets......Page 294
Summary......Page 295
Understanding Windows Forms......Page 297
Simplicity......Page 298
Color......Page 299
Working with Windows Forms......Page 300
Understanding the Design and Code Views......Page 303
Categorized View......Page 304
Alphabetical View......Page 305
Setting Properties of Solutions, Projects, and Windows Forms......Page 306
Try It Out: Working with the TextBox and Button Controls......Page 307
Setting Dock and Anchor Properties......Page 310
Anchor Property......Page 311
Try It Out: Working with the Dock and Anchor Properties......Page 312
Try It Out: Adding a New Form to the Windows Project......Page 315
Try It Out: Setting the Startup Form......Page 316
Try It Out: Creating an MDI Parent Form with a Menu Bar......Page 318
Try It Out: Creating an MDI Child Form and Running an MDI Application......Page 319
How It Works......Page 321
Summary......Page 322
Understanding Web Functionality......Page 323
Introduction to ASP.NET and Web Pages......Page 324
File System Web Site......Page 325
FTP Web Site......Page 326
HTTP Web Site......Page 327
Layout of an ASP.NET Web Site......Page 328
Web Pages......Page 329
The web.config File......Page 331
Try It Out: Working with a Web Form......Page 332
Try It Out: Working with Split View......Page 333
Try It Out: Working with a Master Page......Page 336
Summary......Page 341
Try It Out: Handling an ADO.NET Exception (Part 1)......Page 343
How It Works......Page 347
Try It Out: Handling an ADO.NET Exception (Part 2)......Page 348
How It Works......Page 350
Handling Database Exceptions......Page 351
Try It Out: Handling a Database Exception (Part 1): RAISERROR......Page 353
How It Works......Page 355
Try It Out: Handling a Database Exception (Part 2): Stored Procedure Error......Page 356
Try It Out: Handling a Database Exception (Part 3): Errors Collection......Page 358
Summary......Page 360
Understanding Events......Page 361
Design of Events......Page 362
Event Generator and Consumer......Page 363
Try It Out: Creating an Event Handler......Page 364
Try It Out: Working with Mouse Movement Events......Page 365
How It Works......Page 368
Try It Out: Working with the Keyboard’s KeyDown and KeyUp Events......Page 369
Try It Out: Working with the Keyboard’s KeyPress Event......Page 370
Summary......Page 371
Understanding SQL Server Text and Binary Data Types......Page 373
Storing Images in a Database......Page 374
Try It Out: Loading Image Binary Data from Files......Page 375
How It Works......Page 379
Try It Out: Displaying Stored Images......Page 381
How It Works......Page 384
Working with Text Data......Page 385
Try It Out: Loading Text Data from a File......Page 386
How It Works......Page 389
Try It Out: Retrieving Text Data......Page 391
How It Works......Page 393
Summary......Page 394
Introduction to LINQ......Page 395
Architecture of LINQ......Page 397
LINQ Project Structure......Page 398
Using LINQ to Objects......Page 399
Try It Out: Coding a Simple LINQ to Objects Query......Page 400
How It Works......Page 401
Try It Out: Coding a Simple LINQ to SQL Query......Page 402
How It Works......Page 404
Try It Out: Using the where Clause......Page 405
Try It Out: Coding a Simple LINQ to XML Query......Page 406
Summary......Page 408
Understanding ADO.NET 3.5 Entity Framework......Page 409
Working with the Entity Data Model......Page 410
Try It Out: Creating an Entity Data Model......Page 412
How It Works......Page 419
Try It Out: Schema Abstraction Using an Entity Data Model......Page 420
Summary......Page 424
Index......Page 425