ITC Resources: August 2011

Thursday, August 18, 2011

Debugging and Trouble Shooting

Overview

There are three major types of errors that can occur in your program: First there are compile errors. These occur before you ever run the program. Typically they consist of mispelled key words or variables, missing semi-colons and curly braces. These must be corrected before you can run the program.

Next there are Run time errors. These are errors that occur when the program is running. Typically they consist of Data type conflicts, or objects that are called before they are initialized. These cause the code to break and go into debug mode.

The third kind of error is a logical error. Logical errors are errors in the logic of the program. They usually don't cause the program to fail or crash. Rather they cause the program to return the wrong answer or no answer at all. These are the hardest errors to track down and fix.

Compile Errors

The key with compile errors is to use the Visual Studio environnment.

Use the intellisense. The intellisense will show you all the objects defined by your program. It will show you all the relevant methods and properties. Using it can help cut down on mispellings and errors in capitalization.

Read the error messages. Actually read them. They don't always tell you exactly where the problem is, but they get you close.

Always work from the first error on down. Often fixing the first error will clear up most or all of the remaining errors. Errors tend to cascade through the program. One causes another which causes another, etc. If you work from the bottom of the list you will get frustrated, because you usually won't find anything wrong.

Pay attention to the color coding in the coding environment itself. A keyword should be blue--if it is not it may mean you mistyped it. A red underline signifies an error. A green underline is just a comment or possibly a warning.

Run time errors

Most runtime errors are caused by user input, Users don't always enter what we expect, so the program should be able to handle the unexpected input. Ideally, test all use input to see if it is in the correct form. Use TryParse() instead of Parse().

The other common cause of Runtime errors are objects that are not initialized properly. Make sure that you instantiate an object (make it new) before you call it.

Logical Errors

The key to finding logical errors is testing. Try the program with known values and a known output. If the output varies on the input, test a complete range of inputs to see if the proper outputs are returned.

One strategy is to put temporary outputs in your program that print out the state of certain variables at a given point in the process. You can check these against what the variable should be.

You can also use Visual Studios debugger. You can place breaks in the code and step through the part in question line by line, checking the values of the variables moment to moment.

General Suggestions

When writing a program, do one thing and then test it. If it works then when you add the next thing and the program fails you know it was in the new part.

The same goes when debugging and trouble shooting. Make one change and then test it. Never make a bunch of changes willy nilly. It makes it almost impossible to tell what is working and what isn't.

If you go over the code again and again and can't find a mistake in the logic, chances are good there isn't one. Look for syntax aand spelling errors instead. You often don't see these when looking at the logic.

If possible, have someone else look it over. Another person can often see what you don't.

If you spend hours on a thing and just can't get it to work. Walk away. Do something else entirely for a while. Often when you come back with fresh eyes, the problem that you were missing becomes obvious.

Repetition Structures

Programs often need to do the same commands several times in a row. Repitition structures, or loops, are designed to do this. There are several kinds of loops.

For loops

The first one we will look at is a for loop.

A for loop contains three elements, a variable with an intial value, a terminal condition, and an increment or decriment statement. Here is an example of a simple for loop that simply outputs the numbers 1 through 10.


for(int i=0;i<=10;i++)

{

   Console.WriteLine(i);

}

You could also count backwards:


for(int i=10;i>0;i--)

{

   Console.WriteLine(i);

}

The terminating condition can be a variable:


Console.WriteLine("How far do you want to count?");

int number=int.Parse(Console.ReadLine());



for(i=0;i<=number;i++)

{

   Console.WriteLine(i);	

}

While Loops

For loops are good when you have a specific number or repititions you want to do. While loops are good for when you have an indefinite number of repititions.
Here is an example of a while loop:


int grade=0;

while(grade != 999)

{

    Console.WriteLine("Enter a grade. 999 to exit");

    int grade = int.Parse(Console.Readline());



}

Do loops

Do loops are like while loops, but the condition is at the end of the loop. This has a subtle effect. A while loop might never execute. If the condition is met before the loop is encountered--if, for instance, grade equaled 999, the program would never execute the while loop. A do loop, on the other hand, always executes at least once.

Here is an example of a do loop:


int grade=0;



do

{

    Console.WriteLine("Enter a grade. 999 to exit");

    int grade = int.Parse(Console.Readline());



}while(grade != 999);

Infinite Loops

It is important that a loop have a termination point. If not you can create what is called an infinite loop. The only way out of an infinite loop is to stop the program.


for(int i=0;i>0;i++)

{

   Console.WriteLine(i);

}

Embedded loops, break keyword

It is important to note that you can nest loops and selection structures as you need. You can have loops inside of loops and ifs inside of ifs. We will see some examples of this in later examples and lectures.

One additional Key words need mention: break. You can use break to exit from a loop before the terminal condition, Here is an example:


for(int i=1;i<1000;I++)

{

	Console.WriteLine("enter a name--'exit' to quit");

        string name=Console.ReadLine();

	if (name.Equals("exit") || name.Equals("Exit"))

	{

		break;

	}

}

Wednesday, August 17, 2011

Selection Statements

Programs often have to make choices about what to do with a value. If statements and switches are programming structures used to select what to do if a value falls into a certain range.

If statements

An if consists of the if keyword and a condition in parenthesis. The condition must evaluate to true or false (a bool). If there is only one statement after the if you don't have to use curly braces {}, though I would encourage you to use them just to be in the habit. If there are multiple statements dependent on the condition you do have to use curly braces.

Here is a method that shows an example of simple if statements


        void SimpleIFStatement()

        {

            Console.WriteLine("Enter a number between 1 and 10");

            int number = int.Parse(Console.ReadLine());



           

            if (number <1 )

                 Console.WriteLine("the number is too small");





            if (number > 10)

                Console.WriteLine("The number is too Large");

                

        }

The next example shows the use of the "else" clause in an if statement. The else clause handles any value not covered by the main if statement.




        void IfElseStatement()

        {

            Console.WriteLine("Enter your age");

            int age = int.Parse(Console.ReadLine());



            if (age >= 18)

            {

                Console.WriteLine("You are old enough, Welcome");

            }

            else

            {

                //for anything less than 18

                Console.WriteLine("Come back when your are older");

            }

        }

If you need to test for multiple possible conditions you can use the if, else if, else structure. It is important to note that sequence matters. The program stops at the first true statement. In the following example if I put the lowest temperature first, the program would always read "cool" for any temperature over 40, even if the temperature were 110.




        void IFElseIFExample()

        {

            Console.WriteLine("Enter the day's  high Temperature");

            int temp = int.Parse(Console.ReadLine());



            if (temp >= 90)

            {

                Console.WriteLine("Way too hot");

            }

                else 

                     if(temp >= 80)

                    {

                        Console.WriteLine("A bit warm");

                    }

                else

                    if (temp >= 70)

                    {

                        Console.WriteLine("Just right");

                    }

                    else

                        if (temp >= 60)

                        {

                            Console.WriteLine("Pleasant");

                        }

                        else

                            if (temp >= 40)

                            {

                                Console.WriteLine("cool");

                            }

                            else

                            {

                                Console.WriteLine("Cold");

                            }



        }

Below is an if example used with the TryParse method. If you enter a bad value into the Parse method it crashes the program. With TryParse it doesn't. TryParse returns a boolean, True if the value actually parses into an int or double or whatever, false if it fails to parse. The try parse also requires an out parameter. If the values parses correctly it's value is assigned to the outside variable--in this case "number".
You can test the boolean variable with an if statement to determine if the parse worked or not.


void TryParseExample()

        {

            bool goodint;

            int number;



            Console.WriteLine("Enter an integer value");

            goodint = int.TryParse(Console.ReadLine(), out number);



            if (goodint == true)

            {

                Console.WriteLine("Your integer is {0}", number);

            }

            else

            {

                Console.WriteLine("Not an integer");

            }

        }

Finally here is an example of if statements with multiple conditions using and (&&) and or (||) to combine them.
In and conditions both sides of the statement must be true for the if to evaluate as true. For an or statement, if one or the other statement is true, then the whole statement is true.
Here is the example:


 void ANDORExamples()

        {

            Console.WriteLine("Enter a number between 1 and 10");

            int number = int.Parse(Console.ReadLine());



            //and example both must be true

            if (number > 1 && number < 10)

            {

                Console.WriteLine("Valid Entry");

            }



            //orExample

            {

                if (number < 1 || number > 10)

                {

                    Console.WriteLine("Invalid Entry"); 

                }

            }

        }

Switch

Switch is another way to select based on a set of values, but unlike the if else if structure it only matches exact values.




        void SwitchExample()

        {

            Console.WriteLine("Enter a grade 1, 2, 3, or 4");

            int grade = int.Parse(Console.ReadLine());



            switch (grade)

            {

                case 1:

                    Console.WriteLine("D");

                    break;

                case 2:

                    Console.WriteLine("c");

                    break;

                case 3:

                    Console.WriteLine("B");

                    break;

                case 4:

                    Console.WriteLine("A");

                    break;

                default:

                    Console.WriteLine("Not a valid grade");

                    break;

            }

        }

Tuesday, August 16, 2011

Link to High Availability Presentation

Here is the link to the high Availability presentation"

http://www.seattlejungle.com/highavailability.htm

CLR Procedure

Here is the SQL for the CLR procedure:

Select LocationName as [Location],
Sum(Employee.func_PriceWithDiscount(AutoServiceID,DiscountPercent)) as Subtotal,
Sum(Employee.GetTax(Employee.func_PriceWithDiscount(AutoServiceID,DiscountPercent),TaxPercent))
as Tax,
sum(Employee.func_PriceWithDiscount(AutoServiceID,DiscountPercent) +
Employee.GetTax(Employee.func_PriceWithDiscount(AutoServiceID,DiscountPercent),TaxPercent))
as Total
From Employee.VehicleServiceDetail vsd
inner Join Employee.VehicleService vs
on vs.VehicleServiceID=vsd.VehicleServiceID
inner Join Customer.Location l
on l.LocationID=vs.LocationID
Group by LocationName

