Visual C# and Databases_

Visual C

#®

And Databases

A Computer Programming Tutorial

By

Philip Conrod & Lou Tylee

©2017 Kidware Software LLC

PO Box 701

Maple Valley, WA 98038

http://www.computerscienceforkids.com

http://www.kidwaresoftware.com

Copyright © 2017 by Kidware Software LLC. All rights reserved

Kidware Software LLC

PO Box 701

Maple Valley, Washington 98038

1.425.413.1185

www.kidwaresoftware.com

www.computerscienceforkids.com

All Rights Reserved. No part of the contents of this book may be reproduced or transmitted in any form or

by any means without the written permission of the publisher.

Printed in the United States of America

ISBN-13: 978-1-937161-66-8 (Electronic)

ISBN-13: 978-1-937161-74-3 (Printed)

Previous edition published as “Visual C# & Databases - 2012 Professional Edition”

Cover Design by Neil Sauvageau

Illustrations by Kevin Brockschmidt

This copy of “Visual C# and Databases” and the associated software is licensed to a single user. Copies of

the course are not to be distributed or provided to any other user. Multiple copy licenses are available for

educational institutions. Please contact Kidware Software for school site license information.

This guide was developed for the course, “Visual C# and Databases,” produced by Kidware Software,

Maple Valley, Washington. It is not intended to be a complete reference to the Visual Basic language.

Please consult the Microsoft website for detailed reference information.

This guide refers to several software and hardware products by their trade names. These references are for

informational purposes only and all trademarks are the property of their respective companies and owners.

Microsoft, Visual Studio, Small Basic, Visual Basic, Visual J#, and Visual C#, IntelliSense, Word, Excel,

MSDN, and Windows are all trademark products of the Microsoft Corporation. Java is a trademark product

of the Oracle Corporation.

The example companies, organizations, products, domain names, e-mail addresses, logos, people, places,

and events depicted are fictitious. No association with any real company, organization, product, domain

name, e-mail address, logo, person, place, or event is intended or should be inferred.

This book expresses the author’s views and opinions. The information in this book is distributed on an "as

is" basis, without and expresses, statutory, or implied warranties.

Neither the author(s) nor Kidware Software LLC shall have any liability to any person or entity with respect

to any loss nor damage caused or alleged to be caused directly or indirectly by the information contained in

this book.

About The Authors Philip Conrod has authored, co-authored and

edited numerous computer programming books for kids, teens and adults. Philip

holds a BS in Computer Information Systems and a Master's certificate in the

Essentials of Business Development from Regis University. He also holds a

Certificate in Programming for Business from Warren-Tech. Philip has been

programming computers since 1977. He has held various Information

Technology leadership roles in companies like Command Plus, BibleBytes

Software, Sundstrand Aerospace, Safeco Insurance Companies, FamilyLife,

Kenworth Truck Company, PACCAR and Darigold. In his spare time, Philip

serves as the President & Publisher of Kidware Software, LLC. He is the proud

father of three “techie” daughters and he and his beautiful family live in Maple

Valley, Washington.

Lou Tylee holds BS and MS degrees in Mechanical Engineering and a PhD in

Electrical Engineering. Lou has been programming computers since 1969 when

he took his first Fortran course in college. He has written software to control

suspensions for high speed ground vehicles, monitor nuclear power plants, lower

noise levels in commercial jetliners, compute takeoff speeds for jetliners, locate

and identify air and ground traffic and to let kids count bunnies, learn how to

spell and do math problems. He has written several online texts teaching Visual

Basic, Visual C# and Java to thousands of people. He taught a beginning Visual

Basic course for over 15 years at a major university. Currently, Lou works as an

engineer at a major Seattle aerospace firm. He is the proud father of five children

and proud husband of his special wife. Lou and his family live in Seattle,

Washington.

Acknowledgements

I want to thank my three wonderful daughters - Stephanie, Jessica and Chloe, who helped with various

aspects of the book publishing process including software testing, book editing, creative design and many

other more tedious tasks like finding errors and typos. I could not have accomplished this without all your

hard work, love and support. I want to also thank my best friend Jesus, who has always been there by my

side giving me wisdom and guidance. Without you, this book would have never been printed or published.

I also want to thank my multi-talented co-author, Lou Tylee, for doing all the real hard work necessary to

develop, test, debug, and keep current all the ‘beginner-friendly’ applications, games and base tutorial text

found in this book. Lou has tirelessly poured his heart and soul into so many previous versions of this

tutorial and there are so many beginners who have benefited from his work over the years. Lou is by far one

of the best application developers and tutorial writers I have ever worked with. Thank you Lou for

collaborating with me on this book project.

Contents

Course Description

Course Prerequisites

How to take the Course

Software Requirements

Hardware Requirements

Installing and Using the Downloadable Solution Files

Installing Visual C# & Databases

Foreword by David B. Taylor, Former College Professor & Dept Chair

1. Introducing Visual C# and Databases

Preview

Course Objectives

Course Requirements

What is a Database?

Where Does Visual C# Fit In?

Building a Visual C# Application

Structure of a Visual C# Application

