Follow up to ESB Guidance Training Class

by | Apr 17, 2008 | BizTalk Community Blogs via Syndication

We had a great day yesterday at the Microsoft Canada office with over 50 people attending our ESB Guidance training course.  It was great to chat with so many great BizTalk developers and we had a ton of great questions.  A lot of people were interested in getting a copy of the content we presented, so here’s a link. http://www.peterkelcey.com/downloads/Public_ESBG_Course_Module_1.zip


Thanks to everyone that attended!


Cheers and keep on BizTalking


Peter

Database lookup functoid with caching feature

by | Apr 17, 2008 | BizTalk Community Blogs via Syndication

Nor the out-of-the-box Database lookup functiod or the XRef functoid come with any caching capabilities, which might produce a tremendous amount database traffic, and eventually cause transformations to run poorly. If you are repeatedly executing similar queries you might gain on using these functoids instead.

The ExecuteQuery and ExtractData works pretty much like the corresponding functoids that ships with BizTalk. However these functoids caches the result, and will therefor prevent the same query from being executed more than once.

 

Blogical.Shared.Functoids.ExecuteQuery

This functoid can be used like the Database lookup functoid that ships with BizTalk. It will execute a query against a given database and cache the result using the System.Web.Caching.Cache with a sliding expiration of one minute. This enables the functoid to reuse the data across mapping boundaries, executed on the same host. In other words the same resultset can be used even from within different mapping transformations.

The result from the executed queries are stored in a HashTable, where the key is the concatenation of the database connection string and the actual SQL query. Its value is the System.Data.DataTable returned from executing the query or stored procedure. This makes it possible to query for more then one row, which you may later extract using the ExtractData functoid. You should use this approach with caution; -however this is where you get the best performance effect.

parameters Type Optional Description
1 String  

The connection string or a name of a connection string in the BTSNtSvc.exe.config file. Samples:

Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;
or
config://MyConnectionString
2 Boolean   Indicates whether to search for cached data or not
3 String   The name of the source that is providing the data (table or stored procedure)
4 String X Column name or stored procedure parameter
5 String X Lookup value

The functoid can take a maximum of 6 pairs of either [column name and value] or [sp parameter, parameter value].

 

Blogical.Shared.Functoids.ExtractData

This functiod is used together with the ExecuteQuery functoid, and functions similar to the Value Extractor functiod that ships with BizTalk. The ExtractData functiod gets the value from the result of the query executed by the ExecuteQuery functoid. The optional Column/Value pair can be used when the result from the ExecuteQuery functoid holds more than one row.

Parameters Type Optional Description
1 String   Link from the ExecuteQuery functoid
2 String   The name of the column from which you like to extract its value
3 String X Column name to filter *
4 String X Column value filter *

The functoid can take a maximum of 6 pairs of either [column name and value] or [sp parameter, parameter value].

* When the optional parameters are used the Execute Query functoid is forced to have the whole query result cached.

I share any creds (or criticism) with Johan Hedberg and Niklas H%u00e4ggstr%u00f6m

All downloads are available through CodePlex.

Execute SQL Query from within a C# function

by | Apr 17, 2008 | BizTalk Community Blogs via Syndication

I don’t know how many times I’ve had to execute a stored procedure or a SQL statement from within a BizTalk map or within a C# class, so I’m putting this entry as a reminder to myself of how to do it.

// 1. Create your SQL Connection
SqlConnection conn = null;

// 2. Create and open a connection object
conn = new SqlConnection(“Connection String Goes Here”);

// 3. Open the Connection
conn.Open();

// 4. Create the SQL Command and assign it it a string
string strSQLCommand = “SELECT * FROM TABLE”;

// 5. Execute the SQL Command
SqlCommand command = new SqlCommand(strSQLCommand, conn);

// 6. Use ExecuteScalar() to return the first result
string returnvalue = (string)command.ExecuteScalar();

// 7. Close the Connection
conn.Close();

