Transcript Slide 1

Chapter 11 – Introduction to Databases
Dr. Stephanos Mavromoustakos
Chapter Overview
This chapter will cover:
 What is a database, and what databases are usually used
with ASP.NET pages?
 What is SQL, how does it look, and how do you use it?
 How do you retrieve data from a database using the
SELECT statement?
 How do you manipulate data in a database using other SQL
constructs?
 What are database relations and why are they important?
 What tools do you have available in VWD o manage objects
like tables and queries, and how do you use these tools?
What is a Database?
 A database is a collection of data that is easily
accessed, managed and updated.
 The most popular type of database is the relational
database. A relational table has the notion of tables
where data is stored in rows and columns.
 You can use different kinds of databases in your
ASP.NET projects, including Access, SQL Server,
Oracle, and MySQL.
 We will be using Microsoft SQL Server 2005 Express
edition as it comes for free with VWD.
Practice – Connecting to the SQL
Server Sample Database
In this exercise you learn how to connect to and work with a
database within VWD.
 Create a new Web site. Go to FileNew Web Site
 Make sure you have enough permission to write to its App_Data
folder. Using Windows Explorer, locate the App_Data of this
new site and right-click on the folder. Select Properties and
switch to the security tab. Ensure your account has at least the
Modify permission
 Locate the files PlanetWrox.mdf and PlanetWrox.ldf in
c:\BegASPNET\Source\Chapter 11\App_Data. Arrange
VWD and the Windows Explorer side by side and then drag the
two files into the App_Data folder of your web site in VWD. Click
Yes when you’re asked whether you want to overwrite the .ldf file.
The .mdf file is the actual database, while the .ldf file is used to
keep track of changes made to the database.
Practice – Connecting to the SQL
Server Sample Database
 Double-click the database file in the Solution Explorer.
The Database Explorer will open. You can now expand
the connected database to access its objects like the
tables it contains
Retrieving and Manipulating Data
CRUD – Four types of operations
 Create, Read, Update, Delete
Selecting Data
SELECT Id, Name FROM Genre
Filtering Data
SELECT Id FROM Genre WHERE Name = ‘Grunge’
SELECT Name FROM Genre WHERE Id = 8
 We can use other comparison operators as well (=, >, >=, <, <=, <>)
 To combine multiple WHERE criteria, the SQL language supports a
number of logical operators explained in the next table
Retrieving and Manipulating Data
Operator
Description
AND
Join 2 expressions. E.g.
… WHERE Id > 20 AND Id < 30
OR
Define multiple criteria of which only one has to match, e.g.
… WHERE Id = 12 OR Id = 27
BETWEEN
Specify a range of values that you want to match with a lower and
upper bound, e.g.
… WHERE Id BETWEEN 10 AND 35
LIKE
Determine if a value matches a specific pattern. You can use wild
cards like % (any string of zero or more characters) and the
underscore for any single character) to match specific parts of the
value, e.g.
… WHERE Name LIKE ‘%rock%;’
Returns all genres that have rock in their name, including Indie
Rock, Hard Rock, etc
Retrieving and Manipulating Data
Ordering Data
The ORDER BY clause comes at the end of the SQL
statement. Optionally, can include ASC and DESC to
determine the ascending or descending order.
SELECT Id, Name FROM Genre ORDER BY Name ASC
Practice – Selecting Data from the
Sample Database
In this exercise, you use the database
that you connected to in an earlier
exercise. This database is only used for
the samples in this chapter, so don’t
worry if you mess things up.
 Open the Database Explorer, locate
the Data Connection that you added
earlier, expand it, and then expand
the Tables node. You should see two
tables, Genre and Review
Practice – Selecting Data from the
Sample Database
 Right-click the Genre table and choose Show Table Data.
