How to Fast Start an Embedded QuickTime MOV file

I encountered this problem last week:

After embedding a QuickTime(MOV) file into your web page, it takes an eternity to start. The

reason is because the http request will ask the server to download the whole clip onto the client's

drive, then start the playback.

The solution is simple, and is called FAST START. Fast Start is a QuickTimefeature that enables

users to watch the movie as it's being downloaded (long before the whole movie has been downloaded)

from a standard web server. Fast Start works well for short-form movies where file size is limited.

It ensures high-quality playback regardless of the users' Internet connection speeds, though those

with slower connections will wait longer before media starts to play (
href="http://support.apple.com/kb/HT2438" >Apple QuickTime, 2009).

Check the instructions on the QuickTime website here. It also explains how to optimize your video for online streaming.

JQuery Lightbox - PrettyPhoto

Stumbled across one of the best, in my humble opinion, JQuery Lightbox that provides a cool lightbox effect while displaying your galleries and movie clips as well.
Worth checking it out:
http://www.no-margin-for-errors.com/projects/prettyphoto-jquery-lightbox-clone/

Merry Christmas all, and a Happy New Year 2010!

how to apply the themes

Hi everyone,
I am now following ALAN's project files to catch up everything what I missed and sometimes I am struggling to understand ... I forgot many things. If you have time, can you help me?

< Question >
I can not find out how to apply the themes (including image,css,skin) to web pages because the theme attribute of the @Page directive (eg. <%@ Page Theme="SkinFile" %>) is not set in the all Web Pages of ALAN's project.
Do you remember how & where Alan applied that?

---------------------------------------------

I got the answer from the below page after I posted!
http://www.dotnettreats.com/samplethemes/Default.aspx
He uses "Web.config" to apply the theme for the Web site. :D

C# Unit Testing - Divide function div()

using System;
using System.Text;
using System.Collections.Generic;
using System.Linq;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using MyCal;

namespace CalcTestProject
{
    /// <summary>
    /// Summary description for UnitTest1
    /// </summary>
    [TestClass]
    public class DivTest
    {      

        #region Additional test attributes
        //
        // You can use the following additional attributes as you write your tests:
        //
        // Use ClassInitialize to run code before running the first test in the class
        // [ClassInitialize()]
        // public static void MyClassInitialize(TestContext testContext) { }
        //
        // Use ClassCleanup to run code after all tests in a class have run
        // [ClassCleanup()]
        // public static void MyClassCleanup() { }
        //
        // Use TestInitialize to run code before running each test 
        // [TestInitialize()]
        // public void MyTestInitialize() { }
        //
        // Use TestCleanup to run code after each test has run
        // [TestCleanup()]
        // public void MyTestCleanup() { }
        //
        #endregion

        [TestMethod]
        public void Test_ValidPositive()
        {
            //call method defined in the MyMath class
            Assert.AreEqual(MyMath.Div("20","5"),"4.00");
            Assert.AreEqual(MyMath.Div("25", "2"), "12.50");
            Assert.AreEqual(MyMath.Div("20", "3"), "6.67");
            Assert.AreEqual(MyMath.Div("2147483647", "2"), "1073741823.50");
        }

        [TestMethod]
        public void Test_ValidNegative()
        {
            Assert.AreEqual(MyMath.Div("-40", "2"), "-20.00");
            Assert.AreEqual(MyMath.Div("50", "-2"), "-25.00");
            Assert.AreEqual(MyMath.Div("-4", "-2"), "2.00");
            Assert.AreEqual(MyMath.Div("-2147483648", "-4"), "536870912.00");
        }

        [TestMethod]
        public void Test_ValidZeroes()
        {
            Assert.AreEqual(MyMath.Div("0", "2"), "0.00");
            Assert.AreEqual(MyMath.Div("2", "0"), "ERROR");
            Assert.AreEqual(MyMath.Div("0", "0"), "ERROR");            
        }

        [TestMethod]
        [ExpectedException (typeof (FormatException))]
        public void Test_CharFirstInput()
        {
            MyMath.Div("2aa", "4");
           
        }

        [TestMethod]
        [ExpectedException(typeof(FormatException))]
        public void Test_NotIntInput()
        {
            MyMath.Div("5.5", "2");
            MyMath.Div("4", "2.2");
        }

        [TestMethod]
        [ExpectedException(typeof(FormatException))]
        public void Test_NotValidInput()
        {
            MyMath.Div("", "2");
            MyMath.Div("22", "");
        }

        [TestMethod]
        [ExpectedException(typeof(OverflowException))]
        public void Test_RangeError()
        {
            MyMath.Div("2147483648", "2");
            MyMath.Div("22", "-4147483467");
        }

    }
}

SQL - My Database

I've included the queries to create the tables for an online TShirt Order Management System (My project).
I have also created a couple of views and stored procedures to add, delete and update tables. Feel free to use and modify the code below for your own database :)

TABLE CREATION

--
--***        CREATE TABLES
--***           -------------------------------------------------------------------------------
--***        Customers, Orders, Suppliers, TShirts, OrderDetails
--***
--******************************************************************************
--*****        AUTHOR: Denis K        DATE: 05/10/2009
--******************************************************************************
CREATE TABLE Customers
(
CustomerID INT IDENTITY(1000,1) PRIMARY KEY,
FirstName VARCHAR(20) NOT NULL,
LastName VARCHAR(20) NOT NULL,
Address VARCHAR(40) NOT NULL,
City VARCHAR(20) NOT NULL,
Country VARCHAR(20) NOT NULL,
DOB DATETIME NOT NULL,
Mobile NCHAR(10),
Status INT DEFAULT 0 
)


CREATE TABLE Orders
(
OrderID INT IDENTITY(1,1) PRIMARY KEY,
OrderDate DATETIME,
CustomerID INT NOT NULL,
FOREIGN KEY (CustomerID) REFERENCES Customers (CustomerID)
)

CREATE TABLE Suppliers
(
SupplierID INT IDENTITY(1000,1) PRIMARY KEY,
CompanyName VARCHAR(20) NOT NULL,
ContactName VARCHAR(20) NOT NULL,
Address VARCHAR(40) NOT NULL,
City VARCHAR(20) NOT NULL,
Phone VARCHAR(15) NOT NULL,
Fax VARCHAR(15) NOT NULL,
HomePage VARCHAR(30),
Status INT DEFAULT 0
)

