ITC Resources

Tuesday, July 10, 2012

Add Venue Stored Procedure and Web Form

Here is the stored procedure we created in SQL Server. For this to work, there must be a schema named Venue and a role called VenueRole. The venue roll must gran exec on the Venue Schema and select and insert on the Venue table. (Sorry for naming them the same, a mental fart on my part.)

Some of the parameters have default values. this allows us to skip them. (Specifically VenueAddress1, and VenueDescription)

/*Add new a new venue
Check to make sure the venue doesn't already exist
*/
Go
--this procedure uses the venue schema
--also proc is the same as procedure
Create procedure Venue.usp_AddVenue
@VenueName nvarchar(255),
@VenueAddress1 nvarchar(255) =null, 
@VenueAddress2 nvarchar(255), 
@VenueCity nvarchar(255), 
@VenueState nchar(2) ='WA', 
@VenuePhone nchar(10), 
@VenueCapacity int, 
@VenueAgeRestriction  bit =0, 
@VenueDescription xml =null, 
@VenueURL nvarchar(255)
AS
If not Exists
 (Select VenueName 
  From Venue
  Where VenueAddress2=@VenueAddress2
  And VenueCity=@VenueCity
  And VenueState=@VenueState)
Begin
 Insert into Venue(VenueName, VenueAddress1, VenueAddress2, VenueCity, VenueState, VenuePhone, VenueCapacity, VenueAgeRestriction, VenueDescription, VenueURL)
Values ( @VenueName, @VenueAddress1, @VenueAddress2, 
@VenueCity, @VenueState, 
@VenuePhone, @VenueCapacity, 
@VenueAgeRestriction, 
@VenueDescription, @VenueURL)
return 1 
End
Else
Begin
Print 'The venue already exists'
return 0
End
Go 
Exec Venue.usp_AddVenue
@VenueName ='Comet Tavern', 
@VenueAddress1=null, 
@VenueAddress2='922 Pike', 
@VenueCity='Seattle', 
@VenueState='Wa', 
@VenuePhone='2065551200', 
@VenueCapacity=99, 
@VenueAgeRestriction=true, 
@VenueURL='http://www.cometTavern.com'

Select * From Venue

Delete From Venue where VenueID=5

Next we created a web site that uses the schema. We made a form (an ugly one, to be sure) to add a new venue. It will call the stored procedure. Here is the Html and asp source code for the form

<%@ 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 Venue Name"></asp:Label>
        <asp:TextBox ID="txtVenueName" runat="server"></asp:TextBox>
      </p>
      <p>
        <asp:Label ID="Label2" runat="server" Text="Enter Venue Address"></asp:Label>
        <asp:TextBox ID="txtAddress" runat="server"></asp:TextBox>
      </p>

      <p>
        <asp:Label ID="Label3" runat="server" Text="Enter City"></asp:Label>
        <asp:TextBox ID="txtCity" runat="server"></asp:TextBox>
      </p>


      <p>
        <asp:Label ID="Label4" runat="server" Text="State"></asp:Label>
        <asp:TextBox ID="txtState" runat="server"></asp:TextBox>
      </p>

      <p>
        <asp:Label ID="Label5" runat="server" Text="Enter Venue Phone"></asp:Label>
        <asp:TextBox ID="txtPhone" runat="server"></asp:TextBox>
      </p>

      <p>
        <asp:Label ID="Label6" runat="server" Text="Enter Venue Capacity"></asp:Label>
        <asp:TextBox ID="txtCapacity" runat="server"></asp:TextBox>
      </p>
      <p>

        <asp:DropDownList ID="ddlAgeRestriction" runat="server">
        <asp:ListItem Text="Choose Age Restriction" />
        <asp:ListItem Text="yes" Value="1" />
        <asp:ListItem Text="no" Value="0" />
        </asp:DropDownList></p>
        <p>
        <asp:Label ID="Label7" runat="server" Text="Enter Venue URL"></asp:Label>
        <asp:TextBox ID="txtUrl" runat="server"></asp:TextBox>
        </p>
        <p>
        <asp:Button ID="Button1" runat="server" Text="Submit" onclick="Button1_Click" /><asp:Label ID="lblResult" runat="server"
            Text="Label"></asp:Label>
            </p>
    </div>
    </form>
