ADO Example

ADO.Net consists of a set of objects that help you connect to, retrieve and manipulate data from databases. It the most code intensive way to do these tasks (LINQ and ADO Data Entities do most of the same things with much less code) but it does give you the most control over all the aspects of your data.

This example may seem complex, but I will guide you through it, and your assignment will parallel it very closesly.

Preparing SQL Server

For this example we will use the CommunityAssist database. We will have to do some preparation first. For one thing, CommunityAssist doesn't have a login table. First we will add one. We are going to do the passwords in plain text--a very bad idea--but there are enough complexities in this example for now. Ask me later and I will show you how to hash passwords both in Visual Studio and SQL Server.

The easiest way to do this is to open the SQL Server Management Studio and open a new query. Then enter this code.

---

use CommunityAssist

Create table DonorLogin
(
 loginId int identity(1,1) primary key,
 PersonKey int Foreign Key references Person(personKey),
 LastName nvarchar(255),
 DonorPassword nvarchar(255)
)
Go
Insert into DonorLogin(PersonKey, LastName, DonorPassword)
Select p.PersonKey, LastName, substring(LastName, 1,1) + 'pass' 
From Person p
inner join Donation d
on p.PersonKey=d.PersonKey

---

You can select from DonorLogin to see what the table contains

Now we need to make some additional adjustments to the Database and the Server. We are going to add two new SQL Server Logins and then we have to change the security mode of the server to accept both SQL Server and Windows logins.

Enter the following SQL

---

Create login DonorsLogin with password='P@ssw0rd1', default_database=CommunityAssist
Create user DonorsLogin for Login DonorsLogin
Grant Select on DonorLogin to DonorsLogin

Create Login RegisteredDonorsLogin with password='P@ssw0rd1', default_database=CommunityAssist
create user RegisteredDonorsUser for Login RegisteredDonorsLogin
Grant Select on Person to RegisteredDonorsUser
Grant Select on Donation to RegisteredDonorsUser

Now we need to change the Server authentication mode.

1. In SQL Server Management ServerRight Click on the Server and select properties from the context menu.
2. click Security under select a page.
3. Click the radio button by SQL Server and Windows Authentication mode.
4. Click OK and then OK to the dialog that these changes will not take effect until the server is restarted.
5. Right click on the server again. Choose Restart. Say OK to all the dialog boxes..
6. When the server is restarted you can close the SQL Server Management Studio.

Writing the ADO Code

Open Visual Studio and create a new empty web site. Add Two forms:Default.aspx and Default2.aspx. Ignore them for now.

Add a class called "DonorLoginClass." Click ok on the dialog box that says you need to put it in an "app_code" folder.

First add these two using statements. They call the ADO libraries. The second statement calls the SqlClient library which is specifically designed to work with SQL Server.

In the class we will declare one private field of a SqlConnection type. A SqlConnection is an ADO object that manages connections to the database.

It takes a connection string as an argument. We will initialize it in the constructor.

Now we will create a public method that will check to see if the login credentials match the values in the database. This method uses the SqlCommand object which is used to pass SQL to the database, and the SqlDataReader object which can be used to read data from the database. The method returns the personkey of the person who is logging in as an integer.

Now open the source view for Default1.apsx. We will add a custom login form. Here is the html, asp code:

Now double click the button in design view to get the default click event. We will add the following code to call the login class and confirm the values. If the login is good we will store the personkey in a Session variable and redirect to Default2 , if not we will put a message in the label.

I am going to put a H1 heading saying welcome on Default2. Now select Default.aspx in the Solution explorer and hit run

Now we will add another class called GetDonor. It has two public methods. The first retrieves the name of the donor and the second gets a DataSet of all the donor's donations. A dataset is an ADO object that contains tables. we will attach the DataSet as a data source for a gridview on the web form. Here is the complete code for the class:

Remember you also have to add the using statements for Data and SqlClient

Now we move to Default2. Add a label and a GridView. (the GridView is in the Data Section of the toolbox.)

We are going to use the Form Load event. First we will check to make sure that the session variable exists, if not we will send it back to the login. Next we will call the get donor class methods to get data to populate or form.

You will also have to add an using statement for System.Data to this page.

Here at last are screen shots of the program running

Session Variables

Here is Default.aspx

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

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:Button ID="btnGetAdditionProblem" runat="server" Text="Addition" 
            onclick="btnGetAdditionProblem_Click" /><br />
        <asp:Label ID="lblProblem" runat="server" Text="Label"></asp:Label><br/>
        <asp:TextBox ID="txtAnswer" runat="server"></asp:TextBox>
        <asp:Button ID="btnCheckAnswer" runat="server" Text="Check" 
            onclick="btnCheckAnswer_Click" /><br />
        <asp:Label ID="lblResult" runat="server" Text="Label"></asp:Label>
    </div>
    </form>
</body>
</html>

Here is the code behind Default.aspx.cs

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

public partial class _Default : System.Web.UI.Page
{
    /*This class does a test for simple addition. It
     * also shows how to save a variable to session
     * variable so that it is avialable after the postback
     * (page redraw from server)
     * caused by clicking a button
     */
    
    //class level variables
    int number1;
    int number2;
    int answer;

    protected void Page_Load(object sender, EventArgs e)
    {
        //this event method is used to do anything that needs to
        //be done before the page is loaded for the viewer
    }
    protected void btnGetAdditionProblem_Click(object sender, EventArgs e)
    {
        //call the addition method when the button is clicked
        Addition();
    }

    protected void GetRandom()
    {
        //get random numbers
        Random rand = new Random();
        number1 = rand.Next(0, 10);
        number2 = rand.Next(0, 10);

    }

    protected void Addition()
    {
        //get the random numbers
        GetRandom();
        //make the problem visible in a label
        lblProblem.Text = number1.ToString() + "+" + number2.ToString();
        //assign an answer to the problem
        answer = number1 + number2;
        //save the answer to a server side Session variable
        Session["CurrentAnswer"] = answer;
    }
    protected void btnCheckAnswer_Click(object sender, EventArgs e)
    {
        //get the user's answer from a text box
        int guess = int.Parse(txtAnswer.Text);
        //recall the answer from the server
        //the session variable stores everything as "object"
        //so you have to cast it back to the type of object
        //that it is--in this case an integer
        answer = (int)Session["CurrentAnswer"];
        //check to see if the answer is correct
        //and display the result
        if (guess == answer)
        {
            lblResult.Text = "Congrats";
        }
        else
        {
            lblResult.Text = "Sorry";
        }
    }
}

Insert Update Delete

Use Automart;

Create Table test
(
 testKey int identity(1,1) primary key,
 TestName nvarchar(255)
)

Insert into test (TestName)
Values ('Test One'),
('Test Two'),
('Test Three')

Select * From Test
Select MAX(PersonKey) from Person

Insert into Person (LastName, firstname)
values('Muntz', 'Nelson')


Insert into Customer.RegisteredCustomer(Email, CustomerPassword, PersonKey)
Values ('nelson@gmail.com','nelsonpass', 53)

Insert into Customer.Vehicle(LicenseNumber, 
VehicleMake, 
VehicleYear,
 PersonKey)
Values('456NET', 'VW Beetle','1972', IDENT_CURRENT('Person'))

Select * From Person
Select * From Customer.RegisteredCustomer
Select * From customer.Vehicle

--updates are dangerous
Update Customer.vehicle
Set VehicleYear ='1973'
Where VehicleID =47

