Geeks With Blogs

News This is the *old* blog. The new one is at blog.sixeyed.com

Elton Stoneman
This is the *old* blog. The new one is at blog.sixeyed.com

[Source: http://geekswithblogs.net/EltonStoneman]

The Adapter Pack 2.0 for BizTalk has been released in public beta recently, and among the WCF Line Of Business adapters it contains the WCF SQL adapter. This exposes SQL Server connections as WCF service endpoints, and lets you connect to a SQL Server source using the standard ServiceModel stack. The adapter pack will be released under the BizTalk brand, but the adapters themselves are not limited to BizTalk – the WCF SQL adapter can be used natively in .NET code.

This is a brief walkthrough covering the SQL Adapter for executing stored procedures, and I'll be covering SQL statements in a subsequent post.

Installation

Installation of the Adapter Pack is straightforward – you'll need .NET 3.5 Service Pack 1 with the latest hotfixes applied, and you'll need to install the WCF LOB Adapter SDK and then the BizTalk Adapter Pack 2.0 Evaluation (the beta version is limited to 120-day use). Note, you do not need to have BizTalk installed, and the tooling to support the WCF LOB adapters runs under Visual Studio 2008 as well as 2005. Help files for all the adapters are included, and although in pre-release form they are detailed and thorough.

Walkthrough: Consuming a Stored Procedure

The Adapter Pack adds a new context menu to code projects in Visual Studio – Add Adapter Service Reference. Run this and you're given a generic form for configuring your WCF LOB adapter. Choose sqlBinding to set up a WCF SQL connection:

Click Configure and you specify the connection configuration that will be used to build the binding. In the case of the WCF SQL adapter, you need to specify:

  • Client Credential Type (Windows for integrated authentication);
  • Server (database server name);
  • Instance (SQL instance name, if configured);
  • Initial catalog (the database to connect to).

This will build you a URI of the form: mssql://<server>/<instance>/<initialCatalog>? – with the ending question mark used to separate the core connection details from any configuration options.

Click Connect and you have the option to generate a Client binding, for making outbound requests to SQL Server (executing SQL statements, stored procedures etc.), or a Service binding which will react to inbound calls from SQL Server (for Query Notification or polling). Choose Client and the category view will be populated with a hierarchy of database objects which can be generated as WCF client proxies:

Generated Code

For stored procedure calls, the adapter can create generic proxies for weakly-typed calls returning populated DataSets, or strongly-typed calls which will generate entities representing the return from the call. In this case I've selected a Strongly-Typed Stored Procedure called GetManufacturer; add the selection and with the default options the adapter generates two items:

  • App.config – containing the WCF binding configuration;
  • SqlAdapterBindingClient.cs – containing the generated entity types and proxy classes.

The full binding configuration for the SQL adapter looks like this:

<system.serviceModel>

<bindings>

<sqlBinding>

<binding name="SqlAdapterBinding" closeTimeout="00:01:00" openTimeout="00:01:00"

receiveTimeout="00:10:00" sendTimeout="00:01:00" maxConnectionPoolSize="100"

encrypt="false" workstationId="" useAmbientTransaction="true"

batchSize="20" polledDataAvailableStatement="" pollingStatement=""

pollingIntervalInSeconds="30" pollWhileDataFound="false" notificationStatement=""

notifyOnListenerStart="true" enableBizTalkCompatibilityMode="true"

chunkSize="4194304" inboundOperationType="Polling" useDatabaseNameInXsdNamespace="false"

allowIdentityInsert="false" enablePerformanceCounters="false"

xmlStoredProcedureRootNodeName="" xmlStoredProcedureRootNodeNamespace="" />

</sqlBinding>

</bindings>

<client>

<endpoint address="mssql://x/y/z?" binding="sqlBinding"

bindingConfiguration="SqlAdapterBinding" contract="TypedProcedures_dbo"

name="SqlAdapterBinding_TypedProcedures_dbo" />

</client>

</system.serviceModel>

- note that the binding contains some familiar WCF settings (sendTimeout, receiveTimeout), but the majority are SQL Server specific connection options. The client element specifies the contract as TypedProcedures_dbo, the ServiceContract interface generated by the adapter, which has a single OperationContract defined:

GetManufacturerResponse GetManufacturer(GetManufacturerRequest request);

The proxy code for the client is all generated, so to invoke the stored procedure in your own code it's a familiar case of instantiating the client and calling the service, and of course you have full IntelliSense on the entity representing the resultset:

It's the content of the generated code that's interesting. The service, request, response and entity objects here are contained in 166 lines of generated code. The entity object is just a plain DTO-style class which implements IExtensibleDataObject to allow access to any returned data that hasn't been mapped, and has a DataContract attribute with the schema name representing the stored procedure. The mapping between the entity properties and the returned columns is done with standard System.Runtime.Serialization attributes, so the ManufacturerId column is represented as:

[System.Runtime.Serialization.DataMemberAttribute()]

public System.Nullable<short> ManufacturerId {

get {

return this.ManufacturerIdField;

}

set {

this.ManufacturerIdField = value;

}

}

- note that this is an optional field in the database table, so it's generated as nullable in the entity. No other flags or code are used to map data, so the WCF SQL adapter is effectively deserializing the resultset from the stored procedure call straight into the DataContract.

The generated code works well and is cleanly produced, but it has a few quirks you may not be happy with. A typed client class is generated for each individual procedure, whereas you might want them grouped into a single class which represents the full suite; and the class names are a bit cumbersome ("StoredProcedureResultSet0", "TypedProcedures_dboClient"). However, the code needed to actually connect to SQL through WCF and map the response is so simple that it's a straightforward task to generate your own code from custom templates.

Potential Usage

After an initial look, the WCF SQL adapter seems to be an attractive option for generating and powering the data access layer of a .NET application, entirely apart from its primary purpose as a BizTalk adapter. It repositions data access as a service call and uses the standard WCF mechanisms of ServiceContract and DataContract for information exchange. Assuming other data providers follow suit, or other WCF-database adapters follow from the community, it's a nice way of isolating your application from the physical database, so swapping to MySQL or Oracle could become a simple matter of changing your WCF binding.

It'll be interesting to see the licensing of the WCF LOB adapters from Microsoft. Currently the availability of a comprehensive suite of adapters is being positioned as one of the attractions of BizTalk as the Integration Server, compared to WF+WCF+Dublin as the Application Server. With the WCF SQL adapter there's a lot of potential take-up as a simpler alternative to the ADO.NET Entity Framework, so if it requires a BizTalk license, there will be room for an open source alternative.

 

Posted on Monday, March 2, 2009 6:42 PM CodePlex Project , WCF , SQL Server | Back to top


Comments on this post: Using the WCF SQL Adapter in .NET: Calling Stored Procedures

# re: Using the WCF SQL Adapter in .NET: Calling Stored Procedures
Requesting Gravatar...
Nice walk through of using a WCF adapter from .NET. I've been wondering how that's done and you've illustrated it very nicely.

As you say, if you want a WCF DAL this may be a viable option.
Left by Walter Michel on Mar 10, 2009 7:22 PM

# re: Using the WCF SQL Adapter in .NET: Calling Stored Procedures
Requesting Gravatar...
Thanks Walter, glad you found it useful.
Left by Elton on Mar 10, 2009 9:38 PM

# re: Using the WCF SQL Adapter in .NET: Calling Stored Procedures
Requesting Gravatar...
Very nice article. Just have one question. Can i host the wcf service built using this adapter and poll the database every after 1 minutes to get fresh data.
Left by Malik on Mar 24, 2009 4:31 PM

# re: Using the WCF SQL Adapter in .NET: Calling Stored Procedures
Requesting Gravatar...
Just have one question. Can i host the wcf service built using this adapter and poll the database every after 1 minutes to get fresh data.
Left by Malik on Mar 24, 2009 4:32 PM

# re: Using the WCF SQL Adapter in .NET: Calling Stored Procedures
Requesting Gravatar...
Wonderful knowledgeable and very informative post. great dude i liked it most it is one of the important article for me! You have got my best recommendation on this. Keep this coming!



Left by Hotel isola d'Elba on Jan 31, 2010 11:05 AM

# re: Using the WCF SQL Adapter in .NET: Calling Stored Procedures
Requesting Gravatar...
Hi buddy, I found your site from wikipedia and read a few of your other blog posts.They are cool. Pls continue this great work.
Left by Residence toscana mare on Mar 16, 2010 8:49 AM

# re: Using the WCF SQL Adapter in .NET: Calling Stored Procedures
Requesting Gravatar...
Job well done. This is just what I needed! It's very compact so it's not cluttering up my blog! Thanks again!
Left by alberghi toscana mare on Mar 16, 2010 9:08 AM

# re: Using the WCF SQL Adapter in .NET: Calling Stored Procedures
Requesting Gravatar...
Thank you for such a fantastic blog. Where else could anyone get this kind of info written in such an incite full way? I have a project that I am just now working on, and I have been looking for such information.
Left by Bed and Breakfast Florence on Mar 16, 2010 9:36 AM

# re: Using the WCF SQL Adapter in .NET: Calling Stored Procedures
Requesting Gravatar...
Having written articles that require this much work, I commend you for your service to the future bloggers. I’m sure they will appreciate it!Thanks a lot for sharing.
Left by storesonline555 on Apr 11, 2010 10:05 AM

# re: Using the WCF SQL Adapter in .NET: Calling Stored Procedures
Requesting Gravatar...
This post is well written…thanks for the post!Some great information to be absorbed in this post. Thanks!Keep up the good work.
Left by home security systems21 on May 25, 2010 6:36 AM

# re: Using the WCF SQL Adapter in .NET: Calling Stored Procedures
Requesting Gravatar...
This was a very well-written and enjoyable post to read.Some great information to be absorbed in this post.I have bookmarked this for my friends. Keep blogging.
Left by digital scrapbook-85 on Jun 10, 2010 8:24 AM

# re: Using the WCF SQL Adapter in .NET: Calling Stored Procedures
Requesting Gravatar...
Very interesting topic will bookmark your site to check if you write more about in the future.
Left by platinum protection48 on Jun 12, 2010 9:57 AM

# re: Using the WCF SQL Adapter in .NET: Calling Stored Procedures
Requesting Gravatar...
Hey I just got through with the pages, I should say Pretty nice work done there.I don't know what to say except that I have enjoyed reading.Thanks a lot.
Left by personal care products489 on Aug 30, 2010 8:05 AM

# re: Using the WCF SQL Adapter in .NET: Calling Stored Procedures
Requesting Gravatar...
By using the WCF-Custom adapter you can select and configure the SQL DB Binding and the behavior for the receive location or send port.And it's a nice walk through of using a WCF adapter from .NET.
Left by ultrasound technician schools on Oct 22, 2010 6:31 AM

# re: Using the WCF SQL Adapter in .NET: Calling Stored Procedures
Requesting Gravatar...
It looks at using the adapter to execute SQL statements on database objects.This exposes SQL Server connections as WCF service endpoints, and lets you connect to a SQL Server source using the standard ServiceModel stack.
Left by Stairlifts on Nov 09, 2010 8:07 AM

# re: Using the WCF SQL Adapter in .NET: Calling Stored Procedures
Requesting Gravatar...
I am a student of Bsc IT...I am doing my project in SQL...I think this post will definitely help me to develop my project..
Left by Uffizi tickets reservation on Nov 21, 2010 4:12 AM

# re: Using the WCF SQL Adapter in .NET: Calling Stored Procedures
Requesting Gravatar...
Most stored procedures that you will be writing for MySQL 5.0 will undoubtedly reference static table and column names. However, sometimes it is desirable to be able to pass these details to a stored procedure as parameters.
Left by Cenacolo tickets on Nov 22, 2010 10:15 AM

# re: Using the WCF SQL Adapter in .NET: Calling Stored Procedures
Requesting Gravatar...
Its a nice article.Could you please write some article on using WCF-SQL Adapter,because I am working on Biztalk Server 2009 and I am completely newbie on this.
Left by Brij on Dec 28, 2010 10:55 AM

# re: Using the WCF SQL Adapter in .NET: Calling Stored Procedures
Requesting Gravatar...
I have read few of articles here and can say it was really interesting, thanks for sharing this. I hope you will post again soon.
Left by GGBS Transpation on Mar 09, 2011 11:21 AM

# re: Using the WCF SQL Adapter in .NET: Calling Stored Procedures
Requesting Gravatar...
This blog gives the light in which I can observe the reality. This is very nice one and gives useful information. Thanks for this nice blog..appreciate it
Left by Inland Empire Carpet Repair on Mar 12, 2011 6:08 PM

# re: Using the WCF SQL Adapter in .NET: Calling Stored Procedures
Requesting Gravatar...
BizTalk Adapter Pack 2.0 enables to send and receive messages to and from a line-of-business system.The BizTalk Server team is using this model as a pilot and also for this specific version. The plan to apply this model is based on the feedback that we received from the community.
Left by build my rank on Mar 23, 2011 10:55 AM

# re: Using the WCF SQL Adapter in .NET: Calling Stored Procedures
Requesting Gravatar...
Nice information, valuable and excellent design, as share good stuff with good ideas and concepts, lots of great information and inspiration, both of which we all need, thanks for all the enthusiasm to offer such helpful information here...very good work
Left by Sacramento Carpet Repair on Apr 04, 2011 7:26 PM

# re: Using the WCF SQL Adapter in .NET: Calling Stored Procedures
Requesting Gravatar...
this is a good topic of discussion and hope your success rubs off...
Indian escorts London
Left by London Escorts on Jun 05, 2011 12:15 AM

# re: Using the WCF SQL Adapter in .NET: Calling Stored Procedures
Requesting Gravatar...
Stroll through the use of a WCF adapter. NET. I've been wondering how it's done and well illustrated.
Just Dreams Reviews
Left by Ian on Jun 14, 2011 11:12 AM

# re: Using the WCF SQL Adapter in .NET: Calling Stored Procedures
Requesting Gravatar...
I really appreciate your professional approach. I want to thank you for the work you did in writing this post. I hope the same thing better job of you in the future too.All contents mentioned the theme is too good and can be very useful. Thank you for sharing information with this nice message.. vancouver condo | vancouver townhouse
Left by Shane Vincent on Jul 04, 2011 7:59 AM

# re: Using the WCF SQL Adapter in .NET: Calling Stored Procedures
Requesting Gravatar...
This shows the level of the author that the way in this thread with a vengeance.I have never found this kind of information before it is such an awesome hats to work.. Breguet watches | Hublot Watches
Left by Stephan Crooks on Jul 04, 2011 1:42 PM

# re: Using the WCF SQL Adapter in .NET: Calling Stored Procedures
Requesting Gravatar...
Thank you for providing useful information. I really appreciate your professional approach. I Prefer To Read This Kind Of Thing. Content Quality Is Very Good And The Conclusion Is Correct. Thank You posting the information.. Ab Exercise Equipment
Left by Hamish on Jul 07, 2011 1:53 PM

# re: Using the WCF SQL Adapter in .NET: Calling Stored Procedures
Requesting Gravatar...
Nice information, valuable and excellent design, as share good stuff with good ideas and concepts, lots of great information and inspiration, both of which we all need, thanks for all the enthusiasm to offer such helpful information here...very good work
Left by saga gold on Jul 16, 2011 8:14 PM

# re: Using the WCF SQL Adapter in .NET: Calling Stored Procedures
Requesting Gravatar...
Nice information, valuable and excellent design, as share good stuff with good ideas and concepts, lots of great information and inspiration, both of which we all need, thanks for all the enthusiasm to offer such helpful information here...
Left by saga hair on Jul 17, 2011 5:15 AM

# re: Using the WCF SQL Adapter in .NET: Calling Stored Procedures
Requesting Gravatar...
Having written articles that require this much work, I commend you for your service to the future bloggers. I’m sure they will appreciate it!Thanks a lot for sharing.
Left by saga remy on Jul 20, 2011 3:17 PM

# re: Using the WCF SQL Adapter in .NET: Calling Stored Procedures
Requesting Gravatar...
hmmm, much powerful information for me, I am really very impress to see it. Keep it up dude.
Thanks for sharing such a nice post
regards
Left by small business credit cards on Jul 23, 2011 11:41 PM

# re: Using the WCF SQL Adapter in .NET: Calling Stored Procedures
Requesting Gravatar...
My friend told me to check out this post and I found it quite impressive. I will bookmark this blog for my future is needed and I will tell my friends to see this message. Thank you very much for sharing.
Calgary MLS Listings | Harvest Hills Real Estate
Left by jackredolfcm on Jul 25, 2011 7:47 AM

# re: Using the WCF SQL Adapter in .NET: Calling Stored Procedures
Requesting Gravatar...
Thanks Walter nice article! bed and breakfast florence
Left by Florence on Aug 02, 2011 9:32 AM

# re: Using the WCF SQL Adapter in .NET: Calling Stored Procedures
Requesting Gravatar...
I waited this article, and I got some very useful information from this site.I enjoy the valuable information you provide in your articles. I will bookmark your blog and check my friends here often. I'm sure they will learn many new things here than anyone.
Health Club Equipment
Left by Ammi on Aug 03, 2011 6:55 AM

# re: Using the WCF SQL Adapter in .NET: Calling Stored Procedures
Requesting Gravatar...
Very Glad to see such a nice & useful blog, i think you are imparting some great tips out here The work which you have done is really appreciable keep up the good work.
Left by print shanghai on Aug 06, 2011 10:06 AM

# re: Using the WCF SQL Adapter in .NET: Calling Stored Procedures
Requesting Gravatar...
These type of blogs are always interested because of content and subject! Keep the exchange of ideas in the future as well. This was actually what I was looking, and I'm glad to be here! Thanks for sharing the information with us.
school fundraisers | charity fund raising
Left by Stephan Crooks on Aug 20, 2011 8:34 AM

# re: Using the WCF SQL Adapter in .NET: Calling Stored Procedures
Requesting Gravatar...
Thank you very much for providing this informative blog, which is the stuff that keeps me awake these days. I've been looking around your site after they are referenced from a friend and was very excited when I was able to find it after searching for a while. Being a blogger demanding, I'm glad to see others take the initiative and contribute to the community.
Ultrasonics Cleaning | Cleaning Solutions
Left by sonixiv on Aug 29, 2011 10:11 AM

# re: Using the WCF SQL Adapter in .NET: Calling Stored Procedures
Requesting Gravatar...
I would really appreciate if I would recommend to their readers. Keep up the good work and I am waiting to read well-written, clear and documented articles from you soon.
NW Calgary Real Estate | SE Calgary Real Estate
Left by Jack Redolf on Aug 30, 2011 10:32 AM

# re: Using the WCF SQL Adapter in .NET: Calling Stored Procedures
Requesting Gravatar...
Is very gratifying to see such a nice and useful blog, I think you have done some great tips to keep up the good work that really are providing commendable.
New movies | Movie review
Left by New movies on Sep 09, 2011 8:29 AM

# re: Using the WCF SQL Adapter in .NET: Calling Stored Procedures
Requesting Gravatar...
You most definitely have made this blog into something that’s eye opening and important. You clearly know so much about the subject, you’ve covered so many bases.
network visualization | Display wall software
Left by Roger on Oct 14, 2011 11:10 AM

# re: Using the WCF SQL Adapter in .NET: Calling Stored Procedures
Requesting Gravatar...
Wau, The beauty of these blogging engines and CMS platforms is the lack of limitations and ease of manipulation that allows developers to implement rich content and 'skin' the site in such a way that with very little effort one would never notice what it is making the site tick all without limiting content and effectiveness.
Path to Healing
Left by Rizals on Oct 19, 2011 1:35 PM

# re: Using the WCF SQL Adapter in .NET: Calling Stored Procedures
Requesting Gravatar...
Wonderful blog! I actually love how it’s effortless on my eyes as well as the details is well written. I am wondering how I can be notified whenever a new post has been made.
Left by latest news today on Nov 02, 2011 6:24 PM

# re: Using the WCF SQL Adapter in .NET: Calling Stored Procedures
Requesting Gravatar...
great one yet again
Left by life insurance on Apr 10, 2012 10:35 AM

# re: Using the WCF SQL Adapter in .NET: Calling Stored Procedures
Requesting Gravatar...
check out a lot more often. I enjoy your website.
Left by Black Friday 2012 on Oct 31, 2012 6:55 AM

# re: Using the WCF SQL Adapter in .NET: Calling Stored Procedures
Requesting Gravatar...
Any new versions ? will be open source in codeplex or github?
Left by kiquenet on Oct 22, 2013 10:25 AM

Your comment:
 (will show your gravatar)


Copyright © Elton Stoneman | Powered by: GeeksWithBlogs.net