</body>
</html>

Here is the code that connects to the database and calls the stored procedure

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;


public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        //ADO 1)make a connection
        //command object passes the sql to the server 
        //get all the values from the form

        //here we create a connection string that connects to the server
        //it contains the name of the server, the database and login information
        SqlConnection connect = new SqlConnection
            ("Data Source=localhost;initial catalog=VenueTracker;user=VenuLogin;password=password");

        //the command object passes sql to the server
        SqlCommand cmd = new SqlCommand();
        //it is going to use the connect object to get to the database
        cmd.Connection = connect;
        //we are going to use a stored procedure
        cmd.CommandType = CommandType.StoredProcedure;
        //here is the name of stored procedure
        cmd.CommandText = "usp_AddVenue";
        //We must provide a value for every parameter of the stored procedure
        //which does not have a default value
        cmd.Parameters.AddWithValue("@VenueName", txtVenueName.Text);
        cmd.Parameters.AddWithValue("@VenueAddress2", txtAddress.Text);
        cmd.Parameters.AddWithValue("@VenueCity", txtCity.Text);
        cmd.Parameters.AddWithValue("@VenueState", txtState.Text);
        cmd.Parameters.AddWithValue("@VenuePhone", txtPhone.Text);
        cmd.Parameters.AddWithValue("@VenueCapacity", int.Parse(txtCapacity.Text));
        cmd.Parameters.AddWithValue("@VenueAgeRestriction",int .Parse(ddlAgeRestriction.SelectedValue));
        cmd.Parameters.AddWithValue("@VenueURL", txtUrl.Text);

        //we open the connection
        connect.Open();
        //execute the stored procedure
        int x=cmd.ExecuteNonQuery();

        //check the results
        if (x == 1)
            lblResult.Text = "Venue successfully Entered";
        else
            lblResult.Text = "Problems abound";

        connect.Close();

    }
}

Monday, July 9, 2012

First Look at Inheritance

Here is the pet(parent) class


public class Pet {
 private String owner;
 private String name;
 private String type;
 private char gender;

 
 public void setOwner(String owner){
  this.owner=owner;
 }
 
 public String getOwner(){
  return owner;
 }

 public String getName() {
  return name;
 }

 public void setName(String name) {
  this.name = name;
 }

 public String getType() {
  return type;
 }

 public void setType(String type) {
  this.type = type;
 }
 
 public void setGender(char gender ){
     gender=Character.toUpperCase(gender);
  if (gender=='F' || gender=='M'){
   this.gender=gender;
  }
  else{
   this.gender='U';
  }
 }
 
 public char getGender(){
  return gender;
 }
 
 public String getPetInfo()
 {
  return getName() + ", " + getType() + ", " + getGender();
 }


 
 
   
 }

Here is the interface ISound

public interface ISound {
 public String makeSound();
}

Here is the dog class that inherits from pet and implements the interface ISound


public class Dog  extends Pet implements ISound{
    private String breed;
    private String color;
    
 public String getBreed() {
  return breed;
 }
 public void setBreed(String breed) {
  this.breed = breed;
 }
 public String getColor() {
  return color;
 }
 public void setColor(String color) {
  this.color = color;
 }
 
 public String getPetInfo()
 {
  String info = super.getPetInfo();
  return info + ", " + getBreed();
 }
 @Override
 public String makeSound() {
  
  return "bark";
 }
    
}

Finally here is the Program class with the main


public class Program {

 /**
  * @param args
  */
 public static void main(String[] args) {
  Dog d= new Dog();
  
  d.setBreed("Beagle");
  d.setGender('m');
  d.setName("Spot");
  d.setType("Dog");
  
  System.out.println(d.getPetInfo());
  System.out.println(d.makeSound());

 }

}

Saturday, July 7, 2012

Partitions

A more full description of this process can be found in the book.

Partitioning a table divides the data into separate files on separate file groups. The database developer can determine which data goes on which file. This can make it easier to manage and allow faster access. It is possible to query just one of the file groups which can make queries much quicker.

In order to be able to create partitions, you must have a database with multiple filegroups. We will start by creating a database:

Use Master
Go
Create database PartitionTest
Go

Now we will alter the database to add 3 filegroups

Alter Database PartitionTest
Add FileGroup FG1
Alter Database PartitionTest
Add FileGroup FG2
Alter Database PartitionTest
Add FileGroup FG3

Go

Now we have to add files to those file groups

Alter Database PartitionTest
Add File
(Name=FG1_dat, Filename='C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLServer\MSSQL\Data\PartitionTest_3.ndf', size=2mb)
To FIleGroup FG1

Alter Database PartitionTest
Add File
(Name=FG2_dat, Filename='C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLServer\MSSQL\Data\PartitionTest_4.ndf', size=2mb)
To FIleGroup FG2

Alter Database PartitionTest
Add File
(Name=FG3_dat, Filename='C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLServer\MSSQL\Data\PartitionTest_5.ndf', size=2mb)
To FIleGroup FG3

Now we need to change the context to the current database

Use PartitionTest

To partition a table or view you must first create a partition function and a partitio scheme. The partition function shows what the dividing values are. The scheme determines which file groups the data will reside on

Create Partition Function donationdateFunc (datetime)
As
Range Right
For Values('2/1/2010','4/1/2010')
Go

Create Partition Scheme donationDateScheme
As
Partition donationdateFunc
To (FG1, FG2, FG3)
Go

Now we will create a table that uses the partition. We cannot declare the primary key in the table, because, in order for the partition to work, the column we are using to divide the data on must be a clustered index. The only difference between this and a normal table definition is the on donationDateScheme clause that follows the table definition.

Create table Donation
(
 DonationKey int not null, 
 DonationDate Datetime not null, 
 DonationAmount money, 
 PersonKey int, 
 EmployeeKey int
)
on donationDateScheme(DonationDate)

Now we will alter the table to add a clustered index and a primary key. The primary key must include the DonationKey, the DonationDate and the partition scheme.

Create clustered Index ix_DonationDate on Donation(DonationDate)
on donationDateScheme(DonationDate)

Alter table Donation
Add Constraint pk_Donation Primary Key nonClustered (DonationKey, DonationDate)
on donationDateScheme(DonationDate)

Now we are going to add some data. We are going to copy all the information from the CommunityAssist donation table into our new donation table

Insert into Donation(DonationKey, DonationDate, DonationAmount, PersonKey, EmployeeKey)
Select DonationKey, DonationDate, DonationAmount, PersonKey, EmployeeKey
From CommunityAssist.dbo.Donation

Finally, we will do some queries. The first one just returns the whole table. The second one returns the data on the first partition, and the third on returns the data on the second partition

Select * from Donation

Select * from Donation 
where $partition.donationDateFunc(DonationDate)=2

Select * from Donation 
where $partition.donationDateFunc(DonationDate)=3

Thursday, July 5, 2012

Security 1

these commands are done on sql Server with admin permissions

Create Login test226 with password= 'p@ssw0rd1', default_database=Adventureworks

Use AdventureWorks

Create user test226 for login Test226 with default_schema=HumanResources

--, default_schema='Humanresources'

Create Role HumanResourcesRoll

Grant Select on Schema::HumanResources to HumanResourcesRoll
Grant Update on Schema::HumanResources to HumanResourcesRoll

exec sp_addRolemember HumanResourcesRoll, test226

Creating Schema

In class this morning we created two schema, one called Venue and one called artist. We created a stored procedure that belonged to the Venue Schema. Then we created a role that assigned the permission exec on the Venue schema. We created a new user and login who belonged to the schema and was a member of the VenueRoll roll. Then we logged in as that user. The new user could only access that one stored procedure. Everything else generated an no-permissions error

Here is the SQL. Some of this we did with the GUI

--Create Schemas
--schemas are about ownership of database objects

Create schema Customer
Create Schema Artist


---Some notes on things for the Venue schema
-- they need to see own information
--Venue information--show information
--artist information
--ticketing information

--Select
--Insert--new shows
--Update--change their own Venue information

