Ajax

Here is the source for the ajax with the scriptmanger and the update panel. The radio buttons are in the update panel. The form is in a simple panel.

Default2.aspx
<%@ Page Title="Confirmation" Language="C#" MasterPageFile="~/MasterPage.master" AutoEventWireup="true" CodeFile="Default2.aspx.cs" Inherits="Default2" %>

<asp:Content ID="Content1" ContentPlaceHolderID="head" Runat="Server">
</asp:Content>


<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
<h2>Confirm Order</h2>
<asp:ScriptManager ID="ScriptManager1" runat="server">
</asp:ScriptManager>
<asp:UpdatePanel ID="UpdatePanel1" runat="server">
<ContentTemplate>
<asp:Label ID="Label5" runat="server" Text="Would You Like to Register"></asp:Label>
<asp:RadioButtonList ID="RadioButtonList1" runat="server" AutoPostBack="True"
onselectedindexchanged="RadioButtonList1_SelectedIndexChanged">
<asp:ListItem>Yes</asp:ListItem>
<asp:ListItem>No</asp:ListItem>
</asp:RadioButtonList>
<asp:Panel ID="Panel1" runat="server">
<p>
<asp:Label ID="Label1" runat="server" Text="Enter Your first Name"></asp:Label>
<asp:TextBox ID="txtFirstName" runat="server"></asp:TextBox><br />
<asp:Label ID="Label2" runat="server" Text="Enter Your Last Name"></asp:Label>
<asp:TextBox ID="txtLastName" runat="server"></asp:TextBox><br />
<asp:Label ID="Label3" runat="server" Text="Enter Your first Street"></asp:Label>
<asp:TextBox ID="TxtStreet" runat="server"></asp:TextBox><br />
<asp:Label ID="Label4" runat="server" Text="Enter Your first City"></asp:Label>
<asp:TextBox ID="txtCity" runat="server"></asp:TextBox><br />
<asp:Button ID="Button1" runat="server" Text="Submit" onclick="Button1_Click" />
</p>

</asp:Panel>

</ContentTemplate>
</asp:UpdatePanel>


</asp:Content>

Here is the code behind for the page Default2.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 Default2 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
Panel1.Visible = false;
}
protected void RadioButtonList1_SelectedIndexChanged(object sender, EventArgs e)
{
if (RadioButtonList1.SelectedIndex == 0)
{
Panel1.Visible = true;

}
else
{
Panel1.Visible = false;
string msg = "Thank's for nothing";

Response.Redirect("Default3.aspx?message=" + msg);
}
}
protected void Button1_Click(object sender, EventArgs e)
{
//HttpCookie cookie = new HttpCookie();
//cookie["name"] = "Joe";
//cookie["Address"] = "1001 elsewhere";
//Response.SetCookie(cookie);

Response.Redirect("Default3.aspx?message=Thank you for your interest");


}
}

Here is the code behind for Default3.aspx.

Creating and Altering Tables

--create a new database
--we are not adding any options so
--it will be a copy of the model
--database
Create Database SoftwareTracker

Use SoftwareTracker

--create a table
--identity gives you an auto number
--the first digit (1,1) is the starting number
--the second is the increment, what you want
--to count by
--nvarchar is variable length unicode
--nchar is fixed length unicode
--char and varchar are ascii
Create table Software
(
SoftwareID int identity(1,1),
Constraint PK_Software Primary Key (SoftwareID),--set and name a primary key,
SoftwareName nvarchar(255) Not null, --not null means required
SoftwareCompany nvarchar(255) not null,
SoftwarePrice Decimal(10,2) not null --could use money (4 decimal places)
)

Create Table SoftwareUsers
(
userID int identity(1,1) primary key,--let SQL Server Name key
userEmployeeNumber nvarchar(10) unique, --unique constraint
UserLastName nvarchar(255) not null,
UserDept nvarchar(255) default 'IT', --default constraint
Constraint ck_userDept Check (UserDept in ('IT', 'HR', 'ACC')),
UserPhone Nchar(10)
)

