ITC Resources

Tuesday, April 9, 2013

Assignment 1 calculator code

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

Monday, April 8, 2013

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

Wednesday, April 3, 2013

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%'

Monday, April 1, 2013

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)
*/

Tuesday, March 19, 2013

Web Page Examples

First we did the wizard to get all the products. Here is the source code for Default.aspx<>p>

<%@ 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>
        <h1>Our Products</h1>
        <asp:DataList ID="DataList1" runat="server" CellPadding="4" DataSourceID="SqlDataSource1" ForeColor="#333333">
            <AlternatingItemStyle BackColor="White" />
            <FooterStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
            <HeaderStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
            <ItemStyle BackColor="#E3EAEB" />
            <ItemTemplate>
                <strong>
                <asp:Label ID="ProductNameLabel" runat="server" Text='<%# Eval("ProductName") %>' /></strong>
                <br />
                $
                <asp:Label ID="ProductUnitPriceLabel" runat="server" Text='<%# Eval("ProductUnitPrice") %>' />
                <br />

            </ItemTemplate>
            <SelectedItemStyle BackColor="#C5BBAF" Font-Bold="True" ForeColor="#333333" />
        </asp:DataList>
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:PerfectPizzaConnectionString %>" SelectCommand="SELECT [ProductName], cast([ProductUnitPrice] as decimal(5,2)) as [ProductUnitPrice] FROM [Product] ORDER BY [ProductName]"></asp:SqlDataSource>
    </div>
        <p>Enter your phone number to order or register if you are a new customer  <asp:TextBox ID="txtPhone" runat="server"></asp:TextBox><br />
            <asp:Button ID="Button1" runat="server" Text="Submit" OnClick="Button1_Click" />
        </p>
        <asp:LinkButton ID="LinkButton1" runat="server">Register</asp:LinkButton>
    </form>
</body>
</html>

Here is the code behind which checks to see if the phone number matches

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)
    {
        PerfectPizzaEntities pe = new PerfectPizzaEntities();
        var ph = from p in pe.Customers
                 where p.CustomerPhoneKey.Equals(txtPhone.Text)
                 select p.CustomerPhoneKey;
        if (ph.ToList().Count != 0)
            Response.Redirect("Default3.aspx");
        else
            Response.Redirect("Default4.aspx");
                
    }
}

Here is the form for entering a new customer

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

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <h1>Register</h1>
        <table>
            <tr>
                <td>Enter phone</td>
                <td>
                    <asp:TextBox ID="txtPhone" runat="server"></asp:TextBox> </td>
            </tr>
                       <tr>
                <td>Last Name</td>
                <td>
                    <asp:TextBox ID="txtLastName" runat="server"></asp:TextBox> </td>
            </tr>
                       <tr>
                <td>Address1</td>
                <td>
                    <asp:TextBox ID="txtAddress1" runat="server"></asp:TextBox> </td>
            </tr>
                       <tr>
                <td>Address2</td>
                <td>
                    <asp:TextBox ID="txtAddress2" runat="server"></asp:TextBox> </td>
            </tr>
                       <tr>
                <td>City</td>
                <td>
                    <asp:TextBox ID="txtCity" runat="server"></asp:TextBox> </td>
            </tr>
                       <tr>
                <td>State</td>
                <td>
                    <asp:TextBox ID="txtState" runat="server"></asp:TextBox> </td>
            </tr>
                       <tr>
                <td>Zip Code</td>
                <td>
                    <asp:TextBox ID="txtZip" runat="server"></asp:TextBox> </td>
            </tr>
                       <tr>
                <td>
                    <asp:Button ID="Button1" runat="server" Text="Submit" OnClick="Button1_Click" /></td>
                <td>
                    <asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>  </td>
            </tr>
        </table>
    </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 Default4 : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        PerfectPizzaEntities pe = new PerfectPizzaEntities();
        Customer c = new Customer();
        c.CustomerPhoneKey = txtPhone.Text;
        c.CustomerLastName = txtLastName.Text;
        c.CustomerAddress1 = txtAddress1.Text;
        c.CustomerAddress2 = txtAddress2.Text;
        c.CustomerCity = txtCity.Text;
        c.CustomerState = txtState.Text;
        c.CustomerZip = txtZip.Text;
        pe.Customers.Add(c);
        pe.SaveChanges();

        Response.Redirect("Default2.aspx");
    }
}

