DD Class13

Transactions in ADO

Transactions in ADO.NET http://msdn.microsoft.com/en-us/library/777e5ebh.aspx

SqlTransaction tran = null;        //Declare Transaction Object
 
        try
        {
            conn.Open();
            tran = conn.BeginTransaction(); //init trans object
            command.Transaction = tran;     //use tran object on all commands and adapters
            command = new SqlCommand(strSQL2, conn, tran);
            tran.Commit();      //commmit

        }
        catch (Exception eTran)
        {
            tran.Rollback();
        }
        finally
        { 
            conn.Close();  
        }

and now the ADO way

trans.aspx - source

Transactions in LINQ

Don't use DTS this a much bigger...

DTS transactions http://msdn.microsoft.com/en-us/library/bb387002.aspx#Y0

Use DbTransaction http://msdn.microsoft.com/en-us/library/system.data.common.dbtransaction.aspx

DbTransaction trans = db.Connection.BeginTransaction();

LINQTransaction.aspx - source LINQTransaction.aspx.cs

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

Web services

Page Methods as services

ServicePage.aspx -source ServicePage.aspx.cs

AJAX ala Services

http://asp.net/AJAX/Documentation/Live/tutoria/UsingUpdatePanelUserControls.aspx

http://asp.net/ajax/documentation/live/tutorials/ConsumingWebServicesWithAJAXTutorial.aspx broken example on their website

fixed on our site

CallWebServiceMethods.aspx - source CallWebServiceMethods.js - source

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.

Mail

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

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>

Final

Create a page the is protected by .NET authentication

Data Design Final 2011

Data Design Final 2012