--could have done and named all our constraints
--Create Table SoftwareUsers
--(
-- userID int identity(1,1),
-- userEmployeeNumber nvarchar(10),
-- UserLastName nvarchar(255) not null,
-- UserDept nvarchar(255) default 'IT',
-- UserPhone Nchar(10)
-- Constraint ck_userDept Check (UserDept in ('IT', 'HR', 'ACC')),
-- Constraint PK_SoftwareUser Primary Key (userID),
-- Constraint UQ_EmpNumber Unique(userEmployeeNumber)
--)

--for this table we left the primary key
--to the alter table statement
--it is possible to make the tables and then
--add all the constraints after with alter table
--statements
Create table Computers
(
ComputerID int Identity(1,1),
UserID int,
Location nvarchar(255),
Constraint FK_User Foreign Key(UserID)
references SoftwareUsers(userID)
)

--alter the table to add the primary key
Alter Table Computers
Add Constraint PK_Computers Primary Key (ComputerID)

--alter the table to drop the check contstraint
Alter Table SoftwareUsers
Drop Constraint ck_userDept

--alter the table to add a new column
Alter Table Computers
Add ComputerOS nvarchar(255)

--alter a table to drop a column
Alter Table Softwareusers
Drop Column UserPhone

--create the linking table between software and
--computer
Create Table SoftWareComputer
(
ComputerID int,
SoftwareID int,
Primary Key (ComputerID, SoftwareID),--composite primary key
Constraint FK_Computer Foreign Key (ComputerID)
References Computers(ComputerID),
Constraint FK_Software Foreign Key(SoftwareID)
References Software(SoftwareID)

)

Validation Controls

Here is the default.aspx with the validation controls:

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

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<p>
<asp:Label ID="Label1" runat="server" Text="Enter First Name"></asp:Label><asp:TextBox ID="txtFirstName"
runat="server"></asp:TextBox><br />
<asp:Label ID="Label2" runat="server" Text="*Enter Last Name"></asp:Label><asp:TextBox ID="txtLastName"
runat="server"></asp:TextBox>
<asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server"
ControlToValidate="txtLastName" ErrorMessage="Last Name is required"
ForeColor="Red" Display="None"></asp:RequiredFieldValidator>
<br />
<asp:Label ID="Label3" runat="server" Text="Enter Address"></asp:Label><asp:TextBox ID="txtAddress"
runat="server"></asp:TextBox><br />
<asp:Label ID="Label4" runat="server" Text="Enter City"></asp:Label><asp:TextBox ID="txtCity"
runat="server"></asp:TextBox><br />
<asp:Label ID="Label5" runat="server" Text="Enter state"></asp:Label><asp:TextBox ID="txtState"
runat="server"></asp:TextBox><br />
<asp:Label ID="Label6" runat="server" Text="*Enter Zipcode"></asp:Label><asp:TextBox ID="txtZip"
runat="server"></asp:TextBox>
<asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server"
ControlToValidate="txtZip" ErrorMessage="Zip code is required"
ForeColor="Red" Display="None"></asp:RequiredFieldValidator>
<asp:RegularExpressionValidator ID="RegularExpressionValidator1" runat="server"
ControlToValidate="txtZip" Display="None" ErrorMessage="Invalid Zipcode"
ForeColor="Red" ValidationExpression="\d{5}(-\d{4})?"></asp:RegularExpressionValidator>
<br />
<asp:Label ID="Label7" runat="server" Text="Enter Phone"></asp:Label><asp:TextBox ID="txtPhone"
runat="server"></asp:TextBox>
<asp:RegularExpressionValidator ID="RegularExpressionValidator2" runat="server"
ControlToValidate="txtPhone" Display="None" ErrorMessage="invalid phone format"
ForeColor="Red" ValidationExpression="((\(\d{3}\) ?)|(\d{3}-))?\d{3}-\d{4}"></asp:RegularExpressionValidator>
<br />
<asp:Label ID="Label8" runat="server" Text="Enter email"></asp:Label><asp:TextBox ID="txtEmail"
runat="server"></asp:TextBox>
<asp:RegularExpressionValidator ID="RegularExpressionValidator3" runat="server"
ControlToValidate="txtEmail" Display="None" ErrorMessage="Invalid email"
ForeColor="Red"
ValidationExpression="\w+([-+.']\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*"></asp:RegularExpressionValidator>
<br />
<asp:Label ID="Label9" runat="server" Text="Enter Age"></asp:Label><asp:TextBox ID="txtAge"
runat="server"></asp:TextBox><br />

