B l528 bilgisayar programlama ii
Download
1 / 88

B İL528 – Bilgisayar Programlama II - PowerPoint PPT Presentation


  • 98 Views
  • Uploaded on

B İL528 – Bilgisayar Programlama II. Database Operations. Contents. Database Structure Database, Table, Fields, Primary Key, SQL Statements, Select, Insert, Update, Delete Database Operations with C# Data Objects

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' B İL528 – Bilgisayar Programlama II' - nova


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
B l528 bilgisayar programlama ii

BİL528 – Bilgisayar Programlama II

Database Operations


Contents
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


Database
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.


Database applications
Database Applications

  • A database application is designed using these steps:

    • Determine tables and fields

    • Design database

    • Write a program which reads and manipulates the database

  • In this course, we’ll use Microsoft Access to design databases.


Exercise
Exercise

  • Today, we are going to write a simple database application which displays information about students and courses that students take.


1 determining tables
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


Students table
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)



Courses table
Courses Table

  • Courses table contains these fields:

    • Course code

    • Course name

    • Instructor

  • Adding an ID field will make the database operations simpler:

    • Course ID



Enrolment table
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)



Primary key
Primary Key

  • Primary key is the field which uniquely identifies each row in a table

  • A primary key comprises a single column or aset 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.


2 designing database
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






Click create button
Click Create Button with extension *.mdb






Write firstname into second field and set text size as 30
Write “ with extension *.mdbFirstName” into second field and set “Text Size” as 30


Write lastname into third field and set its text size as 30
Write “ with extension *.mdbLastName” into third field and set its “Text Size” as 30


Write birthday to fourth field and set its type as date time
Write “ with extension *.mdbBirthDay” to fourth field and set its type as “Date/Time”


Write age into fifth field set its type as number and select integer as its size
Write “Age” into fifth field, set its type as “Number” and select “Integer” as its size


Close design view of students table
Close Design View of Students table “Number” and select “Integer” as its size


Click yes to save the changes
Click Yes to save the changes “Number” and select “Integer” as its size


Click create tab and select table
Click “Create” tab and select “Table” “Number” and select “Integer” as its size


Right click table1 and select design view
Right-click Table1 and select Design View “Number” and select “Integer” as its size


Write the name courses and click ok
Write the name “Courses” and click OK “Number” and select “Integer” as its size







Studentid number long integer
Close button.StudentID”, Number, Long Integer



Using mouse select both studentid and courseid fields
Using mouse, select both Close button.StudentID and CourseID fields




Click yes to save the changes2
Click Yes to save the changes become primary keys


Double click students table
Double-click Students table become primary keys



Double click courses table and enter the records
Double-click Courses table and enter the records field, it is automatically filled)


Double click enrolment table and enter the records
Double-click Enrolment table and enter the records field, it is automatically filled)


Close the ms access window
Close the MS Access window field, it is automatically filled)


3 writing the program
3. Writing the Program field, it is automatically filled)

  • Adding database file into solution

  • Displaying students

  • Adding new student

  • Changing student info

  • Deleting a student

  • Displaying all courses a student take


A adding database file into solution

a) Adding Database File into Solution field, it is automatically filled)


Summary of the steps
Summary of the Steps field, it is automatically filled)

  • Create a new project

  • Copy the database file into the solution folder

  • Add the database file into the solution in Visual Studio 2008


Copy database file into solution folder
Copy database file into solution folder field, it is automatically filled)




Select all tables and click finish
Select all tables and click Finish field, it is automatically filled)



After you build your program, you’ll see some additional project-related objects at the top of the toolbox


B displaying students

b) Displaying Students project-related objects at the top of the toolbox



Drag drop a datagridview object
Drag & Drop a menu of the toolbox:DataGridView object



Execute the program
Execute the Program menu of the toolbox:


What to notice
What to Notice? menu of the toolbox:

  • 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!


Which controls are added
Which controls are added? menu of the toolbox:


Which code is added
Which code is added? menu of the toolbox:


Controls
Controls menu of the toolbox:

  • 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


Controls1
Controls menu of the toolbox:

Database

Table Adapter

Data Set

Form

Binding Source


The code
The Code menu of the toolbox:

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


Acquiring data of one student
Acquiring Data of One Student menu of the toolbox:

  • 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



Write fillbystudentid as query name and click query builder button
Write Query commandFillByStudentID as query name and click Query Builder button


Go to the filter column of studentid row and write and click ok
Go to the Filter column of Query commandStudentID row and write “=?” and click OK


Query builder
Query Builder Query command

  • 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



A toolstrip is automatically added into the form
A Query commandToolStrip is automatically added into the form



Run the program write a number into studentid box and click fillbystudentid button
Run the program, write a number into StudentID box, and click FillByStudentID button

Only the student with the selected ID is displayed


About the toolstrip
About the ToolStrip click FillByStudentID button

  • 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


Displaying one student s info by code
Displaying One Student’s Info by Code click FillByStudentID button

  • 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.


Execution of the program
Execution of the Program: click FillByStudentID button


When one student button is clicked
When “One Student” button is clicked: click FillByStudentID button


Is it displayed automatically
Is it displayed automatically? click FillByStudentID button

  • 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


Other methods in table adapter object
Other Methods in Table Adapter Object click FillByStudentID button

  • 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


Sql command types and corresponding methods
SQL Command Types and Corresponding Methods click FillByStudentID button


C adding new student

c) Adding New Student click FillByStudentID button

(will be covered in next week)


D changing student info

d) Changing Student Info click FillByStudentID button

(will be covered in next week)


E deleting a student

e) Deleting a Student click FillByStudentID button

(will be covered in next week)


F displaying all courses a student take

f) Displaying All Courses a Student Take click FillByStudentID button

(will be covered in next week)


ad