MVC Controller to Show Donors and Emails

Here is the controller I created that shows the donors names and emails. This assumes that you have added the ADO Data Entity to model. It, oddly, also required me to create a new donor class to store the results of the query. It was the only way it would pass the results to the View

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Data.Entity;


namespace CommunityAssistMVCProject.Models
{
    public class DonorController : Controller
    {
        //
        // GET: /Donor/

        Donor d;
        CommunityAssistEntities cae = new CommunityAssistEntities();

             
        public ActionResult Index()
        {
            var don = (from p in cae.Donations
                      orderby p.Person.PersonLastName
                      where p.PersonKey == p.Person.PersonKey
                      select new { p.Person.PersonLastName, p.Person.PersonFirstName, p.Person.PersonUsername }).Distinct();

            List<Donor> donors = new List<Donor>();

            foreach (var x in don)
            {
                d = new Donor();
                d.LastName = x.PersonLastName;
                d.FirstName = x.PersonFirstName;
                d.Email = x.PersonUsername;
                donors.Add(d);
            }
                      
            return View(donors);
        }

    }

    public class Donor
    {
        public string PersonKey { get; set; }
        public string LastName { get; set; }
        public string FirstName { get; set; }
        public string Email { get; set; }

    }
}

SQL Part 1

Use TutorManagement
/* these are the basic
forms of the select statement
*/
Select * From Tutor
Select TutorLastName as [Last Name], TutorFirstName as [First Name], TutorEmail as Email
From Tutor

Select TutorLastName as [Last Name], TutorFirstName as [First Name], TutorEmail as Email
From Tutor
order by TutorLastName

Select TutorLastName as [Last Name], TutorFirstName as [First Name], TutorEmail as Email
From Tutor
order by TutorLastName desc

Select * From Tutor where tutorLastName='Brown'

--when searching for nulls use "is" or "is Not"
Select TutorLastName, TutorEmail from Tutor Where TutorEmail is not null

Select * from student where studentAge > 30
Select * from student where studentAge < 30
Select * from student where studentAge >= 30
Select * from student where studentAge <= 30

Select * From Student where StudentEmail Like '%hotmail%' or StudentEmail Like '%gmail%'

Select * from Session
Select Year(SessionDateKey) as [Year] From Session
Select Month(SessionDateKey) as [Month] From Session
Select Day(SessionDateKey) as [Day] from Session

Select cast(SessionTimeKey as Nvarchar(8)) from Session

Select DatePart(hour, SessiontimeKey) as [hour] from Session
Select DatePart(minute, SessionTimeKey) as [Minute] from Session

Select datediff(dd,GetDate(), '3/26/2014')
Select GetDate()
Select dateAdd(dd, 40, GetDate())

Select * From Student
Select Count(StudentKey) From Student
Select Count(StudentAge) From Student
Select Avg(StudentAge) From Student
Select max(StudentAge) From Student
Select Min (StudentAge) from Student
Select Sum(StudentAge) From Student

Admin commands and xml

Admin

--authentication
--Authorization

Use Master

Create Login EmployeeLogin with password='P@ssw0rd1', default_database=communityAssist

Use CommunityAssist

Create user EmployeeUser for Login EmployeeLogin

Create role EmployeeRole

Grant Select, Update on ServiceGrant to EmployeeRole
Grant Insert, Select, Update on GrantReview to EmployeeRole
Grant select on person to Employeerole

exec sp_addrolemember 'EmployeeRole', 'EmployeeUser'

---

XML

/* way to transfer and carry data
an element <element> </element> <element/>
unicode
nested properly
attributes 
root element   -- namespaces

<memo xmlns="http://spconger.com/memo">

Schema: xsd:
xslt */

use CommunityAssist

Select PersonLastname, PersonfirstName, 
PersonUserName from Person for xml raw('person'), elements, root('people')

