Objects in code

Here is the Scanner Class

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

namespace scannerExample
{
    class Scanner
    { 
        //fields or attributes
        private string idNumber;
        private DateTime timeStamp;
        private string status;

        //properties
#region "Public Properties"
        public string IDNumber
        {
            get { return idNumber; }
            set { idNumber = value; }
        }

        public DateTime TimeStamp
        {
            get { return timeStamp; }
            set { timeStamp = value; }
        }

        public string Status
        {
            get { return status; }
            set { status = value; }
        }
#endregion

        //methods
        public Scan ReadCard(string cardNumber)
        {
            Scan scan = new Scan();
            scan.CardNumber = cardNumber;
            scan.ScanDate = TimeStamp;
            scan.ScannerID = IDNumber;
            scan.CurrentStatus = Status;

            return scan;
        }
        //constructors
        public Scanner()
        {
            IDNumber = null;
            this.Status = "closed";
            this.TimeStamp = DateTime.Now;
        }

        public Scanner(string ID, string doorState, DateTime scanDate)
        {
            IDNumber = ID;
            Status = doorState;
            TimeStamp = scanDate;
        }
        //--events
        //--deconstructors



    }


}

---

Here is the Scan class

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

namespace scannerExample
{
    class Scan
    {
        public string ScannerID { get; set; }
        public string CardNumber { get; set; }
        public DateTime ScanDate { get; set; }
        public string CurrentStatus { get; set; }

        public override string ToString()
        {
            return "Scanner: " + ScannerID + "\nCardNumber" +
                CardNumber + "\nDate: " + ScanDate.ToString() + "\nStatus: " + CurrentStatus;
        }
    }
}

---

here is the program Class

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

namespace scannerExample
{
    class Program
    {
        static void Main(string[] args)
        {
            Program p = new Program();
            p.GetScan();
            Console.ReadKey();

        }

        private void GetScan()
        {
            string idNumber = "12345";
            DateTime curTime = DateTime.Now;
            string status = "closed";
            Scanner scanner = new Scanner(idNumber, status, curTime);
            Scan scan = scanner.ReadCard("6789");

            Console.WriteLine(scan.ToString());
   
        }
    }
}

Indexes and views

Here is what we did today, but for a more thorough and organized discussion of indexes you can go to this blog entry

--indexes and views

use communityAssist

-- non clustered index
Create nonclustered index ix_lastname on Person(Lastname)

Select * From Person with (index(ix_lastName))
where Lastname='Anderson'

--filtered index
Create index ix_Apartment on personAddress (apartment)
where Apartment is not null

Create unique index ix_uniqueEmail on PersonContact(contactinfo)
where contactTypekey=6

Drop index ix_Apartment on personAddress

Create index ix_location on PersonAddress(City, State, Zip)

Create table Personb
(
 personkey int,
 lastname nvarchar(255),
 firstname nvarchar(255)
)

Insert into Personb(personkey, lastname, firstname)
Select PersonKey, Lastname, firstname from Person

Select * from PersonB

Create clustered index ix_LastNameCluster on PersonB(Lastname)
Drop index ix_lastnameCluster on Personb

Insert into PersonB
Values(60, 'Brady', 'June')

--views
Go

Alter view vw_Donors 
As
Select lastname [Last Name], 
firstname [First Name],  
DonationDate [Date],
DonationAmount [Amount]
From Person p
inner Join Donation d
on p.PersonKey=d.PersonKey


go
Select [Last Name], [First Name], [Date], [Amount]
from vw_Donors

Select * from vw_Donors
where [Date] between '3/1/2010' and '3/31/2010'
order by [Last Name]

I

go
--this creates an updatable view
Create view vw_Person
As
Select lastname, firstname
from person
go
insert into vw_Person(firstname, Lastname)
Values('test', 'test')

Select * from Person

Select * from vw_donors 
where [Last Name]='Mann'
go

--this creates a bad view because the inclusion of contactinfo causes the 
--donation amount to repeat and gives a false sense of how many donations
--each donor has made
Create view vw_BadDonors
As
Select Lastname, firstName, ContactInfo, donationDate, donationamount
From Person p
inner join personContact pc
on p.PersonKey=pc.personkey
inner join Donation d
on p.PersonKey=d.Personkey