CREATE TABLE TShirts
(
TShirtID INT IDENTITY(50000,1) PRIMARY KEY,
ProductName VARCHAR(20) NOT NULL,
Size VARCHAR(20) NOT NULL,
Color VARCHAR(20) NOT NULL,
UnitPrice MONEY,
UnitsInStock INT,
Discontinued INT DEFAULT 0,
Picture VARCHAR(40),
SupplierID INT NOT NULL, 
FOREIGN KEY (SupplierID) REFERENCES Suppliers (SupplierID)
)

CREATE TABLE OrderDetails
(
OrderDetailsID INT IDENTITY(1,1) PRIMARY KEY,
Quantity INT NOT NULL,
Discount MONEY,
OrderID INT NOT NULL, 
TShirtID INT NOT NULL,
FOREIGN KEY (OrderID) REFERENCES Orders (OrderID),
FOREIGN KEY (TShirtID) REFERENCES TShirts (TShirtID)
)

VIEWS AND STORED PROCEDURES

********************************************************************************
CUSTOMERS
********************************************************************************

CREATE VIEW view_DisplayAllCustomers AS 
SELECT     CustomerID, FirstName, LastName, Address, City, Country, DOB, Mobile, Status
FROM         Customers


CREATE VIEW view_DisplayActiveCustomers AS 
SELECT     CustomerID, FirstName, LastName, Address, City, Country, DOB, Mobile, Status
FROM         Customers WHERE Status = 0


CREATE VIEW view_DisplayInactiveCustomers AS 
SELECT     CustomerID, FirstName, LastName, Address, City, Country, DOB, Mobile, Status
FROM         Customers WHERE Status = 1



    
    (
    @FirstName VARCHAR(20),
    @LastName VARCHAR(20),
    @Address VARCHAR(40),
    @City VARCHAR(20),
    @Country VARCHAR(20),
    @DOB DATETIME,
    @Mobile NCHAR(10),
    @Status INT
    )
    
AS
    INSERT INTO Customers (FirstName, LastName, Address, City, Country, DOB, Mobile, Status)
    VALUES (@FirstName, @LastName, @Address, @City, @Country, @DOB, @Mobile, @Status)
    RETURN


CREATE PROCEDURE dbo.spUpdateCustomer
    (
    @CustomerID INT,
    @FirstName VARCHAR(20),
    @LastName VARCHAR(20),
    @Address VARCHAR(40),
    @City VARCHAR(20),
    @Country VARCHAR(20),
    @DOB DATETIME,
    @Mobile NCHAR(10),
    @Status INT
    )
AS
    UPDATE Customers
        SET FirstName = @FirstName,
        LastName = @LastName,
        Address = @Address,
        City = @City,
        Country = @Country,
        DOB = @DOB,
        Mobile = @Mobile,
        Status = @Status
    WHERE CustomerID = @CustomerID
    RETURN


CREATE PROCEDURE dbo.spDeleteCustomer
    (
    @CustomerID INT
    )
    
AS
    DELETE FROM Customers WHERE CustomerID = @CustomerID
    RETURN


CREATE PROCEDURE dbo.spDeactivateCustomer
    
    (
    @CustomerID INT
    )
    
AS
    UPDATE Customers SET Status = 1
    WHERE CustomerID = @CustomerID
    RETURN


CREATE PROCEDURE dbo.spDeactivateCustomer
    
    (
    @CustomerID INT
    )
    
AS
    UPDATE Customers SET Status = 1
    WHERE CustomerID = @CustomerID
    RETURN


CREATE PROCEDURE dbo.spActivateCustomer
    (
    @CustomerID INT
    )
    
AS
    UPDATE Customers SET Status = 0
    WHERE CustomerID = @CustomerID
    RETURN




********************************************************************************
SUPPLIERS
********************************************************************************

CREATE VIEW view_DisplayAllSuppliers AS
SELECT     *
FROM         Suppliers


CREATE VIEW view_DisplayActiveSuppliers AS
SELECT     *
FROM         Suppliers
WHERE Status = 0


CREATE VIEW view_DisplayInactiveSuppliers AS
SELECT     *
FROM         Suppliers
WHERE Status = 1


CREATE PROCEDURE dbo.spAddNewSupplier
    
    (
    @CompanyName VARCHAR(20),
    @ContactName VARCHAR(20),
    @Address VARCHAR(40),
    @City VARCHAR(20),
    @Phone VARCHAR(15),
    @Fax VARCHAR(15),
    @HomePage VARCHAR(30),
    @Status INT
    )
    
AS
    INSERT INTO Suppliers (CompanyName, ContactName, Address, City, Phone,
    Fax, HomePage, Status)
    VALUES
    (@CompanyName, @ContactName, @Address, @City, @Phone,
    @Fax, @HomePage, @Status)
    RETURN


CREATE PROCEDURE dbo.spUpdateSupplier
    
    (
    @SupplierID INT,
    @CompanyName VARCHAR(20),
    @ContactName VARCHAR(20),
    @Address VARCHAR(40),
    @City VARCHAR(20),
    @Phone VARCHAR(15),
    @Fax VARCHAR(15),
    @HomePage VARCHAR(30),
    @Status INT
    )
    
AS
    UPDATE Suppliers SET CompanyName = @CompanyName,
                    ContactName = @ContactName,
                    Address = @Address,
                    City = @City,
                    Phone = @Phone,
                    Fax = @Fax,
                    HomePage = @HomePage,
                    Status = @Status
                    WHERE SupplierID = @SupplierID
    RETURN



CREATE PROCEDURE dbo.spDeleteSupplier
    
    (
    @SupplierID INT
    )
    
AS
    DELETE FROM Suppliers WHERE SupplierID = @SupplierID
    RETURN


CREATE PROCEDURE dbo.spDeactivateSupplier
    (
    @SupplierID INT
    )
    
AS
    UPDATE Suppliers SET Status = 1
    WHERE SupplierID = @SupplierID
    RETURN


CREATE PROCEDURE dbo.spActivateSupplier
    (
    @SupplierID INT
    )
    
AS
    UPDATE Suppliers SET Status = 0
    WHERE SupplierID = @SupplierID
    RETURN


********************************************************************************
T-SHIRTS
********************************************************************************
CREATE VIEW view_DisplayAllTShirts AS
SELECT     *
FROM         TShirts


CREATE VIEW view_DisplayActiveTShirts AS
SELECT     *
FROM         TShirts
WHERE Discontinued = 0


CREATE VIEW view_DisplayDiscontinuedTShirts AS
SELECT     *
FROM         TShirts
WHERE Discontinued = 1


CREATE PROCEDURE dbo.spAddNewTShirt
    
    (
    @ProductName VARCHAR(20),
    @Size VARCHAR(20),
    @Color VARCHAR(20),
    @UnitPrice MONEY,
    @UnitsInStock INT,
    @Discontinued INT,
    @Picture VARCHAR(40),
    @SupplierID INT 
    )
    
AS
    INSERT INTO TShirts (ProductName, Size,Color, UnitPrice, UnitsInStock,
    Discontinued, Picture, SupplierID)
    VALUES
    (@ProductName, @Size, @Color, @UnitPrice, @UnitsInStock,
    @Discontinued, @Picture, @SupplierID)
    RETURN


CREATE PROCEDURE dbo.spUpdateTShirt
    (
    @TShirtID INT,
    @ProductName VARCHAR(20),
    @Size VARCHAR(20),
    @Color VARCHAR(20),
    @UnitPrice MONEY,
    @UnitsInStock INT,
    @Discontinued INT,
    @Picture VARCHAR(40),
    @SupplierID INT)
AS
    UPDATE TShirt SET ProductName = @ProductName,
    Size = @Size, Color = @Color, UnitPrice = @UnitPrice,
    UnitsInStock = @UnitsInStock, Discontinued = @Discontinued,
    Picture = @Picture, SupplierID = @SupplierID
    WHERE TShirtID = @TShirtID
    RETURN


CREATE PROCEDURE dbo.spDeleteTShirt
    
    (
    @TShirtID INT
    )
    
AS
    DELETE FROM TShirts WHERE TShirtID = @TShirtID
    RETURN



CREATE PROCEDURE dbo.spDeactivateTShirt
    (
    @TShirtID INT
    )
    
AS
    UPDATE TShirts SET Discontinued = 1
    WHERE TShirtID = @TShirtID
    RETURN


CREATE PROCEDURE dbo.spActivateTShirt
    (
    @TShirtID INT
    )
    
AS
    UPDATE TShirts SET Discontinued = 0
    WHERE TShirtID = @TShirtID
    RETURN

ASP.NET - Session time

To record the session time, two variables are initialised in the global application class Global.asax to hold the name of user and time of session.

<%@ Application Language="C#" %>

<script runat="server">

    void Application_Start(object sender, EventArgs e) 
    {
        // Code that runs on application startup

    }
    
    void Application_End(object sender, EventArgs e) 
    {
        //  Code that runs on application shutdown

    }
        
    void Application_Error(object sender, EventArgs e) 
    { 
        // Code that runs when an unhandled error occurs

    }

    void Session_Start(object sender, EventArgs e) 
    {
        // Code that runs when a new session is started
        Session.Add("Name", null);
        Session.Add("Time", null);

    }

    void Session_End(object sender, EventArgs e) 
    {
        // Code that runs when a session ends. 
        // Note: The Session_End event is raised only when the sessionstate mode
        // is set to InProc in the Web.config file. If session mode is set to StateServer 
        // or SQLServer, the event is not raised.

    }
       
</script>

Now, in the first page (Default.aspx) which holds the textbox for the user name and the login button, the following code is written for the Click event of the login button. The variable Time in the Session object will store the date and time of the click.

protected void btnLogin_Click(object sender, EventArgs e)
{
    Session["Name"] = txtName.Text;
    Session["Time"] = DateTime.Now;
    Response.Redirect("~/Default2.aspx");

}

In Default2.aspx, the name is displayed in the Form_Load event.
The button for logout will display the Session time.

protected void Page_Load(object sender, EventArgs e)
{
    lblInfo.Text = Session["Name"].ToString();
}
protected void btnLogout_Click(object sender, EventArgs e)
{

    lblInfo.Text = (DateTime.Now.Subtract(DateTime.Parse( Session["Time"].ToString()) )).Seconds.ToString();
}

Note: This code was written in class and has limitations. It will only display seconds. That is, 1 minute and 5 seconds will display as 5 seconds. I believe there are many ways to solve this problem. Please feel free to share your code.

SQL Practice In class - My SQL solution

Q1

CREATE TABLE Employee
(
FirstName VARCHAR(30),
LastName VARCHAR(30),
email VARCHAR(30),
DOB DATETIME,
Phone VARCHAR(12)
)

INSERT INTO Employee
VALUES
('John', 'Smith', 'John.Smith@yahoo.com', '2/4/1968','626 222-222')

INSERT INTO Employee
                      (FirstName, LastName, email, DOB, Phone)
VALUES     ('Steven', 'Goldfish', 'goldfish@fishhere.net', '4/4/1974', '323 455-4545')

INSERT INTO Employee
                      (FirstName, LastName, email, DOB, Phone)
VALUES     ('Paula', 'Brown', 'pb@herowndomain.org', '5/24/1978', '416 323-3232')

INSERT INTO Employee
                      (FirstName, LastName, email, DOB, Phone)
VALUES     ('James', 'Smith', 'jim@supergig.co.uk', '10/20/1980', '416 323-8888')

Q2

SELECT     FirstName, LastName, email, DOB, Phone
FROM         Employee
WHERE     (LastName LIKE 'SMITH')

Q3

SELECT     COUNT(*) AS Num_of_Emp_Like_SMITH
FROM         Employee
WHERE     (LastName LIKE 'SMITH')

Q4

SELECT     LastName, COUNT(*) AS NumberOfEmp
FROM         Employee
GROUP BY LastName
ORDER BY LastName DESC

Q5

SELECT     FirstName, LastName, email, DOB, Phone
FROM         Employee
WHERE     (DOB >= '01/01/1970')

Q6

SELECT     FirstName, LastName, email, DOB, Phone
FROM         Employee
WHERE     (Phone LIKE '416%')

Q7

SELECT     FirstName, LastName, email, DOB, Phone
FROM         Employee
WHERE     (email LIKE '%.%@%.%')

Q8

UPDATE    Employee
SET              DOB = '05/10/1974'
WHERE     (LastName = 'Goldfish') AND (FirstName = 'Steven')

