Transcript WCF SQL SERVER ADAPTER - Learning Center

|
eWorldTree.com
Implementing Polling and Event-Driven Consumers
{ by Juan I. Arriaga }
Twin Cities Connected Systems
User Group (Nov.17th, 2011)
WCF SQL SERVER
ADAPTER
1
eWorldTree.com
Overview of the WCF SQL Adapter
BizTalk examples
A case study of using the WCF SQL Adapter outside of BizTalk
Questions & answers
|
•
•
•
•
Twin Cities Connected Systems
User Group (Nov.17th, 2011)
Agenda
2
WCF SQL SERVER ADAPTER
OVERVIEW
3
Twin Cities Connected Systems
User Group (Nov.17th, 2011)
|
eWorldTree.com
• For other differences, refer to:
Thiago Almeida’s blog post at
http://connectedthoughts.wordpress.com/2011/01/18/wcf-sql-versusthe-old-sql-adapter/
Richard Seroter’s SOA Patterns with BizTalk Server 2009 (Chapter 9) .
Twin Cities Connected Systems
User Group (Nov.17th, 2011)
|
• The classic SQL adapter does not support SQL Query
Notification; the WCF SQL Adapter does.
eWorldTree.com
Classic SQL Adapter vs. WCF SQL
Adapter
4
eWorldTree.com
Twin Cities Connected Systems
User Group (Nov.17th, 2011)
|
Polling Consumer
Source: http://www.eaipatterns.com/PollingConsumer.html by Gregor Hohpe and Bobby Wolf.
5
eWorldTree.com
Twin Cities Connected Systems
User Group (Nov.17th, 2011)
|
Event-Driven Consumer
Source http://www.eaipatterns.com/EventDrivenConsumer.html by Gregor Hohpe and Bobby Wolf.
6
Twin Cities Connected Systems
User Group (Nov.17th, 2011)
|
eWorldTree.com
Query Notification vs. Polling
7
WCF SQL SERVER ADAPTER
BIZTALK EXAMPLES
Polling Consumer
8
Twin Cities Connected Systems
User Group (Nov.17th, 2011)
|
eWorldTree.com
Download & install:
eWorldTree.com
WCF SQL Adapter Installation
• Microsoft BizTalk Adapter for SQL Server (WCF SQL Adapter)
NOTE:
(1) The WCF SQL adapter is also available as part of the BizTalk Adapter Pack.
(2) A BizTalk license is required.
Twin Cities Connected Systems
User Group (Nov.17th, 2011)
|
• WCF LOB Adapter SDK SP2
9
|
eWorldTree.com
Configure Adapter
Twin Cities Connected Systems
User Group (Nov.17th, 2011)
Consume Adapter Service
10
|
eWorldTree.com
Configure Binding
Twin Cities Connected Systems
User Group (Nov.17th, 2011)
Consume Adapter Service
11
|
eWorldTree.com
Typed Polling Contract
Twin Cities Connected Systems
User Group (Nov.17th, 2011)
Consume Adapter Service
12
|
eWorldTree.com
Generated Items
Twin Cities Connected Systems
User Group (Nov.17th, 2011)
Consume Adapter Service
13
|
eWorldTree.com
WcfReceiveLocation for polling
Twin Cities Connected Systems
User Group (Nov.17th, 2011)
BizTalk Admin Console
14
|
eWorldTree.com
Simple send port to trace polling data
Twin Cities Connected Systems
User Group (Nov.17th, 2011)
BizTalk Admin Console
15
Twin Cities Connected Systems
User Group (Nov.17th, 2011)
|
Time to build the example from scratch!
eWorldTree.com
Sample of Typed Polling Data
16
WCF SQL SERVER ADAPTER
BIZTALK EXAMPLES
Event-Driven Consumer
17
Twin Cities Connected Systems
User Group (Nov.17th, 2011)
|
eWorldTree.com
• To verify whether Service Broker is enabled or not:
eWorldTree.com
SQL Service Broker Verification
ALTER DATABASE [MyDB] SET ENABLE_BROKER;
or
ALTER DATABASE [MyDB] SET ENABLE_BROKER WITH ROLLBACK
IMMEDIATE;
Twin Cities Connected Systems
User Group (Nov.17th, 2011)
• To enable Service Broker functionality (if needed):
|
SELECT is_broker_enabled FROM sys.databases WHERE name = ‘MyDB’;
18
|
eWorldTree.com
Configure Adapter
Twin Cities Connected Systems
User Group (Nov.17th, 2011)
Consume Adapter Service
19
|
eWorldTree.com
Configure Binding
Twin Cities Connected Systems
User Group (Nov.17th, 2011)
Consume Adapter Service
20
|
eWorldTree.com
Notification Service Contract
Twin Cities Connected Systems
User Group (Nov.17th, 2011)
Consume Adapter Service
21
|
eWorldTree.com
Generated Items
Twin Cities Connected Systems
User Group (Nov.17th, 2011)
Consume Adapter Service
22
|
eWorldTree.com
WcfReceiveLocation for notifications
Twin Cities Connected Systems
User Group (Nov.17th, 2011)
BizTalk Admin Console
23
|
eWorldTree.com
Simple send port to trace notifications
Twin Cities Connected Systems
User Group (Nov.17th, 2011)
BizTalk Admin Console
24
Twin Cities Connected Systems
User Group (Nov.17th, 2011)
|
Time to build the example from scratch!
eWorldTree.com
Sample of Notifications
25
WCF SQL SERVER ADAPTER OUTSIDE OF BIZTALK
CUSTOMER CASE STUDY
26
Twin Cities Connected Systems
User Group (Nov.17th, 2011)
|
eWorldTree.com
Twin Cities Connected Systems
User Group (Nov.17th, 2011)
|
eWorldTree.com
WCF SQL Adapter Outside BizTalk
27
|
eWorldTree.com
Add Adapter Service Reference –Generated Items
Twin Cities Connected Systems
User Group (Nov.17th, 2011)
Console Service Sample
28
|
eWorldTree.com
Snippet from NotificationAction class
Twin Cities Connected Systems
User Group (Nov.17th, 2011)
Console Service Demo
29
|
eWorldTree.com
Snippet from Main Routine
Twin Cities Connected Systems
User Group (Nov.17th, 2011)
Console Service Demo
30
|
eWorldTree.com
Changes to SqlAdapterBindingService
Twin Cities Connected Systems
User Group (Nov.17th, 2011)
Console Service Demo
31
|
eWorldTree.com
Listener Started Notification
Twin Cities Connected Systems
User Group (Nov.17th, 2011)
Console Service Demo
32
And after the service is stopped…
Twin Cities Connected Systems
User Group (Nov.17th, 2011)
|
Insert Notification
eWorldTree.com
Console Service Demo
33
eWorldTree.com
|
• The adapter only receives one notification if the SQL
statement (e.g. INSERT) affects more than one row.
• Consider the use of the NotifyOnListenerStart event to
retrieve pending records after a network communication
breakdown.
• Double check the requirements and restrictions for SELECT
statements in a query notification.
Twin Cities Connected Systems
User Group (Nov.17th, 2011)
Caveats & Gotchas
34
eWorldTree, LLC
Branch out your information network ™. Link to eWorldTree™.
eWorldTree.com
|
Thank You!
Twin Cities Connected Systems
User Group (Nov.17th, 2011)
Q&A
35