Difference between revisions of "DD Class12"

Create a new user account

To insert into a new user we need to to combine three steps.

1. INSERT into the People Table
2. Get the new PeopleID
3. INSERT into the roles table

<sql> INSERT into People (salutationID, firstName, lastName, logonName, passwd) values (1, 'jeff', 'meyers', 'jmeyers', 'monkey') </sql>

Check the new users PeopleID

<sql> SELECT PeopleID from People WHERE firstName = 'jeff' and lastName='meyers' and logonName='jmeyers' </sql>

results PeopleID = 1

Give the new user a Role by inserting the PeopleID and a roleID into the roles table

<sql> INSERT into roles ( PeopleID, roleTypeID, RoleActive ) values ( 1, 1, 1) </sql>

Since all of these steps need to happen or fail as a complete unit we must use an transaction. Of course in .NET there are several way to accomplish this.

Test page one

http://iam.colum.edu/dd/classsource/class10/trans.aspx -source

Test page one with form

SQL transation

This can all be done in shorter statement by putting the Select query in the second INSERT

<sql> INSERT INTO People (salutationID, firstName, lastName, logonName, passwd) VALUES (1, 'jeffTest2', 'meyersTest2', 'jmeyersTest2', 'monkey')

GO

INSERT INTO roles ( PeopleID, roleTypeID, RoleActive ) ( SELECT PeopleID, 1, 1 FROM People WHERE firstName = 'jeffTest2' AND lastName='meyersTest2' AND logonName='jmeyersTest2' )

</sql>

By using the Keywords BEGIN TRANSACTION and COMMIT TRANSACTION we can ensure that our batched statements run as a transaction <sql> BEGIN TRANSACTION

/* Remove old trancation test so we can run it again */ DELETE from Roles where PeopleID in (SELECT PeopleID FROM People WHERE firstName = 'jeffTest2' AND lastName='meyersTest2' AND logonName='jmeyersTest2')

DELETE from People where firstName = 'jeffTest2' AND lastName='meyersTest2' AND logonName='jmeyersTest2'

GO

/*INSERT for tranaction */ INSERT INTO People (salutationID, firstName, lastName, logonName, passwd) VALUES (1, 'jeffTest2', 'meyersTest2', 'jmeyersTest2', 'monkey')

GO

INSERT INTO roles ( PeopleID, roleTypeID, RoleActive ) ( SELECT PeopleID, 1, 1 FROM People WHERE firstName = 'jeffTest2' AND lastName='meyersTest2' AND logonName='jmeyersTest2' )

COMMIT TRANSACTION </sql>

Stored Procedures

Stored procedures are a way in MSSQL to store a compiled SQL statement. The statement will run faster than a regular SQL statement because the execution plan has already been calculated. The plan is executed with a new set of parameters. There are both input parameters and output parameters. SQL parameters are prefixed with the @ sign.

Default Template

<sql> -- ================================================ -- Template generated from Template Explorer using: -- Create Procedure (New Menu).SQL -- -- Use the Specify Values for Template Parameters -- command (Ctrl-Shift-M) to fill in the parameter -- values below. -- -- This block of comments will not be included in -- the definition of the procedure. -- ================================================ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: <Author,,Name> -- Create date: <Create Date,,> -- Description: <Description,,> -- ============================================= CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName> -- Add the parameters for the stored procedure here <@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>, <@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0> AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON;

   -- Insert statements for procedure here

SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2> END GO </sql>

Stored Procedure (SProc) to INSERT into people

<sql> -- ============================================= -- Author: Jeff -- Create date: -- Description: -- ============================================= CREATE PROCEDURE PeopleInsert -- Add the parameters for the stored procedure here @salutationID int = 1, @firstName varchar(50), @lastName varchar(50), @logonName varchar(50),

   @passwd varchar(50)

AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. -- SET NOCOUNT ON;

   -- Insert statements for procedure here

INSERT INTO People (salutationID, firstName, lastName, logonName, passwd) VALUES (@salutationID, @firstName, @lastName, @logonName, @passwd) END GO </sql>

You can test the stored procedure by running it and passing in all the required parameters.

<sql> PeopleInsert

@salutationID = 1, @firstName ='sprocTestFname', @lastName='sprocTestLname', @logonName ='sprocTestLogonName',

       @passwd ='monkey'

</sql>