<asp:Button ID="Button1" runat="server" Text="Submit" onclick="Button1_Click" />
<asp:Label ID="Label10" runat="server" Text="Label"></asp:Label>
</p>
</div>
<asp:ValidationSummary ID="ValidationSummary1" runat="server" ForeColor="Red" />
</form>
</body>
</html>

Here is the code for our validation of the age text box:

protected void Button1_Click(object sender, EventArgs e)
{
int age;
bool goodAge = int.TryParse(txtAge.Text, out age);

if (goodAge)
{
if (age < 18)
{
Label10.Text = "No way kid!";
}
if (age > 115)
{
Label10.Text = "Really?";
}
}
else{
Label10.Text = "Enter a number for Age";
}
}

INSERT, UPDATE, DELETE

--inserts updates deletes
use MagazineSubscription

Select * from Magazine

insert into Magazine(MagName,MagType)
Values('Cat fancy','Monthly')

--insert multiple rows in a single statement
--only in 2008 and later
Insert into Magazine(MagName,MagType)
Values('Poprocks and Coke', 'weekly'),
('Fast Company','Quarterly')

Create table Magazine2
(
MagID int,
MagName varchar(100),
MagType char(10)
)

--referential integrity
--inserts you cannot insert into a child table unless
--there is an existing related value in a parent table
--you cannot insert a sale that doesn't have an existing
--customer in a customer table
--it forces you to enter the customer before the sale

--with updates
--you cannot change the foreign key value
--except to an existing value in the parent table

--Delete you cannot delete a parent that has children
--unless you first delete the children

Insert into Magazine2(MagID, MagName, MagType)
(Select MagID, MagName, MagType from Magazine)

Select * from Magazine2

Select * from Customer

Update Customer
Set CustFirstName='Martha',
CustAddress='1000 North Mercer Street'
Where CustID=1

--You can use a transaction to manually start and end
--the transaction. It gives you an undo option with
--rollback
Begin transaction

--this is fine with the where clause
--without the where clause it sets every
--record in the table to 'weekly'
UpDate Magazine2
Set MagType ='weekly'
Where MagID=2

Select * from MagazineDetail

--this changes all the prices
--raising them by 10%
Update MagazineDetail
Set SubscriptionPrice = SubscriptionPrice * 1.1

--either rollback or commit
--rollbakc undoes every thing
Rollback tran--

Commit Tran --writes everythin

--works because 13 has no child records
Delete from Magazine
Where MagID=13

--will delete all subscriptions because
--they are all child records
Delete from subscription

Select * from Subscription

Subqueries

--subqueries

use MagazineSubscription

--this returns the MagdetId and the Subscription price
--for the MagdetID that has the highest subscription price
--this can only be done with subqueries
--It is important that Subqueries in the SELECT clause and
--subqueries in the where clause that are addressed with comparitive
--operators such as = < > etc must return only a single value
Select MagDetID, subscriptionPrice
From MagazineDetail
Where SubscriptionPrice=(Select MAX(SubscriptionPrice) from MagazineDetail)

--comparing with other than the equal sine
Select MagDetID, subscriptionPrice
From MagazineDetail
Where SubscriptionPrice>(Select Avg(SubscriptionPrice) from MagazineDetail)

--this returns the subscriptionprice, and the average subscriptionprice
--and then subtracts the subscriptionprice from the average subscription price
--to get the difference. This is the kind of thing that can only be done with
--sbuqueries
Select SubscriptionPrice,
(Select AVG(SubscriptionPrice) from MagazineDetail) as Average,
((Select AVG(SubscriptionPrice) from MagazineDetail)-SubscriptionPrice) as [Difference]
From MagazineDetail



--in tests where a value is a member of a set
--here is a simple literal set
Select CustID, CustLastName, custfirstName
From Customer Where CustID in (3, 5, 7, 9)

