Stored Procedures Winter 2014

--insert a new customer into automart
--insert into person
--insert into vehicle
--insert into RegisteredCustomer
--transaction --all the inserts should happen or none of them
--should check to make sure the customer doesn't already exist

Use Automart
Go
--this version just does the inserts raw
--it is possible to insert into one or more tables
--even though it errors in another
Create proc usp_AddNewCustomer
--parameters 
@LastName nvarchar(255), 
@FirstName nvarchar(255),
@LicenseNumber nvarchar(10), 
@VehicleMake nvarchar(255), 
@VehicleYear nchar(4), 
@Email nvarchar(255),
@CustomerPassword nvarchar(20)
As
Insert into Person(Lastname, FirstName)
Values(@lastName, @firstName)

Declare @Personkey int
set @PersonKey=ident_Current('Person')

Insert into Customer.vehicle 
(LicenseNumber, VehicleMake, VehicleYear, personKey)
Values(@LicenseNumber, @VehicleMake, @VehicleYear, @PersonKey)

Insert into Customer.RegisteredCustomer
(Email, CustomerPassword, PersonKey)
Values(@Email, @CustomerPassword, @PersonKey)

Go
--test1
exec usp_AddNewCustomer
@LastName ='Nelson', 
@FirstName ='Admiral',
@LicenseNumber='FTW100' , 
@VehicleMake='Prius', 
@VehicleYear='2014', 
@Email='admiral@gmail.com',
@CustomerPassword = 'password'

Select * From Person
Select * From Customer.Vehicle
Select * From Customer.RegisteredCustomer

--version two
--this version adds a transaction and a
--try catch
Go

Alter proc usp_AddNewCustomer
--parameters 
@LastName nvarchar(255), 
@FirstName nvarchar(255),
@LicenseNumber nvarchar(10), 
@VehicleMake nvarchar(255), 
@VehicleYear nchar(4), 
@Email nvarchar(255),
@CustomerPassword nvarchar(20)
As
Begin Tran
Begin Try
Insert into Person(Lastname, FirstName)
Values(@lastName, @firstName)

Declare @Personkey int
set @PersonKey=ident_Current('Person')

Insert into Customer.vehicle 
(LicenseNumber, VehicleMake, VehicleYear, personKey)
Values(@LicenseNumber, @VehicleMake, @VehicleYear, @PersonKey)

Insert into Customer.RegisteredCustomer
(Email, CustomerPassword, PersonKey)
Values(@Email, @CustomerPassword, @PersonKey)
Commit tran
End try
Begin Catch
Rollback tran
print 'The transaction failed'
print error_message() 
end catch
Go

--version 3
--this version checks to see if the customer exists
--and then if the vehicle exists
--if the customer exists and the vehicle does not exist, insert it
--otherwise just end
--if the customer does not exist insert the person, 
--vehicle and registered customer
Alter proc usp_AddNewCustomer
--parameters 
@LastName nvarchar(255), 
@FirstName nvarchar(255),
@LicenseNumber nvarchar(10), 
@VehicleMake nvarchar(255), 
@VehicleYear nchar(4), 
@Email nvarchar(255),
@CustomerPassword nvarchar(20)
As

If exists
 (Select p.PersonKey from Person p
 inner Join Customer.Vehicle v
 on p.personkey=v.personkey
 inner join Customer.RegisteredCustomer r
 on r.PersonKey=p.Personkey
 Where lastname=@lastName
 and firstname=@firstName
 and Email = @Email)

Begin
 Declare @pKey int
 Select @pkey=p.PersonKey from Person p
 inner Join Customer.Vehicle v
 on p.personkey=v.personkey
 inner join Customer.RegisteredCustomer r
 on r.PersonKey=p.Personkey
 Where lastname=@lastName
 and firstname=@firstName
 and Email = @Email
 if not exists
  (Select LicenseNumber, VehicleMake, VehicleYear
    from Customer.Vehicle
    Where personkey=@pKey
    and LicenseNumber=@licenseNumber
    and vehicleMake=@VehicleMake
    and VehicleYear=@VehicleYear)
  Begin
  Begin try
   Insert into Customer.vehicle(LicenseNumber,VehicleMake, VehicleYear, Personkey)
   Values (@LicenseNumber, @VehicleMake, @VehicleYear, @pKey)
   return
  End Try
  Begin Catch
  print 'The license plate already exists'
  return
  End Catch
  end
  Else
  Begin
  print 'Already in the database'
  return
  end
