The World's Favorite Open Source GPL J2EE CFML Runtime Engine

BlueDragon Developer's Journal

Subscribe to BlueDragon Developer's Journal: eMailAlertsEmail Alerts newslettersWeekly Newsletters
Get BlueDragon Developer's Journal: homepageHomepage mobileMobile rssRSS facebookFacebook twitterTwitter linkedinLinkedIn

Open BlueDragon Authors: Reuven Cohen, Elizabeth White, Michael Sheehan, John Gauntt, Salvatore Genovese

Related Topics: Open BlueDragon Developer's Journal, ColdFusion on Ulitzer, SOA & WOA Magazine

BlueDragon: Article

Beyond SOA & Web Services - ColdFusion / .NET Integration

As both a .NET programmer and ColdFusion developer, I always wondered how I could leverage the world of .NET in ColdFusion

To unregister an assembly, just add the /unregister (or /u) switch to the end of the command above. The registration story isn't complete without mentioning that the recommended way of doing this is to create a strong name for your assembly (via the sn.exe utility from .NET), and to put it in the Global Assembly Cache (GAC) - although for testing purposes you can forget about that. If the assembly isn't located in the GAC, you must add the /codebase switch when running the RegAsm.exe tool; this will put the correct path to your DLL in the registry so that COM clients will instantiate your objects without any problems. Listing 5 is a complete example of a .NET class that can be used from any COM client.

Coming back to ColdFusion, all you have to do is use CFOBJECT on the new COM object. One testing idea is to CFDUMP the variable created via CFOBJECT so you can see that the .NET class is fully visible from ColdFusion; you'll get a list of all of the methods and properties for that component (along with the usual stuff inherited from the IDispatch and IUnknown interfaces, such as GetIDsOfNames, Invoke, AddRef, and so on).

There's more than meets the eye in the .NET-COM story, a subject that requires its own series of articles to be discussed properly. Anyway, this should be enough information to get you started, if this is the road you want to take.

Writing CFX Tags in Managed C++
For the next stop in the ColdFusion/.NET interoperability game we need to move further down the list of features offered by ColdFusion. One of the many ways through which the performance of the ColdFusion application server can be improved is to use that special API for creating ColdFusion Extension tags (CFX). They can be written in C++ or Java and must be registered for use via the ColdFusion Administrator.

Java CFX tags are in fact components that must target the Java platform (obviously), and, as such, this language isn't a valid option if you need access to .NET. While Visual J# can be more or less considered a variant of Java, it still remains a language that targets the .NET platform, and the resulting assemblies can't be used as Java CFX tags. So what's left for you is C++.

CFX tags written in C++ are dynamic libraries (DLLs) that must export a special function that will be called by ColdFusion whenever the tag is used from a CFML page. ProcessTagRequest is the name suggested by the ColdFusion Administrator for this function, but any name will do as long as it's a valid C++ identifier. Beyond that, you can implement whatever logic you want in the CFX tag.

The best news when writing a CFX in Visual C++ is that you can combine managed code with the more mundane version of the language. The whole world of .NET is open for you to explore in a CFX tag - the only catch is that you have to compile your Visual C++ project with support for the Common Language Runtime (CLR), but this is already turned on for you when the solution is created.

The fine print in this story is that you need to convert everything that's expressed as .NET variables to string values whenever you need to return such variables back to ColdFusion. However, this isn't a real problem, since all .NET constructs ultimately derive from the System.Object class, which in turn has a handy method called ToString that you can use as is, or override for new .NET objects. You'd want to consult the code in Listing 6 for a C++ sample tag to see this in action.

Of course, the CFX tag can be designed in such a way that the .NET Framework will be used for its internal workings only, and never for building return variables. The overhead mentioned above won't be a bother in this case.

CLR Programming in SQL Server 2005
The last integration option comes from an unexpected source: the database-related tags from ColdFusion, CFQUERY, and CFSTOREDPROC - as well as their associated tags. Actually, this is possible only when using SQL Server 2005 as a back-end database server.

SQL Server supports the creation of various database objects using the .NET Framework - a feature better known as CLR integration. Be aware though that the default installation of SQL Server 2005 sets the CLR integration flag to disabled and it should be activated explicitly. There are two methods to do this. First there's the Transact-SQL way via the sp_configure system stored procedure. When called with no arguments, it will return a list with all configuration options in SQL Server, along with their current state. The option that should be modified is clr enabled, as shown in the following snippet:

