Databases
Bus 92
Roldan
Spring 2008
Note Final Exam Date &
Location!
May 19, 2:45pm to 5:00pm, Eng. 343
Make Up: May 22, 3pm to 5:15pm, BBC 224
Databases
• Allow you to store data for your application
in a file and then retrieve it later
• Have better functionality than Text files
– Allow random access to data – retrieve and
remove data quickly by jumping right to the
data you want (vs. sequential access of text
files)
– Allow you to store different types of data in
complex data structures (More of this in
Business 110A & 112)
Top Level View
SQL – Structured Query Language is used to manipulate data in the database
combined with
VB.Net
Access
ASP.NET
JSP
ETC…..
Display
Database Windows Form
Screen
Web
Access Form/Report
Create a Database in Access
• Part of Microsoft Office Suite, useful for
databases for individual use or Small/Medium
business apps
– Start Microsoft Access and select File>New
– Select Blank Database from the New File Panel
– Browse to the right directory, give your database a
name and then save it
• Note to Office 2007 users
– Make sure you save your file as a Microsoft Access
2003 format (.mdb extension)
– Click on the View button (upper left corner of the
screen) to get to the design window that is similar to
the Office 2003 window shown in the rest of this slide
deck
Design your Database
• Double Click on Create
table in Design View
• Use the design grid to
define your database fields
– Include an ID field (e.g.
productid) and assign it the
autonumber type
– Make the ID field a primary
key by right clicking on the
field name and selecting
Primary Key from the context
menu
• Define other fields and
assign appropriate data
types to each
Save and Populate your DB
• Select File Save to
name and save your
database then close
the design grid
window
• Doubleclick on the
name of the table you
just created in the
Tables panel
• Enter 2-3 lines of data,
close the table window
and exit MS Access
Set up you app to work with the DB
• Create a new project or open your existing
project app
• Select File Save All to save all your app
files into a directory (if you haven’t done so
yet)
• Using Windows Explorer, copy the mdb
file for the access database you just
created into the bin\debug folder in your
application directory
Create a database access form
• Use one textbox
for each field in
your database
• Start Small!
• Next we will see
the code to View,
Add, Delete and
Update the
database items
Step 1. Set form up to work with a
database
Database Name – edit this to
match your database name
Step 2. Add code to view items in
the database
Table Name
One of these
for each field
in your database
The circled areas need to be edited to match your database
Step 3. Add code to insert new
data into the database
SQL: Insert into TableName (Fieldname1, . . . , FieldnameN) Values (‘Value1’, . . . , ‘ValueN’)
The circled area needs to be edited to match your database
Step 4 Add code to Delete Items
SQL: Delete from TableName where Field = ‘Value’
The circled area needs to be edited to match your database
Step 5 – Add code to update items
SQL: Update Table set FieldName1 = ‘Value’ where FieldName2 = ‘Value’
The circled area needs to be edited to match your database
In-Class Work
• Download the testmay1.zip file and unzip it into your flash drive
• Try out the procedure with a product database
– Create a database in Access with 3 fields – productid (autonumber),
productname (text), and productprice (text). Save this table with the name
products
– Save the database as db1.mdb (saved in MSAccess 2003 format)
– Copy the database into the bin\debug folder of the testmay1 app (see slides
4-6)
– Run the testmay1 app to see how the database connection works
• Connect to your first database
– Create an Access database for your app with 2 fields and an id field
(autonumber) and copy it into the bin\debug folder of the testmay1 app (see
slides 4-6)
– Start up the testmay1 solution and edit it to work with your database (see
slides 9-13)
• Build an executable for the testmay1 app and copy the executable for
testmay1 and your database into a folder labeled may6
• If you have time, try out ways to incorporate a database into your app.
Instructions and code for using a database for a loginscreen available in
the loginstuff folder in the course website and bboard