Here is the source for the Default.aspx
<%@ 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="artistInfo.css" rel="stylesheet" type="text/css" />
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
BackColor="White" BorderColor="#E7E7FF" BorderStyle="None" BorderWidth="1px"
CellPadding="3" DataKeyNames="ArtistID" DataSourceID="SqlDataSource1"
GridLines="Horizontal" onselectedindexchanged="GridView1_SelectedIndexChanged">
<AlternatingRowStyle BackColor="#F7F7F7" />
<Columns>
<asp:CommandField ShowSelectButton="True" />
<asp:BoundField DataField="ArtistID" HeaderText="ArtistID"
InsertVisible="False" ReadOnly="True" SortExpression="ArtistID" />
<asp:BoundField DataField="ArtistName" HeaderText="ArtistName"
SortExpression="ArtistName" />
</Columns>
<FooterStyle BackColor="#B5C7DE" ForeColor="#4A3C8C" />
<HeaderStyle BackColor="#4A3C8C" Font-Bold="True" ForeColor="#F7F7F7" />
<PagerStyle BackColor="#E7E7FF" ForeColor="#4A3C8C" HorizontalAlign="Right" />
<RowStyle BackColor="#E7E7FF" ForeColor="#4A3C8C" />
<SelectedRowStyle BackColor="#738A9C" Font-Bold="True" ForeColor="#F7F7F7" />
<SortedAscendingCellStyle BackColor="#F4F4FD" />
<SortedAscendingHeaderStyle BackColor="#5A4C9D" />
<SortedDescendingCellStyle BackColor="#D8D8F0" />
<SortedDescendingHeaderStyle BackColor="#3E3277" />
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:VenueTrackerConnectionString %>"
SelectCommand="SELECT [ArtistID], [ArtistName] FROM [Artist]">
</asp:SqlDataSource>
<asp:Xml ID="Xml1" runat="server"></asp:Xml>
</div>
</form>
</body>
</html>
Here is the code for Default.aspx.cs
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());
string sql = "Select artistbio from Artist where ArtistID=@ID";
SqlCommand cmd = new SqlCommand(sql, connect);
cmd.Parameters.AddWithValue("@ID", int.Parse(GridView1.SelectedRow.Cells[1].Text));
XmlReader xreader = null;
XmlDocument xdoc = new XmlDocument();
connect.Open();
xreader = cmd.ExecuteXmlReader();
xdoc.Load(xreader);
xreader.Close();
connect.Close();
Xml1.Document = xdoc;
Xml1.TransformSource = MapPath("autobio.xslt");
}
}
Here is the artistinfo.css file, such as it is
body
{
font-family:Verdana;
}
h2
{
color:Blue;
}
Here is the XSLT file, that should be imported into the project
<?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:a="http://www.venutracker.com/artistbio"
xmlns="http://www.w3.org/1999/xhtml"
>
<xsl:output method="xml" indent="yes"/>
<xsl:template match="/">
<h2>Artist Bio</h2>
<p> Artist Country <xsl:text>, </xsl:text>
<xsl:value-of select="/a:artistbio/a:background/a:country" />
</p>
<p>Artist Birthdate <xsl:text>, </xsl:text>
<xsl:value-of select="/a:artistbio/a:background/a:birthdate"/>
</p>
<ul>
<xsl:for-each select="a:artistbio/a:albums/a:album">
<li>
<xsl:value-of select="a:title"/>
<xsl:text>, </xsl:text>
<xsl:value-of select="a:date"/>
</li>
</xsl:for-each>
</ul>
</xsl:template>
</xsl:stylesheet>
here is the script so far
use VenueTracker
Alter table Artist
drop column ArtistBio
Create xml Schema collection artistBio_schema
As
'<?xml version="1.0" encoding="utf-8"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" targetNamespace="http://www.venutracker.com/artistbio" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="artistbio">
<xs:complexType>
<xs:sequence>
<xs:element name="background">
<xs:complexType>
<xs:sequence>
<xs:element name="country" type="xs:string" />
<xs:element name="birthdate" type="xs:string" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="albums">
<xs:complexType>
<xs:sequence>
<xs:element maxOccurs="unbounded" name="album">
<xs:complexType>
<xs:sequence>
<xs:element name="title" type="xs:string" />
<xs:element name="date" type="xs:unsignedShort" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>'
Alter table Artist
Add artistbio xml (artistbio_schema)
Select * from Artist
Update Artist
Set artistbio=
'<?xml version="1.0" encoding="utf-8"?>
<artistbio xmlns="http://www.venutracker.com/artistbio">
<background>
<country>United States</country>
<birthdate>5/1/1948</birthdate>
</background>
<albums>
<album>
<title>Blond on Blond</title>
<date>1965</date>
</album>
<album>
<title>Blood on the Tracks</title>
<date>1973</date>
</album>
</albums>
</artistbio>'
Where ArtistID=1
Update Artist
Set artistbio=
'<?xml version="1.0" encoding="utf-8"?>
<artistbio xmlns="http://www.venutracker.com/artistbio">
<background>
<country>United States</country>
<birthdate>6/1/1950</birthdate>
</background>
<albums>
<album>
<title>People</title>
<date>1967</date>
</album>
<album>
<title>Stoney End</title>
<date>1972</date>
</album>
</albums>
</artistbio>'
Where ArtistID=2
Update Artist
Set artistbio=
'<?xml version="1.0" encoding="utf-8"?>
<artistbio xmlns="http://www.venutracker.com/artistbio">
<background>
<country>United States</country>
<birthdate>5/1/1965</birthdate>
</background>
<albums>
<album>
<title>The Crane Wife</title>
<date>2010</date>
</album>
<album>
<title>The Hazards of love</title>
<date>2011</date>
</album>
</albums>
</artistbio>'
Where ArtistID=3
Update Artist
Set artistbio=
'<?xml version="1.0" encoding="utf-8"?>
<artistbio xmlns="http://www.venutracker.com/artistbio">
<background>
<country>United States</country>
<birthdate>5/1/1965</birthdate>
</background>
<albums>
<album>
<title>The Crane Wife</title>
<date>2010</date>
</album>
<album>
<title>The Hazards of love</title>
<date>2011</date>
</album>
</albums>
</artistbio>'
Where ArtistID=3
Update Artist
Set artistbio=
'<?xml version="1.0" encoding="utf-8"?>
<artistbio xmlns="http://www.venutracker.com/artistbio">
<background>
<country>United States</country>
<birthdate>6/1/1970</birthdate>
</background>
<albums>
<album>
<title>Hot fuss</title>
<date>2010</date>
</album>
<album>
<title>Day and Age</title>
<date>2011</date>
</album>
</albums>
</artistbio>'
Where ArtistID=4
Update Artist
Set artistbio=
'<?xml version="1.0" encoding="utf-8"?>
<artistbio xmlns="http://www.venutracker.com/artistbio">
<background>
<country>United States</country>
<birthdate>5/1/1950</birthdate>
</background>
<albums>
<album>
<title>Damn the torpedos</title>
<date>1979</date>
</album>
<album>
<title>Hard Promises</title>
<date>1981</date>
</album>
<album>
<title>Southern Accents</title>
<date>1985</date>
</album>
</albums>
</artistbio>'
Where ArtistID=5
No comments:
Post a Comment