ASP.NET Part 4

1. Instructor: Charles Moen CSCI/CINF 4230 ASP.NETPart 4

2. (Continued from last week) ADO.NET

3. Disconnected Data Access Fetch the data and store it in memory Steps to query the database with direct data access Create Connection and Command objects Create DataSet and DataAdapter objects Retrieve information from the database with the DataAdapter object and add it to the DataSet object Close the connection Interact with the data in your code ASP.NET (MacDonald)‏ In your C# code, import the correct ADO.NET namespaces using System.Data; using System.Data.OleDb; using System.Web.Configuration;

4. Deli Menu Demo ASP.NET (MacDonald)‏ • 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

5. Demo web.config ASP.NET (MacDonald)‏ • 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

6. Demo Default.aspx.cs ASP.NET (MacDonald, Walther)‏ using System; using System.Text; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using System.Data; using System.Data.OleDb; using System.Web.Configuration; public partial class _Default : System.Web.UI.Page { private string connectionString = WebConfigurationManager.ConnectionStrings["Deli"].ConnectionString; private DataSet ds; The DataSetstores the data that was retrieved from the database, so it can be considered to be an in-memory database Add a DataSet object

7. Demo Page_Load ASP.NET (MacDonald)‏ The DataAdapteris the object that is used to transfer data from the physical database to the DataSet, the “in-memory” database 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); } } } 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

8. Demo Page_Load ASP.NET (MacDonald)‏ 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

9. DemocategoriesDropDownList_SelectedIndexChanged ASP.NET (MacDonald)‏ 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>"); sb.Append("<td>"); sb.Append(menuItemRow["price"].ToString()); sb.Append("</td>"); sb.Append("</tr>"); } } sb.Append("</table>"); resultsLabel.Text = sb.ToString(); } } An HTML table can be built by interacting with the data that is stored in memory inside the DataSet object

10. Data Binding

11. 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 ASP.NET (MacDonald)‏

12. Single-Value Binding Insert a data binding expression into the aspx code Could be a variable, property, or expression Call DataBind() in the code ASP.NET (MacDonald)‏ Opening and closing symbols <%# 2 + 2 %> A data binding expression <%# Request.QueryString["name"] %>

13. Single-Value Binding Example ASP.NET (MacDonald)‏ <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

14. 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? ASP.NET (MacDonald)‏ <%# Request.QueryString["name"] %> • What would the URL look like?

15. Repeated-Value Binding Create a data object, such as an ArrayList, and fill it with data Link the data object to a control Some controls that support data binding: ListBox, DropDownList, CheckBoxList, RadioButtonList, HtmlSelect, GridView, FormView, ListView Call DataBind() in the code ASP.NET (MacDonald)‏

16. Repeated-Value Binding Example ASP.NET (MacDonald)‏ <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 on the page

17. 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 ASP.NET (MacDonald)‏

18. Data Source Control Example ASP.NET (MacDonald)‏ <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <title></title> </head> <body> <form id="form1" runat="server"> <div> </div> </form> </body> </html> <?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 Then, add the connection string to the web.config file

19. Data Source Control Example ASP.NET (MacDonald)‏ <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" /> </div> </form> </body> </html> Add a SqlDataSource control

20. Data Source Control Example ASP.NET (MacDonald)‏ <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 %>" /> </div> </form> </body> </html> 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

21. Data Source Control Example ASP.NET (MacDonald)‏ <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" /> </div> </form> </body> </html> The SQL command can be added as an inline string The SqlDataSource control can have one each of the following commands: • SelectCommand • InsertCommand • UpdateCommand • DeleteCommand • (Although some controls do not support all operations)

22. Data Source Control Example ASP.NET (MacDonald)‏ <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

23. Data Source Control Example ASP.NET (MacDonald)‏ <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

24. Data Source Control Example ASP.NET (MacDonald)‏ <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

25. 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 ASP.NET (MacDonald)‏

26. Data Controls Rich data controls allow you to bind an entire table of data, using a Data Source Control GridView DetailsView FormView ASP.NET (MacDonald)‏

27. GridView Example ASP.NET (MacDonald)‏ 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>

28. GridView Example ASP.NET (MacDonald)‏ <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" /> <asp:GridView ID="GridView1" runat="server" DataSourceID="menuItemsSource" /> </div> </form> </body> </html> Add the GridView control Bind it to the SqlDataSource

29. GridView Example ASP.NET (MacDonald)‏ Change the GridView control in the Design view • Auto Format... • Edit Columns... • Add New Column... • Enable Paging • Enable Sorting

30. Master Pages

31. 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 ASP.NET (MacDonald)‏

32. Master Page Example ASP.NET (MacDonald)‏ To add a master page: In the “Website” menu, select “Add New Item” Select “Master Page” The master directive identifies the master page <%@ Master Language="C#" AutoEventWireup="true" CodeFile="MasterPage.master.cs" Inherits="MasterPage" %> <!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"> </asp:ContentPlaceHolder> </div> </form> </body> </html> Content pages go into the ContentPlaceHolder

33. Master Page Example ASP.NET (MacDonald)‏ • 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>

34. Master Page Example ASP.NET (MacDonald)‏ To add a content page: In the “Website” menu, select “Add New Item” Select “Web Form” 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

35. Master Page Example ASP.NET (MacDonald)‏ <%@ 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>

36. 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.