sp_configure 'clr enabled', 1

The call to reconfigure is mandatory to apply the changes done via sp_configure. To disable the CLR integration again, use zero for the second parameter of the stored procedure.

The second method is to open up the Surface Area Configuration tool found in the All Programs/Microsoft SQL Server 2005/Configuration Tools folder. Click on Surface Area Configuration for Features for the computer you want to configure, and locate the CLR Integration option for your SQL Server 2005 instance. Check Enable CLR integration here and then apply the changes.

Coming back to programming, the .NET namespace that contains everything that's needed to build managed database objects in SQL Server 2005 is Microsoft.SqlServer.Server. These objects are stored procedures, triggers, user-defined types, functions, and aggregates - most of them being usable from ColdFusion via CFQUERY or CFSTOREDPROC.

I'm going to show you how to build a managed stored procedure in Visual Studio; the other CLR objects can be created more or less in the same way.

The first step is to create a database project in Visual Studio in your language of choice. Once created, you can add any kind of managed database object to this solution. All of them follow the same pattern: they are created in a separate class, and the actual implementation is found in a public static method (Shared in Visual Basic .NET).

Note that a special attribute decorates this static method. For stored procedures it's SqlProcedure, for user-defined functions it's SqlFunction, and so on. They're added using the full .NET namespace path (Microsoft.SqlServer.Server.SqlProcedure, for example), but you can safely remove the root if you want (as long as you don't forget to add a using or Import directive at the beginning of the file).

The most important object you'll be using here is SqlContext. It provides access to a SqlPipe object (via the Pipe property), which in turn can be used to send some data back to the caller:

public partial class StoredProcedures {
    public static void DotNetStoredProcedure() {
       SqlContext.Pipe.Send("String from a .NET stored procedure");

Of course, the sample above is sending back just a simple string, but you also have methods for creating and returning result sets from a stored procedure. Listing 7 shows you how it's done; it will return a result set containing all the languages supported by the .NET Framework (that is, the ISO codes), along with their English and native names.

Once compiled, the resulting assembly must be deployed on SQL Server 2005. This can be done by Visual Studio for you (go to Build/Deploy Solution in the menu), or manually via SQL.

This last method involves two separate statements: one for registering the assembly with SQL Server 2005 (see the documentation on CREATE ASSEMBLY), and the other for creating the stored procedure or whatever other managed object needs to be deployed (using the CREATE PROCEDURE/FUNCTION/TRIGGER/TYPE/AGGREGATE family of statements) - you use the appropriate DROP command to get rid of them from the database. Having done all this, it's just a matter of CFQUERY/CFSTOREDPROC on the ColdFusion side to actually use them in your project. From a ColdFusion point-of-view, you're consuming database objects, and that's all. Everything is transparent to you.

There are lots of options when considering using ColdFusion and .NET together. This article discussed some of them; other possibilities may still exist, of course. For example, using CFEXECUTE to run an external application (which can be a .NET one, too), but this method has its own risks and I didn't bother to bring it to the table.

By now you saw that I didn't mention anything about New Atlanta's BlueDragon for .NET. This is a perfectly valid option, too, and you're free to try it, if this is your application server of choice. In fact, most methods described here apply to BlueDragon as well (the obvious exception being event gateways, which aren't supported on BlueDragon).

There's more to be said for each of the techniques described in this article. Still, I'm sure you have enough to start thinking about how can you extend ColdFusion to the .NET world.

More Stories By Catalin Sandu

Catalin Sandu is a software developer at RomSoft ( and has 10 years of experience. He is both a Microsoft Certified Professional (on C++ and .NET), and an Advanced ColdFusion MX 7 Developer. Catalin is also a member of the British Computer Society since 2005.

Comments (0)

Share your thoughts on this story.

Add your comment
You must be signed in to add a comment. Sign-in | Register

In accordance with our Comment Policy, we encourage comments that are on topic, relevant and to-the-point. We will remove comments that include profanity, personal attacks, racial slurs, threats of violence, or other inappropriate material that violates our Terms and Conditions, and will block users who make repeated violations. We ask all readers to expect diversity of opinion and to treat one another with dignity and respect.