Transcript Building Data-Driven Web Applications

Building Data-Driven Web Applications
Introduction to ASP.NET
By Kathleen Kalata
Chapter 8
1
Objectives
• In this chapter, you will:
• Use the DataReader object to display data
• Retrieve a DataSet from a database
• Use the DataView object to display data
• Sort data using the DataGrid control
Chapter 8
2
Objectives
• In this chapter, you will:
• Filter data using the DataGrid control
• Insert a new record into a database using the
DataGrid control
• Modify an existing record in a database using the
DataGrid
• Delete a record from a database using the DataGrid
control
• Build a reusable VB .NET component that will
retrieve a DataSet
Chapter 8
3
Using Data Sources
within Visual Studio .NET
• The process of binding the data is the same
regardless of the data source
– DataReader - provides a read-only, direct connection to the
data source
– DataSet - accesses the data as multiple tables
– DataTables - can have relationships defined between the
DataTable Objects
– DataView - retrieves a subset of one of the tables within the
DataSet
Chapter 8
4
The DataReader Object
• The DataReader object is used to retrieve a readonly, non-buffered stream of data from a database.
– Non-buffered means only one record at any one time is
stored in memory
– When the new record is read, the old record is removed
from memory first
– Because it’s a stream of data, it is retrieved sequentially and
is read-only
• SqlDataReader is used with SQL Server databases
• OledbDataReader is used with all other databases, including
Access and Oracle
Chapter 8
5
Create the DataReader Object
• Use the steps shown on pages 346 and 347 of the
textbook to complete the tasks mentioned below:
– Create the DataReader object, and read the data
– Read method to retrieve the data stream in a forward only direction
• Once you have read the record, you can access the value of a column
using the name of the column within quotations, or the index number
• The index number is the number of the column in the order in which it
is retrieved. The index number for the first column is 0
– Use the Close method of the DataReader object when you have
finished reading the data
– Pass the CloseConnection property as a parameter when you call
the Execute method of the Command object to close the
connection to the DataReader
Chapter 8
6
Using the DataReader
to Retrieve Data from a Database
Chapter 8
7
Building Search Queries
Using SQL
• SQL commands retrieve a select group of
records using a criterion that is used to filter the
records
• A search condition evaluates to either true or
false
– Uses the WHERE keyword to identify the search
condition
• “AND” is used to separate conditions; both search conditions
must be resolved to true
• “OR” is used to separate conditions; only one of the search
conditions needs to be resolved to true
Chapter 8
8
Building Search Queries
Using SQL
• The search condition consists of an
expression such as the name of a field, or it
can be a string, a number, or a value passed
from a form
– Strings are case-sensitive when used as
expressions
– keyword NULL can be used to search for empty
fields
– Valid comparison operators include +, <, >, <>, IS,
and ISNOT
Chapter 8
9
Using the DataReader Object
with a SQL Server Database
• The names of the Connection, Command, and
DataReader objects are SqlConnection,
SqlCommand, and SqlDataReader
– The steps required to retrieve the data using the DataReader
object are the same for both the OleDb and SqlClient
databases
– Code from DataReader.aspx, can be rewritten to support a
SQL Server database using the SqlDataReader object
Chapter 8
10
Action Commands
• You can use the following SQL statements to insert, update,
and delete records
– INSERT command is used to add a new record
• Specify a value for every field or use the word “NULL” for the value
• Specify the fieldnames in parentheses after the table if you plan to only
enter values for a subset of fields
– DELETE command is used to delete one or more rows of data
• If you do not specify a specific record or set of records using the
WHERE clause, the entire set of records is deleted
– UPDATE command is used to modify one or more rows of data
• The keyword SET is used to assign the values to the fieldnames
• If you do not specify a search condition using the WHERE clause, the
value is updated for all records in the database
Chapter 8
11
Upsizing an Access
Database to SQL Server
• Use the Upsizing Wizard to upgrade an Access database to
SQL Server for better security, performance, and user
management
– Access provides a report that contains information about the new
database
– Always print the report or save it as a text file for future reference
– Locate the Upsizing Wizard from the Tools on the menu bar, point
to Database Utilities
– Use an account with CREATE DATABASE privileges
– Always create a page to display your data to ensure that the data
connections succeeded
Chapter 8
12
Using the DataReader
Object to Display Data
• Display the categories list using the object
• Within the Page_Load procedure, create the objects,
and retrieve the data using the DataReader object
– Create a connection using a SQL Connection object
– Use the DataReader to Read the data from the database
• Using the upsized database, you will create a page to
display the categories list using the SqlClient
DataReader object by following the steps listed on
pages 355 and 356 of the textbook
Chapter 8
13
Using the DataReader
Object with Stored Procedures
• Use the DataReader object with the Command Object in
order to work with Stored Procedures using the
processes outlined on pages 357 through 361 of the
textbook
– Set the CommandType procedure to
CommandType.StoredProcedure
• The category list is bound to a hyperlink control
– When the user clicks the link, the DataReaderProducts page opens
– The CategoryID field is passed with the hyperlink as a querystring
to the DataReaderProducts page
– Then you create a stored procedure that only displays the
products of the category that was clicked
Chapter 8
14
Using Stored Procedures
to Display Data with
the DataReader Object
Chapter 8
15
Using a Stored Procedure
to Retrieve Data with
the DataReader Object
Chapter 8
16
Steps to Insert the
DataAdapter Object
1. Create the WebForm in Visual Studio .NET
2. Add a DataAdapter from the Data tab in the
Toolbox
3. Select your database connection in the
DataAdapter Wizard
4. Use the Query Builder to build an SQL statement.
5. Add a DataSet object from the Data tab in the
toolbox
6. Use the Generate DataSet Methods from the Data
menu
Chapter 8
17
Steps to Insert the
DataAdapter Object
7. Create a DataView object
8. Assign a table to the DataView object
9. Assign the DataSource property of a data control
to the DataView
10. Call the Fill method of the DataAdapter in the
code behind the page
11. Bind the controls to the data source
12. Save and build the solution and view the Web
page in a browser
Chapter 8
18
The DataAdapter, DataSet,
and DataView Objects
• The DataAdapter, DataSet, and DataView objects
are used together to retrieve data from the
database
• If there is only one table involved, then you can
simply use the default DataView object, or you
can identify the table or SQL statement to use to
retrieve a table for the DataView
• Create a DataView from the DataSet, which is a
subset of one of the tables within the DataSet
Chapter 8
19
Using the DataView to
Retrieve Data from a Database
• Refer to the
DataTable in the
DataView by the
table name, or
by its index
position within
the Tables
collection using
the processes
illustrated on
pages 363
through 365 of
the textbook
Chapter 8
20
Customizing the DataGrid Control
• Customizing a DataGrid can be performed in the
code behind the page, or by using the Visual Studio
.NET tools
• Data Columns
– Bound columns contain data that is bound to a column in
the database and can display the data, or use the data in an
expression, such as the source of an image tag
– Unbound columns display content such as buttons and
other form fields
Chapter 8
21
Paging and Sorting Data with the
DataGrid Control
• The default sorting creates a Link Button control for the column
name at the top of the column
– When the user clicks the Link Button control, the DataGrid is
sorted by that column
– The DataGrid does not actually sort the rows
– It raises a SortCommand event, which causes the data to be
rebound to the DataSource
• Add code to sort the data if the event occurs
– The sort expression is passed as an argument which is
represented as e.SortExpression
– Get the value that is selected as the sort key
– Then rebind the DataGrid to the DataView
Chapter 8
22
Paging and Sorting Data with the
DataGrid Control
• Paging displays a subset of records on a page
– A navigation bar at the bottom of the page allows you to page
through the data across Web pages
• The default number of records displayed is 10
• Paging is turned off by default
– The paging property of the DataGrid also raises a
PageIndexChanged event when the user clicks the link button
– Add the code to handle the event and retrieve the NewPageIndex
property, which is the page the user wants to browse to
– Set the CurrentPageIndex property to the NewPageIndex
– Rebind the data to the DataGrid control
Chapter 8
23
Paging and Sorting Data with the
DataGrid Control
Chapter 8
24
Filtering Data with
the DataGrid Control
• The DataGrid filter allows you to temporarily select a
subset of records from the DataGrid
– The filter does not remove the data from the database, but
only removes the records from the Web page
– You have to set the RowFilter property of the DataView
object to the SQL command that queries the DataView to
retrieve the records
– When the filter is removed, the records are redisplayed
within the Web page
– Perform the steps listed on pages 369 and 370 of the
textbook to practice filtering data with the DataGrid control
Chapter 8
25
Building a Search Tool
Using Visual Studio .NET
Chapter 8
26
Inserting, Modifying, and Deleting
Records
• To maintain a database
– Create new records, modify existing records, and
delete records
– Use sql commands
– Use the methods built into the Data controls
Chapter 8
27
Using the DataGrid
Control to Maintain a Database
– AlternatingItemStyle – Identifies the style for alternating
rows
– EditItemStyle – Identifies the style for a row being edited
– FooterStyle – Identifies the style for the footer row
– HeaderStyle – Identifies the style for the header row
– ItemStyle – Identifies the style for individual items within the
list or control
– PagerStyle – Identifies the style for the page selection
controls
– SelectedItemStyle – Identifies the style for the currently
selected item
Chapter 8
28
Using the DataGrid
Control to Maintain a Database
• HeaderText, HeaderImageURL, and FooterText
properties - appear at the top and bottom of the
TemplateColumn and may contain HTML elements
and controls
• Visible property - allows you to show or hide the
column
• SortExpression property - used to identify the
column from the data source, which is used when
sorting the column
Chapter 8
29
Using the DataGrid
Control to Maintain a Database
• DataField property- identifies the column from the
data source bound to the column
• DataFormatString property - identifies the formatting
rules for the contents of the BoundColumn
• ReadOnly property -used with BoundColumns to
stop the user from editing a column when the
DataGrid is in edit mode
• ItemTemplate column - contains HTML elements and
controls within the column
Chapter 8
30
Using the DataGrid
Control to Maintain a Database
• TemplateColumn - provides additional content, such
as HTML
– EditItemTemplate property - used when the DataGrid is in
edit mode
• HyperLinkColumn - used by the DataGrid control to
bind a hyperlink to data
• ButtonColumn - used by the DataGrid control to
insert a user defined button
– ButtonType property – LinkButton (hyperlink) or
PushButton (button)
Chapter 8
31
Adding and Deleting Records
• You can write code to insert or delete records, or you
can use the methods built into the data controls
– The table uses the ItemCommand to insert add and delete
command functions
– Use the DataGrid control to add and delete records from the
DataSet
• Use a ButtonColumn that to trigger the Add and Delete record
function
• Use a built-in TemplateColumn
• Use the directions shown on pages 372 through 378 of the
textbook to perform these tasks and others as listed
Chapter 8
32
Adding and Deleting Records
Chapter 8
33
Adding and Deleting Records
Chapter 8
34
Updating the Database
Using the DataGrid Control
• The DataGrid control uses the EditCommandColumn
to help add interactivity to your DataGrid
– EditCommandColumn is a button column that contains
LinkButtons labeled Edit, Update, and Cancel
– The EditCommandColumn also allows you to change the
text of the Edit, Update, and Cancel buttons with the
EditText, UpdateText, and CancelText properties
– EditCommand event occurs when you click the edit button
– UpdateCommand event is triggered when you click on the
update button
– CancelCommand is triggered when the user clicks the
cancel button
Chapter 8
35
Updating the Database Using the
DataGrid Control
• Event handlers
– onEditCommand - onCancelCommand
Chapter 8
- onUpdateCommand
36
Building Reusable Components
• Visual Basic .NET allows you to create
reusable, compiled components that create
objects, access stored procedures, and
return data
Chapter 8
37
Creating a Global Variable
in the Web Configuration File
• Create a global variable in the configuration file
named Web.Config which will contain the
connection string
– The Web.Config is an XML-based text file
– You can include comments within the file using the HTML
comment tags
– The appSettings tag indicates the settings for the Web site
– Use the add tag to create the global application variables
Chapter 8
38
Creating a Visual
Basic .NET Component
Chapter 8
39
Creating Web Pages from Reusable
Visual Basic .NET Components
Chapter 8
40