Unit 8.1: Introducing Databases

1. Unit 8.1: Introducing Databases Learning Objectives How data can be used – The Money Ball Example The Role of Data Warehouses Group Exercise Creating Database Driven Websites The Big Picture : Client Server Technology Framework Data control objects The SQL DataSource Object SQL Data Display options Hands on exercise

3. How Data can be used Example: Moneyball • Batting average • Stolen bases • Runs batted in • On-base % • Slugging % • On-base + Slugging (OPS)

5. Databases • Database creation and use MS Visual Web Developer • Database and Query Development • MS SQL Server • Business Intelligence Microsoft BI, IBM Cognos • Real world Application Money Ball

6. Data warehouses Please answer the following questions based on Intricity’sVideo on Benefits of a Data Warehouse • What is a Data Warehouse? • What is the impact of the fact that databases can take in one row of data at a time? • When the video says “You are doing it anyway, what is it talking about?” • List any two benefits of data warehousing.

7. The Client Server Process Request SQL request Database server e.g. MS SQL Server Browsere.g. FireFox Web server e.g. Windows Server SQL ASPX HTML Data & status or exceptions Response

8. The Client Server Process of a data enabled web page SQL DataSource 1 List Fee table 2 Request Database server e.g. MS SQL Server Browsere.g. FireFox Web server e.g. Windows Server SQL ASPX HTML ["1",“Registration Fee",5.00,”2”, “Vehicle License Fee",7.5,”3”, “Weight Fee“, 12.500] 3 4 Display format

9. The SQL Data Source Object Image Source: TopGear.com

10. The SQL Data Source Object sdsFees cs3200 • Used with MS SQL Server • Embodies • SQL command • Connection String • Database Name • Database Location sql3200

11. Characterize this: Format/Number of Records

12. Data Display Controls

13. L1 - 1 • For this exercise we are going to convert the fee table used in Unit 5 to a database table • Copy your ASPPub folder from ASPNET to your desktop and open it in Visual Studio • First we need to create a database and the fee table • Right click on your App_data folder & select Add New Item • Scroll down and select SQL Server Database • Name the Database MIS3200.mdf and click Add

14. L1 - 2 You should now see the database in your App_Data folder • Double-click the MIS3200.mdf database file to open the Database Explorer (it could be on either side of Visual Web Developer) • Right-click on the Tables folder and select Add New Table to open the database table editor

15. L1-3: Auto-increment • Name the first column FID, for fee ID • Set the data type Int • Uncheck Allow Nulls • Move down to the Column Properties window • Scroll down to Identify Specification • Click the arrow to expand the choices • Change (Is Identity) to Yes (see next slide)

16. L1-4: PK When (IsIdentity) is set to true SQL Server will automatically generate a unique integer for each record added to the table. If you don’t have something better to use, this makes a great primary key • Select the row and click the Primary Key icon

17. L1 – 5: Adding Columns • Add a third column • Column Name = Fee • Data Type = money • Allow Nulls = not checked Add columns • Add a second column • Column Name = FeeDescription • Data Type = nvarchar(50) • Allow Nulls = not checked

18. L1 - 6 • Close the table definition • Save the changes • Name the table Fees and click OK • If you don’t already have one, create a Unit8 folder under your MIS3200 folder • Right click on the MIS3200 folder • Click New Folder • Name it Unit8

19. L1 - 7 • Create a new item in the Unit8 folder • Add a Visual C# web form with your standard master page and name it YourlastnameU8L1.aspx • We are going to use this page to display, edit, modify and potentially delete fees that we will use on another page • Make sure that your U8L1.aspx page is open • Switch to Design view • Click in the MainContent area a press Return twice • Type the following, without the quotes: “To change a fee or its cost, press Select” and then press return twice more

20. L1 - 8 • Copy a GridView from the Data section of the Toolbox to below the text you just entered • Use the ID field to name it: gvFees • Click the dropdown next to Choose Data Source and select <New data source>

21. L1 - 9 • Click SQL Database and change the ID to sdsFees and click OK