Begin Tran --if you explicitly begin a transaction
--you can roll it back if you make a mistake
--other wise there is no undo

Update Person
Set FirstName = 'John'
where LastName='Smith'

Select * From Person

rollback tran --undoes the explicit transaction


--you can update
Update Customer.RegisteredCustomer
Set Email='muntz@gmail.com',
CustomerPassword='muntzpass'
Where PersonKey=53

--change all the values in a table on purpose
Update Customer.AutoService
Set ServicePrice=ServicePrice * 1.05

--this actually won't delete much because
--the records in the parent table person
--have child records in other tables
--you must delete all the children before 
--you can delete the parent
Delete From Person

Begin tran

--delete with a criteria
Delete from customer.RegisteredCustomer
where RegisteredCustomerID=4

Select * From customer.vehicle

commit tran--if all is good, commit the transaction
--and write to the database

First ASP

Here is the Default.aspx

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

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <link href="FirstAsp.css" rel="stylesheet" type="text/css" />
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:Calendar ID="Calendar1" runat="server" ></asp:Calendar>
        <asp:Label ID="Label1" runat="server" Text="Enter your name"></asp:Label>
        <asp:TextBox ID="txtName" runat="server"></asp:TextBox>
        
        <br />
        <asp:Button ID="btnSubmit" runat="server" onclick="Button1_Click" 
            Text="Submit" />
        <asp:Label ID="lblResult" runat="server" Text=""></asp:Label>
    </div>
    </form>
</body>
</html>

Here is the Default.aspx.cs

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

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

    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        string selectedDate = 
            Calendar1.SelectedDate.ToShortDateString();
        string name = txtName.Text;
        lblResult.Text= "thank you, " + name + ",  The date you selected is "
            + selectedDate;
        if (Calendar1.SelectedDate.DayOfWeek.ToString() == "Saturday")
        {
            lblResult.CssClass = "weekend";
        }
        else
        {
            lblResult.CssClass = "weekday";
        }
    }
}

Here is the firstAsp.css

body {
}

table tr th
{
    font-weight:bold;
    background-color:Fuchsia;
    color:Lime;
}

.weekend
{
    background-color:Green;
}

.weekday
{
    background-color:White;
}

SQL

use Automart;

Select Firstname, Lastname from Person;

Select * From Person;

--Distinct applies to whole row
Select Distinct locationID from Employee.VehicleService;

--calculations and scalar functions (operates on each row)
Select serviceName, servicePrice, 
cast((ServicePrice * 1.15)as Decimal(7,2)) as [New Price]
From Customer.AutoService;

Select * from Person
Order by Lastname Desc, Firstname;

Select * from Person 
Where LastName= 'Smith';

--=, >, <, >=, <= ,!=, <>,
Select * From Customer.AutoService
Where ServicePrice > 100;

Select * from Employee.VehicleService
Where ServiceDate >='4/1/2010'

Select * From Employee.VehicleServiceDetail
where ServiceDate Between '3/1/2010' and '3/31/2010'
--equivalent of ServiceDate >= '3/1/2010' and ServiceDate <= '3/31/2010'
 
 --and, or, not

Use CommunityAssist 

Select * From PersonAddress
Where Apartment is Not null

--scalar function
use Automart
Select GETDATE()
Select Distinct MONTH(ServiceDate) From Employee.VehicleService

--aggregate functions

Select COUNT(Distinct VehicleID) From Employee.VehicleService

Select AVG(ServicePRice) From Customer.AutoService

Select MAX(ServicePrice) From Customer.Autoservice

Select MIN (ServicePrice) From customer.AutoService

Select AVG(ServicePrice) From Customer.AutoService
where ServicePrice < 
(Select MAX(ServicePrice) from Customer.Autoservice)
and ServicePrice > 
(Select MIN(ServicePRice) from Customer.Autoservice)

Select Servicename, ServicePrice From Customer.Autoservice
Where ServicePrice = (Select MIN(ServicePrice) from Customer.AutoService)
--
Select MONTH(ServiceDate) as [Month], 
COUNT(VehicleID) as [Vehicles]
From Employee.VehicleService
Group by MONTH(ServiceDate) --any field that isn't a part of the aggregate
Having COUNT(VehicleID) >10


Select LastName, Firstname,Email, licenseNumber, VehicleMake
From Person
inner join Customer.RegisteredCustomer
on Person.Personkey=Customer.RegisteredCustomer.PersonKey
inner join Customer.vehicle
on Person.Personkey=Customer.vehicle.PersonKey

Select LastName, rc.PersonKey
From Person as p
left outer join Customer.RegisteredCustomer as rc
on p.Personkey=rc.PersonKey
Where rc.PersonKey is null







 

More Validation and Error Trapping

Here is an example of a program that validates the user choices with TryParse instead of Parse. It also shows the use of try catch blocks and how to throw an exception.

The program consists of three classes. The Program class which contains the Main() method, and nothing else. The Display class which calls the SimpleMath class which does simple integer addition, subtraction, multiplication and division

We will start with the validation of the inputs in the Display class.

