Wednesday, March 23, 2011

WCF-SQL Adapter Stored Procedure : SQL Azure

In previous post I explained how call stored procedure using WCF-SQL Adapter from BizTalk. I now want to do same thing, but instead of calling a stored-procedure in SQL Server I will call a stored procedure in SQL-Azure. If you want to do you will need a Windows Azure Account. If you do than you can sign in through Azure Management Portal and if you do not have SQL Azure database you can go database in portal and click Create a new SQL Database.

image

You will then go through set of steps (e.g. wizard), where you will have to select subscription and server, region (my case North Europe), credentials, firewall rules (I did not apply any), database name, edition (web), and size (1 Gb).

image

So I now have a database in the cloud and I can access it through SQL management studio 2008 R2 on my machine. That’s what I thought, but I got the message below stating I need a firewall rule.

image

This meant I had to go back to the portal and create a firewall rule for IP address of my laptop. I dropped the database and started over again. And tried again and yes I could access the database instantly.

image

Now I needed to create a database with same tables and stored procedure as on-premise version. In SQL Azure you can create a new database by right clicking database and click new database. A script will appear that looks like below that needs to be executed.

CREATE DATABASE ADAPTER_SAMPLES;

image

This may take a few seconds, but then you have a database. Next step was to execute the script for creating table (just Employee) and stored procedure. I right clicked tables and then new table a new query screen appears where I execute following statements:

CREATE TABLE [dbo].[Employee](
[Employee_ID] [int] IDENTITY(10001,1) NOT NULL,
[Name] [varchar](50) NOT NULL,
[DOJ] [datetime] NULL,
[Designation] [varchar](50) NOT NULL,
[Job_Description] [varchar](max) NULL,
[Photo] [image] NULL,
[Salary] [decimal](18, 2) NOT NULL,
[Last_Modified] [timestamp] NULL,
[Status] [int] NULL CONSTRAINT [DF_Employee_Status] DEFAULT ((0)),
[Address] [xml] NULL,
CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
(
[Employee_ID] ASC
))
GO

Now of course I needed to have some data in there.

INSERT INTO [Employee]([Name],[Designation],[Salary])VALUES('Jeff Price','Manager',500000)
INSERT INTO [Employee]([Name],[Designation],[Salary])VALUES('Don Hall','Accountant',40000)
INSERT INTO [Employee]([Name],[Designation],[Salary])VALUES('Keith Harris','Supervisor',300000)
INSERT INTO [Employee]([Name],[Designation],[Salary])VALUES('Jim Hance','Admin',200000)
INSERT INTO [Employee]([Name],[Designation],[Salary])VALUES('Andy Jacobs','Accountant',400000)

And the stored procedure by navigate to stored-procedure and right click new new stored procedure. I deleted the preformatted stuff en pasted the code below:

CREATE PROCEDURE [dbo].[ADD_EMP_DETAILS]
-- Add the parameters for the stored procedure here
@emp_name varchar(50),
@emp_desig varchar(50),
@salary decimal(18,2)

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
INSERT INTO [ADAPTER_SAMPLES].[dbo].[Employee]
([Name]
,[Designation]
,[Salary])
VALUES
(@emp_name
,@emp_desig
,@salary)
SELECT [Employee_ID] FROM Employee where [Employee_ID] = (select IDENT_CURRENT('Employee'))

END

GO

I am fairly new to SQL Azure so some of steps or things can be done differently. You can find many information online, like this MSDN article Getting Started with SQL Azure Development. Now that I have got things set up I now go through same steps to invoke the stored-procedure in SQL Azure using WCF-SQL Adapter.First I fire up VS2010 and create a new BizTalk project and then through add generated items I choose Consume Adapter Service.

image

I configured the URI accordingly,click Configure in security tab name of database administrator and then password, URI Properties the InitialCatalog Name and Server and you will get URI like:

mssql://kwtn4rghlk.database.windows.net//ADAPTER_SAMPLES?

Connect and category will appear. You will then select Procedure and add procedure and click OK.

image

Once that is done then binding and schema’s are generated.

image

After deploying I imported the binding file accompanied with this sample called WcfSendPort_SqlAdapterBinding_Custom.bindinginfo.xml. Send port will be created and only thing I had to do is adding filter to this send ports. If you go to generated send port and click configure for custom-adapter you will see in general tab and others the details.

image

One important things to be noted here is that in binding the useAmbientTransaction has to be set to false! If not you will error message below:

image

You will need to fill in credentials in credential tab! Next step is to add a filter for message type.

image

When this is done I created a receive location for incoming message that will look like this:

<ns0:ADD_EMP_DETAILS xmlns:ns0="http://schemas.microsoft.com/Sql/2008/05/Procedures/dbo">
<ns0:emp_name></ns0:emp_name>
<ns0:emp_desig></ns0:emp_desig>
<ns0:salary></ns0:salary>
</ns0:ADD_EMP_DETAILS>

And a send port that will send response message to a folder. Message that will placed in receive folder is:

image

As a result to follow message is returned:

image

When I run the follow T-SQL command in SQL Azure I get the following result:

image

So as you can see I am in there now.I have showed you a way to invoke a stored procedure in SQL Azure using BizTalk Server with a messaging solution. Cool stuff.

Technorati:

3 comments:

Anonymous said...

Mooi artikel !

Enige ionfo over de preformance ?
Hoe lang duurt een roundtrippie nu helemaal.

EN als je hen tegen een lokale DB aan draait ?

Best wel interessant om te weten.

Mayus said...

Hi, I like your post. In my situation, I met a problem for the SQL stored Procedures published as wcf service. I created orchestration, so I didnt need to set up the filters in the application. There is a stored procedure called "getClient", basicly it passes ClientID and ClientName as request, then the response should be ClientID, ClientName, ClientPhone, ClientEmail, ClientAddress. However, after I tested the wcf service thru soapui, it all worked.Then I added the wcf service as a reference into the visual studio project(C# code). When calling the method from the code, it always has an error says the stored procedure or function 'getClient' expects @ClientId, which was not supplied, then I checked the code, but the code looks right. Do you have any idea what's going on in my case?

Steef-Jan Wiggers said...

Hi,

Could you send your project to me SteefJan @ msn dot com. I can then see what is going on and helped you out better.