WPF Form (Assignment 12)

Here is XAML

<Window x:Class="Assignment12.MainWindow"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
        xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
        xmlns:local="clr-namespace:Assignment12"
        mc:Ignorable="d"
        Title="MainWindow" Height="350" Width="525">
    <Grid>
        <Label x:Name="label" Content="Enter the base Amount" HorizontalAlignment="Left" Margin="49,0,0,0" VerticalAlignment="Top" Height="27" Width="194"/>
        <TextBox x:Name="txtAmount" HorizontalAlignment="Left" Height="23" Margin="210,0,0,0" TextWrapping="Wrap" VerticalAlignment="Top" Width="134"/>
        <RadioButton x:Name="rdbTenPercent" Content="Ten Percent" HorizontalAlignment="Left" Margin="63,45,0,0" VerticalAlignment="Top"/>
        <RadioButton x:Name="rdbFifteenPercent" Content="fifteen Percent" HorizontalAlignment="Left" Margin="63,65,0,0" VerticalAlignment="Top"/>
        <RadioButton x:Name="rdbTwentyPercent" Content="Twenty Percent" HorizontalAlignment="Left" Margin="63,85,0,0" VerticalAlignment="Top"/>
        <RadioButton x:Name="rdbOther" Content="Other" HorizontalAlignment="Left" Margin="63,105,0,0" VerticalAlignment="Top"/>
        <TextBox x:Name="txtOther" HorizontalAlignment="Left" Height="23" Margin="210,103,0,0" TextWrapping="Wrap" VerticalAlignment="Top" Width="120"/>
        <Button x:Name="btnCalculate" Content="Calculate" HorizontalAlignment="Left" Margin="63,141,0,0" VerticalAlignment="Top" Width="75" Click="btnCalculate_Click"/>
        <Label x:Name="lblResult" Content="Label" HorizontalAlignment="Left" Margin="210,126,0,0" VerticalAlignment="Top" Height="97" Width="154"/>
        <Button x:Name="btnClear" Content="Clear" HorizontalAlignment="Left" Margin="63,175,0,0" VerticalAlignment="Top" Width="75" Click="btnClear_Click"/>
        <Button x:Name="btnExit" Content="Exit" HorizontalAlignment="Left" Margin="63,208,0,0" VerticalAlignment="Top" Width="75" Click="btnExit_Click"/>

    </Grid>
</Window>

Here is the Tip Class

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Assignment12
{
    class Tip
    {
       public double Amount { get; set; }
        public double TipPercent { get; set; }

        private const double TAX = .092;

        public double CalculateTip()
        {
            return Amount * TipPercent;
        }

        public double CalculateTax()
        {
            return Amount * TAX;
        }

        public double CalculateTotal()
        {
            return Amount + (Amount * TipPercent)
                + (Amount * TAX);
        }

        public override string ToString()
        {

            StringBuilder sb = new StringBuilder();
            sb.AppendLine("Amount: " + Amount.ToString("$#,###.00"));
            sb.AppendLine("Tip: " + CalculateTip().ToString("$#,###.00"));
            sb.AppendLine("Tax: " + CalculateTax().ToString("$#,###.00"));
            sb.AppendLine("Total: " + CalculateTotal().ToString("$#,###.00"));
            return sb.ToString();
        }
    }
}

Here is the code behind the Form

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;

namespace Assignment12
{
    /// 

    /// Interaction logic for MainWindow.xaml
    /// 
    public partial class MainWindow : Window
    {
        public MainWindow()
        {
            InitializeComponent();
        }

        private void btnCalculate_Click(object sender, RoutedEventArgs e)
        {
            Calculate();
        }

        private void Calculate()
        {
            Tip t = new Tip();
            double amount;
            bool goodAmount = double.TryParse(txtAmount.Text, out amount);
            if (goodAmount)
            {
                t.Amount = amount;
            }
            else
            {
                MessageBox.Show("Enter a valid Amount");
                txtAmount.Clear();
                txtAmount.Focus();
                return;
            }
            t.TipPercent = GetTipPercent();

            lblResult.Content = t.ToString();

        }

        private double GetTipPercent()
        {
            double tipPercent = 0;
            if (rdbTenPercent.IsChecked == true)
                tipPercent = .1;
            if (rdbFifteenPercent.IsChecked == true)
                tipPercent = .15;
            if (rdbTwentyPercent.IsChecked == true)
                tipPercent = .2;
            if (rdbOther.IsChecked == true)
            {
                bool goodPercent = double.TryParse(txtOther.Text, out tipPercent);
                if (tipPercent > 1)
                    tipPercent /= 100;
            }
            return tipPercent;

        }

        private void btnClear_Click(object sender, RoutedEventArgs e)
        {
            Clear();
        }

        private void Clear()
        {
            txtAmount.Clear();
            txtOther.Clear();
            rdbTenPercent.IsChecked = false;
            rdbFifteenPercent.IsChecked = false;
            rdbTwentyPercent.IsChecked = false;
            rdbOther.IsChecked = false;
            lblResult.Content = "";
        }

        private void btnExit_Click(object sender, RoutedEventArgs e)
        {
            //this.Close();
            Application.Current.Shutdown();
        }
    }
}

Web page for Donors

the SQL for the DonorLogin

use Master;
Create Login DonorLogin with password='pass';
Use communityAssist;
Create user DonorLogin for login DonorLogin;
Create Role DonorRole;
Grant select, insert on Person to DonorRole;
Grant select, insert on PersonAddress to DonorRole;
Grant select, insert on PersonContact to DonorRole;
Grant select, insert on Donation to DonorRole;

exec sp_AddRoleMember 'DonorRole', 'DonorLogin'

Here is the asp.net code for the web page

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Donor Registration</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <h1>Donor Registration</h1>
        <table>
            <tr>
                <td>Enter First Name</td>
                <td>
                    <asp:TextBox ID="FirstNameTextBox" runat="server">
                    </asp:TextBox>

                </td>
                </tr>
            <tr>
                <td>Enter Last Name</td>
                <td>
                    <asp:TextBox ID="LastNameTextBox" runat="server">
                    </asp:TextBox>

                </td>
                </tr>
            <tr>
                <td>Enter Street Address</td>
                <td>
                    <asp:TextBox ID="StreetTextBox" runat="server">
                    </asp:TextBox>

                </td>
                </tr>
            <tr>
                <td>Enter Email</td>
                <td>
                    <asp:TextBox ID="EmailTextBox" runat="server">
                    </asp:TextBox>

                </td>
                </tr>
            <tr>
                <td>Enter password</td>
                <td>
                    <asp:TextBox ID="PasswordTextBox" runat="server" TextMode="Password">
                    </asp:TextBox>

                </td>
                </tr>
            <tr>
                <td>
                    <asp:Button ID="SaveDonor" runat="server" Text="Button" OnClick="SaveDonor_Click" /></td>
                <td>
                    <asp:Label ID="ErrorLabel" runat="server" Text="Label"></asp:Label>

                </td>
            </tr>

        </table>
    </div>
    </form>
</body>
</html>

Here is the C# code with the caveat that we never got to run it, and you would need to add all the parameters to make it work.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if(Session["loggedInUser"] == null)
        {
            Response.Redirect("Login.aspx");
        }
    }

    protected void SaveDonor_Click(object sender, EventArgs e)
    {
        SqlConnection connect =
            new SqlConnection(ConfigurationManager.
            ConnectionStrings["CommunityAssistConnection"].ToString());
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = connect;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "usp_NewDonorLogin";
        cmd.Parameters.AddWithValue("@lastName", LastNameTextBox.Text);
        cmd.Parameters.AddWithValue("@FirstName", FirstNameTextBox.Text);

        connect.Open();
        cmd.ExecuteNonQuery();
        connect.Close();

    }
}

And here is the web config file with the connections 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 connectionString="Data source=.\sqlexpress; initial catalog=communityAssist; user=DonorLogin; password=pass"
       name="communityAssistConnection"/>
</connectionStrings>
    <system.web>
      <compilation debug="true" targetFramework="4.5.2" />
      <httpRuntime targetFramework="4.5.2" />
    </system.web>

</configuration>

Classes and objects 2

Here is the Term class

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace TermDictionary
{
    public class Term
    {
        /*
        this class stores a term and its
        definition
        */
        private string word;
        private string definition;

        public string Word
        {
            get
            {
                return word;
            }

            set
            {
                word = value;
            }
        }

        public string Definition
        {
            get
            {
                return definition;
            }

            set
            {
                definition = value;
            }
        }//end property

        public override string ToString()
        {
            string termDef = Word + "--" + Definition;
            return termDef;
        }

    }//end class

    public class Class1
    {
    }
}//end namespace

---

Here is the TDictionary class that stores the terms

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace TermDictionary
{
    public class TDictionary
    {
        /*
        this class stores Terms in a list
        and contains methods for Adding
        terms to the list
        and for searching the list for a particular
        term
        */
        private List<Term> terms;

        public TDictionary()
        {
            terms = new List<Term>();
        }

        public void AddTerm(Term t)
        {
            terms.Add(t);
        }

        public string Search(string word)
        {
            string def=null;
            foreach(Term t in terms)
            {
                if(t.Word.Equals(word))
                {
                    def = t.Definition;
                    break;
                }
            }
            if (def == null)
            {
                def = "definition not found";
            }
            return def;

        }


    }
}

Here is the display class
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace TermDictionary
{
    public class Display
    {
        /*
        gets the input to build the dictionary
        and allows the user to enter words to search for
        */
        TDictionary dictionary;

        public Display()
        {
            dictionary = new TDictionary();
            GetTerms();
            SearchDictionary();
        }

        private void GetTerms()
        {
            string done = "yes";
            while (done == "yes")
            {
                Term t = new Term();
                Console.WriteLine("add a word");
                t.Word = Console.ReadLine();
                Console.WriteLine("Add a definition");
                t.Definition = Console.ReadLine();
                dictionary.AddTerm(t);
                Console.WriteLine("Add another yes/no");
                done = Console.ReadLine();
            }

        }

        private void SearchDictionary()
        {
            Console.WriteLine
                ("Enter the word you want to search for");
            string word = Console.ReadLine();
            Console.WriteLine("The definition is {0}",
                dictionary.Search(word));

        }


    }
}

And here, finally, is the program class with Main()
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace TermDictionary
{
    class Program
    {
        static void Main(string[] args)
        {
            Display d = new Display();
            Console.ReadKey();
        }
    }
}

Objects part 1