--here is a cascading set of subqueries
--each of which tests whether the value
--in the where clause is a member of the
--result set returned by the next subquery.
--It is important to make sure you compare
--like values with like: custID with CustID,
--MagDetID, with MagDetID, etc.
Select CustID,CustLastName,CustFirstName
From Customer
Where CustID in
(Select CustID from Subscription
where MagDetID in
(Select MagdetID from MagazineDetail
where SubscriptionPrice =
(Select MIN(SubscriptionPrice) From MagazineDetail)))

--any All Exists
--All compares the chosen value to all the values in the
--subquery. in this case it must be greater than or equal
--to every value in the subquery. the effect is to return
--the maximum value. It is the only value that is bigger than
--or equal to all other values
Select magDetID, subscriptionPrice
From MagazineDetail
Where SubscriptionPrice >=all
(Select SubscriptionPrice from MagazineDetail)

--any compares the chosen value to every value
--in the subquery. In this case if it is bigger than any
--other value it will be included in the results
--the effect is all but the minimum value is included
Select magDetID, subscriptionPrice
From MagazineDetail
Where SubscriptionPrice >any
(Select SubscriptionPrice from MagazineDetail)


--exists returns a boolean. It is true or false
If exists
(Select [Name] from sys.databases
Where name='CommunityAssist')
Begin
Print 'yep it exists'
end

--Correlated sub query, queries where the sub query depends
--on a value in the top query
--this subquery returns those magazine details that are greater than
--or equal to the average for that subscription type
--so all 1s are compared to 1s
--all 2s are compared to 2s, etc

Select subscriptTypeID, MagDetID, Subscriptionprice
From MagazineDetail md
Where SubscriptionPrice >=
(Select AVG(SubscriptionPRice)
From MagazineDetail md2
Where md.subscriptTypeID=md2.SubscriptTypeID)

Select AVG(subscriptionPRice) from magazineDetail
Where SubscriptTypeID=5

Master Pages

Here is Default.aspx

Here is Default2.aspx


The master page is a template. It can help provide a consistent look and feel for the web site. It also provides a single place where layout changes can be made that will cascade through the whole web site.

Here is the html and ASP code for the Master page and the two Content pages and the stylesheet--such as it is.. I am not posting the class or the code behind. I will add them when they become relevant.

MasterPage.master
<%@ Master Language="C#" AutoEventWireup="true" CodeFile="MasterPage.master.cs" Inherits="MasterPage" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Magazine Subscriptions</title>
<link href="StyleSheet.css" rel="stylesheet" type="text/css" />
<asp:ContentPlaceHolder id="head" runat="server">
</asp:ContentPlaceHolder>
</head>
<body>
<form id="form1" runat="server">
<div>
<h1>Magnificent Magazines</h1>
<p> <a href="Default.aspx">Order Form</a>|<a href="Default2.aspx">Confirmation</a></p>
<asp:ContentPlaceHolder id="ContentPlaceHolder1" runat="server">

</asp:ContentPlaceHolder>
</div>
</form>
</body>
</html>

Here is the simple style sheet

Stylesheet.css
body {
}

h1
{
background-color:Navy;
color:White;
border:2px solid black;
text-align:center;
}

h2
{
color:Navy;

}

Content pages have no html. They just have two content areas. One is for header content such as a script element for javascript, or meta elements

Default.aspx

<%@ Page Title="Order" Language="C#" MasterPageFile="~/MasterPage.master" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<asp:Content ID="Content1" ContentPlaceHolderID="head" Runat="Server">

</asp:Content>


<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
<h2>Magazine Order From</h2>
<p>
<asp:Label ID="Label1" runat="server" Text="Enter Your first Name"></asp:Label>
<asp:TextBox ID="txtFirstName" runat="server"></asp:TextBox><br />
<asp:Label ID="Label2" runat="server" Text="Enter Your Last Name"></asp:Label>
<asp:TextBox ID="txtLastName" runat="server"></asp:TextBox><br />
<asp:Label ID="Label3" runat="server" Text="Enter Your first Street"></asp:Label>
<asp:TextBox ID="TxtStreet" runat="server"></asp:TextBox><br />
<asp:Label ID="Label4" runat="server" Text="Enter Your first City"></asp:Label>
<asp:TextBox ID="txtCity" runat="server"></asp:TextBox><br />
<asp:Button ID="Button1" runat="server" Text="Submit" />
</p>
</asp:Content>


