net web forms
Download
Skip this Video
Download Presentation
.NET Web Forms

Loading in 2 Seconds...

play fullscreen
1 / 64

.NET Web Forms - PowerPoint PPT Presentation


  • 314 Views
  • Uploaded on

.NET Web Forms. Database Queries © 2002 by Jerry Post. Why do we Need Queries. Natural languages (English) are too vague With complex questions, it can be hard to verify that the question was interpreted correctly, and that the answer we received is truly correct.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about '.NET Web Forms' - richard_edik


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
net web forms

.NET Web Forms

Database Queries

© 2002 by Jerry Post

why do we need queries
Why do we Need Queries
  • Natural languages (English) are too vague
    • With complex questions, it can be hard to verify that the question was interpreted correctly, and that the answer we received is truly correct.
    • Consider the question: Who are our best customers?
  • We need a query system with more structure
  • We need a standardized system so users and developers can learn one method that works on any (most) systems.
    • Query By Example (QBE)
    • SQL
four questions to create a query
Four Questions to Create a Query
  • What output do you want to see?
  • What do you already know (or what constraints are given)?
  • What tables are involved?
  • How are the tables joined together?
organization
Organization
  • Single table
  • Constraints
  • Computations
  • Groups/Subtotals
  • Multiple Tables
sample questions
List all animals with yellow in their color.

List all dogs with yellow in their color born after 6/1/01.

List all merchandise for cats with a list price greater than $10.

List all dogs who are male and registered or who were born before 6/1/01 and have white in their color.

What is the average sale price of all animals?

What is the total cost we paid for all animals?

List the top 10 customers and total amount they spent.

How many cats are in the animal list?

Count the number of animals in each category.

List the CustomerID of everyone who bought something between 4/1/01 and 5/31/01.

List the first name and phone of every customer who bought something between 4/1/01 and 5/31/01.

List the last name and phone of anyone who bought a registered white cat between 6/1/01 and 12/31/01.

Which employee has sold the most items?

Sample Questions
query by example sql
List all animals with yellow in their color.

Query04_01

Query By Example & SQL

What tables?

What

conditions?

SELECT AnimalID, Category, Breed, Color

FROM Animal

WHERE (Color LIKE “*yellow*”);

What to see?

basic sql select
Basic SQL SELECT

SELECT columns What do you want to see?

FROM tables What tables are involved?

JOIN conditions How are the tables joined?

WHERE criteria What are the constraints?

order by
ORDER BY

SELECT columns

FROM tables

JOIN join columns

WHERE conditions

ORDER BY columns (ASC DESC)

Name Category Breed

Cathy Bird African Grey

Bird Canary

Debbie Bird Cockatiel

Bird Cockatiel

Terry Bird Lovebird

Bird Other

Charles Bird Parakeet

Curtis Bird Parakeet

Ruby Bird Parakeet

Sandy Bird Parrot

Hoyt Bird Parrot

Bird Parrot

SELECT Name, Category, Breed

FROM Animal

ORDER BY Category, Breed;

distinct
DISTINCT

SELECT Category

FROM Animal;

SELECT DISTINCT Category

FROM Animal;

Category

Fish

Dog

Fish

Cat

Cat

Dog

Fish

Dog

Dog

Dog

Fish

Cat

Dog

. . .

Category

Bird

Cat

Dog

Fish

Mammal

Reptile

Spider

constraints and
List all dogs with yellow in their color born after 6/1/01.

Query04_02

Constraints: And

SELECT AnimalID, Category, DateBorn

FROM Animal