First we set up a menu. (The menu, by the way, uses a switch structure which tests each "case" of the variable "choice." If choice is equal to 1 it call the the GetNumbers() method and the GetAddition() method. The break keeps it from sliding down to the next case.

Here is the basic menu:

Notice the menu loops until the user chooses 5 to exit. Also note the validation of the user choice. It uses a TryParse method to test the value. The TryParse value returns a boolean--true of false. If the input can be parsed as an integer it is passed out to the variable "choice". If not, it just returns false. In this instance I don't have to do anything else. It will keep looping until the user enters 5 to exit.

In the GetNumbers() method the user input is again tested with TryParse(). Here the input is inside a while loop that won't let the user out until they have entered a valid number.

After the numbers are validated they are assigned to properties in the SimpleMath class. Lets look at the SimpleMath class. I am going to focus on just the Division method. With integer division it is an error to divide by zero. I am going to put the division in a try catch block and look explicitly for the Division by zero error. I am going to throw the error back to the Display class which called the method. It is always best if your classes are as agnostic about the display modes as possible. The same class could be used in a console application such as this one, or with a web form or a window's form.

I did the same thing with the Remainder method

If you throw an error you must catch it somewhere. I put a try catch block around the code in the Display class that calls the divide method. It will catch and display the error.

Here is a picture of the console app running. I chose 4 for divide, and put 0 as the second integer. The error message, thrown from the SimpleMath class and caught by the Display class is shown

So here is all the code in a form you can cut and paste

---

SimpleMath

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

namespace tryCatchExamples
{
    class SimpleMath
    {
        /// 

        /// This class just does simple arithmetic.
        /// Its main point is in the Divide method.
        /// There it has a try catch that catches the 
        /// DivideByZeroException. It throws this to the
        /// calling class which is Display
        /// 
        /// 
        // private fields 
        private int number1;
        private int number2;

        //default constructor
        public SimpleMath()
        {
            number1 = 0;
            number2 = 0;
        }

        //overloaded constructor
        public SimpleMath(int num1, int num2)
        {
            number1 = num1;
            number2 = num2;
        }

        //public properties
        public int Number1
        {
            set { number1 = value; }
            get { return number1; }
        }

        public int Number2
        {
            set { number2 = value; }
            get { return number2; }
        }

        //public methods

        public int Add()
        {
            return number1 + number2;
        }

        public int Subtract()
        {
            return number1 - number2;
        }

        public int Multiply()
        {
            return number1 * number2;
        }


        public int Divide()
        {
            int quotient=0;
            //here we try the division if number2
            //is 0 it will throw an error
            //backt to the calling class
            try //try the following code
            {
                quotient = number1 / number2;
            }
            catch (DivideByZeroException) //catch the Divide by zero error
            {
                throw new DivideByZeroException();
            }

            return quotient;
        }

        public int Remainder()
        {
            int remain = 0;
            {
                try //try the following code
                {
                    remain = number1 % number2;
                }
                catch (DivideByZeroException) //catch the Divide by zero error
                {
                    throw new DivideByZeroException();
                }

                return remain;
            }
        }
    }
}

---

Display

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

namespace tryCatchExamples
{
    class Display
    {
        private SimpleMath simpleMath;
        private int num1;
        private int num2;

        public Display()
        {
            simpleMath = new SimpleMath();
            menu();
        }

        private void menu()
        {
            int choice = 1;
            while (choice !=5)
            {
                Console.Clear();
                Console.WriteLine("Choose which operation you wish to do:"
                    +"\n1: Add" 
                    +"\n2: Subtract"
                    +"\n3: Multiply"
                    +"\n4: divide"
                    +"\n5: Exit");

                bool menuitem= int.TryParse(Console.ReadLine(), out choice);
                switch (choice)
                {
                    case 1:
                        GetNumbers();
                        GetAdd();
                        break;
                    case 2:
                        GetNumbers();
                        GetSubtract();
                        break;
                    case 3:
                        GetNumbers();
                        GetMultiply();
                        break;
                    case 4:
                        GetNumbers();
                        GetDivide();
                        break;
                    default:
                        break;
                }
            }
        }

        private void GetNumbers()
        {
            bool goodNumber = false;
            bool goodNumber2 = false;
            while (!goodNumber)
            {
                Console.WriteLine("Enter the first integer");
                goodNumber = int.TryParse(Console.ReadLine(), out num1);

                if (!goodNumber)
                {
                    Console.WriteLine("enter a valid integer");
                }

            }

            while (!goodNumber2)
            {
                Console.WriteLine("Enter the Second integer");
                goodNumber2 = int.TryParse(Console.ReadLine(), out num2);

                if (!goodNumber2)
                {
                    Console.WriteLine("enter a valid integer");
                }

            }
            simpleMath.Number1 = num1;
            simpleMath.Number2 = num2;
        }

            private void GetAdd()
            {
                Console.WriteLine("The sum is {0}", simpleMath.Add() );
                PauseIt();
            }

            private void GetSubtract()
            {
                Console.WriteLine("The Difference is {0}", simpleMath.Subtract());
                PauseIt();
            }

            private void GetMultiply()
            {
                Console.WriteLine("The product is {0}", simpleMath.Multiply());
                PauseIt();
            }

            private void GetDivide()
            {
                int quotient;
                int remainder;
                try
                {
                    quotient = simpleMath.Divide();
                    remainder = simpleMath.Remainder();
                    Console.WriteLine("the quotient is {0}", quotient);
                    Console.WriteLine("The remainder is {0}", remainder);
                    PauseIt();
                }
                catch(Exception ex)
                {
                    Console.WriteLine(ex.Message);
                    PauseIt();
                }
            }

            private void PauseIt()
            {
                Console.WriteLine("Press any key to continue");
                Console.ReadKey();
            }

    }
}

---

Program

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

namespace tryCatchExamples
{
    class Program
    {
        static void Main(string[] args)
        {
            Display d = new Display();
        }
    }
}

Class example from Class

Here is the CalculateCubicYards class

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

namespace ClassExample
{
    class CubicYardCalculator
    {
        //Fields 
        private int width; //feet
        private int depth;//inches
        private int length;//feet
        //36 * 36 * 36
        const int CUBICYARDININCHES = 46656;
        const int FOOT=12;

        public CubicYardCalculator()
        {
            Width = 0;
            Length = 0;
            Depth = 0;
        }

        public CubicYardCalculator(int w, int l, int d)
        {
            Width = w;
            Length = l;
            Depth = d;
        }

        //public properties
        public int Width
        {
            set 
            { 

                width = value;
                WidthInInches();
            }
            get { return width; }
        }

        public int Length
        {
            get { return length; }
            set 
            { 
                length = value;
                LengthInInches();
            }
        }

        public int Depth
        {
            get { return depth; }
            set { depth = value; }
        }

        //methods
        private void WidthInInches()
        {
            width=width * FOOT;
        }

        private void LengthInInches()
        {
            length=length * FOOT;
        }

        public int GetCubicYards()
        {
            int cubicYards=(Length * Width * Depth) / CUBICYARDININCHES;
            if((Length * Width * Depth) % CUBICYARDININCHES !=0)
                cubicYards+=1;
            return cubicYards;
        }
    }
}

Here is the Program class

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

namespace ClassExample
{
    class Program
    {
        static void Main(string[] args)
        {
            Program p = new Program();
            //p.DefaultConstructor();
            p.AlternateConstructor();
            Console.ReadKey();
        }

        private void DefaultConstructor()
        {
            CubicYardCalculator cubic = new CubicYardCalculator();
            cubic.Length = 80;
            cubic.Width = 60;
            cubic.Depth = 6;
            Console.WriteLine("You need {0} Cubic Yards", cubic.GetCubicYards());
        }

        private void AlternateConstructor()
        {
            CubicYardCalculator cubic = new CubicYardCalculator(80, 20, 6);
            Console.WriteLine("You need {0} Cubic Yards", cubic.GetCubicYards());
        }
    }
}

Creating and using a class

This example creates a user defined class called CubicYardCalculator. First Add a new Class to the project and name it "CubicYardCalculator."

This gives you an empty class:

First we add the class level fields. These are class level variables that describe the object. In our case the cubic yards needs three variables to describe it, height, width and depth. I also have added a constant to store the value of a cubic yard in terms of inches (36 * 38 * 36). We will need this later when doing the calculations since we convert everything into inches to do the math and then back to cubic yards.

The fields are private to protect them. This is a part of the object oriented principle called Encapsulation. We will expose them to other classes through special methods called properties. Properties let you set the value and get the value returned. You can use properties to validate the input. You can also let people "get" the value of a field, but not "set" it, or visa versa. We will modify the Width and Length properties shortly.

Properties are different from other methods in that they don't take parameters. Therefore they don't have a () at the end of the declaration

Next we will enter the class methods. I make three of them. One to convert Width to inches, another to convert Length to inches and a third to get the cubic yards. The first two are private, meaning they can only be accessed internally in this class. The last method is public. That means we can call it from another class.

So, what are we going to do with those private methods? I am going to call them from the properties so that we can guarantee that they will be converted to inches. Here are the modified properties.

We are almost done with this class. The last things I am going to add are two constructors. Constructors "construct" a class. They set it up by initializing any variables and calling any methods that should be called.

If you don't create a constructor, the compiler creates a default constructor that initializes every thing to 0 or null. If you make your own constructors the compiler will no longer create a default constructor and it is up to you to initialize your variables.

Constructors can be overloaded. That means you can have multiple constructors as long as they can be clearly distinguished from each other. It is important to note that no matter how many constructors you have only one at a time is used for any particular instance of a class.

Constructors always have the same name as the class, and unlike other methods, they have no return type.

Why have multiple constructors? It gives the user of the class choices. Now they have two ways to initialize the class. They can choose the way that works best in their context.

Next we will instantiate and call our new class from the program class. First I will make a method to use the default constructor--the one that doesn't take arguments. When I use it, I have to use the properties directly to set the values. For brevity I am simply giving it literal values. You could, and should of course, get user input for these values. Here is the first method and the result when it is run.

No here is the code for using the overloaded constructor. Again I am just passing it literal values.

Validating user Input

Here is a brief example that shows how to validate user input

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

namespace ConsoleApplication4
{
    class Program
    {
        /// 

        /// This program uses the quadratic equation
        /// to produce a a prime number
        /// with a seed of any integer between 1 and 41
        /// The main purpose is to show you how you 
        /// can validate the users input.
        /// the TryParse makes sure that the number 
        /// is an integer without crashing the program
        /// the while loop keeps repeating the user prompt
        /// until they enter an integer in the valid range
        /// Steve Conger 11/1/2012
        /// 
       
        static void Main(string[] args)
        {
            Program p = new Program();
            p.HowToTestUserEntry();
            Console.ReadKey();
        }

        private void HowToTestUserEntry()
        {
            //you want them to enter an integer between 1 and 41
            //declare the variabe to test
            int number = 0;
            //create a loop that won't stop until 
            //they enter a proper number
            while (number < 1 || number > 41)
            {
                Console.WriteLine("Enter an integer between 1 and 41");
                //if it parses correctly it will assign the integer to the
                //variable number, otherwise it returns false
                bool entry = int.TryParse(Console.ReadLine(), out number);
                //if it is false or the number is not between
                //1 and 41 prompt them to enter a correct number
                if (!entry || number < 1 || number > 41)
                {
                    Console.WriteLine("Please make sure you entered an integer between 1 and 41");
                }//end if
            }//end while

            //this equation is a mathematical oddity
            //that returns 41 prime numbers in sequence
            //for numbers between 1 and 41
            int prime = number * number - number + 41;
            Console.WriteLine("The prime number is {0}", prime);
        }
    }
}

Random Numbers

here is the code for a random number

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

namespace ConsoleApplication4
{
    class Program
    {
        static void Main(string[] args)
        {
            //instantiate the program class
            Program p = new Program();
            //call the DisplayRandom method
            p.DisplayRandom();
            Console.ReadKey();
        }

        private int GetRandom()
        {
            //instantiate the Random class
            //rand is the local name of our Random
            //object
            Random rand = new Random();
            //this sets the minimum random number
            //at 1 and the 
            int number = rand.Next(1, 100);
            return number;
        }

        private void DisplayRandom()
        {
            //get fifteen random numbers
            for (int i = 1; i <= 15; i++)
            {
                //the prompt just slows it down enough
                //to make it do different randoms
                Console.WriteLine("Press any key for next number");
                Console.ReadKey();
                Console.WriteLine(GetRandom());
            }
        }
    }
}

Video ERD Normalized

Here is the diagram that we did in class for the practices for chapter 5

Loops arrays, while

here is the code for the day. I may return to it to add comments later">

---

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

namespace Shopping
{
    class Program
    {
        //we need to create two arrays
        //one for the items
        //one for the prices
        //we will prompt the user how many items
        //they want to enter
        //Then we will enter items and prices
        //into the two arrays
        //we will sum the prices and add tax
        //we will display a summary information
        //then we will ask if they want to enter 
        //another list. If they say yes we
        //will do it again, if no we will exit
        int number=0;

        static void Main(string[] args)
        {
            Program p = new Program();
            p.Display();
        }

        private void Display()
        {
            //loop the program as long as the user says yes
            string choice = "yes";
            while (choice == "yes" || choice == "y")
            {
                //this changes the console background to blue
                Console.BackgroundColor = ConsoleColor.DarkBlue;
                Console.Clear();
                
                //call methods
                GetNumberOfItems();
                CreateShoppingArray();
                CreatePricesArray();
                FillArrays();
                Console.WriteLine("do you want to contine with another list--yes or no");
                choice = Console.ReadLine();
                choice = choice.ToLower();
            }
            //Console.ReadKey();
        }

        //get the number of items
        private void GetNumberOfItems()
        {
            Console.WriteLine("How many items do you want to enter?");
            number = int.Parse(Console.ReadLine());
         
        }
        //create the two arrays
        private string[] CreateShoppingArray()
        {
            string[] items = new string[number];
            return items;
        }

        private double[] CreatePricesArray()
        {
            double[] prices = new double[number];
            return prices;
        }

        //this prompts the user and fills the arrays
        private void FillArrays()
        {
            string[] shoppingItems = CreateShoppingArray();
            double[] itemPrices = CreatePricesArray();

            for (int i = 0; i < shoppingItems.Length; i++)
            {
                Console.WriteLine("Enter the item");
                shoppingItems[i] = Console.ReadLine();
                Console.WriteLine("Enter the item price");
                itemPrices[i] = double.Parse(Console.ReadLine());
              
            }//end for
            CalculateResults(shoppingItems, itemPrices);
        }//end fill array

        private void CalculateResults(string[] shoppingList, double[] itemCost)
        {
            Console.WriteLine("*****************************\n");
            double total = 0;
            for (int i = 0; i < itemCost.Length; i++)
            {
                //same as total=total + itemCost[];
                total += itemCost[i];
            }

            Console.WriteLine("Your Items");
            for(int i=0;i< shoppingList.Length;i++)
            {
                Console.WriteLine(shoppingList[i] + "\t\t" + itemCost[i].ToString("c"));
            }

            Console.WriteLine("Total {0:C}", total);

        }
    }
}

---

Here is the earlier code

---

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

namespace WhileLoops
{
    class Program
    {
        static void Main(string[] args)
        {
            Program p = new Program();
            //uncomment the method you want to run
            //comment the ones you don't want to run

            p.ForLoopReview();
            p.WhileLoopExample();
            p.DoLoopExample();
            Console.ReadKey();
        }


        private void ForLoopReview()
        {
            string[] languages=new string[4];
            languages[0] = "C++";
            languages[1] = "C#";
            languages[2] = "Java";
            languages[3] = "php";

            for (int i = 0; i < languages.Length; i++)
            {
                Console.WriteLine(languages[i]);
            }//end for
        }//end for loop review

        private void WhileLoopExample()
        {
            int x = 6;
            Console.WriteLine("While Loop OutPut");
            while (x < 6)
            {
                Console.WriteLine(x);
                x++;
            }
        }//end while loop example

        private void DoLoopExample()
        {
            int x = 6;
            Console.WriteLine("Do loop output");
            do
            {
                Console.WriteLine(x);
                x++;
            } while (x < 6);
        }
    }//end class
}//end namespace

Venue Tracking Database Take one

Here is the diagram we did in class:

---

Loops and arrays 1

Here is a first look at creating arrays and for loops:

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

namespace ArraysandLoops
{
    class Program
    {
        static void Main(string[] args)
        {
            Program p = new Program();
            p.Run();
            Console.ReadKey();
        }

        private void Run()
        {
           
            DisplayArray();
            InitializedArray();
        }

        private string[] CreateArray()
        {
            //declare an array of strings
            string[] cheese = new string[5];
            return cheese;
           // FillArray(cheese);
        }

        private string[] FillArray()
        {
            //create an array variable to store
            //the array returned by create array
            string[] queso = CreateArray();
            //this loop starts at zero, and loops
            //until it is less than the length of the array (5)
            //i++ increments the counter by 1
            for (int i = 0; i < queso.Length; i++)
            {
                Console.WriteLine("enter a cheese");
                string cheeseName = Console.ReadLine();
                //assigns it to the current index of the array
                queso[i] = cheeseName;
            }//end for loop

            return queso;
        }//end fill Array

        private void DisplayArray()
        {
            
            string[] fromage = FillArray();
            Console.WriteLine("************************\n");
   
            for (int i = 0; i < fromage.Length; i++)
            {
                Console.WriteLine(fromage[i]);
            }//end for

            Console.WriteLine(fromage[3]);
        }//end displayArray

        private void InitializedArray()
        {
            //create an array of integers and 
            //assign them immediately
            int[] numbers = new int[] { 1, 3, 5, 6, 8, 2 };
            //loop through the array to display them
            for (int i = 0; i < numbers.Length; i++)
            {
                Console.WriteLine(numbers[i]);
            }
        }




    }//end class
}//end namespace

Here is the example of summing and averaging arrays

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

namespace SumandAverageArrays
{
    class Program
    {
        static void Main(string[] args)
        {
            Program p = new Program();
            p.CreateArray();
            Console.ReadKey();
        }

        private void CreateArray()
        {
            double[] numbers = new double[] { 2, 3.21, 4, 5.234, 1.2, 6 };
            GetSum(numbers);

        }

        private void GetSum(double[] numberArray)
        {
            double sum=0;
            for(int i=0;i<numberArray.Length;i++)
            {
                //+= equivalent to sum = sum + numberarray[i]
                sum+=numberArray[i];
            }

            double average = sum / numberArray.Length;
            Console.WriteLine("The sum is {0}", sum);
            Console.WriteLine("The average is {0}", average);
            //these built in methods do the same thing
            Console.WriteLine("**********************");
            Console.WriteLine(numberArray.Sum());
            Console.WriteLine(numberArray.Average());
        }
    }
}

Beginning Database Design

We began by looking at Visio 2010. We drug an Entity onto the grid and use the properties window to Name it DVD. Then we assigned a primary Key and some fields. We Determined that actor was a multi-valued attribute, meaning that every DVD probably has many Actors, so we broke it into its own entity.

There are Three Kinds of relationships:
*One to One, where each record in the primary key is related to no more than 1 record in the child table
*One to Many, where each record in the primary key table can be related to zero or several records in the child table
*Many To Many, where each record in the primary key table can be related to any number of records in the child table, and each record in the child table can be related to any number of records in the parent table. These must be resolved by adding a linking table

Next we determined that there is a many to many relationship between DVD and Actor.Here is the Diagram

---

More if and else if

This is the code that is part of assignment 3

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

namespace Medicare
{
    class Program
    {
        static void Main(string[] args)
        {
            //load the program 
            Program p = new Program();
            //call the display method
            p.Display();
            //pause it
            Console.ReadKey();
        }

        private void Display()
        {
            //get the inputs
            Console.WriteLine("Enter Your pay rate");
            double rate = double.Parse(Console.ReadLine());
            Console.WriteLine("Enter your hours");
            double hours = double.Parse(Console.ReadLine());

            //call the CalculateGross method and store the value
            //it returns in the local variable gross
            double gross = CalculateGrossPay(rate, hours);
            Console.WriteLine("Your gross pay is {0:C}", gross);
            //call the method to CalculateMedicare
            double med = CalculateMedicare(gross);
            Console.WriteLine("Your Medicare Deduction is {0:C}", med);
        }

        private double CalculateGrossPay(double rate, double hours)
        {
            //this method calcuates pay 
            //it takes overtime into account
            double grossPay=0;
            if (hours > 40)
            {
                grossPay = rate * (40 + ((hours - 40) * 1.5));
            }
            else
            {
                grossPay = rate * hours;
            }

            return grossPay;
        }//end calculateGrosspay

        private double CalculateMedicare(double gross)
        {
            double medicare = 0;
            //5000 > .2
            //2000 to 4999 .1
            //1999 or less .03
            //uses and if, else if to calculate percentages
            if (gross >= 5000)
            {
                medicare = gross * .2;
            }
            else if (gross >= 2000)
            {
                medicare = gross * .1;
            }
            else
            {
                medicare = gross * .03;
            }

            return medicare;
        }
    }
}

---

Here are the other if, else if examples

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

namespace Medicare
{
    class Program
    {
        static void Main(string[] args)
        {
            //load the program 
            Program p = new Program();
            //call the display method
            p.Display();
            //pause it
            Console.ReadKey();
        }

        private void Display()
        {
            //get the inputs
            Console.WriteLine("Enter Your pay rate");
            double rate = double.Parse(Console.ReadLine());
            Console.WriteLine("Enter your hours");
            double hours = double.Parse(Console.ReadLine());

            //call the CalculateGross method and store the value
            //it returns in the local variable gross
            double gross = CalculateGrossPay(rate, hours);
            Console.WriteLine("Your gross pay is {0:C}", gross);
            //call the method to CalculateMedicare
            double med = CalculateMedicare(gross);
            Console.WriteLine("Your Medicare Deduction is {0:C}", med);
        }

        private double CalculateGrossPay(double rate, double hours)
        {
            //this method calcuates pay 
            //it takes overtime into account
            double grossPay=0;
            if (hours > 40)
            {
                grossPay = rate * (40 + ((hours - 40) * 1.5));
            }
            else
            {
                grossPay = rate * hours;
            }

            return grossPay;
        }//end calculateGrosspay

        private double CalculateMedicare(double gross)
        {
            double medicare = 0;
            //5000 > .2
            //2000 to 4999 .1
            //1999 or less .03
            //uses and if, else if to calculate percentages
            if (gross >= 5000)
            {
                medicare = gross * .2;
            }
            else if (gross >= 2000)
            {
                medicare = gross * .1;
            }
            else
            {
                medicare = gross * .03;
            }

            return medicare;
        }
    }
}

First Ifs (Selection) and a bit of assignment 2

Here is the piece of Assignment Two

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

namespace ConsoleApplication3
{
    class Program
    {

        //declare a constant
        const double MEDICARE = .03;

        static void Main(string[] args)
        {
            Program p = new Program();
            //call show pay
            p.ShowNetPay();
           
        }

        private double GrossPay(double hours, double rate)
        {
            //we pass hours and rate into the method and then
            //multiply them
            //not worried about overtime at this point
            return hours * rate;
        }

        private double CalculateMedicare(double gross)
        {
            //pass in gross and multiply it by constant
            return gross * MEDICARE;
        }

        private void ShowNetPay()
        {
            //enter the hours and rate
            Console.WriteLine("Enter hours worked");
            double hours = double.Parse(Console.ReadLine());
            Console.WriteLine("Enter the rate of Pay");
            double rate = double.Parse(Console.ReadLine());

            //get the gross pay by calling the method
            //and storing the value it returns
            double grosspay = GrossPay(hours, rate);
            //call the method for medicare
            double med = CalculateMedicare(grosspay);

            //do the calculation for net pay
            double netpay = grosspay - (med );
            //Display the result
            Console.WriteLine("Your net pay is {0}", netpay);
        }
    }
}

---

Here is the sample with if statements

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

namespace SelectionExamples
{
    class Program
    {
        //first we will make a method to determine
        //if a number even
        //we also introduce tryParse
        static void Main(string[] args)
        {
            //initialize the rest of the class
            Program p = new Program();
            //call the TestNumber method
            p.TestNumber();

            //pause the program to wait for a key stroke
            Console.ReadKey();
        }

        private void TestNumber()
        {
            //this gets an integer number from the user
            //if it is not a valid integer it propmpts the user
            //to start again with a valid integer number

            //declare a variable with a default of zero
            //the TryParse out parameter will assign
            //a new value to this variable
            int num = 0;
            //asl for a number from the user
            Console.WriteLine("Enter an Integer");
            //the tryParse returns true or false (a boolean)
            //if the string on the console can be parsed as an integer
            //it returns true and assigns the value to num,
            //if not it returns false
            bool isInt = int.TryParse(Console.ReadLine(), out num);

            //if it is false prompt them to enter a valid number
            if (isInt == false)
            {
                Console.WriteLine("Make sure you enter an integer");
                return; //end the method
            }

            //if it is true it will continue to execute the code
            //we call the TestForEven number,
            //and pass the num we got from the console
            //then we store the 
            //result ("odd" or "even") in the variable numType
            string numType = TestForEven(num);

            //print out the value of numType
            Console.WriteLine(numType);
        }

        private string TestForEven(int number)
        {
            //create a variable with a default value
            //of odd
            string result = "odd";
            //if then the condition in parenthesis
            //must resolve to true or false
            if (number % 2 == 0) //== means equal
            {
                result = "even";
            }

            //return the result
            return result;
        }
    }
}

Methods and Operators

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

namespace ConsoleApplication3
{
    /******************
     * + is addition
     * - is subtraction
     * * is multipication
     * / is division (if both sides are integer then any decimal part is dropped)
     * % modulus, returns the remainder from an integer division
     * follows same order of operations as algebra
     * all multiplications and divisions first left to right
     * all substractions and additons left to write
     * but what is in parenthesis is first
     * embedded parenthesis from inside out 
     * */
    class Program
    {
        private double total;
        private const double TAX = .095;

        static void Main(string[] args)
        {
            Program p = new Program();
            //p.GetPrice();
            //p.DisplayTotal();
            int quotient = p.IntegerDivision(8, 3);
            Console.WriteLine("the quotient is {0}", quotient);
            int modulus = p.GetTheModulus(8, 3);
            Console.WriteLine("the remainder is {0}", modulus);
           Console.ReadKey();
        }

        private void GetPrice()
        {
            Console.WriteLine("Enter the Price");
            double price = double.Parse(Console.ReadLine());

            total=GetTotal(price);
        }

        private double GetTotal(double pr)
        {
            return  pr * (1 + TAX);
        }

        private void DisplayTotal()
        {
            Console.WriteLine("Your total is {0:c}",total);
        }

        private int IntegerDivision(int number1, int number2)
        {
            return number1 / number2;
        }

        private int GetTheModulus(int num1, int num2)
        {
            return num1 % num2;
        }
    }
}

Gas Mileage with Methods

Here is the Gas Mileage calculator without methods that we did as the first assignment

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

namespace GasMileageCalculator
{
    /*********************
     * This program will calculate miles per
     * gallon and price per mile
     * Steve Conger 9/27/2012
     * *********************/
    class Program
    {
        static void Main(string[] args)
        {
            //get inputs 
            //prompt user 
            Console.WriteLine("Enter the Total Mileage");//write prompt
            int miles = int.Parse (Console.ReadLine());

            Console.WriteLine("Enter the total Gallons");
            int gallons = int.Parse(Console.ReadLine());

            Console.WriteLine("Enter the Price Per Gallon");
            double price = double.Parse(Console.ReadLine());

            //Calculate the outputs

            double milesPerGallon = (double) miles / gallons;
            double pricePerMile = price / milesPerGallon;

            // Display outputs
            Console.WriteLine();
            Console.WriteLine("Your miles per gallon is {0:F2} \n", milesPerGallon);
            
            Console.WriteLine("The price per mile is {0:c}", pricePerMile);

            Console.ReadKey();



        }
    }
}

---

Methods

A few words about methods:

Methods are ways of braking up code into more manageable blocks.

Ideally each method does one thing. It can be one complicated thing involving several lines of code, but still one thing.

In order to execute a method must be called. You call a method by using its name and providing any required parameters. (See Example Two.)

Methods can only be called from other methods. At least one method must be called from the Main method to start the program

Methods can be private meaning they can only be seen by other methods in the current class, or they can be public meaning they can be seen by other classes and programs. (there are other options in between such as protected.)

Methods can be void (as in the first example) meaning that they return nothing to the calling method or they can return a value(see example two)

---

Here is the code from class. This version makes the variables have class scope, which means they can be accessed by any of the methods in the class.

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



namespace GasMileageCalculatorWithMethods
{
    class Program
    {
        /*
         * get inputs
         * calculate miles per Gallon
         * calculate price per mile
         * Display the outputs
         */
        //declaring variables with class scope
        int miles;
        int gallons;
        double price;
        
        static void Main(string[] args)
        {
            //The Program class is not yet loaded
            //into memory, though the Main method is
            //because it is static, so we have to
            //load the class into memory with the new
            //keyword
            Program p = new Program();
            p.CallMethods();
           
        }//end main



        private void GetMiles()
        {
            Console.WriteLine("Please Enter the total Miles");
            miles = int.Parse(Console.ReadLine());
        }//end GetMiles

        private void GetGallons()
        {
            Console.WriteLine("Please Enter the total Gallons");
            gallons = int.Parse(Console.ReadLine());
        }//end getGallons

        private void GetPricePerGallon()
        {
            Console.WriteLine("Please Enter the price per Gallon");
            price= double.Parse(Console.ReadLine());
        }

        private double CalculateMilesPerGallon()
        {
            double mpg;
            mpg=miles / (double)gallons;
            return mpg;
        }

        private double CalculateCostPerMile()
        {
            return price / CalculateMilesPerGallon();
        }

        private void DisplayResults()
        {
            Console.WriteLine("Your Miles per Gallon is {0:F2}", CalculateMilesPerGallon());
            Console.WriteLine("The cost per mile is {0:C}", CalculateCostPerMile());
            Console.ReadKey();
        }

        private void CallMethods()
        {
            GetMiles();
            GetGallons();
            GetPricePerGallon();
            DisplayResults();
        }

    }//end class
}//end namespace

---

Here is a second example of the same program. Instead of making variables with class level scope, the variables are passed as parameters to the appropriate methods

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

namespace GasMileageCalculatorMethods
{
    class Program
    {
        //This program use methods with parameters
        // to calculate gas mileage
       
        double price;

        static void Main(string[] args)
        {
            //explain this
            Program p = new Program();
            p.Display();
        }

        private void Display()
        {
            //calling methods
            GetInputs();
            Console.ReadKey();
        }

        private void GetInputs()
        {
            //this method could be broken up as we did
            //in the example above
            Console.WriteLine("Enter the total Miles");
            int miles = int.Parse(Console.ReadLine());
            Console.WriteLine("Enter the Total gallons");
            double gallons = double.Parse(Console.ReadLine());

            //call and pass values
            double milesPerGallon=CalculateMileage(miles, gallons);

            Console.WriteLine("Enter the price per gallon");
            price = double.Parse(Console.ReadLine());

            double pricePerMile = CalculatePrice(price, milesPerGallon);

            DisplayOutputs(milesPerGallon, pricePerMile);


        }

        private double CalculateMileage(int m, double gals)
        {
            //explain return
            return m / gals;
        }

        private double CalculatePrice( double pr, double mpg)
        {
            return pr  / mpg;
        }

        private void DisplayOutputs(double milesPerGallon, double pricePerMile)
        {
            //explain calling the method 
            Console.WriteLine("You got {0:F2} miles per gallon", milesPerGallon);
            Console.WriteLine("The price per mile was {0:C}", pricePerMile);
        }
    }
}

Statement of scope

Here are the questions and ideas we came up with in class related to the database to track local venues and bands

--Track "local" bands  and the venues where they intend to play
what is the lineup?
event name (tour, album)
How many venues , how many bands
Types of bands--types of music
Times they play
Age range--venue age restrictions
Location of venues
commissions? who do you charge?
contact information for both bands and venues
Local or touring bands
seated
web sites 
How many users
who is the user
How do the users access the database
login (some personal informatin)
commissions
--Constraints Not going to track TV, Radio, and other media

---

Here is the preliminary statement of work

History

We know a lot of people who are very interested in the current music scene in the northwest. Currently there is no centralized way to find out which bands are playing at what venue at any given time. We believe a database with a web front end would be the most efficient way to get this information across and would draw a large audience.

Statement of Scope

The database will track regional venues and the bands performing there. It will store basic band information, where they are from, type of music, some contact information etc. It will also store venue information such as address, contact information, and age and other restrictions. The database will track the dates, times and locations of shows. Fans can register to recieve notifications of upcoming shows of bands they mark as of interest.

Constraints: The database will not directly handle ticket sales or other financial information

Band information will be limited to basic description and contact information

Objectives

Make it easier to follow local venues and bands

Time Line

• Gathering Data:
• Analyze Data
• Design and normalize the data
• Build the physical database
• Test the database

Add JList Data on Selecting a Radio Button

Here is some code displays a different JList content depending on which radio button is selected.

First here are the forms

---

here is the code. It uses validate() and repaint() methods on the JFrame to refresh the form

import java.awt.BorderLayout;
import java.awt.GridLayout;
import java.awt.event.ItemEvent;
import java.awt.event.ItemListener;

import javax.swing.ButtonGroup;
import javax.swing.JFrame;
import javax.swing.JList;
import javax.swing.JPanel;
import javax.swing.JRadioButton;


public class mainForm extends JFrame implements ItemListener{
 private JPanel panel;
 private JPanel listPanel;
 private JPanel borderPanel;
 private JRadioButton one;
 private JRadioButton two;
 private JList list;
 private String[] arrayOne= new String[]{"Monday", "Tuesday", "Wednesday"};
 private String[] arrayTwo=new String[]{"Thursday", "Friday", "Saturday"};
 
 public mainForm(){
  CreatePanel();
  this.add(borderPanel);
  this.setBounds(100, 100, 200, 200);
  this.setVisible(true);
 }
 
 private void CreatePanel(){
  panel=new JPanel();
  borderPanel=new JPanel(new BorderLayout());
  
  one=new JRadioButton("one");
  one.addItemListener(this);
  two=new JRadioButton("two");
  two.addItemListener(this);
  list=new JList();
  
  ButtonGroup group = new ButtonGroup();
  group.add(one);
  group.add(two);
  
  panel.add(one);
  panel.add(two);
  listPanel=new JPanel();
  listPanel.add(list);
  
  borderPanel.add(panel, BorderLayout.CENTER);
  borderPanel.add(listPanel, BorderLayout.SOUTH);
  
 }

 @Override
 public void itemStateChanged(ItemEvent arg0) {

  
  Object source = arg0.getSource();
  if(source.equals(one)){
   
   list.removeAll();
   list.setListData(arrayOne);
   
   
  }
  else{
   list.removeAll();
   list.setListData(arrayTwo);
  }
  
  //borderPanel.add(listPanel, BorderLayout.SOUTH);
  //this.add(borderPanel);
  this.validate();
  this.repaint(); 
 }
 
 
}

Final Version of VenueAdmin

I am linking to a zip file of the VenueAdmin Application. The application uses LINQ to log in to the VenueTracker database with the VenueLogin and using the VenueRole. The login page gets the venue name and password. the password is hashed to not be in plain text. If the login is successful the venueid is stored in a Session variable, and the user is redirected to a second web page that retrieves and displays the information about their particular venue on the page using a datalist and an xml control.

There is a button on this web page that allows them to add a show. This redirects them to the add show form. On this form they can enter the show information and submit it. If the artist is not in the dropdown list they can click the add artist button and be directed to the form for entering a new artist. when the artist is submitted it returns them to the show page so that they can complete that form

Before I could get it to run I needed to suspend the trigger we had made earlier. That was the source of the no create table permission error. I also had to grant execute on the schema dbo. Here is the SQL for doing that

Use VenueTracker
Go
disable trigger tr_NotifyCustomers on Show
Go
Select * from Show

Grant exec on schema::dbo to VenueRole

It is also quite possible that you will need to edit the webconfig file to change the connection string. I mistyped "venulogin" for "VenueLogin" when I created the database login. Also substitute your password for mine and change the server name if you are not using localhost.

Here are pictures of the site running

---

---

---

---

SQL Injection Attack

Here is an example of SQL injection attack. It takes a set of fairly foolish mistakes--connecting as admin, not validating the textbox, concatenating the text box directly into the SQL string etc. The key is to meet any critera of the query with something like "or 1=1", then do your command and end with a "--" or "/*" which comments out the rest of the SQL code. Our example takes an update statement. It provides a value, provides a closing quote and then comments out the remainder of the SQL. The result is that every field in the table will have the same value for that field.

Here is the c# for the example

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

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        //or 1 =1; Drop table student /*
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        SqlConnection connect = new SqlConnection("Data Source=localhost;initial catalog=InjectionTest; Integrated Security=true");
        string sql = "Update tblPerson set Firstname='" + TextBox1.Text + "' where PersonKey=1"; 
        SqlCommand cmd = new SqlCommand(sql, connect);
        connect.Open();
        cmd.ExecuteNonQuery();
        connect.Close();
        Label1.Text = "thank you";

    }
}