select Distinct * From vw_BadDonors where lastname='Mann'

Select sum(Amount) From vw_donors
Select sum(donationAmount) from vw_BadDonors

Create and alter tables

use CommunityAssist

Select * from PersonAddress

Begin Tran

Update PersonAddress
Set street ='1000 South Main',
apartment='201',
city='Kent'
where PersonAddressKey=1

Commit tran

--create and alter tables
Create database music
use Music

Create table Employee.Album
(
 AlbumKey int identity(1,1) primary key,
 AlbumTitle Nvarchar(255) not null,
 AlbumReleaseDate Date,
 AlbumRecordlabel nvarchar(255)
)

Create Table Artist
(
 ArtistKey int identity(1,1),
 ArtistName nvarchar(255),
 constraint pk_Artist primary key(ArtistKey)
)

Create Table ArtistAlbum
(
 AlbumKey int,
 ArtistKey int,
 Constraint PK_ArtistAlbum Primary Key(Albumkey, ArtistKey),
 Constraint FK_Album Foreign Key(AlbumKey)
  References Album(AlbumKey),
 Constraint FK_Artist Foreign Key (ArtistKey) 
  References Artist(ArtistKey)
  
)

Create table Sale
(
 SaleKey int identity(1000,1) primary key,
 SaleDatetime DateTime default GetDate(),
 EmployeeKey int not null
)

Create Table SaleDetail
(
 SaleDetailKey int identity(1,1) primary Key,
 SaleKey int foreign Key references Sale(SaleKey),
 SalePrice Decimal(6,2)
 Constraint ck_price check (SalePrice between 5 and 5000)
)

Alter table SaleDetail
Add AlbumKey int

Alter table SaleDetail
Add constraint FK_Album2 Foreign key(AlbumKey) references Album(AlbumKey)

Alter table Album
Drop column AlbumRecordTable


Alter table Album
add AlbumNotes xml

Select * from SaleDetail

/* kinds of constraints
primary key
foreign key
unique
check
default
*/

use CommunityAssist
Select * from sysConstraints
sp_Help 'Donation'
select * from sys.key_constraints
Select * from sys.foreign_keys

First Take on Objects

Here are the first objects we identified for the security card system

Card
Scanner Persons—Employee, visitor, security, custodial
Scan
Record
Room
Validator

Here are the first class diagrams for the scanner and scan objects

Here is the inheritance diagram for person

Here is the diagram for the scanner class in visual studio

Lastly here is the code visual studio generated

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

namespace Scanner
{
    public class Scanner
    {
        private string scannerID;

        public string ScannerID
        {
            get { return scannerID; }
            set { scannerID = value; }
        }
        private DateTime dateTimeStamp;

        public DateTime DateTimeStamp
        {
            get { return dateTimeStamp; }
            set { dateTimeStamp = value; }
        }
        private string status;

        public string Status
        {
            get { return status; }
            set { status = value; }
        }

        public bool ReadCard(string cardNumber)
        {
            throw new System.NotImplementedException();
        }

        public bool UnlockDoor()
        {
            throw new System.NotImplementedException();
        }

        public void RecieveScanStatus(bool status)
        {
            throw new System.NotImplementedException();
        }

        public bool StatusSend()
        {
            throw new System.NotImplementedException();
        }
    }
}

Inserts Updates Deletes

--Inserts updates and deletes

use CommunityAssist
--basic inserts
Insert into person(Lastname, firstname)
Values ('Donahue', 'Jen')

--multiple rows into the same table
Insert into Person(LastName, firstname)
Values('Brown', 'Jim'),
('Smith', 'Jason'),
('Carleson', 'Jane')

--insert into donor
--insert into person
--insert into personaddress
--insert into personcontact
--insert into donation

insert into Person(LastName, firstname)
Values('Conger','Steve')

--ident-current returns the last identity(autonumber) generated
--in the table you put in the parenthesis-- in this case
--the last person entered

Insert into PersonAddress(Street, Apartment, State, City, Zip, PersonKey)
Values('1201 Broadway','3176D', 'Wa', 'Seattle', '98122', IDENT_CURRENT('Person'))