End
Begin Tran
Begin Try
Insert into Person(Lastname, FirstName)
Values(@lastName, @firstName)

Declare @Personkey int
set @PersonKey=ident_Current('Person')

Insert into Customer.vehicle 
(LicenseNumber, VehicleMake, VehicleYear, personKey)
Values(@LicenseNumber, @VehicleMake, @VehicleYear, @PersonKey)

Insert into Customer.RegisteredCustomer
(Email, CustomerPassword, PersonKey)
Values(@Email, @CustomerPassword, @PersonKey)
Commit tran
End try
Begin Catch
Rollback tran
print 'The transaction failed'
print error_message() 
end catch

Go
exec usp_AddNewCustomer
@LastName ='Brown', 
@FirstName ='Susan',
@LicenseNumber='WEB100' , 
@VehicleMake='Tesla', 
@VehicleYear='2013', 
@Email='sb@gmail.com',
@CustomerPassword = 'password'

Select * From Customer.Vehicle

--this is a stored procedure for updating customer
--information
--it has a problem if the customer has more than
--one vehicle. I will let you solve that
Go
Create proc usp_UpdateCustomer
@LastName nvarchar(255), 
@FirstName nvarchar(255),
@LicenseNumber nvarchar(10), 
@VehicleMake nvarchar(255), 
@VehicleYear nchar(4), 
@Email nvarchar(255),
@CustomerPassword nvarchar(20),
@Personkey int
As
Begin tran
Begin try
Update Person
set LastName=@LastName,
FirstName=@firstName
Where Personkey=@personkey

--count the vehicles see if you are
--updating an existing vehicle
--or adding something entirely new
Update Customer.Vehicle
Set LicenseNumber=@LicenseNumber,
VehicleMake=@VehicleMake,
VehicleYear=@vehicleYear
Where PersonKey = @Personkey
And @licenseNumber=@LicenseNumber

Update Customer.RegisteredCustomer
set Email=@Email,
CustomerPassword=@CustomerPassword
Where personKey=@PersonKey
Commit tran
End Try
Begin Catch
Rollback tran
print 'sorry'
end catch

go
exec usp_UpdateCustomer
@LastName ='Brown-Meyers', 
@FirstName ='Susan',
@LicenseNumber='WEB100' , 
@VehicleMake='Tesla', 
@VehicleYear='2013', 
@Email='brown_meyers@gmail.com',
@CustomerPassword = 'password',
@personkey=74

Select * From Person
Select * From Customer.Vehicle
Select * from Customer.RegisteredCustomer where personkey=74

First Stored Procedure Example

Variable

Here is the work (mess) we made to show how to use variables

Use Automart

--these are the functions and queries we ended with last week
Select VehicleServiceID, ServiceName, ServicePrice, DiscountPercent, 
dbo.fx_Percentages(ServicePrice, DiscountPercent) as [Discount Price],
dbo.fx_GetPercent(ServicePrice, TaxPercent) as Tax
From Customer.AutoService a
inner join Employee.VehicleServiceDetail vsd
on a.AutoServiceID=vsd.AutoServiceID
Where VehicleServiceID=3

Select VehicleServiceID, 
Sum(dbo.fx_Percentages(ServicePrice, DiscountPercent)) as SubTotal,
Sum(dbo.fx_GetPercent(ServicePrice, TaxPercent)) as Tax,
Sum(dbo.fx_Percentages(ServicePrice, DiscountPercent) + 
 dbo.fx_GetPercent(ServicePrice, TaxPercent)) as Total
From Customer.AutoService a
inner join Employee.VehicleServiceDetail vsd
on a.AutoServiceID=vsd.AutoServiceID
Where VehicleServiceID=3
Group by VehicleServiceID

--this breaks up the above and divides it into variables
--it is a bit of a mess and much more work than the other way
--but does show the use of variables

--declare the variables
Declare @Subtotal decimal(10,2)
Declare @Tax decimal(10,2)
Declare @Total decimal(10,2)