Default2.aspx

<%@ Page Title="Confirmation" Language="C#" MasterPageFile="~/MasterPage.master" AutoEventWireup="true" CodeFile="Default2.aspx.cs" Inherits="Default2" %>

<asp:Content ID="Content1" ContentPlaceHolderID="head" Runat="Server">
</asp:Content>


<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
<h2>Confirm Order</h2>
<p>
<asp:Label ID="Label1" runat="server" Text="Enter Your first Name"></asp:Label>
<asp:TextBox ID="txtFirstName" runat="server"></asp:TextBox><br />
<asp:Label ID="Label2" runat="server" Text="Enter Your Last Name"></asp:Label>
<asp:TextBox ID="txtLastName" runat="server"></asp:TextBox><br />
<asp:Label ID="Label3" runat="server" Text="Enter Your first Street"></asp:Label>
<asp:TextBox ID="TxtStreet" runat="server"></asp:TextBox><br />
<asp:Label ID="Label4" runat="server" Text="Enter Your first City"></asp:Label>
<asp:TextBox ID="txtCity" runat="server"></asp:TextBox><br />
<asp:Button ID="Button1" runat="server" Text="Submit" />
</p>
</asp:Content>

Joins

Use MagazineSubscription

--standard ansi way to to an inner join
Select CustLastName,
CustFirstName,
SubscriptionID,
SubscriptionStart
From Customer
INNER Join Subscription --same with or without INNER
ON Customer.CustID=Subscription.CustID

--

--old way to do an inner join
Select CustLastName,
CustFirstName,
SubscriptionID,
SubscriptionStart
From Customer, Subscription
Where Customer.CustID=Subscription.CustID

--you can alias the tables to cut back on typing
Select CustLastName,
CustFirstName,
SubscriptionID,
SubscriptionStart
From Customer c --alias the customer as c
INNER Join Subscription s--same with or without INNER
ON c.CustID=s.CustID

--multiple table inner join
Select CustLastName, CustFirstName, MagName, SubscriptionStart
From Customer c
Inner Join Subscription s
ON c.CustID=s.CustID
Inner Join MagazineDetail md
On s.MagDetID=md.MagDetID
Inner Join Magazine m
On m.MagID=md.MagID
Where CustLastName='Able'

Select CustLastName, CustFirstName, MagName, SubscriptionStart
From Customer c, Subscription s, MagazineDetail md, Magazine m
Where c.CustID=s.CustID
And s.MagDetID=md.MagDetID
And md.MagID=m.MagID
And CustLastName='Able'

--if a relationship is missing you get an unintentional cross join
--also called a cartesian join or cartesian dump

Select CustLastName, SubscriptionID
From Customer
Cross Join Subscription

Select CustLastName, SubscriptionID
From Customer, Subscription

--outer joins
--left outer join
Select CustLastName, SubscriptionID
From Customer c
Left Outer Join Subscription s
On c.CustID=s.CustID
Where SubscriptionID is null

--right outer join
Select CustLastName, SubscriptionID
From Subscription s
Right Outer Join Customer c
On c.CustID=s.CustID
Where SubscriptionID is null

--Select Magname, s.SubscriptionID
--From Magazine m
--inner join MagazineDetail md
--On m.MagID=md.MagID
--Inner Join Subscription s
--On s.MagDetID=md.MagDetID
--Left Outer Join Subscription s2
--on s2.MagDetID=md.MagDetID
--Where s2.SubscriptionID is null

Select MagName, Count(s.subscriptionID) as Orders
From Magazine m
Inner Join MagazineDetail md
On m.MagID=md.MagID
Left Outer Join Subscription s
On md.magDetID=s.MagDetID
Group by magName
Having count(s.subscriptionID) < 1


--a self join joins a table with itself
--here is a little script to show
--an example

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', 3)
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

Aggregate Functions

Use CommunityAssist

--problem from homework 2
Select employeeKey, Coalesce(cast (dependents As varchar), 'none') as Dependents
From Employee

--if we had replaced with a number instead of a word
Select employeeKey, Coalesce(dependents, 0) as Dependents
From Employee


Select * From Employee

--Aggregate Functions Sum Count Avg Min Max

