b l528 bilgisayar programlama ii
Download
Skip this Video
Download Presentation
B İL528 – Bilgisayar Programlama II

Loading in 2 Seconds...

play fullscreen
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
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
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
3 writing the program
3. Writing the Program
  • Adding database file into solution
  • Displaying students
  • Adding new student
  • Changing student info
  • Deleting a student
  • Displaying all courses a student take
summary of the steps
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
slide57
After you build your program, you’ll see some additional project-related objects at the top of the toolbox
what to notice
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!
controls
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
controls1
Controls

Database

Table Adapter

Data Set

Form

Binding Source

the code
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

acquiring data of one student
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
query builder
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
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
  • 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
  • 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.
is it displayed automatically
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
other methods in table adapter object
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
c adding new student

c) Adding New Student

(will be covered in next week)

d changing student info

d) Changing Student Info

(will be covered in next week)

e deleting a student

e) Deleting a Student

(will be covered in next week)

f displaying all courses a student take

f) Displaying All Courses a Student Take

(will be covered in next week)

ad