Here is the CLR code with the SQL embedded:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void usp_LocationSales()
{
// Put your code here
using (SqlConnection connect = new SqlConnection("context connection=true"))
{
connect.Open();
SqlCommand cmd = connect.CreateCommand();
cmd.CommandText = "Select LocationName as [Location], "
+ "Sum(Employee.func_PriceWithDiscount(AutoServiceID,DiscountPercent)) as Subtotal, "
+ "Sum(Employee.GetTax(Employee.func_PriceWithDiscount(AutoServiceID,DiscountPercent),TaxPercent)) "
+ " as Tax, "
+ " sum(Employee.func_PriceWithDiscount(AutoServiceID,DiscountPercent) "
+ "+ Employee.GetTax(Employee.func_PriceWithDiscount(AutoServiceID,DiscountPercent),TaxPercent)) "
+ "as Total "
+ "From Employee.VehicleServiceDetail vsd "
+ "inner Join Employee.VehicleService vs "
+ "on vs.VehicleServiceID=vsd.VehicleServiceID "
+ "inner Join Customer.Location l "
+ "on l.LocationID=vs.LocationID "
+ "Group by LocationName ";

SqlContext.Pipe.ExecuteAndSend(cmd);
}
}
};

Monday, August 15, 2011

Windows Form:Tip Calculator

Here is all the auto generated code for the window:

#pragma once

