Here is the Entity Diagram we did in Class
Tuesday, January 29, 2013
Ajax and WCF services
For this excessive we loaded a master page. I am not going to show that First I will show the WCF code. We added an Ajax enabled WCF class to the project. Here is the code for that
using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.ServiceModel.Activation;
using System.ServiceModel.Web;
using System.Text;
[ServiceContract(Namespace = "")]
[AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Allowed)]
public class Service
{
// To use HTTP GET, add [WebGet] attribute. (Default ResponseFormat is WebMessageFormat.Json)
// To create an operation that returns XML,
// add [WebGet(ResponseFormat=WebMessageFormat.Xml)],
// and include the following line in the operation body:
// WebOperationContext.Current.OutgoingResponse.ContentType = "text/xml";
[OperationContract]
public string Hello(string name, string birthday)
{
string status = "It is not your birthday.";
string today = DateTime.Now.Month.ToString() + "/" + DateTime.Now.Day.ToString();
if (today.Equals(birthday))
{
status = "Happy Birthday!";
}
// Add your operation implementation here
return "hello, " + name + ". " + status;
}
// Add more operations here and mark them with [OperationContract]
}
Here is the code for the default content page. We added a script manager and made a reference to the service. then we added HTML controls and some JavaScript
<%@ Page Title="" 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">
<!--script manager with a referemce to the service-->
<asp:ScriptManager ID="ScriptManager1" runat="server">
<Services>
<asp:ServiceReference Path="~/Service.svc" />
</Services>
</asp:ScriptManager>
<!--Use html to gather input-->
<p>Enter Your Name <input id="txtName" type="text" /></p>
<p>Enter birthday Month as a digit 1 to 12 <input id="txtMonth" type="text" /></p>
<p>Enter birthday Day<input id="txtDay" type="text" /></p>
<button id="btnBirthday" onclick="getBDay();" type="button">Submit</button>
<p id="results"></p>
<script type="text/javascript">
function getBDay() {
//get the values from the html controls
var usrname = document.getElementById('txtName').value;
var bMonth = document.getElementById('txtMonth').value;
var bDay = document.getElementById('txtDay').value;
var monthDay = bMonth + "/" + bDay;
//call the function from the service. ServiceCallBack
//and ServiceErrorCallbacks are function calls
//to deal with the returned value from
//service.Hello and with any errors returned
Service.Hello(usrname, monthDay, ServiceCallBack, ServiceErrorCallBack);
}
function ServiceCallBack(result) {
document.getElementById('results').innerText = result;
}
function ServiceErrorCallBack(error) {
alert(error.get_message);
}
</script>
</asp:Content>
Monday, January 28, 2013
Inserts, Updates, Deletes
use CommunityAssist
--basic insert statement
Insert into Person(Lastname, firstname)
Values('Bloggs','Earnest')
--insert several rows at once
Insert into Person(Lastname, firstname)
Values('Higgens','Henry'),
('Newton', 'Isaac'),
('Holmes', 'Sherlock')
--create a table patterned after the person table
--for our experiments
Create Table PersonB
(
personKey int identity(1,1) primary key not null,
Lastname nvarchar(255) not null,
FirstName nvarchar(255)
)
--insert with a select clause
Insert into PersonB(LastName, firstName)
Select lastname, firstname from Person
--manually begin a transaction
--this gives you control over what happens
--and lets you have the possibility of an undo
--via rollback--but does hold the table in a lock
--until you are done.
begin transaction
--basic update statement
--if done without the where clause
--will update everything
Update Personb
Set Firstname='Jason',
Lastname='Smith'
Where personKey=1
--undoes the current transaction
rollback transaction
--commits the transaction to the database
--you can only do one of the two
Commit Tran
--deletes all records from a table
truncate table Personb
Select * From Personb
Select * from Donation
--update to increase all donations by 10%
Begin tran
Update Donation
Set DonationAmount=Donationamount * 1.1
Rollback tran
--delete all records from PersonAddress
--make sure byou begin a transaction first
Delete from PersonAddress
Select * from PersonAddress
--this deletes the persons we inserted
--generally a delete should have a where clause
Delete From Person
Where Personkey >=52
Wednesday, January 23, 2013
SubQueries
--subqueries
Use CommunityAssist
Select PersonKey, max(DonationAmount) From Donation
Group by Personkey
--which donor gave the largest donor
Select Lastname, Firstname, DonationAmount
From Donation d
inner Join Person p
On p.personKey=d.Personkey
Where donationAmount=(Select Max(DonationAmount)from Donation)
--a sub query in the select clause--any subquery
--in the select clause can only return one value
Select Lastname, Firstname, DonationAmount,
(Select avg(donationAmount) from Donation) as Average
From Donation d
inner Join Person p
On p.personKey=d.Personkey
Where donationAmount>(Select avg(DonationAmount)from Donation)
--the in keyword, the value must be "in" the result set
Select LastName, Firstname
From Person
Where Personkey in (Select Personkey from donation)
Select * From PersonAddress
Where City in ('Bellevue', 'kent', 'shoreline')
--return the names of the employees
--who have worked on service Grants
Select Lastname, Firstname
From Person
Where PersonKey in
(Select PersonKey from Employee
where EmployeeKey in
(Select EmployeeKey from ServiceGrant))
--same as left outer join
Select ServiceName from Service
Where ServiceKey not in
(Select ServiceKey from ServiceGrant)
--the join version
Select ServiceName, ServiceGrant.ServiceKey
From Service
Left outer Join ServiceGrant
on Service.ServiceKey=ServiceGrant.ServiceKey
Where ServiceGrant.ServiceKey is null
--this one uses sub queries to get the average,
--see how much the donors donation is above the average,
--get the total of all donations (sum) and thenn
--calculate what percent the donor's donation is
--of the total
--all for those donors whose donations are above
--the average donation
Select Lastname, Firstname, DonationAmount,
(Select avg(donationAmount) from Donation) as Average,
DonationAmount-(Select avg(donationAmount) from Donation)
as [Difference],
(Select Sum(DonationAmount) From Donation) as Total,
(DonationAmount / (Select Sum(DonationAmount) From Donation)) * 100
As Percentage
From Donation d
inner Join Person p
On p.personKey=d.Personkey
Where DonationAmount > (Select avg(DonationAmount) from Donation)
--any, all
--any means the value is greater (or less then) any other donation
--in the list (excludes the min)
--all means it is greater than or equal to all the other donations.
--results in a max
Select DonationAmount from donation
where donationAmount > any
(Select DonationAmount from Donation)
Select DonationAmount from donation
where donationAmount >= all
(Select DonationAmount from Donation)
---correlated subquery
--A correlated subquery is a sub query where
--the subquery in the critera refers to a value
--in the outer query. This has the effect of
--creating a recursive query. In our example
--the subquery matches the subscripttypeID in the
--main query. This makes it so likes are matched to
--like, three year subscriptions are only compared
--to other three year subscriptions, one year subscriptions
--to other one year subscriptions etc.
use MagazineSubscription
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
Select * from SubscriptionType
Select * From MagazineDetail
Select
Tuesday, January 22, 2013
Example for assignment 2
The master page and css are the same as in the previous post
Default.aspx
<%@ Page Title="" 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">
<!--This is the body content-->
<h2>Make A Donation</h2>
<table>
<tr>
<td>Enter First Name</td>
<td>
<asp:TextBox ID="txtFirstName" runat="server"></asp:TextBox></td>
<td></td>
</tr>
<tr>
<td>Enter Last Name</td>
<td>
<asp:TextBox ID="txtLastName" runat="server"></asp:TextBox></td>
<td>
<asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ControlToValidate="txtLastName" ErrorMessage="Last Name is required" CssClass="errMessage" Display="None" ViewStateMode="Disabled"></asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td>Enter Street Address</td>
<td>
<asp:TextBox ID="txtAddress" runat="server"></asp:TextBox></td>
<td></td>
</tr>
<tr>
<td>Enter City</td>
<td>
<asp:TextBox ID="txtCity" runat="server"></asp:TextBox></td>
<td></td>
</tr>
<tr>
<td>Enter State</td>
<td>
<asp:TextBox ID="txtState" runat="server"></asp:TextBox></td>
<td></td>
</tr>
<tr>
<td>Enter Zip Code</td>
<td>
<asp:TextBox ID="txtZip" runat="server"></asp:TextBox></td>
<td>
<asp:RegularExpressionValidator ID="RegularExpressionValidator1" runat="server" ControlToValidate="txtZip" ErrorMessage="Invalid zip code" ValidationExpression="\d{5}(-\d{4})?" CssClass="errMessage"></asp:RegularExpressionValidator>
</td>
</tr>
<tr>
<td>Enter Email</td>
<td>
<asp:TextBox ID="txtEmail" runat="server"></asp:TextBox></td>
<td>
<asp:RegularExpressionValidator ID="RegularExpressionValidator2" runat="server" ControlToValidate="txtEmail" ErrorMessage="Invalid Email" ValidationExpression="\w+([-+.']\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*" CssClass="errMessage"></asp:RegularExpressionValidator>
</td>
</tr>
<tr>
<td>Enter Phone</td>
<td>
<asp:TextBox ID="txtPhone" runat="server"></asp:TextBox></td>
<td></td>
</tr>
<tr>
<td>Enter DonationAmount</td>
<td>
<asp:TextBox ID="txtDonation" runat="server"></asp:TextBox></td>
<td>
<asp:CompareValidator ID="CompareValidator1" runat="server" ControlToValidate="txtDonation" ErrorMessage="Must be a numeric value" Type="Double"></asp:CompareValidator>
</td>
</tr>
<tr>
<td>
<asp:Button ID="btnSubmit" runat="server" Text="Submit" OnClick="btnSubmit_Click" /></td>
<td>
<asp:Label ID="lblMessage" runat="server" Text="Label"></asp:Label>
</td>
<td>
<asp:ValidationSummary ID="ValidationSummary1" runat="server" />
</td>
</tr>
</table>
</asp:Content>
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 btnSubmit_Click(object sender, EventArgs e)
{
//instantiate the Donor object
Donor d = new Donor();
//assign the values from the text fields to the poperties
d.FirstName = txtFirstName.Text;
d.LastName = txtLastName.Text;
d.Address = txtAddress.Text;
d.City = txtCity.Text;
d.State = txtState.Text;
d.Zip = txtZip.Text;
d.Phone = txtPhone.Text;
d.Email = txtEmail.Text;
d.DonationAmt = double.Parse(txtDonation.Text);
//save the object to a session variable
Session["NewDonor"] = d;
//Redirect to the next page
Response.Redirect("Default2.aspx");
}
}
Donor.cs
using System; using System.Collections.Generic; using System.Linq; using System.Web; ////// Summary description for Donor /// public class Donor { //private variables private string lastName; private string firstName; private string address; private string city; private string state; private string zip; private string email; private string phone; private double donationAmt; #region public properties public string LastName { get { return lastName; } set { lastName = value; } } public string Address { get { return address; } set { address = value; } } public string City { get { return city; } set { city = value; } } public string State { get { return state; } set { state = value; } } public string Zip { get { return zip; } set { zip = value; } } public string Email { get { return email; } set { email = value; } } public string Phone { get { return phone; } set { phone = value; } } public double DonationAmt { get { return donationAmt; } set { donationAmt = value; } } public string FirstName { get { return firstName; } set { firstName = value; } } #endregion public Donor() { // // TODO: Add constructor logic here // } //public property }
Default2.aspx
<%@ Page Title="Confirm Information" 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>Check your info</h2>
<table>
<tr>
<td>Enter First Name</td>
<td>
<asp:TextBox ID="txtFirstName" runat="server"></asp:TextBox></td>
<td></td>
</tr>
<tr>
<td>Enter Last Name</td>
<td>
<asp:TextBox ID="txtLastName" runat="server"></asp:TextBox></td>
<td>
<asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ControlToValidate="txtLastName" ErrorMessage="Last Name is required" CssClass="errMessage" Display="None" ViewStateMode="Disabled"></asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td>Enter Street Address</td>
<td>
<asp:TextBox ID="txtAddress" runat="server"></asp:TextBox></td>
<td></td>
</tr>
<tr>
<td>Enter City</td>
<td>
<asp:TextBox ID="txtCity" runat="server"></asp:TextBox></td>
<td></td>
</tr>
<tr>
<td>Enter State</td>
<td>
<asp:TextBox ID="txtState" runat="server"></asp:TextBox></td>
<td></td>
</tr>
<tr>
<td>Enter Zip Code</td>
<td>
<asp:TextBox ID="txtZip" runat="server"></asp:TextBox></td>
<td>
<asp:RegularExpressionValidator ID="RegularExpressionValidator1" runat="server" ControlToValidate="txtZip" ErrorMessage="Invalid zip code" ValidationExpression="\d{5}(-\d{4})?" CssClass="errMessage"></asp:RegularExpressionValidator>
</td>
</tr>
<tr>
<td>Enter Email</td>
<td>
<asp:TextBox ID="txtEmail" runat="server"></asp:TextBox></td>
<td>
<asp:RegularExpressionValidator ID="RegularExpressionValidator2" runat="server" ControlToValidate="txtEmail" ErrorMessage="Invalid Email" ValidationExpression="\w+([-+.']\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*" CssClass="errMessage"></asp:RegularExpressionValidator>
</td>
</tr>
<tr>
<td>Enter Phone</td>
<td>
<asp:TextBox ID="txtPhone" runat="server"></asp:TextBox></td>
<td></td>
</tr>
<tr>
<td>Enter DonationAmount</td>
<td>
<asp:TextBox ID="txtDonation" runat="server"></asp:TextBox></td>
<td>
</td>
</tr>
<tr>
<td>
<asp:Button ID="btnSubmit" runat="server" Text="Submit" OnClick="btnSubmit_Click" /></td>
<td>
<asp:Label ID="lblMessage" runat="server" Text="Label"></asp:Label>
</td>
<td>
<asp:ValidationSummary ID="ValidationSummary1" runat="server" />
</td>
</tr>
</table>
</asp:Content>
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["NewDonor"] != null)
{
Donor d = (Donor)Session["NewDonor"];
txtFirstName.Text = d.FirstName;
txtLastName.Text = d.LastName;
txtAddress.Text = d.Address;
txtCity.Text = d.City;
txtState.Text = d.State;
txtZip.Text = d.Zip;
txtPhone.Text = d.Phone;
txtEmail.Text = d.Email;
txtDonation.Text = d.DonationAmt.ToString();
}
else
{
Response.Redirect("Default.aspx");
}
}
protected void btnSubmit_Click(object sender, EventArgs e)
{
string lName=txtLastName.Text;
string amount=txtDonation.Text;
Response.Redirect("Default3.aspx?name=" +
lName + "&amount=" + amount);
}
}
Default3.aspx
<%@ Page Title="" Language="C#" MasterPageFile="~/MasterPage.master" AutoEventWireup="true" CodeFile="Default3.aspx.cs" Inherits="Default3" %>
<asp:Content ID="Content1" ContentPlaceHolderID="head" Runat="Server">
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
<asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>
</asp:Content>
Default3.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 Default3 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
string lastName = Request.QueryString["name"];
string donation = Request.QueryString["amount"];
Label1.Text = "thank you, " + lastName + " for your donation of "
+ donation;
}
}
WebConfig
<?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"/>
<httpRuntime targetFramework="4.5"/>
</system.web>
<appSettings>
<add key="ValidationSettings:UnobtrusiveValidationMode" value="None"/>
</appSettings>
</configuration>
Thursday, January 17, 2013
Assignment 2 Example
here is the master page
<%@ Master Language="C#" AutoEventWireup="true" CodeFile="MasterPage.master.cs" Inherits="MasterPage" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Community Assist</title>
<asp:ContentPlaceHolder id="head" runat="server">
</asp:ContentPlaceHolder>
<link href="CommunityAssist.css" rel="stylesheet" type="text/css" />
</head>
<body>
<h1>Community Assist</h1>
<form id="form1" runat="server">
<div>
<asp:ContentPlaceHolder id="ContentPlaceHolder1" runat="server">
</asp:ContentPlaceHolder>
</div>
</form>
</body>
</html>
Here is the Default content page
<%@ Page Title="" 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">
<!--This is the body content-->
<h2>Make A Donation</h2>
<table>
<tr>
<td>Enter First Name</td>
<td>
<asp:TextBox ID="txtFirstName" runat="server"></asp:TextBox></td>
<td></td>
</tr>
<tr>
<td>Enter Last Name</td>
<td>
<asp:TextBox ID="txtLastName" runat="server"></asp:TextBox></td>
<td>
<asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ControlToValidate="txtLastName" ErrorMessage="Last Name is required"></asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td>Enter Street Address</td>
<td>
<asp:TextBox ID="txtAddress" runat="server"></asp:TextBox></td>
<td></td>
</tr>
<tr>
<td>Enter City</td>
<td>
<asp:TextBox ID="txtCity" runat="server"></asp:TextBox></td>
<td></td>
</tr>
<tr>
<td>Enter State</td>
<td>
<asp:TextBox ID="txtState" runat="server"></asp:TextBox></td>
<td></td>
</tr>
<tr>
<td>Enter Zip Code</td>
<td>
<asp:TextBox ID="txtZip" runat="server"></asp:TextBox></td>
<td></td>
</tr>
<tr>
<td>Enter Email</td>
<td>
<asp:TextBox ID="txtEmail" runat="server"></asp:TextBox></td>
<td></td>
</tr>
<tr>
<td>Enter Phone</td>
<td>
<asp:TextBox ID="txtPhone" runat="server"></asp:TextBox></td>
<td></td>
</tr>
<tr>
<td>Enter DonationAmount</td>
<td>
<asp:TextBox ID="txtDonation" runat="server"></asp:TextBox></td>
<td></td>
</tr>
<tr>
<td>
<asp:Button ID="btnSubmit" runat="server" Text="Submit" /></td>
<td>
<asp:Label ID="lblMessage" runat="server" Text="Label"></asp:Label>
</td>
<td></td>
</tr>
</table>
</asp:Content>
Here is the Donor Class
using System; using System.Collections.Generic; using System.Linq; using System.Web; ////// Summary description for Donor /// public class Donor { //private variables private string lastName; private string firstName; private string address; private string city; private string state; private string zip; private string email; private string phone; private double donationAmt; #region public properties public string LastName { get { return lastName; } set { lastName = value; } } public string Address { get { return address; } set { address = value; } } public string City { get { return city; } set { city = value; } } public string State { get { return state; } set { state = value; } } public string Zip { get { return zip; } set { zip = value; } } public string Email { get { return email; } set { email = value; } } public string Phone { get { return phone; } set { phone = value; } } public double DonationAmt { get { return donationAmt; } set { donationAmt = value; } } public string FirstName { get { return firstName; } set { firstName = value; } } #endregion public Donor() { // // TODO: Add constructor logic here // } //public property }
Here is the webConfig file
<?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="false" targetFramework="4.5" />
<httpRuntime targetFramework="4.5" />
</system.web>
<appSettings>
<add key="ValidationSettings:UnobtrusiveValidationMode" value="None"/>
</appSettings>
</configuration>
Here is the style sheet
body {
}
h1 {
background-color:navy;
color:white;
border:solid 5px black;
text-align:center;
}
Wednesday, January 16, 2013
Joins
--joins use CommunityAssist --inner join Select Firstname, Lastname, ContactInfo From Person Inner Join PersonContact on person.PersonKey=PersonContact.Personkey where ContactTypeKey=6 --join is the same as inner join Select Firstname, Lastname, ContactInfo From Person Join PersonContact on person.PersonKey=PersonContact.Personkey where ContactTypeKey=6 --with aliases for the table Select p.Personkey, Firstname, Lastname, ContactInfo From Person p Join PersonContact pc on p.PersonKey=pc.Personkey where ContactTypeKey=6 --old syntax for inner joins Select person.Personkey, Firstname, Lastname, ContactInfo From Person, PersonContact Where Person.PersonKey=PersonContact.PersonKey And ContactTypeKey=6 --fully qualified names --[server name].[Database Name].[Schema name].[Table Name] --official cross join Select lastname, contactinfo From Person Cross Join PersonContact --three table join Select Lastname , contactInfo, DonationDate, DonationAmount From Person Inner Join PersonContact on Person.PersonKey=PersonContact.PersonKey Inner Join Donation on Person.Personkey=Donation.Personkey Where ContactTypeKey=1 --sum with person as the inner table Select City, Sum(DonationAmount) as Total From Donation Inner Join Person on Person.PersonKey=Donation.PersonKey Inner Join PersonAddress on Person.PersonKey=PersonAddress.PersonKey Group By City --skipping the middle table because --donation and Personaddress both share --the personkey Select City, Sum(DonationAmount) as Total From Donation inner Join PersonAddress on Donation.PersonKey=PersonAddress.Personkey Group by City --three table join old syntax Select Lastname , contactInfo, DonationDate, DonationAmount From Person, Donation, PersonContact Where Person.PersonKey=Donation.PersonKey And Person.PersonKey=PersonContact.PersonKey And ContactTypeKey=1 --just checking Select Lastname, DonationDate, DonationAmount From Person inner Join Donation on Person.Personkey=Donation.Personkey Where Lastname='Mann' Select * From ServiceGrant --left outer join. left just refers to the first table --listed. The service table will return all its rows --the ServiceGrant (right) table will return only matching --rows. the services that don't have a match in the --service grant table will return null Select ServiceName, ServiceGrant.ServiceKey From Service left outer join ServiceGrant on Service.ServiceKey=ServiceGrant.ServiceKey Where ServiceGrant.ServiceKey is null --same as left outer join but the order --of the tables is flipped Select ServiceName, ServiceGrant.ServiceKey From ServiceGrant right outer join Service on Service.ServiceKey=ServiceGrant.ServiceKey Where ServiceGrant.ServiceKey is null Select Distinct ServiceKey From ServiceGrant Select ServiceKey from Service --returns all the records from both tables --whether they are matched or not Select ServiceName, ServiceGrant.ServiceKey From ServiceGrant full outer join Service on Service.ServiceKey=ServiceGrant.ServiceKey Where ServiceGrant.ServiceKey is null --this creates a simple database with a single --table emmployee. --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
Monday, January 14, 2013
MVC Example With C# Console Program.
This example consists of four classes.
* Tip--the model, where the calculations are done
* Display--The class that gets inputs and shows outputs
* TipCalculatorController--the class that brings the Model and view together
* Program which calls the controller class in the Main() method
The Display class is totally unaware of the Tip or the Controller class. The Tip class is totally unaware of the Display or the controller. Only the controller class is aware of the other two. The point is to create as loose a coupling of the classes as possible. The more independent they are of each other, the easier to update and maintain.
The code is commented
Tip.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace MVCSample
{
class Tip
{
///
/// This class does a very simple tip
/// calculation. It has two fields amount and
/// tip percent. We are ignoring tax and whether
/// we tip before or after tax. The point is the
/// MVC model. This is the model part of MVC.
/// It does the calculations and handles the data
/// it is totally unaware of the Display class
/// or the controller
///
///
//private fields
private double amount;
private double tipPercent;
//default constructor
public Tip()
{
Amount = 0;
TipPercent = 0;
}
//overloaded constructor
public Tip(double amt, double percent)
{
Amount = amt;
TipPercent = percent;
}
//public properties
public double Amount
{
get { return amount; }
set { amount = value; }
}
public double TipPercent
{
get { return tipPercent; }
set
{
//here we check to see if
//they entered the percent
//as a decimal or a whole number
//if it is a whole number
//larger than 1 we divide it by
//100, so the highest possible tip
//is 100%
if (value > 1)
{
value /= 100;
}
tipPercent = value;
}
}
public double CalculateTip()
{
//very simplistic tip calculation
return Amount * TipPercent;
}
public double CalculateTotal()
{
//simple total calculation
return CalculateTip() + Amount;
}
}
}
Display.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace MVCSample
{
class Display
{
///
/// this is the Display class. Its purpose is
/// to gather the input and display the output
/// Unlike our usual display I have made the
/// variables Class level fields. This is so
/// the Controller can have access to the fields
/// through the properties. The Display class
/// is totally unaware of the Tip class (the model)
/// or the controller
///
///
//private fields
private double perc;
private double amt;
private double total;
private double tipAmount;
//constructor
public Display()
{
Percentage= 0;
TipAmount = 0;
Amt = 0;
Total = 0;
GetValues();
}
//public properties
public double TipAmount
{
get { return tipAmount; }
set { tipAmount = value; }
}
public double Total
{
get { return total; }
set { total = value; }
}
public double Percentage
{
get { return perc; }
set { perc = value; }
}
public double Amt
{
get { return amt; }
set { amt = value; }
}
//private method for getting input
//it is called in the constructor
private void GetValues()
{
Console.WriteLine("Enter the Amount of the meal");
Amt=double.Parse(Console.ReadLine());
Console.WriteLine("Enter the percent you want to tip");
Percentage = double.Parse(Console.ReadLine());
}
//public method to show output
//public so I can access it from the controller
public void ShowTipandTotal()
{
Console.WriteLine("Your tip is {0:C}", TipAmount);
Console.WriteLine("The total will be {0:C}", Total);
Console.ReadKey();
}
}
}
TipCalculatorController.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace MVCSample
{
class TipCalculatorController
{
///
/// The TipCalculatorController class brings together
/// the display and the tip or model classes
/// I use the constructor to instantiate the Display.
/// Instantiating the Display calls its constructor
/// which calls the Get input method
/// Once the input is entered I can instantiate
/// the Tip class and pass the values from the
/// Display class. Notice the dot notation and observe
/// how the two classes interact
///
private Tip tip;
private Display display;
public TipCalculatorController()
{
display = new Display();
tip = new Tip(display.Amt, display.Percentage);
display.TipAmount = tip.CalculateTip();
display.Total = tip.CalculateTotal();
display.ShowTipandTotal();
}
}
}
Program.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace MVCSample
{
class Program
{
static void Main(string[] args)
{
TipCalculatorController t= new TipCalculatorController();
}
}
}
Here is a picture of the program running
Aggregate Functions
--aggregate functions use CommunityAssist Select Count(*) as [Number of Donations ] From Donation Where DonationAmount > 500 Select Count(*) From PersonAddress Select Count(Apartment) From PersonAddress Select sum(DonationAmount) as total From Donation Where DonationAmount > 500 Select Round(Avg(DonationAmount),2) as Average From Donation Select Max(DonationAmount) as Biggest, Min(donationAmount) as smallest From Donation Select Max(DonationDate) From Donation Select PersonKey, Max(DonationAmount) as biggest From donation Group by PersonKey Select Max(Lastname) From Person Select Year(DonationDate) as [Year], Month(DonationDate) as [Month], '$' + Cast(Sum(donationAmount)as Nvarchar) as total From Donation Where Month(donationDate)>2 Group by Year(DonationDate), Month(donationDate) Having Sum(donationAmount) > 6000 Select Month(DonationDate), avg(DonationAmount) From Donation Group by Month(donationDate) having Avg(DonationAmount)>500 Select DonationAmount from Donation order by DonationAmount Select avg(DonationAmount)From Donation Select avg(Distinct DonationAmount) from Donation Select Count(PersonKey) From Donation Select count (distinct PersonKey) as Donors from Donation Select Distinct DonationAmount From Donation Select DonationAmount,Count(donationAmount) [number of Donations] From Donation Group by DonationAmount
Thursday, January 10, 2013
Calculator code 1
This is my original version. The names differ a little from your version, It is not commented. I will add comments to it sometime in the very near future
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="CalculatorStyle.css" rel="stylesheet" type="text/css" />
</head>
<body>
<form id="form1" runat="server">
<div>
<h1>Simple Calculator</h1>
<table>
<tr>
<td colspan="3">
<asp:TextBox ID="TextBox1" runat="server" CssClass="entryText" ></asp:TextBox></td>
<td><asp:Button ID="btnMinus" runat="server" Text="-" CssClass="btnWidth"
onclick="btnMinus_Click"/></td>
</tr>
<tr>
<td>
<asp:Button ID="Button1" runat="server" Text="7" OnClick="number_click" CssClass="btnWidth" /></td>
<td><asp:Button ID="Button2" runat="server" Text="8" OnClick="number_click" CssClass="btnWidth" /></td>
<td><asp:Button ID="Button3" runat="server" Text="9" OnClick="number_click" CssClass="btnWidth" /></td>
<td><asp:Button ID="btnPlus" runat="server" Text="+" onclick="btnPlus_Click" CssClass="btnWidth" /></td>
</tr>
<tr>
<td>
<asp:Button ID="Button4" runat="server" Text="4" OnClick="number_click" CssClass="btnWidth" /></td>
<td><asp:Button ID="Button5" runat="server" Text="5" OnClick="number_click" CssClass="btnWidth" /></td>
<td><asp:Button ID="Button6" runat="server" Text="6" OnClick="number_click" CssClass="btnWidth" /></td>
<td><asp:Button ID="btnMultiply" runat="server" Text="*" CssClass="btnWidth"
onclick="btnMultiply_Click" /></td>
</tr>
<tr>
<td>
<asp:Button ID="Button8" runat="server" Text="1" OnClick="number_click" CssClass="btnWidth" /></td>
<td><asp:Button ID="Button9" runat="server" Text="2" OnClick="number_click" CssClass="btnWidth" /></td>
<td><asp:Button ID="Button10" runat="server" Text="3" OnClick="number_click" CssClass="btnWidth" /></td>
<td>
<asp:Button ID="btnDivide" runat="server" Text="/" CssClass="btnWidth"
onclick="btnDivide_Click"/></td>
</tr>
<tr>
<td>
<asp:Button ID="Button11" runat="server" Text="0" OnClick="number_click" CssClass="btnWidth" /> </td>
<td>
<asp:Button ID="Button7" runat="server" Text="." OnClick="number_click" CssClass="btnWidth" />
</td>
<td >
<asp:Button ID="btnCalcu" runat="server" Text="=" onclick="btnCalcu_Click" CssClass="btnWidth" /></td>
<td>
<asp:Button ID="btnClear" runat="server" Text="CLR" CssClass="btnWidth"
onclick="btnClear_Click" /></td>
</tr>
</table>
</div>
</form>
</body>
</html>
Default.aspx.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
public partial class _Default : System.Web.UI.Page
{
double answer=0;
double number1=0;
double number2=0;
int sessionCheck = 1;
protected void Page_Load(object sender, EventArgs e)
{
//if (!IsPostBack)
// Session["Answer"] = 0;
}
protected void number_click(object sender, EventArgs e)
{
Button b = (Button)sender;
TextBox1.Text += b.Text;
}
protected void btnPlus_Click(object sender, EventArgs e)
{
GetNumbers();
Session["Answer"] = number1 + number2;
Session["Operator"] = "+";
}
protected void btnCalcu_Click(object sender, EventArgs e)
{
number1 = (double)Session["Answer"];
number2 = double.Parse(TextBox1.Text);
string op = Session["Operator"].ToString();
switch (op)
{
case "+":
answer = number1 + number2;
break;
case "-":
answer = number1 - number2;
break;
case "*":
answer = number1 * number2;
break;
case "/":
answer = number1 / number2;
break;
}
TextBox1.Text = answer.ToString();
Session["Answer"] = null;
}
protected void GetNumbers()
{
if (Session["Answer"] == null)
{
sessionCheck = 0; ;
}
else
{
number1 = (double)Session["Answer"];
}
bool isNumber = double.TryParse(TextBox1.Text, out number2);
if (!isNumber)
{
TextBox1.Text = "";
return;
}
TextBox1.Text = "";
}
protected void btnMinus_Click(object sender, EventArgs e)
{
GetNumbers();
if (sessionCheck == 0)
{
Session["Answer"] = number2;
}
else
{
Session["Answer"] = number1 - number2;
}
Session["Operator"] = "-";
}
protected void btnMultiply_Click(object sender, EventArgs e)
{
GetNumbers();
if (sessionCheck == 0)
{
Session["Answer"]=number2;
}
else
{
Session["Answer"] = number1 * number2;
}
Session["Operator"] = "*";
}
protected void btnDivide_Click(object sender, EventArgs e)
{
GetNumbers();
if (sessionCheck == 0)
{
Session["Answer"] = number2;
}
else
{
Session["Answer"] = number1 / number2;
}
Session["Operator"] = "/";
}
protected void btnClear_Click(object sender, EventArgs e)
{
TextBox1.Text = "";
Session["Answer"] = null;
Session["Operator"] = null;
}
}
And here is the stylesheet CalculatorStyle.css, such as it is
body {
}
.entryText
{
text-align:right;
width:120px;
}
table
{
background-color:Teal;
}
.btnWidth
{
width:40px;
}
Wednesday, January 9, 2013
Scalar Functions Assignment 2
--scalar functions
Use CommunityAssist
Select DonationKey, [DonationAmount],[DonationDate]
From Donation
Select DonationKey, DonationAmount, DonationAmount * .8 as Charity,
DonationAmount * .2 as [Admin]
From Donation
Select 10 * 2 % (4 + 12)
Select DonationDate, DonationAmount From Donation
Select Month(DonationDate) as [Month], donationamount
From Donation
Where Month(DonationDate)=3
Select Day(donationDate) from Donation
Select Distinct Year(DonationDate) From Donation
Select Distinct DatePart(YY, DonationDate) as [Year] From Donation
Select Cast(DatePart(mm, DonationDate)as Nvarchar) + '/'
+ cast(Datepart(dd,DonationDate) as Nvarchar)
+ '/' + cast(DatePart(YY, DonationDate)as nvarchar) as [Date]
From Donation
Select Datepart(hour, GetDate()), Datepart(Minute, GetDate())
Select Datepart(Minute, GetDate())
Select DateAdd(dd, 30, GetDate())
Select DateDiff(mm, GetDate(), '3/22/2013');
Select ContactInfo, substring(ContactInfo, 1, 3) as [Area Code],
Substring (ContactInfo, 4, 3) as Exhange,
Substring (ContactInfo, 7, 4) as number
From PersonContact
Where ContactTypeKey < 6
--find a space
--charindex locates a particular character in this case
-- as space, so the following reads
--select a substring of the field street, starting with character 1
--return the number of characters up to the first space
Select Charindex(' ', Street) as [Location of first Space],
Substring(Street,1, Charindex(' ', Street)) as [first]
From PersonAddress
Tuesday, January 8, 2013
Statement Of Work
History
Sam has a video shop with some 10000 DVDs and blu rays. He keeps his inventory in Excel, but lately it has become too much to manage. He is keeping rental information in Excel also. Each time someone rents a video he gets their phone and address and so has a lot of duplicate information. He has a problem tracking who has turned in videos late. Multiple versions of the inventory. Team members often mis-enter data causing difficulties when searching for things. Sometimes they give customers unofficial discounts.
A database could help solve most of these problems.
Scope
The new database will track the inventory. It will track customer information and deposits. Track rentals and returns and fines due for late returns. It provide reports on customer preferences in order to make suggestion for rentals. It might also track customer requests and reservations.Track Employee involvement with rentals.
objectives
Better tracking of inventory and rentals
Timeline
Gather information --2 weeks
Requirements--1 week
Business rules--1 week
Design --
Normalization
Build the database --Physical design
Test the database
Monday, January 7, 2013
Selects
Use CommunityAssist; /* Simple Select examples 1/7/13 */ Select FirstName, Lastname from Person; Select * From Person; Select LastName, FirstName From Person Order by LastName DESC, Firstname ASC --types of sort --aliasing fields Select LastName AS [Last Name], FirstName as [First Name] From Person Select LastName "Last Name", FirstName "First Name" From Person Select LastName Last, FirstName First From Person Select Distinct PersonKey From Donation Order by PersonKey Select * From PersonAddress Where City='Kent' Select * From PersonAddress Where City='seattle' or City ='Kent' Order by City Select * From PersonAddress Where not City = 'Seattle' Select * From PersonAddress Where City != 'Seattle' Select * From PersonAddress Where Apartment is not null Select * From PersonAddress Where Apartment is null Select * From Donation Where DonationDate > '3/1/2010' Select * From Donation Where DonationAmount Between 1000 and 2000 Select * From Donation Where DonationAmount >= 1000 and donationAmount <= 2000 Select * From Donation Where DonationDate Between '3/1/2010' and '3/31/2010' Select * From PersonAddress Where Street like '%Way%' Select * From Person Where LastName like 'S%'
Subscribe to:
Posts (Atom)