CSC209 Web Programming

1. CSC209 Web Programming Chapter 11 – Introduction to Databases Dr. Stephanos Mavromoustakos

2. 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?

3. 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.

4. 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.

5. 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

6. 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

7. Retrieving and Manipulating Data

8. Retrieving and Manipulating Data Ordering Data The ORDERBY 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

9. 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

10. Practice – Selecting Data from the Sample Database • Right-click the Genre table and choose Show Table Data.

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

12. 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.

13. 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.

14. 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.

15. 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:

16. Practice – Selecting Data from the Sample Database

17. 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 ORDERBY 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

18. Practice – Selecting Data from the Sample Database • Note that the records are sorted in descending order

19. 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

20. 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

21. 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.

22. 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

23. 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

24. Practice – Joining Data • The results of the query are shown in the Results pane

25. 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

26. 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

27. 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)

28. 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

29. 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

30. 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

31. Creating your own Tables The SQL Server 2005 has its own data types:

32. Creating your own Tables 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.

33. 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.

34. 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:

35. 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.

36. 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.

37. 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:

38. 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

39. 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.

40. 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

41. 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

42. 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

43. 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.

44. 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.

45. 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.

46. 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