Transcript Database - Department of Computer Engineering

BIM211 – Visual Programming
Database Operations
1
Contents
• Database Structure
– Database, Table, Fields, Primary Key, SQL
Statements, Select, Insert, Update, Delete
• Database Operations with C# Data Objects
– Designing database, Adding database into
solution, Displaying a table, Displaying a single
record
• Database Example
2
Database
• A database is an integrated collection of
logically related records.
• A database contains tables.
• The columns of a table is called fields.
• The rows of a table is called records.
• The records are accessed by SQL commands.
3
Database Applications
• A database application is designed using these
steps:
1. Determine tables and fields
2. Design database
3. Write a program which reads and manipulates
the database
• In this course, we’ll use Microsoft Access to
design databases.
4
Exercise
• Today, we are going to write a simple database
application which displays information about
students and courses that students take.
5
1. Determining Tables
• We need three tables:
– Students
• Contains information about the students, such as ID
number, name, birthday, age, etc.
– Courses
• Contains information about the courses, such as course
code, course name, instructor, etc.
– Enrolment
• Contains information about which student is enrolled to
which course
6
Students Table
• Students table contains these fields:
– ID
– First name
– Last name
– Birthday
– Age
• Notice that these fields are related to a
student only (Remember the structs in C)
7
A Sample Students Table
ID
1
2
3
4
5
First Name
Bahar
Eşref
Ergin
İrfan
Selen
Last Name
Çağlar
Pazar
Karadağ
Karazor
Darat
Birthday
01.02.1992
03.04.1993
05.06.1994
07.08.1995
09.10.1996
Age
17
16
15
14
13
8
Courses Table
• Courses table contains these fields:
– Course code
– Course name
– Instructor
• Adding an ID field will make the database
operations simpler:
– Course ID
9
A Sample Courses Table
Course ID Course Code Course Name
1
BIM111
2
BIM211
3
4
BIM201
BIM309
5
BIM213
Instructor
Introduction to
Computer
Muzaffer DOĞAN
Engineering
Visual Programming Muzaffer DOĞAN
System Software
Cüneyt AKINLAR
Artificial Intelligence Sedat TELÇEKEN
Data Structures and
Cüneyt AKINLAR
Algorithms
10
Enrolment Table
• Enrolment table contains information about
the courses taken by each students.
• It has two fields:
– Student ID
– Course ID
• For example, if a record with Student ID = 1
and Course ID = 2, then it means that the
student with ID = 1 (Bahar Çağlar) takes the
course with ID = 2 (Visual Programming)
11
A Sample Enrolment Table
Student ID
Course ID
1
2
1
3
2
1
2
2
2
4
3
2
3
5
4
1
4
3
5
2
5
5
12
Primary Key
• Primary key is the field which uniquely
identifies each row in a table
• A primary key comprises a single column or a
set of columns
• ID field in Students table and Course ID field
in Courses table are primary keys
• Both Student ID and Course ID fields in
Enrolment table must be primary keys
• Always create primary keys for tables.
13
2. Designing Database
• The database design steps are explained in
detail in the next slides
• The basic steps are
– Open Microsoft Access
– Create a database file of format Office 2002-2003
with the extension *.mdb
– Create tables in design view
– Enter records
– Close MS Access
14
Open MS Access
15
Click Office Button and Select New
16
On the right pane, click Browse
button
17
Select a folder, a file name, and Office 20022003 format with extension *.mdb
18
Click Create Button
19
Right-click Table1 on left-pane and
select Design View
20
Give a name to the table and click OK
button
21
Write StudentID into the first field
22
Notice the yellow “Primary Key” icon
on the left
23
Write “FirstName” into second field
and set “Text Size” as 30
24
Write “LastName” into third field and
set its “Text Size” as 30
25
Write “BirthDay” to fourth field and
set its type as “Date/Time”
26
Write “Age” into fifth field, set its type as
“Number” and select “Integer” as its size
27
Close Design View of Students table
28
Click Yes to save the changes
29
Click “Create” tab and select “Table”
30
Right-click Table1 and select Design
View
31
Write the name “Courses” and click OK
32
Fill the field names and set their types
and sizes. Click Close button.
33
Click Yes to save the changes
34
Give the command Create - Table
35
Right-click Table1 and select Design
View
36
Write Enrolment as the table name
and click OK
37
“StudentID”, Number, Long Integer
38
“CourseID”, Number, Long Integer
39
Using mouse, select both StudentID
and CourseID fields
40
Click “Primary Key” button and notice
that both fields become primary keys
41
Close the Design View of Enrolment
table
42
Click Yes to save the changes
43
Double-click Students table
44
Fill the records (don’t write anything into
StudentID field, it is automatically filled)
45
Double-click Courses table and enter
the records
46
Double-click Enrolment table and
enter the records
47
Close the MS Access window
48
3. Writing the Program
a)
b)
c)
d)
e)
f)
Adding database file into solution
Displaying students
Adding new student
Changing student info
Deleting a student
Displaying all courses a student take
49
a) Adding Database File into
Solution
50
Summary of the Steps
• Create a new project
• Copy the database file into the solution folder
• Add the database file into the solution in
Visual Studio 2008
51
Copy database file into solution folder
52
Right-click project name, click Add,
click Existing Item…
53
Filter Data Files, Select the database
file and click Add
54
Select all tables and click Finish
55
Database file and automatically generated files
are displayed in Solution Explorer
56
After you build your program, you’ll
see some additional project-related
objects at the top of the toolbox
57
b) Displaying Students
58
The data-related objects are located
under the “Data” menu of the
toolbox:
59
Drag & Drop a DataGridView object
60
Choose Students as the Data Source
61
Execute the Program
62
What to Notice?
• Notice that...
– We didn’t write any code!
– When we select Data Source, some controls are
added to the form automatically by Visual Studio!
– Some codes are added into Load event handler of
the form automatically by Visual Studio!
63
Which controls are added?
64
Which code is added?
65
Controls
• studentsTableAdapter
– The interface between program and database
• schoolDataSet
– All data is written into this dataset
• studentsBindingSource
– Binds data to the controls (e.g. DataGridView) on
the form
66
Controls
Database
Table Adapter
Data Set
Form
Binding Source
67
The Code
this.studentsTableAdapter.Fill(this.schoolDataSet.Students);
Controls added into
solution automatically
by Visual Studio
The table which is used to
store query results from
Students table of the database
The method which
acquires data from
database
68
Acquiring Data of One Student
• All data acquirement operations are done by
TableAdapter object
• Fill method is the method which brings all
data from the table
• If you want to get only one student’s data, you
need to create a new query in the
TableAdapter object
69
Click on the triangle on table adapter
object and select Add Query command
70
Write FillByStudentID as query name
and click Query Builder button
71
Go to the Filter column of StudentID
row and write “=?” and click OK
72
Query Builder
• All data are acquired from database via SQL
commands
• Query builder makes writing SQL commands
easier
• By clicking Execute button, you can display the
result of your query
• Notice that the SQL command is changed after
you write “=?” into the Filter column
• Here, “?” represents a parameter for the query
73
Click OK to return to Visual Studio
74
A ToolStrip is automatically added
into the form
75
A new code which uses the new
FillByStudentID method is added into the codes
76
Run the program, write a number into
StudentID box, and click FillByStudentID button
Only the student
with the selected
ID is displayed
77
About the ToolStrip
• Generally, the ToolStrip which is added by
Visual Studio is not wanted
• You can simply delete the ToolStrip and write
your own code
• Click on the ToolStrip icon below the form
(near other automatically added controls) and
press Delete button from the keyboard
78
Displaying One Student’s Info by Code
• Place a button on the form and write the
following code in its Click event handler:
this.studentsTableAdapter.FillByStudentID(
this.schoolDataSet.Students, 3);
• In the code above, the Students table in the
data set is filled by the FillByStudentID
method of the table adapter with the data of
the student with ID 3.
79
Execution of the Program:
80
When “One Student” button is
clicked:
81
Is it displayed automatically?
• Notice that we only filled the data set and we
didn’t write any code to display new data in
DataGridView object
• So, how was data displayed in DataGridView?
• This task is accomplished by the Binding
Source object.
• So, you just need to bring and write data into
the data set and it is automatically displayed
in the controls on the form
82
Other Methods in Table Adapter
Object
• The studentsTableAdapter control has some
useful methods:
– Fill() : Get data from database
– Insert() : Add a new record to the database
– Update() : Change an existing record
– Delete() : Delete an existing record
• These methods correspond to the SQL
commands of types Select, Insert, Update,
and Delete respectively
83
SQL Command Types and
Corresponding Methods
SQL Command Type
SELECT
INSERT
UPDATE
DELETE
Table Adapter Method
Fill()
FillByStudentID()
FillByStudentName()
etc.
Insert()
Update()
Delete()
84
c) Adding New Student
(will be covered in next week)
85
d) Changing Student Info
(will be covered in next week)
86
e) Deleting a Student
(will be covered in next week)
87
f) Displaying All Courses a
Student Take
(will be covered in next week)
88