DD Class13
Contents
Transactions in LINQ
Links
SQL Transactions
Larger Transactions
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
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]
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
Higher Normal Forms
once a db is in 3NF you can go further
Boyce-Codd Normal form
- needs to be in 3NF
http://en.wikipedia.org/wiki/Boyce-Codd_normal_form
Fourth Normal Form
- needs to be in BCNF
Fourth Normal Form
- separates independent multivalued facts stored in one table into separate tables.
http://en.wikipedia.org/wiki/Fourth_normal_form
Fifth Normal Form
- needs to be in 4NF
Fifth Normal Form
- breaks out data redundancy that is not covered by any of the previous normal forms.
http://en.wikipedia.org/wiki/Fifth_normal_form
More normal forms
http://en.wikipedia.org/wiki/Database_normalization
Mappath
Server.Mappath will return the real physical location of a file that is in a virtual directory.
http://msdn.microsoft.com/en-us/library/ms524632%28v=vs.90%29.aspx
Special permissions need to be set on the folder that you are going to upload to. The anonymous ASP.NET account need to have write permissions to this folder.
It is not recommended that folder with write permissions also have execute and should not allow directory listing.
Upload a File
http up-loader
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]
Modifications
Unique file name
<csharp> // Get the filename
string filename = new System.IO.FileInfo(postedfile.FileName).Name; string strUniqueDate = System.DateTime.Now.Year.ToString() + System.DateTime.Now.Month.ToString() + System.DateTime.Now.Day.ToString() + System.DateTime.Now.Ticks.ToString() ; filename = strUniqueDate + "_" + filename;
</csharp>
After you upload w file it os often wise to add the file name to a database(especially if you obfuscate the name you might want to save the original name). You may also may want to send an email notifying you that a file has been uploaded.
Aspx Templates and Reuse
The template page
http://iam.colum.edu/dd/classsource/class11/UserControls/TemplateNoControls.aspx - source
Include Files
<csharp>
This is the header |
||
Main Column
Content Content Content</a> </td> |
</td> </tr> | |
</td> </tr> </table> </csharp> http://iam.colum.edu/dd/gbrowser.php?file=/classsource/class11/UserControls/incs/col1.aspx http://iam.colum.edu/dd/gbrowser.php?file=/classsource/class11/UserControls/incs/col3.aspx http://iam.colum.edu/dd/gbrowser.php?file=/classsource/class11/UserControls/incs/foot.aspx User ControlsUser Controls are user defined controls that are reusable. They have and event model similar to the page class. They have an isolated scope. The page class can send data to user controls via user defined atributes. User Controls must be registered via a Register Directive (this can also be done via web.config for an entire site) <csharp> <%@ Register TagPrefix="DataDesign" TagName="Col1" Src="controls/col1.ascx" %> </csharp> the control can then be used by adding the custom tag to the page <csharp> <DataDesign:Col1 id="ucCol1" runat="server" /> </csharp> http://iam.colum.edu/dd/classsource/class11/UserControls/TemplateUserControls.aspx Controls http://iam.colum.edu/dd/gbrowser.php?file=/classsource/class11/UserControls/controls/col1.ascx http://iam.colum.edu/dd/gbrowser.php?file=/classsource/class11/UserControls/controls/col3.ascx http://iam.colum.edu/dd/gbrowser.php?file=/classsource/class11/UserControls/controls/foot.ascx <csharp> string strTo = "jmeyers3@iit.edu"; string strFrom = "jmeyers3@iit.edu"; string strSubject = "Hi jeff"; string strBody = "A real nice body text here"; //Send email //SmtpMail.SmtpServer = "mail2.iit.edu"; //SmtpMail.Send(strFrom, strTo, strSubject, strBody); SmtpClient client = new SmtpClient("localhost", 25); client.DeliveryMethod = SmtpDeliveryMethod.Network; client.Send(strFrom, strTo, strSubject, strBody); </csharp> in web config <xml> <system.net> <mailSettings> <smtp deliveryMethod="Network" from="jeff@iam.colum.edu"> <network host="localhost" port="25" defaultCredentials="true"/> </smtp> </mailSettings> </system.net> </xml> HomeWorkFinal Proposal Final Website proposal. Due 11/29. Final Proposal Write a short requirements document (website) that explains
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.
FinalClassified site. The website should by able to host classified posts by category by location. The posts should be readable by everyone but only people with accounts should be able to post. The the most current x number of posts should show in a location/category. A user should be able to edit their own posts but not others posts. Admins can edit everyone posts. Admins can also edit categories and location. Optional (extra credit) posts have pictures. Posts use a rich text editor.
Default Page
Posts Page
New User
Logon
Protected Pages New Post
Admin\Categories
Admin\Cities
Admin\Edit posts
UserName PostTitle PostDetail PostCategory CityName RecCreationDate
PeopleID UserName Password IsAdmin
PeopleID PostTitle PostDesc PostCategoryID CityID RecCreationDate
PostCatID PostCategotyID PostCategoryName
CityID CityName |