namespace TipCalculator {

using namespace System;
using namespace System::ComponentModel;
using namespace System::Collections;
using namespace System::Windows::Forms;
using namespace System::Data;
using namespace System::Drawing;

///

/// Summary for Form1
///

public ref class Form1 : public System::Windows::Forms::Form
{
public:
Form1(void)
{
InitializeComponent();
//
//TODO: Add the constructor code here
//
}

protected:
///

/// Clean up any resources being used.
///

~Form1()
{
if (components)
{
delete components;
}
}
private: System::Windows::Forms::Panel^ panel1;
protected:
private: System::Windows::Forms::RadioButton^ rdoTwentyPercent;
private: System::Windows::Forms::RadioButton^ rdoFifteenPercent;
private: System::Windows::Forms::RadioButton^ rdoTenPercent;
private: System::Windows::Forms::TextBox^ txtAmount;
private: System::Windows::Forms::Label^ label1;
private: System::Windows::Forms::Button^ button1;
private: System::Windows::Forms::Label^ label2;
private: System::Windows::Forms::Label^ label3;
private: System::Windows::Forms::Label^ label4;
private: System::Windows::Forms::Label^ lblTax;
private: System::Windows::Forms::Label^ lblTip;
private: System::Windows::Forms::Label^ lblTotal;

private:
///

/// Required designer variable.
///

System::ComponentModel::Container ^components;

#pragma region Windows Form Designer generated code
///

/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
///

void InitializeComponent(void)
{
this->panel1 = (gcnew System::Windows::Forms::Panel());
this->rdoTenPercent = (gcnew System::Windows::Forms::RadioButton());
this->rdoFifteenPercent = (gcnew System::Windows::Forms::RadioButton());
this->rdoTwentyPercent = (gcnew System::Windows::Forms::RadioButton());
this->txtAmount = (gcnew System::Windows::Forms::TextBox());
this->label1 = (gcnew System::Windows::Forms::Label());
this->button1 = (gcnew System::Windows::Forms::Button());
this->label2 = (gcnew System::Windows::Forms::Label());
this->label3 = (gcnew System::Windows::Forms::Label());
this->label4 = (gcnew System::Windows::Forms::Label());
this->lblTax = (gcnew System::Windows::Forms::Label());
this->lblTip = (gcnew System::Windows::Forms::Label());
this->lblTotal = (gcnew System::Windows::Forms::Label());
this->panel1->SuspendLayout();
this->SuspendLayout();
//
// panel1
//
this->panel1->Controls->Add(this->rdoTwentyPercent);
this->panel1->Controls->Add(this->rdoFifteenPercent);
this->panel1->Controls->Add(this->rdoTenPercent);
this->panel1->Location = System::Drawing::Point(32, 56);
this->panel1->Name = L"panel1";
this->panel1->Size = System::Drawing::Size(280, 170);
this->panel1->TabIndex = 0;
//
// rdoTenPercent
//
this->rdoTenPercent->AutoSize = true;
this->rdoTenPercent->Location = System::Drawing::Point(58, 22);
this->rdoTenPercent->Name = L"rdoTenPercent";
this->rdoTenPercent->Size = System::Drawing::Size(77, 17);
this->rdoTenPercent->TabIndex = 0;
this->rdoTenPercent->TabStop = true;
this->rdoTenPercent->Text = L"10 Percent";
this->rdoTenPercent->UseVisualStyleBackColor = true;
//
// rdoFifteenPercent
//
this->rdoFifteenPercent->AutoSize = true;
this->rdoFifteenPercent->Location = System::Drawing::Point(58, 60);
this->rdoFifteenPercent->Name = L"rdoFifteenPercent";
this->rdoFifteenPercent->Size = System::Drawing::Size(77, 17);
this->rdoFifteenPercent->TabIndex = 1;
this->rdoFifteenPercent->TabStop = true;
this->rdoFifteenPercent->Text = L"15 Percent";
this->rdoFifteenPercent->UseVisualStyleBackColor = true;
//
// rdoTwentyPercent
//
this->rdoTwentyPercent->AutoSize = true;
this->rdoTwentyPercent->Location = System::Drawing::Point(58, 99);
this->rdoTwentyPercent->Name = L"rdoTwentyPercent";
this->rdoTwentyPercent->Size = System::Drawing::Size(96, 17);
this->rdoTwentyPercent->TabIndex = 2;
this->rdoTwentyPercent->TabStop = true;
this->rdoTwentyPercent->Text = L"twenty Percent";
this->rdoTwentyPercent->UseVisualStyleBackColor = true;
//
// txtAmount
//
this->txtAmount->Location = System::Drawing::Point(157, 257);
this->txtAmount->Name = L"txtAmount";
this->txtAmount->Size = System::Drawing::Size(100, 20);
this->txtAmount->TabIndex = 1;
//
// label1
//
this->label1->AutoSize = true;
this->label1->Location = System::Drawing::Point(32, 260);
this->label1->Name = L"label1";
this->label1->Size = System::Drawing::Size(126, 13);
this->label1->TabIndex = 2;
this->label1->Text = L"Enter Amount Before Tax";
//
// button1
//
this->button1->Location = System::Drawing::Point(35, 305);
this->button1->Name = L"button1";
this->button1->Size = System::Drawing::Size(75, 23);
this->button1->TabIndex = 3;
this->button1->Text = L"Calculate";
this->button1->UseVisualStyleBackColor = true;
this->button1->Click += gcnew System::EventHandler(this, &Form1::button1_Click);
//
// label2
//
this->label2->AutoSize = true;
this->label2->Location = System::Drawing::Point(334, 263);
this->label2->Name = L"label2";
this->label2->Size = System::Drawing::Size(28, 13);
this->label2->TabIndex = 4;
this->label2->Text = L"Tax:";
//
// label3
//
this->label3->AutoSize = true;
this->label3->Location = System::Drawing::Point(337, 305);
this->label3->Name = L"label3";
this->label3->Size = System::Drawing::Size(25, 13);
this->label3->TabIndex = 5;
this->label3->Text = L"Tip:";
//
// label4
//
this->label4->AutoSize = true;
this->label4->Location = System::Drawing::Point(337, 338);
this->label4->Name = L"label4";
this->label4->Size = System::Drawing::Size(34, 13);
this->label4->TabIndex = 6;
this->label4->Text = L"Total:";
//
// lblTax
//
this->lblTax->AutoSize = true;
this->lblTax->Location = System::Drawing::Point(437, 263);
this->lblTax->Name = L"lblTax";
this->lblTax->Size = System::Drawing::Size(0, 13);
this->lblTax->TabIndex = 7;
//
// lblTip
//
this->lblTip->AutoSize = true;
this->lblTip->Location = System::Drawing::Point(440, 304);
this->lblTip->Name = L"lblTip";
this->lblTip->Size = System::Drawing::Size(0, 13);
this->lblTip->TabIndex = 8;
//
// lblTotal
//
this->lblTotal->AutoSize = true;
this->lblTotal->Location = System::Drawing::Point(440, 338);
this->lblTotal->Name = L"lblTotal";
this->lblTotal->Size = System::Drawing::Size(0, 13);
this->lblTotal->TabIndex = 9;
//
// Form1
//
this->AutoScaleDimensions = System::Drawing::SizeF(6, 13);
this->AutoScaleMode = System::Windows::Forms::AutoScaleMode::Font;
this->BackColor = System::Drawing::Color::FromArgb(static_cast(static_cast(192)), static_cast(static_cast(192)),
static_cast(static_cast(255)));
this->ClientSize = System::Drawing::Size(612, 446);
this->Controls->Add(this->lblTotal);
this->Controls->Add(this->lblTip);
this->Controls->Add(this->lblTax);
this->Controls->Add(this->label4);
this->Controls->Add(this->label3);
this->Controls->Add(this->label2);
this->Controls->Add(this->button1);
this->Controls->Add(this->label1);
this->Controls->Add(this->txtAmount);
this->Controls->Add(this->panel1);
this->Name = L"Form1";
this->Text = L"Tip Calculator";
this->panel1->ResumeLayout(false);
this->panel1->PerformLayout();
this->ResumeLayout(false);
this->PerformLayout();

}
#pragma endregion

And here, much shorter, is the piece of code we wrote in the button event handler:

private: System::Void button1_Click(System::Object^ sender, System::EventArgs^ e) {

double TAX = .095;
double mealAmount =double::Parse(txtAmount ->Text);
double taxAmount=(mealAmount * TAX);
lblTax->Text=taxAmount.ToString("c");
double percent=0;
if (rdoTenPercent->Checked)
{
percent=.1;
}
if (rdoFifteenPercent->Checked)
{
percent=.15;
}
if(rdoTwentyPercent->Checked)
{
percent=.2;
}

double tipAmount=(mealAmount * percent);
lblTip->Text=tipAmount.ToString("c");

lblTotal->Text=(mealAmount + taxAmount + tipAmount).ToString("c");
}

Thursday, August 11, 2011

Links to Presentation Powerpoints

Dynamic Management Views

Full Text Indexing

Security Presentation

SQL Server Agent

Here is the script for the backup and recovery presentation:

-- change recovery model
ALTER DATABASE Automart SET RECOVERY SIMPLE;
--ALTER DATABASE Automart SET RECOVERY BulkLogged;
ALTER DATABASE Automart SET RECOVERY full;

-- create a full backup of Automart
BACKUP DATABASE Automart
TO DISK = 'C:\Users\ITStudent\Documents\Backups\Automart.bak'
with init;

-- create a differential backup of Automart appending to the last full backup
BACKUP DATABASE Automart
TO DISK = 'C:\Users\ITStudent\Documents\Backups\Automart.bak'
with differential;

-- create a backup of the log
use master;
BACKUP LOG Automart
TO disk = 'C:\Users\ITStudent\Documents\Backups\AutomartLog4.bak'
WITH NORECOVERY, NO_TRUNCATE;

create table TestTable
(
ID int identity(1,1) primary key,
MyTimestamp datetime
);

insert into TestTable values
(
GETDATE()
);

use Automart;
select * from TestTable;

-- restore from the full backup
use master;
RESTORE DATABASE Automart
FROM disk = 'C:\Users\ITStudent\Documents\Backups\Automart.bak'
with norecovery, file = 1;

-- restore from the differential backup on file 2
RESTORE DATABASE Automart
FROM disk = 'C:\Users\ITStudent\Documents\Backups\Automart.bak'
with norecovery, file = 2;

-- restore from the differential backup on file 3
RESTORE DATABASE Automart
FROM disk = 'C:\Users\ITStudent\Documents\Backups\Automart.bak'
with norecovery, file = 3;

-- restore from the log
use master;
RESTORE LOG Automart
FROM disk = 'C:\Users\ITStudent\Documents\Backups\AutomartLog.bak'
WITH NORECOVERY;

restore database Automart;

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

--############### CREATE A COMPRESSED, MIRRORED, FULL BACKUP ###########--

BACKUP DATABASE Automart
TO DISK = 'C:\Users\Tri\My Documents\Backups\Automart_backup_20110810.bak'
MIRROR TO DISK = 'C:\Users\Tri\My Documents\Backups1\Automart_backup2_20110810.bak'
WITH COMPRESSION, INIT, FORMAT, CHECKSUM, STOP_ON_ERROR
GO
-- MIRROR TO DISK - TO CREATE A COPY OF BACKUP INTO ANOTHER FOLDER
-- WITH COMPRESSION (option) - TO SAVE SPACE

--CREATE A TRANSACTION LOG BACKUP--
USE Automart
GO
--CREATE A TEST TABLE--
create table Test_DataBACKUP
(
ID int identity(1,1) primary key,
BackUpTime datetime
)
--drop table Test_DataBACKUP
INSERT INTO Test_DataBACKUP
VALUES
(
GETDATE()
)
GO

--SELECT * FROM Test_DataBACKUP

BACKUP LOG Automart
TO DISK = 'C:\Users\Tri\My Documents\Backups\Automart_backup_lOG_20110810.trn'
WITH COMPRESSION, INIT, CHECKSUM, STOP_ON_ERROR
GO

--INSERT INTO TEST TABLE AGAIN TO PERFORM A 2ND TRANSACTION LOG BACK UP--
--INSERT STATEMENT--
--THEN--
BACKUP LOG Automart
TO DISK = 'C:\Users\Tri\My Documents\Backups\Automart_backup2_lOG_20110810.trn'
WITH COMPRESSION, INIT, CHECKSUM, STOP_ON_ERROR
GO

-------------- DIFFERENTIAL BACKUPS ---------------
BACKUP DATABASE Automart
TO DISK = 'C:\Users\Tri\My Documents\Backups\Automart_backup_20110810.dif'
MIRROR TO DISK = 'C:\Users\Tri\My Documents\Backups\Automart_backup2_20110810.dif'
WITH DIFFERENTIAL, COMPRESSION, INIT, FORMAT, CHECKSUM, STOP_ON_ERROR
GO

--################## SET THE RECOVERY MODEL #################--
ALTER DATABASE Automart
SET RECOVERY FULL
GO
--Can also check inside Database Properties in Option.

-------RESTORE A FULL BACK UP--------
--First step in restore Process is to back up the tail of the Log--
BACKUP LOG Automart
TO DISK = 'C:\Users\Tri\My Documents\Backups\Automart_backup3_log_20110810.trn'
WITH COMPRESSION, INIT, NO_TRUNCATE
GO

--Have you ever wonder how we manage to back up the transaction log--
--even when every data file for the database no longer exists.--
--As long as the transaction log has not benn damaged, it is possible to back up the log, even in the--
--absence of every data file within the database.

--Now that you have the tail of the log, execute the following code to restore the full backup--
USE master
GO

RESTORE DATABASE Automart
FROM DISK = 'C:\Users\Tri\My Documents\Backups\Automart_backup_20110810.bak'
WITH FILE = 1,
NOUNLOAD, STATS = 10
Go

--#### WITH STANDBY ? ####--

--RESTORE A DIFFERENTIAL BACKUP--
RESTORE DATABASE Automart
FROM DISK = 'C:\Users\Tri\My Documents\Backups\Automart_backup2_20110810.dif'
WITH RECOVERY
GO
-- WITH RECOVERY - RECOVER A DATABASE TO MAKE IT ACCESSIBLE FOR TRANSACTIONS

--RESTORE LOG BACKUP--
RESTORE LOG Automart
FROM DISK = 'C:\Users\Tri\My Documents\Backups\Automart_backup3_log_20110810.trn'
WITH FILE = 1, NORECOVERY, STATS = 10
GO

Here is the script for the presentation of Dynamic management views

--Retrieve Information about Database Objects
SELECT * FROM sys.databases
SELECT * FROM sys.schemas
SELECT * FROM sys.objects
SELECT * FROM sys.tables
SELECT * FROM sys.columns
SELECT * FROM sys.identity_columns
SELECT * FROM sys.foreign_keys
SELECT * FROM sys.foreign_key_columns
SELECT * FROM sys.default_constraints
SELECT * FROM sys.check_constraints
SELECT * FROM sys.indexes
SELECT * FROM sys.index_columns
SELECT * FROM sys.triggers
SELECT * FROM sys.views
SELECT * FROM sys.procedures

--retrive database and object size
SELECT * FROM sys.database_files
SELECT * FROM sys.partitions
SELECT * FROM sys.allocation_units

SELECT object_name(a.object_id), c.name, SUM(rows) rows,
SUM(total_pages) total_pages, SUM(used_pages) used_pages,
SUM(data_pages) data_pages
FROM sys.partitions a INNER JOIN sys.allocation_units b ON a.hobt_id = b.container_id
INNER JOIN sys.indexes c ON a.object_id = c.object_id and a.index_id = c.index_id
GROUP BY object_name(a.object_id), c.name
ORDER BY object_name(a.object_id), c.name

--Retrieve Index Statistics
SELECT * FROM sys.dm_db_index_operational_stats(NULL,NULL,NULL,NULL)
SELECT * FROM sys.dm_db_index_physical_stats(NULL,NULL,NULL,NULL,NULL)
SELECT * FROM sys.dm_db_index_usage_stats

--Determine Indexes to Create
SELECT * FROM sys.dm_db_missing_index_details
SELECT * FROM sys.dm_db_missing_index_group_stats
SELECT * FROM sys.dm_db_missing_index_groups
--Execute the following aggregation script and review the results
SELECT *
FROM (SELECT user_seeks * avg_total_user_cost * (avg_user_impact * 0.01)
AS index_advantage, migs.*
FROM sys.dm_db_missing_index_group_stats migs) AS migs_adv
INNER JOIN sys.dm_db_missing_index_groups AS mig
ON migs_adv.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid
ON mig.index_handle = mid.index_handle
ORDER BY migs_adv.index_advantage

--Execute the following code to force an index miss against the AdventureWorks database
SELECT City,PostalCode
FROM Person.Address
WHERE City IN ('Seattle','Atlanta')

SELECT City,PostalCode,AddressLine1
FROM Person.Address
WHERE City = 'Atlanta'

SELECT City,PostalCode,AddressLine1
FROM Person.Address
WHERE City like 'Atlan%'

--Execute the aggregation script again and review the results

SELECT *
FROM (SELECT user_seeks * avg_total_user_cost * (avg_user_impact * 0.01)
AS index_advantage, migs.*
FROM sys.dm_db_missing_index_group_stats migs) AS migs_adv
INNER JOIN sys.dm_db_missing_index_groups AS mig
ON migs_adv.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid
ON mig.index_handle = mid.index_handle
ORDER BY migs_adv.index_advantage

--Execute the following script to repeatedly run a SELECT statement and review the new results of the aggregation script
SELECT City,PostalCode,AddressLine1
FROM Person.Address
WHERE City like 'Atlan%'
GO 100

SELECT *
FROM (SELECT user_seeks * avg_total_user_cost * (avg_user_impact * 0.01)
AS index_advantage, migs.*
FROM sys.dm_db_missing_index_group_stats migs) AS migs_adv
INNER JOIN sys.dm_db_missing_index_groups AS mig
ON migs_adv.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid
ON mig.index_handle = mid.index_handle
ORDER BY migs_adv.index_advantage

--Determine Execution Statistics

SELECT query_plan, text, *
FROM sys.dm_exec_requests CROSS APPLY sys.dm_exec_query_plan(plan_handle)
CROSS APPLY sys.dm_exec_sql_text(sql_handle)

SELECT City,PostalCode,AddressLine1
FROM Person.Address
WHERE City like 'Atlan%'
GO 100
--Execute the following script to repeatedly run a SELECT statement and review the new results of the aggregation script
SELECT *
FROM (SELECT user_seeks * avg_total_user_cost * (avg_user_impact * 0.01)
AS index_advantage, migs.*
FROM sys.dm_db_missing_index_group_stats migs) AS migs_adv
INNER JOIN sys.dm_db_missing_index_groups AS mig
ON migs_adv.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid
ON mig.index_handle = mid.index_handle
ORDER BY migs_adv.index_advantage

Wednesday, August 10, 2011

Inheritance Example

Here is an example of inheritance. For this program we have a general account class that contains all the fields and methods that describe an account in the abstract. Then we have a Checking class that inherits from the Account class and adds to it the specific elements of a checking account. This is the usual patter of inheritance, General to specific.

Here is the code for the Account header and the class definition:

Account.h

#pragma once
#include <string>

class Account
{
/**********************
This is the parent class. We will never
actually implement this class only its
children. A child inherits all "public"
fields and methods
************************/
public:
Account(void);
~Account(void);
double GetBalance();
void SetBalance(double);
int GetAccountNumber();
void SetAccountNumber(int);
std::string GetCustomerName();
void SetCustomerName(std::string);
//virtual allows a child to change the method
virtual double CalculateBalance(double);

private:
double balance;
int accountNumber;
std::string customerName;
};

Account.cpp

#include "Account.h"

Account::Account(void)
{
}

Account::~Account(void)
{
}

//sets
void Account::SetAccountNumber(int accNumber)
{
accountNumber=accNumber;
}

void Account::SetBalance(double iniBalance)
{
balance=iniBalance;
}

void Account::SetCustomerName(std::string name)
{
customerName=name;
}

//gets
int Account::GetAccountNumber()
{
return accountNumber;
}

double Account::GetBalance()
{
return balance;
}

std::string Account::GetCustomerName()
{
return customerName;
}

//this method only returns a token. We want
//the children of the class to overwrite it
//to fit their needs
double Account::CalculateBalance(double transaction)
{
return 0;
}

Now here are the definitions of the child class. Notice that inheritance is signified by a single colon and then the accessor and name of the parent class You do not have to define any of the elements that are received through inheritance. The CalculateBalance() method is listed because it is overwritten in the child method. This is possible because it was declared "virtual" in the Parent class Account.

Checking.h

#pragma once
#include "account.h"
class Checking :
public Account
{
/************************
this class inherits from account
and so has access to all its public
fields and methods. Inheritance is
usually general->specific. Account
is general, checking is a specific kind
of account
**************************/
public:
Checking(void);
~Checking(void);
//only need to define new methods
void Withdrawal(double);
void Deposit(double);
//overwrite parent method of same name
double CalculateBalance(double);

};

Checking.cpp

#include "Checking.h"

Checking::Checking(void)
{
}

Checking::~Checking(void)
{
}

//give a body to the new methods
void Checking::Withdrawal(double w)
{
CalculateBalance(w * -1);
}

void Checking::Deposit(double d)
{
CalculateBalance(d);
}

//overwrite the parent method
double Checking::CalculateBalance(double transaction)
{
double newBalance= GetBalance() + transaction;

SetBalance(newBalance);

return GetBalance();
}

Now here is the Program class that tests the code. Note that only the Checking class is invoked.

Program.cpp
#include <iostream>
#include "Checking.h"
using namespace std;

int main()
{
//get the basic checking account info
Checking check;
cout << "Enter the beginning balance" << endl;
double bal;
cin >> bal;
check.SetBalance(bal);
cout << "Enter the Account number "<< endl;
int number;
cin >> number;
cin.ignore();
check.SetAccountNumber(number);
cout<<"Enter The customer name" <<endl;
string customer;
getline(cin, customer);
check.SetCustomerName(customer);
//get all deposits
cout << "How many deposits do you want to enter?" << endl;
int num;
cin >> num;
for (int i=1;i<=num;i++)
{
cout << "Enter Deposit :";
double dep=0;
cin>> dep;
check.Deposit(dep);
}

//get all Withdrawals
cout << "How many Withdrawals do you want to enter?" <<endl;
int num2;
cin >> num2;
for (int i=1;i<=num2;i++)
{
cout << "Enter Withdrawal :";
double wd=0;
cin >> wd;
check.Withdrawal(wd);
}
//get balance
cout<<"************************************"<<endl;
cout << "For checking Account " << check.GetAccountNumber ()
<<" belonging to " << check.GetCustomerName() << endl;
cout << "the current balance is " << check.GetBalance();

char c;
cin >> c;

}

Thursday, August 4, 2011

Methods--First Take

Methods are blocks of code that do something. Ideally you should break up your code into seperate methods, each of which does just one thing. This makes the program easier to debug and manage.

A basic method signature follows this pattern


[accessability][return type] [method name](parameters if any)
{
}

Here is an example of a simple method that returns nothing(void) and takes no parameters.


private void PrintName()
{
   Console.WriteLine("Enter your name");
   string name=Console.ReadLine();
   Console.WriteLine("Hello {0}",name);
}

Here is an example of a method that returns an integer and takes a parameter.


private int Cube(int x)
{
   return x*x*x;
}

A method that has a return type other than void must have a return statement.

Calling methods

Methods must be called to be run. Below is a brief program that uses the cube method and another void method called GetValues(). The program is heavily commented.


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

namespace CubeMethodex
{
    class Program
    {
        /// 
        /// this program demonstrates basic
        /// examples of breaking a program 
        /// into seperate methods
        /// 

      
        static void Main(string[] args)
        {
            //because main is static
            //you must initialize the class
            //to access its methods
            Program p = new Program();
            p.GetValues(); //call the method 
            p.PauseIt(); //call the method
        }

        //the private means the mehtod can only be 
        //accessed from within this class
        private void GetValues()
        {
            //prompt the user for a value
            Console.WriteLine("Enter an integer Value");
            //get the value and use the Parse method of int
            //to convert it to an integer
            int number = int.Parse(Console.ReadLine());
            //write the number {0}, then call the cube
            //method passing it the number
            //output the result returned by the
            // method {1}
            Console.WriteLine
             ("The cube of your number {0} is {1}", 
                number, Cube(number));
        }

        private int Cube(int x)
        {
            //the number in the previous method
            //is mapped to x
            //return the result as an int
            return x * x * x;
        }

        private void PauseIt()
        {
            //a method to pause the console
            //long enough to read it
            Console.WriteLine("Press any key to Exit");
            Console.ReadKey();
        }
    }
}

Notice how main calls GetValues() and the GetValues() method calls Cube() passing it the number provided by the user. Each method does one basic thing. If something goes wrong you know where to look.

Scope Basics

Scope is a term that refers to a variables accessibility and existence. While a variable is in scope it is stored in the computer's memory. Its value can be accessed and manipulated. When a variable goes out of scope it is erased from the computer's memory and its value can no longer be accessed or manipulated.

The easiest way to think about scope is that a variable has the scope of the block it is declared in.
Blocks are defined by opening and closing curly braces. A variable declared in the class block has class scope. It can be seen and used by any method in the class. A variable that has method scope can be seen and used by any command in the method. A variable that has block scope, such as in an if block or a loop,
can only be accessed from within that block.

Below is example of various scopes: (it is not a real program and won't run. It is just to show the levels of scope.)



class program
{
   int number; //class scope
  
   static void Main()
   {
      string name; //method scope

      number=0;//changing the class level variable

      while (number==0)
      {
         int counter=1; //block scope
      }

   } 

}

A couple of other things to note. If you name two variables the same, the one with the smaller scope takes precidence. That means if I have a class level variable named number, and I name a variable in one of my methods number, the variable is given method level scope for the duration of the method,

There are some exceptions to the general scope rule but we will deal with them as we encounter them.

General Principle

Give any variable the most limited scope possible, while still enabling it to do what it needs to do.

More on Algorithms

An Algorithm consists of the steps needed to accomplish a task in the order that they need to be processed.

Let's look at a simple algorithm for a program to calculate gas mileage. It still follows the pattern of Get a clear picture of the program's purpose. Determine the inputs and the outputs and the process needed to get from one to the other.

The inputs will be the beginning mileage, the end mileage and the number of gallons
The output will be miles per gallon.

The algoritm looks like this:

1) Get beginning mileage
2) Get ending mileage
3) Get gallons needed to refill the tank (gallons used)
4) Subtract beginning miles from ending miles to get miles traveled
5) Divide miles traveled by gallons used to get Miles per gallon
6) Output miles per gallon