Use MagazineSubscription

--counts all rows
Select COUNT(*) From customer

--doesn't count nulls
Select COUNT(CustPhone) From Customer
--embedded coalesce
Select COUNT (Coalesce (CustPhone, 'none')) From Customer

Select * From Subscription

Select COUNT(custID) from Subscription

--only get distinct values
Select Count(Distinct Custid) From Subscription

Select SUM (subscriptionPrice) as [Total Prices] From MagazineDetail
Select SUM (Distinct subscriptionPrice) as [Total Prices] From MagazineDetail

Select AVG(SubscriptionPrice) as Average from MagazineDetail

Select AVG(Distinct SubscriptionPrice) as Average From MagazineDetail

Select MAX(SubscriptionPrice) As Biggest from MagazineDetail
Select Min(SubscriptionPrice) As Biggest from MagazineDetail

Select * from Customer

--you cannot have any column in a select clause that contains an aggregate function,
--that is not a part of an aggregate function, unless it is included in a Group By Clause
Select SubscriptTypeID, AVG(SubscriptionPrice) as Average
From MagazineDetail
Where SubscriptTypeID > 3 --normal criteria based on table values
Group By SubscriptTypeID
Having AVG(SubscriptionPrice) > 70 --when you have an aggregate function in the criteria

--It all has to be in the above order
--Select
--From
--Where [optional]
--Group by
--having [optional]

--one way to see the max and the magazine detail which has the maximum price together
--involves subqueries which are in assignment 5
Select MagDetID ,(Select MAX(SubscriptionPrice) from MagazineDetail) from MagazineDetail
Where SubscriptionPrice = (Select MAX(SubscriptionPrice) from MagazineDetail)

--a way to find out which magdetID has the maximum price
--once we know the maximum price
--Having requires a group by even if no aggregate function
--in the select clause
Select MagDetID From MagazineDetail
Group by MagDetID
Having MAX(SubscriptionPrice)=200.50

--problem from homework 3
Use CommunityAssist

Select MONTH(GrantDate) as [Month], SUM (GrantAmount) as Total
From ServiceGrant
Group By MONTH(GrantDate)

Assignment2 Sample Code

App_Code\ComputerOrder.cs

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

///

/// Summary description for ComputerOrder
///

public class ComputerOrder
{
public ComputerOrder()
{
//
// TODO: Add constructor logic here
//
}

private string brand;

public string Brand
{
get { return brand; }
set { brand = value; }
}
private string computerType;

public string ComputerType
{
get { return computerType; }
set { computerType = value; }
}
private string hardDiskSize;

public string HardDiskSize
{
get { return hardDiskSize; }
set { hardDiskSize = value; }
}
}
Default.aspx

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

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<link href="StyleSheet.css" rel="stylesheet" type="text/css" />
</head>
<body>
<form id="form1" runat="server">
<div>
<h1>Computer order form</h1>
<!--this is a comment-->
<p><asp:Label ID="Label1" runat="server" Text="Computer Brand "></asp:Label>
 <asp:TextBox ID="txtBrand" runat="server"></asp:TextBox></p>
<asp:Label ID="Label2" runat="server" Text="choose Type"></asp:Label><!--this is a simple form-->
<asp:RadioButtonList ID="rdoType" runat="server">
<asp:ListItem>Laptop</asp:ListItem>
<asp:ListItem>Desktop</asp:ListItem>
</asp:RadioButtonList>
<asp:Label ID="Label3" runat="server" Text="Hard Disk size" ></asp:Label>
<asp:DropDownList ID="ddlHardDisk" runat="server">
<asp:ListItem>250 Gigs</asp:ListItem>
<asp:ListItem>350 Gigs</asp:ListItem>
<asp:ListItem>500 Gigs</asp:ListItem>
</asp:DropDownList><br />

