Classes and arrays

Program.java

import java.util.*;
public class Program {

/**
* @param args
*/
public static void main(String[] args) {

TestScores ts = new TestScores(5);
Scanner scan=new Scanner(System.in);
int score=0;
for(int i=0; i<5; i++)
{

System.out.println("Enter a score");
score=scan.nextInt();

ts.Addscore(i, score);
}

int[] scores2=ts.GetScores();

for (int i=0;i<5;i++)
{
System.out.println(scores2[i]);
}

}

}

TestScores.Java

public class TestScores {

private int number;
private int[] scores;

public TestScores(int numberOfScores)
{
number=numberOfScores;
scores=new int[number];
}

public void Addscore(int i, int score)
{
scores[i]=score;
}

public int[] GetScores()
{
return scores;
}

}

Stored Procedures 1

--stored procedures

Use MagazineSubscription
Go

Alter proc usp_GetSubscriptionSummary
@Month int =3
As
Select MONTH(SubscriptionStart) as [Month]
,COUNT (SubscriptionID) as [Subscriptions]
,SUM(SubscriptionPrice) as Total
From Subscription s
Inner Join MagazineDetail md
On md.MagDetID=s.MagDetID
Where MONTH(SubscriptionStart)=@month
Group by MONTH(SubscriptionStart)

go
exec usp_GetSubscriptionSummary 2

usp_GetSubscriptionSummary @Month=3


--stored procedure to enter a new magazine subscription
--new customer --add the customer
--will need the new customer ID
--Get the magazine
--what the subscription terms are for that magazine-magdetID
--insert the subscription information (date function)
--Both inserts should happen or none -- transaction and error trapping
Go
Alter proc usp_NewSubscription
--parameters provided by the user
@LastName varchar(30),
@FirstName varchar(25),
@CustAddress varchar(100),
@CustCity varchar(50),
@CustState Char(2),
@CustZipcode char(11),
@CustPhone char(10),
@magDetID int
As
Declare @StartDate datetime
set @StartDate = GETDATE()
Declare @EndDate Datetime
Begin Tran
Begin try
Insert into Customer (CustLastName,
CustFirstName,
CustAddress,
CustCity,
CustState,
CustZipcode,
CustPhone)
Values(
@LastName,
@FirstName,
@CustAddress,
@CustCity,
@CustState ,
@CustZipcode,
@CustPhone)

Declare @CustID int
Set @CustID=SCOPE_IDENTITY()

Declare @SubscriptionType int
Select @SubscriptionType=SubscriptTypeID
from MagazineDetail
Where MagDetID=@MagDetID


Insert into Subscription(
CustID,
MagDetID,
SubscriptionStart,
SubscriptionEnd)
Values(
@CustID,
@magDetID,
@StartDate,
dbo.func_SubscriptionEndDate(@subscriptionType, @StartDate))
commit tran
End try
Begin Catch
print error_message()
RollBack Tran
End Catch

exec usp_NewSubscription
@LastName ='schlitz',
@FirstName ='Malt',
@CustAddress='100 curbside',
@CustCity='Seattle' ,
@CustState='WA' ,
@CustZipcode='98000',
@CustPhone='2065551232' ,
@magDetID=100

Java classes

Here is the code for the java classes that I have been working on. These classes show how to implement an interface called IActivity. The classes are meant to represent a very simple bank account application.

Here is a picture from Eclipse of the files involved:



Here is the UML for the class diagrams that I did before writing the program There have been some modifications to the classes in the process of writing the code that are not reflected in the diagram. Specifically I modified the interface and changed the transactions method into GetDebitTransactions and GetCreditTransactions. Also there were some modifications of the Display class.



Finally, before I give the code for the actual classes. Here is the console transcript that shows how the program looks when running:

Choose an account:
1. Checking
2. Savings
3. HomeEquity/n0 to quit

1
Enter the beginning balance

