Aaron Lowe

Since I started blogging again, I haven’t done any technical posts yet and I figured it’s about time.

I have been using Visual Studio Database Projects since DataDude beta’s came out in 2005.  A while ago, I was tasked with a database project that had a CLR dependency and working through the dependency.  Here’s how to have a database project that depends on a CLR project and works for Deployment. 

Let’s create a simple Database Project with just 1 table for Customers.  In the Customer table  we’ll store an email that will have a constraint for proper email format written in CLR.  We’ll also build the dependency between the projects and configure proper deployment.

So first we need to create the Database Project called CLRDependency:

And a simple Customers table:

CREATE TABLE [dbo].[Customers]
(
    pkCustomerID int NOT NULL IDENTITY(1,1),
    FirstName varchar(25) NULL,
    LastName varchar(25) NOT NULL,
    EmailAddress varchar(50) NOT NULL
);

Next let’s add a new CLR project called CLRFunctions:

When creating this, it will ask for a database to reference.  Since we haven’t created the database yet you can just click Cancel.  Next Create a UserDefinedFunction called ValidateEmailAddress.cs with the following code:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text.RegularExpressions;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlBoolean ValidateEmailAddress(SqlString emailAddress)
    {

        return new SqlBoolean(Regex.IsMatch(emailAddress.Value, @"^([\w-\.]+)@((\[[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.)|(([\w-]+\.)+))([a-zA-Z]{2,4}|[0-9]{1,3})(\]?)$"));
    }
};

In Solution Explorer, Right-click on References in the database project and click on Add Reference, then select the project :

You’ll see CLRFunctions appear under References:

Now you only have to build the CLRFunctions Project and the Assembly will appear in your Database Schema View:

Now we need to create the UserDefinedFunction in the Database Project so Right-Click on Functions and Add a Scalar Function:

CREATE FUNCTION [dbo].[ValidateEmailAddress]
(
    @EmailAddress nvarchar(50)
)
RETURNS BIT
AS
    EXTERNAL NAME [CLRFunctions].[UserDefinedFunctions].[ValidateEmailAddress];

Lastly let’s create the Check Constraint:

ALTER TABLE [dbo].[Customers]
    ADD CONSTRAINT [CK_ValidateEmailAddress]
    CHECK  ([dbo].[ValidateEmailAddress](EmailAddress) = 1);

So now we have the two projects working together and everything is great.  However if you build it, you’ll see this:

------ Build started: Project: CLRFunctions, Configuration: Debug Any CPU ------
  CLRFunctions -> C:\AMLProjs\Local\CLRDependency\CLRFunctions\bin\Debug\CLRFunctions.dll
------ Build started: Project: CLRDependency, Configuration: Debug Any CPU ------
  CLRDependency -> C:\AMLProjs\Local\CLRDependency\CLRDependency\sql\debug\CLRDependency.dbschema
------ Deploy started: Project: CLRFunctions, Configuration: Debug Any CPU ------
Error: Cannot deploy. There is no database connection specified. To correct this error, add a database connection using the project properties.
------ Deploy started: Project: CLRDependency, Configuration: Debug Any CPU ------
    Deployment script generated to:
C:\AMLProjs\Local\CLRDependency\CLRDependency\sql\debug\CLRDependency.sql 

    The deployment script was generated, but was not deployed. You can change the deploy action on the Deploy tab of the project properties.
========== Build: 2 succeeded or up-to-date, 0 failed, 0 skipped ==========
========== Deploy: 1 succeeded, 1 failed, 0 skipped ==========

That’s not good, we have a failure.  However this is easily solved.  Just uncheck the deployment option on the CLR Project in the solution properties:

And now when you deploy you will see:

------ Build started: Project: CLRFunctions, Configuration: Debug Any CPU ------
  CLRFunctions -> C:\AMLProjs\Local\CLRDependency\CLRFunctions\bin\Debug\CLRFunctions.dll
------ Build started: Project: CLRDependency, Configuration: Debug Any CPU ------
    Loading project references...
    Loading project files...
    Building the project model and resolving object interdependencies...
    Validating the project model...
    Writing model to CLRDependency.dbschema...
  CLRDependency -> C:\AMLProjs\Local\CLRDependency\CLRDependency\sql\debug\CLRDependency.dbschema
------ Skipped Deploy: Project: CLRFunctions, Configuration: Debug Any CPU ------
Project not selected to build for this solution configuration
------ Deploy started: Project: CLRDependency, Configuration: Debug Any CPU ------
    Deployment script generated to:
C:\AMLProjs\Local\CLRDependency\CLRDependency\sql\debug\CLRDependency.sql

    Creating CLRDependency...
    Creating [CLRFunctions]...
    Creating [dbo].[Customers]...
    Creating [dbo].[ValidateEmailAddress]...
    Creating CK_ValidateEmailAddress...
    Checking existing data against newly created constraints
========== Build: 2 succeeded or up-to-date, 0 failed, 0 skipped ==========
========== Deploy: 1 succeeded, 0 failed, 1 skipped ==========

Something to remember is that you can actually deploy this to a server that does not have CLR Enabled, however when you go to insert anything into that table, it will fail with this error: 

Msg 6263, Level 16, State 1, Line 1
Execution of user code in the .NET Framework is disabled. Enable "clr enabled" configuration option.

So make sure you enable CLR on the Server.  Now onto testing (you do test your work right?)

USE CLRDependency;
GO
INSERT INTO dbo.Customers
    (FirstName, LastName, EmailAddress)
VALUES
    ('Aaron', 'Lowe', 'AaronL@AaronLowe.net');
GO
INSERT INTO dbo.Customers
    (FirstName, LastName, EmailAddress)
VALUES
    ('Aaron', 'Lowe', 'boo');
GO

Results as expected:

(1 row(s) affected)
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint "CK_ValidateEmailAddress". The conflict occurred in database "CLRDependency", table "dbo.Customers", column 'EmailAddress'.
The statement has been terminated.