Difference between revisions of "DD Class12"
(New page: Category:Data Design) |
(→HomeWork) |
||
(32 intermediate revisions by the same user not shown) | |||
Line 1: | Line 1: | ||
[[Category:Data Design]] | [[Category:Data Design]] | ||
+ | |||
+ | ==Authentcation== | ||
+ | Authentication | ||
+ | Discuss Methods and problems | ||
+ | |||
+ | Methods | ||
+ | |||
+ | * Windows | ||
+ | * Passport | ||
+ | * Forms | ||
+ | |||
+ | The authentication method is set in the root web.config | ||
+ | <xml> | ||
+ | <authentication mode="Forms"> | ||
+ | <forms loginUrl="logon.aspx" | ||
+ | timeout="20" path="/" | ||
+ | protection="All"> | ||
+ | </forms> | ||
+ | </authentication> | ||
+ | </xml> | ||
+ | |||
+ | the folder web.config | ||
+ | |||
+ | <xml> | ||
+ | <location path="admin"> | ||
+ | <system.web> | ||
+ | <authorization> | ||
+ | <deny users="?"/> | ||
+ | </authorization> | ||
+ | </system.web> | ||
+ | </location> | ||
+ | </configuration> | ||
+ | </xml> | ||
+ | |||
+ | We will be using a small section of the .net provider model.S | ||
+ | |||
+ | ==Logon Using the tables we made last week== | ||
+ | You can use the Authorization Section of web.config to protect file or a directory. You can have more than one web.config in a project also. You can have one for each folder. The web.config setting inherit from the parent folder and most section can be overridden. Here is an example of a small web.config that protects a folder call admin with the .net authentication mechanism. | ||
+ | |||
+ | http://iam.colum.edu/dd/gbrowser.php?file=/classsource/Logon/Web.config | ||
+ | |||
+ | Main web.config add the following to the system.web section | ||
+ | <xml> | ||
+ | <system.web> | ||
+ | |||
+ | <authentication mode="Forms"> | ||
+ | <forms loginUrl="classsource/logon/logon.aspx" name="adAuthCookie" timeout="20" path="/" protection="All"> | ||
+ | </forms> | ||
+ | </authentication> | ||
+ | |||
+ | </system.web> | ||
+ | </xml> | ||
+ | |||
+ | Now any link to the admin folder | ||
+ | |||
+ | http://iam.colum.edu/dd/classsource/logon/admin/ | ||
+ | |||
+ | should redirect to the logon page | ||
+ | |||
+ | '''Logon Page''' | ||
+ | |||
+ | http://iam.colum.edu/dd/classsource/Logon/Logon.aspx [http://iam.colum.edu/dd/gbrowser.php?file=/classsource/Logon/Logon.aspx - source] | ||
+ | http://iam.colum.edu/dd/gbrowser.php?file=/classsource/Logon/Logon.aspx.cs | ||
+ | |||
+ | Add a New user page | ||
+ | http://iam.colum.edu/dd/classsource/Logon/CreateAccount.aspx [http://iam.colum.edu/dd/gbrowser.php?file=/classsource/Logon/CreateAccount.aspx -source] | ||
+ | http://iam.colum.edu/dd/gbrowser.php?file=/classsource/Logon/CreateAccount.aspx.cs | ||
+ | |||
+ | '''Admin Page''' | ||
+ | http://iam.colum.edu/dd/classsource/Logon/Admin/Default.aspx [http://iam.colum.edu/dd/gbrowser.php?file=/classsource/Logon/Admin/Default.aspx - source] | ||
+ | http://iam.colum.edu/dd/gbrowser.php?file=/classsource/Logon/Admin/Default.aspx.cs | ||
+ | |||
+ | ==Logon the Microsoft way== | ||
+ | |||
+ | |||
+ | |||
+ | ==Create a new user account== | ||
+ | |||
+ | To insert into a new user we need to to combine three steps. | ||
+ | #INSERT into the People Table | ||
+ | #Get the new PeopleID | ||
+ | #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 [http://iam.colum.edu/dd/gbrowser.php?file=/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 [http://msdn.microsoft.com/en-us/library/ms188929.aspx BEGIN TRANSACTION] and [http://msdn.microsoft.com/en-us/library/ms190295.aspx 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 | ||
+ | [http://iam.colum.edu/dd/gbrowser.php?file=/classsource/class10/trans.aspx - source] | ||
+ | |||
+ | ==HomeWork== | ||
+ | |||
+ | Link to Logon.sql | ||
+ | |||
+ | http://iam.colum.edu/dd/gbrowser.php?file=/classsource/Class7/logon.sql | ||
+ | |||
+ | Try to create a use logon page that inserts a new user. |
Latest revision as of 00:06, 27 November 2012
Contents
Authentcation
Authentication Discuss Methods and problems
Methods
- Windows
- Passport
- Forms
The authentication method is set in the root web.config <xml> <authentication mode="Forms">
<forms loginUrl="logon.aspx" timeout="20" path="/" protection="All"> </forms>
</authentication> </xml>
the folder web.config
<xml> <location path="admin">
<system.web> <authorization> <deny users="?"/> </authorization> </system.web> </location>
</configuration> </xml>
We will be using a small section of the .net provider model.S
Logon Using the tables we made last week
You can use the Authorization Section of web.config to protect file or a directory. You can have more than one web.config in a project also. You can have one for each folder. The web.config setting inherit from the parent folder and most section can be overridden. Here is an example of a small web.config that protects a folder call admin with the .net authentication mechanism.
http://iam.colum.edu/dd/gbrowser.php?file=/classsource/Logon/Web.config
Main web.config add the following to the system.web section <xml> <system.web>
<authentication mode="Forms">
<forms loginUrl="classsource/logon/logon.aspx" name="adAuthCookie" timeout="20" path="/" protection="All"> </forms>
</authentication>
</system.web>
</xml>
Now any link to the admin folder
http://iam.colum.edu/dd/classsource/logon/admin/
should redirect to the logon page
Logon Page
http://iam.colum.edu/dd/classsource/Logon/Logon.aspx - source http://iam.colum.edu/dd/gbrowser.php?file=/classsource/Logon/Logon.aspx.cs
Add a New user page http://iam.colum.edu/dd/classsource/Logon/CreateAccount.aspx -source http://iam.colum.edu/dd/gbrowser.php?file=/classsource/Logon/CreateAccount.aspx.cs
Admin Page http://iam.colum.edu/dd/classsource/Logon/Admin/Default.aspx - source http://iam.colum.edu/dd/gbrowser.php?file=/classsource/Logon/Admin/Default.aspx.cs
Logon the Microsoft way
Create a new user account
To insert into a new user we need to to combine three steps.
- INSERT into the People Table
- Get the new PeopleID
- 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
Link to Logon.sql
http://iam.colum.edu/dd/gbrowser.php?file=/classsource/Class7/logon.sql
Try to create a use logon page that inserts a new user.