Transcript AspPart4

CSCI/CINF 4230
ASP.NET
Part 4
Instructor: Charles Moen
ADO.NET
(Continued from last week)
ASP.NET (MacDonald)
Disconnected Data Access
Fetch the data and store it in memory
Steps to query the database with direct data access
1.
Create Connection and Command objects
2.
Create DataSet and DataAdapter objects
3.
Retrieve information from the database with the DataAdapter object and add it
to the DataSet object
4.
Close the connection
5.
Interact with the data in your code
In your C# code, import the correct ADO.NET namespaces
using System.Data;
using System.Data.OleDb;
using System.Web.Configuration;
3
ASP.NET (MacDonald)
Deli Menu Demo
 Use ASP.NET and Visual Studio 2008 to build a web application
to display the results of a menu search by category using a
DropDownList
4
ASP.NET (MacDonald)
Demo web.config
 Define the connection string in the web.config file so that it is
available throughout your application
NOTE: This is a partial file.
<?xml version="1.0"?>
<configuration>
<connectionStrings>
<add name="Deli" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|/db.mdb"/>
</connectionStrings>
<configuration>
The alias that you can use
in your code
Points to the App_Data folder inside
your web application directory
5
ASP.NET (MacDonald, Walther)
Demo Default.aspx.cs
using
using
using
using
using
using
using
using
System;
System.Text;
System.Web;
System.Web.UI;
System.Web.UI.WebControls;
System.Data;
System.Data.OleDb;
System.Web.Configuration;
public partial class _Default : System.Web.UI.Page
{
private string connectionString =
WebConfigurationManager.ConnectionStrings["Deli"].ConnectionString;
private DataSet ds;
Add a DataSet object
The DataSet stores the data that was retrieved
from the database, so it can be considered to be
an in-memory database
6
ASP.NET (MacDonald)
Demo Page_Load
protected void Page_Load(object sender, EventArgs e)
{
string selectSql = "SELECT id, category FROM Categories ";
OleDbConnection conn = new OleDbConnection(connectionString);
OleDbCommand cmd = new OleDbCommand(selectSql, conn);
OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);
ds = new DataSet();
try
{
conn.Open();
adapter.Fill(ds, "Categories");
}
catch (Exception error)
{
resultsLabel.Text = "ERROR: " + error.Message;
}
finally
{
conn.Close();
}
if (!IsPostBack)
{
foreach (DataRow row in ds.Tables["Categories"].Rows)
{
ListItem newItem = new ListItem();
newItem.Text = row["category"].ToString();
newItem.Value = row["id"].ToString();
categoriesDropDownList.Items.Add(newItem);
}
}
}
The DataAdapter is the object that
is used to transfer data from the
physical database to the DataSet,
the “in-memory” database
Create the DataSet and
DataAdapter objects
Retrieve the data with the
DataAdapter object and fill
the DataSet object with it
After storing the data in
memory, close the
connection
Then iterate through the
data stored in memory to
create the list
7
ASP.NET (MacDonald)
Demo Page_Load
protected void Page_Load(object sender, EventArgs e)
{
string selectSql = "SELECT id, category FROM Categories ";
OleDbConnection conn = new OleDbConnection(connectionString);
OleDbCommand cmd = new OleDbCommand(selectSql, conn);
OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);
ds = new DataSet();
try
{
conn.Open();
adapter.Fill(ds, "Categories");
cmd.CommandText = "select * from MenuItems";
adapter.Fill(ds, "MenuItems");
}
catch (Exception error)
{
resultsLabel.Text = "ERROR: " + error.Message;
}
finally
{
conn.Close();
}
if (!IsPostBack)
{
foreach (DataRow row in ds.Tables["Categories"].Rows)
{
ListItem newItem = new ListItem();
newItem.Text = row["category"].ToString();
newItem.Value = row["id"].ToString();
categoriesDropDownList.Items.Add(newItem);
}
}
}
What if we want to use
the data from both
tables on this page?
The DataSet object can
hold multiple tables
8
ASP.NET (MacDonald)
Demo
categoriesDropDownList_SelectedIndexChanged
protected void categoriesDropDownList_SelectedIndexChanged(object sender, EventArgs e) {
if (IsPostBack)
{
StringBuilder sb = new StringBuilder();
sb.Append("<table border=\"1\" cellpadding=\"5\" width=\"640px\">");
foreach (DataRow menuItemRow in ds.Tables["MenuItems"].Rows)
{
if (menuItemRow["category"].ToString() == categoriesDropDownList.SelectedValue)
{
sb.Append("<tr>");
sb.Append("<td width=\"100px\">");
sb.Append(menuItemRow["item"].ToString());
sb.Append("</td>");
sb.Append("<td>");
sb.Append(menuItemRow["description"].ToString());
sb.Append("</td>");
An HTML table can be
sb.Append("<td>");
sb.Append(menuItemRow["price"].ToString());
built by interacting with
sb.Append("</td>");
the data that is stored
sb.Append("</tr>");
in memory inside the
}
}
DataSet object
sb.Append("</table>");
resultsLabel.Text = sb.ToString();
}
}
9
Data Binding
ASP.NET (MacDonald)
Data Binding
 Binding a control to a data source
 Single-value binding