---

Here is a picture of the form running

---

Here is a picture of the results

Creating a Login using LinQ

First we Reviewed some of the requirements for the Venue user

---

/*
Venue
Login and get their information
Edit own information
Add a show-- choose or add an Artist
Edit add tickets
See their own profits (stored procedure)
----
we have usp_venuShows
usp_AddVenue
usp_ShowList
Usp_UpdateVenue
veiw CurrentSalesSummary

-- 
add some privledges to venuerole
--select and insert on Artist, ArtistGenre
--Select and insert on show
--Select and insert on ticketoutlet
--add a password field to the Venue
*/

---

Here is the code for creating a login. First we had to make some changes in SQL Server. We added a password field to the Venue Table

---

Use VenueTracker 

Alter Table Venue
add VenuePassword varbinary(500)

---

Then we added passwords for each of the venues. Your VenueIDs may be different than mine

---

Declare @password varbinary(500)
Set @password = HASHBYTES('MD5', 'arena')
update Venue 
Set VenuePassword=@password
where VenueID=1
go
Declare @password varbinary(500)
Set @password = HASHBYTES('MD5', 'gorge')
update Venue 
Set VenuePassword=@password
where VenueID=2
go
Declare @password varbinary(500)
Set @password = HASHBYTES('MD5', 'tractor')
update Venue 
Set VenuePassword=@password
where VenueID=3

