Transcript Tech Ed 2008 (Entity Framework and SQL Server CE)

SQL Server Compact Edition and the
Entity Framework
Rob Sanders
Readify
Discover, Master, Influence
Slide 1
SQL Server Compact Edition and the
Entity Framework
INTRODUCTION
Discover, Master, Influence
Slide 2
The SQL Server Family
The SQL Server 2008 Family of Products
• Enterprise
• Standard
• Workgroup
• Web
• Express
• Compact
Discover, Master, Influence
Slide 3
Overview: SQL Server Compact Edition
• Relational Database
• Part of the SQL Server Family
• Runs on:
–
–
–
–
–
Windows Mobile Operating Systems
Windows XP
Windows Server 2003
Windows Vista
Windows Server 2008
Discover, Master, Influence
Slide 4
Why would I use Compact Edition?
Benefits:
• Great for Disconnected Systems,
• Lightweight - low footprint,
• Support for T-SQL
• Critical Business Reports?
Discover, Master, Influence
Slide 5
Why would I use Compact Edition?
•
•
•
•
•
•
•
Flat File based
Memory and CPU conscious design
Support for T-SQL, Encryption (but not Stored Procedures)
In-Process
Doesn’t Require a Service (unlike other SQL Server databases)
ODBC/ADO Supported
Also supported by the Sync Framework (SQL Server 2008)
Discover, Master, Influence
Slide 6
What’s new in Compact Edition 3.5?
• Synchronization (Sync Framework)
• Better integration with SQL Management Studio &
Visual Studio Server Explorer
• Extended T-SQL support
–
–
–
–
CROSS APPLY
CAST
TOP
Identity (Insert)
• Local Transaction Scope (On Desktops)
• Unicode support
• LINQ to SQL
Discover, Master, Influence
Slide 7
Introducing the ADO.Net Entity Framework(EF)
• The goal of EF is to decrease the amount of code and maintenance
required for data-oriented applications
– Lets you create data access applications by programming against a
conceptual model instead of writing directly against a database
schema
– Applications are freed from hard-coded dependencies on a particular
data engine or storage schema.
– ADO.NET Data Services enables data to be exposed as a REST-based
data service that can be consumed by Web clients.
• Entity Framework is central to Microsoft’s data access strategy for
the .Net Framework
• Shipped with .Net Framework 3.5 Service Pack 1
• Underpinned by LINQ to Entities
Discover, Master, Influence
Slide 8
The Entity Data Model
• The Entity Data Model is a Entity-Relationship data model
which supports advanced features such as:
–
–
–
–
–
–
–
–
Abstract classes
Property / Entity renaming
Inheritance
Complex Types
Stored procedure mapping for insert, update and delete methods.
Mapping a single entity to multiple tables
Hiding join tables in collections
Documentation which populate Xml comments
Discover, Master, Influence
Slide 9
Entity SQL
• Write queries independently of the data store with a more concise
language
– Provides a common query language across providers by extending the
existing SQL language with constructs to work with strong notions of type,
inheritance, and relationships from the Entity Data Model
• SQL
– SELECT sp.FirstName, sp.LastName, sp.HireDate
FROM SalesPerson sp
INNER JOIN Employee e ON sp.SalesPersonID = e.EmployeeID
INNER JOIN Contact c ON e.EmployeeID = c.ContactID
WHERE e.SalariedFlag = 1 AND e.HireDate >= '2006-01-01'
• Entity SQL
– SELECT sp.FirstName, sp.LastName, sp.HireDate
FROM AdventureWorks.AdventureWorksDB.SalesPeople AS sp
WHERE e.HireDate >= '2006-01-01‘
• Only used for select statements
Discover, Master, Influence
Slide 11
SQL Server Compact Edition and the
Entity Framework
PRACTICAL IMPLEMENTATION
Image: http://www.techpin.com
Discover, Master, Influence
Slide 13
How Compact Edition Works with the
Entity Framework
Scope of the demonstration:
• Compact Edition integration with Server Explorer and SQL Server
Management Studio
• Create a SQL Server Compact Edition Database
• Query a Compact Edition database
Discover, Master, Influence
Slide 14
How Compact Edition Works with the
Entity Framework
[Demo]
Discover, Master, Influence
Slide 15
Further Reading
See the full presentation next month at
SQL Code Camp in Wagga Wagga
October 11th and 12th
2008
References
Discover, Master, Influence
Slide 16
Getting Started with Entity Framework
• Reading
– MSDN Library Page
http://msdn.microsoft.com/en-us/library/bb399572.aspx
– ADO.NET Team Blog
http://blogs.msdn.com/adonet
– MSDN Forum
http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=533&Site
ID=1
• Resources
– Microsoft .NET Framework 3.5 Service Pack 1
http://www.microsoft.com/downloads/details.aspx?FamilyId=AB99342F5D1A-413D-8319-81DA479AB0D7
– Microsoft Visual Studio 2008 Service Pack 1
http://www.microsoft.com/downloads/details.aspx?familyid=FBEE16487106-44A7-9649-6D9F6D58056E
– eSqlBlast
http://code.msdn.microsoft.com/esql.
Discover, Master, Influence
Slide 17
Thank you
Rob Sanders
Readify
http://www.readify.net
http://www.twitter.com
[Username @AusRob]
http://rob.sanders.name
[Personal Site]
http://internationalized.spaces.live.com [Blog]
Discover, Master, Influence
Slide 18