Q9

SELECT     FirstName, LastName, email, DOB, Phone
FROM         Employee
ORDER BY DOB DESC

Q10

SELECT     FirstName, LastName, email, DOB, Phone
FROM         Employee
WHERE     (FirstName LIKE '_____%')

Q11

ALTER TABLE Employee ADD Id INT IDENTITY
CONSTRAINT pk_ID PRIMARY KEY(Id)

Q12

SELECT     FirstName, LastName, email, DOB, Phone, Id
FROM         Employee
WHERE     (DATEPART(m, DOB) = DATEPART(m, GETDATE()))

Q13

create table EmployeeHours
(
empFName Varchar(30),
empLName Varchar(30),
Date DATETIME,
Hours 



insert into employeehours
VALUES
('John', 'Smith', '5/6/2004', 8)

insert into employeehours
VALUES
('John', 'Smith', '5/7/2004', 9)

insert into employeehours
VALUES
('Steven', 'Goldfish', '5/7/2004', 8)

insert into employeehours
VALUES
('James', 'Smith', '5/7/2004', 9)

insert into employeehours
VALUES
('John', 'Smith', '5/8/2004', 8)

insert into employeehours
VALUES
('James', 'Smith', '5/8/2004', 8)

Q13


Alter table EmployeeHours
add EmployeeID INT
CONSTRAINT fk_ID Foreign Key (EmployeeID) References Employee(id)

Q14

CREATE PROCEDURE UpdateEmpId
    
AS
    UPDATE    EmployeeHours
    SET       EmployeeId =
             (SELECT     Id
              FROM       Employee
WHERE      (EmployeeHours.EmpFName = FirstName) AND (EmployeeHours.EmpLName = LastName))

Web design - Professional portfolio updates

The weekend did not rhyme with good rest and leisure...
I have added 2 more web projects to my professional portfolio.

Family Lawyers website:
(Thanks to Paul for the help)




Arthur Chen Photography website:




For more info, you can click on my professional portfolio link.

PART 1 & 2 - SQL User Defined Functions - Homework solutions

Question 1
Create a function called fnGetTimeOnly to return the time part (hh:mm) of a DateTime value

ALTER FUNCTION dbo.fnGetTimeOnly(@datefield DATETIME)
RETURNS varchar(5)
AS
    BEGIN
        RETURN CONVERT(VARCHAR(2),
        DATEPART(hh,@datefield)) + ':' + CONVERT(VARCHAR(2),DATEPART(mi,@datefield))
    END

----------------------------------------------------

SELECT     dbo.fnGetTimeOnly(GETDATE()) AS Expr1

Create a function called fnGetDateOnly to return the date part (dd/mm/yyyy) of a DateTime value

ALTER FUNCTION dbo.fnGetDateOnly
    (@datefield DATETIME)
RETURNS VARCHAR(10)
AS
    BEGIN
        RETURN CONVERT(VARCHAR(2),DATEPART(dd,@datefield))+ '/' +
        CONVERT(VARCHAR(2),DATEPART(mm,@datefield))+ '/' +
        CONVERT(VARCHAR(4),DATEPART(yyyy,@datefield))
    END

---------------------------------------------------------

SELECT     dbo.fnGetDateOnly(NOW()) AS Expr1

Create a stored procedure called OrderDateTimeSpecific to return all order dates by splitting the date part and the time part into different columns. Use the functions created above in the stored procedure.

ALTER PROCEDURE dbo.OrderDateTimeSpecific

AS
        SELECT dbo.fnGetDateOnly(OrderDate) AS OrderDate, 
        dbo.fnGetTimeOnly(OrderDate) AS OrderTime 
        FROM Orders
    RETURN

Question 2
Create a function called fnContactInfo that returns a table containing all employees full name and their contact number

ALTER FUNCTION dbo.fnContactInfo()
RETURNS @contactInfoTable TABLE (fullname varchar(30), contactnumber varchar(24))
AS
    BEGIN
        INSERT INTO @contactInfoTable
           SELECT FirstName + ' ' + LastName, HomePhone FROM Employees
    RETURN
    END

Create a query to return all values from the fnContactInfo function

SELECT     fullname, contactnumber
FROM         dbo.fnContactInfo() AS fnContactInfo_1

Question 3
Create a function called fnLastDay to get the last day in a month of a given date.

ALTER FUNCTION dbo.fnLastDay(@givendate DATETIME)
RETURNS VARCHAR(30)
AS
    BEGIN
        DECLARE @dayindex INT
        DECLARE @lastday VARCHAR(30)
        DECLARE @lastdate DATETIME
        
        SET @lastdate = DATEADD(day, - 1, DATEADD(month, DATEDIFF(month, 0, @givendate) + 1, 0))
        SET @dayindex = CONVERT(INT,DATEPART(weekday,@lastdate))
        
        SELECT @lastday =
        CASE @dayindex
            WHEN 1 
                THEN 'Family Day Sunday'
            WHEN 2 
                THEN 'Back to work Day Monday'
            WHEN 3 
                THEN 'Movie night Tuesday'
            WHEN 4
                THEN 'Borrow money day Wednesday'
            WHEN 5 
                THEN 'Shopping spree Thursday'
            WHEN 6
                THEN 'Binge drinking night Friday'
            WHEN 7
                THEN 'Activities day Saturday'
        END
        
        
        RETURN @lastday
    END

Create a query to return all employees and their pay day last month, this month, and next month. The pay day is always the last day of the month.

SELECT     LastName, FirstName, 
dbo.fnLastDay(DATEADD(month, - 1, GETDATE())) AS PreviousMonthPayDay, 
dbo.fnLastDay(GETDATE()) AS ThisMonthPayDay, 
dbo.fnLastDay(DATEADD(month, 1, GETDATE())) AS NextMonthPayDay
FROM         Employees

Question 4
Create a function called fnRoundCurrency to format the currency value into the correct monetary format. For e.g. 39.07 should be rounded to 39.10.

ALTER FUNCTION dbo.fnRoundCurrency(@currency MONEY)

RETURNS DECIMAL(10,2)
AS
BEGIN
    RETURN CAST ((ROUND((@currency * 2),1) /10) *5 AS DECIMAL(10,2))
END

Modify the stored procedure created last week and call this function to format the new price of the first ten products into the right format.

ALTER PROCEDURE dbo.spShowCorrectPrice
AS
    SELECT TOP 10 *,dbo.fnRoundCurrency(UnitPrice) as RoundedPrice FROM Products
    
    RETURN

PART II
Create a table called Books in Northwind database containing BookID, ISBN, Publisher, Author, Year, Country, Category, Description.

CREATE TABLE Books
(
BookID INT,
ISBN VARCHAR(13),
Publisher VARCHAR(30),
Author VARCHAR(30),
Year INT,
Country VARCHAR(20),
Category VARCHAR(30),
Description VARCHAR(30),
CONSTRAINT pk_BookID PRIMARY KEY (BookID)
)

Question 1: FUNCTIONS TO CHECK CONSTRAINTS IN A TABLE DEFINITION
Create a function called fnValidISBN to check the value entered into ISBN field in the Books table. The following requirements are to check for valid ISBN:
ISBN numbers can contain 10 or 13 digits number.
Assuming that the company only accepts English-language publisher books, make sure that the ISBN follows the systematic pattern.
To check if the numbers entered are valid or not, use the formula and pattern in this webpage: http://en.wikipedia.org/wiki/International_Standard_Book_Number

ALTER FUNCTION dbo.fnValidISBN 
    (@isbn VARCHAR(13))
RETURNS INT
AS
    BEGIN
        DECLARE @result INT
        DECLARE @isbn_ten INT
        DECLARE @isbn_thirteen INT
        DECLARE @isbn_sum INT
        DECLARE @startpos INT
        DECLARE @isbnlen INT
        
        
        SET @isbn_ten = 9
        SET @isbn_thirteen = 12
        SET @isbn_sum = 0
        SET @startpos = 1
        
        
        SET @isbnlen =  LEN(@isbn)
        
        IF (@isbnlen = @isbn_ten) 
        BEGIN
                WHILE (@startpos<= @isbn_ten+1)
                BEGIN
                    SET @isbn_sum = @isbn_sum + (@isbn_ten * SUBSTRING(@isbn, @startpos, 1))
                    SET @startpos = @startpos + 1
                END
                
                IF (11-@isbn_sum%11) = SUBSTRING(@isbn,10,1)
                    SET @result = 0    --Success
                ELSE 
                    SET @result= 1    --Failure
        END
        
        ELSE IF (@isbnlen = @isbn_thirteen+1)
        BEGIN
                WHILE (@startpos <= @isbn_thirteen)
                BEGIN
                    IF @startpos % 2 <> 0
                        BEGIN
                            SET @isbn_sum = @isbn_sum + (1 * SUBSTRING(@isbn, @startpos, 1))
                            SET @startpos = @startpos + 1
                        END
                    ELSE
                        BEGIN
                            SET @isbn_sum = @isbn_sum + (3 * SUBSTRING(@isbn, @startpos, 1))
                            SET @startpos = @startpos + 1
                        END
                END
                
                
                IF (10 - @isbn_sum % 10) = SUBSTRING(@isbn,13,1)
                    SET @result = 0    --Success
                ELSE 
                    SET @result= 1    --Failure
        END
        
        ELSE SET @result = 1
        
        RETURN @result
    END


---------------------------------------------

SELECT     dbo.fnValidISBN(9780306406156) AS Expr1

Question 3....

i did some browsing on the net for how to do this k so im not that good... but i do understand it now....this is a way of finding the last day of a month by:

1 - taking the date input "(@pInputDate DATETIME)"
2 - declare a date variable which you will use to output "SET @vOutputDate DATETIME"
3 - cast the year and month of the inputdate into varchar and then + /01, then assign it as the new @vOutputDate....(in other words if ur input was 23/09/2009, it will now be 01/09/2009 which is the FIRST day of the month)
4 - now add 1 month then minus 1 day (this will make it LAST day of the month)
SET @vOutputDate = DATEADD(DD, -1, DATEADD(M, 1, @vOutputDate))
5 - now return the output
6 - now thank louie for pasting the whole thing so u can paste it aswell LOL


ALTER FUNCTION dbo.fnGetLastDayOfMonth (@pInputDate DATETIME)
RETURNS DATETIME
BEGIN

DECLARE @vOutputDate DATETIME

SET @vOutputDate = CAST(YEAR(@pInputDate) AS VARCHAR(4)) + '/' +
CAST(MONTH(@pInputDate) AS VARCHAR(2)) + '/01'
SET @vOutputDate = DATEADD(DD, -1, DATEADD(M, 1, @vOutputDate))

RETURN @vOutputDate

END


now this is the query for 2nd part of question 3....cant b bothered explaining it ...u figure it out!

SELECT FirstName + LastName AS Expr1, dbo.fnGetLastDayOfMonth(GETDATE()) AS thismonthpayday, dbo.fnGetLastDayOfMonth(DATEADD(mm, - 1, GETDATE()))
AS lastmonthpayday, dbo.fnGetLastDayOfMonth(DATEADD(mm, 1, GETDATE())) AS nextmonthpayday
FROM Employees

...oh yeh and the query part

SELECT fullname, contact
FROM dbo.fnContactInfo() AS fnContactInfo_1

Question 2

ALTER FUNCTION dbo.fnContactInfo()

RETURNS @contactinfo TABLE
(fullname nvarchar(30),contact nvarchar(20))
AS
BEGIN
INSERT INTO @contactinfo
SELECT firstname + ' ' + Lastname, homephone FROM dbo.Employees
RETURN
END

3rd part of question 1 (i think its right lol)

ALTER PROCEDURE dbo.OrderDateTimeSpecific

AS
select
dbo.fngetdateonly(orderdate) as orderdate,
dbo.fngettimeonly(orderdate) as ordertime
from
orders

RETURN

WHILE LOOP Stored Procedure - Class example

Add 5 new customers and date

ALTER PROCEDURE spAddCustomerIDtoOrdersFiveTimes
    @CustID NCHAR(5)
AS
    DECLARE @counter INT
    SET @counter = 0
    
    WHILE @counter < 5
    BEGIN
        INSERT INTO Orders (CustomerID, OrderDate)
        VALUES (@CustID, DATEADD(day,@counter,GETDATE()))
        
        SET @counter = @counter +1
    END
    
    RETURN

Calculate to new salary average

ALTER PROCEDURE spIncreaseSalUntilTargetReached
    @target money
AS
    DECLARE @avg MONEY
    DECLARE @count INT
    
    SET @count = 0
    SELECT @avg = AVG(SAL) FROM EMP
    
    WHILE @avg < @target
    BEGIN
        UPDATE EMP
            SET SAL = SAL + 50
        
        SELECT @avg = AVG(SAL) FROM EMP
        SET @count = @count + 1
    END
    
    PRINT 'No of records updated: ' + CAST(@count AS VARCHAR(20))
    PRINT 'Calculated AVG: ' + CAST(@avg AS VARCHAR(20))
        
    RETURN

Integrated Project - Part 1 - 8 HIGH LEVEL USE CASES

Please post your comments concerning your questions about use cases here. :)
This post is open to discussion, so feel free to ask questions/suggest answers!