1000
Enter Your choice, 0 to exit
********************************
*1. Credit                     *
*2. Debit                      *
*3. Balance                    *
*4. Get Transactions           *
*5. Get Interest               *
********************************
1
Enter the credit amount
250
Enter Your choice, 0 to exit
********************************
*1. Credit                     *
*2. Debit                      *
*3. Balance                    *
*4. Get Transactions           *
*5. Get Interest               *
********************************
1
Enter the credit amount
1200
Enter Your choice, 0 to exit
********************************
*1. Credit                     *
*2. Debit                      *
*3. Balance                    *
*4. Get Transactions           *
*5. Get Interest               *
********************************
2
Enter the debit amount
35.96
Enter Your choice, 0 to exit
********************************
*1. Credit                     *
*2. Debit                      *
*3. Balance                    *
*4. Get Transactions           *
*5. Get Interest               *
********************************
2
Enter the debit amount
813.77
Enter Your choice, 0 to exit
********************************
*1. Credit                     *
*2. Debit                      *
*3. Balance                    *
*4. Get Transactions           *
*5. Get Interest               *
********************************
2
Enter the debit amount
99.50
Enter Your choice, 0 to exit
********************************
*1. Credit                     *
*2. Debit                      *
*3. Balance                    *
*4. Get Transactions           *
*5. Get Interest               *
********************************
4
Credits
250.0
1200.0
Debits
35.96
813.77
99.5
Balance
1500.77
Enter Your choice, 0 to exit
********************************
*1. Credit                     *
*2. Debit                      *
*3. Balance                    *
*4. Get Transactions           *
*5. Get Interest               *
********************************
0

So here are the classes:
IActivity

import java.util.*;
/*******************************************
* this is an interface that contains
* five method definitions one for credits
* one for debits one for getting the balance
* one for getting all debits in a list
* and one for getting all the credits in a list
* An interface forces any class that
* implements it to implement all its methods
* @author Steve
*
********************************************/
public interface IActivity {

public void Credit(double amt);
public void Debit (double amt);
public double GetBalance();
public ArrayList GetDebits();
public ArrayList GetCredits();
}

Program.Java

public class Program {

/**
* This is the starting point of the program
* It contains the main method.
* The only thing the main method will do
* is call the display class.
*/
public static void main(String[] args) {
// TODO Auto-generated method stub
Display dsplay=new Display();
}

}

Display.Java
import java.util.*;
/***********************************
* The purpose of the display class is
* to gather the input needed and
* to display the output. This display
* class is console, but it could be replaced
* by a form or web page without any changes
* being reguired in the other classes.
* @author Steve
*
*/

public class Display {

//we are declaring the interface as a class
//level variable. At runtime we can assign
//one of the classes that implements this
//interface to the variable.
//this is a core principle of Object oriented
//design and actually one of the elements
//of polymorphism: a child can always be substituted
//for the parent
IActivity actv;
int bal=0;
Scanner scan = new Scanner(System.in);

public Display()
{
Menu();
}

public void Menu()
{
int choice=1;

System.out.println("Choose an account:\n1. Checking\n2. Savings\n3. HomeEquity/n0 to quit");
choice = scan.nextInt();
if (choice==1)
{
SetBalance();
actv = new Checking(bal);
}
else if(choice==2)
{
SetBalance();
actv = new Savings(bal);
}
else if(choice==3)
{
//not defined at this time
return;
}
else if(choice==0)
{
return;
}
else
{
System.out.println("not a valid choice");
Menu();
}

while(choice != 0)
{
System.out.println("Enter Your choice, 0 to exit");


System.out.println("********************************");
System.out.println("*1. Credit *");
System.out.println("*2. Debit *");
System.out.println("*3. Balance *");
System.out.println("*4. Get Transactions *");
System.out.println("*5. Get Interest *");
System.out.println("********************************");

choice = scan.nextInt();

//at this point only the checking options are
//implemented
switch (choice)
{
case 0:
break;
case 1:
AddCredit();
break;
case 2:
AddDebit();
break;
case 3:
GetBalance();
break;
case 4:
GetTransactions();
break;
case 5:
GetInterest();
break;
default:
System.out.println("Not a valid choice");
return;


}


}
}



private void SetBalance()
{
System.out.println("Enter the beginning balance");
bal=scan.nextInt();
}

private void AddCredit(){

System.out.println("Enter the credit amount");
double cr = scan.nextDouble();
actv.Credit(cr);
}

private void AddDebit(){

System.out.println("Enter the debit amount");
double db = scan.nextDouble();
actv.Debit(db);
}

private void GetBalance() {
double balance = actv.GetBalance();
System.out.println("the balance is " + balance);
}

private void GetTransactions(){
ArrayList creditList =actv.GetCredits();
ArrayList debitList=actv.GetDebits();

System.out.println("Credits");
for (int i=0;i<creditList.size();i++)
{
System.out.println(creditList.get(i));
}

System.out.println("Debits");
for (int i=0;i<debitList.size();i++)
{
System.out.println(debitList.get(i));
}
System.out.println("Balance");
System.out.println(actv.GetBalance());

}

private void GetInterest()
{
//not defined
}

}

