Table Of ContentBeginning Visual Basic .NET
Database Programming
Denise Gosnell
Matthew Reynolds
Bill Forgey
Wrox Press Ltd.
Beginning Visual Basic .NET
Database Programming
© 2001 Wrox Press
All rights reserved. No part of this book may be reproduced, stored in a retrieval system or transmitted
in any form or by any means, without the prior written permission of the publisher, except in the case of
brief quotations embodied in critical articles or reviews.
The author and publisher have made every effort in the preparation of this book to ensure the accuracy
of the information. However, the information contained in this book is sold without warranty, either
express or implied. Neither the authors, Wrox Press, nor its dealers or distributors will be held liable for
any damages caused or alleged to be caused either directly or indirectly by this book.
Published by Wrox Press Ltd,
Arden House, 1102 Warwick Road, Acocks Green,
Birmingham, B27 6BH, UK
Printed in the United States
ISBN 1861005555
Trademark Acknowledgements
Wrox has endeavored to provide trademark information about all the companies and products
mentioned in this book by the appropriate use of capitals. However, Wrox cannot guarantee the
accuracy of this information.
Credits
Authors Technical Editors
Denise Gosnell Victoria Blackburn
Matthew Reynolds Richard Deeson
Bill Forgey
Author Agent
Technical Reviewers Laura Jones
Beth Breidenbach
PJ Burke Project Administrator
Mike Clark Rob Hesketh
Simon Delamare
Damien Foggon Category Manager
Zach Greenvoss Sarah Drew
Mark Horner
Wendy Lanning Production Manager
Carl Mayes Liz Toy
Dale Onyon
Sumit Pal Production Coordinator
Rachelle Reese Pip Wonson
Sean M Schade
David Schultz Production Assistant
Brian Sherwin Matt Clark
Phillip Sidari
Konstantinos Vlassis Index
David Williams Michael Brinkman
Thearon Willis
Proof Reader
Agnes Wiggers
Technical Architect Cover
Paul Jeffcoat Dawn Chellingworth
About the Authors
Denise Gosnell
Denise Gosnell is a consultant in the Microsoft Consulting Services Public Sector Practice at Microsoft
([email protected]). Denise has a unique background in both law and technology and uses her
background to help federal, state, and local governments implement hi-tech solutions.
She received a bachelor's degree in Computer Science – Business (summa cum laude) from Anderson
University and a Doctor of Jurisprudence from Indiana University School of Law in Indianapolis.
Denise is an attorney licensed to practice law in Indiana and is an active member of the Indiana and
Indianapolis Bar Associations. Her legal areas of expertise are intellectual property law and real estate
law. Denise is also a Microsoft Certified Solution Developer.
Denise has worked in the computer industry since 1994 in a variety of roles ranging from Systems
Engineer, Programmer, IS Manager, and Senior Consultant. Denise is also an avid writer, and has co-
authored the following books: MSDE Bible (IDG Books), Professional SQL Server 2000 XML (Wrox Press),
and Professional .NET Framework (Wrox Press).
When Denise isn't working, writing, or studying, she and her husband Jake enjoy traveling around the
globe to interesting places such as Russia, China, and Poland.
To my husband Jake for his patience and understanding this year while I was simultaneously
working on three books with Wrox on most evenings and weekends. To the fine folks at Wrox Press
for making this book a reality.
Matthew Reynolds
After working with Wrox Press on a number of projects since 1999, Matthew is now an in-house author
for Wrox Press writing about and working with virtually all aspects of Microsoft .NET. He's also a
regular contributor to Wrox's ASPToday and C#Today, and Web Services Architect. He lives and works in
North London and can be reached on [email protected].
For Fanjeev Sarin.
Thanks very much to the following in their support and assistance in writing this book: Len,
Edward, Darren, Alex, Jo, Tim, Clare, Martin, Niahm, Tom, Ollie, Amir, Gretchen, Ben,
Brandon, Denise, Rob, Waggy, Mark, Elaine, James, Zoe, Faye and Sarah. And, also thanks to my
new friends at Wrox, which include Charlotte, Laura, Karli, Dom S, Dom L, Ian, Kate, Joy, Pete,
Helen, Vickie, John, Dave, Adam, Craig, Jake, Julian, Rob and Paul.
Bill Forgey
Bill writes: "I began my career in the early 1990's, originally an Electronic Engineering major and, soon
after, the U.S. Navy. I soon found myself in a shut down engineering firm and was too stubborn to take
anything less. My shipmate introduced me to VB 3.0 and Access 2.0 and, for the next few months, I
found myself learning everything I could about VB. I began developing a phonebook program using VB
and MS Access. I would program 12 to 14 hours a day, including all nighters or until my hands got
numb. I read every book I could on VB, many of which were references and how to's. Everything I
wanted to do in VB I was able to, thanks to the language. After four months of steady learning, I landed
a contract position writing VB software to control data acquisition modules – luckily the majority of the
work was with VB and Access. I thought I knew everything after that. I earned a grand a week and soon
forgot about school. For my first three years I worked very hard and put in lots of hours, and I bought
and read even more books. Books like Dan Appleman's Programmer's API, which I didn't understand for
over a year after I bought it. As soon as Wrox books came out I was hooked. My first book was the
Revolutionary Guide to Visual C++. I liked the style as well as the straight forward information not found
anywhere else. As the years have passed, I have found learning new and other types of technology
much easier. I found it just takes time, dedication, and some common sense to succeed in this business.
I am the Technical Lead in my current position, introducing project methodology, new technologies,
standards, and training to development teams. I have spent some time consulting and have been
exposed to technologies such as ASP, Delphi, Pascal, COM, C/C++, SQL, Java, ADO, Visual Basic,
and now .NET. I currently live in Sacramento, California, and can be contacted via e-mail at
[email protected]."
Thanks goes out to Wrox Press, Paul, Richard, Rob, Laura, these are wonderful people to work
with. Also thanks to the team of technical reviewers.
I'd also like to thank Desiree for being so forgiving for all those late nights and lost moments. I
could never write the words to express my feelings about you.
Introduction
All software is based on the principle of manipulating data. Whether it's the code that runs inside your
VCR to start recording at a specific time, or air traffic control software, code is always working with
data in one form or another.
Today, we find that sophisticated applications store their data in a "database", a central repository of
data overseen by a Database Management System, or DBMS. A DBMS does two things. Firstly, it
handles the storage of the data. Secondly, it provides mechanisms for retrieving data as well as adding,
removing, and changing data. A DBMS endeavors to do this in the most efficient way possible.
Over the years, the DBMS market has grown into a mature sophisticated industry in its own right, offering
products designed for use in large enterprise environments like Oracle 9i or Microsoft SQL Server 2000,
down to products designed for use on the desktop like Microsoft Access. In some cases, you even find that
software packages include their own DBMS software for managing their own proprietary databases.
You'll find in your work as a programmer that applications often require access to data managed by a
DBMS. In fact, you'll most likely find that using a DBMS is the easiest way to store and manipulate your
application's data. However, with a wide variety of vendors to choose from, how can we write
application code that can work with any database our customer cares to choose?
The trick here is to build your application to work with a "data access layer" of some kind. Rather than
writing code that specifically requires a specific DBMS, you write code that talks to the layer. It's then
the layer's responsibility to switch to the "native" calls that the DBMS itself uses. Microsoft calls this
vision "Universal Data Access", or UDA. Microsoft's latest tool for UDA is ADO.NET, a comprehensive
set of objects that work together to make up a data access layer.
Introduction
This book is all about building Visual Basic .NET applications that harness the power of ADO.NET. We
will show how to use this technology in a variety of different ways: with desktop applications using
Windows Forms; with Web applications using ASP.NET; and with Web Services.
Who Is This Book For?
This book is for programmers with some basic experience of Visual Basic .NET, who want to begin
programming database applications.
It might be useful if you have some limited experience of Access, although this is not strictly necessary.
Note that this book is not an introduction to Visual Basic .NET. If you are completely new to Visual
Basic .NET, you will probably find Beginning Visual Basic .NET (Wrox Press, ISBN 1861004966) a better
choice to get you off the ground.
Likewise, this book is not aimed at getting experienced VB6 developers up to speed with the changes
between VB6 and Visual Basic .NET. If you fall into this category, try Professional VB.NET (Wrox Press,
ISBN 1861004974) instead.
What Does This Book Cover?
Visual Basic .NET is tightly coupled to very comprehensive and flexible data access technologies, so the
potential range of things that might fall under the title of this book is huge. Rather than trying to cover
too much, we have concentrated on providing a detailed introduction to the following strands:
❑ Basic database design principles.
❑ The SQL Server Desktop Engine.
❑ Querying the database using T-SQL.
❑ Using Visual Studio .NET's Server Explorer to run queries, views, stored procedures, etc.
❑ ADO.NET and the DataSet object.
❑ Reading data into the DataSet, binding it to a control on the user interface, changing data in
the DataSet, and saving those changes back in the underlying database.
❑ XML's role in ADO.NET.
❑ Internet database applications using Web Forms and Web Services.
What Do I Need to Use this Book?
All you'll need is a PC running:
❑ Windows 2000, XP, or NT4 Server.
❑ IIS 5, which comes with Windows 2000 and Windows XP.
2
Introduction
❑ Internet Explorer.
❑ Access XP (or 2000).
❑ Visual Studio .NET Professional edition. (Higher versions of Visual Studio, e.g. the Enterprise
editions, should work fine too. However, at the time of writing, they were unavailable and so
this book was written using the Professional edition.)
❑ SQL Server 2000 Desktop Engine. This comes with Visual Studio .NET.
This book was written before the final release of Visual Studio .NET. If there are any
substantial changes between the instructions given in this book and those required to
work with the final release of Visual Studio .NET, we will provide free updates on the
Wrox online errata service.
Conventions
We've used a number of different styles of text and layout in this book to help differentiate between the different
kinds of information. Here are examples of the styles we used and an explanation of what they mean.
Try It Outs – How Do They Work?
1.
Each step has a number.
2.
Follow the steps through.
3.
Then read the How It Works section to find out what's going on.
These boxes hold important, not-to-be forgotten, mission-critical details that are
directly relevant to the surrounding text.
Background information, asides, and references appear in text like this.
Bullets appear indented, with each new bullet marked as follows:
❑ Important words are in a bold type font
❑ Words that appear on the screen, or in menus like the File or Window, are in a similar font to
the one you would see on a Windows desktop
❑ Keys that you press on the keyboard, like Ctrl and Enter, are in italics
Code has several fonts. If it's a word that we're talking about in the text, for example, when discussing a
For ... Next loop, it's in this font. If it's a block of code that can be typed as a program and run, then
it's also in a gray box:
3
