In a post yesterday I should a way to invoke a stored procedure in SQL Azure using WCF-SQL Adapter. There is another to invoke a stored procedure using AppFabric Connect for Services functionality in BizTalk Server 2010. This new feature feature brings together the capabilities of BizTalk Server and Windows Azure AppFabric thereby enabling enterprises to extend the reach of their on-premise Line of Business (LOB) systems and BizTalk applications to cloud. To be able to bridge the capabilities of BizTalk Server and Windows Azure AppFabric you will need Biztalk Server 2010 Feature Pack.
In this post I will show how to leverage BizTalk WCF-SQL Adapter to extend reach of stored procedure to the cloud. First I will create a service that will expose the stored-procedure. I create a VS Studio project and select WCF Adapter Service. Steps that follow are also described in an article called Exposing LOB Services on the Cloud Using AppFabric Connect for Services. I skip first page concerning and go to choose the operations to generate a service contract page.
I choose local database, stored procedure ADD_EMP_DETAILS (same as in previous posts) and click Next. In AppFabric Connect page, select the Extend the reach of the service on the cloud checkbox. In the Service Namespace text box, enter the service namespace that you must have already registered with the Service Bus.
Next step is configure the service behavior. You will have to configure the service behavior (for both on-premises and cloud-based services) and the endpoint behavior (for only endpoints on the Service Bus).
I enabled the EnableMetadatExchange to True, so the service metadata is available using standardized protocols, such as WS-Metadata Exchange (MEX). I not using security features as in certificates so the UseServiceCertificate is set to False. I enabled EndpointDiscovery, which makes the endpoints publicly discoverable. Next page is around configuring endpoints.
I accepted the the defaults for the on-premises endpoint and focus on configuring the Service Bus endpoints. I select netTcpRelayBinding for which the URL scheme is sb and set EnableMexEndPoint to True. Rest I accepted default values. After configuration you will have to click Apply. Then click and final screen will appear.
Finish and the wizard creates both on-premise and Service Bus endpoints for the WCF service.
Next steps involve publishing the service. In the Visual Studio project for the service, right-click the project in Solution Explorer and click Properties. On the Properties page, in the Web tab, under Servers category, select the Use Local IIS Web Server option.The Project URL text box is automatically populated and then click Create Virtual Directory.In Solution Explorer, right-click the project again and then click Build. When service is build you will have to configure it in IIS for auto-start. By right clicking the service, Manage WCF and WF Service and then configure you might run into this error (which you can ignore a this element is not present in intellisense).
In Auto-Start you can set it to enable. Next step is to verify if the Service Bus endpoints for the WCF service are published. You will have to view all the available endpoints in the Service Bus ATOM feed page for the specified service namespace:
https://<namespace>.servicebus.windows.net
I typed this URL in a Web browser and saw a list of the all the endpoints available under the specified service bus namespace. To be able to consume the WCF service to able to invoke the procedure you will need to build a client. I create a Windows Application to consume WCF Service, so inside my project I created a new project a Windows Forms Application and added a service reference using the URL for the relay metadata exchange endpoint.
In ServiceBus Endpoint the RelayClientAuthentication was set to RelayAccessToken, which means that the client will need to pass the authentication token to authenticate itself to the Service Bus. Therefore you will have to add following section in app.config.
<behaviors>
<endpointBehaviors>
<behavior name=”secureService”>
<transportClientEndpointBehavior credentialType=”SharedSecret”>
<clientCredentials>
<sharedSecret issuerName=”<name>” issuerSecret=”<value>” />
</clientCredentials>
</transportClientEndpointBehavior>
</behavior>
</endpointBehaviors>
</behaviors>
You must get the values for issuerName and issuerSecret from the organization (e.g. your Azure account) that hosts the service. The endpoint configuration in app.config has to be changed to this:
<endpoint address=”sb://contoso.servicebus.windows.net/Procedures_dbo/”
binding=”netTcpRelayBinding” bindingConfiguration=”Procedures_dboRelayEndpoint”
contract=”ServiceReferenceSP.Procedures_dbo” name=”Procedures_dboRelayEndpoint” behaviorConfiguration=”secureService“/>
Code for making this work (implementation of invoke button).
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using ExposeSPCloudTestClient.ServiceReferenceSP;
namespace ExposeSPCloudTestClient
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void btnInvoke_Click(object sender, EventArgs e)
{
int returnValue = -1;
Procedures_dboClient client = new Procedures_dboClient(“Procedures_dboRelayEndpoint”);
client.ClientCredentials.UserName.UserName = “sa”;
client.ClientCredentials.UserName.Password = “B@rRy#06”;
try
{
Console.WriteLine(“Opening client…”);
Console.WriteLine();
client.Open();
client.ADD_EMP_DETAILS(“Steef-Jan Wiggers”, “Architect”, 20000, out returnValue);
}
catch (Exception ex)
{
MessageBox.Show(“Exception: ” + ex.Message);
}
finally
{
client.Close();
}
txtResult.Text = returnValue.ToString();
}
}
}
Query employee table before execution of code above results as depicted below:
After invoking stored-procedure through consuming the service the new record is added.
As you can see I am added to employees table through adapter service called by the client.As you can see there is another way of invoking a stored-procedure making use of BizTalk AppFabric Connect Feature.
