ITC Resources: March 2015

Tuesday, March 17, 2015

Simple Ajax

First we have a simple web service

here is the interface

using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;

// NOTE: You can use the "Rename" command on the "Refactor" menu to change the interface name "ItimeService" in both code and config file together.
[ServiceContract]
public interface ItimeService
{
 [OperationContract]
 string GetCurrentTime();
}

Here is the extremely simple service that implements that interface

using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;

// NOTE: You can use the "Rename" command on the "Refactor" menu to change the class name "timeService" in code, svc and config file together.
public class timeService : ItimeService
{
 

    public string GetCurrentTime()
    {
        return DateTime.Now.ToLongTimeString();
    }
}

You want to run this and keep it running. Start a second instance of Visual Studio to create the client. Create the reference to the service.

here is the web form for 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>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <h1>Time of Day</h1>
        <asp:Image ID="Image1" runat="server" ImageUrl="~/the-persistence-of-memory-4.jpg"/>
        <asp:ScriptManager ID="ScriptManager1" runat="server"></asp:ScriptManager>
        <asp:UpdatePanel ID="UpdatePanel1" runat="server">
          <ContentTemplate>
              <asp:Timer ID="Timer1" runat="server" Enabled="true"   OnTick="Timer1_Tick" Interval="1000" ></asp:Timer>
              <asp:Label ID="lblTime" runat="server" Text="Label"></asp:Label>
          </ContentTemplate>
        </asp:UpdatePanel>
        
    </div>
    </form>
</body>
</html>

Here is the default.aspx.cs 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
{
    protected void Page_Load(object sender, EventArgs e)
    {
        
        
       
        
    }
    protected void Timer1_Tick(object sender, EventArgs e)
    {
       
        TimeServiceReference.ItimeServiceClient tsr = new TimeServiceReference.ItimeServiceClient();
        lblTime.Text = tsr.GetCurrentTime();
    }
   
}

Wednesday, March 4, 2015

use communityAssist

Insert into Person([PersonLastName],[PersonFirstName],[PersonUsername], personEntryDate)
Values('Turin','Alan', 'AlanTuring@gmail.com',GetDate()),
('Ginsberg', 'Alan', 'aginsburg@gmail.com', getDate())

Insert into PersonAddress(Street, Apartment, State, City, Zip, PersonKey)
Values('1001 Broadway', '201', 'WA', 'Seattle','98001', ident_current('Person'))

Select * From Person
update Person
Set PersonFirstName='Jason',
PersonUserName='jasonAnderson@gmail.com'
where personKey=1

begin tran

Update Person
Set PersonLastName='Smith'
rollback tran

Commit tran

Delete From PersonAddress where personkey=130
Delete From Person where personkey=130

Select * from PersonAddress

SQL and XML


use CommunityAssist

Select PersonFirstName, PersonLastName, PersonUsername 
From Person for xml raw('person'), root('people'),  elements

Select ServiceName, GrantKey, GrantAmount, GrantDate, GrantNeedExplanation
From CommunityService 
inner join ServiceGrant 
on CommunityService.ServiceKey=ServiceGrant.ServiceKey
order by Servicename
for xml auto,  root('services'), elements

Select GrantReviewDate, GrantReviewNote.query( 'declare namespace rn = "http://www.communityassist.org/reviewnotes"; //rn:reviewnote/rn:concerns')
From GrantReview 


Create xml Schema Collection schemaMeeting
As
'<?xml version="1.0" encoding="utf-8"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" targetNamespace="http://www.CommunityAssist.org/meeting" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="meeting">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="attending">
          <xs:complexType>
            <xs:sequence>
              <xs:element maxOccurs="unbounded" name="attendee" type="xs:string" />
            </xs:sequence>
          </xs:complexType>
        </xs:element>
        <xs:element name="topic" type="xs:string" />
        <xs:element name="notes" type="xs:string" />
        <xs:element name="tasks">
          <xs:complexType>
            <xs:sequence>
              <xs:element maxOccurs="unbounded" name="task" type="xs:string" />
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>'