--this creates a stored procedure that belongs to the 
--Venue Schema
Go
Create proc Venue.usp_ShowList
@VenueID int --user provided parameter
As
Select VenueName [Venue], 
ArtistName [Artist],
TourName [Tour Name],
ShowDate [Date],
ShowTime [Time],
ShowPosition [Position],
ShowDescription [Description]
From Venue v
Inner join Show s
On v.VenueID=s.VenueID
inner join Artist a
on a.ArtistID=s.ArtistID
Where v.VenueID=@VenueID

--this tests the stored procedure
exec Venue.usp_ShowList 
@VenueID=2

--this creates a role. Roles are collections of 
--permissions
Create Role venueRole

--this grants the permission to execute stored procedures
--that belong to the schema venu
Grant exec  on Schema::Venue to VenueRole

Monday, July 2, 2012

Overview of Indexes

Overview

Indexes are used to speed up searches. Properly used indexes can greatly increase search speeds, but they can also slow updates and deletes. Whenever the values in the tables change the index must be recreated. This can take resources and time.

Kinds of Indexes

Clustered indexes

A clustered index physically organizes the data by the indexed fields. If it is a character type the records with be ordered A-Z, if numeric 123... etc. Consequently, there can only be one clustered index per table. By Default, the primary key is given a clustered index, but you can override this. Below is an example of creating a table with the social security number used as a clustered index and the primary key as a non clustered index:

CREATE TABLE Employee
(
  EmployeeID INT IDENTITYy(1,1),
  SocialSecurityNumber NCHAR(10) NOT NULL UNIQUE,
  EmployeeName NVARCHARr(255),
  HireDate Date
)

ALTER TABLE Employee
ADD CONSTRAINT pk_Employee 
  PRIMARY KEY NONCLUSTERED(EmployeeID)

CREATE CLUSTERED INDEX ix_SocialSecurity 
     ON Employee (SocialSecurityNumber)

Most indexes are non clustered. Non clustered indexes create a separate structure called a Balanced Tree or B-Tree. Below is an image of a B-Tree Cluster:

Below is the code for creating a non clustered index. "NONCLUSTERED" is optional. The second option is for an index with an included column.

CREATE NONCLUSTERED INDEX ix_EmployeeName ON Employee(EmployeeName)
CREATE NONCLUSTERED INDEX ix_NameDate ON Employee (EmployeeName) 
INCLUDE (HireDate)

Filtered Index

You can create an index which filters for certain conditions:

CREATE NONCLUSTERED INDEX ix_HireDate ON Employee (HireDate) 
WHERE Hiredate IS NOT NULL

Disabling, Rebuilding, and Dropping Indexes.

When loading bulk data or testing sometimes it is necessary to disable an index temporarily. When you are done you can rebuild it. You can also drop an index completely

ALTER INDEX ix_EmployeeName ON Employee DISABLE
ALTER INDEX ix_EmployeeName ON Employee REBUILD

DROP INDEX ix_EmployeeName ON Employee

Forcing an Index

Even if you make an index, the database engine may not use it. Generally it is faster to just sort through the records until a certain threshold (several thousand) records are present. For testing purposes you can force SQL Server to use an index with the following syntax

SELECT * FROM Employee 
WHERE EmployeeName='John Smith' 
ITH (NOLOCK, INDEX(ix_EmployeeName))

Computer Management Example (for assignment 2)

here are the classes for the Computer Management example

Here is the Computer.java class

package com.spconger.www;

public class Computer {
 /**
  * This class stores information 
  * about a computer
  * steve conger 7/2/2012
  */
 
 //private fields
 private int computerID;
 private int gigsOfRam;
 private  int hardDiskSize;
 private String brand;
 
 //constructors
 public Computer(int ID, int ram, int hd, String name){
  setComputerID(ID);
  setGigsOfRam(ram);
  setHardDiskSize(hd);
  setBrandName(name);
  
 }
 
 public Computer(int ID){
  setComputerID(ID);
  setGigsOfRam(0);
  setHardDiskSize(0);
  setBrandName("");
  
 }
 
 public Computer(){
  setComputerID(0);
  setGigsOfRam(0);
  setHardDiskSize(0);
  setBrandName("");
 }
 
 // gets and sets
 public void setComputerID(int ID){
  computerID=ID;
 }
 
 public int getComputerID(){
  return computerID;
 }
 