--get the subtotal
Select @Subtotal=Sum(dbo.fx_Percentages(ServicePrice, DiscountPercent))
From Employee.VehicleServiceDetail sd
Inner Join Customer.AutoService a
on sd.AutoServiceID=a.AutoServiceID
Where VehicleServiceID=3
group by VehicleServiceID

--get the tax amount
Select @Tax=Sum(dbo.fx_GetPercent(ServicePrice, TaxPercent))
From Employee.VehicleServiceDetail sd
Inner Join Customer.AutoService a
on sd.AutoServiceID=a.AutoServiceID
Where VehicleServiceID=3
Group by vehicleServiceID

--get total
print 'subtotal ' + Cast(@Subtotal as nvarchar)
print 'Tax ' + Cast(@Tax as nvarchar)
Select @Total=@subtotal + @Tax

Select VehicleServiceID, @Subtotal as SubTotal, @Tax as Tax, @total as Total
From Employee.VehicleServiceDetail
Where VehicleServiceID=3
Group by VehicleServiceID

---

Simple Stored Procedure

Here is our first, simple stored procedure

--here is a stored procedure that uses the functions
--and queries above and creates a parameter for the 
--VehicleServiceID. This gives it the flexibility
--so you can use it with any Vehicle's service
Go
Create proc usp_GetReceipt
@VehicleServiceID int
AS

Select VehicleServiceID, ServiceName, ServicePrice, DiscountPercent, 
dbo.fx_Percentages(ServicePrice, DiscountPercent) as [Discount Price],
dbo.fx_GetPercent(ServicePrice, TaxPercent) as Tax
From Customer.AutoService a
inner join Employee.VehicleServiceDetail vsd
on a.AutoServiceID=vsd.AutoServiceID
Where VehicleServiceID=@vehicleServiceID

Select VehicleServiceID, 
Sum(dbo.fx_Percentages(ServicePrice, DiscountPercent)) as SubTotal,
Sum(dbo.fx_GetPercent(ServicePrice, TaxPercent)) as Tax,
Sum(dbo.fx_Percentages(ServicePrice, DiscountPercent) + 
 dbo.fx_GetPercent(ServicePrice, TaxPercent)) as Total
From Customer.AutoService a
inner join Employee.VehicleServiceDetail vsd
on a.AutoServiceID=vsd.AutoServiceID
Where VehicleServiceID=@VehicleServiceID
Group by VehicleServiceID

Go
--this is not part of the stored procedure. 
--these are using the stored procedure
--two versions with the same result
Execute usp_GetReceipt 4
Exec usp_GetReceipt @VehicleServiceID=7

Link to GitHub code for Assignment5

Here is the link to the Github code for assignment 5

https://github.com/spconger/Assignment5172Example

Home made Functions

 --functions 
 --scalar functions operate on one row at time
 --table valued function
 --aggregate functions 

 use automart
 go

 --here is an a very simple function
 Create function fx_Cube
 (@number int ) --parameters, provided by the user
 returns int --return type for the function note the s
 As
 Begin -- begin function
 Declare @Cube int --declare a variable
 Set @Cube = @number * @number * @number
 return @cube --value to return
 End
 Go

 --using the function
 Select dbo.fx_Cube(37) as Cube

 go
 --altering the function to change the data type
  alter function fx_Cube
 (@number money )
 returns money
 As
 Begin
 Declare @Cube money
 Set @Cube = @number * @number * @number
 return @cube
 End

 Go
 ---use the changed function
 Select ServicePrice, dbo.fx_cube(ServicePrice) as increase
 From Customer.AutoService

Go
--this uses an if statement
Create function fx_GetPercent
(@number decimal(10,2), @percent decimal(3,2))
Returns decimal(10,2)
As
Begin
Declare @percentage decimal(10,2)
Set @Percentage = @number * @Percent
Return @Percentage
End
Go 
Select ServicePrice, dbo.fx_GetPercent(ServicePrice, .05) Discount,
ServicePrice -dbo.fx_GetPercent(ServicePrice, .05) DiscountedPrice
From Customer.AutoService
Go
--added an if statement
Alter function fx_GetPercent
(@number decimal(10,2), @percent decimal(3,2))
Returns decimal(10,2)
As
 Begin --begin function
 Declare @percentage decimal(10,2)
  if @Percent > 1
   Begin --begin if
   Set @Percent=@Percent /100
  End --end if
 Set @Percentage = @number * @Percent
 Return @Percentage
