2

For the existing application that uses the MS SQL server, is it possible to implement a C# functionality that would be mapped as an SQL table?

In other words, can a C# functionality be queried from a legacy SQL query so that it acted as if the SQL table was there?

The motivation: I have blobs with heavily compressed data (archived as files elsewhere). I would like to replace the huge existing SQL table so that the data would be extracted from the archive in the background.

Think as if the SQL table was a cache memory that could be queried as a normal table. When there is a cache miss, the data is extracted by a C# code to fill the missing data in the cache, and only then the real SQL query is performed.

The operation is not time critical. This way even a simpler solution is acceptable -- when the SQL query appears, the arguments are somehow passed to a C# function that fill the empty table with the extracted data, and then the SQL query is performed and the data is returned.

Is anything like that possible at MS SQL server?

pepr
  • 20,112
  • 15
  • 76
  • 139
  • Maybe you look something like: http://stackoverflow.com/questions/13420305/storing-files-in-sql-server – VikciaR Feb 13 '17 at 08:46
  • To add: just use built in FILESTREAM functionality, there is no need to make custom CLR implementation. – VikciaR Feb 13 '17 at 08:47
  • and there is still the option to implement a table-valued function in .NET: http://stackoverflow.com/questions/5653963/how-is-a-clr-table-valued-function-streaming, https://msdn.microsoft.com/en-us/library/ms131103.aspx – Cee McSharpface Feb 13 '17 at 08:48
  • Are you trying to manually implement [Stretch Database](https://msdn.microsoft.com/en-gb/library/dn935011.aspx)? – Damien_The_Unbeliever Feb 13 '17 at 08:48
  • @VikciaR Actually, I need to use the C# code as it should compress the legacy SQL table. They are samples of an analogue signal [a sensor], and the C# code detects the sampling period, discontinuities of the curve, separates timestamps from values, and the values are compressed using a suitable technique. The result is actually a blob but it cannot be directly used. The C# code must be used to unpack the data back. – pepr Feb 13 '17 at 09:02
  • @Damien_The_Unbeliever I will read more about the Stretch Database for inspiration. Anyway, the solution must fit with the legacy application that runs on MS SQL 2012. – pepr Feb 13 '17 at 09:04
  • @pepr: NTFS compression? – VikciaR Feb 13 '17 at 09:07
  • Are you looking maybe for something like [this](https://www.codeproject.com/Articles/19954/Execute-NET-Code-under-SQL-Server) ? – Pikoh Feb 13 '17 at 10:04
  • @VikciaR No. It is a special compression designed for the sampled analogue signal. It uses quantization of the sample values, using a function to predict the next value, calculation of the difference, variable-length encoding of the difference and zipping the resulting sequence (plus some details). – pepr Feb 13 '17 at 12:42
  • @Pikoh Thanks, but I am aware of the possibility to call C# code from SQL. The question is whether the call can be kind of _transparently_ hooked into existing SQL queries. – pepr Feb 13 '17 at 13:09
  • 1
    I see.The only thing I can think about would be a .net program called by a sql programmed task (o maybe using a trigger?) that could keep an updated view with all the transformations you need.... – Pikoh Feb 13 '17 at 14:19
  • 1
    I would recommend then using Table Valued function hidden by View. Then usage would be select * from myView; and your legacys software requirements, I think, will be fulfilled. I have some doubts about performance of this solution, but you can try. – VikciaR Feb 13 '17 at 14:41
  • 1
    BTW, some time ago I asked similar question: http://stackoverflow.com/questions/31625273/stored-procedure-output-to-view Some people thinks, that this type of problem is uncommon - question has rating -1 :-) – VikciaR Feb 13 '17 at 14:43
  • @VikciaR Thanks for the pointers. I have found a different workaround which will be simpler (to hack one of the existing stored procedures in the solution). But the idea with table-valued function looks nice. However, as you said, the performance would be questionable because of the nature and quantity of the data. – pepr Feb 14 '17 at 09:28

1 Answers1

0

You can achieve that using partial class. 

public partial class MyTable
{
    public byte[] HeavilyCompressedData
    {
        get
        {
            //loading and returning large data
        } 
    }
}

Remember that you can't write a condition in LINQ on this property (The specified type member is not supported in LINQ to Entities), but I think you don't want to do that.

Tomas Chabada
  • 2,869
  • 1
  • 17
  • 18
  • Thanks for trying to help. But I am aware of the possibility to call C# code from SQL. This is the reason why I am thinking about implementation in C#. The question is whether the call can be kind of _transparently_ hooked into existing SQL queries. In other words if the `SELECT * FROM xxx` can call the code internally. That is, if the `xxx` content can be obtained from the code instead of getting it from a table. (Say, `xxx` is a view.) – pepr Feb 13 '17 at 13:12