Create CLR objects in SSDT

I don’t know whether the same thing was possible in Data Dude / DB Pro but in SSDT for VS 2012 and VS 2013 it is very simple to create your CLR objects. I did originally think (maybe incorrectly) that you had to create a separate solution for your CLR .NET code that when built would produce a DLL that you included as a reference in your DB project. You then needed to create a CREATE ASSEMBLY script and then a script to create the SQL object, e.g. CREATE FUNCTION…

I’ve recently found out that in SSDT you just create your CLR .NET object within your DB project and when you publish the DB it generates everything for you.

In a previous post I talked about using a CLR aggregate function to concatenate row data into a single string.

I created this aggregate in SSDT by creating a new SQL CLR C# object in my DB project as shown below.

SSDT CLR  C# Aggregates 1

As you can see in the list on the left there are SQL CLR and SQL CLR C# object types available. The SQL CLR objects are the objects that you see in SQL Server, i.e. the objects that you can create with T-SQL CREATE statements. The SQL CLR C# objects are the things that contain the .Net code.

You can tell SSDT to automatically create the SQL objects required to utilise your new CLR code by opening the properties of the database project that the code exists in.  On the SQLCLR tab there is a checkbox Generate DDL.

SSDT CLR  C# Aggregates 5

If this is checked then SSDT will automatically generate the SQL object in the dbo schema.  

You can see this in the Projects folder in SQL Server Object Explorer in VS as shown below…

SSDT CLR  C# Aggregates 2

… and also once you’ve published the database project you will see the objects in SSMS as shown below.

SSDT CLR  C# Aggregates 3

Above you can see both the assembly that has been created (with the same name as the database project, SimonBlog) and the SQL object (in this case an aggregate function, ConcatMagic).  Any other CLR objects that you add to the same database project will also get published in the same assembly.  You can confirm that the correct .Net method is being used by scripting the aggregate function.

SSDT CLR  C# Aggregates 4

If you want to add your objects to a schema other than dbo then it seems you have to uncheck the Generate DDL checkbox in the database project settings and add the required SQL objects, e.g. the CREATE AGGREGATE script.  Then when you publish SSDT won’t create the object automatically in dbo but will still publish the assmebly and run in the CREATE AGGREGATE script.