Deploying assemblies (SQLCLR)
Published 31 J 2019
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:
- Stored procedures
- DDL Triggers
- User-defined functions
- User-defined aggregates
- 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:
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:<ItemGroup>
<ProjectReference Include="..\ClrTestLib\ClrTestLib.csproj">
<Name>ClrTestLib</Name>
<Project>{34f86898-f078-4ce0-85ef-3e51b208672b}</Project>
<Private>False</Private>
<IsModelAware>True</IsModelAware>
<GenerateSqlClrDdl>True</GenerateSqlClrDdl>
</ProjectReference>
</ItemGroup>For a reference to a DLL file:
<ItemGroup>
<Reference Include="ClrTestLib">
<HintPath>..\lib\ClrTestLib.dll</HintPath>
<IsModelAware>True</IsModelAware>
</Reference>
</ItemGroup>- 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" />
Reference the file within the script:
CREATE
ASSEMBLY [ClrTestLib]
AUTHORIZATION
[dbo]
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.