--contacttypekey 1 is for home phone
Insert into PersonContact(ContactInfo, PersonKey, ContactTypeKey)
Values('2065551234', Ident_Current('Person'), 1)

--contactTypekey 6 = email
Insert into PersonContact(ContactInfo, PersonKey, ContactTypeKey)
Values('steve@conger.com', Ident_Current('Person'), 6)

--donation GetDate() returns current date and time
Insert into donation(DonationDate, DonationAmount, PersonKey, EmployeeKey)
Values(GetDate(), 200,Ident_current('Person'), 1)

--example with a literal personkey--just enter the number
Insert into donation(DonationDate, DonationAmount, PersonKey, EmployeeKey)
Values(GetDate(), 200,55, 1)

--for an existing person not the last person entered
--create a variable to store the key
Declare @person int
--get the key with a select statement
select @Person=personkey from Person 
where lastname='Anderson'
and firstname='Jay'
--use the variable in the insert (all these must be run together)
Insert into donation(DonationDate, DonationAmount, PersonKey, EmployeeKey)
Values(GetDate(), 200,@Person, 1)


--create a table to copy into
Create table PersonB
(
 personkey int,
 lastname nvarchar(255),
 firstname nvarchar(255)
)
Go

--insert with a select. This copies all the records from Person
--into Person b
Insert into Personb(personkey, lastname, firstname)
Select Personkey, Lastname, firstname from Person

--remove on record
Delete from Person
where Personkey=54

--updates change existing records. this changes jay anderson to 
--jason anderson
update Person
Set firstname='Jason'
where Personkey=1

--without a where the the update changes
--all the records in a table
Update PersonB
Set Lastname='Anderson'

Select * from PersonB

--if you manually start and close a transaction you
--can use rollback to undo the command

begin transaction
Update Person
Set Lastname='Smith'

Select * from Person

--this restores the table to its previous state
--and ends the transaction
Rollback transaction



Select * from Person
Update Person
Set Firstname='Jay'
where PersonKey=1

Select * from Person

--commit writes the transaction to the database
commit transaction

--you may want to change several values at once
update Donation
set donationamount = donationamount*1.05

Select * from Donation

Rollback tran
Select * from employee

--will fail because there is no employee 12
--you can't update to a value that would
--violate integrity
Update Donation
set EmployeeKey = 12
where EmployeeKey=1

--won't work because personkey1 has child records
--in other tables
Delete from Person
where personkey=1

--both commands remove all the records from Personb
Delete from Personb
Truncate table Personb

Select * from PersonB

Begin tran
--removes the table from the database
--won't work here because Person has 
--child records
Drop table Person
rollback tran

subqueries

--subqueries
Use CommunityAssist

--can't get who donated max amont
Select max(donationAmount) from Donation
Group by PersonKey

--but with a subquery in the criteria you can
--which donation is equal to the maximum donation
Select lastname, firstname, DonationDate, DonationAmount
From Person p
inner join Donation d
on p.Personkey = d.Personkey
Where DonationAmount = (Select Max(donationamount) From Donation)

--which donation is greater than the average donation
Select lastname, firstname, DonationDate, DonationAmount
From Person p
inner join Donation d
on p.Personkey = d.Personkey
Where DonationAmount >(Select Avg(donationamount) From Donation)

--here we have added the avg donation as a column. You
--can do subqueries in the select
--we also added a calculate column in which the 
--avg donation (as a subquery) is subtracted from the donation
--amount, and lastly the criteria is only returning the amounts
--that are greater than the average donation
Select lastname, firstname, DonationDate, DonationAmount, (Select Avg(donationamount) from Donation) as Average,
DonationAmount - (Select avg(DonationAmount) From Donation) as Difference
From Person p
inner join Donation d
on p.Personkey = d.Personkey
Where DonationAmount >(Select Avg(donationamount) From Donation)

--return only the names of donors
--in allows the criteria to return a set of values
--and matches the values to the field in the where clause
--it is important to match like to like (personkey to personkey)
Select FirstName, LastName from Person
Where Personkey in (Select personkey from Donation)

--in using literal values
Select * from PersonAddress
Where city in ('Bellevue', 'Kent', 'Shoreline')

--in with a subquery that returns identical results
Select * from PersonAddress
Where city in (Select city from PersonAddress where not city = 'Seattle')

