Free Trial

Safari Books Online is a digital library providing on-demand subscription access to thousands of learning resources.

  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL
Help

6. BizTalk AppFabric Connect > Performing table operations in SQL Azure

Performing table operations in SQL Azure

Performing table operations in SQL Azure AppFabric Connect for Services also offers the possibility of connecting BizTalk with the cloud. It is possible to get connected with SQL Azure and call a stored procedure or perform a table operation. SQL Azure delivers cloud database services which are built on SQL Server technologies and is a component of the Windows Azure platform. Instead of storing or retrieving data from the on-premise SQL Server database, you can do it in the cloud. Benefits of SQL Azure are scalability and high availability because of elasticity to the cloud. With AppFabric Connect for Services, you are able to develop a hybrid BizTalk solution connecting with the cloud and on-premise systems. Getting ready Before you can try with use this recipe, you must have an account on Windows Azure and the SQL Azure database. Go to the SQL Azure Portal ( http://www.microsoft.com/windowsazure/sqlazure/) to get a SQL Azure database. You must also have Windows Azure AppFabric SDK version 1.0 (September update or later) and the BizTalk Server 2010 Feature Pack installed. Also, see the Installing the AppFabric Connect Feature and AppFabric Connect for Services recipe discussed earlier in this chapter. You will find SQL the Script for the SQL Azure Database and table and the BizTalk project (BTS.Cookbook.SQLAzure.TableOps), which you can use for reference, accompanied with this book. How to do it... You have to perform the following steps to be able to perform table operations in SQL Azure: Open Visual Studio.Create a New Project dialog box, select BizTalk Projects and in the middle pane, click to select Empty BizTalk Server Project. Give it a descriptive name and click on OK.Click to select the project in Solution Explorer, right-click to display the context menu and click to select Add | Generated Items to display the Add Generated Items dialog box. Select Consume Adapter Service and click on Add: a. Choose sqlBinding in the Select a binding section. b. Click on Configure and then click on the Security tab. In Client Credential Type, select UserName. c. Fill in the credentials for the account which has access to the SQL Azure database. d. Select the URI Properties tab and fill in the Server name as Initial Catalog. e. On the Binding tab, set UseAmbientTransaction to False. Setting it to True will elevate the transaction to MSDTC. This is not supported in SQL Azure yet (http://msdn.microsoft.com/en-us/library/ee336250.aspx). f. Click on OK and you will see a URI, as shown in the following screenshot: Click on Connect.Verify that the Consume Adapter Service dialog box is populated with the values, as shown in the following screenshot, and click on the OK button: The schemas and a custom binding file will be created.Right-click on the schema which has a name similar to TableOperation.dbo.CustomerAddress.xsd and click Generate Instance.Save the generated XML to the file.Modify the file, as shown in the following code snippet: <ns0:Select xmlns:ns0="http://schemas.microsoft.com /Sql/2008/05/TableOp/dbo/CustomerAddress"> <ns0:Columns>*</ns0:Columns> <ns0:Query></ns0:Query> </ns0:Select> Move the Update node in the schema above Select.Right-click on the schema which has a name similar to TableOperation.dbo.CustomerAddress.xsd and click on Generate Instance.Save the generated xml to the file.Modify the file, as shown in the following code snippet: <ns0:Update xmlns:ns0="http://schemas.microsoft.com/ Sql/2008/05/TableOp/dbo/CustomerAddress"> <ns0:Rows> <ns0:RowPair> <ns0:After> <ns1:FirstName xmlns:ns1="http://schemas.microsoft.com/ Sql/2008/05/Types/Tables/dbo">Jay</ns1:FirstName> <ns1:MiddleName xmlns:ns1= "http://schemas.microsoft.com/Sql/ 2008/05/Types/Tables/dbo">K.</ns1:MiddleName> <ns1:LastName xmlns:ns1="http://schemas.microsoft.com/ Sql/2008/05/Types/Tables/dbo">Adams</ns1:LastName> <ns1:FullName xmlns:ns1="http://schemas.microsoft.com/ Sql/2008/05/Types/Tables/dbo"> Jay K.Adams</ns1:FullName> <ns1:CompanyName xmlns:ns1= "http://schemas.microsoft.com/Sql/2008/05/ Types/Tables/dbo">Bicycle Specialists</ns1:CompanyName> <ns1:FullAddress xmlns:ns1= "http://schemas.microsoft.com/Sql/2008/05/ Types/Tables/dbo">Blue Ridge Mall</ns1:FullAddress> <ns1:PostalCode xmlns:ns1= "http://schemas.microsoft.com/Sql/2008/05/ Types/Tables/dbo">64106</ns1:PostalCode> <ns1:City xmlns:ns1="http://schemas.microsoft.com/Sql/ 2008/05/Types/Tables/dbo">Kansas City</ns1:City> <ns1:StateProvince xmlns:ns1= "http://schemas.microsoft.com/Sql/2008/05/ Types/Tables/dbo">Missouri</ns1:StateProvince> <ns1:Country xmlns:ns1= "http://schemas.microsoft.com/Sql/2008/05/ Types/Tables/dbo">United States</ns1:Country> </ns0:After> <ns0:Before> <ns1:FirstName xmlns:ns1= "http://schemas.microsoft.com/Sql/2008/05/ Types/Tables/dbo">Jay</ns1:FirstName> <ns1:MiddleName xmlns:ns1= "http://schemas.microsoft.com/Sql/2008/05/ Types/Tables/dbo">K.</ns1:MiddleName> <ns1:LastName xmlns:ns1= "http://schemas.microsoft.com/Sql/2008/05/ Types/Tables/dbo">Adams</ns1:LastName> <ns1:FullName xmlns:ns1= "http://schemas.microsoft.com/Sql/2008/05/ Types/Tables/dbo">Jay K. Adamns</ns1:FullName> <ns1:CompanyName xmlns:ns1= "http://schemas.microsoft.com/Sql/2008/05/ Types/Tables/dbo">Bicycle Specialists</ns1:CompanyName> <ns1:FullAddress xmlns:ns1= "http://schemas.microsoft.com/Sql/2008/05/ Types/Tables/dbo">Blue Ridge Mall</ns1:FullAddress> <ns1:PostalCode xmlns:ns1= "http://schemas.microsoft.com/Sql/2008/05/ Types/Tables/dbo">64106</ns1:PostalCode> <ns1:City xmlns:ns1= "http://schemas.microsoft.com/Sql/2008/05/ Types/Tables/dbo">Kansas City</ns1:City> <ns1:StateProvince xmlns:ns1= "http://schemas.microsoft.com/Sql/2008/05/ Types/Tables/dbo">Missouri</ns1:StateProvince> <ns1:Country xmlns:ns1= "http://schemas.microsoft.com/Sql/2008/05/ Types/Tables/dbo">United States</ns1:Country> </ns0:Before> </ns0:RowPair> </ns0:Rows> Move the Update node back to its original place in the schema.Sign the solution and give it an appropriate application name.Build and deploy the solution.Open the BizTalk Administration Console and navigate to the application you just deployed.Right-click on the application and then select Import | Bindings.Go to the created binding file and click on Open.Navigate to the Send ports in Application and you will see a newly created Send port.Double-click on the Send port and then click on Configure.In the General tab, remove Update Operation in SOAP Action Header.In the Credentials tab, fill in the credentials for the account which has access to the SQL Azure database.Click on OK.Select Filters in the left pane of the Send port.Create the following filter: BTS.MessageType == http://schemas.microsoft.com/Sql/ 2008/05/TableOp/dbo/CustomerAddress#Select Click on OK.Create a new Send port similar to the created Send port from the imported binding file.In the General tab, put the following as a SOAP action header: <BtsActionMapping xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <Operation Name="Update" Action="TableOp/Update/dbo/CustomerAddress" /> </BtsActionMapping> In the General tab, fill in the same URI as in the other Send port.In the Credentials tab, fill in the credentials for the account that has access to the SQL Azure database.Click on OK.Create the following filter: BTS.MessageType == http://schemas.microsoft.com/Sql/2008/05/TableOp/dbo/CustomerAddress#Update Navigate to the Receive ports and create a new Receive port. Give it an appropriate name.Create a new receive location, select the File adapter and give it an appropriate name, and point to the folder from where it can pick up xml messages.Navigate to the Send ports and create a new Send port. Give it an appropriate name.Select the File adapter for this port and point to the folder where messages can be dropped.Add one of the following filters to this port: BTS.MessageType == http://schemas.microsoft.com/Sql/2008/05/TableOp/dbo/CustomerAddress#SelectResponse BTS.MessageType == http://schemas.microsoft.com/Sql/2008/05/TableOp/dbo/CustomerAddress#UpdateResponse or BTS.MessageType == http://schemas.microsoft.com/Sql/2008/05/TableOp/dbo/CustomerAddress#UpdateResponse Right-click on the application and click on Start.Drop a message for the selected operation in the folder and check the other folder for the result.You can do the same for the update operation message. How it works... The Consume Adapter Service is a metadata generation tool included in the WCF LOB Adapter SDK for using with BizTalk Projects. This tool can be used by adapter consumers in Visual Studio 2010 to browse (and search) metadata from the adapter and then generate XML Schemas for selected operations. Once you have started this tool, you can configure security, LOB connection strings, and adapter binding properties. To connect with SQL Azure, you have to choose sqlBinding, hence, SQL Azure is built on SQL Server technologies. You can choose to use an inbound or outbound contract type and select one or more operations. Based on your choice, you will see that the tool will generate schemas and either a WCF-Custom Send port binding information XML or a WCF-Custom Receive port binding information XML. In this recipe, you have created an XML schema for the Select and Update operation on the table in SQL Azure. The generated WCF-Custom Send port binding is imported to create a Send port for communication with SQL Azure. You have to create a Send port for each operation unfortunately, because multiple SOAP header actions result in a BizTalk runtime and not understanding which one to choose. In the schema, as shown in the following screenshot, you can see how messages with certain operations (Select, Update) are routed by BizTalk to SQL Azure and the results of the operations are routed back and end up as messages in the file folder: There's more... Besides operations performed directly on the table, it is also possible for instance, to call a stored-procedure in SQL Azure. It is explained in the document called BizTalk AppFabric Connect: WCF-Adapter Service Stored Procedure at http://soa-thoughts.blogspot.com/2011/03/biztalk-appfabric-connect-wcf-adapter.html. You can find a lot of information online for SQL Azure and a quick start to have some basic understanding is the wiki page called SQL Azure FAQ at http://social.technet.microsoft.com/wiki/contents/articles/sql-azure-faq.aspx. See also Refer to the Installing AppFabric Connect and AppFabric Connect for Services recipe discussed earlier in this chapter

  

You are currently reading a PREVIEW of this book.

                                                                                                                    

Get instant access to over $1 million worth of books and videos.

  

Start a Free Trial


  
  • Safari Books Online
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint