Transcript CPGM21X1 - Sheridan College

PROG 11044
Advanced Web Applications
With .NET
Programming Data Pages
Review So Far
Classes we used:
SqlConnection
A connection object that facilitates
communication with the database
SqlCommand
A command object that models a specific
SQL command to be executed over a
specific connection
Connection property – references a
connection object
CommandText property – contains the SQL
command to execute
7/20/2015
Wendi Jollymore, ACES
2
Review So Far
SqlDataReader
A forward-only, read-only set of
records
Returned by the command object’s
ExecuteReader() method
command.ExecuteReader() executes the
CommandText query over the Connection
object
The resulting rows or records are returned
as a Data Reader object
7/20/2015
Wendi Jollymore, ACES
3
Review So Far
SqlDataReader, continued
Read() method
Returns true if it read a record
Used to move through the records
reader[“fieldName”] syntax
Allows you to access the field values in the
current record
7/20/2015
Wendi Jollymore, ACES
4
Review So Far
SqlParameter
Some SQL statements will contain
values that are dynamic
E.g. select * from authors where
authorID = ?
The ? Value could come from a list box
selection
Parameter objects are used to replace
unknown or variable values
More secure than concatenating values
together to build SQL statements
7/20/2015
Wendi Jollymore, ACES
5
The DataSet Object
System.Data.DataSet
A virtual database
The client machine can download a copy
of the tables/query results they want
DataSet data can be modified
You can move around in a data set
i.e. it’s not forward-only
The data set object can be used with
any provider
7/20/2015
Wendi Jollymore, ACES
6
The DataSet Object
A DataSet contains a collection
of tables
It contains information about the
tables, such as:
Schema
Records
Relationships
Constraints
7/20/2015
Wendi Jollymore, ACES
7
The DataSet Object
DataSet.Tables property
References the collection of tables
This collection object is an instance of
the DataTableCollection class
A DataTableCollection object contains
a set of DataTable objects
Each DataTable object in the
DataTableCollection is one of the
tables in the database
7/20/2015
Wendi Jollymore, ACES
8
The DataSet Object
Ways to reference a table in the
dataset:
dataSet.Tables[“Books”]
dataSet.Tables[0]
7/20/2015
Wendi Jollymore, ACES
9
The DataTable Object
Models a table from the database
dataTable.Rows property
References the DataRowCollection
object
A collection of DataRow objects
The set of rows (records) in the table
7/20/2015
Wendi Jollymore, ACES
10
The DataTable Object
dataTable.TableName property
The name you gave the table when
you added it to the data set
dataTable.Columns property
References the DataColumnCollection
object
A collection of DataColumn object
The set of columns (fields) in the table
dataTable.Columns.Count property
The number of columns in the table
7/20/2015
Wendi Jollymore, ACES
11
The DataRow Object
Models a row (record) in the row
collection of the table
dataTable.Rows.Count property
The number of DataRow objects in the
DataRowCollection
i.e. the number of records
dataTable.Rows.Find(keyValue)
Used to locate a specific record in the
collection
keyValue is the value for the primary key
field to match
7/20/2015
Wendi Jollymore, ACES
12
The SqlDataAdapter Object
A data adapter facillitates
communication between the
DataSet and the data source
DataSet is not provider specific
Everything else is
The data adapter will pass any data
from the data source to the DataSet
The data adapter will also handle the
data manipulation for you
7/20/2015
Wendi Jollymore, ACES
13
The SqlDataAdapter Object
SelectCommand property
Contains a command object that contains a
SELECT query
UpdateCommand, DeleteCommand,
InsertCommand properties
Contains command objects for UPDATE,
DELETE, and INSERT commands
Command objects have their own
connection, so that connection is used to
execute the various commands
7/20/2015
Wendi Jollymore, ACES
14
The SqlDataAdapter Object
dataAdapter.Fill(dataSet) method
Executes the query in the
SelectCommand property’s command
object
Fills the dataset with the results of that
query
dataAdapter.Fill(dataSet, strName)
As above but assigns the result table
with strName as a table name
Accessed via dataSet.Tables[strName]
7/20/2015
Wendi Jollymore, ACES
15
Exercise
ADO Notes: “Advanced
Programming”
Do the tutorial “Working with the
DataSet”
Do the “Multiple Tables in a DataSet”
tutorial and exercise
7/20/2015
Wendi Jollymore, ACES
16
Iterating Through Records
There are a few ways you can iterate
through the collection of data rows
(records)
The rows collection can be used to
access records and field values:
Get the third row:
DataRow aRow = dataSet.Tables[“Authors”].Rows[3];
Get the last name field value from the 1st
row:
String lastName = aRow[“lastName”];
String lastName =
dataSet.Tables[“Authors”].Rows[3][“lastName”];
7/20/2015
Wendi Jollymore, ACES
17
Iterating Through Records
dataRow.ItemArray property
References the field values in a row as an
array of generic objects
You can retrieve this array and then iterate
through it:
object[] values =
dataSet.Tables[“Books"].Rows[rowIndex].ItemArray;
for (int i=0; i<values.Length; i++) {
string output += values[i].ToString();
}
7/20/2015
Wendi Jollymore, ACES
18
Exercise
Try the two exercises in the section
“Displaying Records
Programmatically”
Do the “Navigating Records”
exercise
7/20/2015
Wendi Jollymore, ACES
19