Practice – Selecting Data from the
Sample Database
 Look at the Query Designer toolbar (if you can’t see it,
right-click an existing toolbar and click Query
Designer)
Diagram Pane
Criteria Pane
Change Type of Query
SQL Pane
Results Pane
Verify SQL Syntax
Add Table
Execute SQL
Add Derived Table
Add Group By
Practice – Selecting Data from the
Sample Database
 On the toolbar, click the Criteria pane, the Diagram Pane, and the SQL pane buttons to
open their respective windows. Note that they are displayed in the Document Window in
a stacked order. The first four buttons on the toolbar should now be in pressed state and
the Document Window is split in four regions.
Practice – Selecting Data from the
Sample Database
 In the SQL pane, position your cursor right after the word
Genre, press ENTER once and then type WHERE Id > 4.
Your complete SQL should be:
SELECT *
FROM
Genre
WHERE
Id > 4
 To make sure the SQL statement is valid, click the Verify
SQL Syntax button on the toolbar and fix any errors your
statement may contain. Next, click the Execute SQL button
(or press Ctrl+R) to update the Results pane showing all
genres with an ID larger than 4.
Practice – Selecting Data from the
Sample Database
 Besides showing your results, VWD also changed your query.
Instead of SELECT *, it has listed each column. In the Diagram
pane you can check and uncheck column names to determine
whether they end up in the query. De-select the SortOrder
column. Note that it also gets removed from the Criteria pane
and the SQL statement in the SQL Pane.
 Take a look at the Criteria pane. It shows the two columns you
are selecting. In the Filter column it shows the expression that
filters all genres with an ID larger than 4. In this pane you can
modify the query by applying an additional filter; type LIKE
‘%rock%’ in the Filter cell for the Name row. This limits the
results to all genres that contain the word rock and that have an
ID that is larger than 4.
Practice – Selecting Data from the
Sample Database
 To determine the sort order, you can use the Sort Type
column. To order by the SortOrder column, click the
cell under Name once. It changes and now shows a
drop-down list instead. Choose SortOrder. When
you tab away, VWD places a checkmark in the Output
column. You can click that checkmark to remove the
column again if you want, however, for this exercise it’s
OK to leave the column selected.
 Then in the Sort Type column choose Descending
from the drop-down list for the SortOrder. Your final
Criteria pane now looks like this:
Practice – Selecting Data from the
Sample Database
Practice – Selecting Data from the
Sample Database
 While you make your changes using the Diagram and
Criteria panes, VWD continuously updates the SQL
pane. Your final SQL statement should now include
the extra WHERE clause and the ORDER BY statement:
SELECT
FROM
WHERE
ORDER BY
Id, Name, SortOrder
Genre
(Id > 4) AND (Name LIKE '%rock%')
SortOrder DESC
 Press Ctrl+R again and the Results pane will show the
records from the Genre table that match your criteria
Practice – Selecting Data from the
Sample Database
 Note that the records are sorted in descending order
Joining Data
A JOIN in your query allows you to express a relationship between one or more tables. For
example, you can use a JOIN to find all the reviews from the Review table that have
been published in a specific genre and then select some columns from the Review table
together with the Name of the genre.
SELECT
Review.Id, Review.Title, Genre.Name
FROM
Review
INNER JOIN Genre ON Review.GenreId = Genre.Id
The INNER JOIN returns matching records. The OUTER JOIN allows you to retrieve
records from one table regardless of whether they have a matching record in another
table. E.g. Returns a list with all the genres together with the reviews in each genre:
SELECT
Genre.Id, Genre.Name, Review.Title
FROM
Genre
LEFT OUTER JOIN Review ON Genre.Id = Review.GenreId
For each review assigned to a genre, a unique row is returned that contains the review’s Title.
However, even if a genre has no reviews assigned, the row is still returned
Joining Data
 Besides the LEFT OUTER JOIN, there is also RIGHT
OUTER JOIN that returns all the records from the
table listed at the right side of the JOIN. LEFT and
RIGHT OUTER JOIN statements are very similar, and
in most cases you’ll see the LEFT OUTER JOIN
Practice – Joining Data
To join data from two tables, you need to write a JOIN
statement in your code. VWD helps you in that but most of
the times the code is wrong, therefore you need to correct
it.
 In the Database Explorer, right-click the Review table and