•
Inserted in almost any element in the aspx file
•
Insert a variable, property, or expression into a page
 Repeated-value binding
•
Works with ASP.NET list controls that support data binding
•
Set the control properties
 DataBind()
•
Method of the Page class
•
Activates the data bindings, typically called in the Page_Load handler
11
ASP.NET (MacDonald)
Single-Value Binding
1. Insert a data binding expression into the aspx code
•
Could be a variable, property, or expression
2. Call DataBind() in the code
Opening and closing symbols
<%# 2 + 2 %>
A data binding expression
<%# Request.QueryString["name"] %>
12
ASP.NET (MacDonald)
Single-Value Binding Example
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Label ID="todayLabel" runat="server">Today is <%# DateTime.Today.ToShortDateString() %></asp:Label>
</div>
</form>
</body>
</html>
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
DataBind();
}
}
1. Insert a data binding expression
into the aspx code
2. Call DataBind() to see the output on the page
13
ASP.NET (MacDonald)
Some questions about single-value binding
 What are some problems with single-value binding?
•
ANSWER: It mixes the code with the presentation layer instead of keeping it all
in one place, inside the code-behind file
 What shows up on the page when you forget to call DataBind()?
 What shows up on the page when the following data binding
expression is used in the aspx code?
<%# Request.QueryString["name"] %>
 What would the URL look like?
14
ASP.NET (MacDonald)
Repeated-Value Binding
1. Create a data object, such as an ArrayList, and fill it
with data
2. Link the data object to a control
Some controls that support data binding:
ListBox, DropDownList, CheckBoxList, RadioButtonList,
HtmlSelect, GridView, FormView, ListView
3. Call DataBind() in the code
15
ASP.NET (MacDonald)
Repeated-Value Binding Example
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Label ID="categoriesLabel" runat="server" Text="Categories: "></asp:Label>
<asp:DropDownList ID="categoriesDropDownList" runat="server">
</asp:DropDownList>
</div>
</form>
</body>
</html>
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
ArrayList categories = new ArrayList();
categories.Add("Sandwiches");
categories.Add("Salads");
categories.Add("Pasta");
categoriesDropDownList.DataSource = categories;
DataBind();
}
}
Add a list control that supports
data binding in the aspx code
1. Create a data object filled with data
2. Link the data object with the control –
not necessary to use a loop to add
the ListItems
3. Call DataBind() to see the output
16
on the page
ASP.NET (MacDonald)
Data Source Controls

We can use data source controls to interact with a
database without having to write the data access code

SqlDataSource
•

Connect to any data source that has an ADO.NET data provider
AccessDataSource
•
Connect to an Access database file

ObjectDataSource

XmlDataSource
•