--which employees have worked on service grant
Select LastName, firstname From Person
Where personkey in
  (Select PersonKey from Employee 
  where employeekey in 
   (Select EmployeeKey from ServiceGrant))

--we added an employee so that there would be one that
--did not work on grants
Insert into Person(lastname, firstname)
Values('Vader','Darth')


Insert into Employee(HireDate, SSNumber, Dependents, PersonKey)
values(GetDate(),'999999999',null,(Select max(personkey) from Person))

--return any employees who never worked on a grant
Select LastName, firstname From Person
Where personkey in
  (Select PersonKey from Employee 
  where employeekey not in 
   (Select EmployeeKey from ServiceGrant))

--same as the outer join we did last time
Select ServiceName from Service
 Where ServiceKey not in (Select ServiceKey from ServiceGrant)

--means that we will return all donation amounts
--that are greater than any other donation amount in the list.
--so the only one that is not returned is the smallest donation
--because it is not greater than "any" other donation
 Select DonationAmount from Donation
 where DonationAmount > any (Select DonationAmount from Donation)

--for all it must be larger that all the other values in the set
-->= returns the maximum donation
 Select DonationAmount from Donation
 where DonationAmount >= all (Select DonationAmount from Donation)

--correlated subquery
use MagazineSubscription

Select avg(subscriptionPrice) from magazineDetail
 where SubscriptTypeID=5

 --a correlated subquery is when the subquery uses a value in the 
 --outer query as part of its criteria
 --it results in something resembling a recursive function
 --in this case what it does is makes sure that
 --like is compared to like
 --subscription type 1 (one year) is compared only to other
 --subscription type 1's and subscription type 5 (five year) 
 --is compared only to other subscription type 5's etc.
Select subscriptTypeID, MagDetID, SubscriptionPrice
From MagazineDetail md
Where subscriptionPrice >=
(Select Avg(SubscriptionPrice) from magazineDetail md2
 where md.SubscriptTypeID=md2.SubscriptTypeID)

 

Activity diagrams

Here is the first Activity Diagram

Here is the second Activity with swim lanes

Joins (mark 3 or 4)

--Joins
--inner joins
--cross joins
--outer joins
--self join
--equi-joins
--

Use CommunityAssist

Select * from PersonAddress

--simple inner join
Select Person.PersonKey, Lastname, firstname, Street, City, State, Zip
From Person
Inner Join PersonAddress
On Person.Personkey=PersonAddress.Personkey

--alternate inner join syntax
Select p.PersonKey, Lastname, firstname, Street, City, State, Zip
From Person p
Join PersonAddress pa
On p.Personkey=pa.Personkey

--Fully qualified column Name: server.database.TableName.schema.columnName

Select p.PersonKey, Lastname, firstname, Street, City, State, Zip
From Person p, PersonAddress pa
Where p.PersonKey=pa.PersonKey

--cross join old syntax
Select p.PersonKey, Lastname, firstname, Street, City, State, Zip
From Person p, PersonAddress pa

--cross join new syntax with Cross key word
Select p.PersonKey, Lastname, firstname, Street, City, State, Zip
From Person p
cross join PersonAddress pa

--multiple table join

Select Distinct Lastname, Firstname, contactInfo as Email
From Person p
Inner join personContact pc
on p.PersonKey=pc.PersonKey
inner join Donation d
on d.PersonKey=p.PersonKey
Where ContactTypeKey=6

--multiple table join old syntax
Select Distinct Lastname, Firstname, contactInfo as Email
From Person p, PersonContact pc, Donation d
Where p.PersonKey=pc.PersonKey
And p.PersonKey=d.PersonKey
And contactTypeKey=6

--outer join
Select * from [Service]

--first table is left. Returns all the data from the Service
--table, only matching data from ServiceGrant
Select ServiceName, sg.ServiceKey
From Service s
Left outer join ServiceGrant sg
on s.ServiceKey=sg.ServiceKey
where sg.ServiceKey is null

--same query flipped to right outer join
Select ServiceName, sg.ServiceKey
From ServiceGrant sg
Right join Service s
on s.ServiceKey=sg.ServiceKey
where sg.ServiceKey is null

