unit 8 1 introducing databases n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Unit 8.1: Introducing Databases PowerPoint Presentation
Download Presentation
Unit 8.1: Introducing Databases

Loading in 2 Seconds...

play fullscreen
1 / 58

Unit 8.1: Introducing Databases - PowerPoint PPT Presentation


  • 55 Views
  • Uploaded on

Unit 8.1: Introducing Databases. L earning 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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Unit 8.1: Introducing Databases' - elita


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
unit 8 1 introducing databases
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

how data can be used
How Data can be used

Example: Moneyball

  • Batting average
  • Stolen bases
  • Runs batted in
  • On-base %
  • Slugging %
  • On-base + Slugging (OPS)
databases
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

data warehouses
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.
the client server process
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

the client server process of a data enabled web page
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

the sql data source object
The SQL Data Source Object

Image Source: TopGear.com

the sql data source object1
The SQL Data Source Object

sdsFees

cs3200

  • Used with MS SQL Server
  • Embodies
    • SQL command
    • Connection String
      • Database Name
      • Database Location

sql3200

slide13
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
slide14
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
l1 3 auto increment
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)
l1 4 pk
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
l1 5 adding columns
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
slide18
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
slide19
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

slide20
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>
slide21
L1 - 9
  • Click SQL Database and change the ID to sdsFees and click OK
l1 10
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

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

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

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

l1 15
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
l1 16
L1 - 16
  • Now run the page
    • What happens?
    • Why?
l1 17
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
l1 18
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
l1 19
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
l1 20
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

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

l1 22
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)
l1 23
L1 - 23
  • Now try to run the web again
    • What happens?
    • Why?
l1 24
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
l1 25
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

l1 26
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?
l1 27
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)
l1 28
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

l1 29
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
l1 30
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
l1 31
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?
l1 32
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.
l1 33
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
l1 34
L1 - 34

A

B

C

D

E

l1 35
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
l1 36
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
l1 37
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?
l1 38
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
l1 39
L1 - 39
  • Click Edit Templates from the DetailsView’sSmartMenu
  • Click the dropdown to Display the available templates.
    • You can then click on an individual template or
    • You can click on the Field name to display all templates for the field
  • Click on Field[1] - FeeDescription
l1 40
L1 - 40
  • Add a RequiredFieldValidator next to the TextBox in the EditItemTemplate
    • Set the (ID) to rfvDescription
    • Set the ControlToValidate to TextBox1
    • Set the ErrorMessage to “Fee Description is Required”
    • Set ForeColor to Red
    • Set Text to *
    • Set Validation Group to Edit
  • Repeat the previous step in the InsertItemTemplate but
    • Set Validation Group to Insert
l1 41
L1 - 41
  • Change to templates for the Fee field (if the SmartMenu isn’t displayed, click the DetailsView’s title bar, click the SmartMenu and change the Display to Field[2])
  • Add RequiredFieldValidators to both the Edit and Insert templates. Change properties as before except…
    • Change the ErrorMessage to say the the Fee is Required
    • Change the ControlToValidate to TextBox2
    • Change the Display to Dynamic
l1 42
L1 - 42
  • Add a CompareValidator to the Edit and Insert Templates
    • Set the (ID) to cvFee
    • Set the ControlToValidate to TextBox2
    • Set the Display to Dynamic
    • Set the ErrorMessage to “Fee must be a positive number”
    • Set the ForeColor to Red
    • Set the Operator to Greater Than
    • Set the Text to *
    • Set the Type to Double
    • Set the ValidationGroup to Edit or Insert
    • Set the ValuetoCompare to 0
l1 43
L1 - 43
  • Open the template for Field[3], the field with the buttons
  • In the EditItemTemplate
    • Click on the Update LinkButton
    • Change the (ID) to lbUpdate
    • Change the ValidationGroup to Edit
  • Make similar changes in the Insert button on the InsertItemTemplate
    • But set the ValidationGroup to Insert
l1 44
L1 - 44
  • Close the templates
  • Add a ValidationSummary after the DetailsView
    • Set the (ID) of the first to vsEdit
    • Set ShowMessageBox to True
    • Set ShowSummary to False
    • Set the ValidationGroup to Edit
  • Add a second ValidationSummary changing
    • (ID) to vsInsert
    • ValidationGroup to Insert
l1 45
L1 - 45
  • Run the page, make sure all validators work, that you get the delete confirmation and that you can insert, edit and delete fees
  • Link the page to your MIS3200 homepage
  • Copy ASPPub back to your ASPNET account and submit your MIS Portfolio public URL to the Unit 8.1 L1 drop box
think about it
Think About It!
  • Why was it necessary to add validators to the insert and edit pages?
  • How could the database be changed to eliminate the need for the validators
  • What are some of the potential negative consequences of those changes?