Difference between revisions of "DD Class9"

esse quam videri
Jump to: navigation, search
m (Text replacement - "syntaxhighlight lang="csharp" line="1" " to "syntaxhighlight lang="csharp"")
 
(19 intermediate revisions by the same user not shown)
Line 1: Line 1:
 
[[Category:Data Design]]
 
[[Category:Data Design]]
  
−
==Review First Normal Form==
+
==Repo Pattern==
  
−
Let build the example for the homework.
+
Definition from P of EE http://martinfowler.com/eaaCatalog/repository.html
 +
MSDN [http://msdn.microsoft.com/en-us/library/ff649690.aspx The Repository Pattern]
  
−
==Build Interface to GameDB==
+
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.
  
−
'''Games'''
+
First we can start with the cheese definition
−
{| class="wikitable" cellpadding="5" cellspacing="0"
 
−
!GameTitle !!  GameGenre !! DeveloperName !! Platform(s) !! Year !! DeveloperWebsite !! GameWebsite
 
−
|-
 
−
| Quake1 || FPS || id || Dos || 1996 || http://www.idsoftware.com/ || http://www.idsoftware.com/games/quake/quake/
 
−
|-
 
−
| Diablo|| RPG|| Blizzard || Windows 95|| 1996 || http://www.blizzard.com/  || http://www.blizzard.com/diablo/
 
−
|-
 
−
| SimCity || Sim || Interplay || Dos || 1993 || http://www.interplay.com/  || http://www.maxis.com/
 
−
|}
 
  
−
Tables Games, Developers, Websites, Platforms?
+
<syntaxhighlight lang="csharp">
−
 
+
public class Cheese
−
Build Views
 
−
 
 
−
 
 
−
==DataReader==
 
−
 
 
−
*DataReader http://iam.colum.edu/dd/classsource/ado/dataReader.aspx [http://iam.colum.edu/dd/gbrowser.php?file=/classsource/ado/dataReader.aspx source]
 
−
 
 
−
 
 
−
 
 
−
1 Connection Object
 
−
<csharp>
 
−
//Connection string comes from web config
 
−
SqlConnection objConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["cheeseConnectionString"].ConnectionString);           
 
−
</csharp>
 
−
 
 
−
web.config connection strings
 
−
 
 
−
<xml>
 
−
<configuration>
 
−
 
−
              <connectionStrings>
 
−
 
−
  <add name="cheeseConnectionString" connectionString="Data Source=iamdb;Initial Catalog=cheese;User ID=student;Password=Student" providerName="System.Data.SqlClient"/>
 
−
      </connectionStrings>
 
−
</xml>
 
−
 
 
−
2 Command Object
 
−
<csharp>string strSQL = "SELECT CheeseName,CheeseDescription FROM cheese";
 
−
   
 
−
    string strResultsHolder = "";
 
−
   
 
−
    SqlCommand objCommand = new SqlCommand(strSQL, objConnection);
 
−
</csharp>
 
−
 
 
−
3 Reader and Reading
 
−
<csharp>
 
−
SqlDataReader objDataReader = null;                            //Reader to read through the result
 
−
    try
 
 
     {
 
     {
−
         objConnection.Open();                                      //Open Connection
+
          
−
         objDataReader = objCommand.ExecuteReader();
+
         public int CheeseID { get; set; }
−
 
+
          
−
         while (objDataReader.Read() == true)
+
         public string CheeseName { get; set; }
−
         {
+
       
−
            strResultsHolder += String.Format("<b>{0}</b>:{1}<br /><br />",
+
        public string CheeseDescription { get; set; }
−
                objDataReader["CheeseName"], objDataReader["CheeseDescription"]);
 
−
        }
 
 
     }
 
     }
−
    catch (Exception e)
+
</syntaxhighlight>
 +
Then interface for the repo
 +
<syntaxhighlight lang="csharp">
 +
public interface IRepository<T>
 
     {
 
     {
−
         lblErr.Text = "Connection failed to open successfully.<br/>";
+
         T GetById(int id);
−
         lblErr.Text += e.ToString();
+
        List<T> GetItems();
 +
        void Add(T entity);
 +
         void Remove(T entity);
 
     }
 
     }
−
     finally
+
</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>
 
     {
 
     {
−
         objDataReader.Close();     //Close Reader
+
         List<Cheese> GetItems();
−
         objConnection.Close();     //Close Connection
+
         List<Cheese> GetCheeses(); //New Method To Get Cheeses
−
 
+
        //May want to expand the definition to get cheese by name
 +
        //Cheese GetByName(string name)
 
     }
 
     }
−
    //Write results
+
</syntaxhighlight>
−
    divListCheese.InnerHtml = strResultsHolder;
+
Finally the repo interface can then be implemented like this
−
}
+
<syntaxhighlight lang="csharp">
 +
public class TestCheeseRepository : ICheeseRepo
 +
    {
  
−
</csharp>
+
        //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" }
 +
            };
  
  
−
==Events and Commands==
+
        #region ICheeseRepo Members
 +
        public List<Cheese> GetItems()
 +
        {
 +
            return GetCheeses();
 +
        }
  
−
{{ASPX csharp commands}}
 
  
−
==More Normal Forms==
+
        public List<Cheese> GetCheeses()
 +
        {
 +
            return fakeCheeses.ToList();
 +
        }
  
−
===Second Normal Form===
+
        #endregion
  
−
In order to reach 2NF the table must first be in 1NF
+
        #region IRepository<Cheese> Members
  
−
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.
+
        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();
 +
        }
  