Here is our Customer class

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ObjectExample
{
    public class Customer
    {
        //private fields--a class level variable
        //the describes elements of the class
        private string name;
        private string phone;
        private string email;
        private int rewardPoints;

        //default constructor, no arguments
        //initializes everything to lowest possible value
        public Customer()
        {
        }

        //overloaded constructor takes parameters
        //the compiler chooses which constructor to use
        //based on the number and types of arguments
        //but only one constructor is used at a time
        public Customer(string name, string phone,
            string email, int points)
        {
            Name = name;
            Phone = phone;
            Email = email;
            RewardPoints = points;
        }

        //public properties--properties make the
        //private fields accessible
        //set allows the user to change the value of
        //a field. Get allows the use to see the value
        //of a field
        public string Name
        {
            set { name = value; }
            get { return name; }
        }

        public string Phone
        {
            get { return phone; }
            set { phone = value; }
        }

        public string Email
        {
            get
            {
                return email;
            }

            set
            {
                email = value;
            }
        }

        public int RewardPoints
        {
            get
            {
                return rewardPoints;
            }

            set
            {
                rewardPoints = value;
            }
        }

        //public metods
        public void AddRewards(decimal amount)
        {
            RewardPoints += (int)(amount * .1M);
        }

        public void SubtractPoints(int points)
        {
            RewardPoints =- points;
        }


    }
}

Here is the Program class were we instantiate and use the Customer class

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ObjectExample
{
    class Program
    {
        static void Main(string[] args)
        {

            //this instantiates an object of the customer type
            //and then uses the propeties set to assign the values
            Customer customer = new Customer();
            customer.Name = "John Lennon";
            customer.Phone = "(212)555-3132";
            customer.Email = "john@thebeatles.org";
            customer.RewardPoints = 20;

            //this instantiates another object of the customer type
            //and uses the overloaded constructor to pass in the values
            Customer customer2 = new Customer("Ringo Starr",
                "(212)555-1234", "ringo@beatles.com", 10);

            //the output uses the get element of the properties
            Console.WriteLine(customer.Name + "\n " +
                customer.Phone + "\n"
                + customer.Email + "   "
                + customer.RewardPoints);

            customer.AddRewards(60.89M);
            Console.WriteLine("*****************");

            Console.WriteLine(customer2.Name + "\n " +
               customer2.Phone + "\n"
               + customer2.Email + "   "
               + customer2.RewardPoints);

            Console.ReadKey();
         
        }
    }
}

Guessing Game Diagram

Methods 2: the Tip Calculator

Here is the code with comments

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace TipCalculator
{
    class Program
    {
        /*
            Get the amount of the bill
            Get the tip percent
            Get the tax
            calculate the tax
            calculte the tip
            Get the total
            Display the amount, the tax and the total

    */
    //since every method uses this I set it at Class Scope
        double amount=0;
        static void Main(string[] args)
        {
            //initialize the class
            Program p = new Program();
            //call get amount
            p.GetAmount();
            //call calculate total which calls all the 
            //other methods
            p.CalculateTotal();
            p.PauseIt();
           

        }

        void GetAmount()
        {
            //this method gets the amount 
            Console.WriteLine("Enter the Amount");
            amount = double.Parse(Console.ReadLine());
        }

        double GetTipPercent()
        {
            //in this method we create a simple menu
            //to give the user a choice of tip options
            double tipPercent = 0;
            int choice = 0;
            Console.WriteLine("Choose your tip Percent");
            Console.WriteLine("1 for 10 Percent");
            Console.WriteLine("2 for 15 Percent");
            Console.WriteLine("3 for 20 Percent");
            Console.WriteLine("4 for other");
            choice = int.Parse(Console.ReadLine());
            //the switch analyses their choice and assigns
            //the appropriate tip
            switch (choice)
            {
                case 1:
                    tipPercent = .1;
                    break;
                case 2:
                    tipPercent = .15;
                    break;
                case 3:
                    tipPercent = .2;
                    break;
                case 4: 
                    //when choosing other we give them the 
                    //opportunity to enter their own tip amount
                    Console.WriteLine("Enter your alternate tip amount");
                    tipPercent = double.Parse(Console.ReadLine());
                    //make sure it is in decimal form
                    if (tipPercent > 1)
                    {
                        tipPercent =tipPercent/ 100;
                    }
            
                    break;
                default:
                    Console.WriteLine("Not a valid choice.");
                    break;
            }
            return tipPercent;

        }

        double GetTaxPercent()
        {
            //let the user enter the tax percent
            Console.WriteLine("Enter The tax Percent");
            double taxPercent = double.Parse(Console.ReadLine());
            if (taxPercent > 1)
            {
                taxPercent /= 100;
            }

            return taxPercent;
        }

        double CalculateTax()
        {
            //calculate the tax --calls
            //get Tax percent
              double tax=amount * GetTaxPercent();
               return tax;
        }

        double CalculateTip()
        {
            //calculate tip calls GetTipPercent
            return amount * GetTipPercent();
        }

        void CalculateTotal()
        {
            //CalculateTip calls GetTipPercent
            //CalculateTax call GetTaxPercent.
            //This method also calls Display,
            //so this is the only method besides
            //GetAmount that we need to call
            //From Main
            double tip = CalculateTip();
            double tax = CalculateTax();
            double total = amount + tax + tip;
            //call display and pass the values
            Display(tip, tax, total);

        }

        void Display(double tip, double tax, double total)
        {
            //display the results
            Console.WriteLine("Your amount is {0}", amount);
            Console.WriteLine("the tip is {0}", tip);
            Console.WriteLine("Your tax is {0}", tax);
            Console.WriteLine("the Total is {0}", total);
        }

        void PauseIt()
        {
            //pause the console
            Console.WriteLine("Press any key to exit");
            Console.ReadKey();
        }

    }
}

Here is the diagram we drew of how the methods execute

SQL Part 2

use CommunityAssist
--another join of three tables
--the as Aliases a column giving it a temporary
--name for the result table
--the as key word is actually optional
Select p.Personkey, PersonLastName as "Last Name", 
   PersonFirstname as "First Name",
   [Street],[Apartment],[State],[City],[Zip],
 [DonationDate],[DonationAmount]
 From Person  p
 inner join PersonAddress pa
 on p.PersonKey=pa.PersonKey
 inner join donation d
 on p.PersonKey=d.PersonKey
 where DonationAmount > 1000
 order by DonationAmount desc

 --a cross join matches every value in the first table
 --to every value in the second table
 Select PersonLastName, DonationAmount
 From Person
 cross Join Donation

 --getdate is a function that returns the current date and time
 select GetDate() as Today

 Select * from Donation

 --some date functions
 Select Distinct Year(DonationDate) as [Year] from Donation
 Select Distinct Month(DonationDate) as [Month] from Donation
 Select Distinct Day(DonationDate) as [Day] from Donation

 --DateDiff is a function that subtracts one date from
 --another. You have to specify the unit, the one below
 --specifies days, yy is years, mm is month
 Select DonationDate, DonationConfirmDate, 
 DateDiff(dd,DonationDate, DonationConfirmDate) 
 as "Time to Confirmation"
 From Donation

 --these are the basic aggregate functions
 Select Sum(DonationAmount)From Donation
 Select Avg(DonationAmount)From Donation
 Select Count(DonationAmount)From Donation
 Select Max(DonationAmount)From Donation
 Select Min(DonationAmount)From Donation

 --any column that is not included in the aggregate function
 --in this case sum must be include in a group by statment
 --the group by prioritizes the grouping from left to right
 Select Year(DonationDate) as [year],
  Month(DonationDate) as [Month],
  Sum(donationAmount) as total
 From Donation
 group by Year(DonationDate), Month(DonationDate)

  Select Year(DonationDate) as [year],
  Month(DonationDate) as [Month],
  Avg(donationAmount) as Average
 From Donation
 group by Year(DonationDate), Month(DonationDate)

 --the having clause is like the where clause
 --but is used when the criteria includes
 --an aggregate function, in this case count
 Select Month(DonationDate) as [Month],
  Day(DonationDate) as [Day], 
  count(*) as [Count]
  From donation
  group by Month(DonationDate), Day(DonationDate)
  Having Count(DonationAmount) > 2

  --insert a new person and then a new donation
  Insert into Person(PersonLastName, PersonFirstName)
  values('Bird','Larry')
  --the ident_current function returns the last autonumber
  --created in the table listed--it only works with autonumbers
  --(identities)
  Insert into Donation([DonationDate],[DonationAmount],[PersonKey])
  Values(GetDate(), 5000.00,ident_Current('Person'))

  Select * from Person

  Select * From Donation

  --update changes existing data
  --it is crucial to have a where clause
  Update Person
  set PersonFirstname = 'Jason'
  where PersonKey=1

  --transition manually creates a transistion
  --this allows you the possiblity of a undo (Rollback)
  Begin tran

  update Person
  Set PersonLastName='Smith'

  --undo the above transaction
  rollback tran
  --or write it
  commit tran

  --won't work because person 3 has related records 
  --in other tables
  Delete from Person where personkey=3

  Begin tran

  --this however will delete eveything in donation
  Delete from Donation

Methods 1

Here is the code we did in class for Methods.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace methodsexample
{
    class Program
    {

        //this is a class level variable
        //a variable has the "scope" of the block
        //it is declared in--the class
        //That means age can be seen and accessed
        //by any method in the class
        int age = 0;
        static void Main(string[] args)
        {
            //because Main is static it is loaded into memory
            //immediately, but the rest of the class is not
            //this line loads the rest of the class into memory
            Program p = new Program();
            //this calls the method "Hello." To Call a method just
            //name it and pass any parameters
            //p is our variable standing for the class Program
            //p.Hello() means the method belongs to the Program class
            p.Hello();
            //call Pauseit
            p.PauseIt();
           
        }

        //void means it doesn't return anything
        //it just does its stuff and returns
        //to where it was called
        void Hello()
        {
            Console.WriteLine("Hello");
            //call the method GetName()
            GetName();
        }

        void GetName()
        {

            Console.WriteLine("What is your name?");
            string person=Console.ReadLine();
            //call the method getAge() and pass it the string parameter 
            //person
            GetAge(person);
        }

        //this method takes an argument or Parameter 
        //of the type string
        void GetAge(string nom)
        {
            Console.WriteLine("What is your age?");
            //age has class scope. It is important to not
            //re-declare it here by saying "int age"
            //if you do C# gives the local variable precidence
            //and the class level age will not get a value
            age = int.Parse(Console.ReadLine());
            //calls the Method HowIsItGoing() and passes
            //the parameter nom
            HowIsItGoing(nom);
        }

        //This method returns a value of the type int
        //Any method that returns something other than void
        //must have a return statment that returns
        //a value of the kind indicated
        int GetBirthYear()
        {
            int years = 0;
            //use the built in DateTime class to get the year
            int currentYear = DateTime.Now.Year;
            //subtract the age from the current year
            years = currentYear - age;
            //return the resulting value
            return years;

        }


        void HowIsItGoing(string name)
        {
            Console.WriteLine
                ("You are {0} years old. How's it going, {1}?",age, name);
            //this calls the GetBirthYear() function and store the value
            //it returns in the variable birthYear
            //if you don't assign the returned value to a variable
            //it just goes away
            int birthYear = GetBirthYear();
            Console.WriteLine("you were born in {0}", birthYear);
        }


        void PauseIt()
        {
            Console.WriteLine("Press any key to exit");
            Console.ReadKey();
        }


    }
}