all one line is fine

<sql> PeopleInsert @salutationID = 1, @firstName ='sprocTestFname', @lastName='sprocTestLname', @logonName ='sprocTestLogonName', @passwd ='monkey' </sql>

GetPeopleID <sql> CREATE PROCEDURE GetPeopleID -- Add the parameters for the stored procedure here @firstName varchar(50), @lastName varchar(50),

       @logonName varchar(50) 

AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON;

       -- Insert statements for procedure here

SELECT PeopleID FROM People WHERE firstName = @firstName AND lastName= @lastName AND logonName= @logonName END </sql>

Again we can run it like this <sql> GetPeopleID @firstName ='sprocTestFname', @lastName='sprocTestLname', @logonName ='sprocTestLogonName' </sql>

Transactions

Whenever you are making changes (insert/update/delete) two more than one table or the results of a select statement are used to update another table you need to use a transaction. Transaction either succeed to fail as a complete unit. Trans actions can cause a lock on a row or tables and should be kept a brief as possible. After all of the work of the transaction is complete it can either be committed or rolled back.

Commit

Actually commits tht changes

Rollback

rolls back all changes and the database.

Transaction can also have scope.

Single SQL SProc

full InsertPersonAndRole

<sql> -- ============================================= -- Author: jeff -- Create date: -- Description: -- ============================================= CREATE PROCEDURE AddUserAndRole -- Add the parameters for the stored procedure here @salutationID int = 1, @firstName varchar(50) , @lastName varchar(50), @logonName varchar(50), @passwd varchar(50), @roleTypeID int AS BEGIN --Start transaction BEGIN TRANSACTION

--INSERT into People INSERT into People (salutationID, firstName, lastName, logonName, passwd) values (@salutationID, @firstName, @lastName, @logonName, @passwd)

--Get PeopleID DECLARE @PeopleID int

Select @PeopleID = @@identity

--INSERT into Roles INSERT into roles (PeopleID, RoleTypeID, RoleActive) values (@PeopleID, @roleTypeID, 1)

--Commit Transaction ROLLBACK TRANSACTION

       --COMMIT TRANSACTION

END GO

</sql>

and the SQL statement to call the stored procedure

<sql> AddUserAndRole @salutationID = 1, @firstName = 'fnTransTest' , @lastName ='lnTransTest', @logonName ='lnTransTest', @passwd ='pwTransTest', @roleTypeID = 1 </sql>

Stored Procedures

Add edit example this will add or edit if the record exsits already

<sql> -- ============================================= -- Author: Jeff Meyers -- Create date: -- Description: -- ============================================= ALTER PROCEDURE [dbo].[AddUpdatePerson] -- Add the parameters for the stored procedure here @UserName varchar(50), @OasisID varchar(50), @OasisEmail varchar(50), @Email varchar(50), @FirstName varchar(50), @LastName varchar(50), @PhoneNumber varchar(50) AS BEGIN DECLARE @PeopleID int

   -- Insert statements for procedure here

SELECT @PeopleID = PeopleID from People WHERE UserName = @UserName

IF @PeopleID is null BEGIN INSERT into People (UserName, OasisID, OasisEmail, Email, FirstName, LastName, PhoneNumber) VALUES (@UserName, @OasisID, @OasisEmail, @Email, @FirstName, @LastName, @PhoneNumber)

select @@identity as PeopleID END ELSE BEGIN UPDATE People set UserName = @UserName, OasisID = @OasisID, OasisEmail = @OasisEmail , Email = @Email , FirstName = @FirstName , LastName = @LastName, PhoneNumber = @PhoneNumber, LastUpdate = GETDATE() WHERE PeopleID = @PeopleID

select @PeopleID as PeopleID END END

</sql>

and now the ADO way

http://iam.colum.edu/dd/classsource/class10/trans.aspx - source

HomeWork

Try to create a use logon page that inserts a new user.

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

HomeWork

Final Proposal

Final Website proposal. Due 11/29. Final Proposal

Write a short requirements document (website) that explains

1. Rationale: for website design or introduction of product features
2. Audience: Description of target audience and segments for new website areas
3. Requirements Summary: Key Areas and Features elaborate on requirements above
4. 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

Sample Cheese Poposal

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 multi-valued 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

upload permissions

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]

Modifications

Uniqie filename

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

Add to database instead of sending email

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