Create table Meeting
(
    MeetingKey int identity(1,1) primary key,
 MeetingDate date not null default GetDate(),
 MeetingNotes xml(schemaMeeting)
)

Insert into Meeting(MeetingNotes)
Values('<?xml version="1.0" encoding="utf-8"?>
<meeting xmlns="http://www.CommunityAssist.org/meeting">
  <attending>
    <attendee>Conger</attendee>
    <attendee>Lisa</attendee>
    <attendee>Ed</attendee>
  </attending>
  <topic>new computers</topic>
  <notes>
    New computers will be coming next quarter
  </notes>
  <tasks>
    <task>Create install plan</task>
    <task>something</task>
  </tasks>
</meeting>')

Select * From Meeting

Monday, March 2, 2015

SQL

--set the database context
Use CommunityAssist
--returns all columns and all rows from the Person table
--the asterisk is a wild cards for all columns
Select * from Person

--choose the column to display
SELECT PersonLastName, 
PersonFirstName, 
PersonUserName
FROM Person;

--alias with as
SELECT PersonLastName AS [Last Name], 
PersonFirstName AS [First Name], 
PersonUserName AS [User Name]
FROM Person;

--alias without as
SELECT PersonLastName [Last Name], 
PersonFirstName [First Name], 
PersonUserName [User Name]
FROM Person;

--the where criteria selects which "rows" to display
--the or lets you choose multiple values
Select * From PersonAddress
Where City='Kent' or City ='Shoreline'

-- nulls cannot be = to anything so
--that we use "is"
Select * from PersonAddress
Where Apartment is null
And City='Kent'

Select * from PersonAddress
Where Apartment is not null
And City='Kent'

--use of not
Select * from PersonAddress
where Not City='Seattle'

Select * from PersonAddress
where  City != 'Seattle'

Select * From Donation
--between
Select * From Donation
Where DonationDate between '8/1/2013' and '8/30/2013'

--like, the % is a wild card meaning any number
--of characters
Select PersonLastName, PersonFirstName
From Person
Where PersonLastName like '%ay%'

Select * From Donation
--scalar date functions
Select Distinct Month(DonationDate) AS [Month] from Donation
Select Distinct  Year(DonationDate) as [Year] from Donation
Select Distinct Day(DonationDate) As [Day] from Donation

Select Distinct Month(DonationDate) AS [Month] ,
Year(DonationDate) as [Year],
Day(DonationDate) As [Day]
From Donation

Select * from Donation
--datediff function
Select DonationKey, donationAmount, DateDiff(dd,DonationDate, DonationConfirmDate) [Days to confirmation]
From Donation
Where DateDiff(dd,DonationDate, DonationConfirmDate) > 3

--aggregate functions
Select count(DonationKey) as [total Number] From Donation
Select Sum(DonationAmount) as total from Donation
Select AVG(DonationAmount) as Average from Donation
Select max(DonationAmount) as Maximum from Donation
Select min(DonationAmount) as Minimum from Donation

--group by
--you must group by any column not included in the aggregate function
Select Month(DonationDate) as [Month], Sum(DonationAmount) as Total
From Donation
Group by Month(DonationDate)

--use of having
--when you have an aggregate value in the criteria
--you need to use "having" instead of where
Select PersonKey, count(personKey) from Donation
Group by Personkey
having count(personKey) > 1

Select * From Donation
-- inner join
Select [PersonLastName],[PersonFirstName], Apartment, Street, City
[PersonUsername], DonationDate,
DonationAmount
From Donation
Inner join Person
on person.PersonKey=Donation.PersonKey
inner Join PersonAddress
on person.PersonKey=PersonAddress.PersonKey

--outer join
Select ServiceName, sg.ServiceKey
From CommunityService cs
left outer Join ServiceGrant sg
on cs.ServiceKey=sg.ServiceKey
Where sg.ServiceKey is null

--sub query examples
Select PersonFirstName,PersonlastName, DonationAmount From Donation
inner join Person on Person.PersonKey=Donation.PersonKey
Where DonationAmount=(Select Max(DonationAmount) From donation)

Select Distinct (Select Max(DonationAmount) from Donation) as [Max],
(Select Min(donationAmount) From donation) as [Min],
(Select Avg(DonationAmount) From Donation) as Average
From Donation

Triggers

--triggers
use automart
Go
--here is simple trigger that does nothing much
--a trigger is always "on" a table
--it must be "for", "after" or "instead of" one or more
--events such as Insert, update or delete
Create trigger tr_doNothing on Person
for insert
As
print 'An insert occurred'
go

--this will fire the trigger
--it also uses unicode (N') to enter non ASCII characters
Insert into Person(LastName, FirstName)
Values(N'Στεφανοσ',N'Κονγεροσ')

Select * From Person

Select VehicleId, Count(VehicleID) From Employee.VehicleService
group by VehicleId
having count (vehicleID) > 1

--Get a count of how many services they have
--if it is > 4
--message that they get a 10% discount
--there are two temp tables you can access
--inserted and deleted
--after happens after the insert has taken place
go

Create trigger tr_FourTimeDiscount on Employee.VehicleService
after Insert
As
--declare internal variables
Declare @vehicleID int
Declare @PersonKey int
Declare @Count int

--get the vehicleId from the temporary
--table inserted
select @VehicleID = VehicleID from Inserted

--get the personkey based on the vehicleID
--because we want to give the discount
--only to registered customers
Select @personkey = PersonKey from Customer.Vehicle
where vehicleId= @VehicleID

--so if the customer exists as a registered customer
if exists
 (Select RegisteredCustomerID 
  From Customer.RegisteredCustomer
  Where PersonKey=@PersonKey)
Begin
--get the count of their visits
Select @Count=count(vs.VehicleID) From Employee.VehicleService vs
inner Join Customer.Vehicle v
on vs.VehicleID=v.VehicleId
Where personkey=@Personkey
--if the count is greater than or = 4
--alert them of the discount
if @count >=4
Begin
--print is not your best method for this
--better return a variable
print 'Congratulations you earned a 10% discount'
End -- end if > than

End --end if exists

--test the trigger
Insert into Employee.VehicleService(VehicleID, LocationID, ServiceDate, ServiceTime)
Values(1, 1,GetDate(), GetDate())
Select * From Employee.VehicleService

go
--this trigger will create a table where 
--a user can see what was deleted
--and have a chance to restore it
--if the delete was wrong or an accident
Create Trigger tr_CustomerDelete on Customer.RegisteredCustomer
Instead of Delete
As
--we check to see if our temp table exists or not
if not Exists
  (Select name from sys.Tables 
   where name = 'RegisteredCustomerDeletes')
Begin
--if it doesn't exist create it
Create table RegisteredCustomerDeletes
(
 RegisteredCustomerID int, 
 Email nvarchar(255), 
 CustomerPassword nvarchar(20), 
 PersonKey int
)
End
--Get values from the Deleted Table
Declare @CustomerId int
Select @customerID = RegisteredCustomerID from Deleted

--do the insert from the deleted table
Insert into RegisteredCustomerDeletes(RegisteredCustomerID, Email, CustomerPassword, PersonKey)
Select RegisteredCustomerID, Email, CustomerPassword, PersonKey from Deleted

--then do the actual deletion
Delete from Customer.RegisteredCustomer
Where RegisteredCustomerID=@CustomerID

--**************end trigger************

Select * From Customer.RegisteredCustomer

--test Trigger
Delete From Customer.RegisteredCustomer
Where RegisteredCustomerID>36

--see if it wrote the deletions
Select * from RegisteredCustomerDeletes

ITC Resources