Using the WCF SQL Adapter in .NET: Calling Stored Procedures

by | Mar 2, 2009 | BizTalk Community Blogs via Syndication

[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.

nHibernate Tutorials for .Net!

nHibernate Tutorials for .Net!

by | Mar 2, 2009 | BizTalk Community Blogs via Syndication

Often these days, i find managers requesting developers to get their hands on Hibernate methodologies to connect to various relational databases. As i was trying to check this out, i came across this very useful tutorial on nHibernate
@ http://www.hibernate.org/362.html
NHibernate handles persisting your .NET objects to and from an underlying relational database. Rather than you […]

SQL Server 2005/2008 Best Practices!

SQL Server 2005/2008 Best Practices!

by | Mar 2, 2009 | BizTalk Community Blogs via Syndication

Hi All,
I found a list ofBest Practices available for SQL Server 2005/2008. Check them out here:
XML Best Practices for Microsoft SQL Server 2005%u00b7 Performance Optimizations for the XML Data Type in SQL Server 2005%u00b7 SQL Server 2005 Security Best Practices – Operational and Administrative Tasks%u00b7 SQL Server 2005 Full-Text Queries on Large Catalogs: Lessons […]

Xml Schema Documenter

Xml Schema Documenter

by | Mar 2, 2009 | BizTalk Community Blogs via Syndication

I found Sand Castle to be a really useful tool for documenting our projects and now i am more excited to see this new tool called Xml Schema Documenter (a plugin of sandcastle) which documents xml schemas.

XML Schema Documenter allows you to easily create XML schema documentation by extending Sandcastle Help File Builder.
XML Schema Documenter […]

Ramp up

Ramp up

by | Mar 2, 2009 | BizTalk Community Blogs via Syndication

Hi All,
Ramp Up is a free, online, community-based learning program, with a number of different tracks that will help you build your portfolio of professional development skills. Ramp Up has a solid foundation of premium technical content from subject-matter gurus, and provides easy-to-access content in a variety of forms that guide you in learning the […]

Sydney to the MVP 2009 Summit (Seattle) – Day 0

by | Feb 28, 2009 | BizTalk Community Blogs via Syndication

Woke up now yesterday morning Sydney time (GMT+10hrs) feeling great, sunny day the
beach was just glassy and really blue. What a day I thoughtall perfect. 🙂

Upon checking into Sydney Airport for the flight, the check-in lady mentioned extra
forms and electronic visa waivers that needed to be done (nothing that 5 mins online
didn’t fix – but I still had to fill out all usual visa paperwork as per normal –
so I’m questioning the value of my electronic visa waiver)

Before long I had met up with the usual suspects of Guy Riddel, Adam Cogan and new
kid on the block Alessandro (from Brazil – Hyper-V MVP) at the boarding gate. Adam
– just made it as he was late (I can’t print the *reason* why he
was late – but let’s just say it was a unique situation); Guy pulled out the ’I’m
going Business boys’ see you at the other end after a sleep, massage and personal
chef

We were getting ready to go when the plane needed some battery replaced and the recharger
– few of use had AA batteries and could find a charger from somewherebut 1.5hrs later
the real guys had done the real thing.

We’re off!

14hrs later we touch down into San Franciso (beautiful place) and Adam didn’t clear
customs as ’gracefully’ as the rest of us, but none the less he made it.

We picked up our baggage, dropped it off for the domestic flight and proceeded to
the Alaskian domestic gate – Adam didn’t pass security as he was carrying 3 bottles
of water and a Qantas wine bottle. After drinking the 3 bottles in front of the security
official they let him carry on the wine (Adam liked the bottle)

So far all good

We were about to board the 12.30pm Alaska Air flight straight to Seattle – 90 mins
away from finish, a rest, shower and a bit of time to walk about Seattle.

The flight had a “Maintenance problem” which they were “fixing”2 hrs later still
nothing.

I made an executive decision to try and get to Seattle either a) road trip, hire a
van and we all hop in; b) cycle; c) catch another flightwe took c) – a flight to
Portland (I later found out there’s 2 Portland’s in the States!!! One is about an
18hr round trip flight – we got the other)

Portland we were headed – it was a rush and we just squeezed in the door with something
that resembled a boarding pass (some of us had one, Adam had none and I had a boarding
pass for the 2nd half of the flight Portland -> Seattle at a much later time) –
we sorted all that out at Portland.

Finally landed in Seattle after taking another plane with propellers (like the planes
that go Syd<->Canberra) and Adam was moaning at me as he’s scared of flying
and I was forcing him into another take-off and landing.

Meanwhile our original plane in San Fran was still on the tarmac doing not much at
all.

We finally arrived in Seattle at around 5pm – mission accomplished

or so we thought

Our luggage was sitting there getting dizzy – so now the big question is – Where is
it?

It’s now 11pm and no sign, don’t think she’s coming tonight and you may see me in
shirts that say “I love Seattle” or “Save the Super Sonics” you’ll know why 🙂