Connect to an XML file
SiteMapDataSource
17
ASP.NET (MacDonald)
Data Source Control Example
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<?xml version="1.0"?>
<configuration>
<connectionStrings>
<add name="Deli" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|/db.mdb"/>
</connectionStrings>
<configuration>
First, add the db.mdb file to the
App_Data folder in Visual Studio
</div>
</form>
</body>
</html>
Then, add the connection string
to the web.config file
18
ASP.NET (MacDonald)
Data Source Control Example
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:SqlDataSource ID="sourceCategories" runat="server"
/>
Add a SqlDataSource control
</div>
</form>
</body>
</html>
19
ASP.NET (MacDonald)
Data Source Control Example
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:SqlDataSource ID="sourceCategories" runat="server"
ProviderName="System.Data.OleDb"
ConnectionString="<%$ ConnectionStrings:Deli %>"
/>
Add the namespace for the
correct provider as the value of
the “ProviderName” property
Use the alias of the connection
string that you created in the
web.config file
To refer to a connection string in
the aspx file, use this syntax
</div>
</form>
</body>
</html>
20
ASP.NET (MacDonald)
Data Source Control Example
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:SqlDataSource ID="sourceCategories" runat="server"
ProviderName="System.Data.OleDb"
ConnectionString="<%$ ConnectionStrings:Deli %>"
SelectCommand="select id, category from categories"
/>
The SQL command can be added
as an inline string
The SqlDataSource control can
have one each of the following
commands:
</div>
</form>
</body>
</html>
•
•
•
•
SelectCommand
InsertCommand
UpdateCommand
DeleteCommand
(Although some controls do not support
all operations)
21
ASP.NET (MacDonald)
Data Source Control Example
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:SqlDataSource ID="sourceCategories" runat="server"
ProviderName="System.Data.OleDb"
ConnectionString="<%$ ConnectionStrings:Deli %>"
SelectCommand="select id, category from categories"
/>
<asp:Label ID="categoriesLabel" runat="server"
Text="Categories: " />
</div>
</form>
</body>
</html>
Add the label control
22
ASP.NET (MacDonald)
Data Source Control Example
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:SqlDataSource ID="sourceCategories" runat="server"
ProviderName="System.Data.OleDb"
ConnectionString="<%$ ConnectionStrings:Deli %>"
SelectCommand="select id, category from categories"
/>
<asp:Label ID="categoriesLabel" runat="server"
Text="Categories: " />
<asp:DropDownList ID="categoriesDropDownList" runat="server"
DataSourceID="sourceCategories"
/>
</div>
</form>
</body>
</html>
Add the DropDownList control
Link the data object with the control
using the “DataSourceID” property
23
ASP.NET (MacDonald)
Data Source Control Example
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:SqlDataSource ID="sourceCategories" runat="server"
ProviderName="System.Data.OleDb"
ConnectionString="<%$ ConnectionStrings:Deli %>"
SelectCommand="select id, category from categories"
/>
<asp:Label ID="categoriesLabel" runat="server"
Text="Categories: " />
<asp:DropDownList ID="categoriesDropDownList" runat="server"
DataSourceID="sourceCategories"
DataTextField="category"
DataValueField="id"
/>
</div>
</form>
</body>
</html>
Set the “DataTextField” property
and the “DataValueField” property
with the values of the fields
specified in the query
24
ASP.NET (MacDonald)
Advantages of the Data Source Controls

It was not necessary to write any C# code, such as
creating the Connection, Command, and Reader
objects; using try-catch-finally blocks; or using a loop
to populate the list

