First we made sure there was a description for every Venue
Use VenueTracker
Select * From Venue
Update Venue
Set VenueDescription=
'<?xml version="1.0" encoding="utf-8"?>
<venuedescription xmlns="http://www.venutracker.com/description">
<header>
<datelastupdated>7/26/2012</datelastupdated>
<contactname>Robert Zimmerman</contactname>
</header>
<body>
<description>
A stadium venue for sports and concerts at Seattle Center.
</description>
</body>
</venuedescription>'
Where VenueID=1
Update Venue
Set VenueDescription=
'<?xml version="1.0" encoding="utf-8"?>
<venuedescription xmlns="http://www.venutracker.com/description">
<header>
<datelastupdated>7/26/2012</datelastupdated>
<contactname>Sara Smith</contactname>
</header>
<body>
<description>
A large Tavern in Ballard. Ideal for intimate concerts with the newest bands.
</description>
</body>
</venuedescription>'
Where VenueID=3
Update Venue
Set VenueDescription=
'<?xml version="1.0" encoding="utf-8"?>
<venuedescription xmlns="http://www.venutracker.com/description">
<header>
<datelastupdated>7/26/2012</datelastupdated>
<contactname>Joe Manning</contactname>
</header>
<body>
<description>
A classy dive ideal for music. Located on Capital Hill.
</description>
</body>
</venuedescription>'
Where VenueID=4
Update Venue
Set VenueDescription=
'<?xml version="1.0" encoding="utf-8"?>
<venuedescription xmlns="http://www.venutracker.com/description">
<header>
<datelastupdated>7/26/2012</datelastupdated>
<contactname>Sue Lewis</contactname>
</header>
<body>
<description>
Like the tractor but bigger.
</description>
</body>
</venuedescription>'
Where VenueID=6
Delete From Venue Where VenueID = 7
Then we created an XSLT document to style and transform our venue description xml column
Here is the Venue.xslt
<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:msxsl="urn:schemas-microsoft-com:xslt" exclude-result-prefixes="msxsl"
xmlns="http://www.w3.org/1999/xhtml"
xmlns:d ="http://www.venutracker.com/description"
>
<xsl:output method="xml" indent="yes"/>
<xsl:template match="/">
<h2>Venue Description</h2>
<p>
<strong>Date Last Updated:</strong>
<xsl:value-of
select ="d:venuedescription/d:header/d:datelastupdated"/>
</p>
<p>
<strong>Contact Person: </strong>
<xsl:value-of
select="d:venuedescription/d:header/d:contactname"/>
</p>
<hr/>
<p>
<xsl:value-of
select ="d:venuedescription/d:body/d:description"/>
</p>
</xsl:template>
</xsl:stylesheet>
Next we Created a web page. we added two controls, a GridView and an XML control. For the GridView we used the wizard to connect it to the database. We also styled it and added a select button. Here is the code for Default.aspx. Just copying the source will reproduce all the wizard did.
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!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>
<link href="VenueDescriptionTest.css" rel="stylesheet" type="text/css" />
</head>
<body>
<form id="form1" runat="server">
<div>
<h1>Venues</h1>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
BackColor="White" BorderColor="#3366CC" BorderStyle="None" BorderWidth="1px"
CellPadding="4" DataKeyNames="VenueID" DataSourceID="SqlDataSource1"
onselectedindexchanged="GridView1_SelectedIndexChanged">
<Columns>
<asp:CommandField ShowSelectButton="True" />
<asp:BoundField DataField="VenueID" HeaderText="VenueID" InsertVisible="False"
ReadOnly="True" SortExpression="VenueID" />
<asp:BoundField DataField="VenueName" HeaderText="VenueName"
SortExpression="VenueName" />
<asp:BoundField DataField="VenueAddress2" HeaderText="VenueAddress2"
SortExpression="VenueAddress2" />
<asp:BoundField DataField="VenueCity" HeaderText="VenueCity"
SortExpression="VenueCity" />
<asp:BoundField DataField="VenueState" HeaderText="VenueState"
SortExpression="VenueState" />
<asp:BoundField DataField="VenuePhone" HeaderText="VenuePhone"
SortExpression="VenuePhone" />
<asp:BoundField DataField="VenueCapacity" HeaderText="VenueCapacity"
SortExpression="VenueCapacity" />
<asp:CheckBoxField DataField="VenueAgeRestriction"
HeaderText="VenueAgeRestriction" SortExpression="VenueAgeRestriction" />
<asp:BoundField DataField="VenueURL" HeaderText="VenueURL"
SortExpression="VenueURL" />
</Columns>
<FooterStyle BackColor="#99CCCC" ForeColor="#003399" />
<HeaderStyle BackColor="#003399" Font-Bold="True" ForeColor="#CCCCFF" />
<PagerStyle BackColor="#99CCCC" ForeColor="#003399" HorizontalAlign="Left" />
<RowStyle BackColor="White" ForeColor="#003399" />
<SelectedRowStyle BackColor="#009999" Font-Bold="True" ForeColor="#CCFF99" />
<SortedAscendingCellStyle BackColor="#EDF6F6" />
<SortedAscendingHeaderStyle BackColor="#0D4AC4" />
<SortedDescendingCellStyle BackColor="#D6DFDF" />
<SortedDescendingHeaderStyle BackColor="#002876" />
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:VenueTrackerConnectionString %>"
SelectCommand="SELECT [VenueID], [VenueName], [VenueAddress2], [VenueCity], [VenueState], [VenuePhone], [VenueCapacity], [VenueAgeRestriction], [VenueURL] FROM [Venue]">
</asp:SqlDataSource>
<asp:Xml ID="Xml1" runat="server"></asp:Xml>
</div>
</form>
</body>
</html>
You will also need the web.config file. It contains the connection string
<?xml version="1.0"?>
<!--
For more information on how to configure your ASP.NET application, please visit
http://go.microsoft.com/fwlink/?LinkId=169433
-->
<configuration>
<connectionStrings>
<add name="VenueTrackerConnectionString" connectionString="Data Source=localhost;Initial Catalog=VenueTracker;Integrated Security=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
<system.web>
<compilation debug="false" targetFramework="4.0" />
</system.web>
</configuration>
Finally, here is the C# code.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data; //add these four libraries
using System.Data.SqlClient;
using System.Xml;
using System.Configuration;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)
{
SqlConnection connect = new SqlConnection(ConfigurationManager.ConnectionStrings["VenueTrackerConnectionString"].ToString());
XmlDocument xDoc = new XmlDocument();
string sql = "Select venueDescription from Venue where VenueID = @ID";
SqlCommand cmd = new SqlCommand(sql, connect);
cmd.Parameters.AddWithValue("@ID", int.Parse(GridView1.SelectedRow.Cells[1].Text));
XmlReader xreader = null;
connect.Open();
xreader = cmd.ExecuteXmlReader();
xDoc.Load(xreader);
xreader.Close();
connect.Close();
Xml1.Document = xDoc;
Xml1.TransformSource = MapPath("Venue.xslt");
}
}
No comments:
Post a Comment