I just caught up with some of the BizTalk MVP Crew and it was great to see familiar
faces after a while.

My brain is fried and I’ve decided to hit the hay.

It’s good night from Me.

Static code analysis for BizTalk using BizTalkCop

by | Feb 28, 2009 | BizTalk Community Blogs via Syndication

The Problem
If you have been a BizTalk developer long enough, you will know that application lifecycle management has always been a pain point when it comes to managing BizTalk projects.
The Out-of-Box support available in BizTalk Server is not too great for things like automated builds, continuous integration, code analysis, code coverage, unit testing… I can go on and on, but I am sure you get the picture.
Thankfully, most of these are set to improve with the upcoming release of BizTalk Server i.e. BizTalk Server 2009, but there are still improvement areas that I hope the product group will address soon.
One of the other hard things (IMHO) that one needs to do early-on in a BizTalk project is to decide on the naming convention and projects structure for modular deployment. Sometime this is dictated by your customer and sometimes you just follow what has been decided by your company, simple isn’t it? Wish life was that easyJ.
In reality you will find that customers want you to use their own guidelines that are often not complete or not fully thought-out, and you end up using that and try to fill the gap or modify it by adding your own stuff.
Is this so bad?… not if you have gone through this exercise and got it frozen and signed off before you start your development / coding, else, this can turn out to be the cause for endless frustrations for you and your team.
This is especially true for BizTalk projects, where you know that small changes to things like namespaces or typenames can cause a rippling effect across the project, and you end up rebuilding / reconfiguring all of the artefacts.
I have been on multiple projects in the past where naming conventions changed almost every week while the project was halfway through the development phase, so this is not an uncommon thing; however you need to be aware of this and take measures to avoid getting into such situations.
Another challenge is in sticking to the guidelines or conventions that you have so painstakingly chosen and frozen. If you have a large team, you will often see that not all developers are fully aware of the guidelines and conventions that are being followed in the team, especially when developers are constantly moving in and out of the team.
Solution
So what can be done in such situations? One of the things you can do on your BizTalk project is to run a static code analyser as part of your daily builds. I have always been a huge fan of build automation and the niceties that come along with it like automated testing, static code analysis report, code coverage report etc. There is nothing that makes you happier than getting up in the morning gazing at your PDA / mobile device to see that there were no errors or build breaks on the nightly build, especially when you are the Dev Lead / Architect on the project.
You must be thinking… “all this is great… but isn’t this is a BizTalk Solution?… how do you automate checking naming conventions?… especially on things like orchestration shapes, map names, physical ports etc…”. well you are in luck, you can now do static code analysis on BizTalk solutions too, thanks to Elton Stoneman for this cool new tool/plug-in called BizTalkCop available on codeplex.
Check out the links:
http://geekswithblogs.net/EltonStoneman/archive/2008/11/14/introducing-biztalkcop.aspx
BizTalkCop is essentially a set of FxCop rules that will allow you to inspect BizTalk assemblies for structure and naming convention of your BizTalk artefacts such as Orchestrations, Pipelines, Schemas, Maps etc. It can even look into the deployed solutions inside BizTalk Server (using BizTalk Object Model and BizTalkMgmtDB) to validate the names of your physical ports and receive locations on your deployed BizTalk Server application.
If you are already familiar with FxCop, BizTalkCop provides you the same experience since it is just an extension of the FxCop ruleset.
BizTalkCop is currently is release 1.0 and contains a set of rules based on Scott Colestock’s naming conventions, but they’re (mostly) configurable so you can modify them to suit your own standards. You can create your new custom rules as well, since the full source code is provided with base classes and frameworks.
Another thing to note is that since BizTalk (2006/R2) projects are not integrated with Code Analysis tool in Visual Studio 2005, you need to download and install FxCop separately before installing BizTalkCop (even if you are using the Team Suite edition of Visual Studio 2005). I have not tried it with BizTalk 2009 Beta yet since I have it on Professional edition of Visual Studio 2008 installed on my VPC (Code Analysis is available only on Visual Studio Team Suite).
The link above provides instructions on how to install and configure BizTalkCop so I am not going to detail it out here.
Demo – The Bad Project
Let me illustrate the usefulness of BizTalkCop by running it on a badly done solution. I created a very simple BizTalk application a la “Hello World” in BizTalk (I call it the ’BadProject’) without giving much attention to the project structure or naming convention (just used the default names, like we do most of the time). The application basically receives an inventory replenishment request; the request is transformed into a purchase order request and is sent out. For simplicity sake, both the receive port and the send port use FILE adapters.
So essentially, my BizTalk project consists of a single BizTalk project within which there are two Schemas, a Map and an Orchestration as shown in the solution explorer screenshot below:
The orchestration, transformation and schemas are all very elementary as shown below (notice the naming of the artefacts highlighted).
After I have built and deployed the solutions, I fire up FxCop, load the target BizTalk application assembly and hit the Analyze button. I selected only BizTalkCop rules and unchecked others
Note: You also need to make sure that BizTalkCop is configured to point to the right BizTalk application so that it can validate the application level artifacts like the physical post names etc.
You see from the screenshots that many errors (about 28 of them) are generated for such a simple application.
Here are the errors:
The errors are pretty self explanatory so I don’t want to go through each of them.
1. Port names should be prefixed with their direction – start ‘ReceivePort1’ with ‘Receive.’
2. Port names should be prefixed with their direction – start ‘SendPort1’ with ‘Send.’
3. Receive Location names should be prefixed with their Receive Port name – start ‘Receive Location1’ with ‘ReceivePort1’
4. Receive Location names should be suffixed with their transport type – end ‘Receive Location1’ with ‘.FILE’
5. Artifacts should be declared in modules with the correct suffix – consider module name ‘BadProject.Schemas’
6. Schema names should end with the data format. Format: ‘inventoryRequest’ is unknown
7. Schema names should begin with the root node. Start: ‘inventoryRequest’ with: ‘Root’
8. Artifacts should be declared in modules with the correct suffix – consider module name ‘BadProject.Transforms’
9. Map names should have the format “SourceSchema_DestinationSchema”
10.Artifacts should be declared in modules with the correct suffix – consider module name ‘BadProject.Orchestrations’
11.Orchestration members should be Camel cased. Replace Message name: ‘InvReqMsg’ with: ‘invReqMsg’
12.Orchestration members should be Camel cased. Replace Message name: ‘PurchaseOrderMsg’ with: ‘purchaseOrderMsg’
13.Orchestration members should be Camel cased. Replace Port name: ‘Port_1’ with: ‘port1’
14.Orchestration members should be Camel cased. Replace Port name: ‘Port_2’ with: ‘port2’
15.Orchestration shapes should be correctly named – replace ‘ConstructMessage_PurchaseOrder’ with ‘Construct_PurchaseOrderMsg’
16.Orchestration shapes should be correctly named – replace ‘Transform_InvToPurchaseOrder’ with ‘Transform_inventoryRequest_purchaseOrderSchema’
17.Orchestration Shapes should have the correct prefix – start ‘MessageAssignment_1’ with ‘Assign_’
18.Orchestration Shapes should have the correct prefix – start ‘Receive_InvRequest’ with ‘Rcv_’
19.Orchestration Shapes should have the correct prefix – start ‘Send_POMsg’ with ‘Snd_’
20.Orchestration types should be Pascal cased. Replace Port Type name: ‘PortType_1’ with: ‘PortType1’
21.Orchestration types should be Pascal cased. Replace Port Type name: ‘PortType_2’ with: ‘PortType2’
22.Orchestration Types should have the correct suffix – end ‘PortType_1’ with ‘PortType’
23.Orchestration Types should have the correct suffix – end ‘PortType_2’ with ‘PortType’
24.Orchestration Types should have the correct suffix – end ‘Port_1’ with ‘Port’
25.Orchestration Types should have the correct suffix – end ‘Port_2’ with ‘Port’
26.Artifacts should be declared in modules with the correct suffix – consider module name ‘BadProject.Schemas’
27.Schema names should end with the data format. Format: ‘purchaseOrderSchema’ is unknown
28.Schema names should begin with the root node. Start: ‘purchaseOrderSchema’ with: ‘Root’
You can also save the FxCop project and use it in the command line option to generate a pretty cool report as show below.
Demo – The Good Solution
Now I took the same solution and made changes by following the naming conventions and also restructuring the solution to be suitable for modular deployment. You see from the illustration below, I have split the application into three separate projects so that it is not only easier to maintain and saves time during development, but it is absolutely critical that it is broken in this manner from an administration point of view when the solution is in production. Any seasoned BizTalk developer / administrator will understand what I mean here, it’s a whole different topic and don’t want to get swayed into that.
You also see in the solution explorer above that the name of some of the artefacts have also been changed in accordance with the naming convention. For example, the schema names indicate the data format and the root node name of the schema. I also made sure that the physical ports confirm to the naming standards.
The orchestration, map and the schemas below show that changes have been made in the names of the artefacts.
You can see from the below screenshots that the errors have been reduced to just one error.
Conclusion
To sum it all up, I feel FxCop / BizTalkCop makes a great tool to have in your arsenal for that next BizTalk project you are waiting for or even in your current project. Saves you (or the SQA team) a lot of time from having to check for the naming conventions manually on a routine basis and provides you reports on demand.

It can be used both on the individual machines as well as on the build server. You can integrate it with your build scripts so that your application health report now also includes static code analysis among other things. This way you can be assured and can sleep peacefully knowing that the code drop you just sent to the customer conforms to all the naming conventions without having to manually review it at the eleventh-hour, not to mention the amount of time saved. I hope to see many more new rules and improvements from Elton in future releases of BizTalkCop.

Benny Mathew
Sr.Consultant, MGSI (Microsoft Global Services, India).

While I acknowledge that I am an employee of Microsoft., any & all views expressed in this article are mine and do not necessarily reflect the views of Microsoft.