You can also use a diagram to represent the algoritm if it makes it clearer. (These can be expecially useful as the algorithms grow more complex.)

Here is a UML diagram called an Activity diagram that shows the same process.

A Few Preliminary Definitions

Machine Language

Computers are very fast adding machines. Their operations consist of circuits open or closed, spots magnatized or not. These states are represented by 0 and 1. (O closed, 1 open) Everything that can be done on a computer can be done with 0's and 1's.

Programming languages

Programming in 0's and 1's is extremely tedious and prone to errors. To help, programmers first developed assembly language. It provides some key words and abreviations to act as memnonics for the machine languge. Later programmers started to develop what more complex programming languages such as Fortran and Cobol. In these language each command, such as "print" represented a whole batch of machine level commands. The language was processed by a compiler and turned back into machine language.

Fortran and Cobol were top-down languages. That means that you started at the top and wrote your code in one long list of commands until you were done. As computing grew more complex, this method of proved inadequate. It was difficult to trace errors and debug. It was also nearly impossible to reuse code for other projects.

The next generation of lanugages such as C allowed programmers to break the code into seperate functions. This was called Structural programming. A program consisted of any number of functions and each function did only one thing. this made it easier to locate errors and also to reuse code. This was a huge improvement, but as programs grew more complex, it still proved inadequate. A large, complex program could have thousands of functions. Keeping track of and organizing then could be nearly impossible.

