DD Class13
Contents
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
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.
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