SQL Northwind DB - Stored Procedures

1. Create a stored procedure to search employees by the first few letters in their last name.

CREATE PROCEDURE dbo.spSearchEmployeebyLName
    @LName nvarchar(20)

AS
    SET @LName = @LName + '%'
    SELECT * FROM Employees
    WHERE LastName LIKE @LName 
    RETURN

2. Create a stored procedure to create a new product and a new category by passing the CategoryName, ProductName, and the status (Discontinued) is False.

CREATE PROCEDURE spCreateNewProduct
    
    @CategoryName nvarchar(15),
    @ProductName nvarchar(40),
    @Status bit = 'False'

AS
    DECLARE @CatID INT;
    
    INSERT INTO Categories (CategoryName)
    VALUES ( @CategoryName )
    SET @CatID=@@IDENTITY
    
    INSERT INTO Products (ProductName, CategoryID, Discontinued) 
    VALUES (@ProductName, @CatID, @Status)
    
    RETURN

3. Create a stored procedure to show the stock level of every product in the Products table. For unit stock under 20, the stock level is low. Unit stock above 100, the stock level is high. For the rest, the stock level is medium.

CREATE PROCEDURE spShowStockLevel 
    
AS
    SELECT ProductID, ProductName, UnitsInStock,
    CASE
    WHEN UnitsInStock <20
        THEN 'LOW'
    WHEN UnitsInStock >100
        THEN 'HIGH'
    ELSE
        'MEDIUM'
    END
    AS StockLevel
    FROM Products
    RETURN