End --end function

--- price is the autoservice table
-- in employee service detail discount percent and tax percent
--0's have 
Go
Alter function fx_Percentages
(@amount decimal(10,2), @percent decimal(3,2))
returns decimal(10,2)
AS
Begin --begin function
 Declare @result decimal(10,2)
  if @amount != 0
   Begin
    Set @result =@Amount-(@amount * @Percent)
   End --end if
  Else
   Begin --begin else
    Set @result=@amount
   End --end else
return @Result
End --end function

Go
Select * From Employee.VehicleServiceDetail
-- Here are a couple of fairly complex queries to figure out
--the totals for a service

Select VehicleServiceID, ServiceName, ServicePrice, DiscountPercent, 
dbo.fx_Percentages(ServicePrice, DiscountPercent) as [Discount Price],
dbo.fx_GetPercent(ServicePrice, TaxPercent) as Tax
From Customer.AutoService a
inner join Employee.VehicleServiceDetail vsd
on a.AutoServiceID=vsd.AutoServiceID
Where VehicleServiceID=3

Select VehicleServiceID, 
Sum(dbo.fx_Percentages(ServicePrice, DiscountPercent)) as SubTotal,
Sum(dbo.fx_GetPercent(ServicePrice, TaxPercent)) as Tax,
Sum(dbo.fx_Percentages(ServicePrice, DiscountPercent) + 
 dbo.fx_GetPercent(ServicePrice, TaxPercent)) as Total
From Customer.AutoService a
inner join Employee.VehicleServiceDetail vsd
on a.AutoServiceID=vsd.AutoServiceID
Where VehicleServiceID=3
Group by VehicleServiceID

Indexes and Views

Use MagazineSubscription

--adding a column
Alter table Customer
Add Email Nvarchar(255) 

Select * from Customer

--dropping a column
Alter Table Customer
Drop column Email


--drop a foreign key constrain
Alter table MagazineDetail
Drop constraint FK1_MagazineDetails

--use a system table to see foreign keys in current database
Select * from sys.foreign_keys 

--create a table to test default
Create table test
(
 testID int identity(1,1) primary key,
 testState nvarchar(10) default 'finished'
)
--add a column
Alter table test 
add testDate date 

--insert some records
Insert into test (TestDate) Values (GetDate())

Select * from Test

/**********************************
*Indexes and views
***********************************/
Use Automart

--noclustered is optional
Create nonclustered index ix_LastName on Person(LastName)

Drop index [UQ__Register__A9D105341CAB755D] on Customer.RegisteredCustomer

--uique index
Create unique index ix_Email on Customer.RegisteredCustomer(Email)
--filtered index
Create index ix_filtered on Employee.VehicleService(ServiceDate)
Where ServiceDate > '1/1/2012'

--composite index
Create index ix_service on Employee.vehicleService(ServiceDate, ServiceTime)

Create table testtable
(
 testKey int identity,
 TestName nvarchar(255) not null,
 Constraint PK_TestTable  primary key nonclustered(testKey)
)
--clustered index
Create clustered index ix_testname on TestTable(testName)

Insert into testTable(testName)
values('FirstTest'),
('a test'),
('third test')

Insert into testTable(testName)
values('alpha')


Select * from testTable
\--forcing an index
Select LastName, firstName, email, LicenseNumber, VehicleMake, VehicleYear
From Person p with (NoLock, Index(ix_lastName))
inner join customer.RegisteredCustomer rc
on p.Personkey=rc.PersonKey
inner join Customer.Vehicle v
on p.Personkey =v.PersonKey
Where LastName='Smith' 

Go --go seperates batches
--views
Create view vw_Customer
As
Select LastName [Last Name]
, firstName [first Name]
, email Email
, LicenseNumber [License Number]
, VehicleMake Make
, VehicleYear [Year]
From Person p 
inner join customer.RegisteredCustomer rc
on p.Personkey=rc.PersonKey
inner join Customer.Vehicle v
on p.Personkey =v.PersonKey
go
Select * from vw_customer

