DD Class9
Contents
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
<csharp>
public class Cheese { public int CheeseID { get; set; } public string CheeseName { get; set; } public string CheeseDescription { get; set; } }
</csharp> Then interface for the repo <csharp> public interface IRepository<T>
{ T GetById(int id); List<T> GetItems(); void Add(T entity); void Remove(T entity); }
</csharp> Now we want to expand the interface definition to cheeses <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) }
</csharp> Finally the repo interface can then be implemented like this <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" } };
#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(); } }
</csharp>
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
<csharp> ICheeseRepo db = new TestCheeseRepository();
gv1.DataSource = db.GetCheeses(); gv1.DataBind();
</csharp>
You can add LINQ helpers to the class <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; } }
</csharp>
and then create a new cheese repo
and update your repo initialization
<csharp> ICheeseRepo db = new CheeseRepository(ConfigurationManager.ConnectionStrings["cheeseConnectionString"].ToString());
gv1.DataSource = db.GetCheeses(); gv1.DataBind();
</csharp>
Primary Keys
The candidate key selected as being most important for identifying a body of information (an entity, object or record).
Normalization
First Normal
- Form eliminates repeating groups by putting each into a separate table and connecting them with a one-to-many relationship.
Not Following First Normal Form Repeating Groups
Blog1
BlogID | BlogText | recCreationDate | Mood |
---|---|---|---|
1 | Blog1 | 03/30/03 | Happy |
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
BlogID | BlogText | recCreationDate | MoodID |
---|---|---|---|
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
MoodID | MoodName |
---|---|
1 | Happy |
2 | Sad |
3 | Mad |
4 | Afraid |
1NF also
- Removes multiple column with the same type of data
Books Not Normal
Books
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
http://en.wikipedia.org/wiki/First_normal_form
ERD
http://en.wikipedia.org/wiki/Entity-relationship_model
tools
- http://staruml.sourceforge.net/en/ free and open source
- http://www.visual-paradigm.com/product/vpuml/ proprietary free community edition
Views
UserTest
UserID | UserName | LastLogon |
---|---|---|
1 | jmeyers | 3/30/03 |
2 | bgates | 4/1/03 |
3 | sjobs | 4/2/03 |
4 | ltorvalds | 4/3/03 |
EmailTest
EmailID | UserID | EmailAddress | Active | displayEmail |
---|---|---|---|---|
1 | 1 | jeff@interactive.colum.edu | 1 | 0 |
2 | 1 | only_a_test@hotmail.com | 0 | 0 |
3 | 2 | bgates@microsoft.com | 1 | 0 |
[INNER] JOIN
The INNER JOIN returns all rows from both tables where there is a match. If there are rows in User that do not have matches in Email, those rows will not be listed.
-- ANSI Style <sql> SELECT u.UserID, u.UserName, u.LastLogon, e.EmailAddress, e.active, e.displayEmail FROM UserTest u JOIN EmailTest e ON e.UserID = u.UserID </sql>
--Theta style <sql> SELECT u.UserID, u.UserName, u.LastLogon, e.EmailAddress, e.active, e.displayEmail FROM UserTest u, EmailTest e WHERE e.UserID = u.UserID </sql>
results
UserID UserName LastLogon EmailAddress active displayEmail ----------- ------------------- ----------------------------- -------------------------------- ------ ------------ 1 jmeyers 2003-03-30 00:00:00.000 jeff@interactive.colum.edu 1 0 1 jmeyers 2003-03-30 00:00:00.000 only_a_test@hotmail.com 0 0 2 bgates 2003-04-01 00:00:00.000 bgates@microsoft.com 1 0 (3 row(s) affected)
LEFT [OUTER] JOIN
The LEFT JOIN returns all the rows from the first table (User), even if there are no matches in the second table (Email). If there are rows in User that do not have matches in Email, those rows also will be listed.
<sql> SELECT u.UserID, u.UserName, u.LastLogon, e.EmailAddress, e.active, e.displayEmail FROM UserTest u LEFT JOIN EmailTest e ON e.UserID = u.UserID </sql>
UserID UserName LastLogon EmailAddress active displayEmail ----------- ------------ ------------------------- ---------------------------- ------ ------------ 1 jmeyers 2003-03-30 00:00:00.000 jeff@interactive.colum.edu 1 0 1 jmeyers 2003-03-30 00:00:00.000 only_a_test@hotmail.com 0 0 2 bgates 2003-04-01 00:00:00.000 bgates@microsoft.com 1 0 3 sjobs 2003-04-02 00:00:00.000 NULL NULL NULL 4 ltorvalds 2003-04-03 00:00:00.000 NULL NULL NULL (5 row(s) affected)
RIGHT [OUTER] JOIN
The RIGHT JOIN returns all the rows from the second table (Email), even if there are no matches in the first table (User). If there had been any rows in Email that did not have matches in User, those rows also would have been listed.
<sql> SELECT u.UserID, u.UserName, u.LastLogon, e.EmailAddress, e.active, e.displayEmail FROM UserTest u RIGHT JOIN EmailTest e ON e.UserID = u.UserID </sql>
results
UserID UserName LastLogon EmailAddress active displayEmail ----------- ------------------- ----------------------------- -------------------------------- ------ ------------ 1 jmeyers 2003-03-30 00:00:00.000 jeff@interactive.colum.edu 1 0 1 jmeyers 2003-03-30 00:00:00.000 only_a_test@hotmail.com 0 0 2 bgates 2003-04-01 00:00:00.000 bgates@microsoft.com 1 0 (3 row(s) affected)
Making views in enterprise manager is easy. Just add the tables you want to join and click. It does all the typing for you.
Review First Normal Form
Let build the example for the homework.
Build Interface to GameDB
Games
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?
Build Views
Nesting Data Bound Controls
nesting controls
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.
Simple filter Demo in class
GridView Details View Master Child Relationship
http://iam.colum.edu/dd/classsource/data/CheeseDataBindingFull.aspx
source
Stored Procedures
http://iam.colum.edu/dd/classsource/data/sproc/sprocADO.aspx
Cheese browser assignment
Make a creative cheese browser from that tables in you db. Use a view to join that data from multiple tables..
Here's and example of a simple cheese shop browser app.
http://iam.colum.edu/dd/classsource/class8/CheeseShop/CheeseShop1.aspx
Homework
Normalize these tables. Make a UML Drawing for both tables.
Games
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/ |
Cheeses
CheeseName | CheeseDescription | RegionName | Consistency | MilkType |
---|---|---|---|---|
Argentinian Reggianito | The vast grazing pastures of Argentina revealed themselves to be ideal for immigrant Italians.. | Hard | Argentina | Goats Milk |
Feta | Feta is made in a traditional manner by a small family dairy in central Greece... | Crumbly | Greek | Mix of Milks |
Cheddar | The most widely purchased and eaten cheese in the world. Cheddar cheeses were originally made in England, however today they are manufactured in many countries all over the world. | Semi-Hard | England | Cows Milk |
Build a blog interface to the blog tables we built in class. The blog interface requires
- An insert blog page. Don't worry about user names or authentications this is a very public blog more like a message board.
- A blogroll display page. Be careful which control you use to display the data.
Extra Credit is an edit or delete page.
READ BDD Chapter 4 again Chapter 5
READ BAD Chapter 8
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