Checking.Java
import java.util.List;
/***************************************
* this class handles checking deposits
* and withdrawels. It implements the interface
* IActivity and so much implement each or
* its methods
*/

import java.util.*;
public class Checking implements IActivity {

double balance;
//the arraylists below are to keep track
//of the activitires
ArrayList Debits = new ArrayList();
ArrayList Credits= new ArrayList();

//a constructor that takes in the beginning
//balance
public Checking(double balance)
{
//the this keyword differentiates
//the balance that belongs to this class
//from the one that comes through
//the contructors arguments
this.balance=balance;
}

@Override
public void Credit(double amt) {
//add the amount to the balance
//and add it to the list of credit
//transactions
balance += amt;
Credits.add(amt);
}

@Override
public void Debit(double amt) {
//Make sure the result won't
//be negative and then subtract
//the amount from the balance
//and add it to the debit list
if(balance >= (balance-amt))
{
balance -= amt;
Debits.add(amt);
}
}

@Override
public double GetBalance() {

return balance;
}

@Override
public ArrayList GetDebits() {
//we are just returning the list
//the display can figure out what to
//do with it
return Debits;
}

@Override
public ArrayList GetCredits() {
// TODO Auto-generated method stub
return Credits;
}



}

Savings.Java

import java.util.*;

/***************************************
* this class handles savings accounts. It is really
* a mirror of the checking, but adds an interest
* calculation. One could argue that there should
* be another level of abstraction, because there
* is so much that is similar between this and checking.
* Either this class should inherit from checking or
* both should inherit from an abstract class called
* Account
* @author Steve
*
*/
public class Savings implements IActivity {

double balance;
ArrayList credits = new ArrayList();
ArrayList debits=new ArrayList();
final double INTERESTRATE=.04;

public Savings(double balance)
{
this.balance=balance;

}


@Override
public void Credit(double amt) {
balance += amt;
credits.add(amt);
}

@Override
public void Debit(double amt) {
balance -= amt;
debits.add(amt);

}

@Override
public double GetBalance() {
// TODO Auto-generated method stub
return balance;
}

@Override
public ArrayList GetDebits() {
// TODO Auto-generated method stub
return debits;
}

@Override
public ArrayList GetCredits() {
// TODO Auto-generated method stub
return credits;
}

public double CalculateInterest()
{
return 0;
}

}

HomeEquity.Java (not implemented yet)

//public class HomeEquity extends Savings{

//public HomeEquity(){}

/********************************
* this class is not implemented at this time.
* It would inherit from savings and add
* only an extra field that accounts for
* the total equity avaialable
*/
//}

LINQ to SQL

To add Linq
make sure you have a connection to the database in the Server Explorer
Right click on the project in the Solution Explorer and select New ITem
In the new Item list select LINQ to SQL
It will open a new Designer window
Drag the classes you want to use from the Server explorer onto the Design window



Here is the code for inserting person

protected void Button1_Click(object sender, EventArgs e)
{


Person peeps = new Person();
peeps.FirstName = txtFirstName.Text;
peeps.LastName = txtLastName.Text;
dc.Persons.InsertOnSubmit(peeps);
try
{

dc.SubmitChanges();

//Session["mypeep"] = peeps;
// Response redirect
}
catch (Exception ex)
{
Response.Write(ex.Message);
}
}

Here is the query for filling the data grid