22. L1 -10 • We don’t have any connections yet • So Click New Connection, • Select the Microsoft SQL Server Database file as the Data Source (driver), • Browse to MIS3200.mdf • Click OK A C C:\Users\Joe Bobcat\Desktop\ASPPUB\App_Dat B D

23. L1-11 If you see a message like this • Ensure that the database is not open elsewhere, and • Cancel your attempt to add the connection • In the App_Datafolder, delete the .ldf file associated with the database and create the New Connection again.

24. L1 - 12 • You should now see your database listed in the dropdown box… • Click Next • Click Yes, save this connection as: • Change the name to cs3200 • Click Next • The next screen lets you select the table and columns to use. • Select the Fees table • Select all three columns individually, don’t select * This will create a Connection String called cs3200 that we will be able to use to connect to the database another time.

25. L1 - 13 • Click the Advanced button and you should see • Click the Generate INSERT, … checkbox and then click OK • If this is gray it means either • FID wasn’t marked as the primary key, or • The primary key wasn’t selected Checking this box tells the system that you want it to generate the SQL statements necessary to insert, update and delete data. If you don’t click this you will only get the Select statement.

26. L1 - 14 • Click Next on the Configure Data Source window • The next window gives you a chance to run a test query but we don’t have any data yet so just click Finish • Your GridView may not show the column headings you selected in the data source. If it doesn’t, click Refresh Schema • You won’t see the Editingand Deleting choices unless selected the Advanced Options If you don’t see this, click the Smart Menu, where the < is now

27. L1 - 15 • Click Enable Selection on the GridView smart menu (see previous slide) • Format the GridView • Click Auto Format on the SmartMenu • Select between on different schema • After you have selected one, click OK

28. L1 - 16 • Now run the page • What happens? • Why?

29. L1 - 17 • You should have seen something like this • No GridView • No records to display • GridViews don’t support data insertion so we need another control • Close the page and add a DetailsView under your GridView • Change the (ID) to dvFee

30. L1 - 18 • Select “Choose Data Source” from the DetailsView’s smart menu and pick “<New data source>” • Select SQL Database and give it the ID “sdsFee” (this is a singular name because a DetailsView shows one record at a time). • Click OK

31. L1 - 19 • Click the dropdown list on the Choose Your Data Connection screen and select “cs3200” (the connection you saved earlier) • Click Next • Select the Fee table and all the fields like you did earlier • Click Advanced and select Generate Insert, … as you also did before • Click OK

32. L1 - 20 • We are going to use the DetalsView to insert new fees but we are also going to use it to modify or delete fees selected in the GridView so we need a way to link the two controls Click WHERE

33. L1 - 21 • Click Column and select the primary key, FID • Click Operator and select = • Click Source and select Control • Then select gvFees from the Control ID list • Finally, click Add • The end result of all of this is that your SQL will contain a WHERE clause with [FID]=@FID and a parameter that assigns the selected value of the GridView to @FID

34. L1 - 22 • If you haven’t already done so, click OK to finish the WHERE clause and then Next> • We still don’t have any data so click Finish from the Text Query window • Click on the DetailsView • Select Enable Inserting, Enable Editing and Enable Deleting • Pick an Auto Format for the DetailsView (using the same one you used for the GridView often looks best)

35. L1 - 23 • Now try to run the web again • What happens? • Why?

36. L1 - 24 • Same problem as before… • There is no data for the GridView to display • The DetailsView is supposed to show the fee selected in the GridView, but no fee is selected • The DetailsView has a New button but you can only see it when a record is displayed – it doesn’t help us enter the first record • Right-click the DetailsView • Select Edit Template • Click the drop down list • Select EmptyDataTemplate

37. L1 - 25 • The EmptyDataTemplate Displayed when NO records are selected (when there are no records and when no record is selected in the GridView) • Copy a LinkButton from the Toolbox into the EmptyDataTemplete • Change the (ID) to lbNewFee • Change the Text to “Add a fee” • Change the CommandName to “new” (no quotes) • Click the DetailsView’s SmartMenu and then End Template Editing

