DD Class10

From IAMMediaWiki Create Change

Contents 1 Create a new user account 1.1 Stored Procedures 2 Transactions 2.1 Single SQL SProc 3 Stored Procedures 4 HomeWork

[edit] 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

 
INSERT INTO People 
(salutationID, firstName, lastName, logonName, passwd)
	VALUES
(1, 'jeff', 'meyers', 'jmeyers', 'monkey')

Check the new users PeopleID

 
SELECT PeopleID FROM People WHERE firstName = 'jeff' AND lastName='meyers' AND logonName='jmeyers'

results PeopleID = 1

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

 
INSERT INTO roles
( PeopleID, roleTypeID, RoleActive )
	VALUES
( 1, 1, 1)

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

[edit] 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

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

Stored Procedure (SProc) to INSERT into people

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

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

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

all one line is fine

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

GetPeopleID

 
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

Again we can run it like this

 
GetPeopleID 
	@firstName ='sprocTestFname', 
	@lastName='sprocTestLname',
	@logonName ='sprocTestLogonName'

[edit] 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.

[edit] Single SQL SProc

full InsertPersonAndRole

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

and the SQL statement to call the stored procedure

 
AddUserAndRole 
	@salutationID = 1,
	@firstName = 'fnTransTest' , 
	@lastName ='lnTransTest',
	@logonName ='lnTransTest',
	@passwd ='pwTransTest',
	@roleTypeID = 1

[edit] Stored Procedures

and now the ADO way

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

[edit] HomeWork

create diagram of your proposed web project