Hi all,

I wanted to ask a question regarding CLR and whether we can ultilise it with SQL and ASP.NET Core

We have a SQL Server project which takes in datafeeds from multiple sources and transforms/converts the data before inserting it into the DB. Sometimes there is logic which looks at the data and based on its context, we may create or 'infer' more records out of this data. We currently use TSQL to apply this logic which is complex but does the job. However, we also have a first-party web application which is another source of data. This is currently a PHP backend application with Angular 5 front-end. The problem here is that we need to write the same logic in PHP as well. This means we're writing the same logic in two different applications which currently leads to a few problems with bugs, mismatched logic and validation rules aren't in sync etc. The PHP application also uses the same DB and the SQL Server team builds table views to meet the table/naming conventions of the PHP application.

I recently discovered an article about CLR and that SQL Server is also compatible, the company has recently decided that we're going to revamp and rewrite the entire feature. We're currently in talks on how to implement.

I'm leaning towards creating a microservice (in .NET core) for this feature and create API endpoints to allow the PHP application to interact with it. The issue still stands that we need the SQL server project to accept the datafeeds, so if we built this microservice API in .NET CORE, is it possible to use or import the logic/functions to the SQL Server project via CLR?

Or am I misunderstanding how it all works? I'm the PHP developer on this product. If the CLR will work for us in this way it would mean that we only need to write the logic in one place (C# and .NET Core) and use it for both the SQL Server project and the PHP application (via the microservice API). I'm assuming that's what the CLR is designed to do?