Difference between revisions of "DD Class11"

Revision as of 01:36, 19 April 2007

Review Data Diagram for final

• are they normal?
• can the design acomplish your goals?
• are they over engineered?

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

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

Student

StudentCourses

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>

<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

then

<sql> SELECT CourseID, CourseName, CourseNumber FROM Course WHERE (

  CourseID NOT IN 
  (
     SELECT CourseID FROM StudentsCourses_vw WHERE StudentID = 1
  )

) </sql>

returns

Course adder example

http://iam.colum.edu/dd/classsource/class8/student.aspx

SQL Subquery

Uploader/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 [-source]

Upload a File

http uploader

Add executionTimeout and maxRequestLength to the web.config so that large uploads will not fail.

<xml> <configuration>

   <system.web>
       <httpRuntime
           executionTimeout="1200"
           maxRequestLength="65536"
       />
   </system.web>

</configuration> </xml>

multipart form <csharp> <form enctype="multipart/form-data" runat="server"> <tr>

 <td>Select file to upload:</td>
 <td>
 <input id="myfile" type="file" runat="server"></td>

</tr> <tr>

 <td>
 <input type=button id="btnUploadTheFile" value="Upload" 
       OnServerClick="btnUploadTheFile_Click" runat="server" />
 </td>

</tr> </form> </csharp>

Parse the multipart form and save the file msdn library. System.Web.HtmlInputFile.PostedFile Property

<csharp> void btnUploadTheFile_Click(object Source, EventArgs evArgs) {

   //Path to save file
   string strBaseLocation = "";
   
   if (null != myfile.PostedFile) 
   {
       // Get the HTTP posted file instance (to simplify the code) 
       HttpPostedFile postedfile = myfile.PostedFile; 
       
       // Get the filename 
       string filename = new System.IO.FileInfo(postedfile.FileName).Name; 
       string filesize = postedfile.ContentLength.ToString();
       try 
       {
           myfile.PostedFile.SaveAs(strBaseLocation+filename);
           txtOutput.InnerHtml = "File " + strBaseLocation + filename + " uploaded successfully";
       }
       catch (Exception e) 
       {
               txtOutput.InnerHtml = "Error saving " + strBaseLocation + filename + "

"+ e.ToString();

       }
       
       //Do some other stuff 
       //maybe  like send an email to let admin know file was uploaded
   }

} </csharp>

http://iam.colum.edu/oop/classsource/class14/up.aspx [up.aspx - source] [web.config - source]

HomeWork

Web Skeleton of your site

Demostate one feature