38. L1 - 26 • Try to run the page again • That happens this time? • What happens when you click Add a fee? • Enter • 5.00 for Fee • Registration Fee for FeeDescription • Click Insert • What happens? • Close the web page and then run it again • What happens now? • What happens if you click Select?

39. L1 – 27 • Why wasn’t the new fee displayed in the GridView? • The GridView and the DetailsView have separate DataSources • Each DataSource gets its own copy of the data but, • They are independent of each other. Neither one automatically knows if the other one changes that data • Both DataSources get a fresh copy of the data when the page initially loads so they will see and display changes made earlier • To “force” the DataSource to check for changes we tell it to use its .DataBind() method (see next page)

40. L1 - 28 • The Control that changes the data needs to let the other controls know… • So, select the DetailsView • Click the Lightening Bolt icon in the properties window • This displays a list of common events associated with a DetailsView

41. L1 - 29 • Notice that many events appear in pairs • ItemUpdating – happens just before the record is updated • ItemUpdated – happens just after the record is updated • ItemInserting – just before the insert • ItemInserted – just after the insert • Etc. • We want to tell the GridView to check the data after the DetailsView inserts a new record, so

42. L1 - 30 • Double-click your mouse in the box to the right of ItemInserted • This should create a method to process the event and switch you into code view • Like the click events you saw before, the method name is formed from the name of the control, dvFee and the name of the event, ItemInserted • In this case all we need to do is tell the GridView to do a DataBind

43. L1 - 31 • Run the page again • Add a new fee • Vehicle License Fee • 7.50 • What happens when you insert it? • Add the following fees • Select any fee in the GridView and modify it in the DetailsView- What Happens? • Select any fee in the GridView and delete it in the DetailsView – What happens?

44. L1 - 32 • When you modify (update) data in the DetailsView the change isn’t shown in the GridView • Same problem we had inserting data – the GridView doesn’t know that the data has changed • Select the DetailsView’s events • Double-click on ItemUpdated to generate the method • Add gvFees.DataBind() to the method • Deleting has the same problem • Add gvFees.DataBind() to the DetailsView’sItemDeleted event method.

45. L1 - 33 • Is it safe to delete data the first time a user says to delete it? • It is generally a good idea to ask for confirmation • To do that we need to modify the delete button on the DetailsView • Click the DetailsView’sSmartMenu • Click Edit Fields • The Delete button is in the CommandField(Selected fields window) and to get to you need to click on CommandField and then • Click “Convert this field into a TemplateField” (see next slide) • Finally, click OK

46. L1 - 34 A B C D E

47. L1 - 35 • Next, from the DetailsView’sSmartMenu – click Edit Templates • In the Template Editing box, click the dropdown arrow to see what templates are available • Field numbering starts are zero and the current commands are in field #3 (0, 1, 2, 3). Select the ItemTemplate in Field[3] if it isn’t already selected • You should see three LinkButtons in the ItemTemplate

48. L1 - 36 • Click the Delete LinkButton and look at its properties • Change the (ID) to lbDelete • Scroll down to a property called OnClientClick • This property allows us to write JavaScript code that will run when the user clicks the button and before the page is posted back to the server • The code we want is: return confirm(“Are you sure?”) • Confirm causes a popup window with message and two buttons – OK and Cancel • If the user clicks Cancel the post-back is cancelled, so the delete is cancelled • If the user clicks OK the post-back is allowed to happen and the record will be deleted • End Template editing

49. L1 - 37 • One more set of problems – what happens if you attempt to insert or update a fee but haven’t entered a description, or a fee? What happens if you try to enter a non-numeric value for the fee? • How did you solve these problems earlier?

50. L1 - 38 • As before, we can solve these problems with Validators, but, to do that we have to get to the TextBoxes where the data is entered and they are buried in the DetailsViewand must be converted to templates. • So, open the DetailsView’sSmartMenu and click EditFields (see slide L1-35 if you need help) • Click on the FeeDescription field and then Convert this field into a TemplateField • Click on the Fee field and then Convert this field into a TemplateField • Click OK to close the Fields window