Beginning VB 2008 Databases (eBook)

Vidya Vrat Agarwal, a Microsoft .NET purist and an MCT, MCPD, MCTS, MCSD.NET, MCAD.NET, and MCSD, works with Lionbridge Technologies (NASDAQ: LIOX), and his business card reads "Subject Matter Expert" (SME). He is also a lifetime member of the Computer Society of India (CSI). He started working on Microsoft .NET with its beta release. He has been involved in software development, evangelism, consultation, corporate training, and T3 programs on Microsoft .NET for various employers and corporate clients. His articles can be read at ProgrammersHeaven.com, and he also reviews .NET preparation kits, available at UCertify.com. He has contributed as technical reviewer to many books published by Apress, including Beginning VB 2008 Databases: From Novice to Professional. He lives with his beloved wife, Rupali, and lovely daughter, Vamika ("Pearly"). He believes that nothing will turn into a reality without them. He is the follower of the concept "No pain, no gain" and believes that his wife is his greatest strength. He is a bibliophile; when he is not working on technical stuff, he likes to be with his family and also likes reading spiritual and occult science books. He blogs at http://Dotnetpassion.blogspot.com. You can reach him at Vidya_mct@yahoo.com.

Contents at a Glance 5
Contents 6
About the Authors 19
About the Technical Reviewer 20
Acknowledgments 21
Introduction 22
Who This Book Is For 22
What This Book Covers 22
How This Book Is Organized 23
How to Download the Sample Code 23
Contacting the Author 23
Getting Your Tools 24
Obtaining Visual Studio 2008 25
Installing SQL Server Management Studio Express 26
Installing the Northwind Sample Database 27
Installing the AdventureWorks Sample Database 32
Summary 36
Getting to Know Your Tools 37
Microsoft . NET Framework Versions and the Green Bit and Red Bit Assembly Model 37
Using Microsoft Visual Studio 2008 38
Using SQL Server Management Studio Express 42
Summary 46
Getting to Know Relational Databases 47
What Is a Database? 47
Choosing Between a Spreadsheet and a Database 48
Why Use a Database? 48
Benefits of Using a Relational Database Management System 48
Comparing Desktop and Server RDBMS Systems 49
The Database Life Cycle 50
Mapping Cardinalities 51
Understanding Keys 53
Understanding Data Integrity 54
Normalization Concepts 55
Drawbacks of Normalization 56
Summary 56
Writing Database Queries 57
Comparing QBE and SQL 57
Beginning with Queries 59
Common Table Expressions 60
GROUP BY Clause 62
PIVOT Operator 64
ROW_ NUMBER() Function 66
PARTITION BY Clause 67
Pattern Matching 68
Aggregate Functions 73
DATETIME Functions 75
Joins 77
Summary 87
Manipulating Database Data 88
Retrieving Data 88
Using SELECT INTO Statements 97
Inserting Data 102
Updating Data 104
Deleting Data 107
Summary 108
Using Stored Procedures 109
Creating Stored Procedures 109
Modifying Stored Procedures 116
Displaying Definitions of Stored Procedures 118
Renaming Stored Procedures 119
Working with Stored Procedures in VB . NET 120
Deleting Stored Procedures 126
Summary 127
Using XML 128
Defining XML 128
Why XML? 129
Benefits of Storing Data As XML 129
Understanding XML Documents 130
Understanding the XML Declaration 131
Converting Relational Data to XML 132
Using the xml Data Type 138
Summary 140
Understanding Transactions 141
What Is a Transaction? 141
When to Use Transactions 142
Understanding ACID Properties 142
Transaction Design 143
Transaction State 144
Specifying Transaction Boundaries 144
T- SQL Statements Allowed in a Transaction 145
Local Transactions in SQL Server 2005 145
Distributed Transactions in SQL Server 2005 146
Guidelines to Code Efficient Transactions 147
How to Code Transactions 148
Summary 159
Getting to Know ADO.NET 160
Understanding ADO. NET 160
The Motivation Behind ADO. NET 161
Moving from ADO to ADO. NET 161
Understanding ADO. NET Architecture 164
Working with the SQL Server Data Provider 166
Working with the OLE DB Data Provider 171
Working with the ODBC Data Provider 175
Data Providers As APIs 184
Summary 185
Making Connections 186
Introducing the Data Provider Connection Classes 186
Connecting to SQL Server Express with SqlConnection 187
Improving Your Use of Connection Objects 194
Connecting to SQL Server Express with OleDbConnection 198
Summary 201
Executing Commands 202
Creating a Command 202
Executing Commands 207
Executing Commands with Multiple Results 210
Executing Statements 212
Command Parameters 215
Summary 219
Using Data Readers 220
Understanding Data Readers in General 220
Getting Data About Data 231
Getting Data About Tables 235
Using Multiple Result Sets with a Data Reader 238
Summary 241
Using Datasets and Data Adapters 242
Understanding the Object Model 243
Working with Datasets and Data Adapters 246
Propagating Changes to a Data Source 261
Concurrency 275
Using Datasets and XML 276
Using Data Tables Without Datasets 279
Understanding Typed and Untyped Datasets 281
Summary 282
Building Windows Forms Applications 284
Understanding Windows Forms 284
User Interface Design Principles 285
Best Practices for User Interface Design 285
Working with Windows Forms 287
Understanding the Design and Code Views 290
Sorting Properties in the Properties Window 291
Setting Properties of Solutions, Projects, and Windows Forms 293
Working with Controls 294
Setting Dock and Anchor Properties 297
Adding a New Form to the Project 302
Implementing an MDI Form 305
Summary 309
Building ASP.NET Applications 310
Understanding Web Functionality 310
Introduction to ASP. NET and Web Pages 311
Understanding the Visual Studio 2008 Web Site Types 312
Layout of an ASP. NET Web Site 315
Using Master Pages 323
Summary 328
Handling Exceptions 329
Handling ADO. NET Exceptions 329
Handling Database Exceptions 337
Summary 346
Working with Events 347
Understanding Events 347
Properties of Events 348
Design of Events 348
Common Events Raised by Controls 349
Event Generator and Consumer 349
Summary 357
Working with Text and Binary Data 358
Understanding SQL Server Text and Binary Data Types 358
Storing Images in a Database 359
Retrieving Images from a Database 366
Working with Text Data 370
Summary 379
Using LINQ 380
Introduction to LINQ 380
Architecture of LINQ 382
LINQ Project Structure 383
Using LINQ to Objects 384
Using LINQ to SQL 387
Using LINQ to XML 391
Summary 393
Using ADO.NET 3.5 394
Understanding ADO. NET 3.5 Entity Framework 394
Understanding the Entity Data Model 395
Working with the Entity Data Model 395
Summary 409
Index 410