SQL Server

Report
Your Data Any Place, Any Time
Data Application Development
Lương Đình Dũng
Technology Specialist – Database Platform
Microsoft Vietnam LLC
0986.844.624 | [email protected]
SQL Server 2008
Microsoft Data Platform
Productivity
Challenges
Translating logical database schemas to
business objects
Incorporating diverse data access syntax
for multiple data sources
Creating and maintaining large, complex
applications
Productivity
Solutions
EDM
• Conceptual
data model
• Faster
development
• Improved
maintainability
LINQ
• Data is a first
class object
• Data source
neutral syntax
• Compile-time
validation
Visual Studio
• Design time
IntelliSense
• Full cycle team
development
• EDM and LINQ
integration
Comprehensive Data Platform
Challenges
Accessing data in diverse stores
throughout the enterprise
Implementing complex data processing
functionality
Extending the reach of data applications
Comprehensive Data Platform
Solutions
Broad Connectivity
Rich
Programmability
• ODBC, OLE DB,
and ADO
• ADO.NET &
Entity
Framework
• XML, JDBC,
PHP
• Native query
syntax (LINQ)
• Conceptual
data model
(EDM)
• SQL Server
integrated CLR
Your Data, Any
Time, Any Place
• Range of data
access options
• Support for
occasionally
connected
solutions
Scalability
Challenges
Storing high volumes of data
Supporting diverse types of data
Building scalable applications and
services
Scalability
Solutions
Enterprise-Scale
Data Store
Broad Data Type
Support
Application
Scalability
• SQL Server
2008 Editions
to suit all
requirements
• Robust data
management
that grows with
your business
• Relational data
• CLR types
• Spatial data
• Filestreams
• XML
• ADO.NET Entity
Framework
• Service Broker
• SQL Compact
Edition
• Visual Studio
Team System
Agenda
Language Integrated Query (LINQ)
ADO.Net Entity Framework
Microsoft Synchronization Framework
Agenda
Language Integrated Query (LINQ)
ADO.Net Entity Framework
Microsoft Synchronization Framework
Language Integrated Query (LINQ)
Use LINQ to
•Simplify data access code
•Enhance developer productivity
•Create flexible data access
solutions
Data Access Programming Challenges
Multiple, disparate data stores
• Developers must learn data store-specific query syntax
Data access code is mixed with application code
• Difficult to debug and maintain
• Code is often complex and verbose
Data model mismatch
• Data schemas do not match application object models
Introduction to LINQ
Language Integrated Query (LINQ)
• Extensions for .NET languages
• Native syntax for query operations
• Strong typing and object-orientation for
data
• Implementations for disparate data sources
Data Access Code Today
class DataAccess{
static void GetNewOrders(DateTime date, int qty) {
using (SqlConnection con = new SqlConnection(Settings.Default.NWDB)) {
con.Open();
Query syntax
SqlCommand cmd = con.CreateCommand();
is sourcecmd.CommandText = @"
specific and
SELECT o.OrderDate, o.OrderID, SUM(d.Quantity) as Total
must be
FROM Orders AS o
embedded
LEFT JOIN [Order Details] AS d ON o.OrderID = d.OrderID
into
WHERE o.OrderDate >= @date
application
GROUP BY o.OrderID
code
HAVING Total >= 1000";
cmd.Parameters.AddWithValue("@date", date);
DbDataReader r = cmd.ExecuteReader();
while (r.Read()) {
Console.WriteLine("{0:d}:\t{1}:\t{2}", r["OrderDate"], r["OrderID"], r["Total"]);
}
Data values are contained
}
in a data structures,
}
which must be navigated
}
Modeling Databases using LINQ to SQL
The DataContext Class
 .NET classes that represent the entities
and database relationships
 A custom DataContext class is generated
for each LINQ to SQL designer file
 It has properties that represent each
Table modeled within the database, as
well as methods for each Stored
Procedure we added
LINQ to SQL Code Examples
 Query data
 Update data
 Insert data
LINQ to SQL Code Examples (cont…)
 Delete data
 Calling a Stored Proc
LINQ to SQL Code Examples (cont…)
 Retrieve data with server-side paging