4. Create a stored procedure to display the territory status for each territory in Territories table. Territories id that starts with 9 is considered as big cities. Territories id starts with 0 or 1 is considered as small cities. Territories id starts with 2 to 6 is considered as other cities. Territories id starts with 7 or 8 is considered as popular cities.

CREATE PROCEDURE spShowTerritoryStatus

AS
    SELECT TerritoryID, TerritoryDescription,
    CASE 
        WHEN TerritoryID LIKE '9%'
            THEN 'big cities'
        WHEN TerritoryID LIKE '[0-1]%'
            THEN 'small cities'
        WHEN TerritoryID LIKE '[2-6]%'
            THEN 'other cities'
        WHEN TerritoryID LIKE '[7-8]%'
            THEN 'big cities'
    END
    AS TerritoryStatus
    FROM Territories
    
    RETURN

5. Create a stored procedure to find the new price of the first 10 products in Products table. The new price will be calculated based on the amount of percentage inputted into the procedure. The output should show the old and the new prices.

CREATE PROCEDURE spFindNewPrice
    @Percentage DECIMAL
AS
    SELECT ProductID, ProductName, UnitPrice, 
    NewUnitPrice = ROUND(( ( (@Percentage/100) +1 ) * UnitPrice ),2)     
    FROM Products
    RETURN

EXERCISE 1

CREATE DATABASE TEMP
NAME =TEMP_EMPLOYEE
FILENAME=S:\WEERAPOL\DDL_DEMO\APP_DATA\TempEmployee.mdf

EXERCISE 2

create table publishers
(
pub_id char(4) primary key check(pub_id like'[1-2][0-9][0-9][0-9]'),
pub_name varchar(40),
suburb varchar(40),
state char(3),
country varchar(30) default 'Australia',

)   

create table jobs
(
job_id smallint primary key identity (1,1),
job_desc varchar(50) not null default 'New Position',
min_lvl tinyint not null check(min_lvl >= 10),
max_lvl tinyint not null check(max_lvl <= 250),
)      

create table employees
(
emp_id char(90) primary key check(emp_id like '[A-Z][0-9][0-9][0-9][0-9][GS]'),
fname varchar(20) not null,
minit char(1),
lname varchar(30) not null,
job_id smallint not null default 1,
job_lvl tinyint default 10,
pub_id char(4) not null default 1000,
hire_date datetime not null default getdate(),

)      

EXERCISE 3

alter table employees
add constraint FKEmployeesjobs foreign key (job_id) references jobs(job_id)
on update no action
on delete cascade

alter table employees
add constraint FKEmployeespublishers foreign key (pub_id) references publishers(pub_id)
on update no action
on delete cascade

EXERCISE 4

TABLE PUBS

//INSERT ONE AT A TIME
1>
INSERT INTO employees
                        (pub_id)
VALUES        ('1000')

2>

INSERT INTO employees
                        (pub_id)
VALUES        ('1001')

3>
INSERT INTO employees
                        (pub_id)
VALUES        ('2000')

//FROM W3SCHOOLS (DOES NOT WORK)

INSERT INTO employees
                        (pub_id)
VALUES        ('2000'),('2002'),('2003')


TABLE JOBS
1>
INSERT INTO jobs
                        (job_desc, min_lvl, max_lvl)
VALUES        ('clerk', '10', '30')

2>
INSERT INTO jobs
       (job_desc, min_lvl, max_lvl)
VALUES         ('Director','200','250')

3>
INSERT INTO jobs
       (min_lvl, max_lvl)
VALUES         ('30','100')


TABLE EMPLOYEES
1>
INSERT INTO employees
                        (emp_id, fname, lname, job_id, pub_id, hire_date)
VALUES        ('JA1000G', 'Joe', 'Abra', '1', '1000', '10/1/2008')


INSERT INTO employees
                        (emp_id, fname, lname, hire_date)
VALUES        ('JA1000G', 'Joe', 'Abra', '10/1/2008')



2>

INSERT INTO employees
values('BA2000S','Jack','S','Majo','2','220','1001')

