Visual C# and Databases
Table of Contents
1. Introducing Visual C# and Databases
Preview 1-1
Course Objectives 1-2
Course Requirements 1-3
What is a Database? 1-4
Where Does Visual C# Fit In? 1-6
Building a Visual C# Application 1-8
Structure of a Visual C# Application 1-9
Steps in Developing Application 1-10
Drawing the User Interface and Setting Properties 1-11
Setting Properties of Controls at Design Time 1-17
Setting Properties at Run-Time 1-19
How Names are Used in Object Events 1-20
Writing Code 1-21
Review of Variables 1-23
Visual C# Data Types 1-24
Variable Declaration 1-25
Example 1-1. Mailing List Application 1-27
Summary 1-36
2. Introduction to Databases
Review and Preview 2-1
Database Structure and Terminology 2-2
Relational Databases 2-4
Using SQL Server Databases 2-6
Sample Relational Database 2-7
Sample Database Structure 2-10
Virtual Database Tables 2-12
Creating a Database 2-15
Summary 2-16
3. Database Connection
Review and Preview 3-1
Data Object Preview 3-2
Connection Object 3-3
Connection Object – Access Database 3-4
Connection Object – SQL Server Database 3-5
Example 3-1. Accessing the Books Database 3-6
Command Object 3-12
Example 3-1 (Command Object). Accessing the Books Database 3-14
DataAdapter Object 3-17
DataAdapter Object – Access Database 3-18
DataAdapter Object – SQL Server Database 3-19
DataSet Object 3-20
DataTable Object 3-21
DataRow Object 3-22
Example 3-1 (Data Table). Accessing the Books Database 3-23
Data Bound Controls 3-26
Example 3-1 (Data Binding). Accessing the Books Database 3-28
CurrencyManager Object 3-34
Example 3-1 (Final Version). Accessing the Books Database 3-36
Note to Visual C# 2008 Users 3-42
Data Wizards 3-43
Example 3-2 (Access Database). Books Database with Wizards 3-44
Example 3-2 (SQL Server Database). Books Database with Wizards 3-55
Using SQL Server Databases in Examples 3-67
Summary 3-68
Example 3-3. Northwinds Trader Database 3-69
Example 3-3. Using SQL Server Databases 3-74
4. Database Queries with SQL
Review and Preview 4-1
SQL Background 4-2
Basics of SQL 4-3
Where Does SQL Fit In Visual C#? 4-5
Example 4-1. SQL Tester 4-6
Example 4-1. Using SQL Server Databases 4-13
SELECT/FROM SQL Statement 4-14
ORDER BY Clause 4-18
WHERE Clause 4-20
Single Table WHERE Clause 4-21
Multiple Table WHERE Clause 4-27
INNER JOIN Clause 4-32
OUTER JOIN Clause 4-36
Functions with SQL (Access Databases) 4-38
Functions with SQL (SQL Server Databases) 4-40
SQL Aggregate Functions 4-42
SQL Construction Tools 4-46
SQL Statements with Access 4-47
SQL Statements with the Data Wizard 4-49
Building SQL Commands in Code 4-55
Example 4-2. Searching the Books Database 4-56
Example 4-2. Using SQL Server Databases 4-63
Summary 4-64
Example 4-3. Northwind Traders Database 4-65
Example 4-3. Using SQL Server Databases 4-67
5. Visual C# Interface Design
Review and Preview 5-1
Interface Design Philosophy 5-2
Example 5-1. Mailing List Revisited 5-4
Visual C# Standard Controls 5-5
Form Control 5-6
Button Control 5-8
Label Control 5-9
TextBox Control 5-11
CheckBox Control 5-13
RadioButton Control 5-15
GroupBox Control 5-17
Panel Control 5-18
PictureBox Control 5-19
Example 5-2. Authors Table Input Form 5-20
Example 5-2. Using SQL Server Databases 5-27
MessageBox Object 5-28
Example 5-3. Authors Table Input Form (Message Box) 5-33
Example 5-3. Using SQL Server Databases 5-35
Application State 5-36
Example 5-4. Authors Table Input Form (Application State) 5-38
Example 5-4. Using SQL Server Databases 5-43
Entry Validation 5-44
Key Trapping 5-45
Example 5-5. Authors Table Input Form (Entry Validation) 5-47
Example 5-5. Using SQL Server Databases 5-48
Input Validation 5-49
Example 5-6. Authors Table Input Form (Input Validation) 5-51
Example 5-6. Using SQL Server Databases 5-55
Error Trapping and Handling 5-56
Example 5-7. Authors Table Input Form (Error Trapping) 5-61
Example 5-7. Using SQL Server Databases 5-64
On-Line Help Systems 5-65
Creating a Help File 5-66
Starting the HTML Help Workshop 5-68
Creating Topic Files 5-70
Creating Table of Contents File 5-73
Compiling the Help File 5-78
HelpProvider Control 5-81
Example 5-8. Authors Table Input Form (On-Line Help) 5-83
Example 5-8. Using SQL Server Databases 5-86
Application Testing 5-87
Other Controls 5-88
MaskedTextBox Control 5-89
NumericUpDown Control 5-91
TabControl Control 5-93
Toolstrip (Toolbar) Control 5-96
ListBox Control 5-101
ComboBox Control 5-105
DataGridVIew Control 5-106
MonthCalendar Control 5-108
DateTimePicker Control 5-110
OpenFileDialog Control 5-111
SaveFileDialog Control 5-114
Summary 5-117
Example 5-9. Publisher Table Input Form 5-118
Build Interface 5-119
Add Message Box(es) 5-127
Code Application State 5-128
Perform Entry Validation 5-131
Perform Input Validation 5-132
Add Error Trapping and Handling 5-134
Add On-Line Help System 5-135
Application Testing 5-138
Example 5-9. Using SQL Server Databases 5-139
6. Database Management
Review and Preview 6-1
Database Management Tasks 6-2
Editing Database Records 6-3
Phone Contact Database 6-5
Example 6-1. Editing Database Records 6-6
Example 6-1. Using SQL Server Databases 6-8
Adding Database Records 6-19
Example 6-2. Adding Database Records 6-21
Example 6-2. Using SQL Server Databases 6-27
Deleting Database Records 6-28
Example 6-3. Deleting Database Records 6-29
Example 6-3. Using SQL Server Databases 6-31
Finding Records in a Database 6-32
Example 6-4. Finding Database Records 6-35
Example 6-4. Using SQL Server Databases 6-36
Modifying Records in Code 6-37
Example 6-5. Modifying Records in Code 6-39
Example 6-5. Using SQL Server Databases 6-41
Stopping a Database Application 6-42
Example 6-6. Stopping a Database Application 6-44
Example 6-6. Using SQL Server Databases 6-47
Example 6-7. Authors Table Input Form 6-48
Additional Navigation Capabilities 6-49
Editing Records 6-51
Adding Records 6-53
Deleting Records 6-55
Stopping the Application 6-57
Example 6-7. Using SQL Server Databases 6-59
Example 6-8. Publishers Table Input Form 6-60
Additional Navigation Capabilities 6-61
Editing Records 6-64
Adding Records 6-66
Deleting Records 6-68
Stopping the Application 6-69
Example 6-8. Using SQL Server Databases 6-71
Multiple Table Database Management 6-72
Database Keys 6-73
Database Modifications 6-74
Final Application 6-75
Example 6-9. Books Database Management System 6-76
Basic Book Titles Input Form 6-77
Finding Records 6-90
Navigation Information 6-94
Adding Publisher Name 6-96
Adding Publisher Editing 6-103
Modify Publishers Input Form 6-108
Modify Authors Input Form 6-114
Adding Author Names 6-120
Example 6-10. Database Detective – Author Search 6-121
Example 6-10. Using SQL Server Databases 6-125
Viewing Author Selections 6-126
Viewing Author Names 6-135
Saving Author Names 6-139
Adding Author Editing 6-142
Input Control Navigation 6-145
Entry and Input Validation 6-148
Titles Form On-Line Help 6-156
Example 6-9. Using SQL Server Databases 6-160
Summary 6-161
7. Database Reports
Review and Preview 7-1
PrintDocument Object 7-2
Printing Document Pages 7-5
Pen Object 7-7
Brush Object 7-8
Graphics Methods 7-9
PageSetupDialog Control 7-12
PrintDialog Control 7-15
PrintPreviewDialog Control 7-17
PrintDocument Object with Databases 7-19
Example 7-1. Database Report 7-21
Example 7-1. Using SQL Server Databases 7-28
Example 7-2. Titles Listing 7-29
Example 7-2. Using SQL Server Databases 7-35
Example 7-3. Book Publishers Listing 7-36
User Interface 7-37
Database Connection and Printing 7-40
Example 7-3. Using SQL Server Databases 7-47
Other Approaches to Database Reports 7-48
Summary 7-49
8. Distributing a Database Application
Review and Preview 8-1
Accessing Database Files in Code 8-2
Database File in Application Path 8-3
Example 8-1. Opening Database Files in Application Directory 8-4
Example 8-1. Using SQL Server Databases 8-7
Database File Location with OpenFile Dialog Control 8-8
Example 8-2. Opening Database Files with OpenFile Dialog Control 8-10
Example 8-2. Using SQL Server Databases 8-15
Distribution of a Visual C# Database Application 8-16
Applications Icons 8-20
Designing Your Own Icon 8-22
Example 8-3. Visual C# Setup Wizard 8-25
Step 1. Welcome to the Setup Project Wizard 8-28
Step 2. Choose a project type 8-29
Step 3. Choose project outputs to include 8-30
Step 4. Choose files to include 8-31
Step 5. Create project 8-34
Assigning Icons and Shortcuts 8-37
Debug Versus Release Configurations 8-40
Building the Setup Program 8-41
Installing/Removing a Visual C# Application 8-43
Summary 8-46
9. Database Design Considerations
Review and Preview 9-1
Database Design 9-2
Database Modeling 9-3
Information Requirements 9-4
Table Requirements 9-6
Field Requirements 9-10
Field Types 9-12
Null Values 9-14
Database Design Implementation 9-15
Building Databases with the Microsoft Access 9-16
Example 9-1. KWSALES Database with Microsoft Access 9-17
Getting Started 9-18
Customers Table 9-19
Orders Table 9-22
Purchases Table 9-23
Products Table 9-25
Define Relationships 9-26
Building SQL Server Databases with Server Explorer 9-29
Example 9-2. KWSALES Database with Server Explorer 9-30
Getting Started 9-31
Customers Table 9-33
Orders Table 9-37
Purchases Table 9-38
Products Table 9-39
Define Relationships 9-40
Building Access Databases with Visual C# 9-44
Example 9-3. KWSALES Database with Visual C# 9-45
Adding Reference to ADOX Library 9-46
Create a Database 9-48
Create a Table 9-50
Add Fields to Table 9-52
Define Table Primary Key 9-56
Define Table Indexes 9-58
Define Table Relationships 9-63
Example 9-4. SQL Server Databases with Visual C# 9-66
Database Testing and Design Refinement 9-70
Summary 9-71
10. Sample Database Projects
Review and Preview 10-1
Overview of Database Projects 10-2
Example 10-1. Sales Order Form Project 10-3
Preliminaries 10-4
Order Information 10-5
Existing Customer Information 10-11
Adding a New Customer 10-20
Product Selection 10-30
Submitting an Order 10-39
Printing an Invoice 10-46
Suggested Improvements 10-51
Example 10-1. Using SQL Server Databases 10-52
Example 10-2. Home Inventory Project 10-52
Home Inventory Database 10-54
Preliminaries 10-56
Home Inventory Interface 10-57
Database Connection 10-61
Display Photo 10-65
Database Navigation 10-67
Editing Records 10-70
Load Photo 10-75
Adding Records 10-78
Deleting Records 10-84
Entry Validation 10-86
Input Validation 10-90
Inventory Report 10-92
Stopping the Application 10-97
Suggested Improvements 10-100
Example 10-2. Using SQL Server Databases 10-101
Example 10-3. Weather Monitor Project 10-103
Weather Monitor Interface 10-104
Record Weather Data Tab 10-106
Weather Monitor Database 10-109
Database Fields 10-113
Adding Date Values and Editing Features 10-116
Opening Database Files 10-123
Date Display Coordination 10-125
View Temperature Data Tab 10-127
Temperature Summary Statistics 10-134
Temperature Plot 10-140
View Precipitation Data Tab 10-142
Precipitation Summary Statistics 10-146
Precipitation Plot 10-151
Weather Monitor Printed Reports 10-153
Weather Data Report 10-155
Temperature Data Report 10-158
Precipitation Data Report 10-160
Weather Monitor Help System 10-162
Weather Monitor Icon 10-165
Weather Monitor Distribution Package 10-166
Suggested Improvements 10-167
Example 10-3. Using SQL Server Databases 10-168
Summary 10-171
11. Other Database Topics
Review and Preview 11-1
Exporting Database Data 11-2
Opening a Sequential File for Output 11-3
Writing Data to a Sequential File 11-4
Saving a Sequential File 11-5
Example 11-1. Exporting Database Data 11-6
Importing Database Data 11-10
Opening a Sequential File for Input 11-11
Reading Data from a Sequential File 11-12
Closing a Sequential File 11-14
Example 11-2. Importing Database Data 11-15
Other Database Types 11-21
ODBC Data Objects 11-22
Oracle Data Objects 11-23
Multi-User Considerations 11-24
Database Web Applications 11-25
Starting a New Web Application 11-26
Web Form Controls 11-29
Building a Web Application 11-36
Example 11-3. Viewing Weather Data 11-39
Summary 11-42
Example 11-4. The Final Application 11-43
