Visual Basic.Net Database Programming for Dummies

Contents

Introduction

About This Book

Conventions Used in This Book

What You're Not to Read

Foolish Assumptions

How This Book Is Organized

Part I: The Basics of Databases

Part II: Making a Connection

Part III: Contacting the User

Part IV: Building a Database

Part V: The Internet Connection

Part VI: Hands-On Programming

Part VII: Working with Queries

Part VIII: The Part of Tens

Icons Used in This Book

Where to Go from Here

Chapter 1: The Big Picture

Servers and Their Clients

ADO.NET: It's about Scalability

The Big Three: Connection, Adapter, and DataSet Objects

Getting Results in Seven Easy Steps

Chapter 2: Databases 101: How Databases Work

Understanding Rows, Columns, Tables, and All the Rest

Rows are filled with information

Columns are labels

Tables are made up of columns

Why use multiple tables?

Tangled relationships: Using unique data to tie tables together

Let the database do it for you: AutoNumber columns

Why not just make a list?

Indexes -- a Key to Success

Imagine nonalphabetic yellow pages

Hey, let's index every column!

Building Your First DataSet

Creating a DataSet object

Adding a table to a DataSet

Viewing a DataSet's Code

Verbosity

Overloads, overrides, yadda yadda

Behind the scenes of a region

Chapter 3: Getting Connected with Data Controls

Generating DataSets the Easy Way

Connecting to a Database

Connecting to SQL Server

Why use Internet Information Services and virtual directories?

Using SqlDataAdapter

Seeing Your DataSet

Creating an SQL Query

Creating a DataSet

Binding Controls to a DataSet

Viewing a DataSet's Properties

One Final Trick

Chapter 4: Exploring the Data Form Wizard and Other Assistants

Using the Data Form Wizard

Displaying an alternate form

Setting the Startup object

Modifying a DataSet

The Single-Row DataForm Style

A Brief Tour of Some Interesting Code

The Count property

The ToString method

The Position property of the BindingContext object

Deleting a row

Adding a row

Working with Stored Procedures

Creating a stored procedure

Using Enterprise Manager

Working with Server Explorer

Displaying Server Explorer

Adding or removing servers

Chapter 5: A Collection of Connections

The Little Engine That Could

The Main Connections

Using a connection control

Using Server Explorer

Accessing Jet Databases

Seeing Some Data 91

Creating Data Connections in Control Panel

Chapter 6: Simple ASP.NET Data Binding and Web Controls

Creating Your First WebForm

Binding to get data from a control

Binding to an ArrayList

Binding to the Results of an Evaluated Expression

Binding in WinForms 105

Binding to a WinForm ComboBox

Adding properties in a WinForm

Understanding Server Controls

The Style Builder Dialog Box

Chapter 7: Designing a User Interface

Using the Classic Components

Attaching a Data-Aware Control

Creating a Jobs DataSet

Binding multiple controls and properties to a DataSet

Binding to other properties

Moving through the DataSet's Rows

A Few More Words about the DataSet

The Splendid DataGrid

Working with Other Classic Controls

How about CheckBoxes?

Using Labels for read-only displays

Understanding ListBox limitations

Exploring Specialized, Data-Related Controls

Chapter 8: Creating a DataSet

More about DataSets

Building Your First DataSet

Some global variables and namespaces

Building a DataSet in code

Playing around

Creating a Menu

Chapter 9: Managing DataSets

Creating a DataSet

Understanding Collections

Opening an Existing DataSet

Adding and Removing Data

Adding data to a DataSet

Removing data from a DataSet

Saving a DataSet

Testing . . . Testing

Moving through the DataSet

Searching a DataSet

Selecting a Search Hit

Chapter 10: Validating and Indexing Your Data

Validating User Input at the Source

Using Validation Controls

Validating programmatically

Validating a zip code

Indexing Your Data

Why not index every field?

The special primary index Part V: The Internet Connection

Chapter 11: Translating Windows Applications to WebForms

Programming a DataSet in a WebForm

Detecting Postback

The Rich Data Controls

The ASP.NET DataGrid

Manipulating the DataGrid programmatically

Using a template

The DataList control

HTML Controls versus WebControls

The WebControl Collection

Buttons for submission

Labeling

Making text interactive

Using CheckBoxes and RadioButtons

Displaying lists

Chapter 12: Active Server Pages: Mixing HTML with VB

