using System;
using System.Collections.Generic;
using System.Data.SqlTypes;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString HelloWorld()
    {
        return new SqlString("Hello world!");
    }
}

Now add a project reference from your SQL Change Automation project to your class library:

Set the Copy Local property to True for the newly-added reference (this copies the assembly to the SQL Change Automation project at build time):

Next we'll create a re-usable script to deploy the assembly to the database. Start by adding a new folder called SQLCLR to the root of your SQL Change Automation project:

Include the new folder in the database deployment by opening the project designer and scrolling down to the Additional Scripts section. Check the item matching the name of the folder you just added:

Using the Solution Explorer, right-click the SQLCLR folder and add a script called ClrTestLib.sql. The following metadata should be included at the top of the script:

-- <Migration InlineDependencies="true" />

This metadata indicates that the script contains dependency references that should be serialized (i.e. replaced with the contents of the dependent files) at build time.

Complete the script by adding statements to DROP and CREATE your assembly and associated CLR functions/procs:

-- <Migration InlineDependencies="true" />
GO

IF OBJECT_ID('[dbo].[HelloWorld]') IS NOT NULL
BEGIN
  DROP FUNCTION [dbo].[HelloWorld]
END

IF EXISTS(select 1 from sys.assemblies where name = 'ClrTestLib')
BEGIN
  PRINT N'Dropping [ClrTestLib]...';
  DROP ASSEMBLY [ClrTestLib];
END
GO

PRINT N'Creating [ClrTestLib]...';
CREATE ASSEMBLY [ClrTestLib]
  AUTHORIZATION [dbo]
  FROM "{ClrTestLib}";

ALTER ASSEMBLY [ClrTestLib]
  ADD FILE FROM "{ClrTestLib.pdb}"
  AS N'ClrTestLib.pdb';
GO

PRINT N'Creating [dbo].[HelloWorld]...';
GO
CREATE FUNCTION [dbo].[HelloWorld] ( )
 RETURNS NVARCHAR (4000)
AS
 EXTERNAL NAME [ClrTestLib].[UserDefinedFunctions].[HelloWorld];

GO
PRINT 'Result from [dbo].[HelloWorld]: ' + [dbo].[HelloWorld]();
GO

Note that the CREATE ASSEMBLY statement contains a special token, enclosed in quotes and curly braces:

CREATE ASSEMBLY [ClrTestLib]
AUTHORIZATION [dbo]
FROM "{ClrTestLib}";

The "{ClrTestLib}" token – which in this case refers to the assembly name – will be replaced with a serialized copy of the assembly at build time. Be sure to include the file extension if you wish to deploy files other than assemblies (e.g. .pdb files, as in the above example).

To deploy the assembly and related files to the database, click Build… Deploy Solution:

The output window will show the assembly being built and subsequently deployed to the database:

Switching over to SQL Server Management Studio reveals the two objects created by the deployment:

Upon subsequent deployments, you’ll notice that your script is executed each time you perform a “clean” build of your solution. This is because the CLR compilation process results in a different assembly being created with every build, even if the code itself hasn’t changed.

Conversely, during an incremental build/deployment, your script may not be executed as a result of artifact caching (this is normal MSBuild behavior).

Packaging & Deploying to Remote Servers

When it comes to packaging your assemblies for deployment to other environments, SQL Change Automation will automatically include all artifacts within the ClrTestDb_Package.sql file (provided you have the SQLCMD Package (.sql) output option enabled in Project Settings): deploy the package file within SQL Management Studio to target database servers, or via the accompanying PowerShell script.