Revision as of 03:34, 28 November 2011

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

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

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]

C:\Windows\Microsoft.NET\Framework\v2.0.50727>aspnet_regsql.exe

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>

   </td>

       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);