INSERT INTO employees
                        (emp_id, fname, minit, lname, job_id, job_lvl, pub_id)
VALUES        ('BA2000S', 'Jack', 'S', 'Majo', '2', '220', '1001')

EXERCISE 5

UPDATE jobs
SET job_desc = 'Receptionist'
WHERE job_desc = 'New Position'

UPDATE employees
SET job_lvl = ceiling(job_lvl + (job_lvl *10)/100)

EXERCISE 6

DELETE
FROM pubs
WHERE pub_id = 2000

EXERCISE 7

SELECT        employees.fname + '  ' + employees.lname + ' is a ' + jobs.job_desc + ' hired  in ' + CAST(DATEPART(yyyy, employees.hire_date) AS char) AS Expr1
FROM            employees LEFT OUTER JOIN
                        jobs ON employees.job_id = jobs.job_id
WHERE        (employees.emp_id = 'JA1000G')

SELECT        employees.fname + ' ' + ISNULL(employees.minit, '') + ' ' + employees.lname + ' is a ' + jobs.job_desc + ' hired  in ' + CAST(DATEPART(yyyy,
                        employees.hire_date) AS char) AS Expr1
FROM            employees LEFT OUTER JOIN
                        jobs ON employees.job_id = jobs.job_id
EXERCISE 8

SELECT        employees.emp_id, employees.fname, employees.minit, employees.lname, employees.job_id, employees.job_lvl, employees.pub_id, employees.hire_date,
                        jobs.job_id AS Expr1, jobs.job_desc, jobs.min_lvl, jobs.max_lvl
FROM            employees LEFT OUTER JOIN
                        jobs ON employees.job_id = jobs.job_id
WHERE        (employees.job_lvl = jobs.min_lvl)

SQL The second Homework

1.
SELECT COUNT(DISTINCT JOB) AS Expr1
FROM EMP

SELECT COUNT(*) AS Expr1, JOB
FROM EMP
GROUP BY JOB

2.
SELECT HIREDATE
FROM EMP
ORDER BY HIREDATE DESC

3.
SELECT HIREDATE
FROM EMP
ORDER BY HIREDATE

4.
SELECT ENAME, JOB, HIREDATE
FROM EMP
ORDER BY HIREDATE DESC

5.
SELECT DEPTNO, JOB, ENAME
FROM EMP
ORDER BY DEPTNO, SAL DESC

6.
SELECT DNAME, DEPTNO
FROM DEPT
WHERE (DEPTNO > 20)

7.
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
FROM EMP
WHERE (COMM > SAL)

8.SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
FROM EMP
WHERE (COMM < SAL)

9.
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
FROM EMP
WHERE (SAL BETWEEN 500 AND 1500)

10.
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
FROM EMP
WHERE (SAL BETWEEN 500 AND 1500)
11.
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO

12.
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
FROM EMP
WHERE (SAL < 2000)

13.
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
FROM EMP
WHERE (SAL > 2000)

14.
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
FROM EMP
WHERE (MGR IN (7902, 7566, 7788))

15.
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
FROM EMP
WHERE (MGR BETWEEN 7092 AND 7788)

16.
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
FROM EMP
WHERE (ENAME LIKE 's%')

17.
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
FROM EMP
WHERE (ENAME LIKE 'a%')

18.
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
FROM EMP
WHERE (ENAME = 'scott')


19.
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
FROM EMP
WHERE (ENAME IN ('smith', 'king'))

20.
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
FROM EMP
WHERE (ENAME LIKE '[wk]%') AND (DEPTNO NOT IN (10, 20, 40))

21.
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
FROM EMP
WHERE (ENAME LIKE 'scott%martin')

22.
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
FROM EMP
WHERE (ENAME LIKE 'm%') AND (DEPTNO = 30)

23.
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
FROM EMP
WHERE (ENAME LIKE '____')

24.
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
FROM EMP
WHERE (ENAME LIKE '_____') AND (JOB = 'manager')

25.
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
FROM EMP
WHERE (ENAME LIKE '____') AND (JOB = 'salesman')

26.
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
FROM EMP
WHERE (ENAME LIKE 'b%') AND (JOB = 'manager')

27.
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
FROM EMP
WHERE (JOB LIKE '[^a]%')

28.
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
FROM EMP
WHERE (JOB LIKE '[^c]%')

29.
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
FROM EMP
WHERE (JOB IN ('manager', 'clerk')) AND (DEPTNO = 10)

30.
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
FROM EMP
WHERE (JOB IN ('analyst', 'salesman')) AND (DEPTNO = 30)

31.
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
FROM EMP
WHERE (JOB = 'clerk') AND (SAL BETWEEN 1000 AND 2000)

32.
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
FROM EMP
WHERE (JOB = 'manager') AND (SAL BETWEEN 2500 AND 3000)

33.
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
FROM EMP
WHERE (JOB IN ('clerk', 'manager')) AND (SAL BETWEEN 2500 AND 3000)

34.
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
FROM EMP
WHERE (JOB = 'manager') AND (SAL BETWEEN 2000 AND 3000)

SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
FROM EMP
WHERE (JOB = 'manager') OR (SAL BETWEEN 2000 AND 3000)

Homework - IComparable and IComparer - A step-by-step tutorial

1) Define your class City, as usual.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace CityCompare
{
    public class City
    {
        //fields
        private string name;
        private string state;

        //properties
        public string Name
        {
            get { return name; }
            set { name = value; }
        }
        public string State
        {
            get { return state; }
            set { state = value; }
        }

        //constructors
        public City() { }   //null constructor
        public City(string _name, string _state)
        {
            this.name = _name;
            this.state = _state;
        }

        //override toString method
        public override string ToString()
        {
            return name + ", " + state;
        }
    }
}

2) We need IComparable to our class to provide for a simple sorting capability
We need to provide a simple sorting capability to the code. We make use of the interface IComparable. To do that, we need to tell the class to implement the interface IComparable. Just add a column (:) and the interface name next to the class name.

public class City : IComparable

3) IComparable Sorting capability - The CompareTo function
Now, we need to implement IComparable CompareTo function to provide the default sort order. We add the following code to our class:

//IComparable.CompareTo needs to be implemented 
        //to provide default sort order. Make it public
        public int CompareTo(Object obj)
        {
            City c = (City)obj; //cast Object type to City object
            return string.Compare(this.name, c.name);
        }