--full join returns all records from both tables
--whether matched or not
Select ServiceName, sg.ServiceKey
From ServiceGrant sg
full join Service s
on s.ServiceKey=sg.ServiceKey



--this creates a simple database with a single
--table employee.
--Supervisors are employees,
--the supervisor's id is the employeeid of the supervisor
--so to see which employees are supervised
--by which other employees requires a 
--self join. In a self join the tables are aliased
--as if they were two seperate tables
--and then joined with an inner join
--the employeeid is joined with the supervisor id
Create Database sample
GO
Use sample
Go
Create table Employee
(
EmployeeID int Primary key,
lastName varchar(30) not null,
Supervisior int
)
GO
Insert into Employee
Values(1,'Smith', 3)
Insert into Employee
Values(2,'Rogers', 2)
Insert into Employee
Values(3,'Johnson', null)
Insert into Employee
Values(4,'Larson', 2)
Insert into Employee
Values(5,'Standish', 3)

--the self join
Select e.LastName as employee, s.Lastname as supervisor
From employee e
Inner join employee s
on s.employeeid=e.supervisior 

Select * from Employee

Aggregate Functions

--aggregate functions

Use communityAssist

--basic aggregate functions
--aggregate functions operatate on multiple rows
Select Sum(DonationAmount) From donation
Select Avg(DonationAmount) as average From Donation
--counts all rows
Select count(*) [Number of Rows] From Donation
--doesn't count nulls
Select Count(donationAmount) from Donation
--all rows
Select Count(*) from PersonAddress
--minus nulls
Select Count (Apartment) From PersonAddress
Select * from PersonAddress

Select Min(DonationAmount) as smallest from Donation
Select Max(DonationAmount) as smallest from Donation

--Doesn't include duplicate values
Select sum(distinct DonationAmount) From Donation
Select avg(Distinct DonationAmount)  from Donation
--get unique donors
Select count(Distinct PersonKey) from Donation

--group by. You must group by any column in the select
--that is not a part of an aggregate function
--(if there are aggregate functions in the select)
Select City, Count(city) [count] From PersonAddress
group by City

--a query with group by that shows the totals
--earned for each month of the year
Select Year(donationDate) as [Year], Month(donationDate) as [Month],
Sum(donationAmount) as total
From Donation
Group by Year(donationDate), Month(donationDate)
Order by Year(DonationDate)

Select Year(donationDate) as [Year], Month(donationDate) as [Month],
Sum(donationAmount) as total
From Donation
Where Month(donationDate) > 2
Group by Year(donationDate), Month(donationDate)
Having sum(donationAmount) > 6000
Order by Year(DonationDate)

Use Cases

Here are the use cases we did in class

Here is the written use case for Determine Access

Use Case: Determine Access

Precondition:

Assumed: Employee Registered, Door Registered, Valid Card. Employee at Closed Door

Trigger:

Employee swipes card in door Card Reader

Steps:

1. Capture the card information
2. Capture door information and time
3. Send information to the System
4. System validates information
5. Signal door to unlock
6. Log information

PostCondition:

Door unlock

Assignment 1 calculator code

Calculator code at GitHub

For assignments you can post on get hub, or zip the file and post it to Google Drive and share with spconger@gmail.com

