DBFunktoids - SQL Server Stored Procedure Functoid / Extractor Functoids
DBFunktoids is a collection of three functoids that enable the invocation and manipulation of SQL Server 2000 / 2005 stored procedures and their results. You can find a zip with the project and a compiled assembly at the end of this article.
DBFunktoids were written for BizTalk 2006 and the .NET Framework 2.0. If there is enough interest, I can port the project back to BizTalk 2004 (it should pretty much be code copy with a replacement of a Dictionary Generic with a Hashtable).
DBFunktoids are described below.
Stored Procedure Runner
The Stored Procedure Runner executes SQL Server stored procedures and provides the following capabilities:
Can call parameter-less stored procedures.
Supports procedures with up to 9 parameters (can be easily tweaked in source).
Stored procedure parameters can be constants or supplied from a source field.
Values are retrieved using the Value Extraktor functoid.
SQL Exceptions are trapped and stored for retrieval using the Error Extraktor functoid.
To use the Stored Procedure Runner, supply the appropriate number of arguments. If your stored procedure has no parameters, you only need to supply two arguments; if it has one or more parameters, you must supply at least four arguments:
Arg# |
Argument |
Description |
Example |
1 |
Database connection String |
Connection string for database server |
Persist Security Info=False;Integrated Security=SSPI;Data Source=myserver;Initial Catalog=AdventureWorks |
2 |
Stored procedure name |
Name of the stored procedure to invoke |
uspGetEmployeeManagers |
3 |
Parameter names |
Pipe-delimited (“|”) list of stored procedure parameters |
@EmployeeID|@LastName |
4 |
Parameter 1 value |
First parameter value. You must have at least one parameter value. |
102 |
n |
Parameter n |
Subsequent parameters. These are optional and if not set will be NULL. |
Benny |
If your stored procedure has parameters, the functoid expects you to supply at least one value even though it will automatically supply NULL values for all other parameters. If you want to call a procedure with null parameter values, write a wrapper proc with no parameters and call that instead.
Value Extraktor
The Value Extraktor retrieves a value corresponding to a column name from the first row of records returned by the stored procedure. It takes two arguments:
Arg# |
Argument |
Description |
Example |
1 |
Output from Stored Procedure Runner |
Guid associated with the stored proc call and results |
|
2 |
Column name |
Name of the column from which to retrieve a value; if the column doesn’t exist, an empty string is returned |
LastName |
Note that the current implementation does not capture return values from the stored procedure nor does it handle out parameters. One potential solution is to include these values in the result set; another is to modify the source to handle queries differently. If there are enough complaints, I can change it.
Error Extraktor
The Error Extraktor returns either an error message if there was an error in executing the stored procedure or an empty string if there was not (or the results cannot be found). It takes one argument:
Arg# |
Argument |
Description |
Example |
1 |
Output from Stored Procedure Runner |
Guid associated with the stored proc call and results |
To Install
To install:
Install the DBFunktoids.dll assembly into the Global Assembly Cache: gacutil –if DBFunctoids.dll.
Copy the DBFunktoids.dll into the <biztalk root>\Developer Tools\Mapper Extensions directory.
From Visual Studio 2005, click Tools | Choose Toolbox Items, click the Functoids tab, then click Browse to browse to and select the DBFunctoids.dll. On the functoid screen, scroll until you see the three new funktoids and then click the check box to put them on the Database Functoids toolbox palette.
Enjoy.
[updated 5/26/2006: fixed bug in source]