Steps in Developing Application

Drawing the User Interface and Setting Properties

Setting Properties of Controls at Design Time

Setting Properties at Run-Time

How Names are Used in Control Events

Writing Code

Review of Variables

Visual C# Data Types

Variable Declaration

Example 1-1. Mailing List Application

Summary

2. Introduction to Databases

Review and Preview

Database Structure and Terminology

Relational Databases

Using SQL Server Databases

Sample Relational Database

Sample Database Structure

Virtual Database Tables

Creating a Database

Summary

3. Database Connection

Review and Preview

Data Object Preview

Connection Object

Connection Object – Access Database

Access Databases and 64 Bit Operating Systems

Connection Object – SQL Server Database

Example 3-1. Accessing the Books Database

Command Object

Command Object – Access Database

Command Object – SQL Server Database

Example 3-1 (Command Object). Accessing the Books Database

DataAdapter Object

DataAdapter Object – Access Database

DataAdapter Object – SQL Server Database

DataSet Object

DataTable Object

DataRow Object

Example 3-1 (Data Table). Accessing the Books Database

Data Bound Controls

Example 3-1 (Data Binding). Accessing the Books Database

CurrencyManager Object

Example 3-1 (Final Version). Accessing the Books Database

Data Wizards

Example 3-2 (Access Database). Books Database with Wizards

Example 3-2 (SQL Server Database). Books Database with Wizards

Using SQL Server Databases in Examples

Summary

Example 3-3. Northwinds Trader Database

Example 3-3. Using SQL Server Databases

4. Database Queries with SQL

Review and Preview

SQL Background

Basics of SQL

Where Does SQL Fit In Visual C#?

Example 4-1. SQL Tester

Example 4-1. Using SQL Server Databases

SELECT/FROM SQL Statement

ORDER BY Clause

WHERE Clause

Single Table WHERE Clause

Multiple Table WHERE Clause

INNER JOIN Clause

OUTER JOIN Clause

Functions with SQL (Access Databases)

Functions with SQL (SQL Server Databases)

SQL Aggregate Functions

SQL Construction Tools

SQL Statements with Access

SQL Statements with the Data Wizard

Building SQL Commands in Code

Example 4-2. Searching the Books Database

Example 4-2. Using SQL Server Databases

Summary

Example 4-3. Northwind Traders Database

Example 4-3. Using SQL Server Databases

5. Visual C# Interface Design

Review and Preview

Interface Design Philosophy

Example 5-1. Mailing List Revisited

Visual C# Standard Controls

Form Control

Button Control

Label Control

TextBox Control

CheckBox Control

RadioButton Control

GroupBox Control

Panel Control

PictureBox Control

Example 5-2. Authors Table Input Form

Example 5-2. Using SQL Server Databases

MessageBox Object

Example 5-3. Authors Table Input Form (Message Box)

Example 5-3. Using SQL Server Databases

Application State

Example 5-4. Authors Table Input Form (Application State)

Example 5-4. Using SQL Server Databases

Entry Validation

Key Trapping

Example 5-5. Authors Table Input Form (Entry Validation)

Example 5-5. Using SQL Server Databases

Input Validation

Example 5-6. Authors Table Input Form (Input Validation)

Example 5-6. Using SQL Server Databases

Error Trapping and Handling

Example 5-7. Authors Table Input Form (Error Trapping)

Example 5-7. Using SQL Server Databases

On-Line Help Systems

Creating a Help File

Starting the HTML Help Workshop

Creating Topic Files

Creating Table of Contents File

Compiling the Help File

HelpProvider Control

Example 5-8. Authors Table Input Form (On-Line Help)

Example 5-8. Using SQL Server Databases

Application Testing

Other Controls

MaskedTextBox Control

NumericUpDown Control

TabControl Control

Toolstrip (Toolbar) Control

ListBox Control

ComboBox Control

DataGridVIew Control

MonthCalendar Control

DateTimePicker Control

OpenFileDialog Control

SaveFileDialog Control

Summary

Example 5-9. Publisher Table Input Form

Build Interface

Add Message Box(es)

Code Application State

Perform Entry Validation

Perform Input Validation

Add Error Trapping and Handling

Add On-Line Help System

Application Testing

Example 5-9. Using SQL Server Databases

6. Database Management

Review and Preview

Database Management Tasks

Editing Database Records

Phone Contact Database

Example 6-1. Editing Database Records

Example 6-1. Using SQL Server Databases

Adding Database Records

Example 6-2. Adding Database Records

Example 6-2. Using SQL Server Databases

Deleting Database Records

Example 6-3. Deleting Database Records

Example 6-3. Using SQL Server Databases

Finding Records in a Database

Example 6-4. Finding Database Records

Example 6-4. Using SQL Server Databases

Modifying Records in Code

Example 6-5. Modifying Records in Code

Example 6-5. Using SQL Server Databases

Stopping a Database Application

Example 6-6. Stopping a Database Application

Example 6-6. Using SQL Server Databases

Example 6-7. Authors Table Input Form

Additional Navigation Capabilities

Editing Records

Adding Records

Deleting Records

Stopping the Application