Here is the code we did in class

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>
    <link href="CalcStyle.css" rel="stylesheet" />
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <table>
        <tr>
            <td colspan="3">
                <asp:TextBox ID="txtDisplay" runat="server" CssClass="display"></asp:TextBox></td>
            <td>
                <asp:Button ID="btnClear" runat="server" Text="C" OnClick="btnClear_Click" /></td>
        </tr>
        <tr>
            <td>
                <asp:Button ID="btn7" runat="server" Text="7"  OnClick="number_click"/>
            </td>
            <td>
                <asp:Button ID="btn8" runat="server" Text="8" OnClick="number_click" />
            </td>
            <td>
                <asp:Button ID="btn9" runat="server" Text="9"  OnClick="number_click"/>
            </td>
            <td>
                <asp:Button ID="btnPlus" runat="server" Text="+" OnClick="btnPlus_Click" />
            </td>
        </tr>

        <tr>
            <td>
                <asp:Button ID="btn4" runat="server" Text="4"  OnClick="number_click"/>
            </td>
            <td>
                <asp:Button ID="btn5" runat="server" Text="5" OnClick="number_click" />
            </td>
            <td>
                <asp:Button ID="btn6" runat="server" Text="6"  OnClick="number_click"/>
            </td>
            <td>
                <asp:Button ID="btnMinus" runat="server" Text="-" OnClick="btnMinus_Click" />
            </td>
        </tr>
        <tr>
            <td>
                <asp:Button ID="btn1" runat="server" Text="1"  OnClick="number_click"/>
            </td>
            <td>
                <asp:Button ID="btn2" runat="server" Text="2" OnClick="number_click" />
            </td>
            <td>
                <asp:Button ID="btn3" runat="server" Text="3"  OnClick="number_click"/>
            </td>
            <td>
                <asp:Button ID="btnMultiply" runat="server" Text="*" OnClick="btnMultiply_Click" />
            </td>
        </tr>
        <tr>
            <td>
                <asp:Button ID="btn0" runat="server" Text="0"  OnClick="number_click"/>
            </td>
            <td>
                <asp:Button ID="btnDecimal" runat="server" Text="." OnClick="number_click" />
            </td>
            <td>
                <asp:Button ID="btnEqual" runat="server" Text="=" OnClick="btnEqual_Click" style="height: 26px"  />
            </td>
            <td>
                <asp:Button ID="btnDivide" runat="server" Text="/" OnClick="btnDivide_Click" />
            </td>
        </tr>
    </table>
    </div>
    </form>
</body>
</html>

---

Default.aspx.

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
{
    /// 

    /// This is the code behind for the calculator
    /// one method handles the number clicks
    /// and there is a method for each of the operators
    /// Steve Conger  4/9/2013
    /// 
    double number1;
    double number2;

    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void number_click(object sender, EventArgs e)
    {
        //This gets the button that was clicked
        //and writes the code to the textbox
        Button button = (Button)sender;
        txtDisplay.Text += button.Text;
    }
    protected void btnPlus_Click(object sender, EventArgs e)
    {
        //bool goodNumber = ValidNumber();

        if (ValidNumber())
        {
            Session["operator"] = "+";
            Session["answer"] = number1;
            txtDisplay.Text = "";
        }
    }
    protected void btnEqual_Click(object sender, EventArgs e)
    {
        if (Session["operator"] != null)
        {
            if (Session["answer"] != null)
            {
                string op = Session["operator"].ToString();
                number1=(double)Session["answer"];
                number2=double.Parse(txtDisplay.Text);
                Operations operations = new Operations();
                switch (op)
                {
                    case "+":
                        txtDisplay.Text = operations.Add(number1, number2).ToString();
                        break;
                    case "-":
                        txtDisplay.Text = operations.Subtract(number1, number2).ToString();
                        break;
                    case "*":
                        txtDisplay.Text = operations.Multiply(number1, number2).ToString();
                        break;
                    case "/":
                        txtDisplay.Text = operations.Divide(number1, number2).ToString();
                        break;
                }
            }
            Session["answer"] = null;
            Session["operator"] = null;
        }
    }
    protected void btnMinus_Click(object sender, EventArgs e)
    {
        if (ValidNumber())
        {
            Session["operator"] = "-";
            Session["answer"] = number1;
            txtDisplay.Text = "";
        }

    }

    protected bool ValidNumber()
    {
        bool isValid=false;
        bool IsNumber = double.TryParse(txtDisplay.Text, out number1);
        if (!IsNumber)
        {
            txtDisplay.Text = "";

        }
        else
        {
            isValid = true;
        }
        return isValid;
    }

    protected void btnMultiply_Click(object sender, EventArgs e)
    {
        if (ValidNumber())
        {
            Session["operator"] = "*";
            Session["answer"] = number1;
            txtDisplay.Text = "";
        }
    }
    protected void btnDivide_Click(object sender, EventArgs e)
    {
        if (ValidNumber())
        {
            Session["operator"] = "/";
            Session["answer"] = number1;
            txtDisplay.Text = "";
        }
    }
    protected void btnClear_Click(object sender, EventArgs e)
    {
        Session["answer"] = null;
        Session["operator"] = null;
        txtDisplay.Text = "";
    }
}

---