go
Declare @password varbinary(500)
Set @password = HASHBYTES('MD5', 'comet')
update Venue 
Set VenuePassword=@password
where VenueID=4

go
Declare @password varbinary(500)
Set @password = HASHBYTES('MD5', 'nuemos')
update Venue 
Set VenuePassword=@password
where VenueID=6

go
Declare @password varbinary(500)
Set @password = HASHBYTES('MD5', 'jazz alley')
update Venue 
Set VenuePassword=@password
where VenueID=9

---

Next we added some permissions to the VenueRole

---

Grant Select on Artist to VenueRole
Grant Insert on Artist to VenueRole
Grant Select on Show to VenueRole
Grant Insert on show to VenueRole
Grant Select on TicketOutlet to VenueRole
Grant Insert on TicketOutlet to Venuerole
Grant Select on Venue to VenueRole
Grant Update on Venue to VenueRole
Grant Select on ArtistGenre to VenueRole
Grant Insert on ArtistGenre to Venuerole

---

Next we went to Visual Studio 2010 and started an empty web site. We added a web page and then added a LINQ to SQL Classes. Then we added a new data connection using SQL server Authentication and the VenueLogin. We dragged on all the tables and stored procedures available in that login.

---

Next we added a new item, a class called PasswordHash. The purpose is to take the password entered into the login control and convert it to a MD5 hash

