|
|
(8 intermediate revisions by the same user not shown) |
Line 151: |
Line 151: |
| [[Image:DDBlogLookupTable.png]] | | [[Image:DDBlogLookupTable.png]] |
| | | |
− | ==HomeWork==
| + | ==Logon Using the tables in blog== |
− | | |
− | Blog project can be done alone or XP style with one partner.
| |
− | | |
− | | |
− | [[DD Blog Project]]
| |
− | | |
− | ==Logon Using the tables we made last week== | |
| You can use the Authorization Section of web.config to protect file or a directory. You can have more than one web.config in a project also. You can have one for each folder. The web.config setting inherit from the parent folder and most section can be overridden. Here is an example of a small web.config that protects a folder call admin with the .net authentication mechanism. | | You can use the Authorization Section of web.config to protect file or a directory. You can have more than one web.config in a project also. You can have one for each folder. The web.config setting inherit from the parent folder and most section can be overridden. Here is an example of a small web.config that protects a folder call admin with the .net authentication mechanism. |
| | | |
Line 195: |
Line 188: |
| | | |
| ==Logon the Microsoft way== | | ==Logon the Microsoft way== |
− |
| |
− | ==Sql Aggregate Functions==
| |
− |
| |
− | They perform a calculation on a set to values and return a single value.
| |
− | Aggregate functions are determinate. They will return the same value given the same dataset.
| |
− |
| |
− | *AVG
| |
− | *MIN
| |
− | *CHECKSUM
| |
− | *SUM
| |
− | *CHECKSUM_AGG
| |
− | *STDEV
| |
− | *COUNT
| |
− | *STDEVP
| |
− | *COUNT_BIG
| |
− | *VAR
| |
− | *GROUPING
| |
− | *VARP
| |
− | *MAX
| |
− |
| |
− | <sql>
| |
− | SELECT MAX(PricePerPound) as MaxPrice from CheesePrice
| |
− |
| |
− | SELECT AVG(PricePerPound) as AvgPrice from CheesePrice
| |
− |
| |
− | SELECT COUNT(CheeseID) as Count from CheesePrice
| |
− | </sql>
| |
− |
| |
− | ==Sub Query==
| |
− |
| |
− | A query may be a part of another query. This allows
| |
− |
| |
− | IN vs EXSISTS
| |
− |
| |
− | Remember the Many to Many relation ship in the Student Registration System
| |
− |
| |
− | [[Image:ManyToManyStudentCourses.png]]
| |
− |
| |
− | Here a student has many course and a course has many student. Imagine in the registration system the student will be presented with a list of classes that they can add. That list of classes '''should not''' include the courses that they are already taking.
| |
− | Course
| |
− | {| class="wikitable" cellpadding="5" cellspacing="0"
| |
− | !CourseID !! CourseName !! CourseNumber
| |
− | |-
| |
− | |9 || Science || 66-0002
| |
− | |-
| |
− | |10 || Math || 66-0001
| |
− | |-
| |
− | |11 || Data Design || 36-2601
| |
− | |-
| |
− | |168 || Application Design || 36-4601
| |
− | |-
| |
− | |169 || OOP || 36-2001
| |
− | |}
| |
− |
| |
− | Student
| |
− | {| class="wikitable" cellpadding="5" cellspacing="0"
| |
− | !StudentID !! PeopleID !! OasisID
| |
− | |-
| |
− | |1 || 1 || 666666
| |
− | |-
| |
− | |2 || 2 || 777777
| |
− | |}
| |
− |
| |
− |
| |
− | StudentCourses
| |
− | {| class="wikitable" cellpadding="5" cellspacing="0"
| |
− | ! StudentID !! CourseID !! SemesterID
| |
− | |-
| |
− | |1 || 9 || 1
| |
− | |-
| |
− | |2 || 9 || 1
| |
− | |-
| |
− | |1 || 10 || 1
| |
− | |}
| |
− |
| |
− | StudentsCourses_vw
| |
− | <sql>
| |
− | SELECT dbo.Student.PeopleID, dbo.Student.OasisID, dbo.StudentCourses.SemesterID, dbo.Course.CourseName,
| |
− | dbo.Course.CourseNumber, dbo.Student.StudentID,
| |
− | dbo.Course.CourseID
| |
− | FROM dbo.StudentCourses INNER JOIN
| |
− | dbo.Student ON dbo.StudentCourses.StudentID = dbo.Student.StudentID INNER JOIN
| |
− | dbo.Course ON dbo.StudentCourses.CourseID = dbo.Course.CourseID
| |
− | </sql>
| |
− |
| |
− | [[Image:StudentsCourses_vw.png]]
| |
− |
| |
− | <sql>
| |
− | SELECT CourseID, CourseName, CourseNumber
| |
− | FROM Course
| |
− | WHERE
| |
− | (
| |
− | CourseID not in
| |
− | (
| |
− | SELECT CourseID FROM StudentsCourses_vw WHERE StudentID = @StudentID
| |
− | )
| |
− | )
| |
− | </sql>
| |
− |
| |
− | This query has two parts
| |
− |
| |
− | SELECT CourseID FROM StudentsCourses_vw WHERE StudentID = 1
| |
− |
| |
− | returns
| |
− | {|
| |
− | !CourseID
| |
− | |-
| |
− | |9
| |
− | |-
| |
− | |10
| |
− | |}
| |
− |
| |
− | then
| |
− |
| |
− | <sql>
| |
− | SELECT CourseID, CourseName, CourseNumber
| |
− | FROM Course
| |
− | WHERE
| |
− | (
| |
− | CourseID NOT IN
| |
− | (
| |
− | SELECT CourseID FROM StudentsCourses_vw WHERE StudentID = 1
| |
− | )
| |
− | )
| |
− | </sql>
| |
− |
| |
− | returns
| |
− | {|
| |
− | ! CourseID !! CousreName !!CourseNumber
| |
− | |-
| |
− | |11 || Data Design || 36-2601
| |
− | |-
| |
− | |168 || Application Design || 36-4601
| |
− | |-
| |
− | |169 || OOP || 36-2001
| |
− | |}
| |
− |
| |
− | Course adder example
| |
− |
| |
− | http://iam.colum.edu/dd/classsource/class8/student.aspx
| |
− |
| |
− | SQL Subquery
| |
− |
| |
− | ==Email==
| |
− |
| |
− |
| |
− |
| |
− | ===Send Mail===
| |
− | <csharp><% @Page Language="C#" %>
| |
− | <% @Import Namespace="System.Web.Mail" %>
| |
− | <%
| |
− | //set up some strings for the email
| |
− |
| |
− | string strTo = "only_a_test@fastmail.fm";
| |
− | string strFrom = "jeff@interactive.colum.edu";
| |
− | string strSubject = "Hi jeff";
| |
− | string strBody = "A real nice body text here";
| |
− |
| |
− | //Send email
| |
− | SmtpMail.SmtpServer = "localhost";
| |
− | SmtpMail.Send(strFrom, strTo, strSubject, strBody);
| |
− | %></csharp>
| |
− | http://iam.colum.edu/oop/classsource/class14/mail.aspx
| |
− | [[http://iam.colum.edu/oop/gbrowser.php?file=/classsource/class14/mail.aspx -source]]
| |
− |
| |
− | C:\Windows\Microsoft.NET\Framework\v2.0.50727>aspnet_regsql.exe
| |
| | | |
| ==HomeWork== | | ==HomeWork== |
| | | |
− | Final Proposal
| + | Create a many to many relationship with games. Create a web interface that can add and remove games from genres. |
| | | |
− | Final Website proposal. Due 11/29.
| + | <!-- |
− | Final Proposal
| + | Blog project can be done alone or XP style with one partner. |
| | | |
− | Write a short requirements document (website) that explains
| |
| | | |
− | # Rationale: for website design or introduction of product features
| + | [[DD Blog Project]] |
− | # Audience: Description of target audience and segments for new website areas
| + | --> |
− | # Requirements Summary: Key Areas and Features elaborate on requirements above
| |
− | # Design Documents: Site Map (hand drawn, word doc, xml visio, paper napkin) and Data Diagram (sql 2008 diagram screen shot)
| |
− | | |
− | Final Requirements
| |
− | | |
− | The final website should show off all of your new .NET skills. I'll leave the idea and the content of the website up to you. Good proposals might be... An anonymous cheese rating website (similar) to midterm but using SQL server as the persistence layer. It would allow ratings for cheese from varios regions then show the most popular cheese by region.
| |
− | | |
− | * Use Web Forms for user interaction
| |
− | * Use State (sessions, application, viewstate)
| |
− | * Use Database (I can help you design your db if you can't do this) with either SQLDataSource Direct ADO or LINQDataSource
| |
− | | |
− | [http://brookfield.rice.iit.edu/jmeyers/463/classsource/CheeseProp.aspx Sample Cheese Poposal]
| |
− | | |
− | ==Logon Using the tables we made last week==
| |
− | You can use the Authorization Section of web.config to protect file or a directory. You can have more than one web.config in a project also. You can have one for each folder. The web.config setting inherit from the parent folder and most section can be overridden. Here is an example of a small web.config that protects a folder call admin with the .net authentication mechanism.
| |
− | | |
− | http://iam.colum.edu/dd/gbrowser.php?file=/classsource/Logon/Web.config
| |
− | | |
− | Main web.config add the following to the system.web section
| |
− | <xml>
| |
− | <system.web>
| |
− |
| |
− | <authentication mode="Forms">
| |
− | <forms loginUrl="classsource/logon/logon.aspx" name="adAuthCookie" timeout="20" path="/" protection="All">
| |
− | </forms>
| |
− | </authentication>
| |
− |
| |
− | </system.web>
| |
− | </xml>
| |
− | | |
− | Now any link to the admin folder
| |
− | | |
− | http://iam.colum.edu/dd/classsource/logon/admin/
| |
− | | |
− | should redirect to the logon page
| |
− | | |
− | '''Logon Page'''
| |
− | | |
− | http://iam.colum.edu/dd/classsource/Logon/Logon.aspx [http://iam.colum.edu/dd/gbrowser.php?file=/classsource/Logon/Logon.aspx - source]
| |
− | http://iam.colum.edu/dd/gbrowser.php?file=/classsource/Logon/Logon.aspx.cs
| |
− | | |
− | Add a New user page
| |
− | http://iam.colum.edu/dd/classsource/Logon/CreateAccount.aspx [http://iam.colum.edu/dd/gbrowser.php?file=/classsource/Logon/CreateAccount.aspx -source]
| |
− | http://iam.colum.edu/dd/gbrowser.php?file=/classsource/Logon/CreateAccount.aspx.cs
| |
− | | |
− | '''Admin Page'''
| |
− | http://iam.colum.edu/dd/classsource/Logon/Admin/Default.aspx [http://iam.colum.edu/dd/gbrowser.php?file=/classsource/Logon/Admin/Default.aspx - source]
| |
− | http://iam.colum.edu/dd/gbrowser.php?file=/classsource/Logon/Admin/Default.aspx.cs
| |
− | | |
− | ==Logon the Microsoft way==
| |
− | | |
− | ==Sql Aggregate Functions==
| |
− | | |
− | They perform a calculation on a set to values and return a single value.
| |
− | Aggregate functions are determinate. They will return the same value given the same dataset.
| |
− | | |
− | *AVG
| |
− | *MIN
| |
− | *CHECKSUM
| |
− | *SUM
| |
− | *CHECKSUM_AGG
| |
− | *STDEV
| |
− | *COUNT
| |
− | *STDEVP
| |
− | *COUNT_BIG
| |
− | *VAR
| |
− | *GROUPING
| |
− | *VARP
| |
− | *MAX
| |
− | | |
− | <sql>
| |
− | SELECT MAX(PricePerPound) as MaxPrice from CheesePrice
| |
− | | |
− | SELECT AVG(PricePerPound) as AvgPrice from CheesePrice
| |
− | | |
− | SELECT COUNT(CheeseID) as Count from CheesePrice
| |
− | </sql>
| |
− | | |
− | ==Sub Query==
| |
− | | |
− | A query may be a part of another query. This allows
| |
− | | |
− | IN vs EXSISTS
| |
− | | |
− | Remember the Many to Many relation ship in the Student Registration System
| |
− | | |
− | [[Image:ManyToManyStudentCourses.png]]
| |
− | | |
− | Here a student has many course and a course has many student. Imagine in the registration system the student will be presented with a list of classes that they can add. That list of classes '''should not''' include the courses that they are already taking.
| |
− | Course
| |
− | {| class="wikitable" cellpadding="5" cellspacing="0"
| |
− | !CourseID !! CourseName !! CourseNumber
| |
− | |-
| |
− | |9 || Science || 66-0002
| |
− | |-
| |
− | |10 || Math || 66-0001
| |
− | |-
| |
− | |11 || Data Design || 36-2601
| |
− | |-
| |
− | |168 || Application Design || 36-4601
| |
− | |-
| |
− | |169 || OOP || 36-2001
| |
− | |}
| |
− | | |
− | Student
| |
− | {| class="wikitable" cellpadding="5" cellspacing="0"
| |
− | !StudentID !! PeopleID !! OasisID
| |
− | |-
| |
− | |1 || 1 || 666666
| |
− | |-
| |
− | |2 || 2 || 777777
| |
− | |}
| |
− | | |
− | | |
− | StudentCourses
| |
− | {| class="wikitable" cellpadding="5" cellspacing="0"
| |
− | ! StudentID !! CourseID !! SemesterID
| |
− | |-
| |
− | |1 || 9 || 1
| |
− | |-
| |
− | |2 || 9 || 1
| |
− | |-
| |
− | |1 || 10 || 1
| |
− | |}
| |
− | | |
− | StudentsCourses_vw
| |
− | <sql>
| |
− | SELECT dbo.Student.PeopleID, dbo.Student.OasisID, dbo.StudentCourses.SemesterID, dbo.Course.CourseName,
| |
− | dbo.Course.CourseNumber, dbo.Student.StudentID,
| |
− | dbo.Course.CourseID
| |
− | FROM dbo.StudentCourses INNER JOIN
| |
− | dbo.Student ON dbo.StudentCourses.StudentID = dbo.Student.StudentID INNER JOIN
| |
− | dbo.Course ON dbo.StudentCourses.CourseID = dbo.Course.CourseID
| |
− | </sql>
| |
− | | |
− | [[Image:StudentsCourses_vw.png]] | |
− | | |
− | <sql>
| |
− | SELECT CourseID, CourseName, CourseNumber
| |
− | FROM Course
| |
− | WHERE
| |
− | (
| |
− | CourseID not in
| |
− | (
| |
− | SELECT CourseID FROM StudentsCourses_vw WHERE StudentID = @StudentID
| |
− | )
| |
− | )
| |
− | </sql>
| |
− | | |
− | This query has two parts
| |
− | | |
− | SELECT CourseID FROM StudentsCourses_vw WHERE StudentID = 1
| |
− | | |
− | returns
| |
− | {|
| |
− | !CourseID
| |
− | |-
| |
− | |9
| |
− | |-
| |
− | |10
| |
− | |}
| |
− | | |
− | then
| |
− | | |
− | <sql>
| |
− | SELECT CourseID, CourseName, CourseNumber
| |
− | FROM Course
| |
− | WHERE
| |
− | (
| |
− | CourseID NOT IN
| |
− | (
| |
− | SELECT CourseID FROM StudentsCourses_vw WHERE StudentID = 1
| |
− | )
| |
− | )
| |
− | </sql>
| |
− | | |
− | returns
| |
− | {|
| |
− | ! CourseID !! CousreName !!CourseNumber
| |
− | |-
| |
− | |11 || Data Design || 36-2601
| |
− | |-
| |
− | |168 || Application Design || 36-4601
| |
− | |-
| |
− | |169 || OOP || 36-2001
| |
− | |}
| |
− | | |
− | Course adder example
| |
− | | |
− | http://iam.colum.edu/dd/classsource/class8/student.aspx
| |
− | | |
− | SQL Subquery
| |
− | | |
− | ==Email==
| |
− | | |
− | | |
− | | |
− | ===Send Mail===
| |
− | <csharp><% @Page Language="C#" %>
| |
− | <% @Import Namespace="System.Web.Mail" %>
| |
− | <%
| |
− | //set up some strings for the email
| |
− |
| |
− | string strTo = "only_a_test@fastmail.fm";
| |
− | string strFrom = "jeff@interactive.colum.edu";
| |
− | string strSubject = "Hi jeff";
| |
− | string strBody = "A real nice body text here";
| |
− |
| |
− | //Send email
| |
− | SmtpMail.SmtpServer = "localhost";
| |
− | SmtpMail.Send(strFrom, strTo, strSubject, strBody);
| |
− | %></csharp>
| |
− | http://iam.colum.edu/oop/classsource/class14/mail.aspx
| |
− | [[http://iam.colum.edu/oop/gbrowser.php?file=/classsource/class14/mail.aspx -source]]
| |
− | | |
− | C:\Windows\Microsoft.NET\Framework\v2.0.50727>aspnet_regsql.exe
| |
− | | |
− | ==HomeWork==
| |
− | | |
− | Final Proposal
| |
− | | |
− | Final Website proposal. Due 11/29.
| |
− | Final Proposal
| |
− | | |
− | Write a short requirements document (website) that explains
| |
− | | |
− | # Rationale: for website design or introduction of product features
| |
− | # Audience: Description of target audience and segments for new website areas
| |
− | # Requirements Summary: Key Areas and Features elaborate on requirements above
| |
− | # Design Documents: Site Map (hand drawn, word doc, xml visio, paper napkin) and Data Diagram (sql 2008 diagram screen shot)
| |
− | | |
− | Final Requirements
| |
− | | |
− | The final website should show off all of your new .NET skills. I'll leave the idea and the content of the website up to you. Good proposals might be... An anonymous cheese rating website (similar) to midterm but using SQL server as the persistence layer. It would allow ratings for cheese from varios regions then show the most popular cheese by region.
| |
− | | |
− | * Use Web Forms for user interaction
| |
− | * Use State (sessions, application, viewstate)
| |
− | * Use Database (I can help you design your db if you can't do this) with either SQLDataSource Direct ADO or LINQDataSource
| |
− | | |
− | [http://brookfield.rice.iit.edu/jmeyers/463/classsource/CheeseProp.aspx Sample Cheese Poposal]
| |
Second Normal Form eliminates functional dependencies on a partial key by putting the fields in a separate table from those that are dependent on the whole key.
Third Normal Form eliminates functional dependencies on non-key fields by putting them in a separate table. At this stage, all non-key fields are dependent on the key, the whole key and nothing but the key. Must be in second normal form.
Look up tables. Are often used to define a many to many relation ship. These lookup tables often have a compound key.
This is a demonstration of a logon system where users can have multiple roles. There is a compound key used on the roles table. The compound key consists of two or more foreign keys. Each combination of foreign keys must be unique.
Here the BlogTags Tbale uses a compound key (two primary keys) to join two other tables. The Blog table is joined to the Tag table where each person can add a tag. This is a one to many one to many relationship. Person can a tag to each blog. This is often referred to as a look up table
You can use the Authorization Section of web.config to protect file or a directory. You can have more than one web.config in a project also. You can have one for each folder. The web.config setting inherit from the parent folder and most section can be overridden. Here is an example of a small web.config that protects a folder call admin with the .net authentication mechanism.
<forms loginUrl="classsource/logon/logon.aspx" name="adAuthCookie" timeout="20" path="/" protection="All">
</forms>
Create a many to many relationship with games. Create a web interface that can add and remove games from genres.