 public void setGigsOfRam(int ram){
  gigsOfRam=ram;
 }
 
 public int getGigsOfRam(){
  return gigsOfRam;
 }
 
 public void setHardDiskSize(int hdisk){
  hardDiskSize=hdisk;
 }
 
 public int getHardDiskSize(){
  return hardDiskSize;
 }
 
 public void setBrandName(String name){
  brand=name;
 }
 
 public String getBrandName(){
  return brand;
 }
 
 // public methods
 
 public String getComputerInfo(){
  return Integer.toString(computerID) + ", " 
   +  brand + ", "
   + Integer.toString(gigsOfRam) + ", " 
   + Integer.toString(hardDiskSize);
 }
 
}

Here is the ComputerManager.java class

package com.spconger.www;

import java.util.ArrayList;

public class ComputerManager {
 /**
  * This class manages computers
  * it uses an ArrayList to collect computer
  * information. The class can return information
  * about a particular computer or return
  * the average size of hard disks
  * the class could do a lot more
  * but this is enough for the example
  * Steve Conger 7/1/2012
  */
 
 //private fields
 private ArrayList<Computer> computers;
 
 //constructor
 public ComputerManager(){
  //initialize the ArrayList
  computers=new ArrayList<Computer>();
 }
 
 //public methods
 public void AddComputer(Computer c){
  //add a computer the the ArrayList
  computers.add(c);
 }
 
 
 public String GetComputerById(int ID){
  //get a computer by its ID
  
  String computerInfo="computer not found";
  
  for(Computer c: computers){
   if(c.getComputerID()==ID){
    
    computerInfo=c.getComputerInfo();
    break;
   }
   
  }

  return computerInfo;
 }
 
 public double getAverageHardDiskSize(){
  //get the average HardDisk size
  //initialize variables
  double average=0;
  int sum=0;
  
  //for each computer in the ArrayList computers
  for(Computer c: computers){
   sum += c.getHardDiskSize();
  }
  //cast sum to double and divide
  //size is the length of the array
  average=(double)sum/computers.size();
  
  return average;
  
 }

}

Here is the Display.java

package com.spconger.www;

import java.text.DecimalFormat;
import java.util.Scanner;

public class Display {
 /**
  * This class handles the input and output 
  * for the Computer and ComputerManager classes
  * Steve Conger 7/2/2012
  */
 //declare class level variables
 ComputerManager cm;
 Scanner scan;
 
 //constructor
 public Display(){
  //initialize classes
  scan = new Scanner(System.in);
  cm= new ComputerManager();
  //call methods
  getInputs();
  showOutputs();
 }

 private void getInputs(){
  int exit=1;
  
  //loop to enter as many computers as desired
  while(exit !=-1){

  //get the values
  System.out.println("Enter Computer ID");
  int id=scan.nextInt();
  
  System.out.println("Hard Disk Size");
  int hd=scan.nextInt();
  
  System.out.println("Enter Ram size");
  int ram=scan.nextInt();
  
  System.out.println("Enter Computer Brand");
  String brandName=scan.next();
  
  //create the computer object and pass it
  //the values through the constructor
  Computer c = new Computer(id, ram, hd, brandName);
  //call the method to add the computer
  //to the ArrayList
  cm.AddComputer(c);
  
  //prompt for exit
  System.out.println("-1 to exit, anything other int to continue");
  exit=scan.nextInt();
  
  }//end while
  
 }//end inputs
 
 private void showOutputs(){
  //get the outputs
  System.out.println("***************************");
  System.out.println("Enter a computer id to get Computer");
  int id = scan.nextInt();
  
  String info=cm.GetComputerById(id);
  
  System.out.println(info);
  
  System.out.println("the average hard disk space is");
  double space = cm.getAverageHardDiskSize();
  
  //Decimal format lets you format the output
  //it doesn't change the actual number
  DecimalFormat df = new DecimalFormat("#,###.##");
  
  System.out.println(df.format(space));
 
 }
}

Here is Program.java

package com.spconger.www;

public class Program {

 /**
  * @param args
  */
 public static void main(String[] args) {
  // TODO Auto-generated method stub
   Display d = new Display();
 }

}