---

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Text;
using System.Security.Cryptography;
using System.Text.RegularExpressions;

/// 

/// Summary description for PasswordHash
/// 
public class PasswordHash
{
    
 public PasswordHash()
 {
  
 }

    //I changed this to return a Byte array instead of a string
    //that makes it work
    public Byte[] hashedpassword(string pass)
    {
        Byte[] originalBytes;
        Byte[] encodedBytes;
        MD5 md5=MD5.Create(); //this is also a change

      
        originalBytes = ASCIIEncoding.Default.GetBytes(pass);
        encodedBytes = md5.ComputeHash(originalBytes);
        //string hashstr = ConvertBytes(encodedBytes);
        return encodedBytes;

    }

    //No longer need this method though it is a neat
    //use of Regular expressions

    //private string ConvertBytes(Byte[] encodedBytes)
    //{
    //    string x = BitConverter.ToString(encodedBytes);
    //    Regex rgx = new Regex("[^a-zA-Z0-9]");
    //    x = rgx.Replace(x, "");
    //    return "0x" + x;
    //   // return x;
       
    //}

}

---

Here is the login class

---

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

/// 

/// Summary description for LoginClass
/// 
public class LoginClass
{
    string name, pass;
 public LoginClass(string name, string pass)
 {
        this.name = name;
        this.pass = pass;
 }