Operations.cs

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

/// 

/// Summary description for Operations
/// 
public class Operations
{
 public Operations()
 {
  //
  // TODO: Add constructor logic here
  //
 }

    public double Add(double num1, double num2)
    {
        return num1 + num2;
    }

    public double Subtract(double num1, double num2)
    {
        return num1 - num2;
    }

    public double Multiply(double num1, double num2)
    {
        return num1 * num2;
    }

    public double Divide(double num1, double num2)
    {
        return num1 / num2;
    }
}

---

CalcStyle.css

body {
}

.display {
    text-align:right;
    background-color:aliceblue;
}

scalar functions

--scalar  function
--operators 
use communityassist
Select 5 * 2 /3 + 4

-- math operators
Select DonationAmount, DonationAmount * .78 as ToCharity from Donation

--concatination
Select Lastname + ', ' + firstname "Name" from Person

--Date time functions
Select * From Donation
Select  Distinct Month(DonationDate) [Month] From Donation
Select Day(DonationDate) [Day] From Donation
Select Year(DonationDate) [Year] from Donation
Select DatePart(yy,DonationDate) from Donation
Select DatePart(mm,DonationDate) from Donation
Select GetDate() as Today
Select DateAdd(yy,5,DonationDate) [add 5 years] from Donation

Select distinct Year(DateAdd(yy, 5,DonationDate)) [Year],
Month(DateAdd(mm,3,DonationDate)) [Month] from Donation

Select cast(Month(donationDate) as nvarchar) + '/' 
+ cast(day(donationDate)as nvarchar) + '/' + cast(year(DonationDate)as nvarchar)
as [Date]
From donation

Select * From personContact

Select contactinfo, '(' + substring(Contactinfo, 1,3) + ')' 
+ substring(ContactInfo, 4,3) + '-' + Substring(ContactInfo, 6, 4) as Phone
From PersonContact
Where not ContactTypeKey =6

Select * from PersonAddress 

Select street, Substring(street, 1, charindex(' ',Street,1)) from PersonAddress

Select * from ContactType

Select upper(Street) From PersonAddress
Select lower(Street) From PersonAddress

Select donationDate,
 case Month(DonationDate) 
 when 2
  then 'February'
 when 3
  then 'March'
 when 4
  then 'April'
 else
  'Sometime'
 end
 as Month
 From Donation

 Select * From PersonAddress
 Select Street, Coalesce(Apartment, 'N/A') from PersonAddress

ITC222 first Selects

Use CommunityAssist

--simple select
Select firstname, lastname from person;
Select * From Person

--aliasing the field names
Select Firstname as [First Name], Lastname as [Last Name]
From Person

--aliasing without the as keyword
Select Firstname  [First Name], Lastname  [Last Name]
From Person

--select distinct values
Select Distinct PersonKey from Donation

--sort results
Select * From Person
order by LastName desc, Firstname Desc

--where criteria
Select * From PersonAddress
Where City ='Seattle'

Select * From PersonAddress
Where City ='kent'

Select * From PersonAddress
Where Not City ='Seattle'

Select * From PersonAddress
Where City !='Seattle'

Select * From PersonAddress
Where City <>'Seattle'

--finding nulls
Select * From PersonAddress
Where Apartment is null

--finding not nulls with and criteria
Select * From PersonAddress
Where Apartment is Not null
And Not City = 'Seattle'

--or
Select * From PersonAddress
Where Apartment is Not null
OR Not City = 'Seattle'

Select * From Donation
Where DonationAmount > 2000
--you can use these comparison operators
-- >, <, >=, <=, =

--using between
Select * From Donation
Where DonationDate between '3/1/2010' and '3/31/2010'

-- using the in keyword
Select * From Donation where DonationAmount in (500, 1000, 1200, 50)

--like

Select * From PersonAddress
where Street Like '%ave%'

Select * From PersonAddress nolock
where City Like '%ll%' 

First SQL

--this is a in-line comment
Use CommunityAssist;

Select * From Person;
Select [DonationDate], 
[DonationAmount],
[PersonKey]
From Donation

/*
Nvarchar --variable width unicode
Varchar--variable with ASCII
char fixed width ASCII
Nchar fixed with Unicode
NVarchar(max)
*/