Beginning VB. 2008 Databases From Novice to Pro [Visual Basic]

439 Pages·2008·8.87 MB·English

by  James Huddleston

cyan yelloW
MaGenTa Black
panTone 123 c
Books for professionals By professionals® The eXperT’s Voice® in .neT
Companion
eBook Available
Beginning VB 2008 Databases:
Beginning
From Novice to Professional
V Beginning
Dear Reader,
B
This book focuses on accessing databases using VB 2008 as a development tool
in conjunction with the new release of Visual Studio 2008 and .NET Framework

3.5. This book will walk you through all the aspects of programming a database
2 VB 2008
Vidya Vrat Agarwal, with SQL Server 2005, pulling the data into an application developed using
author of VB 2008.
Beginning C# 2008 As you work your way through this book, you get a chance to explore the 0
Databases: From Novice concepts covered by creating sample applications in “Try It Out” sections,
to Professional which will help you apply what you learn to your real-world applications. 0
Following the sample applications, I explain each code statement in “How It
Works” sections to help you understand the code. 8
I hope that you will achieve not only knowledge of VB 2008, but also
knowledge of SQL Server. I have targeted quite a few database concepts, from
the basics to the key T-SQL features of SQL Server. This book will also help you D Databases
to build your code competency in a gradual manner because I begin with the
easy topics before moving on to the complex ones. This book starts from basic
a
application development and goes over the concepts of LINQ and ADO.NET 3.5
and building applications with them.
t
I believe that you will find this book to be an asset in enriching your VB database
application development skills, and will provide you with new insights into a
using VB 2008 with SQL Server.
b
James Huddleston
Vidya Vrat Agarwal From Novice to Professional
a
s
Companion eBook THE APRESS ROADMAP
e
Pro VB 2008 and the
Beginning
Beginning VB 2008 .NET 3.5 Platform,
VB 2008 Databases Third Edition s
Pro WPF with VB 2008
Beginning ASP.NET 3.5 Visual Basic 2008
See last page for details Accelerated VB 2008 in VB 2008, Recipes
on $10 eBook version Second Edition
H
u
SOURCE CODE ONLINE dA Vidya Vrat Agarwal
d
www.apress.com ISBN-13: 978-1-59059-947-1 g
l
ISBN-10: 1-59059-947-0 ea
sr
US $44.99 54499 tonwa and James Huddleston
l
,
Shelve in
Programming Languages/
Visual Basic
User level:
9 781590 599471
Beginner–Intermediate
this print for content only—size & color not accurate spine = 0.835" 440 page count