WHERE ((Category="Dog") AND (Color Like "*Yellow*")

AND (DateBorn>#6/1/2001#));

conditions and or
List all dogs who are male and registered or who were born before 6/1/2001 and have white in their color.

Query04_03

Conditions: AND, OR

SELECT AnimalID, Category, Gender, Registered, DateBorn, Color

FROM Animal

WHERE (( Category="Dog") AND

( ( (Gender="Male") AND (Registered Is Not Null) ) OR

( (DateBorn<#6/1/2001#) AND (Color Like "*White*") ) ) );

oracle views and sql
Oracle Views and SQL

CREATE VIEW Pets.Example AS

SELECT Pets.Animal.AnimalID,

Pets.Animal.Breed,

Pets.Animal.Category,

Pets.Animal.Color

FROM Pets.Animal

WHERE (Pets.Animal.Color LIKE ‘%Yellow%’)

SQL version is created by the Oracle View Wizard.

The CREATE VIEW command saves it with the specified name.

simple computations
Simple Computations

OrderItem(OrderID, ItemID, Price, Quantity)

Select OrderID, ItemID, Price, Quantity, Price*Quantity As Extended

From OrderItem;

OrderID ItemID Price Quantity Extended

151 9764 19.50 2 39.00

151 7653 8.35 3 25.05

151 8673 6.89 2 13.78

Basic computations (+ - * /) can be performed on numeric data.

The new display column should be given a meaningful name.

computations aggregation avg
What is the average sale price of all animals?

Sum

Avg

Min

Max

Count

StDev

Var

Query04_04

Computations: Aggregation--Avg

SELECT Avg(SalePrice) AS AvgOfSalePrice

FROM SaleAnimal;

computations math operators
What is the total value of the order for PONumber 22?

Use any common math operators on numeric data.

Operate on data in one row at a time.

OrderTotal

1798.28

Query04_05

Computations (Math Operators)

SELECT Sum([Quantity]*[Cost]) AS OrderTotal

FROM OrderItem

WHERE (PONumber=22);

subtotals where
How many cats are in the Animal list?

Query04_06

Subtotals (Where)

SELECT Count(AnimalID) AS CountOfAnimalID

FROM Animal

WHERE (Category = “Cat”);

groups and subtotals
Count the number of animals in each category.

You could type in each WHERE clause, but that is slow.

And you would have to know all of the Category values.

Category CountOfAnimalID

Dog 100

Cat 47

Bird 15

Fish 14

Reptile 6

Mammal 6

Spider 3

Query04_07

Groups and Subtotals

SELECT Category, Count(AnimalID) AS CountOfAnimalID

FROM Animal

GROUP BY Category

ORDER BY Count(AnimalID) DESC;

conditions on totals having
Count number of Animals in each Category, but only list them if more than 10.

Category CountOfAnimal

Dog 100

Cat 47

Bird 15

Fish 14

Query04_08

Conditions on Totals (Having)

SELECT Category, Count(AnimalID) AS CountOfAnimalID

FROM Animal

GROUP BY Category

HAVING Count(AnimalID) > 10

ORDER BY Count(AnimalID) DESC;

where detail v having group
Count Animals born after 6/1/2001 in each Category, but only list Category if more than 10.

Category CountOfAnimalID

Dog 30

Cat 18

Query04_09

Where (Detail) v Having (Group)

SELECT Category, Count(AnimalID) AS CountOfAnimalID

FROM Animal

WHERE DateBorn > #6/1/2001#

GROUP BY Category

HAVING Count(AnimalID) > 10

ORDER BY Count(AnimalID) DESC;

multiple tables intro distinct
List the CustomerID of everyone who bought something between 4/1/01 and 5/31/01.

CustomerID

6

8

14

19

22

24

28

36

37

38

39

42

50

57

58

63

74

80

90

Query04_10

Multiple Tables (Intro & Distinct)

Avoid Duplicates

with DISTINCT

SELECT DISTINCT CustomerID

FROM Sale

WHERE (SaleDate Between #4/1/01# And #5/31/01#)

ORDER BY CustomerID;

joining tables
List LastNames of Customers who bought between 4/1/2001 and 5/31/2001.

CustomerID LastName

22 Adkins

57 Carter

38 Franklin

42 Froedge

63 Grimes

74 Hinton

36 Holland

6 Hopkins

50 Lee

58 McCain

37 McPherson

90 Nichols

14 Patterson

8 Reid

28 Samuels

80 Vance

39 Williams

24 Young

19 Zhang

Query04_11

Joining Tables

SELECT DISTINCT Sale.CustomerID, Customer.LastName

FROM Customer

INNER JOIN Sale ON Customer.CustomerID = Sale.CustomerID

WHERE (Sale.SaleDate Between #4/1/2001# And #5/31/2001#)

ORDER BY Customer.LastName;

sql join
SQL JOIN

FROM table1

INNER JOIN table2

ON table1.column = table2.column

SQL 92 syntax (Access and SQL Server)

FROM table1, table2

WHERE table1.column = table2.column

SQL 89 syntax (Oracle)

FROM table1, table2

JOIN table1.column = table2.column

Informal syntax

syntax for three tables
Syntax for Three Tables

SQL ‘92 syntax to join three tables

FROM Table1

INNER JOIN (Table2 INNER JOIN Table3

ON Table2.ColA = Table3.ColA)

ON Table1.ColB = Table2.ColB

Easier notation, but not correct syntax

FROM Table1, Table2, Table3

JOIN Table1.ColB = Table2.ColB

Table2.ColA = Table3.ColA

multiple tables many
List the Last Name and Phone of anyone who bought a registered White cat between 6/1/2001 and 12/31/2001.

Query04_12

Multiple Tables (Many)

SELECT DISTINCTROW Customer.LastName, Customer.Phone

FROM Customer INNER JOIN (Sale INNER JOIN (Animal INNER JOIN SaleAnimal

ON Animal.AnimalID = SaleAnimal.AnimalID) ON Sale.SaleID = SaleAnimal.SaleID)

ON Customer.CustomerID = Sale.CustomerID

WHERE ((Animal.Category="Cat") AND (Animal.Registered Is Not Null)

AND (Color Like "*White*") AND (SaleDate Between #6/1/2001# And #12/31/2001#));

building a query
List the Last Name and Phone of anyone who bought a registered White cat between 6/1/01 and 12/31/ 01.

Identify the tables involved.

Look at the columns you want to see.

LastName, Phone: Customer

Look at the columns used in the constraints.

Registered, Color, Category: Animal

Sale Date: Sale

Find connector tables.

To connect Animal to Sale: SaleAnimal

Select the desired columns and test the query.

Enter the constraints.

Set Order By columns.

Add Group By columns.

Add summary computations to the SELECT statement.

Building a Query
joining tables hints
Build Relationships First

Drag and drop

From one side to many side

Avoid multiple ties between tables

SQL

FROM Table1

INNER JOIN Table2

ON Table1.ColA = Table2.ColB

Join columns are often keys, but they can be any columns--as long as the domains (types of data) match.

Multiple Tables

FROM (Table1

INNER JOIN Table2

ON T1.ColA = T2.ColB )

INNER JOIN Table3

ON T3.ColC = T3.ColD

Shorter Notation

FROM T1, T2, T3

JOIN T1.ColA = T2.ColB

T1.ColC = T3.ColD

Shorter Notation is not correct syntax, but it is easier to write.

Joining Tables (Hints)
tables with multiple joins
Potential problem with three or more tables.

Access uses predefined relationships to automatically determine JOINs.

JOINS might loop.

Most queries will not work with loops.

Tables with Multiple Joins

A query with these four tables with four JOINS would only return rows where the Employee had the same ZipCode as the Supplier. If you only need the Supplier city, just delete the JOIN between Employee and ZipCode. If you want both cities, add the ZipCode table again as a fifth table.

table alias
Table Alias

SELECT Supplier.SID, Supplier.CityID, City.City, Employee.EID, Employee.LastName, Employee.CityID, City2.City

FROM (City INNER JOIN Supplier ON City.CityID = Supplier.CityID) INNER JOIN ((City AS City2 INNER JOIN Employee ON City2.CityID = Employee.CityID) INNER JOIN AnimalOrder ON Employee.EmployeeID = AnimalOrder.EmployeeID) ON Supplier.SupplierID = AnimalOrder.SupplierID;

SID Supplier.CityID City.City EID LastName Employee.CityID City2.City

4 7972 Middlesboro 5 James 7083 Orlando

2 10896 Springfield 1 Reeves 9201 Lincoln

4 7972 Middlesboro 3 Reasoner 8313 Springfield

9 10740 Columbia 8 Carpenter 10592 Philadelphia

5 10893 Smyrna 3 Reasoner 8313 Springfield

saved query create view
Save a query

Faster: only enter once

Faster: only analyze once

Any SELECT statement

Can use the View within other SQL queries.

Saved Query: Create View

CREATE VIEW Kittens AS

SELECT *

FROM Animal

WHERE (Category = ‘cat’) AND (Today - DateBorn < 180);

SELECT Avg(ListPrice)

FROM Kittens

WHERE (Color LIKE “*Black*”);

updateable views
To be updateable, a view must focus on one primary table. (OrderItem)

Goal is to change data in only one table. (OrderItem)

Data can be displayed from other tables. (Item)

Never include or attempt to change primary keys from more than one table. (Item.ItemID)

Updateable Views

OrderItem(OrderID, ItemID, Quantity)

Item(ItemID, Description)

OrderLine(OrderID, ItemID, Description, Quantity)

non updateable view
Non Updateable View

OrderItem(OrderID, ItemID, Quantity)

Item(ItemID, Description)

121 57 3

121 82 2

122 57 1

57 Cat food

58 Dog food

59 Bird food

OrderLine(OrderID, Item.ItemID, Description, Quantity)

121 57 Cat food 3

121 82 Bird feeder 2

122 57 Cat food 1

32

If you attempt to change the Item.ItemID in the OrderLineView:

You will simply change the primary key value in the Item table.

It will not add a new row to the OrderItem table.

no views are updateable in net
No Views are Updateable in .NET
  • Most databases do not really support updateable views very well, and Oracle had lots of problems. So, in .NET, you can only update underlying tables, not the views.
    • This approach complicates your applications.
    • But it is a realistic approach.
primary portability hint
Primary Portability Hint
  • The DBMSs use different variations on SQL, particularly Oracle, which uses an older version.
  • To reduce application maintenance and make your application more portable:
    • Save all relatively complex queries in the database (as views).
    • Then use only simple Select queries in your application.
    • That includes all queries with:
      • JOIN
      • Concatenation
      • Most computations
    • In SQL Server, go a step further and save all of your queries as parameterized views.
sql data definition
SQL Data Definition
  • Create Schema AuthorizationdbNamepassword
  • Create TableTableName (Column Type, . . .)
  • Alter TableTable {Add, Column, Constraint, Drop}
  • Drop {Table Table | Index Index On table}
  • Create IndexIndexName ON Table (Column {ASC|DESC})
syntax examples
Syntax Examples

CREATE TABLE Customer

(CustomerID INTEGER NOT NULL,

LastName CHAR (10),

more columns

);

ALTER TABLE Customer

DROP COLUMN ZipCode;

ALTER TABLE Customer

ADD COLUMN CellPhone CHAR(15);

sql data manipulation commands
SQL Data Manipulation Commands

Insert Intotarget (column1 . . .) VALUES (value1 . . .)

Insert Intotarget (column1 . . .) SELECT . . . FROM. . .

Delete Fromtable WHERE condition

Updatetable SET Column=Value,. . . Where condition

Note the use of the Select and Where conditions.

Synatx is the same--only learn it once.

You can also use subqueries.

copy old animal data
Copy Old Animal Data

INSERT INTO OldAnimals

SELECT *

FROM Animals

WHERE AnimalID IN

(SELECT AnimalOrderItem.AnimalID

FROM AnimalOrder INNER JOIN AnimalOrderItem

ON AnimalOrder.OrderID = AnimalOrderItem.OrderId

WHERE (AnimalOrder.OrderDate<#1/1/01#) );

delete old animal data
Delete Old Animal Data

DELETE

FROM Animals

WHERE AnimalID IN

(SELECT AnimalOrderItem.AnimalID

FROM AnimalOrder INNER JOIN AnimalOrderItem

ON AnimalOrder.OrderID = AnimalOrderItem.OrderId

WHERE (AnimalOrder.OrderDate<#1/1/01#) );

update example
Change the ListPrice of Animals at the PetStore.

For cats, increase the ListPrice by 10%.

For dogs, increase the ListPrice by 20%.

Typically use two similar UPDATE statements.

With the CASE function, the statements can be combined.

Update Example

UPDATE Animal

SET ListPrice = ListPrice*1.10

WHERE Category = “Cat” ;

UPDATE Animal

SET ListPrice = ListPrice*1.20

WHERE Category = “Dog” ;

parameterized queries
Parameterized Queries
  • Parameterized queries often run faster and are somewhat easier to use in .NET.
  • Support for parameterized queries:
    • SQL Server The best of them all.
    • Access OK, available in most cases.
    • Oracle OK for action queries but hard with Select.
    • DB2 Unknown.
  • SQL Server:

CREATE PROCEDURE GetOrder (@OrderID int) AS

SELECT OrderID, OrderDate, CustomerID

FROM Order WHERE OrderID = @OrderID

RETURN

  • Even if you cannot (or do not want to) create parameterized queries in the DBMS, we will create them within the code:

cmdMine.CommandText = “SELECT OrderID, OrderDate, CustomerID FROM Order WHERE OrderID = ?

importance of parameterized queries
Importance of Parameterized Queries
  • Consider a typical SQL Update command:

UPDATE Customer SET LastName = ‘O’Malley’ WHERE CustomerID=222

  • Note the problem with the name—it includes an apostrophe, which also happens to be the string delimiter for SQL. So, this statement will crash.
  • The statement will still fail even if you create a variable (sLastName = “O’Malley”)
  • strSQL = “UPDATE Customer SET LastName = “ & sLastName …
  • But, if you create a parameterized query, it will work!

strSQL = “UPDATE Customer SET LastName=? WHERE CustomerID=?”

Parameter 0 is “O’Malley”

Parameter 1 is 222

  • You must always use parameterized queries for UPDATE and INSERT, so you might as well use them all of the time.
sql syntax list
SQL Syntax List
  • ALTER TABLE
  • COMMIT
  • CREATE INDEX
  • CREATE TABLE
  • CREATE VIEW
  • DELETE
  • DROP
  • INSERT
  • GRANT
  • REVOKE
  • ROLLBACK
  • SELECT
  • SELECT INTO
  • UPDATE
sql syntax alter table
SQL Syntax: ALTER TABLE

ALTER TABLE table

ADD COLUMN column datatype (size)

DROP COLUMN column

CREATE TABLE

See also:

DROP TABLE

Return to List

sql syntax commit
SQL Syntax: COMMIT

COMMIT WORK

ROLLBACK

See also:

Return to List

sql syntax create index
SQL Syntax: CREATE INDEX

CREATE [UNIQUE] INDEX index

ON table (column1, column2, … )

WITH {PRIMARY | DISALLOW NULL | IGNORE NULL}

CREATE TABLE

See also:

Return to List

sql syntax create table
SQL Syntax: CREATE TABLE

CREATE TABLE table

(

column1 datatype (size) [NOT NULL] [index1] ,

column2 datatype (size) [NOT NULL] [index2],

… ,

CONSTRAINT pkname PRIMARY KEY (column, …),

CONSTRAINT fkname FOREIGN KEY (column)

REFERENCES existing_table (key_column),

)

ALTER TABLE

See also:

DROP TABLE

Return to List

sql syntax create view
SQL Syntax: CREATE VIEW

CREATE VIEW viewname AS

SELECT …

SELECT

See also:

Return to List

sql syntax delete
SQL Syntax: DELETE

DELETE

FROM table

WHERE condition

DROP

See also:

Return to List

sql syntax drop
SQL Syntax: DROP

DROP INDEX index ON table

DROP TABLE

DROP VIEW

DELETE

See also:

Return to List

sql syntax grant
SQL Syntax: GRANT

GRANT privilege privileges

ON object ALL, ALTER, DELETE, INDEX,

TO user | PUBLIC INSERT, SELECT, UPDATE

REVOKE

See also:

Return to List

sql syntax insert
SQL Syntax: INSERT

INSERT INTO table (column1, column2, …)

VALUES (value1, value2, … )

INSERT INTO newtable (column1, column2, …)

SELECT …

SELECT

See also:

Return to List

sql syntax revoke
SQL Syntax: REVOKE

REVOKE privilege privileges

ON object ALL, ALTER, DELETE, INDEX,

FROM user | PUBLIC INSERT, SELECT, UPDATE

GRANT

See also:

Return to List

sql syntax rollback
SQL Syntax: ROLLBACK

SAVEPOINT savepoint {optional}

ROLLBACK WORK

TO savepoint

COMMIT

See also:

Return to List

sql syntax select
SQL Syntax: SELECT
  • SELECT DISTINCT table.column {AS alias} , . . .
  • FROM table/query
  • INNER JOIN table/query ON T1.ColA = T2.ColB
  • WHERE (condition)
  • GROUP BY column
  • HAVING (group condition)
  • ORDER BY table.column
  • { UNION, INTERSECT, EXCEPT … }
  • GROUP BY CUBE (dimension1, dimension2, …)
  • TRANSFORM aggfunction {Crosstab values}
    • SELECT . . . FROM . . . GROUP BY {Crosstab rows}
    • PIVOT pivot column {Crosstab columns}

Return to List

sql syntax select into
SQL Syntax: SELECT INTO

SELECT column1, column2, …

INTO newtable

FROM tables

WHERE condition

SELECT

See also:

Return to List

sql syntax update
SQL Syntax: UPDATE

UPDATE TABLE table

SET column1 = value1, column2 = value2, …

WHERE condition

DELETE

See also:

Return to List

ad