SQL part 1

Here is the SQL we did in class so far

--this changes the context to the database communityAssist
use CommunityAssist;

--a select statement that choose fields
SELECT 
Personlastname, 
PersonfirstName, 
PersonUserName
FROM Person;

--the astric * is a wild card saying return all columns
--order by sorts, desc sorts in reverse
Select * from Person
order by PersonLastName desc, PersonFirstname desc;

--the where clause limits what "rows" are returned
Select * From Person
Where PersonLastName = 'Baker';

Select * From Person
Where PersonLastName Like '%B%';

Select * from PersonAddress;

Select * from PersonAddress
where Apartment is not null

Select * From ServiceGrant
Where GrantAllocation > 400

Select * From ServiceGrant
where GrantDate between '2013-08-09' and '2013-08-19'

--inner joins join data from two or more related tables
Select PersonLastName, PersonFirstname,
GrantDate, GrantAmount, ServiceName, GrantNeedExplanation
From Person
inner join ServiceGrant
on Person.PersonKey=ServiceGrant.PersonKey
inner Join CommunityService
on CommunityService.ServiceKey = ServiceGrant.ServiceKey
Where PersonLastName='Anderson'

arrays

Here is the code from class such as it is.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace arraysExample
{
    class Program
    {
        static void Main(string[] args)
        {
            //let someone enter test scores
            //let them enter as many as they want
            // enter -1 to quit
            double[] grades;
            double grade = 0;
           // double sum = 0;
            int counter = 0;
            /*
            every value is distinquished by an index

                    double[] grades = new double[20];
                    grades[0]=100;
                    grades[1]=98;
                    grades[2]=10;
                    grades[3]=84;
                    grades[19]=34;
            this is an alternate way to declare an array
            double grades[] = new double[] {90, 30, 24.5, 60, 21.5};
            */
            Console.WriteLine("about how many grades do think you want to enter");
            int number = int.Parse(Console.ReadLine());

            grades = new double[number];


            Console.WriteLine("Use -1 to exit");
            do
            {
                Console.WriteLine("Enter Grade");
                grade = double.Parse(Console.ReadLine());

                if (grade != -1)
                {
                    if (counter < grades.Length)
                    {
                        //sum += grade;
                        grades[counter] = grade;
                        counter++;
                    }
                }

            } while (grade != -1);

            for (int i = 0; i < grades.Length; i++)
            {
                Console.WriteLine(grades[i]);
            }

            double average = grades.Sum() / counter;
            Console.WriteLine("you entered {0} grades", counter);
            Console.WriteLine("The average is {0}", average);

            Console.WriteLine("the Fifth Element is {0}", grades[4]);
            grades[4] = 90;
            Console.WriteLine("the fifth element is now {0}", grades[4]);

            Random rand = new Random();
            int[] randomNumbers = new int[10];

            for (int i = 0; i < randomNumbers.Length; i++)
            {
                randomNumbers[i] = rand.Next(1, 101);
            }

            for (int i = 0; i < randomNumbers.Length; i++)
            {
                Console.WriteLine(randomNumbers[i]);
            }

            Console.WriteLine("the total is {0}", randomNumbers.Sum());
            Console.WriteLine("The Average is {0}",
                randomNumbers.Average());


            string[,] songs = new string[3, 2];
            songs[0, 0] = "Royals";
            songs[0, 1] = "Lorde";
            songs[1, 0] = "Hard Days Night";
            songs[1, 1] = "Beatles";
            songs[2, 0] = "Satisfaction";
            songs[2, 1] = "Rolling Stones";

            Console.WriteLine("Give me an artist");
            string artist = Console.ReadLine();

            for (int i = 0; i < 3; i++)
            {
                if (artist.Equals(songs[i, 1]))
                {
                    Console.WriteLine(songs[i, 0]);
                }

            }






                Console.WriteLine("Press any key to exit");
            Console.ReadKey();
            

        }
    }
}

Types of Entities

Linking-- connect two Entities that have a many-to-Many relationship resulting in two one-to-many relationships

Domain Entity—Entities that cover main business Customer, Album, Sale, Employee

Lookup Entity-Consistency – List of all states, Rating

Weak Entities--depends on another Entity for its meaning Sale/SaleDetail Employees/Dependents

Loops

Here is the code from class. It is, of course, not a coherent program just a set of examples

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace LoopExamples
{
    class Program
    {
        static void Main(string[] args)
        {
            //this is a simple for loop
            //first you set the counter, then you set
            //the limit, then you increment (++) the counter
            for (int counter = 0; counter < 20; counter++)
            {
                Console.WriteLine(counter);
            }

            //this is a for loop that outputs the prime numbers
            //from the previous assignment
            for (int i = 1; i < 41; i++)
            {
                int prime = i * i - i + 41;
                Console.WriteLine(prime);
            }

            //your can use a variable for the loop limit
            Console.WriteLine("how many loops do you want to do?");
            int number = int.Parse(Console.ReadLine());

            //avoid an infinite loop--infinite loops result
            //when the end condition will never be met
            for (int x = 1; x < number; x--)//decrement -1
            {
                Console.WriteLine(x);
                if (x == -10)
                {
                    break;
                }

            }

            //you can use other operators that ++ or --
            //this one counts up by 3s
            for (int i = 1; i < 20; i += 3)
            {
                Console.WriteLine(i);
            }

            // +=, -=, *=, /=, %=
            //number -= 2  equivalent to number = number - 2
           // number *= 2 equivelant to number = number * 2

            Console.WriteLine("how many numbers do you want to enter");
            int num = int.Parse(Console.ReadLine());
            //I declare these outside the loop so that I can use them
            //outside--if I declared them in the loop they would 
            //have the "scope" of the loop. In general a variable has the scope
            //of the block "{}" it is declared in
            double sum = 0;
            double average = 0;

            for (int i = 1; i <= num; i++)
            {
                Console.WriteLine("enter a number");
                double myNumber = double.Parse(Console.ReadLine());
                sum += myNumber;

            }

            Console.WriteLine("the sum is {0}", sum);
            average = sum / num;
            Console.WriteLine("The average is {0}", average);

            //a while loop continues looping until the critera
            //set at the beginning is no longer true

            string quit = "no";
           //Equals is equivalent to "==" but works better with strings
            while (quit.Equals("no") || quit.Equals("No"))
            {
                Console.WriteLine("Are your ready to quit 'yes/no");
                quit = Console.ReadLine();
                quit = quit.ToLower();//this forces everything to lower case
            }

            bool goodNumber = false;
            int number2=0;
            //this loops until the user enters a good number
            while (goodNumber == false)
            {
                Console.WriteLine("enter a valid number");
                goodNumber = int.TryParse(Console.ReadLine(), out number2);
            }

            Console.WriteLine("Your Number is {0}", number2);

            // a do while loop is like a while loop except it checks its
            //criteria at the end. that means it is guaranteed to execute
            //at least once
            do
            {
                Console.WriteLine("enter a valid number");
                goodNumber = int.TryParse(Console.ReadLine(), out number);

            } while (goodNumber == false);




            Console.ReadKey();

        }
    }
}

Normalization

First Normal Form

All data in a given attribute must be of the same kind No attribute should contain arrays—(lists of items) and there should be no repeating values

CDKey CDTitle
1     Jakalope
2     Popcorn

TrackKey CDKey  TrackTitle
1         1     Pretty Life
2         1     Feel It
3         1     Go Away
4         2     Please Please Please
5         2     Try me

Second Normal Form

Remove all functional dependencies Every entity should be about only one thing Groups of Attributes that depend on each other rather than on the main theme (key) of the Entity

Third Normal Form

Remove all transient dependencies Usually one field that refers to another field for its meaning rather than describing the whole topic (the key)

Here is the CD ERD after normalization

Here is the sale saleDetail relation

Selection examples

The if examples

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ifStatementExamples
{
    class Program
    {
        static void Main(string[] args)
        {
            int number;
            Console.WriteLine("Enter a number");
            //the tryParse returns a boolean
            //if the number is good it returns true
            //if the number is not good it returns false
            //if the number is good it also assigns it to the variable specified in the
            //out parameter, if it is not good it assigns 0
            bool goodNumber = int.TryParse(Console.ReadLine(), out number);

            // ! not, != not equal, == equals
            if (goodNumber == false)
            {
                Console.WriteLine("Please enter a good number");
                Console.ReadKey();
                return;
            }


            if (number > 20)
            {
                Console.WriteLine("your number is greater than 20");
            }
            else
            {
                Console.WriteLine("Your number is less than 20");
            }
            //&& = and
            //|| = or

            if (number > 0 && number <= 20)
            {
                Console.WriteLine("Your number is between 1 and 20");
            }
            else if (number > 20 && number <=  50)
            {
                Console.WriteLine("Your number is between 21 and 50");
            }
            else if (number > 50 && number <= 100)
            {
                Console.WriteLine("Your number is between 51 and 100");
            }
            else
            {
                Console.WriteLine("Your number is more than 100");
            }

            int number2;
            Console.WriteLine("Enter a number between 1 and 5");
            bool goodNumber2 = int.TryParse(Console.ReadLine(), out number2);

            if (!goodNumber2)
            {
                Console.WriteLine("Please enter a good number");
                Console.ReadKey();
                return;
            }

            //a switch is good for some things but is less
            //flexible than a if elseif. It can't test a range
            //of values but only specific values
            switch (number2)
            {
                case 1:
                    Console.WriteLine("One");
                    break;
                case 2:
                    Console.WriteLine("Two");
                    break;
                case 3: //you can fall through to the next case
                case 4:
                    Console.WriteLine("Three or Four");
                    break;
                case 5:
                    Console.WriteLine("Five");
                    break;
                default:
                    Console.WriteLine("Not between 1 and 5. Follow directions!");
                    break;
            }





            Console.ReadKey();

        }
    }
}

