We explored the 1:many table relationship architecture using Primary Key (PK) in one table (for example Department table) and using Primary Key (PK) + Foreign Key (FK) in the second table (for example Employee table). so what did we gain? we got a department which could include many employees (1:many). then we saw how to retrieve an employee with his department name using join query. we also saw how to perform a count in SQL query, i.e. “how many employees belong to each department?”: we wrote an SQL query which updates the count column for each row in the Department table using a nested Update query. then we wrote a C# program which executes a join query on these two tables, and stores the result in an anonymous object (for a new view). we also saw how to modify a query result column name in order to avoid ambiguity in the C# reader result
More topics covered:
- Using = and == in SQL query
- Adding customozied names to an anonymous object properties
- POCO should override == , != , Equals, GetHashCode
- SQLite Auto Increment
Links:
- Previous homework solution – 1:1 Capital City
- class code: 1:n tables queries and C#
- dynamic type – MSDN link
- Lesson Summary Video!
- Homework!
dotnethacker.home.blog 