public partial class _Default : System.Web.UI.Page
{

peopleDataContext dc = new peopleDataContext();

protected void Page_Load(object sender, EventArgs e)
{
var contacts = from p in dc.PersonContacts
where p.ContactTypeKey == 6
orderby p.Person.LastName
select new { p.Person.LastName, p.Person.FirstName, p.ContactInfo };
GridView1.DataSource=contacts.ToList();
GridView1.DataBind();

}

Here is the linq code for the insert of the magazine

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
{
mySubscription s;
protected void Page_Load(object sender, EventArgs e)
{
s = (mySubscription)Session["magOrder"];
txtFirstName.Text = s.Firstname;
txtLastName.Text = s.Lastname;
txtAddress.Text = s.Address;
txtCity.Text = s.City;
txtState.Text = s.State;
txtZip.Text = s.Zip;
txtPhone.Text = s.Phone;
txtMagazine.Text = s.Magazine;
txtLength.Text = s.Subscriptiontype;
}
protected void Button1_click(object sender, EventArgs e)
{
magazineClassesDataContext dc = new magazineClassesDataContext();
Customer cust = new Customer();
cust.CustLastName = txtLastName.Text;
cust.CustFirstName = txtFirstName.Text;
cust.CustAddress = txtAddress.Text;
cust.CustCity = txtCity.Text;
cust.CustState = txtState.Text;
cust.CustZipcode = txtZip.Text;
cust.CustPhone = txtPhone.Text;

dc.Customers.InsertOnSubmit(cust);



Subscription subs = new Subscription();
subs.Customer = cust;
subs.MagDetID = s.MagdetailID;
subs.SubscriptionStart = DateTime.Now;
subs.SubscriptionEnd = null;

dc.Subscriptions.InsertOnSubmit(subs);

dc.SubmitChanges();

Response.Redirect("Default4.aspx?msg=Thank you for your subscription");
}
}

I will post the complete magazineSubscription project soom

Moving Database files

Detatching files
SQL Server files are server centric. In genereal the assumption is that they will not be moved, and in real life they are usually too big to move with any ease. But it is possible to move them by detatching them, copying them and then reattaching them on a different machine.

There are a minimum of two files and you must copy them both. There is a .mdb database file and a .ldf log file.

You must detatch them from the server to copy them.
To detach the files:
1. Make sure all windows connecting to the database you want to detatch are closed. There can be no active connections.
2.Right click on the database in the Object Explorer, select Tasks/ Detatch
3. Accept the options in the dialog box as they are and click ok.
The database files can now be copied.

The files are most likely located at C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data though this could vary depending on your installation.
Copy the files

Re-attatching the files
to reattach the files on the machine where you copied them
1. Right click on databases in the object explorer
2. Select attach files
3. In the dialog box browse until you find the files. It will only show the .mdb file
4. Click OK and Ok

Use the same process on the other machine. You can place the files in any folder on the root drive, but you cannot place them on a user specific location such as the desktop.

Generating an SQL Script
A second way to move or share a database is to generate an SQL script. To do this
Right click on the database and choose tasks. Choose Generate scripts. In the wizard make sure that everything you want scripted is selected. (the data itself is not selected by default.) Generate the script to a new Query window. Save the file. This file can be used to re create the database on any SQL Server.

Scalar Functions One

Here are a couple of examples of scalar functions. Scalar functions only effect one row at time.
All SQL parameters and variables begin with the @ symbol
Parameters are provided by the user (they are the arguments of the function)
Variables are internal to the function and must be declared with the DECLARE key word.
The basic structure of a scalar function is

CREATE FUNCTION <functionName>
(<Parameter1 datatype>, <parameter2 datatype>, . . .
AS
BEGIN
<Function body>
END

Use MagazineSubscription
go

--Basic functions

Create function func_Cube
(@num int)--parameter provided by user
returns int
As
Begin
Declare @cube int --declare variable
Set @cube=@num * @num *@num --assign value
return @cube
End
go
Alter Function func_Cube
(@num decimal(10,2))
returns Decimal(10,2)
As
Begin
Declare @cube decimal(10,2) --declare variable
Set @cube=@num * @num *@num --assign value
return @cube
End
Go

Select SubscriptionPrice, dbo.func_Cube(SubscriptionPrice) as cubed
from MagazineDetail
Go
Select * from SubscriptionType
--get the type
--determine the length in years or months
--calculate the end date
Go
Create Function func_SubscriptionEndDate
(@type int, @startdate datetime)
Returns DateTime
As
Begin
Declare @endDate Datetime
Select @endDate=
Case
When @type=1 then dateadd(mm,6,@startdate)
when @type=2 then dateadd(yy,1,@startDate)
when @type=3 then dateadd(mm, 3, @startDate)
when @type=4 then dateadd(yy, 2, @startDate)
when @type=5 then dateadd(yy,5, @startdate)
when @type=6 then dateadd(yy,3,@startdate)
Else GetDate()
end
Return @endDate

End
Go
Select * from magazineDetail
Insert into subscription(CustID, MagDetID, SubscriptionStart, SubscriptionEnd)
Values(1, 3, GETDATE(),dbo.func_subscriptionEndDate(5,GETDATE()))

Select * from Subscription

Views and Indexes

Use MagazineSubscription
Go

--simple view
Create view vw_Customer
AS
Select CustLastName as [Last Name]
,CustFirstName as [First Name]
,CustPhone as Phone
From Customer
Go

--stored
Select * from vw_Customer
Order by [Last Name]

Select [Last name], [First Name], Phone
from vw_Customer
Where [Last Name]='Terrance'

--in order to update a view
--on table can't have a join
--cannot have any calculated fields
--

Update vw_Customer
Set [Last Name]='Able'
Where [first Name]='Tina'

Drop view vw_customer
go

Alter View vw_Customer
As
Select CustLastName as [Last Name]
,CustFirstName as [First Name]
,CustAddress as [Address]
,CustCity as City
,CustState as [State]
,CustZipcode as [Zip Code]
,CustPhone as Phone
From Customer



Go
Select * from Subscription
Go
Create view manager.vw_SalesSummary
AS
Select MONTH(SubscriptionStart) as [Month]
,COUNT (SubscriptionID) as [Subscriptions]
,SUM(SubscriptionPrice) as Total
From Subscription s
Inner Join MagazineDetail md
On md.MagDetID=s.MagDetID
Group by MONTH(SubscriptionStart)

Go

Drop view vw_SalesSummary

Create Schema manager

Select * from vw_SalesSummary
Where [Month]=3

--Indexes
--three kinds
--clustered
--non clustered
--unique

Create index ix_LastName on Customer(CustLastName)
Create clustered index ix_LastNameclustered on Customer(CustLastName) --throws error
Create unique index ix_uniquePhone on Customer(CustPhone)

Drop index PK_Customer on Customer

Running SQL Scripts in VIsual Studio 2010

Instructions

Open Visual studio.
Go to the data menu
Choose transact SQL
Then choose new connection
Below is a screenshot of the connection dialog, connecting to sqlexpress



Copy and paste the script into the window. (Links to the scripts can be found on the ITC 222 site.



From the Data Menu select Transact SQL/Run Query. The script will install the database objects and the data into sql server

ADO code 1

Here is the code to fill the subscription length drop down box

protected void ddlMagazine_SelectedIndexChanged(object sender, EventArgs e)
{
//connection
//comand object
//DataReader

SqlConnection connect = new SqlConnection(@"Data Source=.\sqlexpress;initial catalog=MagazineSubscription;integrated security=true");

string sql = "Select SubscriptTypeName, MagDetID "
+ "From subscriptionType st "
+ "Inner Join MagazineDetail md "
+ "On st.SubscriptTypeID=md.SubscriptTypeID "
+ "Where MagID = @MagID";

SqlCommand cmd = new SqlCommand(sql, connect);
cmd.Parameters.AddWithValue("@MagID", int.Parse(ddlMagazine.SelectedValue.ToString()));

SqlDataReader reader;

connect.Open();

reader = cmd.ExecuteReader();

ddlSubscriptionLenght.DataSource = reader;
ddlSubscriptionLenght.DataTextField = "SubscriptTypeName";
ddlSubscriptionLenght.DataValueField = "MagDetID";
ddlSubscriptionLenght.DataBind();

reader.Close();
connect.Close();
}

Another Java Class with Inheritance

This uses the boat and motorboat classes from the last post. What I have added is a sailboat class that also inherits from boat and added some code to the Program.java file where the main function resides. I am only going to post the changed code here:

SailBoat.java

public class Sailboat extends Boat {
private double mastHeight;
private int numberOfSails;

public void SetMastHeight(double height)
{
mastHeight=height;
}

public double GetMastHeight()
{
return mastHeight;
}

public void SetNumberOfSales(int sails)
{
numberOfSails=sails;
}

public int GetNumberOfSails()
{
return numberOfSails;
}
}

Program.Java

public class Program {

/**
* @param args
*/
public static void main(String[] args) {
// TODO Auto-generated method stub

MotorBoat myBoat=new MotorBoat();
myBoat.setBoatSize(30);
myBoat.setCost(25000);
myBoat.SetHorsePower(20);
double cpf=myBoat.CostPerFoot();


System.out.println("the boat is " + myBoat.getBoatSize()+ " feet long");
System.out.println("the boat costs " + myBoat.getCost() + " dollars");
System.out.println("the horse power is " + myBoat.horsepower );
System.out.println("the cost per foot is " + cpf);

Sailboat myBoat2 = new Sailboat();
myBoat2.setBoatSize(25);
myBoat2.setCost(45000);
myBoat2.SetMastHeight(30);
myBoat2.SetNumberOfSales(2);

System.out.println("*********************************************");
System.out.println("the boat is " + myBoat2.getBoatSize()+ " feet long");
System.out.println("the boat costs " + myBoat2.getCost() + " dollars");
System.out.println("the mast height is " + myBoat2.GetMastHeight() );
System.out.println("the boat has " + myBoat2.GetNumberOfSails() + " sails");
System.out.println("the cost per foot is " + myBoat2.CostPerFoot());

}

}

Here is the output:

the boat is 30 feet long
the boat costs 25000.0 dollars
the horse power is 20.0
the cost per foot is 833.3333333333334
*********************************************
the boat is 25 feet long
the boat costs 45000.0 dollars
the mast height is 30.0
the boat has 2 sails
the cost per foot is 1800.0

Creating and Altering Tables

Use Master
If exists
(Select [name] from sys.databases
Where [name]='Projects')
Begin
Drop Database Projects
End
Go

Create Database Projects
Go
Use Projects
Go
Create Table Employee
(
EmployeeKey int identity(1000,1),
Constraint PK_Employee Primary Key(EmployeeKey),
EmployeeLastName NVarchar(255) not null unique,
EmployeeFirstName Nvarchar(255) null,
EmployeeSalary Money default 0,
--Constraint chk_Salary check (EmployeeSalary between 0 and 100000)


)
Go
Alter table Employee
Add Constraint chk_Salary check (EmployeeSalary between 0 and 100000)
Go
Create Table Project
(
ProjectKey int identity(1,1) primary key,
ProjectName Nvarchar(255) not null,
ProjectStartDate Date,
ProjectDescription xml
)
Go
Alter Table Project
Add ProjectEndDate Date

--Alter Table Project
--Drop column ProjectEndDate

Go
--Drop table Project

Create Table EmployeeProject
(
EmployeeKey int,
ProjectKey int,
Constraint FK_Employee Foreign Key (EmployeeKey)
References Employee(EmployeeKey) on delete cascade on update cascade,
Constraint FK_Project Foreign Key (ProjectKey)
References Project(ProjectKey) on delete cascade on update cascade,
Constraint PK_EmployeeProject primary key(EmployeeKey, ProjectKey)

)

--default
--unique
--check
--cascade

Insert into Employee( EmployeeLastName, EmployeeFirstName, EmployeeSalary)
Values('Smith','Joe',100000)
Insert into Employee( EmployeeLastName, EmployeeFirstName)
Values('Smythe','Joe')

Select * from Employee

Insert into Project(ProjectName, ProjectStartDate, ProjectDescription, ProjectEndDate)
Values('pitchfork','2/9/2011',
'<projectDescription>
<header>
Project for something or other
</header>
<body>
This project will cost a lot of money and take a lot of time.
</body>
</projectDescription>'
,null)

Select * from Project

Inserts Updates and Deletes

Use MagazineSubscription

--the basic insert statement
--You must insert into every required
--column, columns and values are
--matched by sequence, first to first,
--second to second etc.
Insert into Customer(
CustLastName
, CustFirstName
, CustAddress
, CustCity
, CustState
, CustZipcode
, CustPhone)
Values(
'Custard'
, 'Colonel'
,'Library Study'
,'Seattle'
,'Wa'
,'98000'
,'2065550987')

--these are just for checking on things
Select * from customer
Select * From Magazine
Select * From MagazineDetail
Select * from SubscriptionType
Select * from Subscription

--an insert that uses a subquery and a function
Insert into Subscription (CustID, MagDetID, SubscriptionStart, SubscriptionEnd)
Values((Select MAX(custID) from Customer),2,'2/7/2011', DATEADD(YY,1,'2/7/2011'))

--inserting multiple rows. This syntax only
--became available in SQL Server 2008
--otherwise had to write the complete Insert
--statement for each row
Insert Into Magazine(MagName, MagType)
Values('Trout fishers Anonymous', 'Quarterly'),
('Think Geek', 'Weekly'),
('Amiga User group', 'Annual')

--create a simple table
Create Table CallList
(
LastName nvarchar(255),
FirstName nvarchar(255),
Phone nvarchar(20)
)

--An insert that uses a select for the values
--the columns in the subquery need
--to be compatible in datatype.
--again they are matched by sequence
Insert Into CallList(LastName, FirstName, Phone)
(Select CustLastName, CustFirstName, custPhone From Customer)

Select * from CallList

--manually beginning a transaction creates
--the possibility of an undo
Begin Transaction

--update two columns in customer
--without the where clause every record
--woulc be updated
Update Customer
Set CustFirstName='Colonel',
CustAddress='Kitchen'
Where CustID=16

Select * from Customer

--if there is a mistake you can rollback
--all sql statements since the begin tran
--will be undone
Rollback Transaction

--if there is no error you can
--commit in order to write the changes
--to the database
Commit tran

--a delete statement
Delete from CallList
Where LastName='custard'
And FirstName='Colonel'

--stored procedure to get meta data
--about a table
exec sp_help Customer

--other system views
Select * from sys.Tables
Select * from sys.procedures
Select * from sys.Databases

--fully qualified columns
--running a query with values from a different
--database context
Select CommunityAssist.dbo.Person.LastName, CommunityAssist.dbo.Person.FirstName
From CommunityAssist.dbo.Person

java classes and Inheritance

Here is a simple case of making a class and extending it (inheriting from it.) First We create a simple boat class.

Boat.java

public class Boat {

//private class fields
private int boatSize;
private double cost;

//public gets and sets (accessors and mutators)
//for the fields
public int getBoatSize()
{
return boatSize;
}
public void setBoatSize(int size)
{
boatSize=size;
}

public double getCost()
{
return cost;

}
public void setCost(double price)
{
cost=price;
}

//a simple public method
public double CostPerFoot()
{
return cost/boatSize;
}
}

Here is the class the extends, inherits from Boat. It gets
all the public methods of the parent

MotorBoat.java

public class MotorBoat extends Boat
{
double horsepower;

public double GetHorsePower()
{
return horsepower;
}
public void SetHorsePower(double hp)
{
horsepower=hp;
}
}

Here is the program class that has the main method
and which uses the motorboat class

Program.java

public class Program {

/**
* @param args
*/
public static void main(String[] args) {
// TODO Auto-generated method stub

MotorBoat myBoat=new MotorBoat();
myBoat.setBoatSize(30);
myBoat.setCost(25000);
myBoat.SetHorsePower(20);
double cpf=myBoat.CostPerFoot();

System.out.println("the boat is " + myBoat.getBoatSize()+ " feet long");
System.out.println("the boat costs " + myBoat.getCost() + " dollars");
System.out.println("the horse power is " + myBoat.horsepower );
System.out.println("the cost per foot is" + cpf);

}

}

Here is the output from the program:

the boat is 30 feet long
the boat costs 25000.0 dollars
the horse power is 20.0
the cost per foot is 833.3333333333334

These classes are still very simple. They don't have constructors
or overridable methods. I will do that next.

Animal Shelter Diagram

Database Wizards

How to view the database in Visual Studio

From the view menu select Server Explorer.
At the top of Server Explorer right click on Database Connections
Choose new Database Connection
In the dialog box choose SQL Server Connection
In the next dialog box type localhost for server
in the drop down list for databases select the desired database
The database should show up in the server explorer
Click the little triangle beside it to expand it and veiw the tables and other database objects.

Wizards with databound controls

Drag a data bindable control such as a gridview onto the designer.
Click the little smart tag in the upper right corner
from the resulting menu choose data source/new data source
Choose SQL Database
If you have an existing connection string to the database you want to use, use it,
other wise choose new data string. It opens the dialog box where you enter
localhost and select the database
Once the connection string is established the dialog box opens where you
Select what table you want the data to come from.
You can select all or some of the fields
With the Where button you can set criteria for which rows to show
with the Sort button you can set the sort
With the advance you can set up Insert Update and Delete statements
Click next
Test you query
Finish