Here is the code for the extra credit

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            //determine how many busses you need
            //each bus has a capacity of 45
            // const is used to declare a constant. that means a value that
            //cannot be changed by the program
            const int CAPACITY= 45;
            int students;
            int busses;

            Console.WriteLine("How many people need a ride");
            bool goodNumber = int.TryParse(Console.ReadLine(), out students);

            //! means not a good number
            if (!goodNumber)
            {
                Console.WriteLine("Please enter a valid number");
                Console.ReadKey();
                return;
            }


            busses = students / CAPACITY;

            if (students % CAPACITY > 0)
            {
                //equivelent to busses = busses + 1
                //also could do busses++ which increments by 1
                busses += 1;
            }

            Console.WriteLine("You will need {0} busses", busses);

            Console.ReadKey();

            //the peer excercise
            //if(number % 2 ==0)
            // even
            //else
            //odd

        }
    }
}

Diagraming a database 1

Modeling a Database

Field list for DVDs

Groups: Entity should be about one thing: No multi valued groups

Candidate Key-- surrogate auto or random number for keys- Natural key is a natural field in the table

DVD

Title, length, Release Date, origin year, purchase year, number of disks, Rating, Description, Price, copyright

Actors

Name, gender, awards, alias

Features

FeatureType, description

Studio

Name, Location

Languages

name

Writer

Name

Genre

Name, description

Original list of fields

Name or title
director
Lead actors
Time length
Release year
Original year
Genre
Number of disks
Studio
Description
Writer
Language choices
Rating
Extras
Plot summary
Country of Origin
Date purchased
Price
copyright

Relationship types

Initial Diagram DVDs Actors

Data Examples

Requirements and Business Rules

Requirements and Business Rules

Here are the notes that we did in class, such as they are

---

Things the database must do
*Data requirements
*Reporting Requirements
*Security Requirements who will be using the database what permissions will they need

---

Some data requirements

Show Dates and times
Venues address city state zip phone webpage name email
Capacity restrictions description handicap access
Act capacity reserved vs open
Restrictions
Contact for venues acts
Customer list subscription to acts or venues

Reporting Requirements

Calendar
View a particular venue and see upcoming shows
View an act and see where they are playing
Query a venue based on restrictions

Security requirements:

Access management--
support
Application—general
Select (read) (CRUD) (except the customer or fan lists)
Insert (put in new records)
Update (change or edit existing records)
Delete (delete records)
Venues—update, insert, select --constraint
only their own data
Customer fan—select insert update only own information

---

Business rules

How things
Venues have to enter their own schedules
Rule that venues must update weekly
Confirmation email for new customers

Ints doubles and operators

Here is the code from today's class.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ExamplesForAssignment2
{
    class Program
    {
        /*************************
        These are examples for assignment 2
        involving operators 
        +   addition
        -    subtraction
        *    multiplication
        /    division
        %   modulus--remainder in integer division
        and the numeric data types
        int, whole number
        and double, float or decimal 
        ****************************/

        static void Main(string[] args)
        {
            //number variables int double
            int number, number2;
            int sum, difference, product, quotient, remainder;
            double  exponent;
            double decimalQuotient;

            //inputs
            Console.WriteLine("enter an integer");
            //C# treats all input from the console as a string
            //Parse removes the quotes and sees if the content
            //is of the correct type--in this case int
            number = int.Parse(Console.ReadLine());

            Console.WriteLine("enter another integer");
            number2 = int.Parse(Console.ReadLine());

            Console.WriteLine("Enter a double");
            //this is an example of parsing a double from the console
            double newNumber = double.Parse(Console.ReadLine());

            //algorithm
            //Here are all the operators
            sum = number + number2;
            difference = number - number2;
            product = number * number2;
            quotient = number / number2;
            remainder = number % number2;
            //in this one we cast one of the sides to a double
            //the equation always defaults to the type with the higher precision
            //doubles always have a higher precision because they contain doubles
            //this makes it so the result returns the decimal part
            decimalQuotient = (double)number / number2;
            //the Math library is static and always available
            exponent = Math.Pow(number, number2);

            //outputs
            Console.WriteLine("The sum of {0}, and {1} is {2}", number, number2, sum);
            Console.WriteLine("The difference of {0}, and {1} is {2}", number, number2, difference);
            Console.WriteLine("The product of {0}, and {1} is {2}", number, number2, product);
            Console.WriteLine("The quotient of {0}, and {1} is {2}", number, number2, quotient);
            Console.WriteLine("The remainder of {0}, and {1} is {2}", number, number2, remainder);
            Console.WriteLine("The exponent of {0}, and {1} is {2}",number, number2, exponent);
            Console.WriteLine("The decimal quotient of {0}, and {1} is {2}", number, number2, decimalQuotient);

            Console.WriteLine("Press any key to exit");
            Console.ReadKey();



        }
    }
}

Information Gathering

Information Gathering

Here are our class notes on information gathering, such as they are.

Ask client (interviews) -- prepare
One retreat profession facilitator record everything
stakeholders (access) security
current problems
why they want a new database
What the database requires
What would they like beyond requirements
Growth scalability

 

Look at the existing systems Budget Security
Phone/dept (should never have two kinds of values in a field)
Forms (entering data) fields
Reports (displaying data) summary

 

Job Shadowing
Exceptions
flow

First assignment example

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace AssignmentExamples
{
    class Program
    {
        //comments with your name 
        static void Main(string[] args)
        {
            //prompt
            Console.WriteLine("Enter your favorite color ");
            //getting input
            string colorChoice= Console.ReadLine();
            Console.WriteLine("Enter your favorite animal");
            string animal = Console.ReadLine();
            //output results with placeholders
            Console.WriteLine
                ("Your favorite color is {0}, and your favorite animal is a{1}"
                ,colorChoice, animal);
            
            //concatination
            Console.WriteLine("your favorite color is " + colorChoice +
                ", your favorite animal is a " + animal);

            //pause long enough to read it
            Console.ReadKey();


        }
    }
}

Login Stored procedures and test database

Use Master
go
Create database LoginTest
Go
Use LoginTest
go
Create table SecurityQuestion
(
  QuestionKey int identity(1,1) primary key,
  Question nvarchar(255) not null
)

go

Create table UserLogin
(
   UserKey int identity(1,1) primary key,
   UserName nvarchar(50) not null,
   UserEmail nvarchar(255) not null,
   UserRandomInt int not null,
   UserPassword varBinary(500) not null,
   UserDateEntered Date not null,
   UserDateLastModified Date not null,

)
alter table UserLogin
add constraint unique_UserName unique(userName)

alter table UserLogin
add constraint unique_Email unique(useremail)

go

Create Table UserSecurityQuestion
(
 UserKey int Foreign Key references UserLogin(UserKey) not null,
 QuestionKey int Foreign Key references SecurityQuestion(QuestionKey) not null,
 UserAnswer NVarchar(255) not null,
 Constraint PK_UserSecurityQuestion primary key(UserKey, QuestionKey),
 
)

Go
Create table LoginHistory
(
 LoginHistoryKey int identity(1,1) primary key,
 UserKey int foreign key references UserLogin(userKey),
 LoginHistoryDateTime datetime default GetDate()
)

Go

Insert into SecurityQuestion(Question)
values('Where were you when you got your first traffic ticket?'),
('What is your least favorite book?'),
('What acloholic drink made you the sickest?'),
('What food do you truely hate?')

go
create function fx_hashPassword
(@password nvarchar(50), @RandomInt int)
returns varbinary(500)
As
begin
Declare @Combined nvarchar(60)
Declare @hashed varbinary(500)
Set @Combined = @password + cast(@randomInt as Nvarchar(10))
Set @hashed = HASHBYTES('sha2_512', @combined)
return @hashed
End
go

Select dbo.fx_hashPassword('mypass','1342567901')

Go

Create function fx_getRandomInt()
returns int
As
Begin
Declare @intNumber int
set @intNumber=DatePart(NanoSecond, GetDate())
return @intNumber
End

go

--password, all the info for login table
--userKey as output
--write to userlogin table
--write security question table
--write to login history table
--put in transaction

Alter proc usp_NewLogin
@userName nvarchar(50),
@Password nvarchar(50),
@userEmail nvarchar(255),
@securityQuestion int,
@answer nvarchar(255)
As
--declare internal variables
Declare @intRandom int
Declare @hash varbinary(500)
Declare @Date Date
--check to see if user exists
If Exists
 (Select userKey from userLogin
 where userName=@userName
 And UserEmail=@userEmail)
Begin
Print 'user already exists'
return -1
End

--get random seed 
select @intRandom=dbo.fx_getRandomInt()
--get hash of password
select @hash = dbo.fx_hashPassword(@password, @intRandom)

Set @date =GetDate()
--Begin transaction
Begin tran
Begin try
--insert int userLogin
Insert into UserLogin(UserName, UserEmail, UserRandomInt, UserPassword, UserDateEntered, UserDateLastModified)
Values(@username, @userEmail, @intRandom, @hash, @date, @date)

Declare @UserKey int
Set @UserKey= Ident_Current('UserLogin')
--insert into userSecurityQuestion
Insert into UserSecurityQuestion(UserKey, QuestionKey, UserAnswer)
Values(@UserKey, @SecurityQuestion, @answer)
--Insert into LoginHistory
Insert into LoginHistory(UserKey, LoginHistoryDateTime)
values(@UserKey, @Date)
commit tran
Return @userKey
End Try
Begin Catch
Rollback tran
return 0
End Catch

Exec usp_NewLogin
@userName='George', 
@Password='P@ssw0rd1', 
@userEmail='George@gmail.com', 
@securityQuestion=3, 
@answer='whiskey'

Select * from SecurityQuestion

Select * From userLogin
Select * From UserSecurityQuestion
Select * From LoginHistory



--existing login
--intake password username
--get the salt that goes with the username
--(-1) if no username
--rehash the text password with the salt
--compare the hashes
--if they match the login is successful return user key
--if they fail return 0
go
Alter proc usp_Login
@Password nvarchar(50),
@userName nvarchar(50)
As
Declare @intRandom int
Declare @Newhash varbinary(500)
Declare @DBHash varbinary(500)
Declare @UserKey int

Select @UserKey= userKey, @intRandom=UserRandomInt, @DBHash=userPassword from UserLogin
Where UserName=@userName

if @IntRandom is null
 Begin
  Print '-1'
  Return -1
 End
Select @newHash=dbo.fx_hashPassword(@password, @intRandom)

if @DBHash=@Newhash
 Begin

 insert into LoginHistory(UserKey, loginHistoryDateTime)
 Values(@UserKey,GetDate())

 print cast(@UserKey as nvarchar(10))
    Return @UserKey
 end
Else
   Begin
  print '0'
  Return 0
   End


Exec usp_login
@Password='P@ssw0rd1', 
@userName='spconger'

Select * From LoginHistory


/*
login and validate
Get new Password
Rehash the passord
Update the Login table
*/
go

