DD Class10

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 acomplish this.

Test page one

Test page one with form

Stored Porcedures

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>

SProc to INSERT into people

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

test by running

<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 runn 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 tracation.

Locks

Commit

Rollback

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) values (@PeopleID, @roleTypeID)

--Commit Transaction ROLLBACK 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

Fourth Normal Form

Fourth Normal Form

separates independent multi-valued facts stored in one table into separate tables.

Fifth Normal Form

Fifth Normal Form

breaks out data redundancy that is not covered by any of the previous normal forms.

HomeWork

Course adder example

http://iam.colum.edu/dd/classsource/class8/student.aspx

SQL Subquery

<sql>SELECT CourseID, CourseName, CourseNumber FROM Course WHERE (

  CourseID not in 
  (
     SELECT CourseID FROM StudentsCourses_vw WHERE StudentID = @StudentID
  )

) </sql>