Joins

Use Community_Assist
--joins
Select * From Employee

--inner join
Select PersonLastName, PersonFirstName, PersonEmail,
EmployeeHireDate, EmployeeAnnualSalary
From Person
inner join Employee
On Person.PersonKey = Employee.PersonKey

--just join inner optional and aliased tables
Select p.PersonKey, 
PersonLastName, PersonFirstName, PersonEmail,
EmployeeHireDate, EmployeeAnnualSalary
From Person p
join Employee e
On p.PersonKey = e.PersonKey

Select * from EmployeePosition
Select * from Position

--4 table inner join
Select PersonLastName, PersonFirstName, PersonEmail,
EmployeeHireDate, PositionName, EmployeeAnnualSalary
From Person
inner Join Employee
on Person.PersonKey=Employee.PersonKey
inner join EmployeePosition
on Employee.EmployeeKey=EmployeePosition.EmployeeKey
inner join Position
on Position.PositionKey=EmployeePosition.PositionKey

Select PersonLastName, PersonFirstName, PersonEmail,
EmployeeHireDate, PositionName, EmployeeAnnualSalary
From Person, Employee, EmployeePosition, Position
Where Person.PersonKey=Employee.PersonKey
And Employee.EmployeeKey = EmployeePosition.EmployeeKey
And Position.PositionKey=EmployeePosition.EmployeeKey

--leave out position creates an accidental cross  join
Select PersonLastName, PersonFirstName, PersonEmail,
EmployeeHireDate, PositionName, EmployeeAnnualSalary
From Person, Employee, EmployeePosition, Position
Where Person.PersonKey=Employee.PersonKey
And Position.PositionKey=EmployeePosition.EmployeeKey

--cross join
Select PersonLastName, PersonFirstName, PersonEmail,
EmployeeHireDate
From Person, Employee
--explicit cross join
Select PersonLastName, PersonFirstName, PersonEmail,
EmployeeHireDate
From Person
Cross join Employee

--outer join
--an outer join returns all the records from one table
--and only matching records from the other
--the left table is the first table named
--the right table is the second table named
Select GrantTypeName, GrantRequest.GrantTypeKey
From GrantType
left outer join GrantRequest
On GrantType.GrantTypeKey = GrantRequest.GrantTypeKey
Where GrantRequest.GrantTypeKey is null

Select GrantTypeName, format(avg(GrantRequestAmount),'$#,##0.00') Average
From GrantType
inner join GrantRequest
on GrantType.GrantTypeKey=GrantRequest.GrantTypeKey
Group by GrantTypeName
having avg(GrantRequestAmount)>400

Parts of the Community Assist

Web interface the look and feel of the website
Database (Data layer)  
Security (database website) users, permissions
Server Database Web
Employee access intranet
Customers asking for grants—applying for grants
Donors able to donate tracking donations
Administrator elements
General user
Reporting services
Coffee and cookies

Ado classic in class version.

Here is the dataClass.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
//libraries need to talk to database
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

/// 

/// This class will connect to the database
/// It will have methods to retrieve the Services
/// It will also retreive all the grants for that service
/// Steve Conger 2016-4-12
/// 
/// 
public class DataClass
{
    private SqlConnection connect; 
    public DataClass()
    {
        connect = new SqlConnection
            (ConfigurationManager.
            ConnectionStrings["CommunityAssistConnectionString"].ToString());
    }//end constructor

    public DataTable GetServices()
    {
        DataTable tbl = null;

        string sql = "Select GrantTypeKey, GrantTypeName from GrantType";
        SqlCommand cmd = new SqlCommand(sql, connect);
       
     
        tbl = ReadData(cmd);

        
        return tbl;
    }

    public DataTable GetGrants(int grantTypeKey)
    {
        DataTable tbl = null;
        string sql = "SELECT GrantRequestDate, GrantRequestExplanation, GrantRequestAmount "
            + "FROM GrantRequest "
            + "WHERE GrantTypeKey=@Key";

        SqlCommand cmd = new SqlCommand(sql, connect);
        cmd.Parameters.AddWithValue("@Key", grantTypeKey);

        tbl = ReadData(cmd);
        return tbl;

        
    }

    private DataTable ReadData(SqlCommand cmd)
    {
        SqlDataReader reader = null;
        DataTable tbl = new DataTable();

        connect.Open();
        reader = cmd.ExecuteReader();
        tbl.Load(reader);
        reader.Close();
        connect.Close();

        return tbl;
    }



}//end class

Here is the Default.aspx 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></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:DropDownList ID="DropDownList1" runat="server" 
AutoPostBack="True" 
            OnSelectedIndexChanged="DropDownList1_SelectedIndexChanged">
        </asp:DropDownList>
        <asp:GridView ID="GridView1" runat="server"></asp:GridView>
    </div>
    </form>
</body>
</html>

Here is the code behind in Default.aspx.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data; //added for datatable

public partial class _Default : System.Web.UI.Page
{

