Database Access with Visual Basic .NET / Edition 3

The purpose of this book is to show you how to develop database applications, using Visual Basic.NET and ADO.NET.

Although both the OLEDB and the ODBC Data Providers for .NET are discussed (Chapter 4), almost all the demonstrations and examples in this book use the data provider for Microsoft SQL Server. It is readily available and used by most of our readers. Moreover, applying the concepts and making the required code modifications for other data sources are normally very straightforward. We point out where there are significant differences.

Even though we expect that most readers will be working with SQL Server, we do not assume that they are experienced with it. You may be new to database applications or only have experience with other databases such as Microsoft Access or Oracle. We have therefore included a hefty overview of Microsoft SQL Server in Chapter 3. If you are experienced with SQL Server, you may want to skip this chapter. However, it covers a wide variety of topics, and you may still find a "nugget" or two that would make reading the chapter worthwhile.

Along with the coverage of SQL Server as a data source, we have included coverage of XML and XML integration with ADO.NET. This aspect of developing database applications with Visual Basic.NET is crucial and is often overlooked or short-changed. However, because XML is so important to developing modern data-driven applications we have provided rather extensive coverage of this topic.

This book contains a lot of demonstrations, examples, and code. We believe that their use is the best way to help you understand the concepts being presented. We normally provide relatively simple examples to demonstrate the concepts and then present Business Cases to put the concepts into a real-world context.

Who This Book Is For

We assume that you are already familiar with Visual Basic.NET (VB.NET). Although we give step-by-step coding instructions and code examples, we do not cover VB.NET syntax in this book. We assume that you are reasonably comfortable with VB.NET and Visual Studio.NET (VS.NET) and do not waste your time reviewing fundamental concepts. For example, we do not normally step through basic tasks, such as opening and saving project files, except maybe the first time you encounter them. We do, however, often show how to do the same thing in several different ways—we hope that this will extend your knowledge of VB.NET, without rehashing the basics. You may also notice variant coding styles, and even screen shots on different versions of Windows—all illustrating the flexibility of VB.NET.

Most of the examples in this book are presented with Windows Application (Windows Forms) as the project type. The reason is that nearly all Visual Basic programmers are most familiar and comfortable with this type of application. This approach allows us to focus on database access, rather than on the issues involved in the different types of .NET projects. Still, in later chapters, we do discuss and show ASP.NET Web Applications and Web Services, providing database access examples for these technologies and project types.

How This Book Is Organized

This book can be thought of as containing three parts. The first part, consisting of Chapters 1-3, comprises the preliminaries. The coverage of database basics, SQL, and SQL Server is meant to provide the fundamentals required for the novice to proceed comfortably throughout the rest of the book. At the same time, these chapters provide a good review of these topics even for someone who is experienced is these areas.

The second part can be thought of as the core of the book. Chapters 4-7 provide in-depth explanations and numerous examples of the major ADO.NET objects and the use of their properties and methods. Chapter 7 goes beyond the basics to explore advanced features and techniques of the ADO.NET objects.

The third part of the book shows how the ADO.NET technologies and techniques previously presented can be used to build real-world applications. This part includes the use of Visual Studio Database Projects for managing SQL scripts in Chapter 8, a discussion of XML in Chapter 9, and the integration of XML and ADO.NET in Chapter 10. Finally, we present additional types of applications that utilize ADO.NET: Chapter 11 covers ASP.NET Web Applications and Chapter 12 covers Web Services and middle-tier objects.

The Software Environment

We assume that you have already installed, or are capable of installing, Visual Studio.NET. The only thing to note regarding its use is that there are significant differences between available capabilities and behaviors of the visual database tools, depending on the edition of Visual Studio and the type of database that you use. Some of these differences are as follows.

In parts of Chapters 1, 2, and 8 we use some features found only in the Enterprise Developer or Enterprise Architect versions of Visual Studio.

In Chapter 3 we provide step-by-step instructions for installing SQL Server 2000, in case you're not familiar with the process. We recommend that you back up or make a copy of the pubs sample database installed with SQL Server because many of the code examples use this database and some of them modify the data that it contains.

All the Business Cases and many of the other code samples use the Novelty database, which was designed specifically for this book. Both Chapters 3 and 8 show the development of SQL scripts to create this database. To use many of the code samples in the book, you must first create and populate the Novelty database on SQL Server 2000. The steps provided here are based on the assumption that the user (you, in most cases) logging in has the rights necessary to create a database on the server. Keep in mind that some people may refer to a database and actually mean the application that handles the data—that is not the case anywhere in this book. When we use the word database, we explicitly mean the container of organized, relational data kept in SQL Server 2000.

Included in the download samples for this book, located at http://www.awprofessional.com/titles/0672323435, is the file NoveltyDB.sql, which is used to create the database. Complete the following steps to create the database on SQL Server 2000.

1. Open SQL Server Query Analyzer and log in to the desired SQL Server.
2. Open the NoveltyDB.sql file by first selecting the File menu and then Open and browsing to the location of the file on your computer.
3. Once open, the SQL code is displayed in a window for you to view.
4. Click on the Execute Query item on the toolbar. It is a green arrow to the right of a checkmark icon.
5. The script will execute and create the database. You can populate the database by repeating steps 2-4 and replacing the filename NoveltyDB.sql with any of the SQL files with the word "Data" in the name. The OrdersData.sql file is an example of the included files that will insert data into the database.

Finally, the original 1.0 release of the .NET Framework and Visual Studio.NET did not include the .NET Data Provider for ODBC. It is included in later releases, and you can download it separately from the Microsoft Web site (http://www.microsoft.com) if you need to do so. While there, you can also download the Microsoft .NET Data Provider for Oracle, if you use an Oracle database, although we don't specifically discuss that provider in this book.

Keeping in Touch

The projects, demonstrations, examples, and code used in this book, along with any future changes or additions, can be found at http://www.awprofessional.com/titles/0672323435. E-mails from readers are welcome; contact Jackie Goldstein at Jackie@Renaissance.co.il or webmaster@awprofessional.com.

0672323435P01242003