Create, Deploy & Use MS SQL Server CLR functions in the OutSystems Enterprise Cloud

 Create, Deploy & Use MS SQL Server CLR functions in the OutSystems Enterprise Cloud

  

Introduction

With the OutSystems Platform Enterprise Cloud you can use the SQL Server feature to deploy Common Language Runtime Assemblies directly on the database functions. This way you can deploy .Net functions directly on the database server that can improve the scalability of your data logic and data processing  requirements.

In this post you will find information on how to create and deploy an assembly with functions that can be used directly on the database server as if they were relational database functions. All the functions you create should be created under a specific customer schema provided by OutSystems upon request.

 

CLR assemblies lifecycle

Before we start, note that the deployment of CLR assemblies is separate from OutSystems Platform’s automatic deployment mechanisms (Lifetime) and should be treated as an external dependency that needs to be kept in sync.
This means that if you change the CLR assembly in one environment, you have to make sure you change it in other environments as well.

Deploying applications via Lifetime won't automatically update the target environment's CLR assembly.

 

Pre-requisites

  • Database credentials for direct access to your OutSystems Enterprise Cloud platform;
  • Visual Studio 2010 or above in your local computer;
  • SQL Management Studio 2008 R2 or above in your local computer;
  • SQL Data Tools installed in your local computer;


1. Create a New SQL CLR Assembly with functions

  1. In Visual Studio, create a New SQL Server Database Project.
     
  2. Go to Project Properties on the project you have just created:

    1. Mark the Create Script Checkbox as Checked.
       
    2. Change the default schema to be the Customer_### schema provided by Outsystems.
       
    3. Don’t forget to save your changes.
       
  3. Add New Item to the project  and choose SQL CLR C# > SQL CLR C# User Defined Function.

     
  4. Develop your function(s) under a new class.

     
  5. Add a new Schema  Item to the project  with the Name : Customer_####.
  6. Build the project in Visual Studio.
     
  7. Afterwards, to get the SQL to be deployed on the SQL Server:

    a) Go to the the obj directory under your project;
    b) Locate the ".generated" file - this file contains the SQL Function(s) definition, the CREATE ASSEMBLY definition and the ALTER ASSEMBLY definition generated for deployment. This is the file you will use to create the assembly in the OutSystems Platform Enterprise Cloud




     

     

2. Deploy CLR  assemblies to the database of an individual environment

  1. Open a SQL Management Studio and use the your CUSTOMER_### credentials provided by OutSystems to connect to the database of the environment where you are deploying.
     
  2. Execute the statement you gathered from the ".generated" file (under 1.6 above) to create a new assembly.
    Note that this assembly will be created in the CUSTOMER_### SCHEMA.
    Your final statement should look like the one below.


     
  3. Create the functions that will be used in your application to call the CLR code:


     
    In order to call the new function via Service Studio please be sure you have granted the EXEC privilege to the function(s) you have created.


References

- http://msdn.microsoft.com/en-us/library/ms186755.aspx
- http://msdn.microsoft.com/en-us/library/bb669061(v=vs.110).aspx
- http://www.tryexcept.com/articles/2009/09/22/using-net-assemblies-inside-sql-server-quick-start-guide.html