--using a view
Select [Last Name], Email from vw_Customer
Where [Last Name] ='Smith'
order by Email
go

Create view vw_simple
As
Select * from Person
Order by LastName
--can't order a view

Go
--altering a view
Alter view vw_customer
As
Select LastName [Last Name]
, firstName [first Name]
, email Email
, LicenseNumber [License Number]
, VehicleMake Make
, VehicleYear [Vehicle Year]
From Person p 
inner join customer.RegisteredCustomer rc
on p.Personkey=rc.PersonKey
inner join Customer.Vehicle v
on p.Personkey =v.PersonKey

Creating and altering Tables

Create DataBase PetPals

Use PetPals

Create table Pet
(
   PetKey int identity(1,1) primary key,
   PetName nvarchar(255) not null,
   PetSpecies nvarchar(255) not null,
   PetSex nchar(1),
   PetCondition nvarchar(255),
   PetAge int,
   PetDateAdded Date default GetDate()
)

alter table Pet
Add Constraint chk_Sex Check (PetSex in ('F', 'M'))




Insert into Pet(PetName, PetSpecies, PetSex, PetCondition, PetAge)
Values('Skittles','Cat','F', 'Good', 2)

Select * from Pet


Create Table CareTakers
(
 CareTakerKey int identity(1,1),
 CaretakerName nvarchar(255) not null,
 CareTakerStreet nvarchar(255) not null,
 CareTakerCity nvarchar(255) default 'Seattle',
 careTakerState nchar(2) default 'WA',
 CareTakerZip nchar(10) not null,
 CareTakerPhone nchar(13),
 Constraint PK_CareTakers primary key(CareTakerKey)
)

Create table Services
(
 ServiceKey int identity(1,1),
 ServiceName Nvarchar(255) not null,
 ServicePrice Decimal(10,2) not null
)
 Alter table Services
 add Constraint PK_Service primary Key (ServiceKey)

 Alter table Services
 Add Constraint unique_name Unique(ServiceName)

Create Table CareTakerPet
(
 CareTakerPetKey int identity(1,1),
 PetKey int Foreign key references Pet(PetKey),
 CareTakerKey int,
 CareTakerPetDate Date not null,
 Constraint FK_CareTaker Foreign Key (CareTakerKey)
  references CareTakers(CareTakerKey),
 Constraint PK_CareTakerPet Primary Key (CareTakerPetKey)
 

)

Create Table CareTakerPetDetail
(
 CareTakerPetKey int,
 ServiceKey int,
 Constraint PK_CareTakerPetDetail Primary Key (CareTakerPetKey, ServiceKey),
 Constraint FK_CareTakerPetKey Foreign Key (CareTakerPetKey)
  References CaretakerPet(CareTakerPetKey),
 Constraint FK_Service foreign key (ServiceKey) 
  references Services(ServiceKey)
)

ADO Data Entities

For this example we will do much the same thing we did for the ADO example for assignment 4, only we will do it with ADO Entities. This will require less coding since the Entities wizards will create classes for all the Tables we add to the designer. We can use the same hash class as in the previous assignment.

First we create a new Empty web site. I will call it "Assignment5Example."

Next I am going to add an App_Code folder and add the existing class "PasswordHash."

To use the ADO Entities we will need to have a Data connection in the Server window. If it is is not open, open the Server window from View. Make a new DataConnection to Automart Database. Use the AutomarLogin we created for Assignment4

It will only show you the tables you have permissions for.

Now Let's add the ADO Data Entites. Right click on the project and select Add NewItem then ADO Data Entity Model. I am naming it "AutomartModel."

Click ADD, then YES when it tells you it needs to place these files in a special folder. Then Choose Generate from Database

Click NEXT, then make sure it has the correct connection. Click YES to include sensitive data and then NEXT

Next you need to select what you are going to add to the designer. Select all the tables under the customer and dbo schemas

Click Finish and it will set up the designer. You may get one or more security warnings. Just click OK.

Now we set up the Web forms. The login form, the registration form and the Welcome will have exactly the same structure as the assignment four example, so I will not go over them here.