    public int ValidateLogin()
    {
        int vID = 0;
        PasswordHash ph = new PasswordHash();
        Byte[] hashed = ph.hashedpassword(pass);

        VenueClassesDataContext context = new VenueClassesDataContext();

        var log = from l in context.Venues
                  where l.VenueName == name && l.VenuePassword == hashed
                  select new { l.VenueID, l.VenueName, l.VenuePassword };
        //match it as byte[] instead of string
       //&& l.VenuePassword.ToString() == hashed

        if (log != null)
        {
            foreach (var i in log)
            {
                Console.WriteLine(i.VenuePassword);
                vID = i.VenueID;
  
            }
        }
            


        return vID;
    }
}

Here is the code behind for Default.aspx.cs

---

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

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        //PasswordHash ph = new PasswordHash();
        //string passwrd = ph.hashedpassword("arena");
        //Response.Write(passwrd);
    }
    protected void Login1_Authenticate(object sender, AuthenticateEventArgs e)
    {
        LoginClass lc = new LoginClass(Login1.UserName, Login1.Password);
        int id = lc.ValidateLogin();
        Response.Write(id.ToString());
        if (id != 0)
        {
            Session["venueid"] = id;
            
            e.Authenticated = true;
            Response.Redirect("Default2.aspx");
        }
        else
        {
            e.Authenticated = false;
        }
    }
}