Benefits of LINQ
Increased
developer
productivity
Flexible data
access for the
enterprise
Simpler data
access code
LINQ
LINQ Flexibility
• Strongly-typed
LINQ access to
Microsoft
ADO.NET Entity
Framework
• Strongly-typed
LINQ access to
SQL Server
• Strongly-typed
LINQ access to
XML data
LINQ to
SQL
LINQ to
Entities
LINQ to
XML
LINQ to
DataSet
• Strongly-typed
LINQ access to
DataSet
functionality
Language Integrated Query
Agenda
Language Integrated Query (LINQ)
ADO.Net Entity Framework
Microsoft Synchronization Framework
ADO.NET Entity Framework
Today’s Challenges
Logical Database Schema
• Developers need to understand the logical database schema in
order to program against it
Disparate Data Sources
• Developers need to know multiple languages and dialects to
program against disparate data sources
Normalized Data
• Developers need to write complex code to work with business
object whose data is spread across many tables and rows
ADO.NET Entity Framework
Solutions
Entity Data Model
• Developers can use the Entity Data Model to transform relational
data into conceptual entities
LINQ and Entity SQL
• Developers can use LINQ and Entity SQL to write data access
code in a .NET programming language
Entities
• Developers can work with entities instead of tables and rows to
simplify data access code
An Adaptive Framework
Work with Conceptual Business Objects
Map tables in the data source to
entities in the conceptual model
Map multiple tables to a single
entity and map many-to-many
relationships
dbo.Customer
dbo.CustomerDetails
Use complex types to represent
related objects
Customer
An Adaptive Framework
Use Inheritance with Data
Conceptual model that supports inheritance
• Developers are familiar with inheritance
• The ADO.NET Entity Framework brings inheritance to
the conceptual model
• The ADO.NET Entity Framework supports three types
of inheritance
• Table per hierarchy
• Table per subclass
• Table per concrete type
An Adaptive Framework
Customize Client Views
Customize the automatically generated client views to meet
your business requirements
• Use an Entity SQL WHERE clause to return a subset of the data
SELECT P
FROM AW.Production.Product AS P
WHERE p.ProductCategory.Name = 'Road Bikes'
• Use a DefiningQuery to specify the required data using the native
query language
<EntitySet Name="Product" EntityType="AW.Product">
<DefiningQuery>
SELECT p.Name, p.ProductNumber, p.ReorderPoint
FROM Product as p WHERE p.DaysToManufacture > 1
</DefiningQuery>
</EntitySet>
An Extensible Framework
Customize Modeling Files
Use a schema-aware XML editor to add metadata or
modify properties in the automatically generated
modeling and mapping files
CSDL
<FunctionImport Name="GetOrderDetails"
EntitySet="SalesOrderDetail"
ReturnType="Collection(AdventureWorksModel.SalesOrderDetail)">
<Parameter Name="SalesOrderHeaderId" Type="Int32" Mode="in">
</Parameter>
</FunctionImport>
MSL
<FunctionImportMapping FunctionImportName="GetOrderDetails"
FunctionName="AdventureWorksModel.Store.GetOrderDetails"/>
An Extensible Framework
Customize Generated Code
Add business logic to data access code
• Extend the partial classes that the tools generate
• Create custom implementations of the partial classes’
base interfaces
[Serializable()]
public partial class Customers : Cust
{
// autogenerated code
[Serializable()]
}
public partial class Customers : Cust
{
// your code
}
An Evolutionary Framework
Visual Studio 2008 Tools
 Microsoft® Visual Studio® 2008
provides Entity Framework
Tools to work with models and
mappings
 Entity Designer to generate and
validate models
 Entity Mapping Tool to define
entities and associations
 Entity Model Browser to visualize