</div><asp:Button ID="Button1" runat="server" Text="Submit" onclick="Button1_Click" />
</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 Button1_Click(object sender, EventArgs e)
{
ComputerOrder myComputerOrder = new ComputerOrder();
myComputerOrder.Brand = txtBrand.Text;
myComputerOrder.ComputerType =
rdoType.SelectedValue.ToString();
myComputerOrder.HardDiskSize =
ddlHardDisk.SelectedValue.ToString();

Session["order"] = myComputerOrder;

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

Default2.aspx

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

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<link href="StyleSheet.css" rel="stylesheet" type="text/css" />
</head>
<body>
<form id="form1" runat="server">
<div>
<h1>Confirm your order</h1>
<p><asp:Label ID="Label1" runat="server" Text="Brand: "></asp:Label>
<asp:Label ID="lblBrand" runat="server" Text="" CssClass="sample"></asp:Label><br />
<asp:Label ID="Label2" runat="server" Text="Type: "></asp:Label>
<asp:Label ID="lblType" runat="server" Text="" CssClass="sample"></asp:Label><br />
<asp:Label ID="Label3" runat="server" Text="Hard Disk: "></asp:Label>
<asp:Label ID="lblHardDisk" runat="server" Text="" CssClass="sample"></asp:Label></p>
<asp:Button ID="Button1" runat="server" Text="Confirm"
onclick="Button1_Click" />
</div>
</form>
</body>
</html>

Default2.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 Default2 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (Session["order"] != null)
{
ComputerOrder confirmOrder = (ComputerOrder)Session["order"];
lblBrand.Text = confirmOrder.Brand;
lblType.Text = confirmOrder.ComputerType;
lblHardDisk.Text = confirmOrder.HardDiskSize;
}
else
{
Response.Redirect("Default.aspx");
}
}
protected void Button1_Click(object sender, EventArgs e)
{

}
}

StyleSheet.css

body {
}

h1
{
color:Navy;
}

.sample
{
color:Red;
}

Scalar(in-line) Functions

Use MagazineSubscription

--concatenate

Select CustLastName + ', ' + CustFirstName AS [Name], CustPhone
From Customer

--Substring (string name, starting position, number of characters)
Select Magname, SUBSTRING(magname, 1, 5) [First Five]
From Magazine

--find the character index of a space
Select CHARINDEX(' ', MagName, 1) from Magazine

--use the character index to return the first full word
Select SUBSTRING(magname, 1, CHARINDEX(' ', Magname, 1)) as [first word]
From Magazine

Select UPPER(magname) as [Upper Case]
From magazine
Select lower(magname) as [lower Case]
From magazine

Select * From Subscription

--date and time functions

Select MONTH(SubscriptionStart) from Subscription
Select DAY (SubscriptionStart) from Subscription
Select YEAR(SubscriptionStart) from Subscription

--datepart does the same as the above but also more
Select DATEPART(M, SubscriptionStart) from Subscription
Select DATEPART(yy, SubscriptionStart) from Subscription
Select DATEPART(d, SubscriptionStart) from Subscription
Select DATEPART(Hour, SubscriptionStart) from Subscription

--get the difference between dates
Select DATEDIFF(mm,'12/1/2006','4/11/2011') as [Difference]

Select DATEDIFF(mm,subscriptionStart, SubscriptionEnd) as [length]
From Subscription

--add two dates
Select SubscriptionStart,DATEADD(mm,12,SubscriptionStart) as [One Year] From subscription

--cast a date as a character type
Select CAST(SubscriptionStart as Varchar(11)) from Subscription
Select * From MagazineDetail
--case a number as a character to format
Select MagDetID, '$' + CAST(SubscriptionPrice as Varchar) as Price
From MagazineDetail

--use a case to assign values to a new column in the result set
Select Magname, MagType,
Case MagType
when 'Monthly' then 'Six Months'
when 'Quarterly' then 'One year'
When 'weekly' then 'Three months'
Else 'Unknown'
END
As [Minimum Subscription]
From Magazine
Order by Magname

Select * from Customer

--substitute your value for nulls
--also looks for that substituted value by repeating the function
--in the where clause
Select CustFirstName, CustLastName, Coalesce(CustPhone, 'unknown') as Phone
From Customer
Where Coalesce(CustPhone, 'unknown')='unknown'

Use CommunityAssist
--on way to answer the question in the assignments
Declare @Emp1HireDate as datetime
Select @Emp1HireDate = HireDate from Employee Where EmployeeKey=1
Select DATEDIFF(mm, @Emp1HireDate, hiredate) From Employee where EmployeeKey=4

