ITC Resources: July 2012

Tuesday, July 31, 2012

Service Broker and system tables

Use master

Alter Database Automart
Set Enable_Broker

Use Automart
--create reply and request message types
Create Message Type
[//AWDB/1DBSample/RequestMessage]
Validation = Well_Formed_xml
GO
Create Message Type
[//AWDB/1DBSample/ReplyMessage]
Validation=well_formed_xml
Go
--create a contract with the message types
Create Contract [//AWDB/1DBSample/SampleContract]
([//AWDB/1DBSample/RequestMessage]
Sent by initiator,
[//AWDB/1DBSample/ReplyMessage]
sent by Target)
Go
--create queue for messages
Create Queue TargetQueue1Db

--create a service for target and initiator

Create service 
[//AWDB/DBSample/TargetService]
On Queue TargetQueue1Db
([//AWDB/1DBSample/SampleContract]) 

Create Queue InitiatorQueue1DB
Create Service
[//AWDB/1DBSample/InitiatorService]
On Queue InitiatorQueue1DB

Declare @initDigHandle UniqueIdentifier
Declare @requestMessage nvarchar(100);

Begin Transaction
Begin Dialog @initDigHandle
From Service
[//AWDB/1DBSample/InitiatorService]
To Service
N'//AWDB/DBSample/TargetService'
on Contract
[//AWDB/1DBSample/SampleContract]
With
Encryption=off

Select @requestMessage=N'Message for Target Service';
Send
on Conversation @initDigHandle
Message type
[//AWDB/1DBSample/RequestMessage]
(@RequestMessage)
Select @requestMessage As SentRequestMsg;
Commit Transaction

Declare @RecvReqDigHandle uniqueidentifier
Declare @RecvReqMsg Nvarchar(100)
Declare @recvReqMsgName sysname

Begin Tran
Waitfor
(
    Receive top(1)
    @RecvReqDigHandle=conversation_handle,
    @RecvReqMsg=message_body,
    @recvReqMsgName=message_type_name
   From TargetQueue1DB 
),
TimeOut 1000
Select @RecvReqMsg As RecievedRequestMsg
if  @recvReqMsgName=N'//AWDB/1DBSample/RequestMessage'
Begin
Declare @replyMsg NVarchar(100)
Select @replyMsg=N'Message for initiator service';
Send on Conversation  @RecvReqDigHandle
Message type
[//AWDB/1DBSample/ReplyMessage]
(@replymsg)
End Conversation  @RecvReqDigHandle
End
Select @replyMsg as SentReplyMsg
Commit transaction
GO

Declare @RecvReqDigHandle uniqueidentifier
Declare @RecvReqMsg Nvarchar(100)
Begin Transaction
Waitfor
(
   Receive top(1)
    @RecvReqDigHandle=conversation_handle,
    @RecvReqMsg=message_body
   From InitiatorQueue1DB 
),
Timeout 1000
End
Conversation @RecvReqDigHandle
Select @RecvReqMsg as RecievedReplyMsg
Commit Transaction

use VenueTracker
exec sp_help 

Use Automart
--there are over 200 of these in sql server
Select name from sys.Tables
Select * from sys.Databases
Select * from sys.Tables
Select * from sys.all_columns where object_id=357576312 and Is_nullable=1
Select * from sys.backup_devices
Select * from sys.database_files
Select * from sys.xml_schema_collections

Select * from sys.conversation

Xml for Artist Bio

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

Monday, July 30, 2012

Simple check Box Example

Here is a picture of the form running

Here is the code for the Form1.java and panel

import java.awt.FlowLayout;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;

import javax.swing.*;
public class Form1 {
 JFrame frame= new JFrame("Checkbox test");
 JPanel panel = new JPanel(new FlowLayout());
 JCheckBox chk1 = new JCheckBox("0ne");
 JCheckBox chk2=new JCheckBox("Two");
 JButton button = new JButton("test");
 
 JLabel label = new JLabel();
 
 public Form1(){
 
 panel.add(chk1);
 panel.add(chk2);
 panel.add(button);
 button.addActionListener(new TestAction());
 panel.add(label);
 frame.add(panel);
 frame.setBounds(100,100,300,300);
 frame.setVisible(true);
 
 }
 
 private class TestAction implements ActionListener{

  @Override
  public void actionPerformed(ActionEvent arg0) {
   String s = "";
  
   if(chk1.isSelected()){
    s+=chk1.getText() + " ";
   }
   
   if (chk2.isSelected()){
    s+=chk2.getText() + " ";
   
  }
   label.setText(s);
  
 }
 }
}

Here is Program.java


public class Program {

 public static void main(String[] args){
  Form1 f = new Form1();
  
 }
}

Thursday, July 26, 2012

XSLT and Reading XML from SQL Server

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");
          
    }
}

Wednesday, July 25, 2012

Swing Example

Here is a picture of the form operating

Here is the Program.java class

package com.spconger.www;

public class Program {

 /**
  * @param args
  */
 public static void main(String[] args) {
  MainJFrame m = new MainJFrame();
  m.setTitle("tip Calculator");
  m.setBounds(200,200,350, 350);
  
  m.setVisible(true);
 }

}

Here is the MainJFrame.java class

package com.spconger.www;

import javax.swing.*;

public class MainJFrame extends JFrame{

 /**
  * this method creates the JFrame
  * and adds the panel
  */
 private static final long serialVersionUID = 1L;
 
 public MainJFrame(){
  //JFrame frame = new JFrame();
  setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
  JPanel mainPanel= new MealTipPanel();
  this.add(mainPanel);
  
  
 }
 
 

}

Here is the MealTipPanel.Java class

package com.spconger.www;

import java.awt.BorderLayout;
import java.awt.FlowLayout;
import java.awt.GridLayout;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;

import javax.swing.*;

public class MealTipPanel extends JPanel{

 /**
  * 
  */
 private static final long serialVersionUID = 1L;

 //declare all my objects
 JPanel panel;
 JPanel buttonPanel;
 JLabel lblAmount;
 JTextField txtAmount;
 JLabel lblChooseTipPercent;
 JRadioButton rbFifteenPercent;
 JRadioButton rbTenPercent;
 JRadioButton rbTwentyPercent;
 JLabel lblTaxPercent;
 JTextField txtTaxPercent;
 JLabel lblTipAmount;
 JLabel lblTaxAmount;
 JLabel lblTotalAmount;
 
 JButton btnCalculate;
 JButton btnExit;
 
 private Tip tip;
 
 
 public MealTipPanel(){
  createPanel();
 }
 
 private void createPanel(){
  panel = new JPanel();
  panel.setLayout(new GridLayout(10,2,5,5));
  
  lblAmount = new JLabel("Enter the Pre Tax Amount");
  txtAmount = new JTextField(10);
  
  lblChooseTipPercent = new JLabel("Choose the Tip Percent");
  rbTenPercent = new JRadioButton("Ten Percent");
  rbFifteenPercent = new JRadioButton("Fifteen Percent");
  rbTwentyPercent = new JRadioButton("Twenty Percent");
  
  ButtonGroup group = new ButtonGroup();
  group.add(rbTenPercent);
  group.add(rbFifteenPercent);
  group.add(rbTwentyPercent);
  
  lblTaxPercent=new JLabel("Enter Tax Percent");
  txtTaxPercent= new JTextField(10);
  
  lblTipAmount = new JLabel();
  lblTaxAmount= new JLabel();
  lblTotalAmount = new JLabel();
  
  panel.add(lblAmount);
  panel.add(txtAmount);
  panel.add(lblChooseTipPercent);
  panel.add(rbTenPercent);
  panel.add(rbFifteenPercent);
  panel.add(rbTwentyPercent);
  panel.add(lblTaxPercent);
  panel.add(txtTaxPercent);
  panel.add(lblTipAmount);
  panel.add(lblTaxAmount);
  panel.add(lblTotalAmount);
  
  buttonPanel = new JPanel();
  buttonPanel.setLayout(new FlowLayout(FlowLayout.RIGHT));
  
  btnCalculate = new JButton("Calculate");
  btnCalculate.addActionListener(new CalculateListener());
  buttonPanel.add(btnCalculate);
  
  btnExit = new JButton("Exit");
  btnExit.addActionListener(new ExitListener());
  buttonPanel.add(btnExit);
  
  this.setLayout(new BorderLayout());
  this.add(panel, BorderLayout.CENTER);
  this.add(buttonPanel, BorderLayout.SOUTH);
  
 }//end create panel
 
 private class ExitListener implements ActionListener{

  @Override
  public void actionPerformed(ActionEvent e) {
   // TODO Auto-generated method stub
   System.exit(0);
  }
  
 }//end of ExitListenerClass
 
 private class CalculateListener implements ActionListener{

  @Override
  public void actionPerformed(ActionEvent e) {
   tip = new Tip();
   //set the field values
   tip.setMealAmount(Double.parseDouble(txtAmount.getText()));
   
   double percent=0;
   if (rbTenPercent.isSelected()){
    percent=.1;
   }
   else if (rbFifteenPercent.isSelected()){
    percent=.15;
   }
   else if (rbTwentyPercent.isSelected())
   {
    percent=.2;
   }
    
   tip.setTipPercent(percent);
   tip.setTaxPercent(Double.parseDouble(txtTaxPercent.getText()));
   
   lblTipAmount.setText("The tip amount is " 
             + Double.toString(tip.calculateTip()));
   
   lblTaxAmount.setText("The tax amount is " +
            Double.toString(tip.calculateTax()));
   
   lblTotalAmount.setText("The Total is " +
     Double.toString(tip.calculateTotal()));
   
  }
  
 }
 
 
}//end class

Here is the Tip.java class

package com.spconger.www;

public class Tip {
 private double mealAmount;
 private double tipPercent;
 private double taxPercent;
 
 public double getMealAmount() {
  return mealAmount;
 }
 public void setMealAmount(double mealAmount) {
  this.mealAmount = mealAmount;
 }
 public double getTipPercent() {
  return tipPercent;
 }
 public void setTipPercent(double tipPercent) {
  if (tipPercent >=1){
   tipPercent=tipPercent/100;
  }
  this.tipPercent = tipPercent;
 }
 public double getTaxPercent() {
 
  return taxPercent;
 }
 public void setTaxPercent(double taxPercent) {
  if (taxPercent >= 1)
  {
   taxPercent=taxPercent /100;
  }
  this.taxPercent = taxPercent;
 }
 
 public double calculateTip(){
  return getMealAmount() * getTipPercent();
 }
 
 public double calculateTax(){
  return getMealAmount() * getTaxPercent();
 }
 
 public double calculateTotal(){
  return getMealAmount() + calculateTip() + calculateTax();
 }
 

}

Tuesday, July 24, 2012

XML in SQL Server

Use VenueTracker

Select * from Customer
for xml raw

Select * from Customer
for xml raw, elements, root('customers')

Select * from Customer
for xml raw('customer'), elements, root('customers')

Select Distinct CustomerLastName, customerEmail, ArtistName
From Customer cust
Inner join CustomerArtist custartist
on cust.CustomerID=custArtist.ArtistID
inner Join Artist art
on art.ArtistID=custartist.ArtistID
order by CustomerLastName
for xml auto, elements, root ('root')

Select * From customerArtist

Use Automart

Select * From Customer.AutoService

use VenueTracker
Create xml schema collection schema_VenueDescription
As
'<?xml version="1.0" encoding="utf-8"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified"
           targetNamespace="http://www.venutracker.com/description" 
           xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="venuedescription">
    <xs:complexType>
      <xs:sequence>
       <xs:element name="header">
         <xs:complexType>
            <xs:sequence>
              <xs:element name="datelastupdated" type="xs:string" />
              <xs:element name="contactname" type="xs:string" />
            </xs:sequence>
          </xs:complexType>
        </xs:element>
        <xs:element name="body">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="description" type="xs:string" />
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>'

Alter table Venue
Drop column VenueDescription

Alter Table Venue
Add VenueDescription Xml(schema_VenueDescription)

Select * From Venue

Update Venue
Set VenueDescription ='<?xml version="1.0" encoding="utf-8"?>
<venuedescription xmlns="http://www.venutracker.com/description">
  <header>
     <contactname>John Doe</contactname> 
    <datelastupdated>2012-7-1</datelastupdated>

  </header>
  <body>
 
   <description>
      The Gorge Amphitheatre is located in the Columbia River in George, 
      Washington. It offers lawn-terrace seating. Administered by Live Nation, 
      it is considered one of the premier and most scenic concert locations 
      not just in North America, but the world.[1] 
      The venue has been a host to big name performers like The Who, 
      David Bowie, Coldplay, Tom Petty, Pearl Jam, Dave Matthews Band, 
      Phish, and Rush. The venue offers sweeping and 
      majestic views of the Columbia River as it skirts the foothills 
      of the Cascade Range southbound, as well as extreme eastern Kittitas 
      County and extreme western Grant County. It is also known for its spectacular 
      views of the Columbia gorge canyon (not to be confused with the 
      Columbia River Gorge proper, which begins several tens of miles 
      further downstream as the river turns west to cross through the 
      Cascades toward Portland and the Pacific Ocean).

    </description> 
    
  </body>
  
</venuedescription>'

Where VenueId=2

Insert into venue( VenueName, VenueAddress1, VenueAddress2, VenueCity, VenueState, VenuePhone, VenueCapacity, VenueAgeRestriction, VenueURL, VenueDescription)
Values ('jazz Alley', null, '2033 6th Ave South', 'Seattle','WA','2065553214', 200,1,'http://jazzalley.com','<?xml version="1.0" encoding="utf-8"?>
<venuedescription xmlns="http://www.venutracker.com/description">
  <header>
 <datelastupdated>2012-23-1</datelastupdated> 
    <contactname>Jane Doe</contactname> 
    

  </header>
  <body>
 
    <description>
      An intimate venue with food and drinks for viewing world renowned artists.
    </description> 
    
  </body>
  
</venuedescription>')

use Automart

Select * From customer.AutoService

Select ServiceName, Serviceprice, 
ServiceDescription.query('declare namespace d="http://www.automart.com/servicedescription"; d:servicedescription/d:parts/d:part/d:perpartprice') as parts
From Customer.AutoService
Where ServiceName='tune up'

Wednesday, July 18, 2012

Reading and Writing Text files

Here is the file for writing text files. We tried to make it as generic as possible so it can be reused in a variety of contexts. One weakness of the class is that it is up to the user to call the closeFile method. A text file cannot be read or used if it is not closed

WriteFile.java

package com.spconger.www;

import java.io.*;

public class WriteFile {
 
 
 private String path;
 private PrintWriter writer;
 
 public WriteFile(String path) throws IOException{
  this.path=path;
  createFile();
 }
 
 private void createFile() throws IOException{
  
  
  FileWriter outFile = new FileWriter(path, true);
  writer = new PrintWriter(outFile);
  
 }
 public void addText(String content){
  
  writer.println(content);
 
 }
 
 public void closeFile(){
  writer.close();
 }
 

}

Here is the ReadFile.Java

package com.spconger.www;

import java.io.*;

public class ReadFile {
 
 private String path;
 
 public ReadFile(String path){
  this.path=path;
 }
 
 public String getText() throws FileNotFoundException ,IOException{
  String content="";
  FileInputStream fstream = new FileInputStream(path);
  DataInputStream in = new DataInputStream(fstream);
  BufferedReader br = new BufferedReader(new InputStreamReader(in));
  String strLine;
  while ((strLine=br.readLine()) != null){
   content += (strLine + "\n");
  }
  
  return content;
 }

}

Here is the Program.java where we test our classes

package com.spconger.www;

import java.io.IOException;



public class Program {

 /**
  * @param args
  * 
  */
 public static void main(String[] args) {
  
   try {
    WriteFile write = new WriteFile("email.txt");
    write.addText("Steve" + ", steven.conger@seattlecolleges.edu");
    write.addText("George" + ", george@gmail.com");
    write.closeFile();
    
    ReadFile read = new ReadFile("email.txt");
    System.out.println(read.getText());
    
   } catch (IOException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
   }
   
  
   
  }
  

 }

Tuesday, July 17, 2012

Hashing, Snapshots, Full Text Indexes

Use Automart
--inserting non english characters
--into a table
--the N stands for unicode
--the underlying type must be Nvarchar not varchar
Insert into Person(LastName, FirstName)
Values(N'κονγεροσ',N'Στεφονοσ')

--unicode character set
--1st 255 8 bits Ascii
--32000 16 bit character
--   24 bit character
--   32 bit character
--   64 bit character

Select * From Person

Select * From Customer.RegisteredCustomer

--hashbytes is a function that "hashes"
--a value. You can use various hash methods
--MD5 is one
Declare @password  varbinary(500)
Set @password=Hashbytes('MD5','jpass')
Select @password

--uses shai hash method
Declare @password  varbinary(500)
Set @password=Hashbytes('sha1','jpass')
Select @password

--add a column to the Registered customer table
Alter table Customer.RegisteredCustomer
Add hashedPassword varbinary(500)

Select * from Customer.RegisteredCustomer

--add a hash for the first customer
Update Customer.RegisteredCustomer
Set hashedPassword=HASHBYTES('MD5','jpass')
Where RegisteredCustomerID=1

--simulate checking the hash as if
--for a login
Declare @Password Varbinary(500)
Set @password = HASHBYTES('MD5','jpass')
if Exists
 (Select Hashedpassword from Customer.RegisteredCustomer
   Where hashedPassword=@password)
Begin
 Print 'Login successful'
End
Else
Begin
   Print 'Login Failed'
End

--snapshots
--a snapshot takes a picture of a database
--at a moment in time
--it only physically stores the data if it has changed in 
--the underlying database. the original form is copied to
--the snapshot
Create Database Automart_Snapshot
On
(Name ='Automart', Filename='C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Automart_Snapshot.ds')
As 
Snapshot of Automart

Use Automart_Snapshot

Select * From Person

Use Automart
Delete From Person where Personkey=54
--make some changes in the underlying database and then 
--compare the two database
Update Person
Set FirstName='Jason'
where personkey=1

--in an emergancy you can recover
--a database from a snapshot
use Master
Restore Database Automart from Database_Snapshot='Automart_Snapshot'

--Full Text Catalog
use Master

--add a filegroup
Alter Database Automart
Add Filegroup FullTextCatalog

use Automart
--add a table with some text
Create Table TextTest
(
   TestId int identity (1,1) primary key,
   TestNotes Nvarchar(255)
)

--insert text
Insert into TextTest(TestNotes)
Values('For test to be successful we must have a lot of text'),
('The test was not successful. sad face'),
('there is more than one test that can try a man'),
('Success is a relative term'),
('It is a rare man that is always successful'),
('The root of satisfaction is sad'),
('men want success')

Select * From TextTest

--create full text catalog
Create FullText Catalog TestDescription
on Filegroup FullTextCatalog

--Create a full text index
Create FullText index on textTest(TestNotes)
Key Index PK__TextTest__8CC33160412EB0B6
on TestDescription
With Change_tracking auto

--run queries on the full text catalog

--find all instances that have the word "sad"
Select TestID, TestNotes 
From TextTest
Where FreeText(TestNotes, 'sad')

--do the same with successful
Select TestID, TestNotes 
From TextTest
Where FreeText(TestNotes, 'successful')

Select TestID, TestNotes 
From TextTest
Where Contains(TestNotes, '"success"')

--look for any words containing the letters "success"
--the * is a wildcard
Select TestID, TestNotes 
From TextTest
Where Contains(TestNotes, '"success*"')

--looks for all grammatical forms of a word
Select TestID, TestNotes 
From TextTest
Where Contains(TestNotes, ' Formsof (Inflectional, man)')

--finds words near another word
Select TestID, TestNotes 
From TextTest
Where Contains(TestNotes, ' not near successful')

Select TestID, TestNotes 
From TextTest
Where Contains(TestNotes, ' sad near successful')

Functions and A Trigger

Use VenueTracker



--to do
--artist take venue take Sellout

--Function for sellout
--function for Venue take
--function for Artist take

--trigger to make a list of people to notify about upcoming events
Go

--this function has some problems. It duplicates capacity and so gives a false
--amount for shows that have multiple ticket vendors
Alter Function Venue.func_SellOut
(@ticketPrice decimal(5,2),
@Capacity int) --these are user provided parmeters
returns Decimal(9,2) --the return type for the function
As --begin function
Begin--functions require a begin and end statement
Declare @Total Decimal(9,2) --declare a variable
Set @Total= @ticketPrice * @Capacity --set the value
return @Total --return it
End
GO

Select VenueName, s.ShowID, TicketPrice, VenueCapacity, 
Venue.func_SellOut(TicketPrice, VenueCapacity) as Total
From Venue v
inner Join Show s
on s.VenueID = v.VenueID
inner join TicketOutlet t
on t.ShowID=s.ShowID

Select VenueName, s.ShowID, VenueCapacity, 
Sum(Venue.func_SellOut(TicketPrice, VenueCapacity) )as Total
From Venue v
inner Join Show s
on s.VenueID = v.VenueID
inner join TicketOutlet t
on t.ShowID=s.ShowID
Group by VenueName, s.ShowID,  VenueCapacity

Select * From TicketOutlet

Alter Table TicketOutlet
Add TotalTickets int

Select * From TicketOutlet
Select * From Venue
Go
Create Function Venue.func_CurrentSales
(@Totaltickets int, @TicketsRemaining int, @TicketPrice Decimal(5,2))
Returns Decimal(9,2)
As
Begin
Declare @TicketsSold int
Set @TicketsSold=@Totaltickets-@TicketsRemaining
Return @TicketsSold * @TicketPrice

End
--test the function
Select VenueName, s.ShowID, 
Venue.func_CurrentSales(TotalTickets, TicketsRemaining, ticketPrice)
As TotalSale
From Venue v
inner Join Show s
On v.VenueID=s.VenueID
Inner Join TicketOutlet t
on s.ShowID=t.ShowID
Go
--this is a view using the function
--it also uses a sum to combine the revenues
--of more than one ticket outlet
Go
Create View Venue.CurrentSalesSummary
As
Select VenueName, s.ShowID, 
Sum(Venue.func_CurrentSales(TotalTickets, TicketsRemaining, ticketPrice))
As TotalSale
From Venue v
inner Join Show s
On v.VenueID=s.VenueID
Inner Join TicketOutlet t
on s.ShowID=t.ShowID
Group by VenueName, s.ShowID

Select * From Venue.CurrentSalesSummary 
Where VenueName='Key Arena'

go
--this does the same as the view
--but requires the name of a venue
Create Proc Venue.usp_SalesSummary
@VenueName nvarchar(255)
As
Select VenueName, s.ShowID, 
Sum(Venue.func_CurrentSales(TotalTickets, TicketsRemaining, ticketPrice))
As TotalSale
From Venue v
inner Join Show s
On v.VenueID=s.VenueID
Inner Join TicketOutlet t
on s.ShowID=t.ShowID
Where VenueName=@VenueName
Group by VenueName, s.ShowID

Exec Venue.usp_SalesSummary 'Columbia Gorge'

--lets do a trigger
--Trigger will be triggered whenever there is an insert on the show table
--and it will make a table of people to be notified of upcoming events
--check the artistid and see if it matches any artistid in CustomerArtist Table where the ArtistAlert is set to true
--create a table of people to be alterted which will consist of the customer email, the artist name and the showvenue and date

Go
Create trigger tr_NotifyCustomers
on Show --the table the trigger applies to
For Insert  -- the even
As
Declare @ArtistID int
--inserted is a table that exists in the temp database
--it only exists for the duration of the transaction
--about a millesecond
Select @ArtistID=ArtistID from inserted
--check to see if someone wants to be alerted
--about the artist
If Exists
 (Select ArtistID from CustomerArtist
  Where ArtistID=@ArtistID 
  And ArtistAlert=1) 
Begin --if someone does
--check to see if our alert table exists
 If  not Exists
  (Select Name from sys.tables
   Where name='CustomersToAlert')
 Begin --if it doesn't create the table
  Create Table CustomersToAlert
  (
   CustomerEmail nvarchar(255),
   ArtistName nvarchar(255),
   VenueName nvarchar(255),
   ShowDate date,
   ShowTime Time
  )
 End --end if table doesn't exist
 --declare the variable we will need to
 --insert into our table
Declare @CustomerEmail nvarchar(255)
Declare @ArtistName nvarchar(255)
Declare @VenueName nvarchar(255)
Declare @ShowDate date
Declare @ShowTime time

--assign values to each of the variables
Select @ArtistName=ArtistName from Artist
Where ArtistID = @ArtistID

Select @CustomerEmail = CustomerEmail
From Customer c
Inner join CustomerArtist  ca
On c.CustomerID=ca.CustomerID
where ca.ArtistID=@ArtistID
And ca.ArtistAlert=1

Select @VenueName=VenueName
From Venue v
inner Join Inserted i
on v.VenueID=i.VenueID


Select @ShowDate = ShowDate, @ShowTime = ShowTime
From Inserted
--insert into our new alert table

Insert into CustomersToAlert(CustomerEmail, ArtistName, VenueName, ShowDate, ShowTime)
Values (@CustomerEmail, @ArtistName, @VenueName, @ShowDate, @ShowTime)

End
--/////this is the end of the trigger///---

--Test the trigger with an insert
Insert into Show(VenueID, ArtistID, TourName, ShowDate, ShowTime, ShowPosition, ShowDescription)
Values(3,1,'Some Kind of tour','8/12/2012','20:00:00','Headline',null)

Select * from CustomersToAlert

--it only inserted one value--we need to alter the trigger 
--to create a loop and a cursor 
--which will go through all the available records

Thursday, July 12, 2012

Backup and Restore

--this backs up the database Automart
--the init makes it the first and allows differential backups
BackUp Database Automart
To Disk='C:\AutomartBackup\Automart.bak'
with init

--we will add a table between the initial and the differential backup
Use Automart
Create Table TestTable
(
   ID int identity(1,1) primary key,
   MyTimestamp DateTime
)

Insert into TestTable (MyTimestamp)
Values(GETDATE())

--the differential backup backs up whatever has changed
--since the last backup
Backup Database Automart
To disk='C:\AutomartBackup\Automart.bak'
with differential

--we will add a couple more rows
--that will be only in the log
Insert into TestTable (MyTimestamp)
Values(GETDATE())

use master
--now we backup the log
--the norecovery is required so that
--we can restore the initial backup and all
--the differential backups
Backup log Automart
to disk='C:\AutomartBackup\AutomartLog.trn'
With norecovery, no_truncate

--now we will restore the backups
--first the initial backup
--the the differential backup
--then the log. the log is with recovery
--to put the database back in a useable
--mode
Restore Database Automart
From Disk ='C:\AutomartBackup\Automart.bak'
with noRecovery, file=1

Restore Database Automart
From Disk ='C:\AutomartBackup\Automart.bak'
with noRecovery, file=2

use master
Restore log Automart
From Disk='C:\AutomartBackup\Automartlog.trn'
with Recovery

Use Automart

Select * From TestTable

VenueTracker Database Script

USE [master]
GO
/****** Object:  Database [VenueTracker]    Script Date: 07/12/2012 12:20:32 ******/
/*If Exists
 (Select name from sys.databases
  Where name = 'VenueTracker')
Begin
Drop Database VenueTracker
End*/
Go

CREATE DATABASE [VenueTracker] 
Go
USE [VenueTracker]
GO
/****** Object:  User [VenuLogin]    Script Date: 07/12/2012 12:20:32 ******/
CREATE USER [VenuLogin] FOR LOGIN [VenuLogin] WITH DEFAULT_SCHEMA=[Venue]
GO
/****** Object:  Role [venueRole]    Script Date: 07/12/2012 12:20:32 ******/
CREATE ROLE [venueRole] AUTHORIZATION [dbo]
GO
/****** Object:  Schema [Venue]    Script Date: 07/12/2012 12:20:32 ******/
CREATE SCHEMA [Venue] AUTHORIZATION [dbo]
GO
/****** Object:  Schema [Customer]    Script Date: 07/12/2012 12:20:32 ******/
CREATE SCHEMA [Customer] AUTHORIZATION [dbo]
GO
/****** Object:  Schema [Artist]    Script Date: 07/12/2012 12:20:32 ******/
CREATE SCHEMA [Artist] AUTHORIZATION [dbo]
GO
/****** Object:  Table [dbo].[Genre]    Script Date: 07/12/2012 12:20:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Genre](
 [GenreID] [int] IDENTITY(1,1) NOT NULL,
 [GenreName] [nvarchar](255) NULL,
PRIMARY KEY CLUSTERED 
(
 [GenreID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
UNIQUE NONCLUSTERED 
(
 [GenreName] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Genre] ON
INSERT [dbo].[Genre] ([GenreID], [GenreName]) VALUES (3, N'Alternative')
INSERT [dbo].[Genre] ([GenreID], [GenreName]) VALUES (5, N'Country')
INSERT [dbo].[Genre] ([GenreID], [GenreName]) VALUES (4, N'Easy Listening')
INSERT [dbo].[Genre] ([GenreID], [GenreName]) VALUES (2, N'Folk')
INSERT [dbo].[Genre] ([GenreID], [GenreName]) VALUES (7, N'House')
INSERT [dbo].[Genre] ([GenreID], [GenreName]) VALUES (6, N'Pop')
INSERT [dbo].[Genre] ([GenreID], [GenreName]) VALUES (1, N'Rock')
SET IDENTITY_INSERT [dbo].[Genre] OFF
/****** Object:  Table [dbo].[Venue]    Script Date: 07/12/2012 12:20:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Venue](
 [VenueID] [int] IDENTITY(1,1) NOT NULL,
 [VenueName] [nvarchar](255) NOT NULL,
 [VenueAddress1] [nvarchar](255) NULL,
 [VenueAddress2] [nvarchar](255) NOT NULL,
 [VenueCity] [nvarchar](255) NOT NULL,
 [VenueState] [nchar](2) NOT NULL,
 [VenuePhone] [nchar](10) NOT NULL,
 [VenueCapacity] [int] NULL,
 [VenueAgeRestriction] [bit] NULL,
 [VenueDescription] [xml] NULL,
 [VenueURL] [nvarchar](255) NULL,
PRIMARY KEY CLUSTERED 
(
 [VenueID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Venue] ON
INSERT [dbo].[Venue] ([VenueID], [VenueName], [VenueAddress1], [VenueAddress2], [VenueCity], [VenueState], [VenuePhone], [VenueCapacity], [VenueAgeRestriction], [VenueDescription], [VenueURL]) VALUES (1, N'Key Arena', NULL, N'something 1st Avenue North', N'Seattle', N'Wa', N'2065551234', 14000, 0, N'where the sonics used to play', N'http://www.arena.com')
INSERT [dbo].[Venue] ([VenueID], [VenueName], [VenueAddress1], [VenueAddress2], [VenueCity], [VenueState], [VenuePhone], [VenueCapacity], [VenueAgeRestriction], [VenueDescription], [VenueURL]) VALUES (2, N'Columbia Gorge', NULL, N'George Washington', N'George', N'Wa', N'2065552345', 30000, 0, N'Big place by the river', N'http://theGorge.com')
INSERT [dbo].[Venue] ([VenueID], [VenueName], [VenueAddress1], [VenueAddress2], [VenueCity], [VenueState], [VenuePhone], [VenueCapacity], [VenueAgeRestriction], [VenueDescription], [VenueURL]) VALUES (3, N'Tractor Tavern', NULL, N'1001 Ballard', N'Seattle', N'Wa', N'2065553456', 150, 1, N'Ballard Tavern', N'http://www.tractortavern.com')
INSERT [dbo].[Venue] ([VenueID], [VenueName], [VenueAddress1], [VenueAddress2], [VenueCity], [VenueState], [VenuePhone], [VenueCapacity], [VenueAgeRestriction], [VenueDescription], [VenueURL]) VALUES (4, N'Comet Tavern', NULL, N'922 Pike', N'Seattle', N'Wa', N'2065551200', 99, 1, NULL, N'http://www.cometTavern.com')
INSERT [dbo].[Venue] ([VenueID], [VenueName], [VenueAddress1], [VenueAddress2], [VenueCity], [VenueState], [VenuePhone], [VenueCapacity], [VenueAgeRestriction], [VenueDescription], [VenueURL]) VALUES (6, N'Nuemos', NULL, N'923 Pike', N'Seattle', N'WA', N'2065551234', 100, 1, NULL, N'http://nuemos.com')
INSERT [dbo].[Venue] ([VenueID], [VenueName], [VenueAddress1], [VenueAddress2], [VenueCity], [VenueState], [VenuePhone], [VenueCapacity], [VenueAgeRestriction], [VenueDescription], [VenueURL]) VALUES (7, N'dfdsf', NULL, N'dffdadffds', N'Seatt;e', N'Wa', N'2924384577', 100, 1, NULL, N'http://www.dfds.com')
SET IDENTITY_INSERT [dbo].[Venue] OFF
/****** Object:  Table [dbo].[Artist]    Script Date: 07/12/2012 12:20:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Artist](
 [ArtistID] [int] IDENTITY(1,1) NOT NULL,
 [ArtistName] [nvarchar](255) NOT NULL,
 [ArtistBio] [xml] NULL,
 [ArtistURL] [nvarchar](255) NULL,
PRIMARY KEY CLUSTERED 
(
 [ArtistID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
UNIQUE NONCLUSTERED 
(
 [ArtistName] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Artist] ON
INSERT [dbo].[Artist] ([ArtistID], [ArtistName], [ArtistBio], [ArtistURL]) VALUES (1, N'Bob Dylan', NULL, NULL)
INSERT [dbo].[Artist] ([ArtistID], [ArtistName], [ArtistBio], [ArtistURL]) VALUES (2, N'Barbara Streisand', NULL, NULL)
INSERT [dbo].[Artist] ([ArtistID], [ArtistName], [ArtistBio], [ArtistURL]) VALUES (3, N'Decemberists', NULL, NULL)
INSERT [dbo].[Artist] ([ArtistID], [ArtistName], [ArtistBio], [ArtistURL]) VALUES (4, N'The killers', NULL, NULL)
INSERT [dbo].[Artist] ([ArtistID], [ArtistName], [ArtistBio], [ArtistURL]) VALUES (5, N'Tom Petty', NULL, NULL)
SET IDENTITY_INSERT [dbo].[Artist] OFF
/****** Object:  Table [dbo].[Customer]    Script Date: 07/12/2012 12:20:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Customer](
 [CustomerID] [int] IDENTITY(1,1) NOT NULL,
 [CustomerLastName] [nvarchar](255) NOT NULL,
 [CustomerFirstName] [nvarchar](255) NULL,
 [CustomerEmail] [nvarchar](255) NOT NULL,
 [CustomerPassword] [nvarchar](255) NOT NULL,
PRIMARY KEY CLUSTERED 
(
 [CustomerID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
UNIQUE NONCLUSTERED 
(
 [CustomerPassword] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
UNIQUE NONCLUSTERED 
(
 [CustomerEmail] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Customer] ON
INSERT [dbo].[Customer] ([CustomerID], [CustomerLastName], [CustomerFirstName], [CustomerEmail], [CustomerPassword]) VALUES (1, N'Smith', N'John', N'johnsmith@msn.com', N'jspass')
INSERT [dbo].[Customer] ([CustomerID], [CustomerLastName], [CustomerFirstName], [CustomerEmail], [CustomerPassword]) VALUES (2, N'Jones', N'Sue', N'suejones@hotmail.com', N'sjpass')
INSERT [dbo].[Customer] ([CustomerID], [CustomerLastName], [CustomerFirstName], [CustomerEmail], [CustomerPassword]) VALUES (3, N'Grant', N'Cary', N'carygrant@gmail.com', N'cgpass')
INSERT [dbo].[Customer] ([CustomerID], [CustomerLastName], [CustomerFirstName], [CustomerEmail], [CustomerPassword]) VALUES (4, N'Kerr', N'Debra', N'debrakerr@yahoo.com', N'dkpass')
INSERT [dbo].[Customer] ([CustomerID], [CustomerLastName], [CustomerFirstName], [CustomerEmail], [CustomerPassword]) VALUES (5, N'Depp', N'Johnny', N'johnnydepp@aol.com', N'jdpass')
INSERT [dbo].[Customer] ([CustomerID], [CustomerLastName], [CustomerFirstName], [CustomerEmail], [CustomerPassword]) VALUES (6, N'Madonna', N'', N'madonna@msn.com', N'mpass')
SET IDENTITY_INSERT [dbo].[Customer] OFF
/****** Object:  SqlAssembly [CLRVenuetrackerExample]    Script Date: 07/12/2012 12:20:35 ******/
CREATE ASSEMBLY [CLRVenuetrackerExample]
AUTHORIZATION [dbo]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300B11FFF4F0000000000000000E00002210B010800000A00000006000000000000EE290000002000000040000000004000002000000002000004000000000000000400000000000000008000000002000000000000030040850000100000100000000010000010000000000000100000000000000000000000942900005700000000400000F003000000000000000000000000000000000000006000000C000000D42800001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000F409000000200000000A000000020000000000000000000000000000200000602E72737263000000F00300000040000000040000000C0000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001000000000000000000000000000004000004200000000000000000000000000000000D0290000000000004800000002000500BC2000001808000001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001B3002004800000001000011007201000070730F00000A0A00066F1000000A00066F1100000A0B0772310000706F1200000A00281300000A076F1400000A0000DE100614FE010C082D07066F1500000A00DC002A0110000002000C002A360010000000001E02281600000A2A42534A4201000100000000000C00000076322E302E35303732370000000005006C00000018020000237E0000840200003803000023537472696E677300000000BC05000084010000235553004007000010000000234755494400000050070000C800000023426C6F620000000000000002000001471402000900000000FA25330016000001000000170000000200000002000000160000000C00000001000000010000000200000000000A00010000000000060046003F0006007400620006008B0062000600A80062000600C70062000600E00062000600F900620006001401620006002F01620006006701480106007B0162000600A70194013300BB0100000600EA01CA0106000A02CA010A0066024B020A0092027C020A00B302A0020A00C5027C020A00DE02A0020A00F8024B020A0003034B02060023033F000000000001000000000001000100010010002500000005000100010050200000000096004D000A000100B4200000000086185C000E00010011005C00120019005C00120021005C00120029005C00120031005C00120039005C00120041005C00120049005C00120051005C00170059005C00120061005C001C0071005C00220079005C000E0081005C000E0089005C0012009100C0020E008900D0022C00A100E8021200A9000B033100B10014033600B9002F030E0009005C000E002000730027002E002B0044002E00130060002E001B0060002E00230066002E000B0044002E00330075002E003B0060002E004B0060002E005B0096002E0063009F002E006B00A8003C000480000001000000E011D84D00000000000028020000020000000000000000000000010036000000000002000000000000000000000001003F020000000000000000003C4D6F64756C653E00434C5256656E7565747261636B65724578616D706C652E646C6C0053746F72656450726F63656475726573006D73636F726C69620053797374656D004F626A656374007573705F56656E756553686F7773002E63746F720053797374656D2E5265666C656374696F6E00417373656D626C795469746C6541747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C7943756C747572654174747269627574650053797374656D2E52756E74696D652E496E7465726F70536572766963657300436F6D56697369626C6541747472696275746500417373656D626C7956657273696F6E4174747269627574650053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C69747941747472696275746500434C5256656E7565747261636B65724578616D706C650053797374656D2E44617461004D6963726F736F66742E53716C5365727665722E5365727665720053716C50726F6365647572654174747269627574650053797374656D2E446174612E53716C436C69656E740053716C436F6E6E656374696F6E0053797374656D2E446174612E436F6D6D6F6E004462436F6E6E656374696F6E004F70656E0053716C436F6D6D616E6400437265617465436F6D6D616E64004462436F6D6D616E64007365745F436F6D6D616E64546578740053716C436F6E746578740053716C50697065006765745F506970650045786563757465416E6453656E640049446973706F7361626C6500446973706F73650000002F63006F006E007400650078007400200063006F006E006E0065006300740069006F006E003D00740072007500650000814F530065006C006500630074002000560065006E00750065004E0061006D00650020002C002000560065006E00750065004100640064007200650073007300320020002C0020004100720074006900730074004E0061006D00650020002C002000530068006F007700440061007400650020002C002000530068006F007700540069006D0065002000460072006F006D002000560065006E007500650020007600200049006E006E006500720020006A006F0069006E002000530068006F0077002000730020004F006E00200076002E00560065006E0075006500490044003D0073002E00560065006E007500650049004400200049006E006E006500720020004A006F0069006E0020004100720074006900730074002000610020004F006E00200061002E00410072007400690073007400490044003D0073002E0041007200740069007300740049004400200000000069844A87BFF2914D9B98DFD09FAA8D320008B77A5C561934E0890300000103200001042001010E042001010205200101113504200101080401000000042000124D040000125905200101124D0707031245124D021B010016434C5256656E7565747261636B65724578616D706C6500000501000000000E0100094D6963726F736F667400002001001B436F7079726967687420C2A9204D6963726F736F6674203230313200000801000701000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F7773010000000000B11FFF4F0000000002000000A2000000F0280000F00A000052534453FD583AF7F8654242B339011BDA34CF6301000000633A5C75736572735C73636F6E6765725C646F63756D656E74735C76697375616C2073747564696F20323031305C50726F6A656374735C434C5256656E7565747261636B65724578616D706C655C434C5256656E7565747261636B65724578616D706C655C6F626A5C44656275675C434C5256656E7565747261636B65724578616D706C652E706462000000BC2900000000000000000000DE290000002000000000000000000000000000000000000000000000D02900000000000000000000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF250020400000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000980300000000000000000000980334000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE0000010000000100D84DE01100000100D84DE0113F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B004F8020000010053007400720069006E006700460069006C00650049006E0066006F000000D4020000010030003000300030003000340062003000000034000A00010043006F006D00700061006E0079004E0061006D006500000000004D006900630072006F0073006F00660074000000580017000100460069006C0065004400650073006300720069007000740069006F006E000000000043004C005200560065006E007500650074007200610063006B00650072004500780061006D0070006C0065000000000040000F000100460069006C006500560065007200730069006F006E000000000031002E0030002E0034003500370036002E00310039003900320038000000000058001B00010049006E007400650072006E0061006C004E0061006D006500000043004C005200560065006E007500650074007200610063006B00650072004500780061006D0070006C0065002E0064006C006C00000000005C001B0001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A90020004D006900630072006F0073006F0066007400200032003000310032000000000060001B0001004F0072006900670069006E0061006C00460069006C0065006E0061006D006500000043004C005200560065006E007500650074007200610063006B00650072004500780061006D0070006C0065002E0064006C006C0000000000500017000100500072006F0064007500630074004E0061006D0065000000000043004C005200560065006E007500650074007200610063006B00650072004500780061006D0070006C0065000000000044000F000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0034003500370036002E00310039003900320038000000000048000F00010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0034003500370036002E00310039003900320038000000000000000000000000000000000000000000002000000C000000F03900000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = SAFE
GO
ALTER ASSEMBLY [CLRVenuetrackerExample]
ADD FILE FROM 0x4D6963726F736F667420432F432B2B204D534620372E30300D0A1A4453000000000200000200000017000000700000000000000014000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000C0FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF3800E0FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF0BCA3101380000000010000000100000000000000D00FFFF04000000038000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000300000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000942E3101B11FFF4F01000000FD583AF7F8654242B339011BDA34CF63000000000000000001000000010000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000BCA310138000000001000000010000000000000FFFFFFFF040000000380000000000000FFFFFFFF00000000FFFFFFFF00000000FFFFFFFF000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000F862513FC607D311905300C04FA302A1C4454B99E9E6D211903F00C04FA302A10B9D865A1166D311BD2A0000F80849BD60A66E40CF64824CB6F042D48172A7991000000000000000E9F4640B7B370B58403860A441E959FF000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000480000000000000048000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000FEEFFEEF01000000F000000000633A5C75736572735C73636F6E6765725C646F63756D656E74735C76697375616C2073747564696F20323031305C50726F6A656374735C434C5256656E7565747261636B65724578616D706C655C434C5256656E7565747261636B65724578616D706C655C7573705F56656E756553686F77732E63730000633A5C75736572735C73636F6E6765725C646F63756D656E74735C76697375616C2073747564696F20323031305C70726F6A656374735C636C7276656E7565747261636B65726578616D706C655C636C7276656E7565747261636B65726578616D706C655C7573705F76656E756573686F77732E63730004000000780000000100000079000000000000000300000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001BE230018000000069CE15236160CD01010000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000010000000200000001000000020000000000000079000000280000001BE230016B6FDCC9580000000100000078000000790000006500000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000400000036002A11000000008C01000000000000480000000000000000000000010000060000000001000000007573705F56656E756553686F7773001600031104000000580100004800000000000000010000000A0024115553797374656D00120024115553797374656D2E44617461000000001A0024115553797374656D2E446174612E53716C436C69656E7400001A0024115553797374656D2E446174612E53716C54797065730000001E002411554D6963726F736F66742E53716C5365727665722E536572766572001E00201102000000010000110000000000000400435324342430303030000000160003113C000000540100004600000001000000010000001A00201100000000010000110000000000000000636F6E6E6563740016000311EC00000050010000280000000C000000010000001600201101000000010000110000000000000000636D64000200060002000600020006002E000404C93FEAC6B359D649BC250902BBABB460000000004D0044003200000004010000040000000C0000000100050002000600F20000009C000000000000000100010048000000000000000B00000090000000000000000C000080010000000D0000800C0000000E0000800D0000000F00008014000000100000801B00000011000089270000001C000080330000001E00008034000000EEEFFE8046000000EEEFFE80470000001F000080050006001000530009000A000D001C000D0036000D0047000D00310009000A00000000000000000005000600F4000000080000000100000000000000080000000000000020000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000FFFFFFFF1A092FF1100000000C02000021000000010000000100000001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000010000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001E002511000000000400000001007573705F56656E756553686F77730000000016002911000000000400000001003036303030303031000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000010000000000000000000000000000000000000000000000000000000FFFFFFFF1A092FF10000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000FFFFFFFF77093101010000000A00008A0B006F760C0001005C000000200000002C00000084000000000000000000000016000000190000000000EEC00000000000000000FFFF000000000000FFFFFFFF00000000FFFF00000000000000000000000009009001000000000000B40000000100000038E99D09000000000000000053746F72656450726F636564757265730043444135313336460000002DBA2EF101000000000000004800000000000000000000000000000000000000020002000D01000000000100FFFFFFFF00000000480000000802000000000000FFFFFFFF00000000FFFFFFFF010001000000010000000000633A5C75736572735C73636F6E6765725C646F63756D656E74735C76697375616C2073747564696F20323031305C50726F6A656374735C434C5256656E7565747261636B65724578616D706C655C434C5256656E7565747261636B65724578616D706C655C7573705F56656E756553686F77732E63730000FEEFFEEF010000000100000000010000000000000000000000FFFFFFFFFFFFFFFFFFFF0800FFFFFFFFFFFFFFFFFFFF0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000942E3101B11FFF4F01000000FD583AF7F8654242B339011BDA34CF63A40000002F4C696E6B496E666F002F6E616D6573002F7372632F686561646572626C6F636B002F7372632F66696C65732F633A5C75736572735C73636F6E6765725C646F63756D656E74735C76697375616C2073747564696F20323031305C70726F6A656374735C636C7276656E7565747261636B65726578616D706C655C636C7276656E7565747261636B65726578616D706C655C7573705F76656E756573686F77732E6373000400000006000000010000001E00000000000000110000000600000022000000070000000A0000000500000000000000040000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000D00000018000000FC000000380000009B0100000000000014010000800000005800000028000000500200002C0200002C0000003800000003000000120000000600000011000000090000000A00000007000000080000000B0000000C0000000D0000000E000000100000000F00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000130000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
AS N'bin\Debug\CLRVenuetrackerExample.pdb'
GO
ALTER ASSEMBLY [CLRVenuetrackerExample]
ADD FILE FROM 0xEFBBBF7573696E672053797374656D2E5265666C656374696F6E3B0D0A7573696E672053797374656D2E52756E74696D652E436F6D70696C657253657276696365733B0D0A7573696E672053797374656D2E52756E74696D652E496E7465726F7053657276696365733B0D0A7573696E672053797374656D2E446174612E53716C3B0D0A0D0A2F2F2047656E6572616C20496E666F726D6174696F6E2061626F757420616E20617373656D626C7920697320636F6E74726F6C6C6564207468726F7567682074686520666F6C6C6F77696E670D0A2F2F20736574206F6620617474726962757465732E204368616E6765207468657365206174747269627574652076616C75657320746F206D6F646966792074686520696E666F726D6174696F6E0D0A2F2F206173736F636961746564207769746820616E20617373656D626C792E0D0A5B617373656D626C793A20417373656D626C795469746C652822434C5256656E7565747261636B65724578616D706C6522295D0D0A5B617373656D626C793A20417373656D626C794465736372697074696F6E282222295D0D0A5B617373656D626C793A20417373656D626C79436F6E66696775726174696F6E282222295D0D0A5B617373656D626C793A20417373656D626C79436F6D70616E7928224D6963726F736F667422295D0D0A5B617373656D626C793A20417373656D626C7950726F647563742822434C5256656E7565747261636B65724578616D706C6522295D0D0A5B617373656D626C793A20417373656D626C79436F707972696768742822436F7079726967687420C2A9204D6963726F736F6674203230313222295D0D0A5B617373656D626C793A20417373656D626C7954726164656D61726B282222295D0D0A5B617373656D626C793A20417373656D626C7943756C74757265282222295D0D0A0D0A5B617373656D626C793A20436F6D56697369626C652866616C7365295D0D0A0D0A2F2F0D0A2F2F2056657273696F6E20696E666F726D6174696F6E20666F7220616E20617373656D626C7920636F6E7369737473206F662074686520666F6C6C6F77696E6720666F75722076616C7565733A0D0A2F2F0D0A2F2F2020202020204D616A6F722056657273696F6E0D0A2F2F2020202020204D696E6F722056657273696F6E0D0A2F2F2020202020204275696C64204E756D6265720D0A2F2F2020202020205265766973696F6E0D0A2F2F0D0A2F2F20596F752063616E207370656369667920616C6C207468652076616C756573206F7220796F752063616E2064656661756C7420746865205265766973696F6E20616E64204275696C64204E756D626572730D0A2F2F206279207573696E672074686520272A272061732073686F776E2062656C6F773A0D0A5B617373656D626C793A20417373656D626C7956657273696F6E2822312E302E2A22295D0D0A0D0A
AS N'Properties\AssemblyInfo.cs'
GO
ALTER ASSEMBLY [CLRVenuetrackerExample]
ADD FILE FROM 0xEFBBBF7573696E672053797374656D3B0D0A7573696E672053797374656D2E446174613B0D0A7573696E672053797374656D2E446174612E53716C436C69656E743B0D0A7573696E672053797374656D2E446174612E53716C54797065733B0D0A7573696E67204D6963726F736F66742E53716C5365727665722E5365727665723B0D0A0D0A0D0A7075626C6963207061727469616C20636C6173732053746F72656450726F636564757265730D0A7B0D0A202020205B4D6963726F736F66742E53716C5365727665722E5365727665722E53716C50726F6365647572655D0D0A202020207075626C69632073746174696320766F6964207573705F56656E756553686F777328290D0A202020207B0D0A20202020202020207573696E67202853716C436F6E6E656374696F6E20636F6E6E656374203D6E65772053716C436F6E6E656374696F6E2822636F6E7465787420636F6E6E656374696F6E3D747275652229290D0A20202020202020207B0D0A202020202020202020202020636F6E6E6563742E4F70656E28293B0D0A20202020202020202020202053716C436F6D6D616E6420636D64203D20636F6E6E6563742E437265617465436F6D6D616E6428293B0D0A202020202020202020202020636D642E436F6D6D616E6454657874203D202253656C6563742056656E75654E616D6520220D0A202020202020202020202020202020202020202020202020202020202020202020202020202020202B20222C2056656E7565416464726573733220220D0A202020202020202020202020202020202020202020202020202020202020202020202020202020202B20222C204172746973744E616D6520220D0A202020202020202020202020202020202020202020202020202020202020202020202020202020202B20222C2053686F774461746520220D0A202020202020202020202020202020202020202020202020202020202020202020202020202020202B20222C2053686F7754696D6520220D0A202020202020202020202020202020202020202020202020202020202020202020202020202020202B202246726F6D2056656E7565207620220D0A202020202020202020202020202020202020202020202020202020202020202020202020202020202B2022496E6E6572206A6F696E2053686F77207320220D0A202020202020202020202020202020202020202020202020202020202020202020202020202020202B20224F6E20762E56656E756549443D732E56656E7565494420220D0A202020202020202020202020202020202020202020202020202020202020202020202020202020202B2022496E6E6572204A6F696E20417274697374206120220D0A202020202020202020202020202020202020202020202020202020202020202020202020202020202B20224F6E20612E41727469737449443D732E417274697374494420223B0D0A0D0A20202020202020202020202053716C436F6E746578742E506970652E45786563757465416E6453656E6428636D64293B0D0A0D0A20202020202020207D0D0A202020207D0D0A7D3B0D0A
AS N'usp_VenueShows.cs'
GO
EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyProjectRoot', @value=N'c:\users\sconger\documents\visual studio 2010\Projects\CLRVenuetrackerExample\CLRVenuetrackerExample' , @level0type=N'ASSEMBLY',@level0name=N'CLRVenuetrackerExample'
GO
/****** Object:  Table [dbo].[ArtistGenre]    Script Date: 07/12/2012 12:20:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ArtistGenre](
 [ArtistID] [int] NOT NULL,
 [GenreID] [int] NOT NULL,
 CONSTRAINT [pk_ArtistGenre] PRIMARY KEY CLUSTERED 
(
 [ArtistID] ASC,
 [GenreID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[ArtistGenre] ([ArtistID], [GenreID]) VALUES (1, 1)
INSERT [dbo].[ArtistGenre] ([ArtistID], [GenreID]) VALUES (1, 2)
INSERT [dbo].[ArtistGenre] ([ArtistID], [GenreID]) VALUES (1, 5)
INSERT [dbo].[ArtistGenre] ([ArtistID], [GenreID]) VALUES (2, 4)
INSERT [dbo].[ArtistGenre] ([ArtistID], [GenreID]) VALUES (3, 1)
INSERT [dbo].[ArtistGenre] ([ArtistID], [GenreID]) VALUES (3, 3)
INSERT [dbo].[ArtistGenre] ([ArtistID], [GenreID]) VALUES (4, 3)
INSERT [dbo].[ArtistGenre] ([ArtistID], [GenreID]) VALUES (5, 1)
/****** Object:  StoredProcedure [dbo].[usp_VenueShows]    Script Date: 07/12/2012 12:20:37 ******/
Go
CREATE PROCEDURE [dbo].[usp_VenueShows]
AS
EXTERNAL NAME [CLRVenuetrackerExample].[StoredProcedures].[usp_VenueShows]
GO
EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFile', @value=N'usp_VenueShows.cs' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'usp_VenueShows'
GO
EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFileLine', @value=N'12' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'usp_VenueShows'
GO
/****** Object:  StoredProcedure [Venue].[usp_UpdateVenue]    Script Date: 07/12/2012 12:20:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create proc [Venue].[usp_UpdateVenue]
@VenueID int,
@VenueName nvarchar(255), 
@VenueAddress1 nvarchar(255) = null, 
@VenueAddress2 nvarchar(255), 
@VenueCity nvarchar(255) ='Seattle', 
@VenueState nchar(2) ='WA', 
@VenuePhone nchar(10), 
@VenueCapacity int, 
@VenueAgeRestriction bit =0, 
@VenueDescription xml=null, 
@VenueURL nvarchar(255)
As
Begin Tran
Begin Try
if Exists
 (Select VenueID from Venue
  Where VenueID = @VenueID)
 Begin
 Update Venue
 Set VenueName =@VenueName, 
 VenueAddress1=@VenueAddress1, 
 VenueAddress2=@VenueAddress2, 
 VenueCity=@VenueCity, 
 VenueState=@VenueState,
 VenuePhone=@VenuePhone, 
 VenueCapacity=@VenueCapacity, 
 VenueAgeRestriction=@VenueAgeRestriction, 
 VenueDescription=@VenueDescription, 
 VenueURL=@VenueURL
 Where VenueID = @VenueID
 Commit tran
 end
 Else
 Begin
 Commit Tran
 Print 'The Venue doesn''t exist'
 End
 End try
 Begin Catch
 Rollback Tran
 print Error_message()
 End catch
GO
/****** Object:  Table [dbo].[Show]    Script Date: 07/12/2012 12:20:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Show](
 [ShowID] [int] IDENTITY(1,1) NOT NULL,
 [VenueID] [int] NULL,
 [ArtistID] [int] NULL,
 [TourName] [nvarchar](255) NULL,
 [ShowDate] [date] NOT NULL,
 [ShowTime] [time](7) NOT NULL,
 [ShowPosition] [nvarchar](255) NULL,
 [ShowDescription] [nvarchar](max) NULL,
PRIMARY KEY CLUSTERED 
(
 [ShowID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Show] ON
INSERT [dbo].[Show] ([ShowID], [VenueID], [ArtistID], [TourName], [ShowDate], [ShowTime], [ShowPosition], [ShowDescription]) VALUES (2, 1, 1, N'Never Ending Tour', CAST(0xED370B00 AS Date), CAST(0x07002058A3A70000 AS Time), N'Headline', N'New stuff')
INSERT [dbo].[Show] ([ShowID], [VenueID], [ArtistID], [TourName], [ShowDate], [ShowTime], [ShowPosition], [ShowDescription]) VALUES (3, 1, 5, N'free falling', CAST(0x0F380B00 AS Date), CAST(0x0700B893419F0000 AS Time), N'Headline', N'Old Stuff')
INSERT [dbo].[Show] ([ShowID], [VenueID], [ArtistID], [TourName], [ShowDate], [ShowTime], [ShowPosition], [ShowDescription]) VALUES (5, 1, 4, N'free Falling', CAST(0x0F380B00 AS Date), CAST(0x0700B893419F0000 AS Time), N'Opening', NULL)
INSERT [dbo].[Show] ([ShowID], [VenueID], [ArtistID], [TourName], [ShowDate], [ShowTime], [ShowPosition], [ShowDescription]) VALUES (6, 2, 4, N'Summer Fest', CAST(0x1D3A0B00 AS Date), CAST(0x0700B893419F0000 AS Time), N'Festival', NULL)
INSERT [dbo].[Show] ([ShowID], [VenueID], [ArtistID], [TourName], [ShowDate], [ShowTime], [ShowPosition], [ShowDescription]) VALUES (7, 2, 3, N'Summer Fest', CAST(0x1D3A0B00 AS Date), CAST(0x07002058A3A70000 AS Time), N'Festival', NULL)
INSERT [dbo].[Show] ([ShowID], [VenueID], [ArtistID], [TourName], [ShowDate], [ShowTime], [ShowPosition], [ShowDescription]) VALUES (8, 3, 2, N'Odd Places', CAST(0x263A0B00 AS Date), CAST(0x0700881C05B00000 AS Time), N'Headline', NULL)
SET IDENTITY_INSERT [dbo].[Show] OFF
/****** Object:  Table [dbo].[CustomerArtist]    Script Date: 07/12/2012 12:20:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CustomerArtist](
 [CustomerID] [int] NOT NULL,
 [ArtistID] [int] NOT NULL,
 [ArtistAlert] [bit] NULL,
 CONSTRAINT [Pk_CustomerArtist] PRIMARY KEY CLUSTERED 
(
 [CustomerID] ASC,
 [ArtistID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[CustomerArtist] ([CustomerID], [ArtistID], [ArtistAlert]) VALUES (1, 3, 0)
INSERT [dbo].[CustomerArtist] ([CustomerID], [ArtistID], [ArtistAlert]) VALUES (1, 5, 1)
INSERT [dbo].[CustomerArtist] ([CustomerID], [ArtistID], [ArtistAlert]) VALUES (2, 1, 1)
INSERT [dbo].[CustomerArtist] ([CustomerID], [ArtistID], [ArtistAlert]) VALUES (3, 1, 1)
INSERT [dbo].[CustomerArtist] ([CustomerID], [ArtistID], [ArtistAlert]) VALUES (3, 4, 1)
INSERT [dbo].[CustomerArtist] ([CustomerID], [ArtistID], [ArtistAlert]) VALUES (4, 5, 1)
INSERT [dbo].[CustomerArtist] ([CustomerID], [ArtistID], [ArtistAlert]) VALUES (5, 2, 0)
INSERT [dbo].[CustomerArtist] ([CustomerID], [ArtistID], [ArtistAlert]) VALUES (6, 3, 1)
/****** Object:  StoredProcedure [Venue].[usp_AddVenue]    Script Date: 07/12/2012 12:20:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [Venue].[usp_AddVenue]
@VenueName nvarchar(255),
@VenueAddress1 nvarchar(255) =null, 
@VenueAddress2 nvarchar(255), 
@VenueCity nvarchar(255), 
@VenueState nchar(2) ='WA', 
@VenuePhone nchar(10), 
@VenueCapacity int, 
@VenueAgeRestriction  bit =0, 
@VenueDescription xml =null, 
@VenueURL nvarchar(255)
AS
If not Exists
 (Select VenueName 
  From Venue
  Where VenueAddress2=@VenueAddress2
  And VenueCity=@VenueCity
  And VenueState=@VenueState)
Begin
 Insert into Venue(VenueName, VenueAddress1, VenueAddress2, VenueCity, VenueState, VenuePhone, VenueCapacity, VenueAgeRestriction, VenueDescription, VenueURL)
Values ( @VenueName, @VenueAddress1, @VenueAddress2, 
@VenueCity, @VenueState, 
@VenuePhone, @VenueCapacity, 
@VenueAgeRestriction, 
@VenueDescription, @VenueURL)
return 1 
End
Else
Begin
Print 'The venue already exists'
return 0
End
GO
/****** Object:  Table [dbo].[TicketOutlet]    Script Date: 07/12/2012 12:20:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TicketOutlet](
 [TicketOutletID] [int] IDENTITY(1,1) NOT NULL,
 [ShowID] [int] NOT NULL,
 [TicketOutletURL] [nvarchar](255) NOT NULL,
 [TicketPrice] [decimal](5, 2) NOT NULL,
 [TicketsRemaining] [int] NULL,
 CONSTRAINT [PK_Ticket] PRIMARY KEY CLUSTERED 
(
 [TicketOutletID] ASC,
 [ShowID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[TicketOutlet] ON
INSERT [dbo].[TicketOutlet] ([TicketOutletID], [ShowID], [TicketOutletURL], [TicketPrice], [TicketsRemaining]) VALUES (1, 2, N'http://www.Ticketmaster.com', CAST(75.00 AS Decimal(5, 2)), 7000)
INSERT [dbo].[TicketOutlet] ([TicketOutletID], [ShowID], [TicketOutletURL], [TicketPrice], [TicketsRemaining]) VALUES (3, 3, N'http://www.Ticketmaster.com', CAST(75.00 AS Decimal(5, 2)), 12000)
INSERT [dbo].[TicketOutlet] ([TicketOutletID], [ShowID], [TicketOutletURL], [TicketPrice], [TicketsRemaining]) VALUES (5, 5, N'http://www.Ticketmaster.com', CAST(75.00 AS Decimal(5, 2)), 12000)
INSERT [dbo].[TicketOutlet] ([TicketOutletID], [ShowID], [TicketOutletURL], [TicketPrice], [TicketsRemaining]) VALUES (6, 6, N'http://www.Ticketmaster.com', CAST(55.00 AS Decimal(5, 2)), 10000)
INSERT [dbo].[TicketOutlet] ([TicketOutletID], [ShowID], [TicketOutletURL], [TicketPrice], [TicketsRemaining]) VALUES (7, 7, N'http://www.Ticketmaster.com', CAST(55.00 AS Decimal(5, 2)), 10000)
INSERT [dbo].[TicketOutlet] ([TicketOutletID], [ShowID], [TicketOutletURL], [TicketPrice], [TicketsRemaining]) VALUES (8, 7, N'http://ticketsWest.com', CAST(45.00 AS Decimal(5, 2)), 2000)
INSERT [dbo].[TicketOutlet] ([TicketOutletID], [ShowID], [TicketOutletURL], [TicketPrice], [TicketsRemaining]) VALUES (9, 8, N'http://www.Ticketmaster.com', CAST(200.00 AS Decimal(5, 2)), 40)
INSERT [dbo].[TicketOutlet] ([TicketOutletID], [ShowID], [TicketOutletURL], [TicketPrice], [TicketsRemaining]) VALUES (10, 8, N'http://www.TractorTavern.com', CAST(200.00 AS Decimal(5, 2)), 20)
SET IDENTITY_INSERT [dbo].[TicketOutlet] OFF
/****** Object:  StoredProcedure [Venue].[usp_ShowList]    Script Date: 07/12/2012 12:20:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create proc [Venue].[usp_ShowList]
@VenueID int
As
Select VenueName [Venue], 
ArtistName [Artist],
TourName [Tour Name],
ShowDate [Date],
ShowTime [Time],
ShowPosition [Position],
ShowDescription [Description]
From Venue v
Inner join Show s
On v.VenueID=s.VenueID
inner join Artist a
on a.ArtistID=s.ArtistID
Where v.VenueID=@VenueID
GO
/****** Object:  Default [DF__CustomerA__Artis__38996AB5]    Script Date: 07/12/2012 12:20:37 ******/
ALTER TABLE [dbo].[CustomerArtist] ADD  DEFAULT ((1)) FOR [ArtistAlert]
GO
/****** Object:  Check [ck_position]    Script Date: 07/12/2012 12:20:37 ******/
ALTER TABLE [dbo].[Show]  WITH CHECK ADD  CONSTRAINT [ck_position] CHECK  (([ShowPosition]='festival' OR [ShowPosition]='Opening' OR [ShowPosition]='Headline'))
GO
ALTER TABLE [dbo].[Show] CHECK CONSTRAINT [ck_position]
GO
/****** Object:  ForeignKey [FK_Artist]    Script Date: 07/12/2012 12:20:35 ******/
ALTER TABLE [dbo].[ArtistGenre]  WITH CHECK ADD  CONSTRAINT [FK_Artist] FOREIGN KEY([ArtistID])
REFERENCES [dbo].[Artist] ([ArtistID])
GO
ALTER TABLE [dbo].[ArtistGenre] CHECK CONSTRAINT [FK_Artist]
GO
/****** Object:  ForeignKey [FK_Genre]    Script Date: 07/12/2012 12:20:35 ******/
ALTER TABLE [dbo].[ArtistGenre]  WITH CHECK ADD  CONSTRAINT [FK_Genre] FOREIGN KEY([GenreID])
REFERENCES [dbo].[Genre] ([GenreID])
GO
ALTER TABLE [dbo].[ArtistGenre] CHECK CONSTRAINT [FK_Genre]
GO
/****** Object:  ForeignKey [FK__Show__ArtistID__0CBAE877]    Script Date: 07/12/2012 12:20:37 ******/
ALTER TABLE [dbo].[Show]  WITH CHECK ADD FOREIGN KEY([ArtistID])
REFERENCES [dbo].[Artist] ([ArtistID])
GO
/****** Object:  ForeignKey [FK__Show__VenueID__0BC6C43E]    Script Date: 07/12/2012 12:20:37 ******/
ALTER TABLE [dbo].[Show]  WITH CHECK ADD FOREIGN KEY([VenueID])
REFERENCES [dbo].[Venue] ([VenueID])
GO
/****** Object:  ForeignKey [FK__CustomerA__Artis__37A5467C]    Script Date: 07/12/2012 12:20:37 ******/
ALTER TABLE [dbo].[CustomerArtist]  WITH CHECK ADD FOREIGN KEY([ArtistID])
REFERENCES [dbo].[Artist] ([ArtistID])
GO
/****** Object:  ForeignKey [FK__CustomerA__Custo__36B12243]    Script Date: 07/12/2012 12:20:37 ******/
ALTER TABLE [dbo].[CustomerArtist]  WITH CHECK ADD FOREIGN KEY([CustomerID])
REFERENCES [dbo].[Customer] ([CustomerID])
GO
/****** Object:  ForeignKey [FK__TicketOut__ShowI__108B795B]    Script Date: 07/12/2012 12:20:37 ******/
ALTER TABLE [dbo].[TicketOutlet]  WITH CHECK ADD FOREIGN KEY([ShowID])
REFERENCES [dbo].[Show] ([ShowID])
GO

ITC Resources