The control could have been added and configured by
using Visual Studio features such as the Design view,
the Toolbox, and the Properties editor
25
ASP.NET (MacDonald)
Data Controls
Rich data controls allow you to bind an entire table of
data, using a Data Source Control
•
GridView
•
DetailsView
•
FormView
26
ASP.NET (MacDonald)
GridView Example
Add to your Web Site:
• The db.mdb file
• A connection string in web.config
• A SqlDataSource control in the aspx file
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:SqlDataSource ID="menuItemsSource" runat="server"
ProviderName="System.Data.OleDb"
ConnectionString="<%$ ConnectionStrings:Deli %>"
SelectCommand="select * from MenuItems"
/>
</div>
</form>
</body>
</html>
27
ASP.NET (MacDonald)
GridView Example
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:SqlDataSource ID="menuItemsSource" runat="server"
ProviderName="System.Data.OleDb"
ConnectionString="<%$ ConnectionStrings:Deli %>"
SelectCommand="select * from MenuItems"
/>
Add the GridView control
<asp:GridView ID="GridView1" runat="server"
DataSourceID="menuItemsSource" />
</div>
</form>
</body>
</html>
Bind it to the SqlDataSource
28
ASP.NET (MacDonald)
GridView Example
Change the GridView control in
the Design view
• Auto Format...
• Edit Columns...
• Add New Column...
• Enable Paging
• Enable Sorting
29
Master Pages
ASP.NET (MacDonald)
Master Pages
 Used to define the layout of multiple pages in your
web site
•
Page templates are used to define features such as headers, footers,
navigation panels
• Use the .master file extension
•
Must be used with content pages which are inserted at the location of
the ContentPlaceHolder controls
 Content pages
•
Inserted into the master page layout
•
Acquires the layout of the master page
31
ASP.NET (MacDonald)
Master Page Example
To add a master page:
1. In the “Website” menu, select “Add New Item”
2. Select “Master Page”
<%@ Master Language="C#" AutoEventWireup="true"
CodeFile="MasterPage.master.cs" Inherits="MasterPage" %>
The master directive
identifies the master
page
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<asp:ContentPlaceHolder id="head" runat="server">
</asp:ContentPlaceHolder>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:ContentPlaceHolder id="ContentPlaceHolder1" runat="server">
Content pages go
into the
ContentPlaceHolder
</asp:ContentPlaceHolder>
</div>
</form>
</body>
</html>
32
ASP.NET (MacDonald)
Master Page Example
•
Add elements to the master page outside of the ContentPlaceHolder
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<asp:ContentPlaceHolder id="head" runat="server">
</asp:ContentPlaceHolder>
<style type="text/css">
.banner
{
font-family: Arial, Helvetica, sans-serif;
background-color: #800000;
color: #FFFFFF;
font-weight: bold;
padding: 5px;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<div>
<h1 class="banner">Garden Fresh Sandwich Deli</h1>
<asp:ContentPlaceHolder id="ContentPlaceHolder1" runat="server">
</asp:ContentPlaceHolder>
</div>
</form>
</body>
</html>
33
ASP.NET (MacDonald)
Master Page Example
To add a content page:
1. In the “Website” menu, select “Add New Item”
2. Select “Web Form”
3. Check “Select master page” in the dialog
The page directive
connects it to the
master page
<%@ Page Title="" Language="C#" MasterPageFile="~/MasterPage.master"
AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<asp:Content ID="Content1" ContentPlaceHolderID="head" Runat="Server">
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1"
Runat="Server">
</asp:Content>
The page content should be inserted into
this Content element
The Content element with the “head” id
should contain styles or scripts that are
specific to a particular content page
34
ASP.NET (MacDonald)
Master Page Example
<%@ Page Title="Home page" Language="C#" MasterPageFile="~/MasterPage.master"
AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<asp:Content ID="Content1" ContentPlaceHolderID="head" Runat="Server">
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1"
Runat="Server">
<asp:DropDownList ID="DropDownList1" runat="server">
<asp:ListItem Value="1">Sandwiches</asp:ListItem>
<asp:ListItem Value="2">Salads</asp:ListItem>
<asp:ListItem Value="3">Pasta</asp:ListItem>
</asp:DropDownList>
</asp:Content>
35
References
MacDonald, Matthew, Beginning ASP.NET 3.5 in C# 2008: From Novice to Professional,
Second Edition. Apress, 2007.
Walther, Stephen. ASP.NET 3.5 Unleashed. SAMS, 2008.
36