−
'Remove Fields that are not dependent on the primary key'
+
        public void Add(Cheese entity)
 +
        {
 +
            fakeCheeses.Add(entity);
 +
        }
  
 +
        public void Remove(Cheese entity)
 +
        {
 +
            fakeCheeses.Remove(entity);
 +
        }
  
−
Not Normal
+
        #endregion
  
−
Adresses
+
        //added statement to find cheese by name
−
{| class="wikitable" cellpadding="5" cellspacing="0"
+
        public Cheese GetByName(string name)
−
! CustNum !! FirstName !! LastName !! Address !! City !! State !! ZIP
+
        {
−
|-
+
            //linq statement to find cheese by name
−
| 1 || John || Doe || 12 Main Street || Sea Cliff || NY || 11579
+
            var currentCheese = from c in fakeCheeses where c.CheeseName == name select c;
−
|-
+
            return currentCheese.FirstOrDefault();
−
| 2 || Alan || Johnson || 82 Evergreen Tr || Sea Cliff || NY || 11579
+
        }
−
|-
+
    }
−
| 3 || Beth || Thompson || 1912 NE 1st St  || Miami || FL  || 33157
+
</syntaxhighlight>
−
|-
 
−
| 4 || Jacob  || Smith || 142 Irish Way || South Bend || IN || 46637
 
−
|-
 
−
| 5 || Sue || Ryan || 412 NE 1st St || Miami || FL  || 33157
 
−
|}
 
  
 +
Link to the implememtation http://iam.colum.edu/datadesign/gbrowser.php?file=/App_code/Repo/CheeseRepo.cs
  
−
Normal
+
Now to use the Repo
  
−
StatesZips
+
http://iam.colum.edu/datadesign/classsource/repo/Default.aspx
−
{| class="wikitable" cellpadding="5" cellspacing="0"
 
−
! ZIP !! City !! State
 
−
|-
 
−
| 11579 || Sea Cliff || NY
 
−
|-
 
−
|33157  || Miami || FL
 
−
|-
 
−
|46637 || South Bend || IN
 
−
|}
 
  
−
Address
+
http://iam.colum.edu/datadesign/gbrowser.php?file=/classsource/repo/Default.aspx.cs
−
{| 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===
+
<syntaxhighlight lang="csharp">
 +
ICheeseRepo db = new TestCheeseRepository();
 +
        gv1.DataSource = db.GetCheeses();
 +
        gv1.DataBind();
 +
</syntaxhighlight>
  
−
Normalize the books exmaple
 
  
−
'''Books'''
 
−
{| class="wikitable" cellpadding="5" cellspacing="0"
 
−
!Author !! Title !! Pages !! Publisher !! PublisherURL !! Subject !! ISBN
 
−
|-
 
−
| 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===
+
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>
  
−
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.
+
and then create a new cheese repo
  
−
Not normal
+
and update your repo initialization
−
{| 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
+
<syntaxhighlight lang="csharp">
 +
ICheeseRepo db = new CheeseRepository(ConfigurationManager.ConnectionStrings["cheeseConnectionString"].ToString());
 +
        gv1.DataSource = db.GetCheeses();
 +
        gv1.DataBind();
 +
</syntaxhighlight>
  
−
{| class="wikitable" cellpadding="5" cellspacing="0"
+
LINQ classes in MVC must use the entire namespace
−
! KompanyID !! KompanyName !! Zip
+
<syntaxhighlight lang="csharp">
−
|-
+
/// <summary>
−
|1 || Acme Widgets || 10169
+
    /// Cheese Class for Our Repo
−
|-
+
    /// </summary>
−
|2 || ABC Corporation || 33196
+
    [System.Data.Linq.Mapping.Table(Name = "Cheese")]
−
|-
+
    public class Cheese
−
|3  || XYZ, Inc. || 21046
+
    {
−
|}
+
        [System.Data.Linq.Mapping.Column(IsPrimaryKey = true, IsDbGenerated = true, AutoSync = AutoSync.OnInsert)]
−
 
+
        public int CheeseID { get; set; }
−
 
+
        [System.Data.Linq.Mapping.Column]
−
{| class="wikitable" cellpadding="5" cellspacing="0"
+
        public string CheeseName { get; set; }
−
! Zip !! CityID
+
        [System.Data.Linq.Mapping.Column]
−
|-
+
        public string CheeseDescription { get; set; }
−
|10169 || 1
+
    }
−
|-
+
</syntaxhighlight>
−
|33196 || 2
 
−
|-
 
−
|21046 || 3
 
−
|}
 
−
 
 
−
 
 
−
{| class="wikitable" cellpadding="5" cellspacing="0"
 
−
! CityID !! City
 
−
|-
 
−
| 1 || New York
 
−
|-
 
−
| 2 || Miami
 
−
|-
 
−
| 3  || Columbia
 
−
|}
 
−
 
 
−
 
 
−
{| class="wikitable" cellpadding="5" cellspacing="0"
 
−
! 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]]
 
−
 
 
−
===Lookup Table===
 
−
 
 
−
Here the BlogTags Tbale uses a compound key (two primary keys) to join two other tables. The Blog table is joined to the Tag table where each person can add a tag. This is a one to many one to many relationship. Person can a tag to each blog. This is often referred to as a look up table
 
−
 
 
−
[[Image:DDBlogLookupTable.png]]
 
−
 
 
−
==HomeWork==
 
  
−
Blog project can be done alone or XP style with one partner.
+
==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.
  
−
[[DD Blog Project]]
+
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.

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.

Retrieved from "http://imamp.colum.edu/mediawiki/index.php?title=DD_Class9&oldid=22482"