Table Of Content

This document was created by an unregistered ChmMagic, please go to http://www.bisenter.com to regis.ter it. Thanks Beginning ASP.NET Databases using VB.NET Jesudas Chinnathampi (Das) Fabio Claudio Ferrachiati James Greenwood John Kauffman Brian Matsik Eric N. Mintz Jan D. Narkiewicz Kent Tegels John West Donald Xie Wrox Press Ltd.® Copyright © 2002 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 authors and publisher have made every effort during the preparation of this book to ensure accuracy of the material. 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 have been caused either directly or indirectly by this book. Published by Wrox Press Ltd, Arden House, 1102 Warwick Road, Acocks Green, Birmingham, B27 6BH, UK This document was created by an unregistered ChmMagic, please go to http://www.bisenter.com to regis.ter it. Thanks Printed in the USA ISBN 0-76454-375-X Trademark Acknowledgements Wrox has endeavored to adhere to trademark conventions for all companies and products mentioned in this book, such as the appropriate use of capitalization. Wrox cannot however guarantee the accuracy of this information. Credits Authors Jesudas Chinnathampi (Das) Fabio Claudio Ferrachiati James Greenwood John Kauffman Brian Matsik Eric N. Mintz Jan D. Narkiewicz Kent Tegels John West Donald Xie Additional Material Matt Butler Dave Sussman Commisioning Editor Craig Berry Lead Technical Editors Jon Hill David Mercer Technical Editors Catherine Alexander Helen Callaghan Alastair Ewins Ian Nutt Douglas Paterson Rob Shaw Managing Editor Louay Fatoohi Author Agent Cilmara Lion Project Manager Christianne Bailey Technical Reviewers Richard Conway Damien Foggon Mark Horner Eric Mintz Osiris Navarro Reglero Adil Rehan David Schultz Brian Sherwin Production Coordinator Abbie Forletta Proof Reader This document was created by an unregistered ChmMagic, please go to http://www.bisenter.com to regis.ter it. Thanks Dev Lunsford Chris Smith Cover Natalie O'Donnell Index Martin Brooks About the Authors Jesudas Chinnathampi (Das) Jesudas Chinnathampi, also known as Das, has been working with Active Server Pages since 1998. Currently he is working with Silicomm Corporation, http://silicomm.com. Das is also a member of the ASPElite, a select group of developers who help to manage the discussions at ASPFriends.com. Das has been working with ASP .NET since the first Beta. Das also writes article// for ASPAlliance, http://aspalliance.com, the number one ASP.NET developer community. You can read the articles written by Das at http://aspalliance.com/das. Das has a Masters Degree in Computer Application. During leisure time, he enjoys driving, playing chess and watching games (Cricket, Basketball, Shuttle Badminton). You can reach Das at <[email protected]>. Fabio Claudio Ferracchiati Fabio Claudio Ferracchiati is a software developer and technical writer. In the early years of his ten-year career he worked with classical languages and old Microsoft tools like Visual Basic and Visual C++. After five years he decided to dedicate his attention to the Internet and related technologies. In 1998 he started a parallel career writing technical articles for Italian and international magazines. He works in Rome for CPI Progetti Spa (http://www.cpiprogetti.it) where he develops Internet/intranet solutions using Microsoft technologies. Fabio would like to thank the Wrox people who have given him the chance to write on this book, and especially Alastair for his kindness and consideration. To Danila: What are three years of love? Perhaps a long time for some; perhaps little time for others. To me, they have been so intense that they have seemed like an eternity. You have become part of me. You are the air that I breathe. Three years of pure love. Happy anniversary. I love you. James Greenwood James Greenwood is a technical architect and author based in West Yorkshire, England. He spends his days (and most of his nights) designing and implementing .NET solutions from government knowledge-management systems to mobile integration platforms, all the while waxing lyrical on the latest Microsoft technologies. His professional interests include research into distributed interfaces, the automation of application development, and human-machine convergence. When prised away from the keyboard, James can be found out and about, indulging in his other great loves - British sports cars and Egyptology. You can reach him at <[email protected]>. John Kauffman John Kauffman was born in Philadelphia, the son of a chemist and a nurse. John's family of six shared daily brain teasers and annual camping forays that covered most of the 50 United States. After jobs weeding strawberry patches, bussing tables, running spotlights for rock and roll concerts, touring North America with a drum and bugle corps, prematurely leaving three colleges, stuffing voles for a mammologist, packing boxes of rat poison, tarring roofs, delivering drapes in New York City, laboring in a candy factory, teaching canoeing, driving a forklift, studying tiger beetles in the Chihuahua desert, managing a picture framing factory, coaching a youth yacht racing team, and volunteering as a human guinea pig for medical research, John (to the great relief of all around him) earned a pair of degrees in the sciences from The Pennsylvania State University and appeared to settle down. He then conducted research for Hershey Foods in the genetics of the cacao tree and the molecular biology of chocolate production. Subsequently he moved to the Rockefeller University where he identified, cloned and sequenced DNA regions which control the day and night biochemical cycles of plants. But science didn't hold a candle to a woman he met in 1985 and married. Since then he has followed Liz in her career as a diplomat across four continents. They moved to Tanzania in 1986 and John began work with computers and business management in an This document was created by an unregistered ChmMagic, please go to http://www.bisen.ter.com to register it. Thanks assistance program for subsistence-level farmers. In 1990 they moved to Taiwan and then mainland China where John provided software training services to multi-national corporations and the diplomatic community in Beijing, Hong Kong, Shanghai, and Sichuan. During the graduation banquet for one course he was honored by his students with a special entree of snake bile, frog skin, and turtle meats. John and Liz performed their most significant genetics experiments in 1988 and 1990 with the production of their children Sylvia and John. Growing up in Africa and China, the kids are doing well hashing through another generation's worth of brain teasers and camping trips. John continues to teach, write, and program in Asia and North America, primarily in the areas of ASP, ASP.NET, Access, SQL and Visual Basic. This book is dedicated to Sylvia and John. Nothing else in my life makes me as proud as seeing you mature into fine young people. Keep up your good work in academics, fight hard on the field of play, and continue to improve your music. Never forget that each of the people around you has feelings that deserve your respect - "No boasting when you win, no gloating when you lose. "Be leaders by being the hardest workers and offering the hand of magnanimity. And above all, avoid what is expedient and do what you know is right. Thanks for being a part of our family. Brian Matsik Brian Matsik is the President and Senior Consultant at OOCS in Charlotte, NC and is currently a Microsoft Certified Solution Developer and Microsoft Certified Trainer. His experience with Visual Basic, VBScript, and VBA goes back to the DOS days and VB 2.0. Brian currently specializes in ASP, SQL Server, ADO, and VB COM. When he is not coding, training, or writing he is either in his scuba gear or in the garage trying to turn perfectly good oak into perfectly good kindling. Brian can be reached at <[email protected]>. Eric N. Mintz Eric Mintz is a software analyst with over 20 years' experience in a variety of technical and leadership positions. His ceaseless enthusiasm and curiosity have contributed to wide-ranging expertise, particularly in the areas of computer/human-interaction, data and object modeling, SQL, and Visual Basic. Currently, Eric is CEO of American Webware, Inc. in Atlanta where he resides with his wife and her three cats who all think he works way too hard. When he does have spare time, Eric likes to play jazz guitar and trumpet or go fly-fishing. Eric holds a BSEE, earned at the University of Houston, TX. Jan D. Narkiewicz Jan D. Narkiewicz is Chief Technical Officer at Software Pronto, Inc (<[email protected]>). In his spare time Jan is Academic Coordinator for the Windows curriculum at U.C. Berkeley Extension, teaches at U.C. Santa Cruz Extension and writes for ASP Today. Kent Tegels Kent Tegels is a system developer and engineer working for HDR, Inc., a leading Engineering, Architecture and Consulting Firm. He is a Microsoft Certified Professional, plus Site Builder, System Engineer (plus Internet), and Database Administrator. John West John West is a Principal Consultant at Intellinet, based out of Atlanta, Georgia. He specializes in leading Microsoft .NET application development efforts. When not working, he usually spends his time reading, hanging out with friends from church, and trying to learn to wakeboard. Donald Xie Donald Xie has 14 years experience in enterprise application development for various types of businesses. He is a co-author of Professional CDO Programming and a contributing author for a number of books. Donald currently works as a consulting system architect for the Department of Training in Western Australia, Australia. You can contact Donald at <[email protected]>. This document was created by an unregistered ChmMagic, please go to http://www.bisenter.com to register it.. Thanks .New in "Beginning ASP.NET Databases Using VB.NET - Written and Tested or Final Release of .NET v1.0" byJohn Kauffman, Fabio Claudio Ferracchiatiet al.? Wrox Press ?2002 New in "Beginning ASP.NET Databases Using VB.NET: Written and Tested or Final Release of .NET v1.0" Additional content from December 22, 0016 Introduction What Does This Book Cover? Who Is This Book For? What You Need to Use This Book Conventions Customer Support This document was created by an unregistered ChmMagic, please go to http://www.bisenter.com to register it. Thanks. . .Beginning ASP.NET Databases Using VB.NET (Written and Tested or Final Release of .NET v1.0) byJohn Kauffman, Fabio Claudio Ferracchiatiet al. ISBN:076454375X Wrox Press 2002 (459 pages) This book is for people who have some experience of programming ASP.NET with Visual Basic .NET, are familiar with the operation of the .NET Framework, and want to learn how to use ASP.NET to make data-centric web applications. Table of Contents Back Cover Table of Contents Beginning ASP.NET Databases using VB.NET New in "Beginning ASP.NET Databases Using VB.NET: Written and Tested or Final Release of .NET v1.0" Introduction Chapter 1 -Displaying Data on the Web Chapter 2 -Relational Databases Chapter 3 -Connecting to a Data Source Chapter 4 -Data Readers, Command Objects, and Web Server Controls Chapter 5 -Reading Data using the Dataset Object Chapter 6 -Creating and Inserting Records Chapter 7 -Updating and Deleting Records Chapter 8 -Using Stored Procedures Chapter 9 -Data-Driven ASP.NET Applications in the Real World Chapter 10 -Componentization Chapter 11 -Performance Chapter 12 -Writing an Application Index List of "Try It Out" This document was created by an unregistered ChmMagic, please go to http://www.bisenter.com to regis.ter it. Thanks .Introduction byJohn Kauffman, Fabio Claudio Ferracchiatiet al.? Wrox Press ?2002 Introduction One of the most important requirements for any web site is to accurately and safely transmit and store information. This information could be in any form, from the credit card details stored by a credit company, to the results of a straw poll on a marketing site. Whatever you use ASP.NET web pages for, you'll find that sooner or later you're going to need to access, read from, write to, and generally master operations involving a database. Luckily for us, working with databases from a web application is much simpler than it used to be. The .NET Framework has revolutionized the way in which we can develop sophisticated web sites. ASP.NET has made enormous improvements to the ways in which we can develop complex and interactive web sites, and ADO.NET - which provides powerful and flexible data processing functionality - has radically changed the ways that we can retrieve, process, and store data from within a web application. In this book, we're going to explain how to can create data-enabled ASP.NET applications. Written with hands-on learning in mind, it is packed with code examples that demonstrate important fundamental principles. Requiring some basic knowledge of ASP.NET and Visual Basic .NET, but assuming no prior experience of working with databases, the authors will guide you through the processes involved in connecting ASP.NET pages to a database, and investigate the various ways of reading, processing, and updating data. They'll then explore all the major issues involved in data-heavy ASP.NET programming, including some more advanced topics such as using stored procedures and components. The book concludes with a case study that puts everything in the preceding chapters into context. Revised: 08/19/2002 Note: Access to sample database A couple of readers have reported difficulty in getting access to the sample Northwind database from their ASP.NET applications. If this is happening to you, take a look at the following file, which should provide the help you need: Program Files\Microsoft Visual Studio .NET\FrameworkSDK\ Samples\Setup\html\ConfigDetails.htm Revised: 11/07/2002 Warning about operating systems To use this book, you need a computer running Windows 2000 (any version) or Windows XP Professional Edition. The examples it contains will not run on Windows XP Home Edition. What Does This Book Cover? Chapter 1 begins by looking at the general notion of creating data-driven web sites. More specifically, we talk about the relationship between web sites and data, introducing ADO.NET in the process. Later in the chapter, we look at setting up our development environment for the rest of the book, and discuss some of the stumbling blocks that you may encounter. It seems like a strange question, but what exactly is a database? In Chapter 2 we examine different types of databases, how they are designed, and how we can use them to store and retrieve information. We then move on to present an overview of the SQL language, and draw a quick comparison between Microsoft's database solutions. Having taken our first look at databases, we need to learn how to connect to them from our ASP.NET code. Chapter 1, clarifying the important terms that you need to know. We then move on to create and test a connection to the sample Northwind database that we'll be using throughout the book. This chapter contains numerous examples that demonstrate how to connect to diverse data sources such as Access, SQL Server, Excel, and XML, enabling us to be confident in whatever environment we have to develop in. Packed with examples, Chapter 4 looks at reading and displaying data using data reader objects. We start with a discussion of the theory of handling data in ASP.NET and ADO.NET that introduces command objects. After that, we go on to look at how we can use data readers to obtain data, and then bind that data to different controls like radio buttons, check boxes, and most importantly, the DataGrid. This document was created by an unregistered ChmMagic, please go to http://www.bisenter.com to register it. Thanks. Chapter 5 talks about an important ADO.NET object calledD ataSet, which is used to store and retrieve data of any complexity on the server. Once the initial theory is dealt with, we use plenty of examples to demonstrate different ways to use a dataset. Of course, we don't always just read other people's data, so Chapter 6 explains how to create our own records in existing databases. Topics covered in this chapter include ASP.NET's validation controls, and using command and DataSet objects to perform record insertions. Following on from the previous chapter, Chapter 7 demonstrates how to modify and delete records. We look at how to edit data using the DataGrid and DataView controls, and at how to update and delete records from a dataset object Next in line is Chapter 8, which looks at stored procedures. In it, we explain what they are, what they can be used for, and some of their advantages and disadvantages. After demonstrating how to create stored procedures with Visual Basic .NET, this chapter shows how to invoke them from your ASP.NET code, and how to supply them with parameters to perform certain tasks. Chapter 9 is a mine of information regarding some of the more advanced topics that can crop up when you're creating commercial-quality data-driven ASP.NET applications. The four subjects that we introduce and explain here are error handling, code structure, scalability and concurrency, and security. Approaching the end of the book, Chapter 10 introduces components, explaining why they can be valuable before discussing how to create them. The code samples in this chapter center around the creation of a class library that includes numerous methods for accessing a database. Since one of the goals of any programmer should be to write the best code possible, Chapter 11 is devoted to performance issues. We look at connection pooling and performance counters, and produce some metrics on the relative performance of dataset and data reader objects in different situations. We also highlight the importance of porting old ADO code to new ADO.NET as soon as you can. We finish with a case study in Chapter 12. This builds on all the subjects that have been presented in the book, and presents a complete, real-world application that uses multiple ASPX pages, ASP.NET web server controls, VB.NET components, and stored procedures. This document was created by an unregistered ChmMagic, please go to http://www.bisenter.com to re.gister it. Thanks .Introduction byJohn Kauffman, Fabio Claudio Ferracchiatiet al.? Wrox Press ?2002 Who Is This Book For? This book is for people who: Have some experience of Visual Basic .NET and ASP.NET Have some familiarity with the .NET Framework and related technologies Want to learn how to use ASP.NET to create data-enabled web applications However, you need to know nothing about databases in order to understand this book - although, of course, a little prior knowledge never goes amiss. This document was created by an unregistered ChmMagic, please go to http://www.bisenter.com to re.gister it. Thanks .Introduction byJohn Kauffman, Fabio Claudio Ferracchiatiet al.? Wrox Press ?2002 What You Need to Use This Book To run the examples in this book, you need to have a PC running the following: Microsoft Windows NT, Windows 2000, or Windows XP The .NET Framework A version of Visual Studio .NET that includes, as a minimum, the Standard Edition of Visual Basic .NET In addition, some of the examples use Microsoft Access and Microsoft SQL Server, but there is no requirement for you to own either. All of the important examples use the MSDE as their database engine, and this ships with all versions of Microsoft Visual Studio .NET.