SQL Change Automation 4

Deploying assemblies (SQLCLR)

SQLCLR is a technology for hosting the .NET common language runtime engine within SQL Server. It allows users to create some programmable objects in a .NET language like C#.  The programmable objects it supports are:

  1. Stored procedures
  2. DDL Triggers
  3. User-defined functions
  4. User-defined aggregates
  5. User-defined types

SQL Change Automation features a code first workflow for building and deploying SQLCLR which allows you to edit your database objects and code libraries together.

When generating SQL deployment scripts SQL Change Automation will inline any SQLCLR DLL file into the appropriate migrations.

The following steps are necessary to configure this:

  1. Add a reference to the SQLCLR project or dll within your SQL Change Automation project
    For a reference to a project within the same solution:

      <ProjectReference Include="..\ClrTestLib\ClrTestLib.csproj">

    For a reference to a DLL file:

      <Reference Include="ClrTestLib">

  2. Create a migration script, programmable object , or an additional script and add the InlineDependencies flag to the metadata

    For a programmable object or an additional script:

    -- <Migration InlineDependencies="true" />

    For a migration script:

    -- <Migration ID="(migration id)" InlineDependencies="true" />

  3. Reference the file within the script:

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

Download a sample project to see the full behavior:  ClrTestDB.zip  (8 kB).

For an in-depth tutorial, see SQLCLR in Visual Studio.

Didn't find what you were looking for?