This gave rise to the next set of languages such as C++, C# and Java. These languages use a Object Oriented structure. The code is still divided into functions but the functions are grouped into objects. These object represent the "natural" organization of the program. For instance a Point of Sale program would have objects like customer, item, inventory, sale, etc.

It is important to realize that the computer doesn't understand C# or any of the other languages any more than you probably do at this point. They must be compiled into machine language by another program called a compiler

Classes
Classes are part of any object oriented language. A class is an abstact defintion of an object. A customer, for instance, has a name, an address, perhaps a customer number. An object is a specific instance of a class, such as the customer Joe Smith.

Policy Management Report

Policy-Based Management

What is it?
New to SQL Server2008, allows you to define and enforce policies
A policy can force developers to follow certain guidelines.
Here are a couple of examples of policies (we’ll be setting these up in class)
All stored procedures must begin with the letters “Usp”
All tables must have primary keys

Policy management consists of four components:
Target
An object which can be managed.
An example is a stored procedure or a table
Facet
A predefined set of properties that can be managed
Condition
A condition is something that will be evaluated to either True of False. A condition can check one or more statements using and/or.
Using our example from above, all stored procedures must begin with the letters “Usp”.
Policy
A condition to be checked and enforced.

A policy has four evaluation modes:
On Demand
On Demand lets the admin check the policy and receive a list of violations
On Schedule
On Schedule lets the admin schedule the checking of a policy at specific intervals.
On Change - Log Only
On Change - Log makes an entry into the database log every time a change occurs that triggers a violation.
On Change - Prevent
On Change - Prevent prevents a change that would violate the policy.

The following SQL snippet displays a 1 if the table named ‘Person’ has a primary key and nothing if it doesn’t.

select 1
from sys.tables t
inner join sys.indexes i
on i.object_id = t.object_id
where i.is_primary_key = 1
and t.name = 'Person';

The following snippet evaluates to 1 if the table being evaluated has a primary key and to nothing if it doesn’t. This is entered in the field area.

The oprator is =
and the value is 1

The condition therefore returns true if the table being evaluated has a primary key and false if it doesn’t.

ExecuteSql('Numeric','select 1 from sys.tables t inner join sys.indexes i on i.object_id = t.object_id where i.is_primary_key = 1 and t.name = @@ObjectName')

Wednesday, August 3, 2011

First Program

Thinking Through the Process

We are going to demonstrate some of the basics of writing a C# program, or any program, for that matter. One of the most common questions is where to start.

The first thing you should do is get a good grasp on the basic purpose of the program. In our case this is simple. We are going to prompt the user for a name and then display the word "Hello" and the name. In many cases, though, where the program is complex, this can be difficult. It is essential though. You really can't write a program if you don't know very clearly what the program is meant to do.

Next you should identify the inputs. Inputs are pieces of data that the program will need to complete its purpose. Inputs can come from the user, from a file or a database or from another program. In our case the input will come from the user and consist of his or her name.

Thirdly you should look at the output. Output or those things the program produces as a result of its operations. Outputs can take the form of display on the screen, files, or inserts into a database. In our case the output is the word "Hello" plus the user's name.

The fourth thing you should consider is what the process is to transform the inputs into the outputs. You should list each of the steps in the order they need to occur. This list of steps is called an Algorithm. In our case the steps are

Prompt the user to enter their name
Concatenate the word Hello and the name
Display the phrase on the console

This is obviously very simple. Many algorithms are quite complex and long.

Here is the code:


using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;



namespace Hello

{

    class Program 

    {

        

        static void Main(string[] args)

        {

            string name; //variable to store name

            //prompt user

            Console.WriteLine("Please enter your name");

            //assign value from the console to the variable

            name = Console.ReadLine();

            //output the word Hello and the name. 

            //the {0} is a place holder for the variable name

            Console.WriteLine("Hello, {0}", name);



            //this is just to pause it

            Console.ReadKey();

        }

    }

}

Description of the Code

The using statements are there to include libraries of code. The System library, for instance, contains the class definition for "Console" which we use to get the input and to output the results.

Namespaces are used to group code that belongs together and to keep code separate that should be separate. By default the namespace is the name you gave to the project when you created it.

In C# all code must be in a class. That is part of its object oriented nature.

Main is a method in the class Program. It is a special method. Main is the starting point of any program. No program can have more than 1 Main. The static means it is loaded immediately into the computers memory. This is necessary because it is the starting point. We will talk more about static later. void means the method does not return anything. Again we will talk about this later. string[] args creates and array that can be used to pass into from the command line. We will not be using this.

Note the curly braces {}. Each block has its own pair of opening and closing curly braces. The namespace is a block, the class is a block and the main method is a block. Leaving out a curly brace or inserting an extra one is a common mistake that can keep your program from compiling.

string name; declares a variable of the type string called "name". A semicolon ; terminates the command. All commands in C# must be ended with a semicolon.

statements beginning with // are comments. I have commented what each line does.

Notice the dot in Console.WriteLine(). Console is an object that belongs to the .Net framework. The dot introduces a member of that object, in this case, a method "WriteLine." We will see this "dot notation" in every program.

Review of Process

Once again these are the steps to follow in creating any program:

1) Get a clear overview of the program's purpose
2) Identify the program's inputs
3) Identify the programs outputs
4) determine the steps required to get from the inputs to the outputs

Tuesday, August 2, 2011

Security Presentation

--Permissions
USE master
GO
CREATE LOGIN TestLogin WITH PASSWORD = ‘p@ssw0rd1', CHECK_POLICY = ON, CHECK_EXPIRATION = ON
GO
USE AdventureWorks
GO
CREATE USER TestLogin FOR LOGIN TestLogin
GO
USE AdventureWorks
GO
CREATE USER TestUser WITHOUT LOGIN
GO

--Encryption

USE AdventureWorks
GO

--Review the symmetric keys in the database
SELECT * FROM sys.symmetric_keys
GO

--If you do not already have a database master key created, execute the following code
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password111'
GO

--Different hash algorithms produce different hash values
DECLARE @HashValue varchar(100)
SELECT @HashValue = 'SQL Server'
SELECT HashBytes('MD5', @HashValue)
SELECT @HashValue = 'SQL Server'
SELECT HashBytes('SHA1', @HashValue)
GO

--Hash values are case sensitive
DECLARE @HashValue varchar(100)
SELECT @HashValue = 'sql'
SELECT HashBytes('SHA1', @HashValue)
SELECT @HashValue = 'SQL'
SELECT HashBytes('SHA1', @HashValue)
GO

CREATE TABLE SalaryHashes
(Salary INT NOT NULL,
MD2Hash VARBINARY(500) NOT NULL,
MD4Hash VARBINARY(500) NOT NULL,
MD5Hash VARBINARY(500) NOT NULL,
SHAHash VARBINARY(500) NOT NULL,
SHA1Hash VARBINARY(500) NOT NULL)

DECLARE @salary INT,
@salarylimit INT

SET @salary = 10000
SET @salarylimit = 300000

WHILE @salary <= @salarylimit
BEGIN
INSERT INTO SalaryHashes
(Salary, MD2Hash, MD4Hash, MD5Hash, SHAHash, SHA1Hash)
SELECT @salary, Hashbytes('MD2',cast(@salary as varchar(6))), Hashbytes('MD4',cast(@salary as varchar(6))), Hashbytes('MD5',cast(@salary as varchar(6))),
Hashbytes('SHA',cast(@salary as varchar(6))), Hashbytes('SHA1',cast(@salary as varchar(6)))

SET @salary = @salary + 10
END

SELECT Salary, MD2Hash, MD4Hash, MD5Hash, SHAHash, SHA1Hash
FROM SalaryHashes
GO

/* End of hash encryption */
/* Start symmetric keys */

CREATE SYMMETRIC KEY MySymmetricKey WITH ALGORITHM = RC4
ENCRYPTION BY PASSWORD = 'password111'
GO

SELECT * FROM sys.symmetric_keys
GO

CREATE TABLE SymmetricKeyDemo
(ID int IDENTITY(1,1),
PlainText varchar(30) NOT NULL,
EncryptedText varbinary(80) NOT NULL)
GO

--Symmetric key must be opened before being used
OPEN SYMMETRIC KEY MySymmetricKey DECRYPTION BY PASSWORD = 'password111'
GO

INSERT INTO SymmetricKeyDemo
(PlainText, EncryptedText)
VALUES('SQL Server', EncryptByKey(Key_GUID('MySymmetricKey'),'SQL Server'))
GO

SELECT ID, PlainText, EncryptedText, cast(DecryptByKey(EncryptedText) AS varchar(30))
FROM SymmetricKeyDemo
GO

CLOSE SYMMETRIC KEY MySymmetricKey
GO

/* End of symmetric keys */
/* Start of certificates */

CREATE TABLE CertificateDemo
(ID int IDENTITY(1,1),
PlainText varchar(30) NOT NULL,
EncryptedText varbinary(500) NOT NULL)
GO

CREATE CERTIFICATE MyCert AUTHORIZATION dbo
WITH SUBJECT = 'Test certificate'
GO

SELECT * FROM sys.certificates
GO

INSERT INTO CertificateDemo
(PlainText, EncryptedText)
VALUES('SQL Server',EncryptByCert(Cert_ID('MyCert'), 'SQL Server'))
GO