I also added the PasscodeGenerator class from the previous assignment. I realized I would need it for the registration.

Let's do the registration code first. We will do this in the page, as there is little gained by separating it into a class. Double click the submit button in design view.

first we will initialize the data entities classes

//initialize the Data Entity Data context
        AutomartEntities entites = new AutomartEntities();

Then we initialize the person class, set its properties and add it to the people collection. Next we do the same with Vehicles. The Person, property sets the relation to the person just created above and will insert that person's personkey as a foreign key.

 //initialize the person class, set the values
        //and add to People (a class that stores persons)
        Person p = new Person();
        p.FirstName = txtFirstName.Text;
        p.LastName = txtLastName.Text;

        entites.People.Add(p);

        //same for vehicle
        vehicle v = new vehicle();
        v.LicenseNumber = txtLicense.Text;
        v.Person = p;
        v.VehicleMake = txtMake.Text;
        v.VehicleYear = txtYear.Text;
        entites.vehicles.Add(v);

The Registered customer class will take a little more work because we have to get the passcode and hash the password. Once done we save changes and the values are written to the database

PasscodeGenerator pg = new PasscodeGenerator();
        int passcode = pg.GetPasscode();

        PasswordHash ph = new PasswordHash();

        RegisteredCustomer rc = new RegisteredCustomer();
        rc.Person = p;
        rc.Email = txtEmail.Text;
        rc.CustomerPassCode = passcode;
        rc.CustomerPassword = txtPassword.Text;
        rc.CustomerHashedPassword = ph.HashIt(txtPassword.Text, passcode.ToString());
        entites.RegisteredCustomers.Add(rc);

        //write to database
        entites.SaveChanges();

Now let's turn to the Login. We will create a new Login class. This one will use the entities syntax. So First add a class to the app_code called LoginClass. this class needs to take the user name and password, query the database to retrieve the passcode and then hash the password and code. If they match the existing hash then the user is authenticated. Here is the whole class with comments.

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

/// 

/// Summary description for LoginClass
/// 
public class LoginClass
{
    string user;
    string pass;
    int personkey=0;
    Byte[] hashed;
    AutomartEntities ent = new AutomartEntities();

 public LoginClass(string userName, string pWord)
 {
        user = userName;
        pass = pWord;
 }

    //get the passcode from the database
    private int GetPassCode()
    {
        int passcode = 0;
        //this is linq syntax for a query
        var code = from c in ent.RegisteredCustomers
                   where c.Email == user
                   select new { c.PersonKey, c.CustomerPassCode, c.CustomerHashedPassword };
        //we loop through the result and assign some values
        foreach (var v in code)
        {
            passcode = (int)v.CustomerPassCode;
            personkey = (int)v.PersonKey;
            hashed = (Byte[])v.CustomerHashedPassword;
        }
        return passcode;
    }
    // this method hashes the password and matches the database hash
    //to the new hash
    public int Authenticate()
    {
        int passcode = GetPassCode();
        PasswordHash ph = new PasswordHash();
        Byte[] newHash = ph.HashIt(pass, passcode.ToString());
        int pk = 0;

        if (hashed == newHash)
        {
            pk = personkey;
        }

        return pk;
    }
}

Finally we need to write some code for the Login button. It really doesn't need to change much from the code we wrote before.

LoginClass lc = new LoginClass(txtUser.Text, txtPassword.Text);
        int personKey = lc.Authenticate();
        if (personKey != 0)
        {
            Session["personkey"] = personKey;
            Response.Redirect("Welcome.aspx");
        }
        else
        {
            lblError.Text = "Invalid login";
        }
        

Now We will try a login

Ident_current function

When you insert into several tables, and when the primary key is an identity (auto number) the Ident_current function can be used to return the identity created by the insert above. In other words, if you insert a person, you will need the new personkey to insert as a foreign key in the vehicle table and the Registered customer table. Here is an example of an insert using the function.

Use Automart

Insert into Person(lastName, FirstName)
Values ('Oil', 'Caster')

Insert into Customer.vehicle (LicenseNumber, VehicleMake, VehicleYear, PersonKey)
values ('xyz123', 'Ford', '2012', ident_current('Person'))

Note that the argument for the Ident_Current function is the name of the table not the column name