Difference between revisions of "DD Class11"
(→Sub Query) |
(→Uploader/Email) |
||
Line 156: | Line 156: | ||
upload permissions | upload permissions | ||
+ | |||
+ | |||
+ | ===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]] | ||
+ | |||
+ | ===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 Attributes: | ||
+ | executionTimeout="[seconds]" - time in seconds before request is automatically timed out | ||
+ | maxRequestLength="[KBytes]" - KBytes size of maximum request length to accept | ||
+ | useFullyQualifiedRedirectUrl="[true|false]" - Fully qualifiy the URL for client redirects | ||
+ | minFreeThreads="[count]" - minimum number of free thread to allow execution of new requests | ||
+ | minLocalRequestFreeThreads="[count]" - minimum number of free thread to allow execution of new local requests | ||
+ | appRequestQueueLimit="[count]" - maximum number of requests queued for the application | ||
+ | enableKernelOutputCache="[true|false]" - enable the http.sys cache on IIS6 and higher - default is true | ||
+ | enableVersionHeader="[true|false]" - outputs X-AspNet-Version header with each request | ||
+ | --> | ||
+ | <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 | ||
+ | [[http://iam.colum.edu/oop/gbrowser.php?file=/classsource/class14/up.aspx up.aspx - source]] | ||
+ | [[http://iam.colum.edu/oop/gbrowser.php?file=/classsource/class14/web.config web.config - source]] | ||
==HomeWork== | ==HomeWork== |
Revision as of 03:25, 12 April 2007
Contents
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
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
StudentID | PeopleID | OasisID |
---|---|---|
1 | 1 | 666666 |
2 | 2 | 777777 |
StudentCourses
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>
<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
Uploader/Email
Mappath
Server.Mappath
upload permissions
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