Select Statements

Use MagazineSubscription

/*this is a multiline comment
I can write as much as I need
to wrote */

--simple selects and alias
--The AS keyword is optional
--the square brackets indicate a field with a space or a reserved word
--double quotes can also be used

Select CustLastName AS [Last Name],
CustFirstName As [First Name],
CustPhone As [Phone]
From customer

--the * is a wild card that returns all columns
Select * From Customer

Select CustFirstName,CustLastName,CustCity
From Customer
Order by CustLastName Desc --z to a

Select CustFirstName,CustLastName,CustCity
From Customer
Order by CustCity,CustLastName Desc
--primary sort by city and then secondary sort by lastname in reverse order

Select SubscriptionPrice, SubscriptionPrice * .095 AS [TAX]
From MagazineDetail

Select (4 + 3) * 2-1

--WHERE Clauses differentiate between rows

Select CustFirstName,CustLastName,CustCity
From Customer
Where NOT CustCity = 'Seattle'

--can us = > < >= <= with number or dates
Select MagDetID, SubscriptionPrice
From MagazineDetail
Where SubscriptionPrice >100

Select MagDetID, SubscriptionPrice
From MagazineDetail
Where SubscriptionPrice >=50 AND SubscriptionPrice <= 100

Select MagDetID, SubscriptionPrice
From MagazineDetail
Where SubscriptionPrice between 50 And 100

Select SubscriptionID, SubscriptionStart, SubscriptionEnd
From Subscription
Where SubscriptionStart between '4-1-2006' and '4-30-2006'


Select * from Customer

--inserting a new customer just to have a null value
Insert Into Customer(CustLastName,
CustFirstName,
CustAddress,
CustCity,
CustState,
CustZipcode)
Values ('Smith','Fred','1001 Somewhere','Seattle', 'WA', '98001')

--Use the IS keyword to find Nulls
Select * From Customer
Where CustPhone IS Null

--LIKE is used to find patterns
--the wildcards are % any number of characters and _ a single character

Select CustFirstName, CustLastName
From Customer
Where CustFirstName LIKE 'L%'

--this uses an escape character to indicate that
--you are searching for a literal % sign and
--not to use it as a wildcard

Select CustFirstName, CustLastName
From Customer
Where CustLastName LIKE '%an!%'
Escape '!'


--ITC 22_ returns ITC220, ITC222, ITC224, ITC226


--distinct returns only unique rows
Select Distinct Magid from MagazineDetail

ASP.Net Demo

Default.aspx

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

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<link href="StyleSheet.css" rel="stylesheet" type="text/css" />
</head>
<body>
<form id="form1" runat="server">
<div>
<h1>Demo</h1>
<asp:Calendar ID="Calendar1" runat="server" BackColor="White"
BorderColor="Black" BorderStyle="Solid" CellSpacing="1" Font-Names="Verdana"
Font-Size="9pt" ForeColor="Black" Height="250px" NextPrevFormat="ShortMonth"
Width="330px">
<DayHeaderStyle Font-Bold="True" Font-Size="8pt" ForeColor="#333333"
Height="8pt" />
<DayStyle BackColor="#CCCCCC" />
<NextPrevStyle Font-Bold="True" Font-Size="8pt" ForeColor="White" />
<OtherMonthDayStyle ForeColor="#999999" />
<SelectedDayStyle BackColor="#333399" ForeColor="White" />
<TitleStyle BackColor="#333399" BorderStyle="Solid" Font-Bold="True"
Font-Size="12pt" ForeColor="White" Height="12pt" />
<TodayDayStyle BackColor="#999999" ForeColor="White" />
</asp:Calendar>
</div>
<asp:Button ID="Button1" runat="server" Text="Click me"
onclick="Button1_Click" />
<asp:Label ID="Label1" runat="server" Text="Label" CssClass="response></asp:Label>
</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 Button1_Click(object sender, EventArgs e)
{
Label1.Text = "The selected date is "
+ Calendar1.SelectedDate.ToShortDateString();
}
}

Stylesheet.css

body
{
font-family:Verdana Sans-Serif;
}

h1
{
color:Navy;
}

.response
{
color:Fuchsia;
}