ITC Resources: May 2011

Tuesday, May 31, 2011

Error Trapping

Here is the MagazineData Class

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

///

/// Summary description for MagazineData
///

public class MagazineData
{

private string magName;
private string magType;
private SqlConnection connect;

public MagazineData()
{
InitializeConnection();
}

public MagazineData(string magazineName, string magazineType)
{
magName = magazineName;
magType = magazineType;
InitializeConnection();
}

public DataTable GetMagazineTypes()
{
DataTable table = new DataTable();
string sql = "Select Distinct magType from Magazine";
SqlCommand cmd = new SqlCommand(sql, connect);
SqlDataReader reader = null;

try
{

connect.Open();
reader = cmd.ExecuteReader();
table.Load(reader);
}
catch (SqlException)
{
//we make our own exception and give it a message
Exception ex = new Exception("Database could not be Found");
//throw it back to the form
throw ex;
}
finally
{
//these happen no matter what
if (reader != null)
{
reader.Close();
}
connect.Close();
}

return table;

}

private void InitializeConnection()
{
connect = new SqlConnection
("Data Source=localhost;initial catalog=MagazineSubscription;integrated Security=true");
}
}

Here is Default.aspx

<%@ 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>
<h1>Enter a new Magazine</h1>
<asp:Label ID="Label1" runat="server" Text="Enter New Magazine">
</asp:Label><asp:TextBox ID="txtMagazine" runat="server"></asp:TextBox><br />
<asp:DropDownList ID="ddlMagazineType" runat="server">
</asp:DropDownList><br />
<asp:Button ID="Button1" runat="server" Text="Save" onclick="Button1_Click" />
</div>
</form>
</body>
</html>

Default.aspx.cs

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

public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
MagazineData md = new MagazineData();
try
{
DataTable tbl = md.GetMagazineTypes();
ddlMagazineType.DataSource = tbl;
ddlMagazineType.DataTextField = "MagType";
ddlMagazineType.DataBind();
}
catch (Exception ex)
{
Response.Redirect("Default2.aspx?err=" + ex.Message);
}
}
protected void Button1_Click(object sender, EventArgs e)
{

}
}

Default2.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default2.aspx.cs" Inherits="Default2" %>

<!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>
<h1>Error Page</h1>
<asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>
</div>
</form>
</body>
</html>

Default2.aspx.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class Default2 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
Label1.Text = Request.QueryString["err"];
}
}

Wednesday, May 25, 2011

XML and Logins

Use MagazineSubscription

Select * from Magazine
For xml raw('magazine'), root('Magazines'), Elements

Create xml schema collection MemoCollection
As
'<?xml version="1.0" encoding="utf-8"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" targetNamespace="http://www.spconger.com/memo" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="memo">
<xs:complexType>
<xs:sequence>
<xs:element name="heading">
<xs:complexType>
<xs:sequence>
<xs:element name="to" type="xs:string" />
<xs:element name="from" type="xs:string" />
<xs:element name="about" 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:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>'

Create table Memo
(
memoID int identity(1,1) primary Key,
memoDate datetime,
memocontent xml(dbo.MemoCollection)
)