    DataClass dc = new DataClass();
    protected void Page_Load(object sender, EventArgs e)
    {
        if(!IsPostBack)
        LoadDropDownList();
    }

    protected void LoadDropDownList()
    {
        DataTable tbl = dc.GetServices();
        DropDownList1.DataSource = tbl;
        DropDownList1.DataTextField = "GrantTypeName";
        DropDownList1.DataValueField = "GrantTypeKey";
        DropDownList1.DataBind();
        DropDownList1.Items.Insert(0, "Choose a Service");
    }


    protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
    {
        FillGrid();
    }

    protected void FillGrid()
    {
        if(!DropDownList1.SelectedValue.Equals("Choose a Service"))
        { 
            int key = int.Parse(DropDownList1.SelectedValue.ToString());
            DataTable tbl = dc.GetGrants(key);
            GridView1.DataSource = tbl;
            GridView1.DataBind();
        }
    }

}

Here is the web config with the connection string

<?xml version="1.0"?>

<!--
  For more information on how to configure your ASP.NET application, please visit
  http://go.microsoft.com/fwlink/?LinkId=169433
  -->

<configuration>

    <system.web>
      <compilation debug="true" targetFramework="4.5.2" />
      <httpRuntime targetFramework="4.5.2" />
    </system.web>
  <connectionStrings>
    <add name="CommunityAssistConnectionString" 
         connectionString="data source=srv38;
initial catalog=community_assist; 
integrated security=true"/>
  </connectionStrings>
</configuration>

Selects 2 functions

Use Community_Assist

--scalar
--aggregate
Select * From Donation
--date time functions
Select Distinct Year(DonationDate) [Year] from Donation
Select Distinct Month(DonationDate) [Month] from Donation
Select Distinct Day (donationDate) [Day] from Donation
Select DatePart(YY, DonationDate) [Year] from donation
Select Distinct DatePart(hour, donationDate) [hour] 
From donation

--casting dates to character for formatting
Select cast(month(DonationDate) as nchar(2))+ '/' 
+ cast(day(DonationDate) as nchar(2))
+ '/'+ cast(Year(DonationDate)as nchar(4)) as [Date] 
From Donation

Select * From Employee
--casting and doing math to get the years
Select cast(dateDiff(mm,min(EmployeeHiredate), 
(max(EmployeeHireDate))) / cast(12 as Decimal(10,2))
as decimal(10,2))
 From Employee

 --years and month
 Select dateDiff(mm,min(EmployeeHiredate), 
max(EmployeeHireDate))/12 as [years],
dateDiff(mm,min(EmployeeHiredate), 
max(EmployeeHireDate))% 12 [Months]
From Employee

--format function (only works on numberic types)
Select EmployeeKey, format(EmployeeAnnualSalary,'$#,##0.00')
From Employee

--without format function
Select EmployeeKey, '$' + cast(EmployeeAnnualSalary as nvarchar(10))
From Employee

--just math
Select 4 * 3 - 2 /5.0

--seeing what a 5% raise would look like
Select EmployeeAnnualSalary, 
EmployeeAnnualSalary * 1.05 as Raise
From Employee

--aggregate functions
Select Sum(donationAmount) as total From Donation
Select Avg(DonationAmount) Average from Donation
Select Count(donationAmount) Number from Donation
Select Max(donationAmount) Maximum from Donation
Select Min(donationAmount) Minimum from Donation

--group by. Any column not a part of an 
--an aggregate function must be included
--in a group by clause
Select Year(DonationDate) [year], 
Month(donationDate) [Month],
Sum(donationAmount) total
From Donation
Group by Year(donationDate), Month(DonationDate)

Select Year(GrantRequestDate) [Year],
Month(GrantRequestDate) [Month],
Count(GrantRequestKey) Number,
format(Sum(GrantRequestAmount),'$#,##0.00') total
from GrantRequest
Group by Year(GrantRequestDate),
Month(GrantRequestDate)

--if the criteria contains an aggregate function
--You have to use a having clause
--the having clause always follows the group by clause
--you can still use a where for non aggregate criteria
Select Year(GrantRequestDate) [Year],
Month(GrantRequestDate) [Month],
Count(GrantRequestKey) Number,
format(Sum(GrantRequestAmount),'$#,##0.00') total
from GrantRequest
Where Month(GrantRequestDate)=8
Group by Year(GrantRequestDate),
Month(GrantRequestDate)
having Sum(GrantRequestAmount) > 1000

--Examples of two system views
Select * from Sys.Databases
Select * from sys.Tables
use MetroAlt
Use Community_Assist
Select * from sys.Columns where Object_ID=373576369

Selects Part One

Use Community_Assist;

Select PersonLastName, PersonFirstName, PersonEmail
From Person

Select * From Person

Select * from Person
Order by PersonLastName 

Select * from Person
Order by PersonLastName DESC

Select * from Person
Order by PersonLastName DESC, PersonFirstName 

--column aliasing
Select PersonLastName as [Last Name],
PersonFirstName as [First Name],
PersonEmail as Email
From person