Create proc usp_ChangePassword
@userName nvarchar(50),
@password nvarchar(50),
@newPassword nvarchar(50)
As
Declare @intRandom int
Declare @Newhash varbinary(500)
Declare @DBHash varbinary(500)
Declare @UserKey int

Select @UserKey= userKey, @intRandom=UserRandomInt, @DBHash=userPassword from UserLogin
Where UserName=@userName

if @IntRandom is null
 Begin
  Print '-1'
  Return -1
End
Select @newHash=dbo.fx_hashPassword(@password, @intRandom)

if @DBHash=@Newhash
 Begin
 Declare @newRandom int
 Set @newRandom=dbo.fx_getRandomInt()
 Declare @UpdateHash varbinary(500)
 Set @UpdateHash=dbo.fx_hashPassword(@newPassword, @NewRandom)

 update UserLogin 
 Set UserRandomInt=@newRandom,
 UserPassword=@updateHash
 Where userKey=@UserKey


 insert into LoginHistory(UserKey, loginHistoryDateTime)
 Values(@UserKey,GetDate())

 print cast(@UserKey as nvarchar(10))
    Return @UserKey
 end
Else
   Begin
  print '0'
  Return 0
   End

   Select * from userLogin

   exec usp_ChangePassword
   @userName = 'spconger', 
   @password='P@ssw0rd1', 
   @newPassword='P@ssw0rd2'

   Exec usp_login
@Password='P@ssw0rd2', 
@userName='spconger'

Full Text Catalog

use Master
go
Create database FullTextExample
go
Alter Database FullTextExample
Add Filegroup FullTextCatalog
Go
Use FullTextExample
Go
Create Table Test
(
   testID int identity(1,1) primary Key,
   TestText Nvarchar(255)
)
Go
Insert into Test(TestText)
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')

Insert into Test(TestText)
Values('I go to work sadly'),
('I went to work yesterday'),
('I have gone to work every day'),
('going to work')

Select * From test

Create FullText Catalog TestDescription
on Filegroup FullTextCatalog
Go
Create FullText index on Test(TestText)
Key Index [PK__Test__A29BFBA819F32655]
on TestDescription
With Change_tracking auto
go
--find all instances that have the word "sad"
Select TestID, TestText 
From Test
Where FreeText(TestText, 'sad')

Select TestID, TestText 
From Test
Where FreeText(TestText, 'success')

Select TestID, TestText 
From Test
Where FreeText(TestText, 'men')

Select TestID, TestText 
From Test
Where FreeText(TestText, 'relative')

Select TestID, TestText 
From Test
Where FreeText(TestText, 'Success')

Select TestID, TestText 
From Test
Where FreeText(TestText, 'is')

Select TestID, TestText 
From Test
Where Contains(TestText, '"success*"')


Select TestID, TestText 
From Test
Where Contains(TestText, ' Formsof (Inflectional, see)')

Select TestID, TestText 
From Test
Where Contains(TestText, ' Formsof (Inflectional, go)')

Select TestID, TestText 
From Test
Where Contains(TestText, 'Near ((work, day), max)')

Select * From Test

Data Warehouse SQL Script

Use master
go
if exists (Select name from sys.Databases 
where name='MetroAltDw')
Begin
Drop Database MetroAltDW
End
Go
Create Database MetroAltDW
Go
Use MetroAltDW
Go
Create table DimEmployee
(
   DimEmployeeKey int identity(1,1),
   EmployeeKey int unique,
   EmployeelastName nvarchar(255),
   EmployeeFirstName nvarchar(255),
   EmployeeEmail nvarchar(255),
   EmployeeCity nvarchar(255),
   EmployeeZipCode nchar(5),
   EmployeeHireDate date,
   PositionName nvarchar(255),
   EmployeeHourlyPayRate decimal(5,2)

)
go
Create table DimBus
(
    DimBusKey int identity(1,1),
 BusKey int unique,
 BusPurchaseDate Date,
 BusTypeDescription nvarchar(255),
 BusTypeCapacity int,
 BusTypePurchaseprice decimal(12,2)
)
Go
Create table DimRoute
(
 DimRouteKey int identity(1,1),
 BusrouteKey int unique,
 BusRouteZone nvarchar(255)
)
go
Create table DimBusScheduleAssignment
(
   DimBusScheduleAssignmentKey int identity(1,1),
   BusScheduleAssignmentKey int unique,
   ShiftName nvarchar(255)
   
)
go
Create table DimDate
(
 DimDateKey int not null,
 BusScheduleAssignmentDate Date unique,
 BusScheduleAssignmentYear int,
 BusScheduleAssignmentMonth int,
 BusScheduleAssignmentDay int,
 BusScheduleAssignmentDayOfWeek nvarchar(30)

)

go

Create table FactBusSchedule
(
 DimEmployeeKey int not null,
 DimBusKey int not null,
 DimRouteKey int not null,
 DimBusScheduleAssignmentKey int not null,
 DimDateKey int not null,
 Riders int,
 FareAmount money,
 FareImplementationDateYear int
)
Go
alter table DimEmployee
Add Constraint PK_DimEmployee 
primary key(DimEmployeeKey)

alter table DimBus
Add Constraint PK_DimBus 
primary key(DimBusKey)

alter table DimRoute
Add Constraint PK_DimRoute 
primary key(DimRouteKey)

alter table DimBusScheduleAssignment
Add Constraint PK_DimBusScheduleAssignment 
primary key(DimBusScheduleAssignmentKey)

alter table DimDate
Add Constraint PK_DimDate 
primary key(DimDateKey)

alter table FactBusSchedule
Add Constraint PK_FactBusSchedule 
primary key(DimEmployeeKey, DimRouteKey,
DimBusKey, DimBusScheduleAssignmentKey,
DimDateKey)

Alter table FactBusSchedule
Add Constraint FK_DimEmployee
Foreign Key (DimEmployeeKey) 
References DimEmployee(DimEmployeeKey)

Alter table FactBusSchedule
Add Constraint FK_DimBus
Foreign Key (DimBusKey) 
References DimBus(DimBusKey)

Alter table FactBusSchedule
Add Constraint FK_DimRoute
Foreign Key (DimRouteKey) 
References DimRoute(DimRouteKey)

Alter table FactBusSchedule
Add Constraint FK_DimBusScheduleAssignment
Foreign Key (DimBusScheduleAssignmentKey) 
References DimBusScheduleAssignment(DimBusScheduleAssignmentKey)

Alter table FactBusSchedule
Add Constraint FK_DimDate
Foreign Key (DimDateKey) 
References DimDate(DimDateKey)

XML

here is the xml file we made: memo.xml

<?xml version="1.0" encoding="utf-8"?>
<?xml-stylesheet type="text/xsl" href="memo.xslt"?>
<m:memo xmlns:m="http://www.MetroAlt.com/memo">
 <m:heading>
  <m:to>Drivers</m:to>
  <m:from>Dispatchers</m:from>
  <m:about>Road Closures</m:about>
  <m:date>2015-07-28</m:date>
 </m:heading>
 <m:body>
  <m:p>There are several road closures this week.</m:p>
  <m:p>Watch for bulletons and notices.</m:p>
 </m:body>
</m:memo>

here is the schema that describes the xml document: memo.xsd

<?xml version="1.0" encoding="utf-8"?>
<xs:schema attributeFormDefault="unqualified" 
     elementFormDefault="qualified" 
     targetNamespace="http://www.MetroAlt.com/memo" 
     xmlns:xs="http://www.w3.org/2001/XMLSchema">
 <xs:element name="memo">
  <xs:complexType>
   <xs:sequence>
    <xs:element name="heading">
     <xs:complexType>
      <xs:sequence>
       <xs:element name="to" type="xs:string" />
       <xs:element name="from" type="xs:string" />
       <xs:element name="about" type="xs:string" />
       <xs:element name="date" type="xs:date" />
      </xs:sequence>
     </xs:complexType>
    </xs:element>
    <xs:element name="body">
     <xs:complexType>
      <xs:sequence>
       <xs:element maxOccurs="unbounded" name="p" type="xs:string" />
      </xs:sequence>
     </xs:complexType>
    </xs:element>
   </xs:sequence>
  </xs:complexType>
 </xs:element>
</xs:schema>

Here is the xslt file: memo.xslt

<?xml version="1.0" encoding="utf-8"?>
<xs:schema attributeFormDefault="unqualified" 
     elementFormDefault="qualified" 
     targetNamespace="http://www.MetroAlt.com/memo" 
     xmlns:xs="http://www.w3.org/2001/XMLSchema">
 <xs:element name="memo">
  <xs:complexType>
   <xs:sequence>
    <xs:element name="heading">
     <xs:complexType>
      <xs:sequence>
       <xs:element name="to" type="xs:string" />
       <xs:element name="from" type="xs:string" />
       <xs:element name="about" type="xs:string" />
       <xs:element name="date" type="xs:date" />
      </xs:sequence>
     </xs:complexType>
    </xs:element>
    <xs:element name="body">
     <xs:complexType>
      <xs:sequence>
       <xs:element maxOccurs="unbounded" name="p" type="xs:string" />
      </xs:sequence>
     </xs:complexType>
    </xs:element>
   </xs:sequence>
  </xs:complexType>
 </xs:element>
</xs:schema>

Here are the files we did for SQL Server

Create xml Schema collection sch_memo
As
'<?xml version="1.0" encoding="utf-8"?>
<xs:schema attributeFormDefault="unqualified" 
     elementFormDefault="qualified" 
     targetNamespace="http://www.MetroAlt.com/memo" 
     xmlns:xs="http://www.w3.org/2001/XMLSchema">
 <xs:element name="memo">
  <xs:complexType>
   <xs:sequence>
    <xs:element name="heading">
     <xs:complexType>
      <xs:sequence>
       <xs:element name="to" type="xs:string" />
       <xs:element name="from" type="xs:string" />
       <xs:element name="about" type="xs:string" />
       <xs:element name="date" type="xs:date" />
      </xs:sequence>
     </xs:complexType>
    </xs:element>
    <xs:element name="body">
     <xs:complexType>
      <xs:sequence>
       <xs:element maxOccurs="unbounded" name="p" type="xs:string" />
      </xs:sequence>
     </xs:complexType>
    </xs:element>
   </xs:sequence>
  </xs:complexType>
 </xs:element>
</xs:schema>'

Create table memos
(
   memoId int identity(1,1),
   memoText xml(sch_memo)
)

