Difference between revisions of "DD Class9"

esse quam videri
Jump to: navigation, search
(Logon)
m (Text replacement - "syntaxhighlight lang="csharp" line="1" " to "syntaxhighlight lang="csharp"")
 
(32 intermediate revisions by the same user not shown)
Line 1: Line 1:
 
[[Category:Data Design]]
 
[[Category:Data Design]]
  
review Bios and Logon
+
==Repo Pattern==
  
===Second Normal Form===
+
Definition from P of EE http://martinfowler.com/eaaCatalog/repository.html
 +
MSDN [http://msdn.microsoft.com/en-us/library/ff649690.aspx The Repository Pattern]
  
In order to reach 2NF the table must first be in 1NF
+
The repository pattern is a nice way to abstract data fetching logic and easily separate concerns from different layers of your app. It allows of to quickly refactor and swap repos.
  
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.
+
First we can start with the cheese definition
  
'Remove Fields that are not dependent on the primary key'
+
<syntaxhighlight lang="csharp">
 +
public class Cheese
 +
    {
 +
       
 +
        public int CheeseID { get; set; }
 +
       
 +
        public string CheeseName { get; set; }
 +
       
 +
        public string CheeseDescription { get; set; }
 +
    }
 +
</syntaxhighlight>
 +
Then interface for the repo
 +
<syntaxhighlight lang="csharp">
 +
public interface IRepository<T>
 +
    {
 +
        T GetById(int id);
 +
        List<T> GetItems();
 +
        void Add(T entity);
 +
        void Remove(T entity);
 +
    }
 +
</syntaxhighlight>
 +
Now we want to expand the interface definition to cheeses
 +
<syntaxhighlight lang="csharp">
 +
/// <summary>
 +
    /// ICheeseRepo Changes the type of the Generic Repo To Cheese
 +
    /// </summary>
 +
    public interface ICheeseRepo : IRepository<Cheese>
 +
    {
 +
        List<Cheese> GetItems();
 +
        List<Cheese> GetCheeses();  //New Method To Get Cheeses
 +
        //May want to expand the definition to get cheese by name
 +
        //Cheese GetByName(string name)
 +
    }
 +
</syntaxhighlight>
 +
Finally the repo interface can then be implemented like this
 +
<syntaxhighlight lang="csharp">
 +
public class TestCheeseRepository : ICheeseRepo
 +
    {
  
 +
        //could grab these from the Application Object
 +
        private static List<Cheese> fakeCheeses =
 +
            new List<Cheese> { new Cheese { CheeseID=1, CheeseName="test1", CheeseDescription = "test desc 1" },
 +
                new Cheese { CheeseID=2, CheeseName="test2", CheeseDescription = "test desc 2" },
 +
                new Cheese { CheeseID=2, CheeseName="test3", CheeseDescription = "test desc 3" }
 +
            };
  
Not Normal
 
  
Adresses
+
        #region ICheeseRepo Members
{| class="wikitable" cellpadding="5" cellspacing="0"
+
        public List<Cheese> GetItems()
! CustNum !! FirstName !! LastName !! Address !! City !! State !! ZIP
+
        {
|-
+
            return GetCheeses();
| 1 || John || Doe || 12 Main Street || Sea Cliff || NY || 11579
+
        }
|-
 
| 2 || Alan || Johnson || 82 Evergreen Tr || Sea Cliff || NY || 11579
 
|-
 
| 3 || Beth || Thompson || 1912 NE 1st St  || Miami || FL  || 33157
 
|-
 
| 4 || Jacob  || Smith || 142 Irish Way || South Bend || IN || 46637
 
|-
 
| 5 || Sue || Ryan || 412 NE 1st St || Miami || FL  || 33157
 
|}
 
  
  
Normal
+
        public List<Cheese> GetCheeses()
 +
        {
 +
            return fakeCheeses.ToList();
 +
        }
  
StatesZips
+
        #endregion
{| class="wikitable" cellpadding="5" cellspacing="0"
 
! ZIP !! City !! State
 
|-
 
| 11579 || Sea Cliff || NY
 
|-
 
|33157  || Miami || FL
 
|-
 
|46637 || South Bend || IN
 
|}
 
  
Address
+
        #region IRepository<Cheese> Members
{| class="wikitable" cellpadding="5" cellspacing="0"
 
! CustNum !! FirstName !! LastName !! Address !! ZIP
 
|-
 
|1 || John || Doe || 12 Main Street || 11579
 
|-
 
|2 || Alan || Johnson || 82 Evergreen Tr || 11579
 
|-
 
|3 || Beth || Thompson || 1912 NE 1st St || 33157
 
|-
 
|4 || Jacob || Smith || 142 Irish Way || 46637
 
|-
 
|5 || Sue || Ryan || 412 NE 1st St || 33157
 
|}
 
  
===In class===
+
        public Cheese GetById(int id)
 +
        {
 +
            //linq statement to find cheese by ID
 +
            var currentCheese = from c in fakeCheeses where c.CheeseID == id select c;
 +
            return currentCheese.FirstOrDefault();
 +
        }
  
Normalize the books exmaple
+
        public void Add(Cheese entity)
 +
        {
 +
            fakeCheeses.Add(entity);
 +
        }
  
'''Books'''
+
        public void Remove(Cheese entity)
{| class="wikitable" cellpadding="5" cellspacing="0"
+
        {
!Author !! Title !! Pages !! Publisher !! PublisherURL !! Subject !! ISBN
+
            fakeCheeses.Remove(entity);
|-
+
        }
| Michael Allen Dymmoch || The Man Who Understood Cats || 256 || Avon Books || http://www.harpercollins.com/imprints/index.aspx?imprintid=517994 || Fiction Mystery || 0380722658
 
|-
 
| Joseph Cancellaro || Exploring Sound Design for Interactive Media || 272 || Thomson Delmar Learning || http://www.delmarlearning.com/ || Sound || 1401881025
 
|}
 
  
===Third Normal Form===
+
        #endregion
  
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.
+
        //added statement to find cheese by name
 +
        public Cheese GetByName(string name)
 +
        {
 +
            //linq statement to find cheese by name
 +
            var currentCheese = from c in fakeCheeses where c.CheeseName == name select c;
 +
            return currentCheese.FirstOrDefault();
 +
        }
 +
    }
 +
</syntaxhighlight>
  
Not normal
+
Link to the implememtation http://iam.colum.edu/datadesign/gbrowser.php?file=/App_code/Repo/CheeseRepo.cs
{| class="wikitable" cellpadding="5" cellspacing="0"
 
!Company !! City !! State !! ZIP
 
|-
 
| Acme Widgets || New York || NY || 10169
 
|-
 
| ABC Corporation || Miami || FL  || 33196
 
|-
 
| XYZ, Inc. || Columbia || MD || 21046
 
|}
 
  
Normal
+
Now to use the Repo
  
{| class="wikitable" cellpadding="5" cellspacing="0"
+
http://iam.colum.edu/datadesign/classsource/repo/Default.aspx
! KompanyID !! KompanyName !! Zip
 
|-
 
|1 || Acme Widgets || 10169
 
|-
 
|2 || ABC Corporation || 33196
 
|-
 
|3  || XYZ, Inc. || 21046
 
|}
 
  
 +
http://iam.colum.edu/datadesign/gbrowser.php?file=/classsource/repo/Default.aspx.cs
  
{| class="wikitable" cellpadding="5" cellspacing="0"
+
<syntaxhighlight lang="csharp">
! Zip !! CityID
+
ICheeseRepo db = new TestCheeseRepository();
|-
+
        gv1.DataSource = db.GetCheeses();
|10169 || 1
+
        gv1.DataBind();
|-
+
</syntaxhighlight>
|33196 || 2
 
|-
 
|21046 || 3
 
|}
 
  
  
{| class="wikitable" cellpadding="5" cellspacing="0"
 
! CityID !! City
 
|-
 
| 1 || New York
 
|-
 
| 2 || Miami
 
|-
 
| 3  || Columbia
 
|}
 
  
 +
You can add LINQ helpers to the class
 +
<syntaxhighlight lang="csharp">
 +
/// <summary>
 +
    /// Cheese Class for Our Repo
 +
    /// </summary>
 +
    [Table(Name = "Cheese")]
 +
    public class Cheese
 +
    {
 +
        [Column(IsPrimaryKey = true, IsDbGenerated = true, AutoSync = AutoSync.OnInsert)]
 +
        public int CheeseID { get; set; }
 +
        [Column]
 +
        public string CheeseName { get; set; }
 +
        [Column]
 +
        public string CheeseDescription { get; set; }
 +
    }
 +
</syntaxhighlight>
  
{| class="wikitable" cellpadding="5" cellspacing="0"
+
and then create a new cheese repo
! StateID !! State
 
|-
 
| 1 || NY
 
|-
 
| 2 || FL
 
|-
 
| 3 || MD
 
|}
 
 
 
http://home.earthlink.net/~billkent/Doc/simple5.htm.
 
 
 
==Many to Many Relationships==
 
 
 
Look up tables. Are often used to define a many to many relation ship. These lookup tables often have a [http://en.wikipedia.org/wiki/Compound_key compound key].
 
 
 
See the Student Courses example from the [[Data Relationships]] page.
 
 
 
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.
 
 
 
[[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>
+
and update your repo initialization
SELECT PeopleID from People WHERE firstName = 'jeff' and lastName='meyers' and logonName='jmeyers'
 
</sql>
 
  
results PeopleID = 1
+
<syntaxhighlight lang="csharp">
 +
ICheeseRepo db = new CheeseRepository(ConfigurationManager.ConnectionStrings["cheeseConnectionString"].ToString());
 +
        gv1.DataSource = db.GetCheeses();
 +
        gv1.DataBind();
 +
</syntaxhighlight>
  
Give the new user a Role by inserting the PeopleID and a roleID into the roles table
+
LINQ classes in MVC must use the entire namespace
 
+
<syntaxhighlight lang="csharp">
<sql>
+
/// <summary>
INSERT into roles
+
    /// Cheese Class for Our Repo
( PeopleID, roleTypeID, RoleActive )
+
    /// </summary>
values
+
    [System.Data.Linq.Mapping.Table(Name = "Cheese")]
( 1, 1, 1)
+
     public class Cheese
</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
+
         [System.Data.Linq.Mapping.Column(IsPrimaryKey = true, IsDbGenerated = true, AutoSync = AutoSync.OnInsert)]
        SqlCommandBuilder cb = new SqlCommandBuilder(da);
+
         public int CheeseID { get; set; }
         da.UpdateCommand = cb.GetUpdateCommand();
+
         [System.Data.Linq.Mapping.Column]
         lblUpdateCommand.Text = da.UpdateCommand.CommandText;   //Display the update Command in a Label
+
        public string CheeseName { get; set; }
         da.Update(ds.Tables["Course"]);         //User tha DataAdpater to update the Data Provider
+
         [System.Data.Linq.Mapping.Column]
 +
        public string CheeseDescription { get; set; }
 
     }
 
     }
</csharp>
+
</syntaxhighlight>
 
 
http://iam.colum.edu/dd/classsource/class8/ADOTest3Commit.aspx
 
[http://iam.colum.edu/dd/gbrowser.php?file=/classsource/class8/ADOTest3Commit.aspx - source]
 
  
 
==Homework==
 
==Homework==
  
1 Build a page that demonstrates a single table update with ADO,
+
Use the repo pattern to create a repo for you own class. The the class should map to a table in your database. The repo should be able to show items in you db, add items and remove items.  
 
 
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
+
Add the repo to your Web from site and your MVC site. The repose should be able to list items from the DB. Either the MVC site or the WebForms site should be able to add and delete items.
**Simple map of web site(can be on paper or hyperlinks)
+
I'll give 1 pt extra for updating items and 1 pt for adding/deleting in MVC and Web Forms.
**Number of web pages
 
**Function of web pages
 
*DataBase Design
 
**Mock up of tables and relations
 
**ERD on paper or diagram in sql
 

Latest revision as of 03:20, 9 February 2016


Repo Pattern

Definition from P of EE http://martinfowler.com/eaaCatalog/repository.html MSDN The Repository Pattern

The repository pattern is a nice way to abstract data fetching logic and easily separate concerns from different layers of your app. It allows of to quickly refactor and swap repos.

First we can start with the cheese definition

 public class Cheese
    {
        
        public int CheeseID { get; set; }
        
        public string CheeseName { get; set; }
        
        public string CheeseDescription { get; set; }
    }

Then interface for the repo

public interface IRepository<T>
    {
        T GetById(int id);
        List<T> GetItems();
        void Add(T entity);
        void Remove(T entity);
    }

Now we want to expand the interface definition to cheeses

/// <summary>
    /// ICheeseRepo Changes the type of the Generic Repo To Cheese
    /// </summary>
    public interface ICheeseRepo : IRepository<Cheese>
    {
        List<Cheese> GetItems();
        List<Cheese> GetCheeses();  //New Method To Get Cheeses
        //May want to expand the definition to get cheese by name
        //Cheese GetByName(string name)
    }

Finally the repo interface can then be implemented like this

public class TestCheeseRepository : ICheeseRepo
    {

        //could grab these from the Application Object
        private static List<Cheese> fakeCheeses =
            new List<Cheese> { new Cheese { CheeseID=1, CheeseName="test1", CheeseDescription = "test desc 1" },
                new Cheese { CheeseID=2, CheeseName="test2", CheeseDescription = "test desc 2" },
                new Cheese { CheeseID=2, CheeseName="test3", CheeseDescription = "test desc 3" }
            };


        #region ICheeseRepo Members
        public List<Cheese> GetItems()
        {
            return GetCheeses();
        }


        public List<Cheese> GetCheeses()
        {
            return fakeCheeses.ToList();
        }

        #endregion

        #region IRepository<Cheese> Members

        public Cheese GetById(int id)
        {
            //linq statement to find cheese by ID
            var currentCheese = from c in fakeCheeses where c.CheeseID == id select c;
            return currentCheese.FirstOrDefault();
        }

        public void Add(Cheese entity)
        {
            fakeCheeses.Add(entity);
        }

        public void Remove(Cheese entity)
        {
            fakeCheeses.Remove(entity);
        }

        #endregion

        //added statement to find cheese by name
        public Cheese GetByName(string name)
        {
            //linq statement to find cheese by name
            var currentCheese = from c in fakeCheeses where c.CheeseName == name select c;
            return currentCheese.FirstOrDefault();
        }
    }

Link to the implememtation http://iam.colum.edu/datadesign/gbrowser.php?file=/App_code/Repo/CheeseRepo.cs

Now to use the Repo

http://iam.colum.edu/datadesign/classsource/repo/Default.aspx

http://iam.colum.edu/datadesign/gbrowser.php?file=/classsource/repo/Default.aspx.cs

ICheeseRepo db = new TestCheeseRepository();
        gv1.DataSource = db.GetCheeses();
        gv1.DataBind();


You can add LINQ helpers to the class

/// <summary>
    /// Cheese Class for Our Repo
    /// </summary>
    [Table(Name = "Cheese")]
    public class Cheese
    {
        [Column(IsPrimaryKey = true, IsDbGenerated = true, AutoSync = AutoSync.OnInsert)]
        public int CheeseID { get; set; }
        [Column]
        public string CheeseName { get; set; }
        [Column]
        public string CheeseDescription { get; set; }
    }

and then create a new cheese repo

and update your repo initialization

ICheeseRepo db = new CheeseRepository(ConfigurationManager.ConnectionStrings["cheeseConnectionString"].ToString()); 
        gv1.DataSource = db.GetCheeses();
        gv1.DataBind();

LINQ classes in MVC must use the entire namespace

/// <summary>
    /// Cheese Class for Our Repo
    /// </summary>
    [System.Data.Linq.Mapping.Table(Name = "Cheese")]
    public class Cheese
    {
        [System.Data.Linq.Mapping.Column(IsPrimaryKey = true, IsDbGenerated = true, AutoSync = AutoSync.OnInsert)]
        public int CheeseID { get; set; }
        [System.Data.Linq.Mapping.Column]
        public string CheeseName { get; set; }
        [System.Data.Linq.Mapping.Column]
        public string CheeseDescription { get; set; }
    }

Homework

Use the repo pattern to create a repo for you own class. The the class should map to a table in your database. The repo should be able to show items in you db, add items and remove items.

Add the repo to your Web from site and your MVC site. The repose should be able to list items from the DB. Either the MVC site or the WebForms site should be able to add and delete items. I'll give 1 pt extra for updating items and 1 pt for adding/deleting in MVC and Web Forms.