Select PersonLastName "Last Name",
PersonFirstName [First Name],
PersonEmail Email
From person

--concatenation 
Select PersonLastName + ', ' + PersonFirstName as Name, 
PersonEmail as Email
From Person

--Where clauses
Select * From PersonAddress 
Where PersonAddressCity = 'Seattle'

Select * From Donation
Where DonationDate >'8/9/2015' And DonationDate < '8/10/2015'

Select * From Donation
Where DonationDate between'8/9/2015' And '8/10/2015'

Select * from Donation
Where DonationAmount > 1000

Select * from PersonAddress 
Where Not PersonAddressCity ='Seattle'

--c language not -- not ansi standare
Select * from PersonAddress 
Where PersonAddressCity !='Seattle'

--visual basic not--not ansi standard
Select * from PersonAddress 
Where PersonAddressCity <>'Seattle'

Select * from PersonAddress
Where PersonAddressApt is null

Select * from PersonAddress
Where PersonAddressApt is not null

Select PersonLastName, PersonFirstName, PersonEmail
From Person
Where PersonLastName like 'H%'

Select PersonLastName, PersonFirstName, PersonEmail
From Person
Where PersonLastName like '%and%'
AND PersonFirstName='Martin'

Select top 10 DonationAmount, DonationDate
From Donation
Order by DonationAmount DESC

Select DonationAmount, DonationDate
From Donation
Order by DonationAmount DESC
Offset 10 rows Fetch next 10 rows only

--distinct eliminates duplicate rows
Select Distinct GrantTypeKey from GrantRequest
order by GrantTypeKey

Select Distinct EmployeeKey from DonationCheck
Order by EmployeeKey

Beginnings and Overview

Here is the HTML source code

<%@ 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="FirstStyle.css" rel="stylesheet" />
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <!--This is a web or xml comment-->
    <h1>Birthday Calculator</h1>
        <hr />
        <p>Choose your birthday</p>
        <asp:Calendar ID="Calendar1" runat="server" >

        </asp:Calendar>
        <p>Enter your name <asp:TextBox ID="NameTextBox" runat="server">
                                      </asp:TextBox>
        </p>
        <p>
            <asp:Button ID="SubmitButton" runat="server" Text="Submit" 
OnClick="SubmitButton_Click" />
            <asp:Label ID="ResultLabel" runat="server" Text="" 
CssClass="result"></asp:Label>
        </p>
    </div>
    </form>
</body>
</html>

The C# code

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 a multiline comment. It's a good idea
    to put a header comment for every class */


    protected void Page_Load(object sender, EventArgs e)
    {

    }

    protected void SubmitButton_Click(object sender, EventArgs e)
    {
        GetTimeTillBirthday();
    }

    protected void GetTimeTillBirthday()
    {
        DateTime birthDay;
        

        if (Calendar1.SelectedDate==null)
        {
            birthDay = DateTime.Now;
        }
        else
        {
            birthDay = Calendar1.SelectedDate;
        }
        Response.Write(birthDay);
        string name = NameTextBox.Text;

        //this calculates the time until the birthday
        TimeSpan daysUntilBirthday = birthDay.Subtract(DateTime.Now);
        ResultLabel.Text ="Days until Birthday " +
            Math.Abs(daysUntilBirthday.Days).ToString() +
            ". And this many hours " 
         + Math.Abs(daysUntilBirthday.Hours).ToString();
       


        

    }

}

Here is the minimal css

body {
}

h1{
    color:navy;
}

.result{
    color:green;
}

Creating and Altering Tables

use ITC22;

Create table Customer_Steve
(
 CustomerKey int identity(1,1) primary key,
 CustomerLastName nvarchar(255) not null,
 CustomerFirstName nvarchar(255),
 CustomerDateAdded DateTime default GetDate()

)

Create Table CustomerOrder
(
   CustomerOrderKey int identity(1,1),
   CustomerKey int not null,
   CustomerOrderDate Date default getDate(),
   Constraint PK_CustomerOrder 
          Primary Key(CustomerOrderKey),
   Constraint FK_Customer Foreign Key(CustomerKey)
          References Customer_Steve(CustomerKey)
);

Create Table OrderDetail
(
 OrderDetailKey int identity(1,1),
 OrderKey int not null,
 OrderDetailProduct nvarchar(255) not null,
 OrderDetailPrice decimal(10,2)not null
)

Alter table OrderDetail
Add Constraint Pk_OrderDetail 
     primary key (OrderDetailKey);

Alter Table OrderDetail
Add Constraint FK_CustomerOrder Foreign Key(OrderKey)
   References CustomerOrder (CustomerOrderKey)

Alter Table Customer_Steve
Add CustomerEmail nvarchar(255)

Alter Table Customer_Steve
Drop column CustomerEmail

Alter Table Customer_Steve
Add Constraint unique_Email unique (customerEmail)

Alter Table OrderDetail 
Add constraint check_Price 
Check (OrderDetailPrice Between 1 and 100)