Difference between revisions of "DD Class12"

esse quam videri
Jump to: navigation, search
(→‎Mail)
(→‎HomeWork)
 
(10 intermediate revisions by the same user not shown)
Line 1: Line 1:
 
[[Category:Data Design]]
 
[[Category:Data Design]]
  
−
==LINQ==
+
==Authentcation==
 +
Authentication
 +
Discuss Methods and problems
  
−
c# 3.0 got some new features and key words
+
Methods
  
−
'''var'''
+
* Windows
−
:is a implicitly typed local variable. it is strong typed you don't need to declare the type when you declare the variable.
+
* Passport
 +
* Forms
  
−
''Anonymous Types'''
+
The authentication method is set in the root web.config
−
:allows creation of structural types without declaring an name first http://msdn.microsoft.com/en-us/library/bb397696.aspx
+
<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
  
−
'''Lambda Expressions''' =>
+
http://iam.colum.edu/dd/classsource/class10/trans.aspx [http://iam.colum.edu/dd/gbrowser.php?file=/classsource/class10/trans.aspx -source]
−
: similar to anonymous methods. don't need to be declared first and don't need to specify return type http://msdn.microsoft.com/en-us/library/bb397687.aspx
 
  
−
new keywords http://msdn.microsoft.com/en-us/library/bb310804.aspx
+
Test page one with form
  
−
http://iam.colum.edu/dd/classsource/LINQBlog/LINQTest.aspx
+
===SQL transation===
  
−
http://msdn.microsoft.com/en-us/library/bb397933.aspx
+
This can all be done in shorter statement by putting the Select query in the second INSERT
  
−
http://iam.colum.edu/DD/classsource/Data/LINQ/LINQBinding.aspx
+
<sql>
 +
INSERT INTO People
 +
(salutationID, firstName, lastName, logonName, passwd)
 +
VALUES
 +
(1, 'jeffTest2', 'meyersTest2', 'jmeyersTest2', 'monkey')
  
−
http://iam.colum.edu/DD/classsource/Data/LINQ/CheeseLINQ2.aspx
+
GO
  
−
==Boyce-Codd Normal form==
+
INSERT INTO roles
−
*needs to be in 3NF
+
( PeopleID, roleTypeID, RoleActive )
−
http://en.wikipedia.org/wiki/Boyce-Codd_normal_form
+
(
 +
SELECT PeopleID, 1, 1 FROM People
 +
WHERE firstName = 'jeffTest2' AND lastName='meyersTest2'
 +
AND logonName='jmeyersTest2' )
  
−
==Fourth Normal Form==
+
</sql>
−
*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
+
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
  
−
==Fifth Normal Form==
+
/* Remove old trancation test so we can run it again */
−
*needs to be in 4NF
+
DELETE from Roles where PeopleID in (SELECT PeopleID FROM People
−
Fifth Normal Form
+
WHERE firstName = 'jeffTest2' AND lastName='meyersTest2'
−
:breaks out data redundancy that is not covered by any of the previous normal forms.
+
AND logonName='jmeyersTest2')
  
−
http://en.wikipedia.org/wiki/Fifth_normal_form
+
DELETE from People where firstName = 'jeffTest2' AND lastName='meyersTest2'
 +
AND logonName='jmeyersTest2'
  
−
More normal forms
+
GO
  
−
http://en.wikipedia.org/wiki/Database_normalization
+
/*INSERT for tranaction */
 +
INSERT INTO People
 +
(salutationID, firstName, lastName, logonName, passwd)
 +
VALUES
 +
(1, 'jeffTest2', 'meyersTest2', 'jmeyersTest2', 'monkey')
  
−
===Mappath===
+
GO
  
−
Server.Mappath
+
INSERT INTO roles
 +
( PeopleID, roleTypeID, RoleActive )
 +
(
 +
SELECT PeopleID, 1, 1 FROM People
 +
WHERE firstName = 'jeffTest2' AND lastName='meyersTest2'
 +
AND logonName='jmeyersTest2' )
  
−
upload permissions
+
COMMIT TRANSACTION
 +
</sql>
  
−
===Upload a File===
+
===Stored Procedures===
  
−
http uploader
+
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.
  
−
Add executionTimeout and maxRequestLength to the web.config so that large uploads will not fail.
+
Default Template
  
−
<xml>
+
<sql>
−
<configuration>
+
-- ================================================
−
    <system.web>
+
-- Template generated from Template Explorer using:
−
        <!--
+
-- Create Procedure (New Menu).SQL
−
        httpRuntime Attributes:
+
--
−
            executionTimeout="[seconds]" - time in seconds before request is automatically timed out
+
-- Use the Specify Values for Template Parameters
−
            maxRequestLength="[KBytes]" - KBytes size of maximum request length to accept
+
-- command (Ctrl-Shift-M) to fill in the parameter
−
            useFullyQualifiedRedirectUrl="[true|false]" - Fully qualifiy the URL for client redirects
+
-- values below.
−
            minFreeThreads="[count]" - minimum number of free thread to allow execution of new requests
+
--
−
            minLocalRequestFreeThreads="[count]" - minimum number of free thread to allow execution of new local requests
+
-- This block of comments will not be included in
−
            appRequestQueueLimit="[count]" - maximum number of requests queued for the application
+
-- the definition of the procedure.
−
            enableKernelOutputCache="[true|false]" - enable the http.sys cache on IIS6 and higher - default is true
+
-- ================================================
−
            enableVersionHeader="[true|false]" - outputs X-AspNet-Version header with each request
+
SET ANSI_NULLS ON
−
        -->
+
GO
−
        <httpRuntime
+
SET QUOTED_IDENTIFIER ON
−
            executionTimeout="1200"
+
GO
−
            maxRequestLength="65536"
+
-- =============================================
−
         />
+
-- Author: <Author,,Name>
−
    </system.web>
+
-- Create date: <Create Date,,>
−
</configuration>
+
-- Description: <Description,,>
−
</xml>  
+
-- =============================================
 +
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>
  
−
multipart form
+
Again we can run it like this
−
<csharp>
+
<sql>
−
<form enctype="multipart/form-data" runat="server">
+
GetPeopleID
−
<tr>
+
@firstName ='sprocTestFname',
−
  <td>Select file to upload:</td>
+
@lastName='sprocTestLname',
−
  <td>
+
@logonName ='sprocTestLogonName'
−
  <input id="myfile" type="file" runat="server"></td>
+
</sql>
−
</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
+
==Transactions==
  
−
<csharp>
+
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.
−
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
+
Commit
−
[[http://iam.colum.edu/oop/gbrowser.php?file=/classsource/class14/up.aspx up.aspx - source]]
+
:Actually commits tht changes
−
[[http://iam.colum.edu/oop/gbrowser.php?file=/classsource/class14/web.config web.config - source]]
 
  
−
===Modifications===
+
Rollback
−
Uniqie filename
+
:rolls back all changes and the database.
  
−
<csharp>
+
Transaction can also have scope.
−
// 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
+
===Single SQL SProc===
  
−
==Aspx Templates and Reuse==
+
full InsertPersonAndRole
  
−
The template page
+
<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
  
−
http://iam.colum.edu/dd/classsource/class11/UserControls/TemplateNoControls.aspx
 
−
[http://iam.colum.edu/dd/gbrowser.php?file=/classsource/class11/UserControls/TemplateNoControls.aspx - source]
 
  
−
===Include Files===
+
--INSERT into People
 +
INSERT into People
 +
(salutationID, firstName, lastName, logonName, passwd)
 +
values
 +
(@salutationID, @firstName, @lastName, @logonName, @passwd)
  
−
<csharp>
+
--Get PeopleID
−
<table border="1">
+
DECLARE @PeopleID int
−
<tr>
 
−
<td colspan ="3" align="center"><div align="center" class="Title">This is the header</div></td>
 
−
</tr>
 
−
<tr>
 
−
<td valign="top">
 
−
        <!-- #include virtual="incs/col1.aspx" -->
 
−
</td>
 
−
<td valign="top"><div align="center" class="SectionTitle">Main Column</div>
 
−
<p>Content Content Content</a>
 
−
    </td>
 
−
<td valign="top">
 
−
        <!-- #include virtual="incs/col3.aspx" -->
 
−
    </td>
 
−
</tr>
 
−
<tr>
 
−
<td colspan="3" align ="center">
 
−
        <!-- #include virtual="incs/foot.aspx" -->
 
−
</td>
 
−
</tr>
 
−
</table>
 
−
</csharp>
 
  
−
http://iam.colum.edu/dd/gbrowser.php?file=/classsource/class11/UserControls/incs/col1.aspx
+
Select @PeopleID = @@identity
  
−
http://iam.colum.edu/dd/gbrowser.php?file=/classsource/class11/UserControls/incs/col3.aspx
+
--INSERT into Roles
 +
INSERT into roles
 +
(PeopleID, RoleTypeID, RoleActive)
 +
values
 +
(@PeopleID, @roleTypeID, 1)
  
−
http://iam.colum.edu/dd/gbrowser.php?file=/classsource/class11/UserControls/incs/foot.aspx
+
--Commit Transaction
 +
ROLLBACK TRANSACTION
 +
        --COMMIT TRANSACTION
 +
END
 +
GO
  
−
===User Controls===
+
</sql>
  
−
User Controls are user defined controls that are reusable. They have and event model similar to the page class. They have an isolated scope. The page class can send data to user controls via user defined atributes.
+
and the SQL statement to call the stored procedure
  
−
User Controls must be registered via a Register Directive (this can also be done via web.config for an entire site)
+
<sql>
−
<csharp>
+
AddUserAndRole
−
<%@ Register TagPrefix="DataDesign" TagName="Col1" Src="controls/col1.ascx" %>
+
@salutationID = 1,
−
</csharp>
+
@firstName = 'fnTransTest' ,
 +
@lastName ='lnTransTest',
 +
@logonName ='lnTransTest',
 +
@passwd ='pwTransTest',
 +
@roleTypeID = 1
 +
</sql>
  
−
the control can then be used by adding the custom tag to the page
+
==Stored Procedures==
−
<csharp>
 
−
<DataDesign:Col1 id="ucCol1" runat="server" />
 
−
</csharp>
 
  
−
http://iam.colum.edu/dd/classsource/class11/UserControls/TemplateUserControls.aspx
+
Add edit example this will add or edit if the record exsits already
  
−
Controls
+
<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
  
−
http://iam.colum.edu/dd/gbrowser.php?file=/classsource/class11/UserControls/controls/col1.ascx
+
    -- 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
  
−
http://iam.colum.edu/dd/gbrowser.php?file=/classsource/class11/UserControls/controls/col3.ascx
+
select @PeopleID as PeopleID
 +
END
 +
END
  
−
http://iam.colum.edu/dd/gbrowser.php?file=/classsource/class11/UserControls/controls/foot.ascx
+
</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]
  
−
==Mail==
+
==HomeWork==
−
<csharp>
 
−
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);
 
−
</csharp>
 
  
−
in web config
+
Link to Logon.sql
−
<xml>
 
−
<system.net>
 
−
<mailSettings>
 
−
<smtp deliveryMethod="Network" from="jmeyers3@iit.edu">
 
−
<network host="email2.iit.edu" port="25" defaultCredentials="true"/>
 
−
</smtp>
 
−
</mailSettings>
 
−
</system.net>
 
−
</xml>
 
  
−
==HomeWORK==
+
http://iam.colum.edu/dd/gbrowser.php?file=/classsource/Class7/logon.sql
  
−
Final Proposal
+
Try to create a use logon page that inserts a new user.

Latest revision as of 00:06, 27 November 2012


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.

  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

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.

Retrieved from "https://imamp.colum.edu/mediawiki/index.php?title=DD_Class12&oldid=21257"