Difference between revisions of "DD Class9"

esse quam videri
Jump to: navigation, search
(→‎DataReader)
m (Text replacement - "syntaxhighlight lang="csharp" line="1" " to "syntaxhighlight lang="csharp"")
 
(9 intermediate revisions by the same user not shown)
Line 1: Line 1:
 
[[Category:Data Design]]
 
[[Category:Data Design]]
  
−
==Primary Keys==
+
==Repo Pattern==
−
The candidate key selected as being most important for identifying a body of information (an entity, object or record).
 
  
−
==Normalization==
+
Definition from P of EE http://martinfowler.com/eaaCatalog/repository.html
−
[http://en.wikipedia.org/wiki/Database_normalization Normal Forms]
+
MSDN [http://msdn.microsoft.com/en-us/library/ff649690.aspx The Repository Pattern]
  
−
'''First Normal'''
+
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.
−
:Form eliminates repeating groups by putting each into a separate table and connecting them with a one-to-many relationship.
 
−
:
 
  
−
[[Data Relationships]]
+
First we can start with the cheese definition
  
−
Not Following First Normal Form Repeating Groups
+
<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
 +
    {
  
−
Blog1
+
        //could grab these from the Application Object
−
{| class="wikitable" cellpadding="5" cellspacing="0"
+
        private static List<Cheese> fakeCheeses =
−
!BlogID !!  BlogText !! recCreationDate !! Mood
+
            new List<Cheese> { new Cheese { CheeseID=1, CheeseName="test1", CheeseDescription = "test desc 1" },
−
|-
+
                new Cheese { CheeseID=2, CheeseName="test2", CheeseDescription = "test desc 2" },
−
|1 || Blog1 || 03/30/03 || Happy
+
                new Cheese { CheeseID=2, CheeseName="test3", CheeseDescription = "test desc 3" }
−
|-
+
            };
−
|2 || Blog2 || 03/30/03 || Happy
 
−
|-
 
−
|3 || Blog3 || 03/30/03 || Sad
 
−
|-
 
−
|4 || Blog4 || 03/30/03 || Happy
 
−
|-
 
−
|5 || Blog4 || 03/30/03 || Mad
 
−
|}
 
  
−
Tables that Follow First normal form
 
  
−
Blog2
+
        #region ICheeseRepo Members
−
{| class="wikitable" cellpadding="5" cellspacing="0"
+
        public List<Cheese> GetItems()
−
! BlogID !! BlogText !! recCreationDate !! MoodID
+
        {
−
|-
+
            return GetCheeses();
−
|1 || Blog1 || 03/30/03 || 1
+
        }
−
|-
 
−
|2 || Blog2 || 03/30/03 || 1
 
−
|-
 
−
|3 || Blog3 || 03/30/03 || 2
 
−
|-
 
−
|4 || Blog4 || 03/30/03 || 1
 
−
|-
 
−
|5 || Blog4 || 03/30/03 || 3
 
−
|}
 
−
Mood
 
−
{| class="wikitable" cellpadding="5" cellspacing="0"
 
−
!MoodID !! MoodName
 
−
|-
 
−
|1 || Happy
 
−
|-
 
−
|2 || Sad
 
−
|-
 
−
|3 || Mad
 
−
|-
 
−
|4 || Afraid
 
−
|}
 
  
−
1NF also
 
−
:Removes multiple column with the same type of data
 
  
−
Books Not Normal
+
        public List<Cheese> GetCheeses()
 +
        {
 +
            return fakeCheeses.ToList();
 +
        }
  
−
'''Books'''
+
        #endregion
−
{| class="wikitable" cellpadding="5" cellspacing="0"
 
−
!Author !! Title_01 !! Pages_01 !! Title_02 !! Pages_02 !! Title_03 !! Pages_03
 
−
|-
 
−
| Michael Allen Dymmoch || The Man Who Understood Cats || 256 || White Tiger || 320 || ||
 
−
|-
 
−
| Joseph Cancellaro || Exploring Sound Design for Interactive Media || 272 || || || ||
 
−
|}
 
  
−
In Class Build Blogs Table and Normalize Books Table
+
        #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);
 +
        }
  
−
http://en.wikipedia.org/wiki/First_normal_form
+
        public void Remove(Cheese entity)
 +
        {
 +
            fakeCheeses.Remove(entity);
 +
        }
  
−
==ERD==
+
        #endregion
  
−
http://en.wikipedia.org/wiki/Entity-relationship_model
+
        //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>
  
−
tools
+
Link to the implememtation http://iam.colum.edu/datadesign/gbrowser.php?file=/App_code/Repo/CheeseRepo.cs
−
*http://staruml.sourceforge.net/en/ free and open source
 
−
*http://www.visual-paradigm.com/product/vpuml/ proprietary free community edition
 
  
−
==Review First Normal Form==
+
Now to use the Repo
  
−
Let build the example for the homework.
+
http://iam.colum.edu/datadesign/classsource/repo/Default.aspx
  
−
==Build Interface to GameDB==
+
http://iam.colum.edu/datadesign/gbrowser.php?file=/classsource/repo/Default.aspx.cs
  
−
'''Games'''
+
<syntaxhighlight lang="csharp">
−
{| class="wikitable" cellpadding="5" cellspacing="0"
+
ICheeseRepo db = new TestCheeseRepository();
−
!GameTitle !!  GameGenre !! DeveloperName !! Platform(s) !! Year !! DeveloperWebsite !! GameWebsite
+
        gv1.DataSource = db.GetCheeses();
−
|-
+
        gv1.DataBind();
−
| Quake1 || FPS || id || Dos || 1996 || http://www.idsoftware.com/ || http://www.idsoftware.com/games/quake/quake/
+
</syntaxhighlight>
−
|-
 
−
| 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?
 
  
−
Build Views
 
  
−
==Nesting Data Bound Controls==
+
You can add LINQ helpers to the class
−
nesting controls
+
<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>
  
−
Datasource Controls can probably handle up to 70% of your data binding needs but they do have drawbacks. Consider the cheese database which uses an SQL view to join Cheese with Region, Consistency, and MilkType. Of course SQL view don't support updating and can lead to some bad data binding. we can fix a few of these problem with stored procedures.
+
and then create a new cheese repo
  
−
Simple filter Demo in class
+
and update your repo initialization
  
−
GridView Details View Master Child Relationship
+
<syntaxhighlight lang="csharp">
 +
ICheeseRepo db = new CheeseRepository(ConfigurationManager.ConnectionStrings["cheeseConnectionString"].ToString());
 +
        gv1.DataSource = db.GetCheeses();
 +
        gv1.DataBind();
 +
</syntaxhighlight>
  
−
 
+
LINQ classes in MVC must use the entire namespace
−
http://iam.colum.edu/dd/classsource/data/CheeseDataBindingFull.aspx
+
<syntaxhighlight lang="csharp">
−
[http://iam.colum.edu/dd/gbrowser.php?file=/classsource/Data/CheeseDataBindingFull1.aspx source]
+
/// <summary>
−
 
+
    /// Cheese Class for Our Repo
−
==Stored Procedures==
+
    /// </summary>
−
 
+
    [System.Data.Linq.Mapping.Table(Name = "Cheese")]
−
http://iam.colum.edu/dd/classsource/data/sproc/sprocADO.aspx
+
    public class Cheese
−
 
+
    {
−
==Cheese browser assignment==
+
        [System.Data.Linq.Mapping.Column(IsPrimaryKey = true, IsDbGenerated = true, AutoSync = AutoSync.OnInsert)]
−
 
+
        public int CheeseID { get; set; }
−
Make a creative cheese browser from that tables in you db. Use a view to join that data from multiple tables..
+
        [System.Data.Linq.Mapping.Column]
−
 
+
        public string CheeseName { get; set; }
−
Here's and example of a simple cheese shop browser app.
+
        [System.Data.Linq.Mapping.Column]
−
 
+
        public string CheeseDescription { get; set; }
−
http://iam.colum.edu/dd/classsource/class8/CheeseShop/CheeseShop1.aspx
+
    }
 +
</syntaxhighlight>
  
 
==Homework==
 
==Homework==
−
Make a creative cheese browser from that tables in you db. Use a view to join that data from multiple tables..
 
−
 
−
 
−
==Examples==
 
−
http://iam.colum.edu/dd/classsource/Data/GridAndDetails.aspx
 
−
 
−
http://iam.colum.edu/dd/classsource/Data/GridDataBound.aspx
 
−
 
−
http://iam.colum.edu/dd/classsource/Data/GridDetails.aspx
 
−
 
−
http://iam.colum.edu/dd/classsource/Data/GridEdit.aspx
 
−
 
−
http://iam.colum.edu/dd/classsource/Data/GridSelect.aspx
 
  
−
http://iam.colum.edu/dd/classsource/Data/GridSelectTheme.aspx
+
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.  
  
−
http://iam.colum.edu/dd/classsource/Data/GridTemplate.aspx
+
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"