choose Show Table Data. Next, enable the Diagram,
Criteria, and SQL panes by clicking their respective buttons
on the Query Designer toolbar
 Right-click an open spot of the Diagram pane next to the
Review table and choose Add Table.
 In the dialog box that follows, click the Genre table and
then click the Add button. Finally, click Close.
Practice – Joining Data
 The SQL generated didn’t see the relationship between the
GenreId column of the Review table and the Id column of the
Genre table, so instead it joined both tables on their respective
Id fields:
SELECT * FROM Review
INNER JOIN Genre ON Review.Id = Genre.Id
 To correct this error, right-click the line that is drawn between
the two tables and choose Remove
 Next, click the GenreId column of the Review table in the
Diagram pane once and then drag it onto the Id column of the
Genre table. The VWD creates the new code for you:
SELECT * FROM Review
INNER JOIN Genre ON Review.GenreId = Genre.Id
Practice – Joining Data
 In the Criteria pane, click the left margin of the first
row that contains the asterisk symbol to select the
entire row and then press the Delete key or right-click
the left margin and choose Delete. This removes the
asterisk from the SQL statement. Alternatively, you
can delete it from the SQL directly.
 In the Diagram pane place a checkmark in front of the
Id and Title columns of the Review table and in
front of the Name column of the Genre table
 Finally, press Ctrl+R to execute the query
Practice – Joining Data
 The results of the query are shown in the Results pane
Creating, Updating, and Deleting
Data
 To insert new records in a SQL Server table, you use the
INSERT statement. E.g.
INSERT INTO Genre (Name, SortOrder) VALUES
(‘Tribal House’, 20)
 To update data in a table, you use the UPDATE statement,
e.g.
UPDATE Genre SET Name = ‘Trance’, SortOrder
= 5 WHERE Id =13
 To delete a record, you don’t need to specify any column
names, e.g.
DELETE FROM Genre WHERE Id = 13
Practice – Working with Data in the
Sample Database
In this exercise, you will create a new record in the Genre
table, select it again to find out its new ID, update it using
the UPDATE statement, and finally delete the genre from
the database.
 Open the Database Explorer and locate the Genre table.
Right-click it and choose Show Table Data. If the table was
already open with an old query, you need to close it first by
pressin Ctrl+F4. This gets rid of the existing SQL statement
 Click the first three buttons on the Query Designer toolbar
(Diagram, Criteria, and SQL pane)
 In the Diagram pane, check the columns Name and
SortOrder. Make sure you leave Id unchecked
Practice – Working with Data in the
Sample Database
 On the Query Designer toolbar click the Change Type
button and then choose the third option; Insert
Values. The query in the SQL pane is updated and now
contains a template for the INSERT statement.
 Between the parentheses for the VALUES, enter a
name (between apostrophes) and a sort order for your
genre separated by a comma:
INSERT INTO Genre
(Name, SortOrder)
VALUES
('Folk', 15)
Practice – Working with Data in the
Sample Database
 Press Ctrl+R to execute the query. You should get a dialog box
that tells you that your action caused one row to be affected
 Click OK to dismiss the dialog box
 Clear out the entire SQL statement and replace it with this code
that selects all the genres and sorts them in descending order
SELECT Id, Name FROM Genre ORDER BY Id DESC
Practice – Working with Data in the
Sample Database
 Press Ctrl+R to execute the SELECT statement. The Results pane shows
a list of genres with the one you just inserted at the top of the list. Note
the ID of the newly inserted record. It should be 13.
 Click the Change Type button again, this time choosing Update.
Complete the SQL statement that VWD created so it looks like this:
UPDATE
Genre
SET
Name = 'British Folk',
SortOrder = 5
WHERE
Id = 13
Practice – Working with Data in the
Sample Database
 Press Ctrl+R again to execute the query.
 Once again, clear the SQL pane and then enter and execute the