Wednesday, March 6, 2013

Logins and users

--admin sql
--authentication--are you who you say you are
 --login to the server
 --login mapped to a user at the database level
 --user is given database permissions
 --windows authentication
 --sql server authentication --user name password
--authorization--what can do

--Here is a windows login
Use Master
USE [master]
GO

/****** Object:  Login [NT AUTHORITY\SYSTEM]    Script Date: 3/6/2013 11:26:39 AM ******/
CREATE LOGIN [NT AUTHORITY\SYSTEM] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO

--a sql server login
Create login EmployeeLogin with password='P@ssw0rd1', default_database=CommunityAssist

Use CommunityAssist
--schema are collections of objects
Go
Create schema EmployeeSchema 
--create a user that maps to that login and uses the schema
Create user EmployeeUser for Login EmployeeLogin with default_schema=employeeschema

Create role EmployeeRole

--assign permissions to the role
Grant select on Donation to EmployeeRole
Grant Select on Employee to EmployeeRole
Grant update on Donation to EmployeeRole
Grant exec on usp_newDonor to EmployeeRole
Grant exec on usp_ReturnDonorInfo to EmployeeRole

--assign the user to the role
exec sp_addrolemember 'employeerole', 'employeeUser'

Select * from PersonContact

Go
--create an object that belongs to the schema
Create view EmployeeSchema.GrantsView
As
Select ServiceName, sum(GrantAmount)as Total
From [Service]
inner Join ServiceGrant
on [Service].ServiceKey=ServiceGrant.ServiceKey
Group by ServiceName
go
Grant Select on schema::EmployeeSchema to Employeeuser

Monday, March 4, 2013

XML

use Automart

Select * from Customer.AutoService

use CommunityAssist

Select * From Person
For xml raw('person'), elements, root('People')

Select lastName, FirstName, Contactinfo
From Person 
inner join PersonContact 
On person.PersonKey=personcontact.PersonKey
For xml auto, elements, root('people')

Create xml Schema Collection meetingNotesSchema
As
'<?xml version="1.0" encoding="utf-8"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" targetNamespace="http://www.communityAssist.com/meetingNotes" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="meetingNote">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="heading">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="meetingDate" type="xs:string" />
              <xs:element name="attending">
                <xs:complexType>
                  <xs:sequence>
                    <xs:element maxOccurs="unbounded" name="member" type="xs:string" />
                  </xs:sequence>
                </xs:complexType>
              </xs:element>
              <xs:element name="subject" type="xs:string" />
            </xs:sequence>
          </xs:complexType>
        </xs:element>
        <xs:element name="body">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="notes" type="xs:string" />
              <xs:element name="tasks">
                <xs:complexType>
                  <xs:sequence>
                    <xs:element maxOccurs="unbounded" name="taskName" type="xs:string" />
                  </xs:sequence>
                </xs:complexType>
              </xs:element>
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>'

Create table Meeting
(
 MeetingID int identity(1,1) Primary Key,
 MeetingNote xml(meetingNotesSchema)
)

Insert into Meeting(MeetingNote)
Values('<?xml version="1.0" encoding="utf-8"?>
<meetingNote xmlns="http://www.communityAssist.com/meetingNotes" >
  <heading>
    <meetingdate>3/4/2013</meetingdate>
    <attending>
      <member>George Jetson </member>
      <member>Mark Hammel</member>
      <member>Carie Fisher</member>
    </attending>
    <subject>Star Wars</subject>
  </heading>
  <body>
    <notes>
      We met to talk about our starwars promotion for something or other.
    </notes>
    <tasks>
      <taskName>Get the news out</taskName>
      <taskName>Mind meld</taskName>
    </tasks>
  </body>
</meetingNote>')


Select * from Meeting

use Automart

Select ServiceName, ServiceDescription.query('declare namespace sd="http://www.automart.com/servicedescription"; sd:servicedescription/sd:parts/sd:part') as parts
 from customer.AutoService
 Where ServiceName='Replace fuel pump'

 Select ServiceName, ServiceDescription.query('declare namespace sd="http://www.automart.com/servicedescription"; sd:servicedescription/sd:description') as [Description]
 from customer.AutoService
 Where ServiceName='Replace fuel pump'