Insert into memos (memoText)
Values('<?xml version="1.0" encoding="utf-8"?>
<?xml-stylesheet type="text/xsl" href="memo.xslt"?>
<m:memo xmlns:m="http://www.MetroAlt.com/memo">
 <m:heading>
  <m:from>Dispatchers</m:from>
  <m:to>Drivers</m:to>
  <m:about>Road Closures</m:about>
  <m:date>2015-07-28</m:date>
 </m:heading>
 <m:body>
  <m:p>There are several road closures this week.</m:p>
  <m:p>Watch for bulletons and notices.</m:p>
 </m:body>
</m:memo>')

Select * from Employee 
Where EmployeeCity='Kent'
For xml raw('employee'), elements, root('employees')


Select PositionName, Employeelastname, EmployeeFirstName, EmployeeEmail
From Position
inner join EmployeePosition
on Position.PositionKey=EmployeePosition.PositionKey
inner Join Employee
on Employee.EmployeeKey=EmployeePosition.EmployeeKey
Where EmployeeCity='Bellevue'
order by PositionName
for xml auto, elements, root('Employees')

use CommunityAssist
Select GrantReviewdate, EmployeeKey, GrantKey, 
GrantReviewNote.query('declare namespace rn = "http://www.communityassist.org/reviewnotes";//rn:reviewnote/rn:recommendation') as recommendations
from GrantReview

Security Stored Procedures

Select * from Position
--Driver access to own information
--personal information
--shifts driven
--pay per month
--pay per year
--pay per shift
--select route schedules
--select Bus Barn
go
Create schema DriverSchema
go
Create proc DriverSchema.usp_ViewEmployeeInfo
@EmployeeID int
As
Select e.EmployeeKey [Employee Number], 
EmployeeLastName [Last Name], 
EmployeeFirstName [First Name], 
EmployeeAddress [Address], 
EmployeeCity [City], 
EmployeeZipCode [Zip Code], 
EmployeePhone [Phone], 
EmployeeEmail [Email], 
EmployeeHireDate [Hire Date],
PositionName [Position],
EmployeeHourlyPayRate [Pay Rate]
From Employee e
inner Join EmployeePosition ep
on e.EmployeeKey=ep.EmployeeKey
inner Join Position p
on p.PositionKey=ep.PositionKey
Where e.EmployeeKey=@EmployeeID

exec DriverSchema.usp_ViewEmployeeInfo 1

Go
create proc DriverSchema.usp_shiftsDrive
@month int,
@year int,
@EmployeeID int
As
Select
[BusScheduleAssignmentDate] [Date],
[BusDriverShiftName] [Shift],
[BusRouteKey] [Route],
[BusKey] [Bus],
[BusDriverShiftStartTime] [Start],
[BusDriverShiftStopTime] [Stop],
DateDiff(hh,[BusDriverShiftStartTime],[BusDriverShiftStopTime]) [hours]
From [dbo].[BusScheduleAssignment] bsa
inner Join BusDriverShift bs
on bsa.BusDriverShiftKey=bs.BusDriverShiftKey
Where Year([BusScheduleAssignmentDate])=@Year
And Month([BusScheduleAssignmentDate])=@Month
And bsa.EmployeeKey=@EmployeeID

exec DriverSchema.usp_shiftsDrive
@Year=2014,
@Month=7,
@EmployeeID=1
go
Create Proc DriverSchema.usp_UpdatePersonal
@LastName nvarchar(255),
@FirstName nvarchar(255),
@Address nvarchar(255),
@City nvarchar(255)='Seattle',
@Zip nchar(5),
@Phone nchar(10),
@EmployeeId int
As
if exists
  (Select EmployeeKey from Employee where EmployeeKey=@EmployeeID)
Begin
Update Employee
Set [EmployeeLastName]=@Lastname,
[EmployeeFirstName]=@firstName,
[EmployeeAddress]=@Address,
[EmployeeCity]=@City,
[EmployeeZipCode]=@zip,
[EmployeePhone]=@phone
Where EmployeeKey = @EmployeeID
return 1
End
Else
Begin
Declare @msg nvarchar(30)
Set @msg='Employee Doesn''t Exist'
Print @msg
return 0
end
go
exec DriverSchema.usp_UpdatePersonal
@LastName = 'Kenner-Jones' ,
@FirstName = 'Susanne',
@Address = '234 Some Other Street',
@City ='Seattle',
@Zip= '98100',
@Phone='2065554312',
@EmployeeId =600

Select * From Employee

Create login KJSusa with password='@Passw0rd1'

Use Metroalt 
Create user KJSusa for login KJSusa with default_Schema=DriverSchema

Create role DriverRole

Grant exec, select on Schema::DriverSchema
to DriverRole

exec sp_addrolemember 'DriverRole', 'KJSusa'

SQL For Security

--see who the employees are
Select *
From Person p
inner join Employee e
on p.PersonKey = e.PersonKey

--new login
Create Login TinaMoon with password='password'

--if you had not already created it
Create schema EmployeeSchema

--user for CommunityAssist
Create user TinaMoon for Login TinaMoon
 
--new role
 Create role HumanResourcesRole

--Permission for the role
 Grant select, insert, update on Employee to HumanResourcesRole
 Grant select, insert, update on Person
 To HumanResourcesRole
 Grant select, insert, update on PersonAddress to HumanResourcesRole
 Grant Select, insert, update on
 PersonContact to HumanResourcesRole
Grant exec on usp_newDonation to HumanResourcesRole
Grant select on Schema::EmployeeSchema to HumanResourcesRole

--add use to the role
exec sp_addrolemember 'HumanResourcesRole','TinaMoon'

Queries and Procedures for Community Assist

--community Assist queries and procedures
use CommunityAssist

--Total Donations by year and month
--Total Grants requested amount allocated
--total grants by Service
--How long the employees have worked 
--Total grants per recipient--compared to lifetime maximums
go
create view vw_TotalDonations
AS
Select Year(DonationDate) as [Year],
Month(donationDate) as [Month],
Sum(donationAmount) as [Total]
From Donation
Group by Year(DonationDate), 
Month(donationDate)
go

Select * From vw_TotalDonations
go
Alter view vw_TotalGrantsByService
as
Select ServiceName as [Service],
Sum(GrantAmount) as Requested,
Sum (GrantAllocation) as Allocated,
Sum(GrantAmount)-Sum(GrantAllocation) 
as [Difference]
From CommunityService cs
inner Join ServiceGrant sg
on cs.ServiceKey=sg.ServiceKey
group by ServiceName
Go
Create Proc usp_TotalGrantsByService
@serviceName Nvarchar(255)
As
Select ServiceName as [Service],
Sum(GrantAmount) as Requested,
Sum (GrantAllocation) as Allocated,
Sum(GrantAmount)-Sum(GrantAllocation) 
as [Difference]
From CommunityService cs
inner Join ServiceGrant sg
on cs.ServiceKey=sg.ServiceKey
Where ServiceName = @ServiceName
group by ServiceName

exec usp_TotalGrantsByService 'food'

Select min(EmployeeHireDate) from Employee
go
Create view vw_EmployeeInfo
As
Select PersonLastName as [Last Name],
PersonFirstName as [First Name],
EmployeeStatus as [Status],
EmployeeMonthlySalary [Monthly Salary],
EmployeeHireDate HireDate,
DateDiff(yy,EmployeeHireDate, GetDate())
As [Years With Charity]
From Person p
Inner join Employee e
on p.PersonKey=e.PersonKey

Select * from vw_EmployeeInfo
order by [Years With Charity] desc
go
create view vw_GrantsAndServiceMaximums
As
Select PersonFirstName [First Name],
PersonLastName [Last Name],
ServiceName [Service],
ServiceLifeTimeMaximum [Maximum],
sum(GrantAllocation) Allocation
From Person p
inner join ServiceGrant sg
on p.personKey=sg.Personkey
inner join communityService cs
on cs.servicekey=sg.servicekey
Where GrantAllocation is not null
And not GrantAllocation =0
group by personFirstname,
personlastname,
ServiceName,
ServiceLifeTimeMaximum

The query for Metro alt ridership and fare totals

use MetroAlt

Select Year(BusScheduleAssignmentDate) as[Year]
,BusRoutezone,
case 
When Year(BusScheduleAssignmentDate)=2012 then 2.40
When Year(BusScheduleAssignmentDate)=2013 then 3.15
When Year(BusScheduleAssignmentDate)=2014 then 3.25
When Year(BusScheduleAssignmentDate)=2015 
then 3.50
end
 [Bus Fare],
Count(Riders) as [total Riders],
case 
When Year(BusScheduleAssignmentDate)=2012 then 2.40
When Year(BusScheduleAssignmentDate)=2013 then 3.15
When Year(BusScheduleAssignmentDate)=2014 then 3.25
When Year(BusScheduleAssignmentDate)=2015 
then 3.50
end *  count(riders) as [Total Fares]
from BusRoute br
inner join BusScheduleAssignment bsa
on br.BusRouteKey=bsa.BusRouteKey
inner Join Ridership r
on r.BusScheduleAssigmentKey=
bsa.BusScheduleAssignmentKey
Group by Year(BusScheduleAssignmentDate),
busRouteZone

The query for bus driver pay

Use MetroAlt


Select EmployeeLastName,
EmployeefirstName,
PositionName,
YEar(BusScheduleAssignmentDate) [Year],
EmployeeHourlyPayRate,
Sum(DateDiff(hh, BusDriverShiftSTarttime, BusDriverShiftStopTime)) [total Hours],
Sum(DateDiff(hh, BusDriverShiftSTarttime, BusDriverShiftStopTime)) * EmployeeHourlyPayRate [Annual Pay]
From employee e
inner Join EmployeePosition ep
on e.EmployeeKey = ep.EmployeeKey
inner join Position p
on  p.PositionKey=ep.PositionKey
inner join BusScheduleAssignment bsa
on e.EmployeeKey=bsa.EmployeeKey
inner Join BusDriverShift bs
on bs.BusDriverShiftKey =bsa.BusDriverShiftKey
Where YEar(BusScheduleAssignmentDate)=2013
And e.EmployeeKey=16
Group by  EmployeeLastName,
EmployeefirstName,
PositionName,
YEar(BusScheduleAssignmentDate),
EmployeeHourlyPayRate

Service Query and client

Here is the service Interface for the service which queries Shows for a particular venue. It includes a data contract for for a class that allows us to combine fields from Show and ShowData.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;

// NOTE: You can use the "Rename" command on the "Refactor" menu to change the interface name "IService" in both code and config file together.
[ServiceContract]
public interface IService
{
 [OperationContract]
 List<ShowInfo> GetShowsByVenue(string venueName);

}


[DataContract]
public class ShowInfo
{
    [DataMember]
    public string ArtistName { get; set; }
    [DataMember]
    public string ShowName { get; set; }
    [DataMember]
    public string ShowDate { get; set; }
    [DataMember]
    public string ShowTime { get; set; }

    [DataMember]
    public string TicketInfo { get; set; }
}

Here is the code for the query itself

using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;

// NOTE: You can use the "Rename" command on the "Refactor" menu to change the class name "Service" in code, svc and config file together.
public class Service : IService
{
    ShowTrackerEntities db = new ShowTrackerEntities();

    public List<ShowInfo> GetShowsByVenue(string venueName)
    {
        var shws = from s in db.Shows
                   from d in s.ShowDetails
                   where s.Venue.VenueName.Equals(venueName)
                   select new
                   {
                       d.Artist.ArtistName,
                       s.ShowName,
                       s.ShowTime,
                       s.ShowDate,
                       s.ShowTicketInfo

                   };
        List<ShowInfo> shows = new List<ShowInfo>();

        foreach(var sh in shws)
        {
            ShowInfo sInfo = new ShowInfo();
            sInfo.ArtistName = sh.ArtistName;
            sInfo.ShowName = sh.ShowName;
            sInfo.ShowDate = sh.ShowDate.ToShortDateString();
            sInfo.ShowTime = sh.ShowTime.ToString();
            shows.Add(sInfo);
        }

        return shows;
    }
}

Now here is the ASP code for the simple web page client. We first had to make a reference to the service. Also we used a text box to enter the venue name. It should be a drop down list.

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
        <asp:GridView ID="GridView1" runat="server"></asp:GridView>
        <asp:Button ID="Button1" runat="server" Text="Get Shows" OnClick="Button1_Click" />
    </div>
    </form>
</body>
</html>

And here is the code behind

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        //Instantiate the service client so we have access to the serivce
        ServiceReference1.ServiceClient sc = new ServiceReference1.ServiceClient();
        //create an array and assign it the result of the service query
        ServiceReference1.ShowInfo[] shows = sc.GetShowsByVenue(TextBox1.Text);
        //bind the array to the DataGrid
        GridView1.DataSource = shows;
        GridView1.DataBind();

    }
}