following query by pressing Ctrl+R:
SELECT Id, Name FROM Genre WHERE Id = 13
You should see the updated record appear
 On the Query Designer toolbar, click the Change Type button and
choose Delete. VWD changes the SQL statement so it is now set up to
delete the record with an ID of 13:
DELETE FROM Genre WHERE Id = 13
 Press Ctrl+R to execute the query again and delete it
 To confirm that the record is deleted, click the Change Type button and
choose Select. Then choose one or more columns of the Genre table in
the Diagram pane and press Ctrl+R again. You’ll see that this time no
records are returned, confirming the newly inserted genre has indeed
been deleted from the database
Creating your own Tables
The SQL Server 2005 has its own data types:
SQL 2005 Data Type Description
.NET Data Type
Bit
Boolean values in 0 / 1
System.Boolean
Char / nchar
Fixed-length text. The nchar stores
the data in Unicode format
System.String
Datetime
Stores a date and a time
System.DateTime
Decimal
Stores large, fractional numbers
System.Decimal
Float
Stores large, fractional numbers
System.Double
Image
Stores binary objects
System.Byte[]
Tinyint
Stores integers from 0 – 255
System.Byte
Smallint
Stores integers from -32768-32677
System.Int16
Int
Stores integers from -2,147,483,648 2,147,483,647
Systen.Int32
Creating your own Tables
SQL 2005 Data Type
Description
.NET Data Type
Text / ntext
Stores large amounts of text
System.String
Varchar /
nvarchar
Stores text with a variable length. The System.String
nvarchar stores the data in Unicode
format
Uniqueidentifier
Stores globally unique identifiers
System.Guid
When you define a column of type char, nchar, varchar, or nvarchar you need
to specify the length in characters. For example, an nvarchar (10) allows you to
store a maximum of 10 characters. You can also specify MAX as the maximum size.
With the MAX specifier, you can store data up to 2GB in a single column. For large
pieces of text, like the body of a review, you should consider the nvarchar (max)
data type. If you have a clear idea about the maximum length for a column (like a
zip code or a phone number), you should specify that length instead. For example,
the title of a review could be stored in a nvarchar(200) column to allow up to
200 characters.
Creating your own Tables
Understanding Primary Keys and Identities
 To uniquely identify a record in a table, you can set up a
primary key. A primary key consists of one or more
columns in a table that contains a value that is unique
across all records.
 SQL Server also supports identity columns. An identity
column is a numeric column whose values are generated
automatically whenever a new record is inserted. They are
often used as the primary key for a table.
 It is not a requirement to give each table a primary key, but
it makes your life as a database programmer a lot easier, so
it’s recommended to always add one to your tables.
Creating Tables in the Table
Designer
In this exercise, you will add two tables to a new database.
This exercise should be using your Planet Wrox project.
You can close and delete the test site you created at the
beginning of this chapter
 Right-click the App_Data folder and choose Add New Item.
Click SQL Server Database, type PlanetWrox.mdf as the
name, and then click Add.
 On the Database Explorer, right-click the Tables node and
choose Add New Table
 Enter column names and data types that together make up
the table definition. Create three columns for the Id,
Name, and SortOrder of the Genre table. See the next
figure:
Creating Tables in the Table
Designer
 Make sure you clear the checkbox for all items in the Allow Nulls column. This
column determines if fields are optional or required. In the case of the Genre
table, all three columns are required, so you need to clear the Allow Nulls
column
 Next, select the row for the Id by clicking in the margin on the left and then on
the Table Designer toolbar. Click the second button from the left to turn the Id
into a primary key.
Creating Tables in the Table
Designer
 Below the table definition you see the Column Properties. With the Id column
still selected, scroll down a bit on the Column Properties until you see Identity
Specification. Expand the item and then set (Is Identity) to Yes.
Creating Tables in the Table
Designer
 Press Ctrl+S to save your changes. A dialog box pops up that allows you to provide a name
for the table. Type Genre and click OK.
 Create another table following the steps before, but this time create a table with the