Insert into Memo (memoDate, memocontent)
Values(GETDATE(),
'<?xml version="1.0" encoding="utf-8"?>
<memo xmlns="http://www.spconger.com/memo">
<heading>

<to>Everyone</to>
<from>me</from>
<about>nothing</about>
</heading>
<body>
<p>We need to do nothing today</p>
<p>The same for tomorrow</p>
</body>
</memo>')

Select * from Memo

--administrative sql
Go
Create schema SalesPerson

Create view SalesPerson.vw_Customers
As
Select * from Customer
go
Create login Sales with password='p@ssww0rd1'

Create user Salesuser for login Sales with default_schema=SalesPerson

Grant select on Schema::SalesPerson to SalesUser

Tuesday, May 24, 2011

LINQ Queries and Writing to Database

First add a LINQ to SQL classes designer to your web site.

Drag the tables you need from the Solution Explorer onto the LINQ Designer

Here is the code for the simple form to add a magazine to the database.

Default.aspx
<%@ 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>
<h1>New Magazine</h1>
<asp:Label ID="Label1" runat="server" Text="Enter Magazine Name"></asp:Label>
<asp:TextBox ID="txtMagazineName" runat="server"></asp:TextBox><br />
<asp:DropDownList ID="ddlMagazineType" runat="server">
</asp:DropDownList><br />
<asp:DropDownList ID="ddlSubscriptionType" runat="server">
</asp:DropDownList><br />
<asp:Label ID="Label2" runat="server" Text="Enter Price"></asp:Label>
<asp:TextBox ID="txtPrice" runat="server"></asp:TextBox><br />
<asp:Button ID="Button1" runat="server" Text="Button" onclick="Button1_Click" />
</div>
</form>
</body>
</html>

Here is the code behind.

Default.aspx.cs

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
{
//instantiate the data context
MagazineDataDataContext dc = new MagazineDataDataContext();

protected void Page_Load(object sender, EventArgs e)
{
//make sure it only happens on the original post of the page
if (!IsPostBack)
{
//call the methods that fill the drop down lists
FillMagazineType();
FillSubscriptionType();
}

}
protected void Button1_Click(object sender, EventArgs e)
{
//instantiate the Magazine Class. It is mapped
//to the magazine table in the database
Magazine mag = new Magazine();

//assign values to the magazine instance
mag.MagName = txtMagazineName.Text;
mag.MagType = ddlMagazineType.SelectedItem.ToString();

//mark it for insert on submit
dc.Magazines.InsertOnSubmit(mag);

MagazineDetail md = new MagazineDetail();
md.Magazine = mag; //tie it to the magazine we just made
md.SubscriptTypeID = int.Parse(ddlSubscriptionType.SelectedValue.ToString());
md.SubscriptionPrice = decimal.Parse(txtPrice.Text);

dc.MagazineDetails.InsertOnSubmit(md);

dc.SubmitChanges(); //submit the rows to the database
}

private void FillMagazineType()
{

//a linq query to get the magazine type
//it uses Distinct because in the magazines table
//several different magazines have the same types
//distinct keeps it from repeating
var mtype = (from t in dc.Magazines
orderby t.MagType
select new { t.MagType }).Distinct();

//bind the result of the query to the control
ddlMagazineType.DataSource = mtype.ToList();
ddlMagazineType.DataTextField = "MagType";
ddlMagazineType.DataBind();
}

private void FillSubscriptionType()
{

//LINQ query gets the fields from SubscriptionType
var subType = from s in dc.SubscriptionTypes
orderby s.SubscriptTypeName
select new { s.SubscriptTypeID, s.SubscriptTypeName };

ddlSubscriptionType.DataSource = subType.ToList();
ddlSubscriptionType.DataTextField = "SubscriptTypeName";
ddlSubscriptionType.DataValueField = "SubscriptTypeID";
ddlSubscriptionType.DataBind();
}
}

Wednesday, May 18, 2011

Triggers

Use MagazineSubscription
Go
--triggers are procedures that are "triggered"
--by an event, such as
--insert update delete

--this trigger allows the deletion
--of customers without subscriptions
--but records the record in a table
--to be looked at later
Alter trigger tr_CustomerDelete
ON Customer
After Delete
As
--check to see if we need to
--make the table to store the deletions
If not exists
(Select [name] From sys.Tables
Where [Name]='Reminders')
Begin
Create Table Reminders
(
CustID int,
CustLastName varchar(30),
CustFirstName Varchar(25),
CustAddress varchar(100),
CustCity varchar(50),
CustState char(2),
CustZipcode char(11),
CustPhone char(10)
)
End

--insert the deleted record into the
--reminders table
--we can get the record from the temp
--table "Deleted" which exists
--for the duration of the transaction
--a millesecond or so
Insert into Reminders (
CustID,
CustLastName,
CustFirstName,
CustAddress,
CustCity,
CustState,
CustZipcode,
CustPhone)
Select CustID,
CustLastName,
CustFirstName,
CustAddress,
CustCity,
CustState,
CustZipcode,
CustPhone
From Deleted

--Just checking

Select * from Subscription

Select * from customer
Delete from Customer
where custID =1

Select * from Reminders

Go
--this stored procedure intercepts
--insertions and checks to see if the
--customer is in Florida
--if he or she is it rejects them with a
--message, otherwise it completes
--the insert
Create Trigger tr_NotFlorida
On Customer
Instead of Insert
As
Declare @state char(2)
--check to see if the state is florida
Select @state=CustState From inserted
if @state='FL'
Begin
PRINT 'We do not serve Florida'
End
Else --if it is not florida
Insert into Customer(CustLastName,
CustFirstName,
CustAddress,
CustCity,
CustState,
CustZipcode,
CustPhone)
Select
CustLastName,
CustFirstName,
CustAddress,
CustCity,
CustState,
CustZipcode,
CustPhone
From Inserted

--test it with florida and
--not florida
Insert into customer(
CustLastName,
CustFirstName,
CustAddress,
CustCity,
CustState,
CustZipcode,
CustPhone)
Values
('Newman',
'laurie',
'something',
'Tampa',
'FL',
'00887',
'6875551234')

********************************************
Here is a modified trigger for Saunatina and anyone else who is interested.
This trigger uses a cursor to loop through the subscriptions for each customer
in the deletion list and if their subscriptions ended more than 3 years ago
writes them to a subscription history table and deletes them from the subscription table. The customer is not deleted, but is marked for possible deletion in the reminders table. I ran it and it seems to work.

A variation that would make this better would be to loop through subscriptions again after the old subscriptions are removed. If the count of subscriptions for a given customer are 0, then also remove that customer and place him or her in a customer history table. I may try to do this in the near future.

(This whole process probably makes more sense as a stored procedure that is run periodically to clean up the database, rather than as a trigger on Delete.)

Alter trigger tr_CustomerDelete
ON Customer
Instead of Delete
As
--check to see if we need to
--make the table to store the deletions
If not exists
(Select [name] From sys.Tables
Where [Name]='Reminders')
Begin
Create Table Reminders
(
CustID int,
CustLastName varchar(30),
CustFirstName Varchar(25),
CustAddress varchar(100),
CustCity varchar(50),
CustState char(2),
CustZipcode char(11),
CustPhone char(10)
)
End

If not exists
(Select [name] From sys.Tables
Where [Name]='SubscriptionHistory')
Begin
Create Table SubscriptionHistory
(
SubscriptionID int,
CustID int,
MagDetID int,
SubscriptionStart DateTime,
SubscriptionEnd DateTime
)
End
--insert the deleted record into the
--reminders table
--we can get the record from the temp
--table "Deleted" which exists
--for the duration of the transaction
--a millesecond or so
Insert into Reminders (
CustID,
CustLastName,
CustFirstName,
CustAddress,
CustCity,
CustState,
CustZipcode,
CustPhone)
Select CustID,
CustLastName,
CustFirstName,
CustAddress,
CustCity,
CustState,
CustZipcode,
CustPhone
From Deleted

--below uses a cursor to loop
--through rows and check on old Subscriptions
declare @CustId int
declare @RowNum int
declare @endDate DateTime
declare CustList cursor for
select CustID from Deleted
OPEN CustList
FETCH NEXT FROM CustList
INTO @CustId
set @RowNum = 0
WHILE @@FETCH_STATUS = 0
BEGIN
set @RowNum = @RowNum + 1
Select @endDate=SubscriptionEnd from Subscription
Where CustID=@CustID
IF (DateDiff(yy,@endDate, GetDate()) > 3)
Begin
Insert into subscriptionHistory(
SubscriptionID,
CustID,
MagDetID,
SubscriptionStart,
SubscriptionEnd)
Select
SubscriptionID,
CustID,
MagDetID,
SubscriptionStart,
SubscriptionEnd
From Subscription
where CustID=@CustID
And SubscriptionEnd = @EndDate

Delete from Subscription where CustID=@CustID
And SubscriptionEnd = @EndDate
End

FETCH NEXT FROM CustList
INTO @CustId
END
CLOSE CustList
DEALLOCATE CustList

Monday, May 16, 2011

Stored Procedures 2

Use MagazineSubscription
Go
/******************
handle a new subscription to a magazine
enter customer info
get what magazine they want
What terms for the subscription
Subscription
*Insert into customer
*Look up magazine detail
*Look up subscription type
*Insert into subscription
*************************/
Alter proc usp_NewMagazineSubscription
--parameters provided by the user
@LastName varchar(30),
@FirstName varchar(25),
@Address varchar(100),
@City varchar(50),
@State char(2),
@Zip char(11),
@Phone char(10),
@magazine varchar(100),
@SubscriptTypeID int
AS
--internal variable
Declare @startDate DateTime
Set @startDate=GETDATE()
Declare @CustID int
Begin tran --begin transaction
Begin Try --begin error catching try
--test to see the customer already exists
if Exists
(Select CustID
From Customer
Where CustLastName= @LastName
And CustFirstName=@FirstName
And CustAddress=@Address
And CustCity=@City)
Begin
--if the customer does exist
--just get the customerID
Select @CustID=CustID
From Customer
Where CustLastName= @LastName
And CustFirstName=@FirstName
And CustAddress=@Address
And CustCity=@City
End
Else
Begin
--if the customer doesn't exist
--insert the new customer
Insert into Customer(
CustLastName,
CustFirstName,
CustAddress,
CustCity,
CustState,
CustZipcode,
CustPhone)
Values(
@LastName,
@FirstName,
@Address,
@City,
@State,
@Zip,
@Phone)

--get the current identity for the customer table
Set @CustID = IDENT_CURRENT('Customer')
End
--declare variables to store the
--magazine id and magazine detail id
Declare @MagID int
Declare @MagDetailID int

--get the magID
Select @MagID = MagID
From Magazine
Where MagName = @magazine
--get the magazine detailID
Select @MagDetailID=MagDetId
From MagazineDetail
Where MagID = @MagID
and SubscriptTypeID=@SubscriptTypeID
--Insert the subscription
--this insert uses the subscriptionEndDate
--function we created earlier
Insert into Subscription(CustID,
MagDetID,
SubscriptionStart,
SubscriptionEnd)
Values(@CustID,
@MagDetailID,
@startDate,
dbo.func_SubscriptionEndDate(@SubscriptTypeID, @startDate))

--if there have been no errors so far
--commit the transaction
commit tran
End try --end the try block

Begin Catch --begin the catch block
--if there are errors
--rollback the transaction
Rollback tran
--print only works in the query editor
print 'The subscription was unsuccessful'
print error_message()
End Catch --end catch end procedure

--try the procedure,
--vary the values to see what happens
--with new customers
--existing customers
--and different magazines and
--subscriptionType values
exec dbo.usp_NewMagazineSubscription
@LastName ='Yellow',
@FirstName ='Joe',
@Address ='100 Elsewhere',
@City= 'Las Vegas',
@State ='Nevada',
@Zip='90000',
@Phone ='4065551234',
@magazine ='XBox Anonymous',
@SubscriptTypeID= 2

--look at the results
Select * from Magazine
Select * from MagazineDetail
Select * From Customer
Select * From Subscription

Monday, May 9, 2011

Functions and stored procedures 1

Use magazineSubscription

--functionname(functionArguments)
Go
Create Function func_Cube
(@number int)
Returns int
As
Begin
Declare @cube int
Set @cube=@number * @number * @number
Return @cube
End

Select SubscriptTypeID, dbo.Func_Cube(SubscriptTypeID)
From SubscriptionType

Go
Create Function func_SubscriptionEndDate
(@Type int, @startDate DateTime)
Returns DateTime
As
Begin
Declare @endDate DateTime
Select @endDate =
Case @Type
When 1 then DateAdd(mm,6,@startdate)
when 2 then DateAdd(yy, 1, @startDate)
when 3 then DateAdd(mm, 3, @startDate)
when 4 then DateAdd(yy, 2, @startDate)
when 5 then DateAdd(yy, 5, @startDate)
when 6 then DateAdd(yy, 3, @startDate)
Else GetDate()
End
Return @EndDate
End

Select dbo.func_SubscriptionEndDate(1, '5/9/2011')

Select * from MagazineDetail
Select * from SubscriptionType
Insert into Subscription(
CustID,
MagDetID,
SubscriptionStart,
SubscriptionEnd)
Values(1, 16, '5/9/2011',
dbo.func_SubscriptionEndDate(6,'5/9/2011'))

Select * from Subscription
Go
Create proc usp_CustomerCity
@City varchar(50)
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
Where CustCity=@City

usp_CustomerCity 'Seattle'

exec usp_CustomerCity
@City='Seattle'
Go
Create proc usp_CustomerCityZip
@City varchar(50),
@Zip char(11)
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
Where CustCity=@City
And CustZipcode=@Zip

usp_CustomerCityZip
@City='Seattle',
@Zip='98190'

Sunday, May 8, 2011

Getting Community Assist database through Visual studio

Open visual studio
From the view menu select Server Explorer
In the server explorer window right click on the data sources icon and select new data source
in the window that gives you the options of which type of data source to choose, select sql server
In the following dialog box type ".\sqlexpress" or your computer name \ sqlexpress for the server and choose "Master" for the database
(If you have installed a full edition of sql server you can use "localhost" for the server name)

click OK
This will add a connection to your server explorer
Then go to the data menu and select "Transact sql editor -> new query connection
It will open a new query window
Click connect

Get the CommunityAssist database script and paste it into the query window

Go to the DATA menu option choose Transact SQL Editor -> execute sql
This will create the database
You will want to create a another new database connection to community assis

Wednesday, May 4, 2011

Views and Indexes

Use MagazineSubscription

--views and indexes
Go
Create view vw_Subscriptions
AS
Select CustLastName [Last Name],
MagName [Magazine],
SubscriptionStart [Start],
SubscriptionEnd [End],
SubscriptTypeName [Subscription Type],
SubscriptionPrice [Price]
From Customer c
Inner Join Subscription s
on c.CustID=s.CustID
Inner Join MagazineDetail md
on md.MagDetID=s.MagDetID
Inner Join Magazine m
on m.MagID=md.MagID
Inner Join SubscriptionType st
on st.SubscriptTypeID=md.SubscriptTypeID
--

--you can update, insert or delete through a view
--if you have not aliased the fields
--if there are no joins
--if there are no calculated fields or functions

Select * from vw_Subscriptions
Order by [Last Name]

Select * from vw_Subscriptions where Magazine='IT Toys'

Create index ix_LastName on Customer(CustLastName)

Create clustered index ix_cLastName on Customer(CustLastName)

Create unique index ix_cLastName on Customer(CustLastName)

Tuesday, May 3, 2011

Link to the magazine.zip File

Here is a link to the magazine.zip file that contains examples of all we have done in class so far

Magazine.zip

ITC Resources