Unit Tests

First we created a couple of classes to test. A Grade Class to store grade information and a GPA class that stores grades in a list and has two methods one to calculate GPA and one to calculate a straight average.

Grade Class

---

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ConsoleApplication2
{
    public class Grade
    {
        public string ClassName { get; set; }
        public int Credits { get; set; }

        public double GradePoint { get; set; }
    }
}

---

GPA Class

---

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ConsoleApplication2
{
    public class GPA
    {
        public List Grades { get; set; }

        public GPA()
        {
            Grades = new List();
        }

        public void addGrade(Grade g)
        {
            Grades.Add(g);
        }

        public double CalculateGPA()
        {
            int totalCredits = 0;
            double totalWeight = 0;
            foreach(Grade g in Grades)
            {
                totalCredits += g.Credits;
                totalWeight += g.Credits * g.GradePoint;
            }
            return totalWeight / totalCredits;
        }

        public double StraightAverage()
        {
            double totalGrades = 0;
            foreach(Grade g in Grades)
            {
                totalGrades += g.GradePoint;
            }

            return totalGrades / Grades.Count();
        }

    }
}

---

Next we right clicked on the solution and added a new project. We chose Test and Unit Test. Next we added a reference to the GPA project so we could talk to its classes. (It is important that they be public.)

Here is the Test class

---

using System;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using ConsoleApplication2;

namespace UnitTestProject1
{
    [TestClass]
    public class UnitTest1
    {
        GPA gpa = new GPA();
        [TestMethod]
        public void TestGPA()
        {
            
            AddSomeClasses();
            double g = gpa.CalculateGPA();

            Assert.AreEqual(39.5 / 13, g);
        }

        [TestMethod]
        public void TestStraightAverage()
        {
            
            AddSomeClasses();
            double avg = gpa.StraightAverage();

            Assert.AreEqual(9.5 / 3, avg);
        }

        private void AddSomeClasses()
        {
            
            Grade g1 = new Grade();
            g1.Credits = 5;
            g1.GradePoint = 2;
            gpa.addGrade(g1);

            Grade g2 = new Grade();
            g2.Credits = 3;
            g2.GradePoint = 4;
            gpa.addGrade(g2);

            Grade g3 = new Grade();
            g3.Credits = 5;
            g3.GradePoint = 3.5;
            gpa.addGrade(g3);
        }
    }
}

Assignment 5 Example Service Client

Default.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <table>
        <tr>
            <td>User Name</td>
            <td>
                <asp:TextBox ID="txtUserName" runat="server"></asp:TextBox></td>
        </tr>
        <tr>
            <td>Password</td>
            <td>
                <asp:TextBox ID="txtPassword" runat="server" TextMode="Password">

                </asp:TextBox></td>
        </tr>
        <tr>
            <td>
                <asp:Button ID="btnLogin" runat="server" Text="Log in" OnClick="btnLogin_Click" /></td>
            <td>
                <asp:Label ID="lblError" runat="server" Text=""></asp:Label></td>
        </tr>
    </table>
        <asp:LinkButton ID="LinkButton1" runat="server" 
            PostBackUrl="~/Registration.aspx">
            Register</asp:LinkButton>
    </div>
    </form>
</body>
</html>

---

Default.aspx.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void btnLogin_Click(object sender, EventArgs e)
    {
        RegistrationService.ReviewerRegistrationClient rrc
            = new RegistrationService.ReviewerRegistrationClient();
        int key=rrc.ReviewerLogin
            (txtUserName.Text, txtPassword.Text);
        if (key != 0)
        {
            Session["userKey"] = key;
            Response.Redirect("NewReview.aspx");
        }
        else
        {
            lblError.Text = "Invalid Login";
        }
    }
}

---

Registration.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Registration.aspx.cs" Inherits="Registration" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <p>First Name <br />
        <asp:TextBox ID="txtFirstName" runat="server"></asp:TextBox>
    </p>
 <p>Last Name <br />
        <asp:TextBox ID="txtLastName" runat="server"></asp:TextBox>
    </p>
         <p>Email <br />
        <asp:TextBox ID="txtEmail" runat="server"></asp:TextBox>
    </p>
         <p>UserName <br />
        <asp:TextBox ID="txtUserName" runat="server"></asp:TextBox>
    </p>
         <p>Password <br />
        <asp:TextBox ID="txtPassword" runat="server"></asp:TextBox>
    </p>
         <p><asp:Button runat="server" ID="btnRegister" Text="Register" OnClick="btnRegister_Click" /><br />
             <asp:Label ID="lblError" runat="server" Text=""></asp:Label>
    </p>
        <asp:LinkButton ID="LinkButton1" runat="server" 
            PostBackUrl="~/Default.aspx">Log in</asp:LinkButton>
    </div>
    </form>
</body>
</html>

---

Registration.aspx.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using RegistrationService;

public partial class Registration : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void btnRegister_Click(object sender, EventArgs e)
    {
        Reviewer r = new Reviewer();
        r.ReviewerFirstName = txtFirstName.Text;
        r.ReviewerLastName = txtLastName.Text;
        r.ReviewerUserName = txtUserName.Text;
        r.ReviewerEmail = txtEmail.Text;
        r.ReviewPlainPassword = txtPassword.Text;

        ReviewerRegistrationClient rrc = new 
            ReviewerRegistrationClient();
     
            bool result=rrc.Register(r);
            if (result)
                lblError.Text = "Reviewer Registered";
            else
                lblError.Text = "Registration Failed";
        
    }
}

---

NewReview.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="NewReview.aspx.cs" Inherits="NewReview" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:DropDownList ID="ddlBooks" runat="server"></asp:DropDownList>
        <p>Title<br />
            <asp:TextBox ID="txtTitle" runat="server"></asp:TextBox>
        </p>
        <p>Rating
            <asp:RadioButtonList ID="RadioButtonList1" runat="server">
                <asp:ListItem Text="1" Value="1"></asp:ListItem>
                <asp:ListItem Text="2" Value="2"></asp:ListItem>
                <asp:ListItem Text="3" Value="3"></asp:ListItem>
                <asp:ListItem Text="4" Value="4"></asp:ListItem>
                <asp:ListItem Text="5" Value="5"></asp:ListItem>

            </asp:RadioButtonList>
        </p>
        <p>The Review<br />
            <asp:TextBox ID="txtReview" TextMode="MultiLine" 
                runat="server" Height="130px" Width="328px"></asp:TextBox>
        </p>
        <p>
            <asp:Button ID="btnAddReview" runat="server" Text="Add Review" 
                OnClick="btnAddReview_Click"></asp:Button> <br />
            <asp:Label ID="lblError" runat="server" Text=""></asp:Label>
        </p>
    </div>
    </form>
</body>
</html>

---

NewReview.aspx.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using NewReviewService;

public partial class NewReview : System.Web.UI.Page
{
    CreateReviewServiceClient crc = new CreateReviewServiceClient();
    protected void Page_Load(object sender, EventArgs e)
    {
        if (Session["userKey"] != null)
        {
            if (!IsPostBack)
            {
                Book[] books = crc.GetBooks();
                ddlBooks.DataSource = books;
                ddlBooks.DataTextField = "BookTitle";
                ddlBooks.DataValueField = "BookKey";
                ddlBooks.DataBind();
            }
        }
        else
        {
            Response.Redirect("Default.aspx");
        }
    }
    protected void btnAddReview_Click(object sender, EventArgs e)
    {
        Review r = new Review();
        r.BookKey = int.Parse(ddlBooks.SelectedValue.ToString());
        r.ReviewerKey = (int)Session["userKey"];
        r.ReviewTitle = txtTitle.Text;
        r.ReviewRating = int.Parse(RadioButtonList1.SelectedValue.ToString());
        r.ReviewText = txtReview.Text;

        bool good = crc.WriteReview(r);
        if(good)
        {
            lblError.Text="review saved";
        }
        else
        {
            lblError.Text = "something went horribly wrong";
        }
    }
}

Assignment 4 in class example

Here is the code for the Register and login service. I have not included the HashPass, LoginClass or SeedCode classes.

IReviewerRegistration

using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;


[ServiceContract]
public interface IReviewerRegistration
{
 [OperationContract]
 bool Register(Reviewer reviewer);

    [OperationContract]
    int ReviewerLogin(string userName, string Password);
}

---

ReviewerRegistration the service

using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;

// NOTE: You can use the "Rename" command on the "Refactor" menu to change the class name "ReviewerRegistration" in code, svc and config file together.
public class ReviewerRegistration : IReviewerRegistration
{