Select PersonlastName, PersonfirstName, 
EmployeeHireDate, EmployeeStatus, EmployeeMonthlySalary
From Person 
inner join Employee 
on person.PersonKey=employee.PersonKey
for xml auto, elements, root('Employees')

Select * From GrantReview

Create xml Schema Collection meetingSchema
As 
'<?xml version="1.0" encoding="utf-8"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" targetNamespace="http://www.communityassist.com/meetingnotes" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="meetingnotes">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="head">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="date" type="xs:string" />
              <xs:element name="topic" type="xs:string" />
            </xs:sequence>
          </xs:complexType>
        </xs:element>
        <xs:element name="body">
          <xs:complexType>
            <xs:sequence>
              <xs:element maxOccurs="unbounded" name="p" type="xs:string" />
              <xs:element name="actions">
                <xs:complexType>
                  <xs:sequence>
                    <xs:element maxOccurs="unbounded" name="item">
                      <xs:complexType>
                        <xs:attribute name="name" type="xs:string" use="required" />
                        <xs:attribute name="due" type="xs:string" use="required" />
                      </xs:complexType>
                    </xs:element>
                  </xs:sequence>
                </xs:complexType>
              </xs:element>
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>'


Create table Meeting
(
 MeetingKey int identity primary key,
 MeetingDate Date not null,
 MeetingNotes xml(meetingSchema)
)

Insert into Meeting (MeetingDate, MeetingNotes)
Values(GetDate(),
'<?xml version="1.0" encoding="utf-8"?>
<meetingnotes xmlns="http://www.communityassist.com/meetingnotes">
  <head>
    <date>3/5/2014</date>
    <topic>Xml</topic>
  </head>
  <body>
    <para>
     Xml is very useful. Very simple in concept. 
    </para>
    <para>
      But it can get complicated in practice
    </para>
    <actions>
      <item name="Create Schema" due="3/5/2014"/>
      <item name="Query" due="3/5/2014"/>
    </actions>
  </body>
</meetingnotes>')

select * from meeting

Select * from GrantReview

/*Select MeetingDate, MeetingTopic, 
meetingnotes.query('declare namespace mn="http://www.seattlecentral.edu/meetingnotes"; 
//mn:meetingnotes/mn:heading/mn:attending/mn:member') as Attending
From Meeting*/

Select GrantReviewDate, EmployeeKey, GrantReviewNote.query
('declare namespace rv="http://www.communityassist.org/reviewnotes"; //rv:reviewnote/rv:comment') 
From GrantReview
Where GrantReviewDate='8/11/2013'

Trigger

Use CommunityAssist
Go
Create trigger tr_MaxGrant on ServiceGrant
instead of Insert
As
Declare @amount money
Declare @max money
Declare @ServiceKey int

Select @ServiceKey=serviceKey, @Amount=GrantAmount
From Inserted 
Select @max=ServiceMaximum from CommunityService
Where ServiceKey = @ServiceKey
If @Amount <= @Max
Begin
Insert into ServiceGrant(
GrantAmount, GrantDate, PersonKey, ServiceKey)
Select GrantAmount, GrantDate, PersonKey, ServiceKey
From Inserted
End
Else
Begin
if not exists
 (Select name from sys.tables where name ='Rejects')
 Begin
  Create Table Rejects
  (
   PersonKey int,
   ServiceKey int,
   GrantDate Datetime,
   GrantAmount money,
   ServiceMaximum money,
   GrantDiff money
  )
 End
Insert into Rejects (PersonKey, ServiceKey, GrantDate, GrantAmount, 
ServiceMaximum, GrantDiff)
Select PersonKey, ServiceKey, GrantDate, GrantAmount, @max, GrantAmount-@max
From inserted

End

Select * from ServiceGrant

Select * from CommunityService

Insert into ServiceGrant([GrantAmount],[PersonKey],[GrantDate],[ServiceKey])
Values (300.00, 4, GetDate(), 4)

Select * From ServiceGrant where Personkey =4

Select * from Rejects