Legacy integration with SQL 2005 CLR Table Value Functions
Here's the deal. I'm working on a web project that needs to access legacy data (real time) through a service layer up to a series of web components. The legacy system happens to be an Alpha, running OpenVMS with an old database format called RMS. We have an database driver package for this system (called ConnX), and it runs pretty well.
The issue is that I'd like to encapsulate the details of the legacy integration from the service layer. You see, the legacy system will be going away sometime in the near future, and over the course of time, we'll be migrating the legacy data onto the MS SQL platform. So, our team decided that we want to minimize the number of components that touch the legacy system, thus minimizing the effort of the future migration.
We decided (since we're using MS SQL 2005) to see about writing some C# code to run in the SQL CLR engine to perform the job of encapsulating queries to the legacy database. We thought we'd create some .NET 2.0 assemblies, hook them up to SQL 2005 stored procedures and user defined functions, and use them like any other stored SQL code. We've found this to be a very successful strategy. Now, we have user defined table value functions that are encapsulating calls through an OLEDB driver to the legacy database, and the better news is: we can perform SQL JOINs between native MS SQL tables and legacy table data in real time!!!
So that's the back story. Here's how to actually do it:
(In summary, create a .NET Assembly that contains one or more SQLFunction-compatible methods, and then register the assembly and the individual methods with the SQL 2005 server.)
*** Step 1: Create a SQL Database Project ***
In Visual Studio 2005, create a new project (Visual C# -> Database -> SQL Server Project).
Choose, or create a database connection reference. (This can be your local development SQL Express server for development purposes. Or, you can point it to your integration or team database. The automatic-deployment feature only seems to work well for me when I point it to my local SQL server on my development PC.)
In the Solution Explorer, Add a User-Defined Function. (Add -> User-Defined Function) and name the class (I called mine "LegacySQLFunctions.cs", and I renamed my project to "LegacySQLFunctions").
It will generate some code like this:
|
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class LegacySQLFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString LegacySQLFunctions()
{
// Put your code here
return new SqlString("Hello");
}
}; |
*** Step 2: Program the C# method for the SQL Table Valued Function***
I think of this kind of like the ASP.NET concept of a code-behind module. This assembly is the .NET code behind for the SQL function I need.
To create a SQL Function that returns table values rather than scalar values, you have to do a few extra things:
- Edit the method attributes to specify the DataAccess, TableDefinition, and FillRowMethodName.
- Change the method to return an IEnumerable type.
- Write a separate method to parse the individual IEnumerable members into the defined output table columns.
The idea behind the IEnumerable return type and the FillRowMethodName attribute is that the SQL CLR engine is going to call the main method for the SQL Function, expect to receive an enumerable object, will then walk the enumeration calling the method specified in the FillRowMethodName attribute to parse each record into the expected columns specified for output. Here's how mine is structured:
|
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections;
using System.Data.OleDb;
namespace ACME.TheApp.MyPortal
{
public partial class LegacySQLFunctions
{
//--- Define a main entry point for a SQL 2005 User-Defined-Function that returns a TABLE value type ---
[SqlFunction(
FillRowMethodName = "ParseUserDetailRow",
TableDefinition = "UserAccount NVARCHAR(10), FirstName NVARCHAR(14), LastName NVARCHAR(14), DateOfBirth DATETIME",
DataAccess = DataAccessKind.Read
)]
public static IEnumerable GetUserDetails( string userAccountLike )
{
// Put your legacy integration code here. It should query your legacy database and construct any sort
// of IEnumerable object for return. In my case, I used the Rows property of DataTable as the return value.
// By the way, the legacy connection string, legacy user and legacy password should be saved
// in a configuration table somewhere else on the SQL server, retrieved and cached. But for simplicity of
//
string legacyUser = "imNotTellingYou";
string legacyPwd = "StillNotDumbEnoughToShowThisInABlog";
string legacyConnectString = string.Format(
@"provider=connx ole db provider; Data Source=C:\Connx32\UTILS\legacy.CDD;User ID={0};Password={1};",
legacyUser, legacyPwd);
string queryString = "SELECT USRACCT, FNAME, LNAME, DOB "
+ "FROM SYSUSR "
+ "WHERE USRACCT LIKE '" + userAccountLike + "'";
using (OleDbConnection connection = new OleDbConnection(legacyConnectString))
using (OleDbCommand cmd = new OleDbCommand(queryString, connection))
using (OleDbDataAdapter da = new OleDbDataAdapter(cmd))
{
DataTable dt = new DataTable();
da.Fill(dt);
return dt.Rows; // the Rows collection implements IEnumerable, so I am safe to return this to SQL.
}
}
//--- Define the call-back function that will parse each row in the enumeration ---
public static void ParseUserDetailRow( object item, out string userAccount, out string firstName,
out string lastName, out DateTime dateOfBirth )
{
DataRow row = (DataRow)item;
userAccount = row["USRACCT"].ToString();
firstName = row["FNAME"].ToString();
lastName = row["LNAME"].ToString();
dateOfBirth = Convert.ToDateTime( row["DOB"]);
}
};
} |
*** Step 3: Register the assembly and function with SQL ***
Now, I copy the DLL I've built over to my SQL 2005 server. (In my case, I created a folder on my SQL server named C:\Components\SQLCLR\ into which my .NET assemblies go.) I copy the bin\Debug\LegacySQLFunctions.dll file to the server in C:\Components\SQLCLR\.
Next, I open up SQL 2005 Server Management Studio, connect to the database I'm going to keep the Legacy functions in, and open a new query window.
I write and execute the following T-SQL commands:
|
IF EXISTS (SELECT * FROM sys.assemblies WHERE [name] = 'LegacySQLFunctions')
DROP ASSEMBLY LegacySQLFunctions;
GO
-- If you're going to use CLR code that connects to unmanaged code (e.g. C++ OLEDB drivers)
-- you'll need to enable TRUSTWORTHY attribute on the database, and use the UNSAFE
-- permission set on the assembly. Otherwise, skip this command, and use PERMISSION_SET=Safe
ALTER DATABASE MyPortal SET TRUSTWORTHY ON
GO
-- Add the assembly and CLR integration based stored procedure
CREATE ASSEMBLY LegacySQLFunctions
FROM 'C:\components\SQLCLR\LegacySQLFunctions.dll'
WITH PERMISSION_SET = UnSafe;
GO
-- Now, bind the SQL function to the code-behind method in the assembly
CREATE FUNCTION dbo.fnGetUserDetails( @UserAccountLike NVARCHAR(20) )
RETURNS TABLE
(
UserAccount NVARCHAR(10), FirstName NVARCHAR(14), LastName NVARCHAR(14), DateOfBirth DATETIME
)
AS EXTERNAL NAME LegacySQLFunctions.[ACME.TheApp.MyPortal.LegacySQLFunctions].GetUserDetails; |
After running the above SQL query, the function should be callable from anywhere within SQL code. Here are a few fictitious examples of how I can use this function now that it's been created:
|
-- Example 1: returns all user detail records from the legacy database where the legacy
-- user account name begins with JWHITE
SELECT * FROM dbo.fnGetUserDetails( 'JWHITE%' )
-- Example 2: Retrieves recently logged in Portal users, with a join to legacy user details.
SELECT PortalUser.LastLoginDate, ud.FirstName, ud.LastName, ud.DateOfBirth, PortalUser.Email
FROM PortalUser
INNER JOIN dbo.fnGetUserDetails( '%' ) AS ud
ON PortalUser.LegacyAccountName = ud.UserAccount
WHERE PortalUser.LastLoginDate > DateAdd(dd, -1, getdate())
ORDER BY PortalUser.LastLoginDate DESC
|
The CLR assembly runs very fast. The above code runs sub-second.
Conclusion
SQL 2005 User Defined Functions coupled with the .NET CLR is a beautiful thing!!! Certainly great for legacy integration and encapsulation.