|
|
| Line 1: |
Line 1: |
| | [[Category:Data Design]] | | [[Category:Data Design]] |
| − |
| |
| − | review Bios and Logon
| |
| | | | |
| | ===Second Normal Form=== | | ===Second Normal Form=== |
| Line 142: |
Line 140: |
| | | | |
| | [[Image:ManyToMany.png]] | | [[Image:ManyToMany.png]] |
| − |
| |
| − | ==Home Work==
| |
| − |
| |
| − | *Create the tables necessary for user authentication
| |
| − | *[http://iam.colum.edu/dd/gbrowser.php?file=/classsource/Class7/logon.sql Logon example script] [[Image:LogonFullDiagram.png|thumb|center|Logon Script Diagram]]
| |
| − | **People
| |
| − | **Roles
| |
| − | **RoleTypes
| |
| − |
| |
| − | *Create a form that queries your tables (you should use a view to join the roles)
| |
| − | **If logon was successful show user roles
| |
| − | **If logon was not, do not show user roles
| |
| − |
| |
| − | *Think about Ideas for a final project
| |
| − |
| |
| − |
| |
| − | ==Logon==
| |
| − |
| |
| − | Insert into the People 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>
| |
| − |
| |
| − | Note that we need two INSERT statements to create a working account and role. Since these two statements
| |
| − | depend on each other and we will be left with an Person without a role or a role without a person if one
| |
| − | statement fails the entrire operation should be wrapped in a transaction. More on this next week...
| |
| − |
| |
| − | Now we have a user and that user has a role. We need to be able to read back the userName, passowrd, roleActive and roleTypeName in order to check if a user is valid.
| |
| − |
| |
| − | The easiest way to do this is with a view
| |
| − |
| |
| − | [[Image:Logon_vw.png]]
| |
| − |
| |
| − | <sql>
| |
| − | SELECT dbo.people.peopleID, dbo.people.firstName, dbo.people.lastName, dbo.people.logonName,
| |
| − | dbo.people.passwd, dbo.peopleSalutationTypes.salutationID,
| |
| − | dbo.peopleSalutationTypes.salutation, dbo.roles.roleActive, dbo.roleTypes.RoleTypeID,
| |
| − | dbo.roleTypes.RoleTypeName, dbo.roleTypes.RoleTypeHier,
| |
| − | dbo.roleTypes.roleTypeActive
| |
| − | FROM dbo.people
| |
| − | INNER JOIN
| |
| − | dbo.peopleSalutationTypes ON dbo.people.salutationID = dbo.peopleSalutationTypes.salutationID
| |
| − | INNER JOIN
| |
| − | dbo.roles ON dbo.people.peopleID = dbo.roles.PeopleID
| |
| − | INNER JOIN
| |
| − | dbo.roleTypes ON dbo.roles.RoleTypeID = dbo.roleTypes.RoleTypeID
| |
| − | </sql>
| |
| − |
| |
| − | Now we can use the view to test a logon SELECT statement
| |
| − | <sql>
| |
| − | SELECT peopleID, firstName, lastName, logonName, passwd, salutationID,
| |
| − | salutation, roleActive, RoleTypeID, RoleTypeName, RoleTypeHier,
| |
| − | roleTypeActive
| |
| − | FROM logon_vw
| |
| − | WHERE
| |
| − | logonName = 'jmeyers' and
| |
| − | passwd = 'monkey' and
| |
| − | roleActive = 1 and
| |
| − | roleTypeActive = 1
| |
| − | </sql>
| |
| − |
| |
| − | Now that we have the DataBase working correctly we can move on to the c#
| |
| − |
| |
| − |
| |
| − | ===Sessions===
| |
| − |
| |
| − | Every web application has a collection of objects called a session. Each browser is assigned a unique cookie that is used to identity the SessionID of the connection.
| |
| − |
| |
| − | The session collection can hold any object and remain until a session times out. A session times out when the web server does not receive a request from a client for longer than the timeout period (deafult 20 mins)
| |
| − |
| |
| − | session variables are set just like any other Collection. When you retrive an item form the collection you need to cast it back to it's original type (string casts are implict in c# so you really don't have to cast a string).
| |
| − |
| |
| − | <csharp>
| |
| − | Session["LogonName"] = "test";
| |
| − |
| |
| − | string strLogonName = (string)Session["LogonName"];
| |
| − | </csharp>
| |
| − |
| |
| − | ==Review ADO==
| |
| − |
| |
| − | {{Template:ADO.NET Diagram}}
| |
| − |
| |
| − | [[Template:ADO.NET Diagram]]
| |
| − |
| |
| − | ==Simple ADO Binding==
| |
| − |
| |
| − | <csharp>
| |
| − | //Connection string comes from web config
| |
| − | SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["IamConnectionJeff"].ConnectionString);
| |
| − |
| |
| − | string strSQL = "Select * from Course";
| |
| − |
| |
| − | SqlCommand dc = new SqlCommand(strSQL , conn);
| |
| − | SqlDataAdapter da = new SqlDataAdapter(dc);
| |
| − | DataSet ds = new DataSet();
| |
| − | DataTable dt = new DataTable();
| |
| − |
| |
| − | da.Fill(ds, "Course"); //Use the DataAdapter to fill the DataSet with a named Table
| |
| − | dt = ds.Tables["Course"]; //Retreive the named table from the DataSet
| |
| − |
| |
| − | gvTestOrig.DataSource = dt; //Set the DataTable as the source for the GridView noe AutoGenerateColumns = true
| |
| − | gvTestOrig.DataBind(); //Bind the data form the table to the GridView
| |
| − | </csharp>
| |
| − |
| |
| − | The above code binds to a control called gvTestOrig
| |
| − |
| |
| − | <csharp>
| |
| − | <asp:GridView ID="gvTestOrig" runat="server" AutoGenerateColumns="true" />
| |
| − | </csharp>
| |
| − |
| |
| − | http://iam.colum.edu/dd/classsource/class8/SimpleADO.aspx [http://iam.colum.edu/dd/gbrowser.php?file=/classsource/class8/SimpleADO.aspx - source]
| |
| − |
| |
| − | Same code but I moved some of the code into a function and split the declareation and initialization of some object to that they are scoped to the page not to Page_Load. This allows other functions on the page to use these objects.
| |
| − |
| |
| − | http://iam.colum.edu/dd/classsource/class8/ADOTest1.aspx [http://iam.colum.edu/dd/gbrowser.php?file=/classsource/class8/ADOTest1.aspx - source]
| |
| − |
| |
| − | ==Inserting Editing Deleteing With ADO==
| |
| − |
| |
| − | Add to a DataTable
| |
| − | <csharp>
| |
| − | DataTable dt = ds.Tables["Course"];
| |
| − | DataRow dr = dt.NewRow();
| |
| − | dr["CourseName"] = "Applcations Design";
| |
| − | dr["CourseNumber"] = "36-3601";
| |
| − | dt.Rows.Add(dr);
| |
| − | </csharp>
| |
| − |
| |
| − | Modify and DataTable
| |
| − | <csharp>
| |
| − | DataTable dt = ds.Tables["Course"];
| |
| − | DataRow dr = dt.Rows[3]; //Hack hard coded index
| |
| − | dr["CourseName"] = "Application Design";
| |
| − | dr["CourseNumber"] = "36-4601";
| |
| − | </csharp>
| |
| − |
| |
| − | Delete from a DataTable
| |
| − | <csharp>
| |
| − | DataTable dt = ds.Tables["Course"];
| |
| − | DataRow dr = dt.Rows[1]; //Hack hard coded index
| |
| − | dt.Rows.Remove(dr);
| |
| − | </csharp>
| |
| − |
| |
| − | There are better method to select which row to modify see the example below
| |
| − |
| |
| − | http://iam.colum.edu/dd/classsource/class8/ADOTest2AddModifyDelete.aspx [http://iam.colum.edu/dd/gbrowser.php?file=/classsource/class8/ADOTest2AddModifyDelete.aspx - source]
| |
| − |
| |
| − | notice that changes made to the DataSet are not synchronized back to the Data Provider.
| |
| − | The DataAdapter is able to sync these changes, remeber that the DataAdapter acts as a bridge between the
| |
| − | DataSet and the DataProvider.
| |
| − |
| |
| − | The DataAdapter can use the SqlStringBuilder class to create select, update, and delete statements. Once these statements are built the DataAdapter can update the DataProvider.
| |
| − |
| |
| − | <csharp>
| |
| − | //Uses an SqlBuilder to update the DataAdapter also displays the generated sql in a lable
| |
| − | public void CommitChanges()
| |
| − | {
| |
| − | //Get a stringBuilder from out DataAdapter
| |
| − | SqlCommandBuilder cb = new SqlCommandBuilder(da);
| |
| − | da.UpdateCommand = cb.GetUpdateCommand();
| |
| − | lblUpdateCommand.Text = da.UpdateCommand.CommandText; //Display the update Command in a Label
| |
| − | da.Update(ds.Tables["Course"]); //User tha DataAdpater to update the Data Provider
| |
| − | }
| |
| − | </csharp>
| |
| − |
| |
| − | http://iam.colum.edu/dd/classsource/class8/ADOTest3Commit.aspx
| |
| − | [http://iam.colum.edu/dd/gbrowser.php?file=/classsource/class8/ADOTest3Commit.aspx - source]
| |
| − |
| |
| − | ==Homework==
| |
| − |
| |
| − | 1 Build a page that demonstrates a single table update with ADO,
| |
| − |
| |
| − | 2 Write a proposal for final project the proposal should include
| |
| − |
| |
| − | Proposal Template: http://iam.colum.edu/janell/edu/wi/docs/proposal_wi.rtf
| |
| − |
| |
| − | Proposal Template (pdf): http://iam.colum.edu/janell/edu/wi/docs/proposal_wi.pdf
| |
| − |
| |
| − | *Purpose
| |
| − | *Objectives
| |
| − | *Audience
| |
| − |
| |
| − | *Website Scope
| |
| − | **Simple map of web site(can be on paper or hyperlinks)
| |
| − | **Number of web pages
| |
| − | **Function of web pages
| |
| − | *DataBase Design
| |
| − | **Mock up of tables and relations
| |
| − | **ERD on paper or diagram in sql
| |
Second Normal Form eliminates functional dependencies on a partial key by putting the fields in a separate table from those that are dependent on the whole key.
Third Normal Form eliminates functional dependencies on non-key fields by putting them in a separate table. At this stage, all non-key fields are dependent on the key, the whole key and nothing but the key. Must be in second normal form.
Look up tables. Are often used to define a many to many relation ship. These lookup tables often have a compound key.
This is a demonstration of a logon system where users can have multiple roles. There is a compound key used on the roles table. The compound key consists of two or more foreign keys. Each combination of foreign keys must be unique.
