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

AND OR BETWEEN LIKE

Description

Join 2 expressions. E.g.

… WHERE Id > 20 AND Id < 30 Define multiple criteria of which only one has to match, e.g.

… WHERE Id = 12 OR Id = 27 Specify a range of values that you want to match with a lower and upper bound, e.g.

… WHERE Id BETWEEN 10 AND 35 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 determine the ascending or descending order.

to 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 SQL Pane Change Type of Query Verify SQL Syntax Add Table Criteria Pane Results Pane Execute SQL Add Group By Add Derived Table



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 and the SQL statement in the SQL Pane.

SortOrder column. Note that it also gets removed from the Criteria 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 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.

. When  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 Id, Name, SortOrder FROM Genre WHERE (Id > 4) AND (Name LIKE '%rock%') ORDER BY 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 SELECT 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: 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 it.

JOIN statement in your code. VWD helps you in that but most of the times the code is wrong, therefore you need to correct  In the Database Explorer, right-click the on the Query Designer toolbar Review table and choose Show Table Data. Next, enable the Diagram, Criteria, and SQL panes by clicking their respective buttons   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 then click the Add button. Finally, click Close.

table and

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 Diagram pane once and then drag it onto the Id table in the 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 e.g.

statement, 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 a list of genres with the one you just inserted at the top of the list. Note the ID SELECT statement. The Results pane shows 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 been deleted from the database 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

Creating your own Tables

The SQL Server 2005 has its own data types:

SQL 2005 Data Type Description .NET Data Type

Bit Char / nchar Boolean values in 0 / 1 Fixed-length text. The nchar stores the data in Unicode format System.Boolean

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

Text / ntext Varchar / nvarchar Uniqueidentifier

Description

Stores large amounts of text Stores text with a variable length. The nvarchar stores the data in Unicode format Stores globally unique identifiers

.NET Data Type

System.String

System.String

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 Name , and SortOrder figure: of the Genre Id , table. See the next



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 column Genre table, all three columns are required, so you need to clear the Allow Nulls  Next, select the row for the the Table Designer toolbar. Click the second button from the left to turn the Id into a primary key.

Id by clicking in the margin on the left and then on



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:

Allow Nulls Description Column Name

Id

Data Type

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 Date and Time a review is created CreateDateTime datetime No 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 Review tables, the GenreId column of the table points to the primary key column Id the Genre table, thus making GenreId of 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 the Genre GenreId table, click the left margin of the Id column and then drag it onto 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 well Genre table if it still has reviews attached to it. Click OK to dismiss this dialog box as



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 and choose Show Table Data. Enter some data like below.

table

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 below: Genre table, VWD now shows you the dialog box 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