Setting Up ASP.NET

Follow these steps

Creating an ASP.NET test directory

Writing Your First ASP Script

Writing a standard (not ASP) script

Writing the script in ASP

Chapter 13: ASP.NET: Mixing HTML, XML, and VB .NET

Verifying Your Virtual Server

Testing the program

If things are not working

Taking Your ASP.NET Program for a Spin

Dealing with firewalls

Ensuring proper support

Understanding ASP.NET Source Code

Looking at the browser-side code

Using ID to access controls

Preserving information

New Visual Basic .NET techniques

Try to be flexible

A Peek Beneath

Chapter 14: Migrating to ADO.NET

The Data Set and the DataSet

Moving from ADO to ADO.NET

Why ADO.NET?

The ADO.NET Managed Provider

Creating a DataSet Programmatically

If you have problems

Understanding the code

The DataSet

Beyond Disconnected Recordsets

Translating VB 6 and ADO to VB .NET and ADO.NET

Finding syntax the easy way

Chapter 15: Acronym Soup: DAO, XML, ODBC, ADO, ADO.NET

Getting a Handle on Database Technologies

Understanding Open Database Connectivity (ODBC)

Working with Data Access Objects (DAO)

Understanding ADO (ActiveX Data Objects)

Chapter 16: More about ADO.NET

Some Advantages of ADO.NET

Working with the DataSet Object

Collections within collections

Substituting names (mapping)

Read-Only Sequential

Optimism versus pessimism

Comparing versions with optimistic concurrency

Building optimistic concurrency parameters

A Brief Look at XML

Using XML Designer

Creating types

Adding a complex type

The parser identifies problems

Building relational tables

Adding an element

Defining a relation

Generating your DataSet

Dropping a table into XML Designer

Using Data view

Chapter 17: Killing Bugs

What's a Poor Debugger to Do?

Using VB .NET's Excellent Facilities

Start by finding out where the error happened

Making Use of the Try-Catch-End Try Structure

How runtime errors occur

Understanding Try

The official syntax

Understanding Finally

Throwing exceptions

Tracking Down Logic Errors

The voyeur technique

Using Debug.WriteLine

The Command window responds

The watch technique

Setting breakpoints

Setting conditional breakpoints

Global watches missing

Alternative Debugging Strategies

Step Over (Shift+F10)

Step Out (Ctrl+Shift+F8)

Run to Cursor (Ctrl+F10)

Set Next Statement (Ctrl+Shift+F10)

Show Next Statement

The Call Stack

On Error

Chapter 18: Automatic SQL: Using Query Builder

Using Query Builder

Building an SQL query

The four window panes

Using ORDER BY to Sort Rows

Choosing from 73.2 Million Possible Results, Give or Take a Few

From Separate Tables: Doing a Join

Dynamic SQL

Chapter 19: A Brief Dictionary of SQL

SELECT: The Main SQL Clause to Retrieve Data

WHERE: Narrowing the Field

BETWEEN: Specifying a Range

LIKE: Using a Pattern Match

ORDER BY: Sorting the Results

TOP: Limiting a Range

Using a JOIN: Getting Data from More than One Table at a Time

AS: Renaming Columns (Aliasing)

DISTINCT: Eliminating Duplicates

COUNT, SUM, AVG, MAX, and MIN: Calculating with Aggregate Functions

GROUP BY: Summarizing

HAVING: Narrowing Criteria

SQL Action Queries: Changing a Database

DELETE: Removing rows

Additional action queries

Chapter 20: Ten Great VB .NET Tips

Using the Upgrade Wizard as a Teaching Tool

Talking to the Clipboard

Managing Directories

Randomizing

CStr versus .ToString

Making Quick Layout Changes

Understanding How the Registry Works with VB .NET

Reading from the Registry

Writing to the Registry

The Wizard of Wizards

Simplifying Source Code in Two Ways

Combining the declaration and the assignment

Avoiding repetition

Chapter 21: Ten Important Topics that Don't Fit Elsewhere

Reading the Latest News

Getting Answers to VB .NET Questions

Keeping Visual Basic Healthy

Discovering Microsoft's Plans for the Future

of Database Technology

Visiting Other Web Sites of Interest

Creating Macros

Printing Will Never Be the Same Again

Using the PrintPreview Control

Attending Technical Conferences

No More Null or IsNull Index