Transcript Database

CIS162AD
Databases
14_databases.ppt
Overview of Topics
 Relational Database Terminology
 ADO.Net
 Instructions to complete CS13
 Connecting to a Database
– Connection
– Data Adapter
– DataSet
 Binding data
 DataGridView
 Label and Textbox View
CIS162AD
2
Relational Databases
 Most data is now stored in relational database




management systems (DBMS or RDBMS).
There are various vendors to choose form:
Oracle, MS SqlServer, Sybase, IBM DB2, etc.
Visual Studio is usually used to develop applications
that store data in Microsoft SqlServer.
In this class we will use MS Access, due to
SqlServer’s installation requirements.
Those of you familiar with Access know that it has a
built-in form and report designer. However, keep in
mind that we would normally be accessing a
database stored in SqlServer, which does not have
built-in tools.
CIS162AD
3
Database Terminology
 A database is made up of one or more related tables.
 Conceptually a table is made up of rows and columns






(2D Array).
Each row represents the data for one record
(a person, inventory item, course information).
Each column (field) is a data element
(name, address, city, state, zip).
Tables have a Primary Key Field to uniquely identify each record
(Id number, part number, account number).
Relationships between various tables can be defined.
A DBMS stores everything (tables, columns, relationships, etc.)
about the database in system tables.
System and data tables are usually stored in one file (CSMail.mbd).
CIS162AD
4
SQL – Structured Query Language
 SQL is pronounced sequel.
 SQL – Structured Query Language – Basic set of
commands that are common in all DBMS.
 DBMS vendors may add additional commands.
 SQL commands are usually processed against a record set.
– Select name, address From customer where zip = “85202”;
– Delete From customer where zip = “85202”
All rows matching the criteria would be selected or deleted.
– Record set commands are very powerful.
 We’ll usually want to qualified a command by specifying
a customer id or other unique identifier using the Where
clause.
CIS162AD
5
An Access Table
Columns
Row
CIS162AD
6
C# Application Reading Database
CIS162AD
7
Accessing a Database – ADO.Net
 Use ActiveX Data Objects (ADO)
 An Access database uses the following objects:
– OleDbConnection – connection tool establishes a link to a
data source.
– OleDbDataAdapter – data adapter handles retrieving and
updating the data and creates a Dataset.
– Dataset – bind columns in the Dataset to controls (textbox,
listbox) by setting the DataBinding property.
– Use the Fill method of the data adapter to load the data
into the dataset, daCustomer.Fill(dsCustomer1);
The Fill method is usually placed in the form load event.
CIS162AD
8
Accessing and Presenting Data
Data
Source
Specific
data file
CIS162AD
Connection
Connects to
data source
Data
Adapter
Handles data
transfer and
provides data
for dataset;
uses SQL
Web
Form
Dataset
Windows
Form
Actual data
stored in
memory;
can contain
multiple
tables
Bounded
controls
display
data
9
Completing CS13
 Create CS13 project and download the database
file (CSMail.mbd) into the /debug/bin/ folder of the
project.
 CSMail.mbd is available on the website.
 Name the form CS13Form.
 Change the Text property of the form to
CS13 Your Name.
 Display the Data Sources Panel in Visual Studio.
– Menu Path: Data > Show Data Sources
 The Data Source panel should be displayed over the
Toolbox.
– Click on Add New Data Source
– The Data Source Configuration Wizard should launch.
CIS162AD
10
Data Source Panel
CIS162AD
11
Data Source Configuration Wizard
 Click on Database as the data source type.
 Click on Next >.
CIS162AD
12
Data Connection
 Click on New Connection.
CIS162AD
13
Add Connection
– Click on Change...
– Select Microsoft Access
Database File
– Click on OK.
– Click Browse…
– In the open file dialog box,
navigate to
CS13\bin\Debug\
CSmail.mbd
– Click on Open.
– Click Test Connection
– Click OK.
– Click Next on Choose Data
Connection window.
CIS162AD
14
Copy File Prompt
 Click on No.
CIS162AD
15
Save Connection String
 Make sure Yes is selected.
 Click Next >
CIS162AD
16
Choose Database Objects
 Click on the plus sign in front of Tables to expand list.
 Select Customer.
 Click on Finish.
CIS162AD
17
Add DataGridView
 Drag Customer table from Data Sources on to
the form to add a DataGridView control.
CIS162AD
18
DataGridView Control
 When a table from the Data Sources is dragged on to the form, a




DataGridView control is created by default.
It allows users to browse all the rows in the table as well as add,
update, and delete rows.
The navigation toolbar is also added at the top of the form.
The FormLoad method is also created, which contains a call to
the Fill method to load the data into the DataSet from the source.
Four additional objects are also added to the Component tray.
– Dataset provides access to the data
– TableAdapter provides the commands to read and write to
the table
– BindingNavigator defines the toolbar used to navigate
– BindingSource identifies the data source for the bounded
controls
 Next Slide - Binding
CIS162AD
19
Data Binding
 Complex Binding
– Connect more than one data element to a control.
– DataGridView uses complex binding because several
columns from the table are displayed in the same control.
 Simple Binding
– Connect one data element to a control.
– Connect a textbox to the name column (etc.)
– May use Data Bindings property and select the column from
the database that should be displayed in control.
– Later we’ll build a form using Labels and Textboxes.
CIS162AD
20
Connection String
 When the database file is selected in the wizard, the
directory path is included with the file name.
For example: (E:\CS14\bin\Debug\CSMail.mdb).
 If you use a different computer to run the program later, or
rename a folder, or move the project, the connection will fail
because the drive letter or directory may be different.
(There also seems to be a bug…)
 Set the connection string at runtime by adding the following
command (all on one line) in the Form Load event procedure:
customerTableAdapter.Connection.ConnectionString =
"Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=CSmail.mdb";
 Make sure to place the database file in the Debug folder.
CIS162AD
21
FormLoad Method Example
private void CS13Form_Load(object sender, EventArgs e)
{
customerTableAdapter.Connection.ConnectionString =
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=CSmail.mdb";
this.customerTableAdapter.Fill(this.cSmailDataSet.Customer);
}
CIS162AD
22
Execute the Project
 Test the form by
navigating
through the
rows and using
the scroll bars.
 Feel free to
make the form
and
DataGridView
control bigger in
the design form.
CIS162AD
23
Add Additional Form
 To bind a data source to Labels and Textboxes,
we’ll need to add another form to our project.
Project > Add Windows Form > CS13Form2.cs
 On the Data Sources panel, click on the
Customers table once to select it.
 Click on the drop down arrow, and select
Details (see next slide).
CIS162AD
24
Select Details
CIS162AD
25
Create Labels and Textboxes
 Click and drag the
Customer table on to
the form and the
Labels and Textboxes
are created.
 As well as the
required Component
Tray controls.
 Double click on the
form and set the
connection string.
CIS162AD
26
Change Startup Form
 In the Solution Explore, double click on Program.cs (last file
listed) and change the form that is displayed to CS13Form2 in the
last line of code in the Main method.
CIS162AD
27
Execute the Project
 Test the form
by navigating
through the
rows.
 That should
complete the
project .
CIS162AD
28
Summary
 Relational Database Terminology
 Connecting to a Database
 Binding data
 DataGridView
 Label and Textbox View
CIS162AD
29