---

Here is the source for Default.aspx

---

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

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" 
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <h1>Venue Login</h1>
        <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" onauthenticate="Login1_Authenticate">
            <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>

---

Here is the source code 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>
    <h1>Welcome</h1>
    </div>
    </form>
</body>
</html>

Dynamically generating Checkboxes and accessing their content

Here is code which automatically generates checkboxes based on an arraylist of strings. The trick to accessing the checkboxes afterwards is to also store them in a an arraylist. Here is the code for CheckboxTest.java

---

package com.spconger.www;
/*
 * This class creates two arrays
 * one for a list of titles
 * and one to store dynamically created
 * checkboxes
 */
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.util.ArrayList;

import javax.swing.*;

public class CheckboxTestForm {
 
 //declare private fields
 private ArrayList titles; //arraylist for titles
 private JFrame frame;
 private ArrayListchecks; //array of checkboxes
 private JPanel panel;
 private JButton button;
 private JLabel result;
 private JCheckBox chk;
 
 //constructor that calls the methods
 //and initializes the checkbox array
 public CheckboxTestForm(){
  fillArray();
  checks = new ArrayList();
  createCheckBoxes();
 }
 
 private void fillArray(){
  //this method populates the array
  //manually--you could do this
  //dynamically with user input
  titles=new ArrayList();
  titles.add("Foundation Trilogy");
  titles.add("lord of the Rings");
  titles.add("1@84");
  titles.add("The Martian Chronicles");
 }
 
 private void createCheckBoxes(){
  //set up the frame
  JFrame frame=new JFrame();
  frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
  
  //call the method to create the panel
  createPanel();
  //add the panel to the frame and make it visible
  frame.add(panel);
  frame.setBounds(100,100,300,300);
  frame.setVisible(true);
 }
 
 private void createPanel(){
  //initialize the panel
  panel = new JPanel();
  panel.setLayout(new GridLayout(0,1,5,5));
  //loop through the title array
  for (String s:titles){
    //create a new checkbox 
   //for each string in the array
    chk=new JCheckBox(s);
    //add the checkbox to the panel
    panel.add(chk);
    //add the checkbox to the array
    //of checkboxes
    checks.add(chk);
  }
  
  //add a button
  button=new JButton("Test");
  //assign a listener
  button.addActionListener(new buttonListener());
  //add it to the panel
  panel.add(button);
  
  //add a label to show the results
  result = new JLabel();
  panel.add(result);
  
  
 }
 
 private class buttonListener implements ActionListener{
  /*
   * (non-Javadoc)
   * @see java.awt.event.ActionListener#actionPerformed(java.awt.event.ActionEvent)
   * This class responds to the button click
   */
  @Override
  public void actionPerformed(ActionEvent arg0) {
   String s="";
   //loop through the array of checkboxes
   //and get the text of each checkbox
   //that is checked
   for (JCheckBox ck:checks){
    if(ck.isSelected()){
    s += ck.getText() + ", ";
    }
   }
   //display the results
   result.setText(s);
  }
  
 }

}

---

Here is the code for Program.java

---

package com.spconger.www;

public class Program {

 /**
  * this program is an example of 
  * creating checkboxes dynamically
  * on the fly. You can use this in
  * a todo list. Add your tasks to an 
  * Array List and then dynamically display them
  * The main merely calls the class
  */
 public static void main(String[] args) {
  CheckboxTestForm c= new CheckboxTestForm();

 }

}