To create a web application with database connection.
s9540319j
Project_MasterLevel_Lab9MasterLevel.pdf
Internet Fundamentals & Applications
Institute Of Technical Education - 35 -
Name : ______________ Class: ____________ Lab 9 - Database with Visual Studio Exercise 9.1 -- Login and Membership using Visual Studio 9.1.1 Opening the sample website in Visual Studio 1. Get the vslogin2 folder from the instructor and copy it to the My Documents folder.
2. In Visual Studio, from the File menu, choose Open > Web Site...
3. Browse for the vslogin2 folder in the dialog box and click Open.
The vslogin2 website is opened in the Solution Explorer. 9.1.2 Configuring membership for vslogin2 1. From the Website menu, choose ASP.NET Configuration.
A browser window opens showing the ASP.NET Web Site Administration Tool.
2. On the administration tool main page, select Security from the link on the web page.
The security page appears.
3. On the security page, select Use the security setup wizard to configure security step by step.
The welcome page of the security setup wizard appears.
4. On Step 1: Welcome page, Click Next.
5. On Step 2: Select Access Method page, select From the Internet and click Next.
6. On Step 3: Data Store page, click Next.
7. On Step 4: Define Roles page, skip any configuration and click Next.
8. On Step 5: Create User page, enter the following information as shown in Table 9.1 and Table 9.2:
User Name Andychua Password / Confirm Password p@ssword E-mail [email protected] Security Question 1+1= Security Answer 2
Table 9.1
User Name Bettytan Password / Confirm Password p@ssword E-mail [email protected] Security Question 2+2= Security Answer 4
Table 9.2
Click Create User, and click Next.
Internet Fundamentals & Applications
Institute Of Technical Education - 36 -
9. On Step 6: Add New Access Rules page, skip any configuration and click Next. 10. Click Finish to complete the configuration wizard setup.
A new database, ASPNETDB.MDF, is added to the App_Data folder of your web site. This file is automatically generated and should not be modified manually.
9.1.3 Editing Default.aspx 1. Open Default.aspx by double-clicking its icon in the Solution Explorer.
2. In the Design view as shown in Figure 9.1, drag a LoginStatus control beside the Already a member?
Figure 9.1 9.1.4 Editing login.aspx 1. Open login.aspx by double-clicking its icon in the Solution Explorer.
2. In the Design view, drag a Login control to the middle of the page as shown in Figure 9.2.
Figure 9.2
3. With the Login control selected, set the following properties as shown in Table 9.3:
Settings for Login Control Control Property Setting Login ID lgnMember
DestinationPageUrl ~/members.aspx (Click browse)
Table 9.3
A LoginStatus provides a login/logout hyperlink for users based on the status.
Internet Fundamentals & Applications
Institute Of Technical Education - 37 -
9.1.5 Editing register.aspx 1. Open register.aspx by double-clicking its icon in the Solution Explorer.
2. In the Design view, drag a CreateUserWizard control to the middle of the page as shown in Figure 9.3.
Figure 9.3 3. With the CreateUserWizard control selected, set the following properties as shown in Table 9.4:
Settings for Login Control Control Property Setting Login ID cuwRegister
ContinueDestinationPageUrl ~/members.aspx (Click browse)
Table 9.4
A CreateUserWizard control facilities registration for members.
Internet Fundamentals & Applications
Institute Of Technical Education - 38 -
9.1.6 Editing members.aspx 1. Open members.aspx by double-clicking its icon in the Solution Explorer.
2. In the Design view, drag a LoginStatus and LoginName control to the page as shown in Figure 9.4.
Figure 9.4 Testing the membership
1. Press F5 to run the application.
2. On default page, click Sign Up to go to register.aspx.
3. On register.aspx, enter all required information and click Create User. See the output produced.
4. Once logged in, on members.aspx, see that the UserName is shown in the LoginName control. Click Logout.
5. You are redirected back to login.aspx. From here, you can try to login again.
Internet Fundamentals & Applications
Institute Of Technical Education - 39 -
Exercise 9.2 -- Roles Access using Visual Studio 9.2.1 Opening the sample website in Visual Studio 1. Copy the vslogin2 folder to the My Documents folder.
2. In Visual Studio, from the File menu, choose Open > Web Site...
3. Browse for the vslogin2 folder in the dialog box and click Open.
The vslogin website is opened in the Solution Explorer.
2.2 Restricting access to members.aspx 1. Open members.aspx by double-clicking its icon in the Solution Explorer.
2. Double-click on the Design window to create the Form_Load event procedure.
3. Enter the following code within the Form_Load procedure:
Protected Sub Page_Load(ByVal sender… If Not User.Identity.IsAuthenticated Then
Response.Redirect("login.aspx") End If End Sub
4. Open default.aspx by double-clicking its icon in the Solution Explorer.
5. In the Design view, drag a LoginView to the middle of the page.
6. Click the LoginView’s shortcut arrow and select LoggedInTemplate for Views as shown in Figure 9.5.
Figure 9.5
7. In the LoginView area, create a hyperlink to the members page (members.aspx).
A LoginView provides a different view for anonymous users and members.
9.2.3 Password Change for members
1. From the Website menu, choose Add New Item.
2. In the dialog box, Select Web Form and type changepassword.aspx as the name and click Add.
A new web form is added to your website.
3. From the Format menu, choose Attach Style Sheet.
Internet Fundamentals & Applications
Institute Of Technical Education - 40 -
4. Select StyleSheet.css as the style sheet for the page.
5. In the Design view, create a header (Change Password) and drag a ChangePassword control to the middle of the page as shown in Figure 9.6.
The ChangePassword control allows members to change their membership password.
6. Create a hyperlink to members.aspx at the bottom of the page.
Figure 9.6 9.2.4 Configuring Roles for vslogin 1. From the Website menu, choose ASP.NET Configuration.
A browser window opens showing the ASP.NET Web Site Administration Tool.
2. On the administration tool main page, select Security from the link on the web page.
The security page appears.
3. On the security page, select Enable Roles under the Roles section.
Roles are now enabled for your website.
4. Click on Create or Manage Roles.
5. On the Create New Role page, Enter Gold and click Add Role.
6. Repeat Step 5 for Silver and click Add Role.
Two roles, Gold and Silver, are created in your website. These roles will be assigned to your members.
7. Under the Gold role name, click Manage.
Internet Fundamentals & Applications
Institute Of Technical Education - 41 -
8. On the Manage Users page, click All to display all users under the Search for Users section.
9. Tick the checkbox for user bettytan and click Back.
Repeat steps 7 and 8 for Silver.
10. Tick the checkbox for user andychua and click Back. You are returned to the Create New Role page.
11. On the Create New Role page, click Back.
You are returned to the Security page.
12. On the Security page, click on Manage access rules under the Access Rules section.
The Manage Access Rules page appears, allowing you to allow/deny access to different roles.
13. On the Manage Access Rules page, click on the Gold folder to select it.
14. With the Gold folder selected, click on Add new access rule.
15. Add a rule configuration for Role:Gold and Permission:Allow and click OK.
16. Add a rule configuration for Role:All Users and Permission:Deny and click OK.
A new access rule is set to allow Gold members to access the Gold folder, deny all other users.
Internet Fundamentals & Applications
Institute Of Technical Education - 42 -
17. On the Manage Access Rules page, click on the Silver folder to select it.
18. With the Silver folder selected, click on Add new access rule.
19. Add a rule configuration for Role:Gold and Permission:Allow and click OK.
20. Add a rule configuration for Role:Silver and Permission:Allow and click OK.
21. Add a rule configuration for Role:All Users and Permission:Deny and click OK.
A new access rule is set to allow Gold and Silver members to access the Silver folder, deny all other users
22. Click Done and close the website administration tool. 9.2.5 Testing the website 1. Press F5 to run the application.
2. Try to login using bettytan and see if you can access the Gold and Silver folders. After that, log out and try to login using andychua See if you access the Silver folder, but you will be denied access to the Gold folder.
Internet Fundamentals & Applications
Institute Of Technical Education - 43 -
Exercise 9.3 -- Catalogue & Detail pages using Visual Studio 9.3.1 Opening the sample website in Visual Studio 1. Copy the vslogin folder (completed from Exercise 02) to the My Documents folder.
2. In Visual Studio, from the File menu, choose Open > Web Site...
3. Browse for the vslogin folder in the dialog box and click Open.
The vslogin website is opened in the Solution Explorer.
9.3.2 Attaching a new database
SKIP !
9.3.3 Adding the pictures file 1. In the Solution Explorer, right-click the project icon and select Add Existing Item.
2. As shown in Figure 9.7, in the dialog box, browse for the picture files (provided by your lecturer) and shift-click to select all the pictures and click Add.
The picture files are added to your website.
Figure 9.7 9.3.4 Adding a catalogue page 1. In the Solution Explorer, right-click the project icon and select Add New Item.
2. In the dialog box, Select Web Form and type catalogue.aspx as the name and click Add.
A new web form is added to your website.
3. From the Format menu, choose Attach Style Sheet.
4. Select StyleSheet.css as the style sheet for the page.
5. In the Design view, create a header (Games Catalogue) and drag a GridView control to the middle of the page.
The GridView control shows a table of records, e.g. from the database.
Internet Fundamentals & Applications
Institute Of Technical Education - 44 -
6. With the GridView selected, set the following properties as shown in Table 9.5
The PageSize determines how many records are shown per page.
Settings for GridView Control Property Setting GridView (ID) gvGames
PageSize 3
Table 9.5 6.5 Drag in gamesx.mdf to App_Data folder.
Gamesx.mdf can be found in “Before” folder
7. Click the GridView’s shortcut arrow and select New Data Source… for Choose Data Source as shown in Figure 9.8.
A Data Source connects your GridView content to the database.
Figure 9.8
8. In the Data Source Configuration Wizard, click on the SQL Database icon (2nd from left) and type dtsGames for the ID
and click Next.
Internet Fundamentals & Applications
Institute Of Technical Education - 45 -
9. In the Data Connection step, choose gamesxConnectionString by clicking on the drop down menu.
10. Click Next.
11. In the Configure Select Statement step as shown in Figure 9.9, choose Specify columns for table or view and ensure that the games table is selected in the name combo box. For columns, check the * (asterisk) and click Next. The Data Source generates the necessary SQL statements for you based on your selections.
Figure 9.9
12. In the Test Query step, click Test Query to check that the data can be retrieved successfully. If so, click Finish to complete the wizard. The dtsGames data source is added to your page. Note that this item is hidden and will not show up when previewed in the browser.
9.3.5 Customizing the catalogue page 1. Click the GridView’s shortcut arrow and check the Enable Paging checkbox.
Enable Paging allows paging of records, specified by the page size
2. Under the same Task Pane, select Edit Columns.
3. In the Fields dialog box, remove id, description and picture fields under the Selected fields section.
Internet Fundamentals & Applications
Institute Of Technical Education - 46 -
4. Under the Selected fields section, set the following properties for price as shown in Table 9.6:
Type in {0:c}
Settings for price
Field Property Setting price DataFormatString {0:c}
Table 9.6
The {0:c} is an expression for formatting currency values with the $ sign.
5. Add in ImageField from the Available fields section . Under the Available fields section, select ImageField and click Add.
6. With the Image Field selected, set the following properties as shown in Table 9.7.
Settings for ImageField Field Property Setting ImageField HeaderText picture
DataImageUrlField picture
Table 9.7 7. Click OK to close the Fields dialog box.
8. Press F5 to run the application.
Internet Fundamentals & Applications
Institute Of Technical Education - 47 -
9. Observe that the records are shown in the GridView. The price column is formatted with the $ sign and the pictures are shown in each record.
9.3.6 Creating the detail page
1. In the Solution Explorer, right-click the project icon and select Add New Item.
2. In the dialog box, Select Web Form and type detail.aspx as the name and click Add.
A new web form is added to your website.
3. From the Format menu, choose Attach Style Sheet.
4. Select StyleSheet.css as the style sheet for the page.
5. In the Design view, create a header (Game Details) and drag a DetailsView control to the middle of the page.
6. The DetailsView control shows a single record, e.g. from the database.
7. With the DetailsView selected, set the following properties as shown in Table 9.8:
Settings for DetailsView
Control Property Setting
DetailsView (ID) dvDetails
Table 9.8
8. Click the DetailsView’s shortcut arrow and select New Data Source… for Choose Data Source.
9. In the Data Source Configuration Wizard, click on the SQL Database icon (2nd from left) and type dtsDetails for the ID and click Next.
Internet Fundamentals & Applications
Institute Of Technical Education - 48 -
10. In the Data Connection step, choose gamesxConnectionString by clicking on the drop down menu.
11. In the Configure Select Statement step, choose Specify columns for table or view and ensure that the games table is
selected in the name combo box. For columns, check the * (asterisk).
12. Click WHERE to add selection criteria to your Select Statement as shown in Figure 9.10.
Internet Fundamentals & Applications
Institute Of Technical Education - 49 -
Figure 9.10
13. In the Add WHERE Clause dialog box, select id for Column and (=) for the operator. For the Source, select QueryString.
14. Under the Parameter Properties section, enter id in the QueryString field textbox and 0 in the Default value textbox and click Add.
The selected properties are added to your WHERE clause.
Internet Fundamentals & Applications
Institute Of Technical Education - 50 -
15. Click OK to close the dialog box.
16. Click Next and then Finish to close the Data Source Configuration Wizard.
9.3.7 Customizing the DetailsView 1. Click the DetailsView shortcut arrow and select Edit Fields.
2. In the Fields dialog box, remove the picture field under the Selected fields section.
3. Under the Selected fields section, set the following properties for price as shown in Table 9.6:
Type in {0:c}
Settings for price
Field Property Setting price DataFormatString {0:c}
Table 9.6
The {0:c} is an expression for formatting currency values with the $ sign.
Internet Fundamentals & Applications
Institute Of Technical Education - 51 -
4. Add in ImageField from the Available fields section . Under the Available fields section, select ImageField and click Add.
5. With the Image Field selected, set the following properties as shown in Table 9.7.
Settings for ImageField Field Property Setting ImageField HeaderText picture
DataImageUrlField picture
Table 9.7 6. Click OK to close the Fields dialog box.
7. Save detail.aspx (Ctrl-S).
9.3.8 Updating catalogue.aspx with the link 1. Open catalogue.aspx and click the GridView’s shortcut arrow and select Edit Columns.
2. Under the Available fields section, select HyperLinkField and click Add.
3. With the HyperLinkField selected, set the following properties as shown in Table 9.9:
Settings for HyperLinkField Field Property Setting HyperLinkField HeaderText details
Text more details DataNavigateUrlFields id DataNavigateUrlFormatString detail.aspx?id={0}
Table 9.9
4. Click OK to close the Fields dialog box.
5. Press F5 to run the application.
Internet Fundamentals & Applications
Institute Of Technical Education - 52 -
6. Click the more details link for one of the GridView records. See that the link opens up detail.aspx with the details of the
selected record.
7. Add a hyperlink at the bottom of details.aspx to allow users to return to the catalogue page.
Internet Fundamentals & Applications
Institute Of Technical Education - 53 -
Exercise 9.4 – Adding, Editing and Deleting Items using Visual Studio 9.4.1 Opening the sample website in Visual Studio 1. Copy the vslogin2 folder (completed from Example 4) to the My Documents folder.
2. In Visual Studio, from the File menu, choose Open > Web Site...
3. Browse for the vslogin2 folder in the dialog box and click Open.
The vslogin2 website is opened in the Solution Explorer.
We are going to allow Gold members to add / edit / delete games in the website. 9.4.2 Modifying the gold members page 1. In the Solution Explorer, double-click Gold/default.aspx to open the web form.
2. In the Design view, drag a GridView control to the middle of the page.
3. With the GridView selected, set the following properties as shown in Table 9.10:
Settings for GridView Control Property Setting GridView (ID) gvGames
Table 9.10 4. Click the GridView’s shortcut arrow and select New Data Source… for Choose Data Source as shown in Figure 9.11.
A Data Source connects your GridView content to the database.
Figure 9.11
Internet Fundamentals & Applications
Institute Of Technical Education - 54 -
5. In the Data Source Configuration Wizard, click on the SQL Database icon (2nd from left) and type dtsGames for the ID and click Next.
6. In the Data Connection step, choose gamesxConnectionString by clicking on the drop down menu.
7. In the Configure Select Statement step, choose Specify columns for table or view and ensure that the games table is selected in the name combo box. For columns, check the * (asterisk).
8. Click Advanced.
Internet Fundamentals & Applications
Institute Of Technical Education - 55 -
9. In the Advanced SQL Generation Options dialog box as shown in Figure 9.12, check the Generate INSERT, UPDATE and DELETE statements checkbox and click OK. The Data Source generates the necessary insert, update and delete SQL statements for you to allow database manipulation.
Figure 9.12
10. Click Next and click Finish to close the Data Source Configuration Wizard.
9.4.3 Customizing the GridView 1. Click the GridView’s shortcut arrow and select Edit Columns.
2. In the Fields dialog box, select the price field under the Selected fields section.
3. Set the following properties for price as shown in Table 9.11:
Settings for price Field Property Setting price DataFormatString {0:c}
Table 9.11
4. Click OK to close the Fields dialog box.
5. Click the GridView’s shortcut arrow and check the Enable Editing and Enable Deleting checkboxes. This enables a feature called inline-editing in your GridView.
6. Press F5 to run the application.
7. Observe that you have to login as your gold member account (e.g. bettytan/p@ssword) to access the page. Once at the gold member page, try to edit one of the records. The GridView allows you to edit the items in the table directly. Also try to delete one of the records. However, this may not be appropriate for the description view which is rather long. We can create a web form for updating one record at a time in a single page.
Internet Fundamentals & Applications
Institute Of Technical Education - 56 -
9.4.4 Creating the edit product page
1. In the Solution Explorer, right-click the project icon and select Add New Item.
2. In the dialog box, Select Web Form and type edit.aspx as the name and click Add.
A new web form is added to your website.
3. From the Format menu, choose Attach Style Sheet.
4. Select StyleSheet.css as the style sheet for the page.
5. In the Design view, create a header (Edit Game) and drag a DetailsView control to the middle of the page.
6. With the DetailsView selected, set the following properties as shown in Table 9.12:
Settings for DetailsView Control Property Setting DetailsView (ID) dvDetails
Table 9.12
7. Click the DetailsView’s shortcut arrow and select New Data Source… for Choose Data Source.
8. In the Data Source Configuration Wizard, click on the SQL Database icon (2nd from left) and type dtsDetails for the ID and click Next.
9. In the Data Connection step, choose gamesxConnectionString by clicking on the drop down menu.
Internet Fundamentals & Applications
Institute Of Technical Education - 57 -
10. In the Configure Select Statement step, choose Specify columns for table or view and ensure that the games table is selected in the name combo box. For columns, check the * (asterisk).
11. Click Advanced.
In the Advanced SQL Generation Options dialog box, check the Generate INSERT, UPDATE and DELETE statements checkbox and click OK.
12. Click WHERE to add selection criteria to your Select Statement.
13. In the Add WHERE Clause dialog box, select id for Column and (=) for the operator. For the Source, select QueryString.
14. Under the Parameter Properties section, enter id in the QueryString field textbox and 0 in the Default value textbox and click Add.
The selected properties are added to your WHERE clause.
15. Click OK to close the dialog box.
16. Click Next and then Finish to close the Data Source Configuration Wizard.
Internet Fundamentals & Applications
Institute Of Technical Education - 58 -
9.4.5 Customizing the DetailsView 1. Click the DetailsView shortcut arrow and select Edit Fields.
2. Repeat steps 2 to 4 of Section 9.4.3 to configure the price field for the DetailsView.
3. Click the DetailsView’s shortcut arrow and check the Enable Editing and Enable Deleting checkboxes.
4. Ensure that the DetailsView is selected and click the Events icon in the Properties pane as shown in Figure 9.13.
Figure 9.13
5. Refer to Figure 9.14, look for the ItemDeleted event in the list and double-click on the empty area to generate an event handler for ItemDeleted.
Figure 9.14
6. In the Code View of the generated event handler, enter the following code:
Protected Sub dvDetails_ItemDeleted(ByVal sender... Response.Redirect("default.aspx")
End Sub
This code redirects the user back to the default listing page after the delete has completed.
7. Repeat steps 4 to 6 for the ItemUpdated event.
8. Save edit.aspx (Ctrl-S).
Double-click here
Internet Fundamentals & Applications
Institute Of Technical Education - 59 -
9.4.6 Updating Gold/default.aspx with the link 1. Open Gold/default.aspx and click the GridView’s shortcut arrow and select Edit Columns.
2. Under the Available fields section, select HyperLinkField and click Add.
3. With the HyperLinkField selected, set the following properties as shown in Table 9.13:
Settings for HyperLinkField Field Property Setting HyperLinkField HeaderText details
Text more details DataNavigateUrlFields id DataNavigateUrlFormatString edit.aspx?id={0}
Table 9.13
4. Click OK to close the Fields dialog box.
5. Press F5 to run the application.
6. Click the more details link for one of the GridView records. See that the link opens up detail.aspx with the details of the selected record. You can then click Edit or Delete to manipulate the record one at a time.
9.4.7 Creating an insert game page 7. In the Solution Explorer, right-click the project icon and select Add New Item.
8. In the dialog box, Select Web Form and type insert.aspx as the name and click Add.
A new web form is added to your website.
9. From the Format menu, choose Attach Style Sheet.
10. Select StyleSheet.css as the style sheet for the page.
11. In the Design view, create a header (Edit Game) and drag a DetailsView control to the middle of the page.
12. With the DetailsView selected, set the following properties as shown in Table 9.12:
Settings for DetailsView Control Property Setting DetailsView (ID) dvDetails
Table 9.12
13. Click the DetailsView’s shortcut arrow and select New Data Source… for Choose Data Source.
14. In the Data Source Configuration Wizard, click on the SQL Database icon (2nd from left) and type dtsDetails for the ID and click Next.
Internet Fundamentals & Applications
Institute Of Technical Education - 60 -
15. In the Data Connection step, choose gamesxConnectionString by clicking on the drop down menu.
16. Click the DetailsView’s shortcut arrow and check the Enable Inserting checkbox.
Note that the default view of the DetailsView is a read-only view of the record. However, as there is no record to show during inserting, we need to modify the DetailsView property to show an insert form by default.
17. Ensure that the DetailsView is selected and set the following property as shown in Table 9.14:
Settings for DetailsView Control Property Setting DetailsView DefaultMode Insert
Table 9.14
18. Repeat steps 4 to 6 of Section 9.4.5 to generate an event handler for the ItemInserted event.
19. Save insert.aspx (Ctrl-S).
Internet Fundamentals & Applications
Institute Of Technical Education - 61 -
9.4.8 Adding a link from Gold/default.asp to insert page
20. Open Gold/default.aspx and add a hyperlink to insert.aspx below the GridView as shown in Figure 9.15.
Figure 9.15
21. Press F5 to run the application.
22. Click the insert hyperlink at Gold/default.aspx to insert a new record with the following information as shown in Table 9.15:
Title FIFA10 Description The latest installment of the
FIFA soccer series Price 79.90 Picture Empty.jpg
Table 9.15
Observe that the game is inserted and reflected in Gold/default.aspx. However, you may notice that the image is not real as we did not upload any picture. Also, you cannot insert wrong or empty data during the insert process, otherwise it will cause an error. This will be fixed at a later example.