following specs to hold the CD and concert reviews for the Planet Wrox web site:
Column Name
Data Type
Allow Nulls
Description
Id
int
No
Primary key
Title
nvarchar(200)
No
Title of the review
Summary
nvarchar(max)
No
Short summary for the review
Body
nvarchar(max)
Yes
The full body text of the review
GenreId
int
No
The ID of a genre that the review belongs to
Authorized
bit
No
Determines whether a review is authorized
for publication by an administrator.
Unauthorized reviews will not be visible on
the web site
CreateDateTime
datetime
No
Date and Time a review is created
UpdateDateTime
datetime
No
Date and Time the review is last update
Creating Tables in the Table
Designer
 Make the Id column the primary key again, and set its
(Is Identity) property to Yes
 Click the CreateDateTime column once and then
on the Column Properties, type GetDate() in the
field for the Default Value or Binding property
 Repeat the previous step for the UpdateDateTime
column
 When you’re done, press Ctrl+S to save the table and
call it Review
Creating Relationships Between
Tables
You can define a relationship by creating a relationship
between the primary key of one table, and a column in
another table. The column in this second table is often
referred to as a foreign key. In the case of the Review
and Genre tables, the GenreId column of the
Review table points to the primary key column Id of
the Genre table, thus making GenreId a foreign key.
Creating a Relationship Between
Two Tables
Before you can add a relationship between two tables, you need to add a
diagram to your database. In this exercise, you will create a relationship
between the Review and Genre tables.
 Open up the Database Explorer for the Planet Wrox site. Right-click
the Database Diagrams and click Add New Diagram. If this is the first
time you are adding a diagram to the database, you may get a dialog box
asking if you want VWD to make you the owner of the database. Click
Yes to proceed. This may be followed by another dialog box; click Yes
again to proceed
Creating a Relationship Between
Two Tables
 In the Add Table dialog box
that follows, select both
tables (hold down the Ctrl
Key while you click each
item), click Ad to add them
to the diagram, and then
click Close
 Arrange the tables in the
diagram using drag and drop
so they are positioned next to
each other
 On the Genre table, click
the left margin of the Id
column and then drag it onto
the GenreId column of the
Review table and release
your mouse
Creating a Relationship Between
Two Tables
 Two dialog boxes pop up that allow you to customize the defaults for
the relation. Click OK to dismiss the top window. In the dialog box that
remains, notice how Enforce Foreign Key Constraint is set to Yes. This
property ensures that cannot delete a record from the Genre table if it
still has reviews attached to it. Click OK to dismiss this dialog box as
well
Creating a Relationship Between
Two Tables
 The diagram window should now show a line between the two tables. At the
side of the Genre table you should see a yellow key to indicate the primary key.
At the other end you should see the infinity symbol (the number 8 turned 90
degrees) to indicate that the Review table can have many records that use the
same GenreId.
Creating a Relationship Between
Two Tables
 Press Ctrl+S to save the changes to the diagram. Give a
descriptive name like ReviewsAndGenres and click OK. You’ll get
another warning that states that you are about to make changes
to the Review and Genre tables. Click Yes to apply the changes.
 Go back to the Database Explorer, right-click the Genre table
and choose Show Table Data. Enter some data like below.
Creating a Relationship Between
Two Tables
 Open the Review table and enter some reviews, like below. Set
Authorized to True. You can leave out the dates; the database
will insert the default values. To insert a new row, click outside
the row and then Ctrl+R to insert the row in the table.
Creating a Relationship Between
Two Tables
 Right-click the Genre table again and choose Show Table Data.
Click the SQL pane button and then use the Change Type button
to create a delete query. Modify the query so it looks like this:
DELETE FROM Genre WHERE Id = 2
 Press Ctrl+R to execute the query. Instead of deleting the record
from the Genre table, VWD now shows you the dialog box
below:
HOW IT WORKS
When you try to delete a record
from the Genre table, the
database sees that the genre is
used by a record in the Review
table and cancels the delete
operation