9470fmfinal.qxd 3/15/08 2:51 PM Page i
Beginning VB 2008
Databases
From Novice to Professional
Vidya Vrat Agarwal and
James Huddleston
9470fmfinal.qxd 3/15/08 2:51 PM Page ii
Beginning VB 2008 Databases: From Novice to Professional
Copyright © 2008 by Vidya Vrat Agarwal and James Huddleston
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-13 (pbk): 978-1-59059-947-1
ISBN-10 (pbk): 1-59059-947-0
ISBN-13 (electronic): 978-1-4302-0560-9
ISBN-10 (electronic): 1-4302-0560-1
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 Editor: Dominic Shakeshaft
Technical Reviewer: Fabio Claudio Ferracchiati
Editorial Board: Clay Andres, Steve Anglin, Ewan Buckingham, Tony Campbell, Gary Cornell,
JonathanGennick, Matthew Moodie, Joseph Ottinger, Jeffrey Pepper, Frank Pohlmann,
Ben Renow-Clarke, Dominic Shakeshaft, Matt Wade, Tom Welsh
Senior Project Manager: Sofia Marchant
Copy Editor: Liz Welch
Associate Production Director: Kari Brooks-Copony
Senior Production Editor: Laura Cheu
Compositor: Linda Weidemann, Wolf Creek Press
Proofreader: Nancy Sixsmith
Indexer: Broccoli Information Management
Artist: April Milne
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 [phone], e-mail [email protected],
orvisit http://www.springeronline.com.
For information on translations, please contact Apress directly at 2855 Telegraph Avenue, Suite 600,
Berkeley, CA 94705. Phone [phone], fax [phone], e-mail [email protected], or visit
http://www.apress.com.
Apress and friends of ED books may be purchased in bulk for academic, corporate, or promotional use.
eBook versions and licenses are also available for most titles. For more information, reference our
SpecialBulk Sales–eBook Licensing web page at http://www.apress.com/info/bulksales.
The information in this book is distributed on an “as is” basis, without warranty. Although every pre-
caution 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.
9470fmfinal.qxd 3/15/08 2:51 PM Page iii
To my sweet little daughter (Pearly) and beloved wife (Rupali).
You are precious in my eyes,
and honored,and I love you.
—Vidya Vrat Agarwal
9470fmfinal.qxd 3/15/08 2:51 PM Page iv
9470fmfinal.qxd 3/15/08 2:51 PM Page v
Contents at a Glance
About the Authors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxi
About the Technical Reviewer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxiii
Acknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxv
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxvii
nCHAPTER 1 Getting Your Tools. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
nCHAPTER 2 Getting to Know Your Tools . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
nCHAPTER 3 Getting to Know Relational Databases. . . . . . . . . . . . . . . . . . . . . . . . . . 25
nCHAPTER 4 Writing Database Queries. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35
nCHAPTER 5 Manipulating Database Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67
nCHAPTER 6 Using Stored Procedures. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 89
nCHAPTER 7 Using XML. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109
nCHAPTER 8 Understanding Transactions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 123
nCHAPTER 9 Getting to Know ADO.NET. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 143
nCHAPTER 10 Making Connections. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 169
nCHAPTER 11 Executing Commands. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 185
nCHAPTER 12 Using Data Readers. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 203
nCHAPTER 13 Using Datasets and Data Adapters . . . . . . . . . . . . . . . . . . . . . . . . . . . . 225
nCHAPTER 14 Building Windows Forms Applications. . . . . . . . . . . . . . . . . . . . . . . . . 267
nCHAPTER 15 Building ASP.NET Applications. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 293
nCHAPTER 16 Handling Exceptions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 313
nCHAPTER 17 Working with Events. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 331
nCHAPTER 18 Working with Text and Binary Data. . . . . . . . . . . . . . . . . . . . . . . . . . . . 343
nCHAPTER 19 Using LINQ . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 365
nCHAPTER 20 Using ADO.NET 3.5 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 379
nINDEX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 395
v
9470fmfinal.qxd 3/15/08 2:51 PM Page vi
9470fmfinal.qxd 3/15/08 2:51 PM Page vii
Contents
About the Authors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxi
About the Technical Reviewer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxiii
Acknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxv
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxvii
nCHAPTER 1 Getting Your Tools . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
Obtaining Visual Studio 2008 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2
Installing SQL Server Management Studio Express. . . . . . . . . . . . . . . . . . . . 3
Installing the Northwind Sample Database. . . . . . . . . . . . . . . . . . . . . . . . . . . 4
Installing the Northwind Creation Script . . . . . . . . . . . . . . . . . . . . . . . . 4
Creating the Northwind Sample Database . . . . . . . . . . . . . . . . . . . . . . 6
Installing the AdventureWorks Sample Database . . . . . . . . . . . . . . . . . . . . . 9
Installing the AdventureWorks Creation Script. . . . . . . . . . . . . . . . . . . 9
Creating the AdventureWorks Sample Database. . . . . . . . . . . . . . . . 10
Summary. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
nCHAPTER 2 Getting to Know Your Tools . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
Microsoft .NET Framework Versions and the Green Bit and Red Bit
Assembly Model. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
Using Microsoft Visual Studio 2008. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
Try It Out:Creating a Simple Console Application Project
Using Visual Studio 2008 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18
How It Works . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
Using SQL Server Management Studio Express. . . . . . . . . . . . . . . . . . . . . . 20
Summary. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
nCHAPTER 3 Getting to Know Relational Databases . . . . . . . . . . . . . . . . . . . . 25
What Is a Database?. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
Choosing Between a Spreadsheet and a Database. . . . . . . . . . . . . . . . . . . 26
vii
97a7b91d187e1703ee1e25f687516049
9470fmfinal.qxd 3/15/08 2:51 PM Page viii
viii nCONTENTS
Why Use a Database?. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26
Benefits of Using a Relational Database Management System. . . . . . . . . 26
Comparing Desktop and Server RDBMS Systems. . . . . . . . . . . . . . . . . . . . 27
Desktop Databases. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27
Server Databases. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27
The Database Life Cycle. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28
Mapping Cardinalities. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
Understanding Keys . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31
Primary Keys. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32
Foreign Keys. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32
Understanding Data Integrity. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32
Entity Integrity . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32
Referential Integrity. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33
Normalization Concepts. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33
Drawbacks of Normalization. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34
Summary. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34
nCHAPTER 4 Writing Database Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35
Comparing QBE and SQL. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35
Beginning with Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
Try It Out:Running a Simple Query . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
How It Works . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38
Common Table Expressions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38
Try It Out:Creating a CTE. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39
How It Works . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40
GROUP BY Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40
Try It Out:Using the GROUP BY Clause . . . . . . . . . . . . . . . . . . . . . . . . 40
How It Works . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41
PIVOT Operator. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42
Try It Out:Using the PIVOT Operator . . . . . . . . . . . . . . . . . . . . . . . . . . 42
How It Works . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43
ROW_NUMBER() Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44
Try It Out:Using the ROW_NUMBER() Function . . . . . . . . . . . . . . . . . 44
How It Works. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44