Transcript Document

CIS 375—Web App Dev II
ASP .NET 10
Database 2
Introduction to Server-Side Data



Server-side data access is unique in that Web pages
are basically ___________.
This presents some difficult challenges when trying
to perform database transactions.
The __________ control can help manage these
challenges, allowing you to concentrate more on
your application logic and less on the details of state
management and event handling.
2
Connections, Commands, and
Datasets 1



The common language _________ provides a
complete set of managed data access APIs for dataintensive application development.
These APIs help to abstract the data and present it in
a consistent way regardless of its actual source (SQL
Server, OLEDB, XML, and so on).
There are essentially three objects you will work with
most often:



A __________ represents a physical connection to some data store.
A __________ represents a directive to retrieve from (select) or
manipulate (insert, update, delete) the data store.
A __________ represents the actual data an application works with.
3
Namespaces

To give your page access to the classes you will need
to perform SQL data access, you must import the
System.Data and System.Data.SqlClient
namespaces into your page.
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
4
SQL Query

To perform a select query to a SQL database, you
create a SqlConnection to the database passing the
connection string, and then construct a
SqlDataAdapter object that contains your query
statement.
Dim myConnection As New SqlConnection( _
"server=(local)\NetSDK;database=pubs;Integrated
Security=SSPI")
Dim myCommand As New SqlDataAdapter("select * from
Authors", myConnection)

To populate a DataSet object with the results from
the query, you call the command's ______ method.
Dim ds As New DataSet()
myCommand.Fill(ds, "Authors")
5
SqlDataReader


For Web applications, you are usually performing short
operations with each request (commonly to simply display the
data).
You often don't need to hold a ________ object over a series
of several requests. For situations like these, you can use a
SqlDataReader.
Dim myCommand As SqlCommand = New SqlCommand("select *
from Authors", myConnection)
myConnection.Open()
Dim dr As SqlDataReader = myCommand.ExecuteReader()
...
myConnection.Close()
6
SqlCommand

When performing commands that do not require data
to be returned, such as inserts, updates, and deletes,
you also use a SqlCommand.
Dim myConnection As New SqlConnection(
"server=(local)\NetSDK;database=pubs;Integrated
Security=SSPI")
Dim myCommand As New SqlCommand("UPDATE Authors SET
phone='(800) 555-5555' WHERE au_id = '123-45-6789'",
myConnection)
myCommand.Connection.Open()
myCommand.ExecuteNonQuery()
myCommand.Connection.Close()
7
Binding SQL Data to a DataGrid 1





The following sample shows a simple select query
bound to a DataGrid control.
The DataGrid renders a table containing the SQL
data.
DataGrid1.aspx [Run Sample] | [View Source]
The DefaultView property represents the current
state of a table within a DataSet, including any
changes which have been made by application code.
After setting the DataSource property, you call
DataBind() to populate the control.
MyDataGrid.DataSource=ds.Tables("Authors").DefaultView
MyDataGrid.DataBind()
8
Binding SQL Data to a DataGrid 2

An alternative syntax is to specify both a DataSource and a
DataMember. In this case, ASP.NET automatically gets the
DefaultView for you.
MyDataGrid.DataSource=ds
MyDataGrid.DataMember="Authors"
MyDataGrid.DataBind()



You can also bind directly to a SqlDataReader.
In this case you are only displaying data, so the forward-only
nature of the SqlDataReader is perfectly suited to this
scenario, and you benefit from the performance boost that
SqlDataReader provides.
DataGrid1.1.aspx [Run Sample] | [View Source]
9
Performing a Parameterized Select 1



You can also perform a parameterized select using
the SqlDataAdapter object.
The following sample shows how you can modify the
data selected using the value posted from a select
HtmlControl.
DataGrid2.aspx [Run Sample] | [View Source]
10
Performing a Parameterized Select 2


The SqlDataAdapter maintains a Parameters
collection that can be used to replace variable
identifiers (denoted by an “___" in front of the name)
with values.
You add a new SqlParameter to this collection that
specifies the name, type, and size of the parameter,
and then set its Value property to the value of the
select.
myCommand.SelectCommand.Parameters.Add(New
SqlParameter("@State", SqlDbType.NVarChar, 2))
myCommand.SelectCommand.Parameters("@State").Value =
MySelect.Value
11
Performing a Parameterized Select 3




DataGrid2.aspx statically populates the values of
the select box, but this will not work well if those
values ever change in the database.
Because the select HtmlControl also supports an
IEnumerable DataSource property, you can use a
________ query to dynamically populate the select
box instead, which guarantees that the database and
user interface are always in sync.
The following sample demonstrates this process.
DataGrid3.aspx [Run Sample] | [View Source]
12
Inserting Data in a Database 1






Add a simple input _______ to the page.
Execute an insert command in the form submit
_______ handler.
Use the command object's Parameters collection to
populate the command's values.
Check to make sure the required values are not null
before attempting to insert into the database.
Execute the insert command inside of a __________
block, just in case the primary key for inserted row
already exists.
DataGrid4.aspx [Run Sample] | [View Source] 13
Inserting Data in a Database 2



Instead of explicitly checking the input values, you
could have just as easily used the _________
controls provided with ASP.NET.
Note that using the RegEx Validator provides the
additional benefit of checking the format for certain
kinds of fields.
DataGrid5.aspx [Run Sample] | [View Source]
14
Updating Data in a Database 1



To allow rows to be edited, the DataGrid supports
an integer EditItemIndex property, which indicates
which ______ of the grid should be editable.
When this property is set, the DataGrid renders the
row at that index as text input boxes instead of
simple ________.
The DataGrid can contain an
EditCommandColumn that renders ______ for
firing three special events: EditCommand,
UpdateCommand, and CancelCommand.
15
Updating Data in a Database 2




On the DataGrid tag itself, you wire event
_________ to each of the commands fired from the
EditCommandColumn.
The DataGridCommandEventArgs argument of
these handlers gives you direct access to the
_______ selected by the client, which you use to set
the DataGrid's EditItemIndex.
Performing an update query requires that you know
the ___________ in the database for the row you
wish to update. To support this, the DataGrid
exposes a DataKeyField property that you can set
to the field name for the primary key.
DataGrid6.aspx [Run Sample] | [View Source] 16
Updating Data in a Database 3




One problem with the preceding example is that the
primary key field (au_id) also renders as a text input
box when a row is editable.
You can disable this column from rendering as a text
box by specifying exactly what each column looks like
for the editable row.
You do this by defining each row in the DataGrid's
_________ collection, using the BoundColumn
control to assign data fields with each column.
DataGrid7.aspx [Run Sample] | [View Source]
17
Updating Data in a Database 4





You can also specify a TemplateColumn, which
gives you complete control over the contents of the
column.
The following sample demonstrates using the
TemplateColumn control to render the "State"
column as a drop-down list and the "Contract"
column as a check box ____________.
DataGrid8.aspx [Run Sample] | [View Source]
The following sample adds Validator controls to the
columns to check the ______ input before attempting
to perform the update.
DataGrid9.aspx [Run Sample] | [View Source]
18
Deleting Data in a Database




Another control that can be added to the DataGrid's
Columns collection is the ButtonColumn control.
ButtonColumn supports a CommandName
property that can be set to Delete.
On the DataGrid, you _____ an event handler to the
DeleteCommand, where you perform the delete
operation.
DataGrid10.aspx [Run Sample] | [View Source]
19
Sorting Data from a Database 1



While the DataGrid control doesn't explicitly sort its
data for you, it does provide a way to call an event
handler when the user clicks a column header, which
you can use to sort the data.
When the DataGrid's AllowSorting property is set
to true, it renders __________ for the column
headers that fire a Sort command back to the grid.
You set the OnSortCommand property of the
DataGrid to the handler you want to call when the
user clicks a column link.
20
Sorting Data from a Database 2




The name of the column is passed as a
SortExpression property on the
DataGridSortCommandEventArgs argument,
which you can use to set the Sort property of the
DataView bound to the grid.
DataGrid11.aspx [Run Sample] | [View Source]
When using BoundColumn controls, you can
explicitly set the SortExpression property for each
column, as demonstrated in the following sample.
DataGrid12.aspx [Run Sample] | [View Source]
21
Working with Master-Detail
Relationships




A very common Web-based interface is one in which
a row of data can be selected that navigates the
client to a “________" page.
To accomplish this using the DataGrid, you can add
Columns collection,
a HyperLinkColumn to the __________
which specifies the details page to which the client
will navigate when the link is clicked.
On the details page, you retrieve the ___________
argument and perform a join select to obtain details
from the database.
DataGrid13.aspx [Run Sample] | [View Source]
22
Writing and Using Stored
Procedures


Using stored procedures can reduce the cost of
performing _______ database operations in an
application.
A stored procedure is easy to create, and can even
be done using a ______ statement.
CREATE Procedure GetAuthors AS
SELECT * FROM Authors
return
GO


You indicate to the SqlCommand that the
CommandText is a stored procedure by setting the
CommandType property.
DataGrid14.aspx [Run Sample] | [View Source]23
Accessing XML-based Data





The DataSet supports a ReadXml method that
takes a FileStream object as its parameter.
Each TableName section corresponds to a single
_____ in the table.
DataGrid17.aspx [Run Sample] | [View Source]
You can also read the data and _________
separately, using the ReadXmlData and
ReadXmlSchema methods of the DataSet.
DataGrid18.aspx [Run Sample] | [View Source]
24