4) Writing your tester class with an ArrayList
In this tutorial, I used the Form1 class as the tester class. The tester class will populate the Arraylist with values.
A listbox is made available on the form so that the cities are listed there.
Also, a button that shows "Simple Sort" is available and uses the ArrayList.Sort() method and does not require any additional implementation.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Collections;       //add this line to use ArrayList


namespace CityCompare
{
    public partial class Form1 : Form
    {
        //fields
        private ArrayList cities = new ArrayList();

        //properties
        public ArrayList Cities
        {
            get { return cities; }
            set { cities = value; }
        }

        //constructors
        public Form1()
        {
            InitializeComponent();
        }

        //methods
        //Populate the listbox
        public void PopulateListBox()
        {
            //clear listbox
            lisCitiesList.Items.Clear();

            //Populate the listbox
            foreach (Object item in cities)
            {
                lisCitiesList.Items.Add(item.ToString());
            }
        }

        //Populate the ArrayList with cities and states
        public void PopulateArrayList()
        {
            cities.Add(new City("Sydney", "New South Wales"));
            cities.Add(new City("Albury", "New South Wales"));
            cities.Add(new City("Armindale", "New South Wales"));
            cities.Add(new City("Bathurst", "New South Wales"));
            cities.Add(new City("Blue Mountains", "New South Wales"));
            cities.Add(new City("Palmerston", "Northern Territory"));
            cities.Add(new City("Darwin", "Northern Territory"));
            cities.Add(new City("Melbourne", "Victoria"));
            cities.Add(new City("Perth", "Western Australia"));
            cities.Add(new City("Albany", "Western Australia"));
            cities.Add(new City("Canning", "Western Australia"));
            cities.Add(new City("Gosnells", "Western Australia"));
            cities.Add(new City("Hobart", "Tasmania"));
            cities.Add(new City("Hobart", "AnotherState"));
        }

        //events
        private void Form1_Load(object sender, EventArgs e)
        {
            //Populate cities arraylist
            PopulateArrayList();

            //Populate listbox
            PopulateListBox();
        }

        private void btnNormalSort_Click(object sender, EventArgs e)
        {
            cities.Sort();
            PopulateListBox();  //refresh list
        }

    }
}

5) Sort list of cities first by state name, then by city name, without modifying the implementation of the City class
As Alan stated, you cannot redefine the CompareTo method. Let's stick to the requirement and define a CityComparer class that implements the Comparator<City> interface.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;       //add this line to use interface IComparer

namespace CityCompare
{                                           //STEP 2
    public class CityComparer: IComparer    //we use this IComparer as it gives
                                            //additional comparison mechanisms
    {
        //we need to implement the interface member Compare(obj,obj)
        //from the interface IComparer
        public int Compare(Object a, Object b)  //make it public
        {
            City c1 = (City)a;  //cast Object to City
            City c2 = (City)b;  //cast Object to City

            return String.Compare(c1.State, c2.State);  //State is a string, 
                                                        //hence we use String.Compare
        }

        //StateSort method
        //sorts the ArrayList elements by state
        public static IComparer StateSort()
        {
            return (IComparer)new CityComparer();
        }
    }
}

6) Call the method StateSort to sort by state in the Form1 class.

private void btnCityComparer_Click(object sender, EventArgs e)
{
    cities.Sort(CityComparer.StateSort());  //STEP 3
    PopulateListBox();  //refresh list
}

All done! Suggestions and comments are welcome.

C# - Understanding IComparable and IComparer

Check this link for an article about IComparable and IComparer.

C# UML Class Diagram

Click to enlarge...


Shape Class:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace ShapeInheritance
{
    public abstract class Shape     //enforce that abstract MUST BE OVERRIDEN
    {
        //fields
        protected double xPos;  //'protect' modifier used because we want all 
        protected double yPos;    //subclasses will generally inherit those fields

        //constructors
        public Shape(double _xPos, double _yPos)
        {
            this.xPos = _xPos;
            this.yPos = _yPos;
        }

        //methods
        public abstract double Area();  //enforce that Area HAS TO BE OVERRIDEN BY SUB-CLASSES

        public override string ToString()
        {
            return "Position : (" + xPos.ToString() + ", " + yPos.ToString() + ")\n";
        }
    }
}

Circle class:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace ShapeInheritance
{
    public class Circle : Shape
    {
        //fields
        private double radius;

        //properties
        public double Radius
        {
            get { return radius; }
            set { radius = value; }
        }

        //constructors
        public Circle() : base(0,0)
        {
        }

        public Circle(double _xPos, double _yPos) : base(_xPos , _yPos)
        {
        }

        public Circle(double _xPos, double _yPos, double _radius) : base(_xPos, _yPos)
        {
            this.radius = _radius;
        }

        //methods
        public override double Area()
        {
            return Math.PI * Math.Pow(Radius, 2);
        }

        public override string ToString()
        {
            return base.ToString() + "\nradius : " + radius + "\nArea : " + Area().ToString("N2");
        }
    }
}

Cylinder class:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace ShapeInheritance
{
    public class Cylinder : Circle
    {
        //fields
        private double height;

        //properties
        public double Height
        {
            get { return height; }
            set { height = value; }
        }

        //constructors
        public Cylinder() : base(0,0)
        {
        }

        public Cylinder(double _xPos, double _yPos, double _radius, double _height) : base(_xPos, _yPos, _radius)
        {
            height = _height;
        }

        //methods
        public override double Area()
        {
            return (2 * base.Area()) + (2 * Math.PI * Radius * height);
        }

        public double Volume()
        {
            return Math.PI * Math.Pow(Radius,2) * height;
        }

        public override string ToString()
        {
            return base.ToString() + "\nHeight : " + height  + "\nVolume : " + Volume().ToString("N2");
        }

    }
}

Form coding:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace ShapeInheritance
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void btnCircle_Click(object sender, EventArgs e)
        {
            Circle circle = new Circle(0, 0, 10);
            lblText.Text = circle.ToString();
        }

        private void btnCylinder_Click(object sender, EventArgs e)
        {
            Cylinder cylinder = new Cylinder(0, 0, 10,10);
            lblText.Text = cylinder.ToString();
        }
    }
}

Note:


1. Chain constructors are not used. Sub-constructors are calling the base constructor only.


2. Rectangle class not implemented.