From Tables to Objects

1
From Tables to Objects

• Making Your Database Work with You

2
jeremiah.peschka_at_gmail.com

• Consultant at HMB
• SQL Server Developer (MCITP)
• Specializes in SQL Server development and tuning
• CBusPASS Chapter Leader
• PASS AppDev virtual chapter leader
• Blogs at facility9.com
• Twitter as _at_peschkaj

3
AGENDA

• Database features
• Mapping Data Objects
• Interacting with the Database
• Advanced Features
• Resources

4
DATABASE FEATURES

• Constraints Triggers
• Indexes
• Indexed Views
• Sparse Columns
• Hierarchies

5
DATABASES NOT JUST A HOLE FOR DATA

• Rich features
• Set Operations
• Long term storage
• Optimized for working with data

6
TWO UNIVERSALS CONSTRAINTS TRIGGERS

• Constraints universal rules
• Triggers universal actions

7
INDEXESMORE THAN JUST A LOOKUP

• Covering index
• Filtered index
• Indexed View

8
SPARSE COLUMNS

• Optimized to store null values
• Easy to create add SPARSE to column definition
• When should you use it?

9
STORING HIERARCHIESADJACENCY LIST

• Familiar
• Pro
• Simple modification
• Con
• Complex retrieval

10
STORING HIERARCHIESNESTED SETS

• ID, ParentID, left, right
• Pro
• Simple Querying
• Con
• Ensuring left, right when moving data

lft lt rgt (rgt-lft-1)/2 child num rgt-lft 1
-gt leaf
11
STORING HIERARCHIESHIERARCHYID

• Pros
• Compact
• Fast
• Simple
• Cons
• Not automatically a tree
• Single root node
• Slower moving non-leaf data

12
STORING HIERARCHIESHIERARCHYID - INDEXING

• Depth first
• Breadth first

13
HIERARCHYID DEMO
14
MAPPING DATA OBJECTS

• What is ORM?
• Why NHibernate?
• Plain Old CLR Objects
• Mapping Classes
• Mapping Associations
• Mapping Inheritance

15
ORM? WHATS THAT?

• Object/Relational Mapping
• Bridge layer
• Transforms data from one representation to another

16
SO, WHY ORM?

• Write less code
• Efficient SQL
• Simplicity
• Maintainable

17
PLAIN OLD CLR OBJECTS

• Foundation of NHibernate
• Just like any other object
• Thats it!

18
HOOKING IT UP

• XML
• Fluent NHibernate

19
MAPPING INHERITANCE

• Table per concrete class
• Table per class hierarchy (single table
  inheritance)
• Table per subclass

20
MAPPING VIEWS

• Just like mapping a table
• Can act just like regular entities

21
Interacting with Data
22
HOW TO GET DATA

• By Id
• HQL
• ICriteria

23
PERSISTING DATA

• Create a session
• Modify objects
• Commit

24
DELETING DATA

• Mark for deletion
• Query for deletion

25
RESOURCES

• http//facility9.com/go/ftto
• http//speakerrate.com/peschkaj