// 8. Return the Value
return returnvalue;

Get the Original File Name from either the HIPAA or BaseEDI adapters

by | Apr 17, 2008 | BizTalk Community Blogs via Syndication

One of the properties you’ll quickly wish you had using either the BizTalk HIPAA or BaseEDI adapters is the original file name of the message with which you are dealing. We’ve all searched for BTS.ReceiveFileName to no avail.

Here I’m showing you that there is no BTS.ReceiveFileName:

We’ll I’ve decided to build a C# class with two functions that will extract that for you easily.

The class I created has two static overloaded functions 1: GetBaseEDIOriginalFileNameFunction, and 2: GetHIPAAOriginalFileNameFunction.

Simply pass the <yourmessage(BTS.MessageID)> and (optionally) the Server where your HIPAA or BaseEDI databases are located into the function and you’ll have your original filename.

Email me if you’d like a copy

Announcinganother new ’edition’ to the BizTalk family

by | Apr 17, 2008 | BizTalk Community Blogs via Syndication

Today at the RFID Journal Live conference in Las Vegas, we announced Microsoft BizTalk RFID Mobile -a light weight application that works on mobile RFID devices, and will enable delivery of relevant information from these devices to central business process systems. We’ll deliver this to our customers at the end of the calendar year, 2008. We have a private beta running today.


When we look at the RFID market, it’s truly an example of a technology that’s ’come of age’. Just a couple years back, RFID tags and mobile readers were too expensive to purchase and implement, making reasonable ROI out of reach for most customers. Today, tags cost just pennies, and mobile readers work well with central business process management systems, empowering people to make on-the-spot business decisions that can truly impact the bottom line. At the same time, this important information gets communicated back to the guys in the office to monitor trends and make adjustments-it’s the type of capability most businesspeople have dreamed of-until now.


And none of this would have been possible without the ecosystem-partners across the industry have worked together to come up with standards, and to integrate mobile hardware with central infrastructure, tags and software alike. We’re seeing results from this effort, and Microsoft is particularly happy to be working with several of our hardware partners on BizTalk RFID Mobile integration. Here’s what a few of them had to say:


“BizTalk RFID Mobile represents an exciting opportunity for Motorola to build on our track record of delivering innovative products to meet customer needs,” said Joe White, vp RFID business development, Motorola Enterprise Mobility.  “Motorola is actively partnering with Microsoft so that our customers will be able to reliably and seamlessly run applications on BizTalk RFID Mobile.”


“As a committed leader in the application of mobile RFID, we believe that Microsoft’s BizTalk RFID Mobile will help accelerate the adoption of RFID by easing integration,” said Intermec Director of RFID Business Strategy, Chris Kelley.  “With the release of the Microsoft BizTalk RFID Mobile, Microsoft partners will have a unified interface to all Intermec mobile and fixed readers, as well as our RFID-enabled printers.”


“We’re committed to working with Microsoft towards the integration of our rugged mobile computing devices with Microsoft’s innovative BizTalk RFID Mobile platform. Compatibility with Microsoft’s BizTalk RFID platform will offer our customers an unprecedented ability to leverage RFID applications throughout their enterprise” said Aneline Ghita, RFID Project Manager, Global Solutions Group, Psion Teklogix.


“Samsung is excited to support BizTalk RFID Mobile and see this innovative product drive a evolution in the RFID market,” said Chungkoo Yoon, Vice President Samsung.


“Visibility of edge process is critical business decision, we are glad to announce that unitech readers now support BizTalk Mobile. Microsoft is once again making it simple and easy to create solutions to empower the mobile worker to act on real world visibility. Together with Microsoft BizTalk & Unitech,  the industry now has a best-of-breed platform to build and deploy these real time solutions. And as a core partner of Microsoft BizTalk RFID, we are implementing Microsoft BizTalk RFID and Unitech RFID readers for Unitech RFID Reader Inventory & Shipping System.” said Judy Huang, Global RFID Director of Unitech.