the model
An Evolutionary Framework
Familiar Data Provider Functionality
Continue to use the
ADO.NET data
provider model
Use the EntityClient
data provider to
query against
conceptual models
An Evolutionary Framework
Write Better Code
Query and return results as strongly typed CLR data objects
• By using LINQ or Entity SQL
LINQ
var newPeople = from p in aw.SalesPeople
where p.HireDate > hireDate select p;
foreach(SalesPerson p in newPeople)
{
Console.WriteLine("{0}\t{1}", p.FirstName, p.LastName);
}
Use IntelliSense®
• IntelliSense supports strongly typed objects
An Evolutionary Framework
Simplify Maintenance
When using entities, schema changes do not always
result in changes to the application code
Upgrade to more powerful editions of SQL Server™
with minimal changes to code
ADO.Net Entity Framework
Adaptive
Extensible
Evolutionary
• Map tables to entities
• Map many-to-many relationships
• Use inheritance
• Build custom data views
• Customize modeling files
• Customize generated code
• Simplify modeling and mapping
• Use new functionality in a familiar way
• Write better code
• Simplify application maintenance
ADO.Net Entity Framework
Agenda
Language Integrated Query (LINQ)
ADO.Net Entity Framework
Microsoft Synchronization Framework
Synchronization Scenarios
Seamless
online/offline
solutions (e.g.
Microsoft Office
Outlook)
Rich roaming
and sharing (e.g.
mobile devices,
phones, media
players)
Resilient Web
services with
offline
capabilities
Client-side
storage for line
of business
applications
The Challenges of Synchronization
Profusion of
devices and data
services
Multiple formats
and protocols
Complex content
flow
Increased mobility
and offline
scenarios
Introducing the Microsoft Sync Framework
Flexible sync
solutions
Comprehensive
sync
capabilities
Productive sync
framework
Microsoft
Sync
Framework
Microsoft Sync Framework Goals
Provide a common, reusable and optimized runtime for
building synchronization solutions
Facilitate content flow across solutions by standardizing
on the synchronization metadata
Simplify the actual development of sync solutions by
providing store-specific and end-to-end components for
common scenarios
Microsoft Sync Framework Functionality
 Metadata-based synchronization
 Support for a variety of sync topologies
 Peer-to-peer
 Hub/spoke
 Full-mesh
 Enables virtually any device, service, application, or
platform to perform full, multi-master synchronization
 Filter sync support
 Sync criteria (e.g. “4 star music”, “Mail received in the last 2
days”)
 Sync a subset of fields (e.g. “FirstName”)
MSF Architecture
Core Sync
Runtime
• Metadata services for synchronization, sharing,
conflict resolution, sync granularity, and sync session
management
• Core support for Simple Sharing Extensions (SSE) for
RSS and ATOM
Sync Provider
Framework
• Extensible framework used by developers to create
sync providers for devices and applications
• The framework supports a range of participant levels
Store-Specific
Components
• Built-in sync capability for common stores and
protocols including Synchronization Services for
ADO.NET, SQL Server Compact Metadata Store, File
and Folder Sync Provider, and ADO.NET data Services
Universal Content Flow
 Add synchronization
capabilities to virtually
any
 Device
 Application
 Service
 Synchronize any kind
of data
 Use virtually any
protocol
Sync Provider Participation Level
Full Participants
• Capable of peer-to-peer/full mesh sync
Partial Participants
• Store information but do not understand most of the sync metadata (e.g. a USB
keychain, legacy phone, media device). Despite being very easy to develop even
on endpoints that do not host the MSF engine, these providers can participate in
all multi-master content flow scenarios.
Simple Participants
• Lack the ability to detect changes and that lack the ability to store metadata. MSF
has services to allow data from these participants to flow within the ecosystem on
behalf of a fully featured participant/provider.
Anchor-Based Providers
• Rely on a simple tick-count based enumeration mechanism for their change
detection mechanism (e.g. timestamps, tick counts, etc.)
Quickly Build and Integrate Sync Solutions
• Simple metadatabased abstraction
• Easy development
model
Add synchronization
capabilities with
minimal modifications
Use built-in integration
with popular Microsoft
technologies
• Microsoft .NET
Framework
• SQL Server 2008
• Windows File System
• Add synchronization
to virtually any
device, application, or
service
Extend
synchronization with
custom sync providers
Licensing
MSF is Free to use on Microsoft Windows
• Add support for non-Microsoft participants through
applicable licenses
Microsoft Sync Framework
• A comprehensive, flexible, and productive
platform for developing a synchronization
ecosystem
• Add synchronization to virtually any
device, application or service
• Synchronize any kind of data over
virtually any protocol
Microsoft Synchronization Framework
© 2007 Microsoft Corporation. All rights reserved.
This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.

similar documents