SELECT ID, PlainText, EncryptedText, CAST(DecryptByCert(Cert_Id('MyCert'),
EncryptedText) AS varchar(max))
FROM CertificateDemo
GO

/* End of cerficates */

Full Text Presentation Script

ACTIVATE FILESTREAM
ON THE SERVER, RIGHT CLICK AND GO TO PROPERTIES, THEN ADVANCED, MAKE
FILESTREAM FULLY ADVANCED.
ALSO OPEN SQL SERVER CONFIGURATION MANAGER, GO TO THE PROPERTIES FOR
THE SERVER, THEN THE FILESTREAM TAB AND ENABLE FILESTREAM.
CH 4 EX. 1 TO CREATE NEEDED DATABASES

CREATE DATABASE SQL2008SBS ON PRIMARY
( NAME = N'SQL2008SBS', FILENAME = N'C:\Program Files\Microsoft SQL
Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\SQL2008SBS.mdf' , SIZE = 3MB
, MAXSIZE = UNLIMITED, FILEGROWTH = 10% ),
FILEGROUP FG1 DEFAULT ( NAME = N'SQL2008SBSFG1_Dat1', FILENAME =
N'C:\Program Files\Microsoft SQL
Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\SQL2008SBS_1.ndf' , SIZE =
2MB , MAXSIZE = UNLIMITED, FILEGROWTH = 2MB),
(NAME = N'SQL2008SBSFG1_Dat2', FILENAME =
N'C:\Program Files\Microsoft SQL
Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\SQL2008SBS_2.ndf' , SIZE =
2MB , MAXSIZE = UNLIMITED, FILEGROWTH = 2MB),
(NAME = N'SQL2008SBSFG1_Dat3', FILENAME =
N'C:\Program Files\Microsoft SQL
Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\SQL2008SBS_3.ndf' , SIZE =
2MB , MAXSIZE = UNLIMITED, FILEGROWTH = 2MB)
LOG ON
( NAME = N'SQL2008SBS_Log', FILENAME = N'C:\Program Files\Microsoft
SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\SQL2008SBS.ldf' , SIZE =
2MB , MAXSIZE = UNLIMITED , FILEGROWTH = 10MB )
GO
--done
CREATE DATABASE SQL2008SBSFS ON PRIMARY
( NAME = N'SQL2008SBSFS', FILENAME = N'C:\Program Files\Microsoft SQL
Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\SQL2008SBSFS.mdf' , SIZE =
3MB , MAXSIZE = UNLIMITED, FILEGROWTH = 10% ),
FILEGROUP DocumentFileStreamGroup CONTAINS FILESTREAM
( NAME = N'FileStreamDocuments', FILENAME = N'C:\Program
Files\Microsoft SQL
Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\SQL2008SBSFS' )
LOG ON
( NAME = N'SQL2008SBSFS_Log', FILENAME = N'C:\Program Files\Microsoft
SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\SQL2008SBSFS.ldf' , SIZE
= 2MB , MAXSIZE = UNLIMITED , FILEGROWTH = 10MB )
GO
--done

ALTER DATABASE SQL2008SBS
ADD FILEGROUP FullTextCatalog
GO
--done
ALTER DATABASE SQL2008SBSFS
ADD FILEGROUP FullTextCatalog
GO
--done
ALTER DATABASE AdventureWorks
ADD FILEGROUP FullTextCatalog
GO
--done
ALTER DATABASE SQL2008SBS
ADD FILE (NAME = N'SQL2008SBSFT', FILENAME = N'C:\Program
Files\Microsoft SQL
Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\SQL2008SBSFT.ndf')
TO FILEGROUP FullTextCatalog
GO
--done
ALTER DATABASE SQL2008SBSFS
ADD FILE (NAME = N'SQL2008SBSFSFT', FILENAME = N'C:\Program
Files\Microsoft SQL
Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\SQL2008SBSFSFT.ndf')
TO FILEGROUP FullTextCatalog
GO
--done
ALTER DATABASE AdventureWorks
ADD FILE (NAME = N'AdventureWorksFT', FILENAME = N'C:\Program
Files\Microsoft SQL
Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\AdventureWorksFT.ndf')
TO FILEGROUP FullTextCatalog
GO
--done

USE SQL2008SBS
GO
CREATE FULLTEXT CATALOG ProductDescription
ON FILEGROUP FullTextCatalog
GO
--done

USE SQL2008SBSFS
GO
CREATE FULLTEXT CATALOG ProductDocument
ON FILEGROUP FullTextCatalog
GO
--done

USE AdventureWorks
GO
CREATE FULLTEXT CATALOG Description
ON FILEGROUP FullTextCatalog
GO
--done (had to add)

/* RUN CH.5 scripts from book */

USE SQL2008SBS
GO
Select * from Products.Product
CREATE FULLTEXT INDEX ON Products.Product(ProductDescription)
KEY INDEX pk_product
ON ProductDescription
WITH CHANGE_TRACKING = AUTO
GO
--done

USE SQL2008SBSFS
GO
CREATE FULLTEXT INDEX ON Products.ProductDocument
(Document TYPE COLUMN DocumentType)
KEY INDEX pk_productdocument
ON ProductDocument
WITH CHANGE_TRACKING = AUTO
GO
--done
USE AdventureWorks
GO
Select * from Production.ProductDescription
CREATE FULLTEXT INDEX ON Production.ProductDescription(Description)
KEY INDEX PK_ProductDescription_ProductDescriptionID
on [Description]
WITH CHANGE_TRACKING = AUTO
GO

--Freetext
SELECT ProductDescriptionID, Description
FROM Production.ProductDescription
WHERE FREETEXT(Description,N'safety component')
GO
SELECT ProductDescriptionID, Description
FROM Production.ProductDescription
WHERE FREETEXT(Description,N'bike')
GO

SELECT a.ProductDescriptionID, a.Description, b.*
FROM Production.ProductDescription a
INNER JOIN FREETEXTTABLE(Production.ProductDescription, Description,
N'bike') b ON a.ProductDescriptionID = b.[Key]
ORDER BY b.[Rank]
GO

--Contains
SELECT ProductDescriptionID, Description
FROM Production.ProductDescription
WHERE CONTAINS(Description,N'bike')
GO

SELECT ProductDescriptionID, Description
FROM Production.ProductDescription
WHERE CONTAINS(Description,N'"bike*"')
GO

SELECT ProductDescriptionID, Description
FROM Production.ProductDescription
WHERE CONTAINS(Description,N' FORMSOF (INFLECTIONAL,ride) ')
GO

SELECT ProductDescriptionID, Description
FROM Production.ProductDescription
WHERE CONTAINS(Description,N' FORMSOF (THESAURUS,metal) ')
GO

SELECT a.ProductDescriptionID, a.Description, b.*
FROM Production.ProductDescription a INNER JOIN
CONTAINSTABLE(Production.ProductDescription, Description,
N'bike NEAR performance') b ON a.ProductDescriptionID = b.[Key]
ORDER BY b.[Rank]
GO

SELECT a.ProductDescriptionID, a.Description, b.*
FROM Production.ProductDescription a INNER JOIN
CONTAINSTABLE(Production.ProductDescription, Description,
N'ISABOUT (performance WEIGHT (.8), comfortable WEIGHT (.6),
smooth WEIGHT (.2) , safe WEIGHT (.5), competition WEIGHT (.5))', 10)
b ON a.ProductDescriptionID = b.[Key]
ORDER BY b.[Rank] DESC
GO

USE AdventureWorks
GO

--Bonus script for populating a full text index if you
-- created it with change tracking and manual update.
CREATE PROCEDURE dbo.asp_fulltextcrawl @databasename sysname,
@schema sysname, @tablename sysname
AS
DECLARE @cmd NVARcHAR(MAX)

SET @cmd = 'ALTER FULLTEXT INDEX ON ' + @databasename + '.' +
@schema + '.' + @tablename + ' START UPDATE POPULATION'

--EXEC master.dbo.sp_executesql @cmd
SELECT @cmd
RETURN
GO

exec dbo.asp_fulltextcrawl 'SQL2008SBS','Products','Product'
GO

(5-3? 1,4?)-jg
5-14

USE SQL2008SBS
GO

CREATE SCHEMA HumanResources AUTHORIZATION dbo
GO

CREATE TABLE Customers.Customer
(CustomerID INT IDENTITY(1,1),
CompanyName VARCHAR(50) NULL,
FirstName VARCHAR(50) NULL,
LastName VARCHAR(50) NULL,
ModifiedDate DATE NOT NULL CONSTRAINT df_modifieddate
DEFAULT GETDATE(),
CONSTRAINT pk_customer PRIMARY KEY (CustomerID))
GO

CREATE TABLE Customers.CustomerAddress
(AddressID INT IDENTITY(1,1),
CustomerID INT NOT NULL,
AddressTypeID INT NOT NULL,
AddressLine1 VARCHAR(50) NOT NULL,
AddressLine2 VARCHAR(50) SPARSE NULL,
AddressLine3 VARCHAR(50) SPARSE NULL,
City VARCHAR(50) NOT NULL,
StateProvinceID INT NULL,
CountryID INT NULL,
CONSTRAINT pk_customeraddress PRIMARY KEY (AddressID))
GO

CREATE TABLE Orders.OrderHeader
(OrderID INT IDENTITY(1,1),
CustomerID INT NOT NULL,
OrderDate DATE NOT NULL CONSTRAINT df_orderdate DEFAULT GETDATE(),
SubTotal MONEY NOT NULL CONSTRAINT ck_subtotal CHECK
(SubTotal > 0),
TaxAmount MONEY NOT NULL CONSTRAINT ck_taxamount CHECK
(TaxAmount >= 0),
ShippingAmount MONEY NOT NULL CONSTRAINT ck_shippingamount
CHECK (ShippingAmount >= 0),
GrandTotal AS (SubTotal + TaxAmount + ShippingAmount),
FinalShipDate DATE NULL,
CONSTRAINT pk_orderheader PRIMARY KEY (OrderID))
GO

CREATE TABLE Orders.OrderDetail
(OrderDetailID INT IDENTITY(1,1),
OrderID INT NOT NULL,
SKU CHAR(10) NOT NULL,
Quantity INT NOT NULL CHECK (Quantity > 0),
UnitPrice MONEY NOT NULL CHECK (UnitPrice > 0),
ShipDate DATE NULL,
CONSTRAINT pk_orderdetail PRIMARY KEY (OrderDetailID))
GO

CREATE TABLE Products.Product
(ProductID INT IDENTITY(1,1),
SubcategoryID INT NOT NULL,
ProductName VARCHAR(50) NOT NULL,
ProductCost MONEY NOT NULL CHECK (ProductCost > 0),
ListPrice MONEY NOT NULL CHECK (ListPrice > 0),
ProductMargin AS (ListPrice - ProductCost),
ProductDescription XML NULL,
CONSTRAINT pk_product PRIMARY KEY (ProductID))
GO

CREATE TABLE HumanResources.Employee
(EmployeeID INT IDENTITY(1,1),
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL,
JobTitle VARCHAR(50) NOT NULL,
BirthDate DATE NOT NULL,
HireDate DATE NOT NULL,
CONSTRAINT pk_employee PRIMARY KEY (EmployeeID))
GO

CREATE TABLE HumanResources.EmployeeAddress
(AddressID INT IDENTITY(1,1),
EmployeeID INT NOT NULL,
AddressTypeID INT NOT NULL,
AddressLine1 VARCHAR(50) NOT NULL,
AddressLine2 VARCHAR(50) SPARSE NULL,
AddressLine3 VARCHAR(50) SPARSE NULL,
City VARCHAR(50) NOT NULL,
StateProvinceID INT NULL,
CountryID INT NULL,
CONSTRAINT pk_employeeaddress PRIMARY KEY (AddressID))
GO

CREATE TABLE LookupTables.AddressType
(AddressTypeID INT IDENTITY(1,1),
AddressType VARCHAR(20) NOT NULL UNIQUE,
CONSTRAINT pk_addresstype PRIMARY KEY(AddressTypeID))
GO

CREATE TABLE LookupTables.Country
(CountryID INT IDENTITY(1,1),
CountryName VARCHAR(70) NOT NULL UNIQUE,
CONSTRAINT pk_country PRIMARY KEY(CountryID))
GO

CREATE TABLE LookupTables.StateProvince
(StateProvinceID INT IDENTITY(1,1),
CountryID INT NOT NULL,
StateProvince VARCHAR(50) NOT NULL UNIQUE,
StateProvinceAbbrev CHAR(2) NOT NULL,
CONSTRAINT pk_stateprovince PRIMARY KEY(StateProvinceID))
GO

CREATE TABLE LookupTables.ProductCategory
(CategoryID INT IDENTITY(1,1),
Category VARCHAR(20) NOT NULL UNIQUE,
CONSTRAINT pk_productcategory PRIMARY KEY(CategoryID))
GO

CREATE TABLE LookupTables.ProductSubCategory
(SubcategoryID INT IDENTITY(1,1),
CategoryID INT NOT NULL,
SubcategoryName VARCHAR(20) NOT NULL UNIQUE,
CONSTRAINT pk_productsubcategory PRIMARY KEY(SubcategoryID))
GO

ALTER TABLE Customers.CustomerAddress
ADD CONSTRAINT fk_customertocustomeraddress FOREIGN KEY (CustomerID)
REFERENCES Customers.Customer (CustomerID)
GO

ALTER TABLE Customers.CustomerAddress
ADD CONSTRAINT fk_customeraddresstoaddresstype FOREIGN KEY (AddressTypeID)
REFERENCES LookupTables.AddressType (AddressTypeID)
GO

ALTER TABLE Customers.CustomerAddress
ADD CONSTRAINT fk_customeraddresstostateprovince FOREIGN KEY
(StateProvinceID)
REFERENCES LookupTables.StateProvince (StateProvinceID)
GO

ALTER TABLE Customers.CustomerAddress
ADD CONSTRAINT fk_customeraddresstocountry FOREIGN KEY (CountryID)
REFERENCES LookupTables.Country (CountryID)
GO

ALTER TABLE Orders.OrderHeader
ADD CONSTRAINT fk_customertoorderheader FOREIGN KEY (CustomerID)
REFERENCES Customers.Customer (CustomerID)
GO

ALTER TABLE Orders.OrderDetail
ADD CONSTRAINT fk_orderheadertoorderdeatils FOREIGN KEY (OrderID)
REFERENCES Orders.OrderHeader (OrderID)
GO

ALTER TABLE Products.Product
ADD CONSTRAINT fk_producttosubcategory FOREIGN KEY (SubcategoryID)
REFERENCES LookupTables.ProductSubCategory (SubcategoryID)
GO

ALTER TABLE HumanResources.EmployeeAddress
ADD CONSTRAINT fk_employeetoemployeeaddress FOREIGN KEY (EmployeeID)
REFERENCES HumanResources.Employee (EmployeeID)
GO

ALTER TABLE HumanResources.EmployeeAddress
ADD CONSTRAINT fk_employeeaddresstoaddresstype FOREIGN KEY (AddressTypeID)
REFERENCES LookupTables.AddressType (AddressTypeID)
GO

ALTER TABLE HumanResources.EmployeeAddress
ADD CONSTRAINT fk_employeeaddresstostateprovince FOREIGN KEY
(StateProvinceID)
REFERENCES LookupTables.StateProvince (StateProvinceID)
GO

ALTER TABLE HumanResources.EmployeeAddress
ADD CONSTRAINT fk_employeeaddresstocountry FOREIGN KEY (CountryID)
REFERENCES LookupTables.Country (CountryID)
GO

ALTER TABLE LookupTables.StateProvince
ADD CONSTRAINT fk_countrytostateprovince FOREIGN KEY (CountryID)
REFERENCES LookupTables.Country(CountryID)
GO

ALTER TABLE LookupTables.ProductSubCategory
ADD CONSTRAINT fk_productcategorytosubcategory FOREIGN KEY (CategoryID)
REFERENCES LookupTables.ProductCategory(CategoryID)
GO

USE SQL2008SBSFS
GO

CREATE TABLE Products.ProductDocument
(DocumentID UNIQUEIDENTIFIER ROWGUIDCOL UNIQUE,
ProductID INT NOT NULL,
DocumentType VARCHAR(20) NOT NULL,
Document VARBINARY(MAX) FILESTREAM NULL,
CONSTRAINT pk_productdocument PRIMARY KEY(DocumentID))
GO

CREATE FULL TEXTS CATALOGS
17-1

ALTER DATABASE SQL2008SBS
ADD FILEGROUP FullTextCatalog
GO
ALTER DATABASE SQL2008SBSFS
ADD FILEGROUP FullTextCatalog
GO
ALTER DATABASE SQL2008SBS
ADD FILE (NAME = N'SQL2008SBSFT', FILENAME = N'C:\Program
Files\Microsoft SQL
Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\SQL2008SBSFT.ndf')
TO FILEGROUP FullTextCatalog
GO
ALTER DATABASE SQL2008SBSFS
ADD FILE (NAME = N'SQL2008SBSFSFT', FILENAME = N'C:\Program
Files\Microsoft SQL
Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\SQL2008SBSFSFT.ndf')
TO FILEGROUP FullTextCatalog
GO

17-2

USE SQL2008SBS
GO
CREATE FULLTEXT CATALOG ProductDescription
ON FILEGROUP FullTextCatalog
GO
USE SQL2008SBSFS
GO
CREATE FULLTEXT CATALOG ProductDocument
ON FILEGROUP FullTextCatalog
GO

CREATE FULL TEXT INDICES
17-3

USE SQL2008SBS
GO
CREATE FULLTEXT INDEX ON Products.Product(ProductDescription)
KEY INDEX pk_product
ON ProductDescription
WITH CHANGE_TRACKING = AUTO
GO

USE SQL2008SBSFS
GO
CREATE FULLTEXT INDEX ON Products.ProductDocument
(Document TYPE COLUMN DocumentType)
KEY INDEX pk_productdocument
ON ProductDocument
WITH CHANGE_TRACKING = AUTO
GO

show freetext exampl;es
17-4
USE AdventureWorks
GO
CREATE FULLTEXT INDEX ON Production.ProductDescription(Description)
KEY INDEX PK_ProductDescription_ProductDescriptionID
WITH CHANGE_TRACKING = AUTO
GO

--Freetext
SELECT ProductDescriptionID, Description
FROM Production.ProductDescription
WHERE FREETEXT(Description,N'safety component')
GO
SELECT ProductDescriptionID, Description
FROM Production.ProductDescription
WHERE FREETEXT(Description,N'bike')
GO

SELECT a.ProductDescriptionID, a.Description, b.*
FROM Production.ProductDescription a
INNER JOIN FREETEXTTABLE(Production.ProductDescription, Description,
N'bike') b ON a.ProductDescriptionID = b.[Key]
ORDER BY b.[Rank]
GO

--Contains
SELECT ProductDescriptionID, Description
FROM Production.ProductDescription
WHERE CONTAINS(Description,N'bike')
GO

SELECT ProductDescriptionID, Description
FROM Production.ProductDescription
WHERE CONTAINS(Description,N'"bike*"')
GO

SELECT ProductDescriptionID, Description
FROM Production.ProductDescription
WHERE CONTAINS(Description,N' FORMSOF (INFLECTIONAL,ride) ')
GO

SELECT ProductDescriptionID, Description
FROM Production.ProductDescription
WHERE CONTAINS(Description,N' FORMSOF (THESAURUS,metal) ')
GO

SELECT a.ProductDescriptionID, a.Description, b.*
FROM Production.ProductDescription a INNER JOIN
CONTAINSTABLE(Production.ProductDescription, Description,
N'bike NEAR performance') b ON a.ProductDescriptionID = b.[Key]
ORDER BY b.[Rank]
GO

SELECT a.ProductDescriptionID, a.Description, b.*
FROM Production.ProductDescription a INNER JOIN
CONTAINSTABLE(Production.ProductDescription, Description,
N'ISABOUT (performance WEIGHT (.8), comfortable WEIGHT (.6),
smooth WEIGHT (.2) , safe WEIGHT (.5), competition WEIGHT (.5))', 10)
b ON a.ProductDescriptionID = b.[Key]
ORDER BY b.[Rank] DESC
GO

17-x bonus
USE DBAdmin
GO

--Bonus script for populating a full text index if you
-- created it with change tracking and manual update.
CREATE PROCEDURE dbo.asp_fulltextcrawl @databasename sysname,
@schema sysname, @tablename sysname
AS
DECLARE @cmd NVARcHAR(MAX)

SET @cmd = 'ALTER FULLTEXT INDEX ON ' + @databasename + '.' +
@schema + '.' + @tablename + ' START UPDATE POPULATION'

--EXEC master.dbo.sp_executesql @cmd
SELECT @cmd
RETURN
GO

exec dbo.asp_fulltextcrawl 'SQL2008SBS','Products','Product'
GO

Asp Net with LINQ and logins

In SQL Server

So we created a login table for the Employees:

Create Table Employee.EmployeeLogin
(
EmployeeLoginKey int identity(1,1) primary key,
EmployeeID int Foreign key references Employee(EmployeeID),
username nvarchar(50) not null,
passwd nvarchar(20) not null,
Constraint pass_unique unique(username, passwd)
)

Then we created a stored procedure to process the login and return the employeeID. Note that the actual login to the server and the database is EmployeeLogin that belongs to the Employee Schema. The table is used after the actual login.

Here is the procedure

Create proc Employee.usp_Employeelogin
@user nvarchar(50),
@pass nvarchar(20)
As

if exists
(Select EmployeeID from EmployeeLogin
where username=@user
and passwd=@pass)
Begin
Select EmployeeID from EmployeeLogin
where username=@user
and passwd=@pass
End

Then we added some records to the table:

Insert into Employee.EmployeeLogin(EmployeeID, username, passwd)
Values(1,'mlewis','pass'),
(2, 'ttanner','pass'),
(3,'jpatterson','pass'),
(4,'mbrown','pass')

The we went into the security properties for the EmployeeLogin and granted Select permissions on the EmployeeLogin table and Exec permissions on the the stored procedure.

In Visual Studio

In the ASP.Net page we added a login control. Here is the source for Default.aspx. (The source code also contains a lot of formatting info for the login control)

<%@ 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>
<asp:Login ID="Login1" runat="server" BackColor="#EFF3FB" BorderColor="#B5C7DE"
BorderPadding="4" BorderStyle="Solid" BorderWidth="1px" Font-Names="Verdana"
Font-Size="0.8em" ForeColor="#333333" Height="157px" Width="338px">
<InstructionTextStyle Font-Italic="True" ForeColor="Black" />
<LoginButtonStyle BackColor="White" BorderColor="#507CD1" BorderStyle="Solid"
BorderWidth="1px" Font-Names="Verdana" Font-Size="0.8em" ForeColor="#284E98" />
<TextBoxStyle Font-Size="0.8em" />
<TitleTextStyle BackColor="#507CD1" Font-Bold="True" Font-Size="0.9em"
ForeColor="White" />
</asp:Login>
</div>
</form>
</body>
</html>

Next we added a new Data connection in the server explorer which connected to the database using the EmployeeLogin. Then we added a new LINQ to SQL designer and drug the EmployeeLOgin table into the main part of the designer and the usp_EmployeeLogin procedure into the right pane of the designer. This created a method that corresponded with the procedure.

The first thing that we do is call the procedure and get the EmployeeID. Then we write it to a Session variable. Session variables are stored on the server and are unique to each users session.

Here is the code behind using the LINQ Data Context created by the designer:

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
{
protected void Page_Load(object sender, EventArgs e)
{

}
protected void Login1_Authenticate(object sender, AuthenticateEventArgs e)
{
DataClassesDataContext dc = new DataClassesDataContext();
var valid= dc.usp_Employeelogin(Login1.UserName, Login1.Password);
foreach (var v in valid)
{
if (v.EmployeeID != null)
{
e.Authenticated = true;
Session["EID"] = v.EmployeeID;
Response.Redirect("Default2.aspx?EmpID=" + v.EmployeeID.ToString());
}
}
}
}

If the employee login is successful he or she is redirected to the next page where all the login information is shown. the login information is filtered by the employeeId stored in the session.

the code is placed in an if statement to make sure the session variable exists. IF it doesn't the user is redirected back to the login screen.

Here is the source for 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>
<h2>Page Two</h2>
<p>
<asp:Label ID="Label1" runat="server" Text="Employee Login Key: "></asp:Label>
<asp:Label ID="lblLoginKey" runat="server" Text=""></asp:Label><br />

<asp:Label ID="Label2" runat="server" Text="Employee ID: "></asp:Label>
<asp:Label ID="lblID" runat="server" Text=""></asp:Label><br />

<asp:Label ID="Label3" runat="server" Text="UserName: "></asp:Label>
<asp:Label ID="lblUserName" runat="server" Text=""></asp:Label><br />

<asp:Label ID="Label4" runat="server" Text="Password: "></asp:Label>
<asp:Label ID="lblPassword" runat="server" Text=""></asp:Label><br />
</p>
</div>
</form>
</body>
</html>

Here is the code behind

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)
{
if (Session["EID"] != null)
{
int EmpID = (int)Session["EID"];

DataClassesDataContext dc = new DataClassesDataContext();
var empInfo = from emp in dc.EmployeeLogins
where emp.EmployeeID == EmpID
select new
{
emp.EmployeeLoginKey,
emp.EmployeeID,
emp.username,
emp.passwd
};

foreach (var v in empInfo)
{
lblID.Text = v.EmployeeID.ToString();
lblLoginKey.Text = v.EmployeeLoginKey.ToString();
lblPassword.Text = v.passwd.ToString();
lblUserName.Text = v.username.ToString();

}
}
else
{
Response.Redirect("Default.aspx");
}
}
}

Monday, August 1, 2011

EmployeeDepartment Classes

So I got everything to work finally. What I have here is a slightly simplified version of what we were working on class.

So here is the Employee class. It is pretty much the same as what we were doing, except fewer fields and gets and sets.

Here is the header:

#pragma once
#include

using std::string;

class Employee
{
public:
Employee(void);
~Employee(void);

//gets
string GetName();
string GetHireDate();
//sets
void SetName(string, string);
void SetHireDate(string);

private:
string lastname;
string firstname;
string hireDate;
};

Here is the cpp file

#include "Employee.h"

Employee::Employee(void)
{
}

Employee::~Employee(void)
{
}

//gets

string Employee::GetName()
{
return lastname + ", " + firstname;
}

string Employee::GetHireDate()
{
return hireDate;
}

//sets

void Employee::SetName(string fName, string lName)
{
lastname=lName;
firstname=fName;
}

void Employee::SetHireDate(string hDate)
{
hireDate=hDate;
}

For the Department Class, notice the AddEmployee method and the declaration of the EmployeeList array.

The AddEmployee now takes two arguments a pointer to an object of type Employee and an integer counter. The counter is to say which array index the employee being passed should be added to.

The declaration of the EmployeeList is now also a pointer. The array is empty. This generates a warning but not an error. The constructor for the Department class takes in an int which we will use for the size of a dynamically initialized EmployeeList.

Here is the header file:

#pragma once
#include "Employee.h"

class Department
{
public:
Department(int);
~Department(void);
void AddEmployee(Employee * e, int counter);
string GetEmployee();

private:
int counter;
int size;
void InitializeArray();
Employee * EmployeeList[];

};

Here is the cpp file. Notice how the array is initialized.

#include "Department.h"

Department::Department(int size)
{
this->size=size;
//counter=0;
InitializeArray();
}

Department::~Department(void)
{

}

void Department::InitializeArray()
{
*EmployeeList = new Employee[size];
}

void Department::AddEmployee(Employee *e,int counter)
{

EmployeeList[counter]=e;

}

string Department::GetEmployee()
{
string departmentMember="";
for(int i=0;i < size;i++)
{
departmentMember += EmployeeList[i]->GetName() + "\t" + EmployeeList[i]->GetHireDate() + "\n";
}

return departmentMember;
}

Now here is the program file. I used the "new" key word to dynamically initialize the classes.

#include <iostream>
#include "Department.h"
#include "Employee.h"

void Display();

using namespace std;

int main()
{
Display();
char c;
cin >>c;
}

void Display()
{
int number=0;
cout << "Enter how many employees you want to enter" <<endl;
cin >> number;
cin.ignore();

Department * dept = new Department(number);

for (int i=0; i<number;i++)
{
Employee * emp=new Employee;
cout << "Enter First Name" << endl;
string fname;
getline(cin, fname);
cout << "Enter last name" << endl;
string lname;
getline(cin, lname);
emp->SetName(fname, lname);
cout << "Enter Hire Date" <<endl;
string hdate;
getline(cin, hdate);
emp->SetHireDate(hdate);

dept->AddEmployee(emp, i);

}

cout << "the department includes the following employees" << endl;
cout << "****************************************" << endl;

cout << dept->GetEmployee();

//delete dept;
}

Here is something I got off the web for converting doubles to strings. The c1 and c2 are double variables.

1.Use a stringstream:

std::ostringstream s; s << "(" << c1 << ", " << c2 << ")";
storedCorrect[count] = s.str()