    BookReviewDbEntities db = new BookReviewDbEntities();
    public bool Register(Reviewer reviewer)
    {
        bool good = true;

        try
        {

            KeyCode k = new KeyCode();
            int seed = k.GetKeyCode();
            PasswordHash hash = new PasswordHash();
            byte[] hashedpassword = hash.HashIt
                (reviewer.ReviewPlainPassword, seed.ToString());

           
            reviewer.ReviewerKeyCode = seed;
            reviewer.ReviewerHashedPass = hashedpassword;
            reviewer.ReviewerDateEntered = DateTime.Now;
            db.Reviewers.Add(reviewer);
            db.SaveChanges();
        }
        catch (Exception ex)
        {
            good = false;
        }


        return good;
    }

    public int ReviewerLogin(string userName, string Password)
    {
        LoginClass lc = new LoginClass(userName, Password);
        return lc.ValidateLogin();
    }
}

---

Now here is the code for the second service to Add a review

Here is the Interface

using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;

// NOTE: You can use the "Rename" command on the "Refactor" menu to change the interface name "ICreateReviewService" in both code and config file together.
[ServiceContract]
public interface ICreateReviewService
{
    [OperationContract]
    List GetBooks();

    [OperationContract]
    List GetAuthors();

    [OperationContract]
    List GetCategories();

    [OperationContract]
    bool WriteReview(Review r);
}

---

Here is the service code

using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;

// NOTE: You can use the "Rename" command on the "Refactor" menu to change the class name "CreateReviewService" in code, svc and config file together.
public class CreateReviewService : ICreateReviewService
{
    BookReviewDbEntities db = new BookReviewDbEntities();

    public List<Book> GetBooks()
    {
        var bks = from b in db.Books
                  orderby b.BookTitle
                  select b;

        List<Book> books = new List<Book>();
        foreach(Book b in bks)
        {
            Book bk = new Book();
            bk.BookTitle = b.BookTitle;
            bk.BookISBN = b.BookISBN;
            bk.BookKey = b.BookKey;

            books.Add(bk);
           
        }
        return books;
        
    }

    public List<Author> GetAuthors()
    {
        var auth = from a in db.Authors
                  orderby a.AuthorName
                  select a;

        List<Author> authors = new List<Author>();
        foreach (Author a in auth)
        {
            Author au = new Author();
            au.AuthorKey = a.AuthorKey;
            au.AuthorName = a.AuthorName;


            authors.Add(au);
        }
        return authors;
    }

    public List<Category> GetCategories()
    {
        var cats = from c in db.Categories
                  orderby c.CategoryName
                  select c;

        List<Category> categories = new List<Category>();
        foreach(Category c in cats)
        {
            Category bk = new Category();
            bk.CategoryName = c.CategoryName;
            bk.CategoryKey = c.CategoryKey;
           

            categories.Add(bk);
        }
        return categories;
           
    }

    public bool WriteReview(Review r)
    {
        bool result = true;
        try
        {

            r.ReviewDate = DateTime.Now;
            db.Reviews.Add(r);
            db.SaveChanges();
        }
        catch(Exception ex)
        {
            result = false;
        }
        return result;
    }
}

Class Reader Diagram

Here is the class diagram we did in class.

CardReader is an abstract class that cannot be instantiated except through its children.It containst all the fields and methods that are common to the busreaders--that is methods which have the same implementation.

TrainCardReader and BusCardReader inherit from CardReader.

CardReader has an aggregation relationship to Card. The CardReader class contains an instance of the Card Class, but the Card class has an existence separate from the container.

Card aggregates Trip (as do the readers)

TrainCardReader and BusCardReader implement the interface IReader which means they must provide a body for its abstract methods

Assignment 3 class Example

Here is the Default.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" 
Inherits="_Default" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Login</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <table>
        <tr>
            <td>Enter User Name</td>
            <td>
                <asp:TextBox ID="txtUserName" runat="server">
</asp:TextBox></td>
        </tr>
            <tr>
            <td>Enter Password</td>
            <td>
                <asp:TextBox ID="txtPassword" runat="server">
</asp:TextBox></td>
        </tr>
        <tr>
            <td>
                <asp:Button ID="btnSubmint" runat="server" Text="Log in" 
OnClick="btnSubmint_Click" /></td>
            <td>
                <asp:Label ID="lblResult" runat="server" Text="">
</asp:Label></td>
        </tr>
    </table>
    </div>
    </form>
</body>
</html>

---

Here is the Default.aspx.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void btnSubmint_Click(object sender, EventArgs e)
    {
        LoginClass lc = new LoginClass(txtPassword.Text, txtUserName.Text);
        int result = lc.ValidateLogin();
        if (result != 0)
        {
            
            Session["userKey"] = result;
            Response.Redirect("Welcome.aspx");
        }
        else
        {
            lblResult.Text = "Invalid login";
        }
    }
}

---

Here is the ReviewerRegistration.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" 
Inherits="_Default" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Login</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <table>
        <tr>
            <td>Enter User Name</td>
            <td>
                <asp:TextBox ID="txtUserName" runat="server">
</asp:TextBox></td>
        </tr>
            <tr><%@ Page Language="C#" AutoEventWireup="true" CodeFile="ReviewerRegistration.aspx.cs" Inherits="ReviewerRegistration" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <table>
        <tr>
            <td>First Name</td>
            <td><asp:TextBox ID="txtFirstName" runat="server"></asp:TextBox></td>
        </tr>
         <tr>
            <td>Last Name</td>
            <td><asp:TextBox ID="txtLastName" runat="server"></asp:TextBox></td>
        </tr>
         <tr>
            <td>Email</td>
            <td><asp:TextBox ID="txtEmail" runat="server"></asp:TextBox></td>
        </tr>
         <tr>
            <td>User Name</td>
            <td><asp:TextBox ID="txtUserName" runat="server"></asp:TextBox></td>
        </tr>
         <tr>
            <td>Password</td>
            <td><asp:TextBox ID="txtPassword" runat="server"  TextMode="Password"></asp:TextBox></td>
        </tr>
         <tr>
            <td>Confirm Password</td>
            <td><asp:TextBox ID="txtConfirm" runat="server" TextMode="Password"></asp:TextBox></td>
        </tr>
         <tr>
            <td>
                <asp:Button ID="btnRegister" runat="server" Text="Register" OnClick="btnRegister_Click" /></td>
            <td>
                <asp:Label ID="lblErrorSuccess" runat="server" Text=""></asp:Label></td>
        </tr>
       
    </table>
        <asp:LinkButton ID="LbLogin" runat="server" 
PostBackUrl="~/Default.aspx">Log in</asp:LinkButton>
        <asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server" ControlToValidate="txtUserName" Display="None" ErrorMessage="User name required"></asp:RequiredFieldValidator>
        <asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ControlToValidate="txtLastName" Display="None" ErrorMessage="Last name required"></asp:RequiredFieldValidator>
    </div>
        <asp:RegularExpressionValidator ID="RegularExpressionValidator1" runat="server" ControlToValidate="txtEmail" Display="None" ErrorMessage="Invalid email" ValidationExpression="\w+([-+.']\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*"></asp:RegularExpressionValidator>
        <asp:ValidationSummary ID="ValidationSummary1" runat="server" />
    </form>
</body>
</html>

            <td>Enter Password</td>
            <td>
                <asp:TextBox ID="txtPassword" runat="server">
</asp:TextBox></td>
        </tr>
        <tr>
            <td>
                <asp:Button ID="btnSubmint" runat="server" Text="Log in" 
OnClick="btnSubmint_Click" /></td>
            <td>
                <asp:Label ID="lblResult" runat="server" Text="">
</asp:Label></td>
        </tr>
    </table>
    </div>
    </form>
</body>
</html>

---

And here is the ReviewerRegistration.aspx.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class ReviewerRegistration : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void btnRegister_Click(object sender, EventArgs e)
    {

        BookReviewDbEntities db = new BookReviewDbEntities();
        try
        {


            Reviewer r = new Reviewer();
            r.ReviewerFirstName = txtFirstName.Text;
            r.ReviewerLastName = txtLastName.Text;
            r.ReviewerEmail = txtEmail.Text;
            r.ReviewerUserName = txtUserName.Text;
            r.ReviewPlainPassword = txtPassword.Text;

            KeyCode kc = new KeyCode();
            int code = kc.GetKeyCode();

            r.ReviewerKeyCode = code;

            PasswordHash ph = new PasswordHash();

            Byte[] hashed = ph.HashIt(txtPassword.Text, code.ToString());
            r.ReviewerHashedPass = hashed;
            r.ReviewerDateEntered = DateTime.Now;
            db.Reviewers.Add(r);

            CheckinLog log = new CheckinLog();
            log.Reviewer = r;
            log.CheckinDateTime = DateTime.Now;
            db.CheckinLogs.Add(log);
            
            db.SaveChanges();
            lblErrorSuccess.Text = "Sucessfully Registered";
        }
        catch(Exception ex)
        {
            lblErrorSuccess.Text = ex.Message;
        }
    }
}

---

And here again is the LoginClass.cs though it is the same as in the other blog. I am not including the password hash class or the key code classes

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

/// 
/// This class takes in the user name and password
/// retrieves information from the database
/// and then hashes the password and key to
/// see if it matches the database hash
/// 
public class LoginClass
{
    //class level variables-fields
    private string pass;
    private string username;
    private int seed;
    private byte[] dbhash;
    private int key;
    private byte[] newHash;

    //constructor takes in password and username
    public LoginClass(string pass, string username)
    {
        this.pass = pass;
        this.username = username;
    }

    //gets the user info from the database
    private void GetUserInfo()
    {
        //declare the ADO Entities
        BookReviewDbEntities brde = new BookReviewDbEntities();
        //query the fields
        var info = from i in brde.Reviewers
                   where i.ReviewerUserName.Equals(username)
                   select new { i.ReviewerKey, i.ReviewerHashedPass, i.ReviewerKeyCode };

        //loop through the results and assign the
        //values to the field variables
        foreach (var u in info)
        {
            seed = u.ReviewerKeyCode;
            dbhash = u.ReviewerHashedPass;
            key = u.ReviewerKey;
        }
    }

    private void GetNewHash()
    {
        //get the new hash
        PasswordHash h = new PasswordHash();
        newHash = h.HashIt(pass, seed.ToString());
    }

    private bool CompareHash()
    {
        //compare the hashes
        bool goodLogin = false;

        //if the hash doesn't exist
        //because not a valid user
        //the return will be false
        if (dbhash != null)
        {
            //if the hashes do match return true
            if (newHash.SequenceEqual(dbhash))
                goodLogin = true;
        }

        return goodLogin;

    }

    public int ValidateLogin()
    {
        //call the methods
        GetUserInfo();
        GetNewHash();
        bool result = CompareHash();

        //if the result is not true
        //set the key to 0
        if (!result)
            key = 0;


        return key;
    }

}